Configuring an ExactFlat Database

Configuring an ExactFlat Database

1. SQL Server
Organizations with mutilple users of ExactFlat should use a SQL database located on a network server. Individual users may opt to run SQL locally using SQL LocalDB. In local mode, data backups are handled by the individual user.  See below to learn more about which option to choose.
An ExactFlat SQL Database is appropriate for large enterprises.  A SQL database will require a Microsoft SQL installation. ExactFlat requires SQL 2012 or newer. Running a network server allows many users to connect a the same set of standard data. See below to download a sample SQL database. Feature data used in SolidWorks such as material information, seam allowances and notches is synchronized to local machines each time a connection is made or refreshed. This allows ExactFlat to run seamlessly if a connection to the server is unavailable. Using a network database provides many advantages including:
  • Promotes standard data within an engineering work group.
  • Training new users is simplified, standard data appears when needed.
  • Data backups can be managed using SQL or other database format.
  • Data can be integrated with outside systems.  For example, cost data updates can be automated and fully formatted BOMs can be uploaded to outside systems.
2. SQL LocalDB 

An ExactFlat LocalDB is appropriate for individual users. A sample database is created when ExactFlat is installed. A local database is set up automatically when ExactFlat is installed. The advantage of a local database is simplicity, however there are many disadvantages including:

  • Data backups require managing the local database file.
  • Data communication with other users is restricted.
In order to enable LocalDB, use the LocalDB option available in the installer. A sample dataset and data connection is provided with the installer. The default directory for the local dataset is here: C:\ProgramData\Tri-D Technologies Inc\ExactFlat\Standalone Databases
2.1 All ExactFlat installations will require the following
  • Install either SQL Server 2012 or use the SQL LocalDB option packaged with the installer.
  • Add a Database Connection to the Database Manager.
  • Connect a database Refresh Database using SolidWorks.
3. SQL Database Configuration
An ExactFlat SQL connection allowsthe client computers are made via the ExactFlat Database Manager. ExactFlat is dependent on data to automate design and costing functions. The ExactFlat database is configured to run in conjunction with SQL server 2012 or newer. SQL Express is a free tool for managing databases up to 10GB. The latest version of SQL is available here: SQL Express 2012. An ExactFlat  database can be loaded into SQL via SQL Management Studio.
4. Installing a Stand Alone Database

A Stand Alone Database provides the ability to manage and use local data rather than using a network database.  Each local database stores data in a .mdf format which can be edited using the ExactFlat Database Manager. Each database is stored in a folder located here:

  • C:\ProgramData\Tri-D Technologies Inc\ExactFlat\Standalone Databases
To set up a local database open the ExactFlat Database Manager. Enter a new database name and database.  Click on connect.  Any changes made to the database after connecting are stored in the local folder. A database can be copied to another local machine.  In order to connect to the database on another machine,  the database folder must first be saved to the stand alone database folder. The database field must match the folder name.   The user can now connect to the local data.  An ExactFlat  Database can be migrated and attached to a SQL network datbase at any time.
5. Database Setup and Connection Guide
5.1. Installing Microsoft SQL Server
ExactFlat for SOLIDWORKS requires Microsoft SQL Server 2008 R2 or newer.  ExactFlat for SOLIDWORKS is also fully compatible with Microsoft SQL Server EXPRESS. This step is only required if there is no existing Microsoft SQL Server 2008 R2 or newer installation that can be used for ExactFlat.  This step will provide basic steps for installing the minimum required features of Microsoft SQL Server 2008 R2 64-bit for use with ExactFlat. This installation guide also assumes that SQL Server has not yet been installed on the server.

5.3. Enabling TCP/IP Protocols: Detailed instructions for this topic can be found at the following link:

5.2. Review
  • A Microsoft SQL Server 2008 R2 instance has been installed.
  • The instance ID is ExactFlat.
  • The database engine has been configured for mixed mode authentication.
  • The system administrator password is ExactFlat.
  • The SQL Server administrator is ExactFlatSA.

5.1.6. Database Engine Configuration: Tri-D Technologies Inc. recommends changing the authentication mode during the Database Engine Configuration step.  The default is Windows authentication mode which gives a specific Windows user on the installation server administrative permissions to the instance.  This may become a problem for remote administration in non-active directory environments or if the the selected user account does not exist on the remote computer used for instance administration.  For these reasons, we recommend Mixed Mode authentication.  For the purpose of this guide, the SQL Server system administrator (sa) password will be ExactFlat. For added security, Tri-D Technologies Inc. recommends a dedicated user account be created to act as the system administrator. The dedicated user does not require administrative privileges on the server to be a SQL Server system administrator.  For the purpose of this guide, the dedicated user account name will be ExactFlatSA.

  • Note: The system administrator user account is a special user account that has full administrative permissions to the server instance.  This user can perform any action to any database hosted on the server instance.  The password for this account should be a tightly guarded strong password to prevent unwanted access of the database or unintended data loss.
5.1.5. Server Configuration: The default Server Configuration values are adequate.  These values should only be changed if you have a strong understanding of Windows user and group permissions and Windows services.
5.1.4. Instance Configuration: Select Named instance and give this instance a name.  This guide will use ExactFlat as the Instance Name.  The Instance ID will automatically update to match the Instance Name.
By default, the instance root is “%ProgramFiles%\Microsoft SQL Server”.  This is the directory where all database files will be stored.  This can be changed so the data is stored on a separate drive other than the root drive.  Possible locations include network shares or NFS shares.
5.1.3. Feature Selection: The only feature required is Database Engine Services, although the other features will not inhibit database functionality.
5.1.2. SQL Server 2008 R2 Setup: Only the steps important to ExactFlat for SOLIDWORKS are covered by this guide.  The remaining steps either have no user interaction or simply require you to manually advance the installation process.
5.1.1. SQL Server Installation Center: To begin the installation, select Select New installation or add features to an existing installation from the list of options.
5.3.1. Enabling TCP/IP

Connection to a Microsoft SQL Server instance over a network requires the TCP/IP protocol to be enabled for instance ID ExactFlat.  This is done through the SQL Server Configuration Manager.

  1. In the left-hand panel, expand SQL Server Network Configuration and select the instance ID of your new server.
  2. In the right-hand panel, right-click TCP/IP and select Properties.
  3. Under the Protocol tab, change the value of Enabled to yes.
  4. Under the IP Addresses tab, find the IP address you wish to expose your instance on.  Change the value of Enabled to yes.  Delete the value of TCP Dynamic Ports if not empty.  Change the value of TCP Port to the desired port number.  The default port number for the Microsoft SQL Server Database Engine is TCP port 1433.  If Port 1433 is already in use, then port 1443 is an acceptable alternative.  Please contact your network administrator if you are unsure of which port to use. This guide will use port 1443.
  5. Click the Apply button.
  6. In the left-hand panel, select SQL Server Services.
  7. In the right-hand panel, select the instance ID of your new server.
  8. Either right-click the instance name and select Restart, or click the blue Restart icon in the toolbar.  The instance will be restarted.

5.3.2. Review

  • The TCP/IP protocol has been enabled for instance ExactFlat.
  • The TCP/IP protocol has been enabled on IP address 172.27.83.69 for instance ExactFlat.
  • Dynamic ports have been disabled on IP address 172.27.83.69 for instance ExactFlat.
  • IP address 172.27.83.69 for instance ExactFlat is listening on port TCP 1443.
  • Instance ExactFlat has been restarted to allow the changes to take effect.
5.4. Firewall Management
In most cases, your network or IT administrator will be required to make changes to a firewall to allow remote access to your database instance.  Changes may be required to a hardware firewall, a software firewall on the server itself, or both. If your server is protected by a firewall, the firewall will need to be configured to allow inbound TCP traffic to the port on which your database instance is listening.  For this guide, the port is 1443. The most common software firewall is the Windows firewall.  Detail instructions for configuring a Windows firewall for database engine access can be found at the following link:
5.5. Database Creation and Population
The easiest way to create and populate a database for use with ExactFlat for SOLIDWORKS is to use Microsoft SQL Server Management Studio.
5.6. Database User Accounts
Do not connect to the database using the system administrator user account.  Instead, create a separate non-administrative database user account for use with ExactFlat for SOLIDWORKS.
ExactFlat for SOLIDWORKS requires these permissions on the database user account to function correctly:
  • Connect
  • Delete
  • Create table
  • Insert
  • Update
  • Select
  • Alter
  • Execute
  • References
5.5.3. Populating the Database
Use Microsoft SQL Management Studio and the provided .SQL script.  DO NOT COPY .MDF and .LDF files.
5.5.2. Creating a New Database
Right-click the databases icon and select “New Database”
5.5.1. Connecting to a Database Instance
To connect to a database, click the database connection icon.  The connection details window will appear.
Enter the server address (formatted as server_address,port).
Enter the system administrator username (for this guide, ExactFlatSA) and password.
Click the Connect button.