
May 27, 2005 • Vol.27 Issue 21
Page(s) 22 in print issue
As promised, I want to talk about a rather interesting concept that Microsoft’s SQL Server team is introducing. Although this approach might never see the light of day (we still don’t have a firm ship date for SQL Server 2005), it needs to be addressed before it is cast in concreteif it’s not too late already.
Let’s lay some groundwork first. SQL Server Express Edition (SSEE) is to be the free version of SQL Server 2005. You can download it from the MSDN subscriber site or pick up a copy in Hong Kong next time you're out that way. When SQL Server 2005 goes live, it will be downloadable from everywhere. (I fully expect it to be stuck to the side of cereal boxes.) It's been totally redesigned: The governor has been pulled, the maximum database size has been doubled, and it includes most of the new features of SQL Server 2005 Workgroup Edition, another new SKU that sits between the Express Edition and the now more expensive Standard Edition. This means all of the T-SQL and CLR features being introduced in SQL Server 2005 will be included in the SSEE edition. This way it can provide a “real” database alternative to the JET engine used (and abused) by Access developers and lightweight client applications.
Creating A Unique DBMS Platform
One of the issues Microsoft had to resolve was getting the engine installed on a system that might have multiple independent users, each of whom might need his own unique copy (instance) of the master and application database. It also needed a way to create a safe platform that would be less vulnerable to attacks from worms and viruses. Installation will always be an issue for Windows applications, as “ordinary” users don’t have rights to install or modify anything. (Just ask my wife who complains about her inability to “fix” her system.) Another problem was to create a system that permitted an application to install (attach) the user database when it was first used, and not have to worry about this again until it came time to uninstall the application. Then the system needs to be able to extricate the installed user database without the use of wire cutters or other destructive means.
Tuning SSEE With The ConnectionString
How is this done? Well, to start with, ADO.NET adds a couple of new keywords to the ConnectionString. The ConnectionString is the set of parameters passed to ADO that tells it how to find and access the target data source, as well as identify the user. It also contains settings for a number of options, including how long to wait for the connection to complete and, in this case, the name of the user database to attach. This keyword (Attach-DBFilename) points to a user .MDF file which contains the preconfigured and prepopulated SQL Server database. When the ADO Open method sees this keyword, it tells SQL Server to attach this file and install it as a new databaseat least the first time. Each subsequent time the application runs, SQL Server determines if this is the same file it attached the first time and simply opens the existing database. When you use the default AutoClose option, SQL Server automatically disconnects from the data file when the last user closes his connection. This permits an uninstall application to delete the file and not disturb SQL Server.
Another ConnectionString option is used to create an entirely separate instance of the server. By specifying “User Instance=True” along with the “AttachDBFilename=”, ADO (and SQL Server) make a copy of the master, model, tempdb, and user databases and write them to the user area on disk. This disk region is not visible to other (nonadministrator) users. The connection open method then fires up this unique instance and provides unrestricted access to the user database. This entirely new approach is hoped to protect both SQL Server’s databases and the user database from other users, as well as provide a way to permit the user to log in as system administrator without worrying that they might cause problems that would impact other users or other databases.
SSEE Can Replace MSDE, Too
Sure, it’s possible to install SSEE and configure it to have a single shared instance or multiple instances, some or all of which can be made visible or invisible to your intranet or to the Internet. By default, all of the switches and settings to make SSEE visible are disabled. You’ll have to jump through several hoops to make SSEE more like its bigger and more expensive cousins. Yes, you can use SSEE as a Web host or in a typical client/server rig and expect it to handle dozens to hundreds of users. But as you can see, SSEE is, at least initially, configured to be a reliable, fast, and foolproof single-user DBMS engine to replace MSDE (its immediate predecessor) and JET. That said, keep in mind that Microsoft is likely working on another lighter DBMS for the Windows client desktop. SQL Server CE comes to mind . . .