Autodesk Plant Solutions
Whitepaper
Creating a Project that uses SQL Server Introduction
AutoCAD Plant 3D 2011 and AutoCAD P&ID 2011 use a file-based (SQLite) database 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 and 2008). This document describes setting up Microsoft SQL Server 2008 Express for a new project. It also describes how to configure database link files (DLK and DCF). If you have an existing project that you need to convert, see the whitepaper Migrating a Project to SQL Server. Install SQL Server 2008 Express Enable SQL Server for New Projects Verify the Creation of an SQL Server Project Database Configure AutoCAD Plant 3D Using a Database Link File Configure SQL Server to Allow Remote Connections Copy a Project to a New Location
Install SQL Server 2008 Express
To download and install SQL Server Express, please refer to the Microsoft web page here: SQL Server 2008 Express. Select an installation option that includes the Runtime. Windows Integrated Security is used by default for SQLServer projects.
Enable SQL Express for New Projects
Set the default database for a new project to SQL Server Express using plantconfiguredatabase. 1. Load AutoCAD Plant 3D or AutoCAD P&ID and close the current project. 2. At the Command prompt, enter: PLANTCONFIGUREDATABASE 3. At the Specify Database type [Default/SQLServer] <Default>: prompt, enter: SQLServer 4. The Command prompt displays Use a configuration file? [Yes/No] <No>. If you want to use a configuration (.dlk) file, enter Yes and enter the configuration file name. Configuration files are described in the Configure a Database Link File in the following section of this document.
[Link]
Creating a Project that uses SQL Server
If you are not using a configuration file, enter No. In general, you will not use a configuration the first time you run plantconfiguredatabase because the default SQLServer settings create a configuration file (.dlk) that you can use as a template. 5. The Command prompt displays Specify SQL Server and instance name in the format Server\Instance 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: servername\instancename where the servername can be a period if it is the same machine that project files are hosted on. For example, DBSERVER\SQLEXPRESS, .\MSSQLSERVER, or DBSERVER\PID. You are now configured to use SQL Server with a new Plant 3D project. 6. Create a new Project. Important: 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 Converting a Project to SQL Server Whitepaper.
Verify the Creation of an SQL Server Project Database
After creating a new project with SQL Server enabled, you can list the project database names. 1. Open the Windows Command prompt. 2. At the Command prompt, enter: cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn 3. At the C:\Program Files\Microsoft SQL Server\100\Tools\Binn> prompt, enter: osql -E -S .\SQLEXPRESS Note: the osql options are case sensitive. 4. At the 1> prompt, enter: sp_databases 5. At the 2> prompt, enter: go A list of databases displays. Project databases are created for the project and begin with the name computername_projectfolder_ProcessPower. Underscores replace spaces, colons, and slashes in the project folder. For example: Computername_C__Projects_ProjectName_ProcessPower Computername_C__Projects_ProjectName_ProcessPowerIso
[Link]/autocadplant3d
Creating a Project that uses SQL Server
Computername_C__Projects_ProjectName_ProcessPowerOrtho Computername_C__Projects_ProjectName_ProcessPowerPiping
You can also use Microsoft SQL Server Management Studio Express to view the database names.
Configure a Database Link File
You can use a database link configuration file (.dlk) rather than setting server and instance values at the command prompt. If you want to specify a database name that is different from the default name you must use a configuration file. 1. In Notepad, open [Link] to use as a template. This file is in the project directory of a project that uses SQL Server. 2. Save the file as [Link] into the program directory. This is the same directory where [Link] is located. 3. Enter your database server and instance name in the Data Source value. This value is highlighted below in the .dlk sample as SERVERNAME\SQLEXPRESS. 4. Enter the database name in the Initial Catalog value. This value is highlighted below in the .dlk sample as DATABASENAME. This value must be unique for every project. 5. Save [Link] Example [Link]:
<?xml version="1.0" encoding="utf-8"?> <DatabaseLink xmlns:xsi="[Link] xmlns:xsd="[Link] <Parameter> <Name>DatabaseEngine</Name> <Value xsi:type="xsd:string">[Link]</Value> </Parameter> <Parameter> <Name>Data Source</Name> <Value xsi:type="xsd:string">SERVERNAME\SQLEXPRESS</Value> </Parameter> <Parameter> <Name>Integrated Security</Name> <Value xsi:type="xsd:boolean">true</Value> </Parameter> <Parameter> <Name>Initial Catalog</Name> <Value xsi:type="xsd:string">DATABASENAME</Value> </Parameter> </DatabaseLink>
You can add additional connection parameters (for example: AttachDBFilename). Elements Name and Value are used to form an SQL Server Native Client connection string. For more information, see ConnectionString Property. For example, if you want to use SQL Server User ID and Password, you can replace:
<Parameter> <Name>Integrated Security</Name> <Value xsi:type="xsd:boolean">true</Value> </Parameter>
With:
[Link]/autocadplant3d
Creating a Project that uses SQL Server
<Parameter> <Name>Integrated Security</Name> <Value xsi:type="xsd:boolean">false</Value> </Parameter> <Parameter> <Name>User ID</Name> <Value xsi:type="xsd:string">user_name</Value> <!-- all users of the project must share the same user id and password --> </Parameter> <Parameter> <Name>Password</Name> <Value xsi:type="xsd:string">password</Value> </Parameter>
Configure SQL Server 2008 Express to Allow Remote Connections
To allow remote users to load a project you must share the project folder, and allow remote connections to the SQL Server. By default, SQL Server 2008 Express is not setup to allow remote connections. To enable remote connections, follow the instructions in the Microsoft Knowledge Base article: How to configure SQL Server 2005 to allow remote connections. This article also applies to SQL Server 2008, except that you use the SQL Server Configuration Manager to enable the TCP/IP protocol for the server, and to enable the SQL Server Browser. This procedure needs to be performed on the server machine only.
Copy a Project to a New Location
For a project that uses SQL Server, the files [Link], [Link], [Link], and [Link] are XML files that contain database connection information for the project. If you copy the project to another computer, you will need to copy the SQL Server databases, and modify each project DCF file. Copy the three project databases. For information on how to copy a database, see Microsofts Knowledge Base: How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server. To modify the DCF files: 1. In Notepad, open [Link] in the project folder. 2. Enter the new database server host in the Data Source value. This value is highlighted below in the .dcf sample as SERVERNAME. 3. Enter the new database name in the Initial Catalog value. This value is highlighted below in the .dcf sample as COMPUTERNAME_PROJECTFOLDER. 4. Repeat for additional DCF files (for example: [Link], [Link], and [Link]).
Example [Link]:
<?xml version="1.0" encoding="utf-8"?> <DatabaseLink xmlns:xsi="[Link] xmlns:xsd="[Link] <Parameter> <Name>DatabaseEngine</Name> <Value xsi:type="xsd:string">[Link]</Value> </Parameter> <Parameter> <Name>Data Source</Name> <Value xsi:type="xsd:string">SERVERNAME\SQLEXPRESS</Value>
[Link]/autocadplant3d
Creating a Project that uses SQL Server
</Parameter> <Parameter> <Name>Integrated Security</Name> <Value xsi:type="xsd:boolean">true</Value> </Parameter> <Parameter> <Name>Initial Catalog</Name> <Value xsi:type="xsd:string">COMPUTERNAME_PROJECTFOLDER_ProcessPower</Value> </Parameter> </DatabaseLink>
Autodesk, Inc. 111 McInnis Parkway San Rafael, CA 94903 USA Autodesk [and other products] are either registered trademarks or trademarks of Autodesk, Inc., in the USA and other countries. All other brand names, product names, or trademarks belong to their respective holders. 2010 Autodesk, Inc. All rights reserved.
[Link]/autocadplant3d