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:
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.
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.
- Single source for project data.
- Moves the data from out of the file structure.
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.
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.