SQL Express for AutoCAD Plant 3D (Part 1)

This entry is part 1 of 6 in the series AutoCAD Plant 3D on SQL Express

Today I’m going to do a series of posts on migrating to SQL Express.  Future posts will cover installation and migration details, but today I wanted to lay some of the ground work for why you would need to move to SQL Express and some of the general implementation details.

 

Database, what database?

Behind the scenes Plant 3D/AutoCAD P&ID run on a file-based database called SQLite. While SQLite is extremely fast and small (it’s used in many smart phone applications), it does not handle multi-user access well. The rule of thumb we are going by is that when you have 6-10 people accessing the same project, you should look at moving to SQL Server or SQL Server Express.

SQL Server Express?

SQL Server Express, also called SQL Express, is a scaled down version of SQL Server, an enterprise database offering from Microsoft.

Check out this links to compare SQL Server editions:

http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

SQL Express limits database sizes to 10 GB vs 524 PB (http://en.wikipedia.org/wiki/Petabyte).

SQL Express doesn’t have some of the advanced management tools (I don’t know what most of those do).

SQL Express can’t mirror databases or do backup compression.

In a future post, I will show how to schedule backups to occur automatically and how to maintain only the last X number days of backups.

Why switch?

The issue is never file size (SQLite should be able to run up to 2 TB of data – http://www.sqlite.org/whentouse.html). The problem is more of an issue with record locking. Plant3D doesn’t manage locking out who has permission to write to the database. Rather, the program depends upon the extremely fast read/write capabilities of SQLite.

When you have a bunch of users running the same project, your chances of people trying to write to the database at the same time increase.

Typically the types of errors you may see will be data not be being save or something like that. Sorry I can’t be more precise as we are still trying to find a way to document criteria for moving to SQL Server Express.  My goal is to make switching to SQL Express so easy (and documented), that companies start using that ASAP.

Here is a list of some of the main benefits of using SQL Server Express:

  • Robust, multi-user environment.
  • Can easily automate project database backups.
  • FREE
  • Secure
  • Single source for project data.
  • Moves the data from out of the file structure.

 

Security

As with all data, security should be a primary concern.  SQL Express provides two forms of authentication, SQL Authentication and Windows Authentication.

The article below covers details on advantages of Windows Authentication versus SQL Authentication.

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

From what I have come across on the web, Window Authentication is preferred for security purposes.  SQL Authentication appears to be easier to hack into since once you locate the server you can start trying to guess passwords.

SQL authentication is easier to setup because you don’t have to worry about where SQL Express is installed or about setting up the network domain.

Single Source for Project Data

Some companies find a need to connect their plant data to other applications. Because all of project information being in one spot, the programs can access the data easier.

Data out of Files

When Plant 3D uses a SQL Server Express database, the files that used to store database information hold the connection information.  One side effect of this is that document management solutions can be more effective.  Document management solutions can’t really handle databases, so SQL Express is ideal for combining multi-office solutions like Plant 3D and Vault.

Series NavigationInstalling SQL Server Express
  • Pingback: Installing SQL Server Express | Process Design, from the Outside()

  • jdplant3d

    Some questions have come up regarding if this is a “one-way” operation when migrating a project to SQL Express. It is, in the fact that you cannot convert a project back to SQLite after migrating.

    However, there is still the option of creating a new project from your existing SQL project. In 2012 there is an option to select your database type in the New Project Setup Wizard, which allows you to create a copy of your SQL project, but instead using SQLite again.