make money

Award Winning Plant Industry Community

AutoCAD Plant 3D – SQLite to Microsoft SQL Server

by Jason Drew – Autodesk, Inc.

Learn how to implement Microsoft SQL Server or SQL Server Express with AutoCAD Plant 3D and P&ID. This class will take users through the configuration process for new projects and migrating existing projects to use MS SQL Server. See how a server-based database can be used for improved performance and reliability in multi-user network environments.

Learning Objectives

At the end of this class, you will be able to:

Setup new projects on SQL Server

Migrate existing projects to SQL Server

Move & Copy SQL server project databases from one server to another

Use SQL queries for custom project reports

About the Author

Jason is a Product Support Specialist with Autodesk. He provides support to customers using AutoCAD Plant 3D and P&ID. He is also a member of the “Krusty Krew”, a team of Plant Design professionals from around the globe. Before working with AutoCAD Plant products Jason worked for three years as an administrator/designer using Intergraph SmartPlant P&ID. His original background is information technology where he worked for six years providing computer support for an engineering company in Tulsa, Oklahoma.

Email: jason.drew@autodesk.com

Overview of Plant 3D databases

AutoCAD Plant 3D and AutoCAD P&ID use a file based database (SQLite) by default.

If a server based database is desired for improved multi-user performance and reliability, Microsoft SQL Server Express or Microsoft SQL Server can be used (2005 or 2008)

1.1 Database Structure

AutoCAD Plant 3D and AutoCAD P&ID use both the drawings and a database to store the information contained in a project. This information includes line number tags, equipment tags, pipe specifications, etc. Since the information is stored in the AutoCAD drawings in addition to the database this allows you to copy drawings across projects and retain the project data.

The SQLite databases are .dcf files stored in the project folder.

ProcessPower.dcf – P&ID database

Piping.dcf – 3D piping database

Iso.dcf – Isometric database

Ortho.dcf – Orthographic database

Once a project is migrated to SQL the project files remain where they are and the databases are stored and accessed through SQL Server.

As you work in AutoCAD Plant 3D or AutoCAD P&ID the software is periodically querying the database tables reading and writing information as needed. For example, working in a P&ID drawing when the crosshairs are moved over a primary line segment the information displayed in the rollover tips is queried from the database and displayed on the screen.

Database Engines

1.2.1 SQLite Database Engine

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. Meaning there is no separate database server required. The database engine is embedded and installed with AutoCAD Plant 3D and AutoCAD P&ID.

SQLite uses a file based system so each database is a single file stored on disk. The SQLite database engine uses a read many, write once record locking scheme. So while many users can be reading from the database at any given time the entire database must be locked before a record can be written to a table.

1.2.2 MS SQL Database Engine

Microsoft SQL is a client/server architecture. Meaning the database tranactions are passed from the client to the server, processed and then sent back.

SQL Server uses a granular approch to record locking. It only locks a single database row if it needs to be updated instead of the entire database.

SQLite

File based database

MS SQL Server

Server based database

2. Installing MS SQL Server

2.1 Software Requirements

In order to setup a new project or migrate an existing project to Microsoft SQL Server one of the following versions of MS SQL is required:

Microsoft SQL Server Express 2005 (4GB per database limitation)

Microsoft SQL Server Express 2008 (10GB per database limitation)

Microsoft SQL Server Enterprise/Standard 2005 or 2008 (no database limitations)

2.2 Install MS SQL Express

If you do not have an existing SQL server available to use, Microsoft SQL Server Express 2008 is available for download for free from Microsoft’s website (see Links and Resources section for the website link.)

2.2.1 Configure a New Instance of SQL Server

If no previous installations of MS SQL exist on the server it is recommended that you configure a new default instance. Use the default settings and a new SQL Server instance will be created called “MSSQLSERVER”.

Then run setup again and configure a new named SQL instance for Plant 3D. This simplifies the administration tasks by keeping the databases for Plant separate from existing databases that may reside on an existing SQL Server.

2.2.2 Configure a New Named SQL Server Instance for Plant 3D

Download MS SQL Server Express and launch the installation

The SQL Server Installation Center will be displayed:

Select the first choice, “New installation or add features to an existing installation.”

Accept the license terms and click Next.

The setup support files will be extracted and the Feature Selection window will appear.

(The default options are recommended)

Next, the Instance Configuration options must be set

In the “Named instance” field, input a name for the instance (i.e. Plant3D)

In the “Instance root directory” field, choose a path where the SQL instance will be installed. This should be a folder on a local hard drive of the server.

Click Next

In the Server Configuration window, the SQL Server Database Engine service should be set with to the account name “NT AUTHORITYNETWORK SERVICE, startup type Automatic.

The SQL Server Browser account name and password will be grayed out and the startup should remain “Automatic” as shown below.

Click Next

The Database Engine Configuration window will appear.

On the Account Provisioning tab select the authentication mode to be used with SQL Server.

The default is Windows authentication mode. This mode will use the same login credentials as the Windows user account.

Mixed mode uses both SQL Server authentication and Windows authentication which will require users to input a password when a project is opened from SQL Server.

Next, specify the user account(s) which will be designated as SQL Server administrators. These user accounts or user groups will have unrestricted access to the Database Engine.

Select the Data Directories tab

Type in or browse to set the data root directory. All other paths will be prefixed with this location. Then you can go down the list and modify the additional paths as desired.

Note: The data directories are where the SQL databases reside. This could be the local C: drive for your sysetm but in most cases a server uses a separate drive letter for data storage on disks in an array configuration. If in doubt, check with your system administrator or IT department before proceeding.

Figure 1 – Database Engine Configuration

Select the optional SQL Server error reporting

Click Next to continue and finish the installation process

2.2.3 Configure SQL Server for Remote Connections

Once the SQL Server instance is created you must configure SQL Server to accept remote connections. This will allow workstations access to the database across the network.

Launch SQL Server Configuration Manager from the Start Menu

Microsoft SQL Server 2008 R2 Configuration Tools → SQL Server Configuration Manager

Expand “SQL Server Network Configuration” and select the protocols for your new instance

Right-click “TCP/IP” and select “Enabled”

Restart the server for change to take effect

3. Creating a New Plant 3D 2011 Project on SQL

3.1 Configure Plant 3D database mode

Launch AutoCAD Plant 3D 2011 or AutoCAD P&ID 2011

Set the default database mode for a new project to SQL Server using PLANTCONFIGUREDATABASE at the command prompt

At the command prompt, enter:

PLANTCONFIGUREDATABASE

At the Specify Database type [Default/SQLServer] : prompt, enter:

SQLSERVER

The command prompt displays, Use a configuration file? [Yes/No] .

If you want to use a configuration (.dlk) file from a previous SQL project setup, enter Yes and enter the configuration file name. (See “Configure a database link file” in the Configure New Plant 3D 2011 Project with SQL Server whitepaper)

If you are not using a configuration file, enter No. Typically you will not use a configuration file the first time you run PLANTCONFIGUREDATABASE because the default settings will create a configuration file that you can use as a template.

The Command prompt displays Specify SQL Server and instance name in the format ServerInstance or . for the SQLExpress instance on the project machine .

If the SQL server is installed on the same machine that the project files are hosted on, and the SQL Server instance name is SQLEXPRESS, enter a period alone.

To specify either a different server name or a different instance name, enter: servernameinstancename where the servername can be a period if it is the same machine that project files are hosted on. For example, DBSERVERSQLEXPRESS, .MSSQLSERVER, or DBSERVERPID.

The database mode is now set for SQL Server and you can create a new project.

3.2 Run New Project Setup Wizard

Creating a new project will now add 4 new databases to your SQL server instance along with project files at the location specified in the Project Setup Wizard.

Note: Enabling SQL Server does not convert an existing project database. It configures the next project you create. If you wish to convert an existing project, see section 6. Migrating an Existing Plant 3D 2011 Project to SQL

4. Creating a New Plant 3D 2012 Project on SQL

4.1 Run Project Setup Wizard

Configure project options for page 1 to 4 as you normally would for a project

On page 5 select “SQL Express server database”

Type in the SQL server name and instance in the following format: servernameinstance where the servername can be a period if it is the same machine that project files are hosted on.

Click “Test Connection”

You should see the Connection Succeeded message

Enter a database name prefix or use click generate name to use a random prefix

In the Authentication Details select the authentication mode

Windows Authentication – Users will not be required to enter any credentials when the open the project because their Windows credentials will be used

SQL Server Authentication – Enter a SQL Server administrator login credentials. Users will be required to enter their credentials when the open the project.

Click Next

Click Finish

Figure 2 – Plant 3D 2012 database configuration in Project Setup Wizard

5. Migrating an Existing Plant 3D 2011 Project to SQL

5.1 Project Backup

Backup your existing project files. If the project is from an earlier version first migrate the project to the latest version you are using.

Run PnPSQLServerConverter

Navigate to one of the following:

C:Program FilesAutodeskAutoCAD P&ID 2011

C:Program FilesAutodeskAutoCAD Plant 3D 2011

Run PnPSQLServerConverter.exe

Select a project XML file by clicking the […] button to browse to the location of the project you want to convert

In the open dialog window navigate to the project folder, select the Project.xml, and click open

In the Server name field type the name of the SQL Server instance in the following format:

servernameinstance where the servername can be a period if it is the same machine that project files are hosted on.

Or click the drop-down list button to browse for an existing SQL Server instance

Click the Test Connection button to confirm the connection to the server

In the Connection Succeeded dialog box, click OK

In the database name prefix field enter a prefix to be use (for example the project name). This value will be used to create the database names (i.e. Project001_PnId, Project001_Piping, Project001_Iso)

Click the Convert button

The conversion log window will display the progress of file conversion

In the Project Successfully Converted dialog box, click OK

Click Close

6. Migrating an Existing Plant 3D 2012 Project to SQL

6.1 Project Backup

Backup your existing project files. If the project is from an earlier version first migrate the project to the latest version you are using.

6.2 Run Project Maintenance Utility

1. Browse to the installation folder for AutoCAD Plant 3D or AutoCAD P&ID

C:Program FilesAutodeskAutoCAD Plant 3D 2012 – English

C:Program FilesAutodeskAutoCAD P&ID 2012 – English

2. Run PnPProjectMaintenance.exe

3. Select “Convert a Project to SQL Express”, then click Next

4. In the Convert Details section click the Browse button

In the open dialog window browse to the location of your project and select Project.xml then click Open

In the Server field type the name of the SQL Server instance in the following format:

servernameinstance where the servername can be a period if it is the same machine that project files are hosted on.

Click the Test Connection button to confirm the connection to the server

In the Connection Succeeded dialog box, click OK

In the database name prefix field enter a prefix to be use (for example the project name). This value will be used to create the database names (i.e. Project001_PnId, Project001_Piping, Project001_Iso)

In the Authentication Details select the authentication mode

Windows Authentication – Users will not be required to enter any credentials when the open the project because their Windows credentials will be used

SQL Server Authentication Enter a SQL Server administrator login credentials. Users will be required to enter their credentials when the open the project.

Click Convert

7. Moving and Copying SQL Projects

7.1 Project Backup

Before attempting to move or copy a SQL project be sure to backup your existing project. This includes the project files and folders in addition to the SQL databases using Microsoft SQL Server Management Studio.

7.2 Moving and Copying a Plant 3D 2011 SQL Project

Once a project has been migrated to SQL Server a new project can still be created from it using Project Setup Wizard and selecting “Copy settings from existing project”. If the database mode is still set to SQL Server the new project will be created along with the new SQL databases on the server.

If you need to move or copy an existing SQL project this is a manual procedure in AutoCAD Plant 3D 2011 and AutoCAD P&ID 2011.

For projects that use SQL Server the files ProcessPower.dcf, Piping.dcf, Ortho.dcf, and Iso.dcf located in the project folder are XML files that contain database connection information for the project.

If you move the project files to another location there is no need update these XML files. The project will still find the databases on the SQL server. However if you move the SQL databases to another server, these files will need to be modified.

If you copy the project to another location you will need to also copy the SQL Server databases then modify the XML files.

For information on how to copy a database see the Link and Resources section of this document for information on how to detach and attach databases using SQL Server Management Studio.

To modify the DCF files:

In Notepad, open ProcessPower.dcf in the project folder.

Enter the new database server host in the Data Source value. This value is highlighted on the next page in the .dcf sample as SERVERNAME.

Enter the new database name in the Initial Catalog value. This value is highlighted on the next page in the .dcf sample as COMPUTERNAME_PROJECTFOLDER.

Repeat for additional DCF files (ProcessPower.dcf, Piping.dcf, Iso.dcf, and Ortho.dcf).

Example ProcessPower.dcf:

 

 

 

DatabaseEngine

Autodesk.ProcessPower.DataObjects.DbEngine.PnPMsSQLDbEngine

 

 

Data Source

SERVERNAMESQLEXPRESS

 

 

Integrated Security

true

 

 

Initial Catalog

COMPUTERNAME_PROJECTFOLDER_ProcessPower

 

 

7.3 Moving and Copying a Plant 3D 2012 SQL Project

SQL databases in AutoCAD Plant 3D 2012 and AutoCAD P&ID 2012 can be moved and copied by using the Project Maintenance Utility.

7.3.1 Move a Project Database

Moving a project using the Project Maintenance Utility will move the SQL databases from the server where the project was originally created to a destination server. It does not move the project files and folders.

Run PnPProjectMaintenance.exe from the installation folder of AutoCAD Plant 3D or P&ID 2012

Select “Move a Project Database”

Click Next

Click Browse to select the XML for the existing project to be moved.

In the Database details section specify the destination SQL Server. (The project databases will be moved to this server) Click the drop-down list button to browse for the SQL Server or type in the name using the following format:

SERVERNAMEINSTANCE

Enter a database prefix

Click Move

 

7.3.2 Copy a Project Database

Copying a project using the Project Maintenance Utility will copy the SQL databases from the server where the project was originally created to a destination server. It does not copy the project files and folders.

Run PnPProjectMaintenance.exe from the installation folder of AutoCAD Plant 3D or P&ID 2012

Select “Copy a Project Database”

Click Next

Click Browse to select the XML for the existing project to be moved.

In the Database details section specify the destination SQL Server. (The project databases will be copied to this server) Click the drop-down list button to browse for the SQL Server or type in the name using the following format:

SERVERNAMEINSTANCE

Enter a database prefix

Click Copy

8. SQL Queries and Reports

8.1 Use Project Data from SQL Server in Microsoft Excel

Once you have migrated a project to MS SQL Server you can access that data from external programs to perform queries and run reports.

For example, you need a list of instrument tags from your P&IDs. You could export this information to an Excel spreadsheet through Data Manager, but the information stored in that spreadsheet would only be as current as the last export. Using an ODBC connection allows you to pull current information directly from the database.

8.1.1 Connection to SQL Server

Launch Microsoft Excel

Create a new blank workbook

Select the “Data” tab from the ribbon bar

In the Get External Data panel, click the “From Other Data Sources” drop-down

Select “From SQL Server”

In the Data Connection Wizard window enter the name of the SQL Server in the following format: SERVERNAME/INSTANCE

Select the authentication mode (Windows Authentication or SQL Server Authentication)

Click Next

In the Select Database and Table section, use the drop-down list to choose the database for the project (the example below is connecting to a P&ID database)

Leave the “Connect to specific table” checkbox enabled

Select the table to import (the example below is using the Instrumentation table)

Click Next

Enter an optional description for the data connection

Click Finish

In the Import Data window, click OK

The data will be imported into your Excel spreadsheet

8.1.2 Microsoft Query

Another option for creating a link to project data on SQL Server is to use Microsoft Query. This will require a preconfigured ODBC DSN created using the ODBC Data Source Administrator from Administrative Tools in Windows Control Panel.

Launch Microsoft Excel

Create a new blank workbook

Select the “Data” tab from the ribbon bar

In the Get External Data panel, click the “From Other Data Sources” drop-down

Select “From Microsoft Query

Select the User DSN for the ODBC connection to SQL Server

Click OK

Select the table and columns containing the data to be linked by selecting from the “Available tables and columns” pane and clicking the right arrow to add to the “Columns in your query” pane

Click Next

Select the desired filters (to include all data leave the filters blank)

Click Next

Select the desired sort order (if you don’t want to sort, leave blank)

Click Next

To bring the data directly into Excel, choose “Return Data to Microsoft Excel”

To view and edit the query choose “View or edit query in Microsoft Query”

15. Click Finish

9. Tips and Tricks

9.1 Database Backup Routine

After migrating projects to SQL Server keep in mind that the database are stored on the SQL server while the project files are stored in a separate location. Make sure to update your backup routine accordingly.

9.2 Project Maintenance Routine

In order to ensure database consistency run project audit periodically. This will verify the data stored in the database against the data stored in the drawings. Also, if you have end up with a tag number that you cannot assign but isn’t used in any drawings, run project audit to purge the tag number from the database. This is typically caused when AutoCAD Plant 3D or AutoCAD P&ID crashes, or by manually closing out by using “end task” from Windows Task Manager. The result is the drawing not being saved to disk leaving an orphaned tag number in the database.

9.3 Project Portability

Before migrating to SQL Server consider your field site employees and remote offices. Will they have a fast, reliable connection to the SQL Server at the home office?

One workaround is to create a new project from an existing template project (be sure to switch back to SQLite database mode first when using 2011.) This allows a copy of the project to be used while out of the office. The updated drawings can be copied into the project stored on SQL Server once back in the office.

9.4 Network Performance

One of the best ways to increase performance of AutoCAD Plant 3D and AutoCAD P&ID is to use a gigabit network connection throughout your network. Most desktops, laptops, and servers today already have a gigabit Ethernet connection built-in. Using a 10/100Mbps network switch to connect to your server will limit the connection down to 100Mbps.

10. Links and Resources

Download Microsoft SQL Server Express

http://www.microsoft.com/sqlserver/en/us/editions/express.aspx

Microsoft SQL Server 2008 R2 Express System Requirements

http://msdn.microsoft.com/en-us/library/ms143506.aspx

 

How to: Detach a Database (SQL Server Management Studio)

http://msdn.microsoft.com/en-us/library/ms191491.aspx

How to: Attach a Database (SQL Server Management Studio)

http://msdn.microsoft.com/en-us/library/ms190209.aspx

Sharing Data Between Files

Available for download in the AU class hand-out section:

Autodesk Whitepaper – Creating a Project that uses SQL Server

Autodesk Whitepaper – Migrate a Project to SQL Server

Copyright information

Making the Move from SQLite to Microsoft SQL Server: AutoCAD Plant 3D Database Configuration is an independent publication and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft Corporation.   

Windows, Excel, SQL Server, SQL Server Express, SQL Server Enterprise and SQL Server Standard are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Top