![Page 1: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/1.jpg)
PD4675 – Making the Move from SQLite to Microsoft SQL ServerJason DrewProduct Support Specialist, Autodesk, Inc.
Dave TynerSr. CEA Analyst, The Shaw Group, Inc.
![Page 2: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/2.jpg)
© 2011 Autodesk
Class Summary
Making the move from SQLite to Microsoft SQL Server®
This class covers migrating existing projects and configuring new projects to use Microsoft SQL Server with AutoCAD Plant 3D or AutoCAD P&ID for improvedperformance and reliability in multi-user network environments. The content is applicable to:
AutoCAD Plant 3D 2011AutoCAD Plant 3D 2012AutoCAD P&ID 2011AutoCAD P&ID 2012
![Page 3: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/3.jpg)
© 2011 Autodesk
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
![Page 4: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/4.jpg)
© 2011 Autodesk
Agenda
1 5:00 – 5:05 Intro
2 5:05 – 5:15 Overview of Plant 3D Databases
3 5:15 – 5:30 Installing Microsoft SQL Server
4 5:30 – 5:50 Creating/Migrating Projects
5 5:50 – 6:10 Moving & Copying Projects
6 6:10 – 6:20 SQL Queries and ODBC Links
7 6:20 – 6:30 Q&A
![Page 5: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/5.jpg)
© 2011 Autodesk
Overview of AutoCAD Plant 3D Databases
![Page 6: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/6.jpg)
© 2011 Autodesk
Database Structure
Project information is stored in both the drawings and databases Changes are committed to drawings on save SQLite databases are DCF files:
ProcessPower.dcf – P&ID database Piping.dcf – 3D piping database Iso.dcf – Isometric database Ortho.dcf – Orthographic database
![Page 7: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/7.jpg)
© 2011 Autodesk
Database Engines
File based Self-contained (serverless) Database locking
Server based Client/Server Row locking
![Page 8: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/8.jpg)
© 2011 Autodesk 8
Database Engines
SQLite File based database
MS SQL Server based database
![Page 9: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/9.jpg)
© 2011 Autodesk
Installing Microsoft SQL Server
![Page 10: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/10.jpg)
© 2011 Autodesk
System Requirements for SQL Server
Supported versions of SQL Server: 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)
Supported Hardware and OS: Minimum 1.4GHz CPU (2.0GHz or greater recommended) Minimum 512MB of RAM (1.0GB or more recommended) Windows Server 2003 or 2008 Windows 7, Vista Windows XP (32-bit only)
![Page 11: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/11.jpg)
© 2011 Autodesk
SQL Server InstallationInstance configuration
Download SQL Server Express (free from Microsoft) Install new default instance (MSSQLSERVER) Install new instance for Plant 3D
![Page 12: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/12.jpg)
© 2011 Autodesk
SQL Server InstallationDatabase Engine Configuration
Select Authentication Mode Specify SQL Server administrators Set data directories
![Page 13: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/13.jpg)
© 2011 Autodesk
SQL Server InstallationConfigure Remote Connections
Launch SQL Server Configuration Manager Select protocols for new Plant 3D instance Enable TCP/IP
![Page 14: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/14.jpg)
© 2011 Autodesk
Creating & Migrating Projects to SQL
![Page 15: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/15.jpg)
© 2011 Autodesk
Creating a new Plant 3D 2011 project on SQL
Switch database mode from SQLite to SQL Server PLANTCONFIGUREDATABASE
New projects will now be setup with SQL databases Existing SQL projects can be opened Use Project Setup Wizard to create new project on SQL
![Page 16: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/16.jpg)
© 2011 Autodesk
Creating a new Plant 3D 2012 project on SQL
No need to switch database modes Create new project and select SQL Express server database Enter SQL Server info That’s it!
![Page 17: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/17.jpg)
© 2011 Autodesk
Migrate Existing Plant 3D 2011 project to SQL
Back up the project Run PnPSQLServerConverter.exe Specify existing project XML SQL server name/instance Enter database name prefix Project DCFs will be migrated and used as XML
configuration files
![Page 18: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/18.jpg)
© 2011 Autodesk
Migrate Existing Plant 3D 2012 project to SQL
Back up the project Run Project Maintenance Utility Select Convert a project to SQL Express Specify existing project XML Specify SQL Server & instance name Specify authentication mode Project DCFs will be migrated and used as XML
configuration files
![Page 19: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/19.jpg)
© 2011 Autodesk
Moving & Copying Projects
![Page 20: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/20.jpg)
© 2011 Autodesk
Moving and copying SQL Projects
New projects can still be created from existing projects using Project Setup Wizard
Moving and copying is a manual procedure in Plant/P&ID 2011
Use Project Maintenance Utility in 2012 Back up the project first
![Page 21: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/21.jpg)
© 2011 Autodesk
Moving and Copying Plant 3D 2011 Projects
Project .DCF files (XML format) define the SQL server configuration Moving only project files – no change needed in XMLs Copying a project – copy project files then copy SQL databases Update project XML configuration to point to new server
![Page 22: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/22.jpg)
© 2011 Autodesk
Moving and Copying Plant 3D 2012 Projects
No manual changes required to .DCF XML files Use Project Maintenance Utility (PnPProjectMaintenance.exe) Move Project Database – only moves SQL databases, not the project files Copy Project Database – only copies SQL databases, not the project files
![Page 23: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/23.jpg)
© 2011 Autodesk
SQL Queries & ODBC Links
![Page 24: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/24.jpg)
© 2011 Autodesk
Connection to SQL using MS Excel
Custom Reports through data link
Launch MS Excel Get External Data from SQL Server Enter SQL Server information Select database and table to import
![Page 25: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/25.jpg)
© 2011 Autodesk
Connection to SQL using MS Excel
Imports “live” project Data into MS Excel Data import is read only (one way link) Use Refresh to update with latest data
![Page 26: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/26.jpg)
© 2011 Autodesk
MS Query using MS Excel
Configure user DSN in ODBC Data Sources Select tables and columns to include Setup filters and sort order Data from the query can be sent to MS Excel or viewed directly in
MS Query
![Page 27: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/27.jpg)
© 2011 Autodesk
Tips & Tricks
Database Backup Routine
After migrating projects to SQL Server keep in mind that the databases are stored on the SQL server while the project files are stored in a separate location. Make sure to update your backup routine accordingly
![Page 28: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/28.jpg)
© 2011 Autodesk
Tips & Tricks
Project Maintenance Routine
To ensure database consistency run project audit periodically Verifies the data stored in the database against the data stored
in the drawings Auditing the project will remove tag numbers that are used in
the database but not in a drawing
![Page 29: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/29.jpg)
© 2011 Autodesk
Tips & Tricks
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?
Workaround - create a new project from an existing template project then copy drawings into the SQL Server project
![Page 30: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/30.jpg)
© 2011 Autodesk
Tips & Tricks
Network Performance
One of the best ways to increase performance of AutoCAD Plant 3D and AutoCAD P&ID is to use a gigabit 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
![Page 31: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/31.jpg)
© 2011 Autodesk
Conclusion
![Page 32: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/32.jpg)
© 2011 Autodesk
What we Covered:
Overview of Plant 3D Databases Installing Microsoft SQL Server Creating new projects on SQL Server Migrating existing projects to SQL Server Moving & copying SQL Server project databases Using SQL queries and ODBC Links in MS Excel
![Page 33: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/33.jpg)
© 2011 Autodesk
Q&A
![Page 34: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/34.jpg)
© 2011 Autodesk
ResourcesAutodesk
AutoCAD Plant Exchange - www.autodesk.com/plantexchange Plant Learning Resources and videos Ask the Expert Content packs Discussion Forums
Plant Design Community Sitesdavetyner.com (Digital Plant Design Community) – www.davetyner.com/forum
pipingdesigners.com – www.pipingdesigners.com
Email - [email protected] Feedback – [email protected]
![Page 35: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/35.jpg)
© 2011 Autodesk
Autodesk University Session Feedback
Your feedback is very important to Autodesk.
You can complete the session survey on your mobile device, PC, or at a survey station.
Each completed session survey enters you in that day’s drawing for a free AU 2012 pass.
You can help make AU 2012 better!
Complete the AU Conference Survey at a survey station and receive an AU 2011 T-Shirt.
Thank youEnjoy the rest of AU and we look forward to seeing you next year!
![Page 36: PD4675 – Making the Move from SQLite to Microsoft SQL Server Jason Drew Product Support Specialist, Autodesk, Inc. Dave Tyner Sr. CEA Analyst, The Shaw](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d925503460f94a783c3/html5/thumbnails/36.jpg)
© 2011 Autodesk
Autodesk, AutoCAD P&ID, and AutoCAD Plant 3D are registered trademarks or trademarks of Autodesk, Inc., and/or its subsidiaries and/or affiliates in the USA and/or other countries. All other brand names, product names, or trademarks belong to their respective holders. Autodesk reserves the right to alter product and services offerings, and specifications and pricing at any time without notice, and is not responsible for typographical or graphical errors that may appear in this document. © 2011 Autodesk, Inc. All rights reserved.
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.