Managing Your MSDE Database

Here's the scenario: You've developed an application that uses SQL Server. You're developing against the Microsoft SQL Server Data Engine (MSDE), the desktop version of SQL Server, on your own system, but your application runs on a target system that doesn't have MSDE installed and doesn't have access to SQL Server on the network. You realize that you have to install MSDE on the target system, so you do some research and cobble together a strategy to install MSDE on the target system. Great, but before you get started, you still need to think about a few things, such as:

I've written some sample code to illustrate how you can manage an MSDE installation through SQL Distributed Management Objects (SQL-DMO) and other, more traditional techniques. Let's look at some detailed explanations of the techniques I used and some suggestions about how to keep your application and users working long after the installation is complete. You can also use these techniques with non-MSDE SQL Server applications, so when you upgrade from MSDE, you won't have to make many changes in the routines you use to manage the server.

Related: MSDE and SQL Server

Starting the MSDE Engine

This article assumes that you've already installed MSDE on your system.

To access an existing installation of the MSDE version of SQL Server, you need to open an ADO connection (these techniques also work with other database management system—DBMS—interfaces such as Data Access Objects—DAO—RDO, ODBC, and OLE DB). When your code attempts to open a connection, make sure the connection error handler is programmed to deal with the possible errors.

However, before you try to open a connection through any data-access interface, you need to check at least once to see whether the MSDE engine is started. Also, you might want to check periodically to see whether it's still running. First, let's look at how to check whether the engine is running.

ADO doesn't provide a way to start SQL Server, so if you try to use the Connection.Open method before the engine is started, you'll discover that ADO can't connect. For example, the error message in Figure 1 says that either ADO couldn't find the server or it found the server, but SQL Server wouldn't let you connect for security reasons. This error message isn't particularly useful. If the message had a little more granularity (i.e., couldn't find the server returns one message and access denied returns another), you could more easily write an error handler to deal with problems.

Another factor in connecting is time—getting your application loaded, connected, and initialized can take 10 seconds or more. In these situations, my applications usually have a splash screen to give the user something to look at while the application gets started, but even then, users can get impatient by the time the error handler kicks in. The default ConnectionTimeout is 15 seconds (assuming the LAN is connected to your NIC). You might reduce this time to 5 seconds for an MSDE configuration, but that's still quite a while in today's fast-paced work environment. More to the point, you might never get your application running if startup takes 10 seconds but the process times out after 5 seconds.

So, you know that it's important to start your server or at least verify that it's running before using the ADO Open method. You can start MSDE by using SQL-DMO, an object library that installs automatically when you install MSDE or SQL Server. The SQL-DMO object model is top-heavy (i.e., it has considerable overhead you might never use), but it's a powerful tool for performing SQL Server administrative functions. You can use SQL-DMO to start (or restart), stop, and pause the server; back up, checkpoint, and restore the server; manage and install databases, users, and permissions; and much more.

Using SQL-DMO to test for a running SQL Server or MSDE is fairly easy. The example code in this article is extracted from a single-user MSDE application I wrote for my church. I'll use it to demonstrate the concepts I discuss. The code at callout A in Listing 1 declares the Microsoft SQLDMO object library before referencing the SQL-DMO objects. Before you can access the SQL-DMO DLL, you also have to register the SQLDMO library by using the Visual Basic (VB) Project, References menu option. This DLL is installed when you install any version of SQL Server on your development machine.

The code at callout B instantiates the SQL-DMO SQLServer object and calls SQL-DMO to start a named server or simply verify that it's started by using the SQLServer object's Start method. In this case, simply pass the server name—"(local)" or "." will do for your local MSDE—and a valid login ID/password pair to the Start method. I used sa with the appropriate password. On installation, the default sa password is NULL, so you need to change it after installing MSDE to prevent any unauthorized person from accessing your client's data. However, you don't need to use sa to start the server in this case—just a valid user ID/password pair.

If the Server.Start method finds the server, it might trip a trappable error—the rationale is unclear, but the error message you might encounter indicates that the server is already started. This result is akin to the noise you get when you turn the key on a running engine in your car. I didn't get this error on my Windows 98 system, but the documentation says it could trip an error on Windows XP, Windows 2000, or Windows NT—just be prepared to catch it.

Sometimes you have to give SQL Server a few seconds to get on its feet after you tell it to start. It's a good idea to follow the Server.Start method with a call to the Windows API Sleep to give the server 10 seconds or so to get up to speed before you put it in gear and pop the clutch (i.e., before you run your first query). Note that Sleep isn't a native VB function. To use it, you must add the following declaration to your project:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Is Your Database Installed?

The next step in the sample code determines whether the application's database is installed—it won't be until you take steps to install it. But you need to do this only once, assuming no one uninstalls MSDE or deletes your database. If your setup routine doesn't run a database install script, you can run the script after you determine that the database isn't installed.

The code at callout C in Listing 1 illustrates one technique to verify that your application database is installed on the target SQL Server—simply check for your database in the SQL-DMO Server object's Databases collection. In this case, the code checks to see whether the "VolunteerSQL" database is already installed. If it's there, you're done. If not, the code trips a trappable error and you can install the database.

Note that you can also use late binding to reference the Server.Databases collection—it might even be easier to code than using early binding, but in this case, you don't know whether a Database object exists for your named database. I illustrate the late-binding technique when I discuss restoring the database.

If the code didn't find a reference to your database in the Server.Databases collection, you need to install the database. Installation is easy if you use the sp_detach_db stored procedure to detach your MSDE database from your development SQL Server and save the .mdf database file on the installation CD-ROM. Clear this database of users before you detach it. Because the user IDs probably won't match up on the newly installed database, you might need to recreate the valid logins, users, and permissions with a script after you reattach.

Reattaching the amputated database file is also easy. SQL-DMO's Server object exposes the AttachDB method, which can reattach a database file in a single call. The tricky part here is how SQL-DMO handles "multi" strings, which define how SQL Server should deal with embedded spaces and pathnames containing special characters. (SQL Server Books Online—BOL—describes multi strings at length.) To format your path as a multi string, be sure to bracket the full path to your .mdf file in square brackets, as the code at callout D shows. This bracketing deals with embedded spaces in the path and other issues. Be ready to trap errors that this method generates. If the file isn't found or another problem arises, you'll get another trappable error.

When the database is reattached, but before you use it, you need to run a script to install the user IDs and passwords and set each user's permissions for each of the user tables, stored procedures, and views. Be sure to include permission for the selected user to back up the database, or create an admin account that can perform this maintenance function.

The easiest way to create a script file like this is to use Enterprise Manager. (Right-click your development database and choose All Tasks, Generate SQL Script. On the Options tab, which Figure 2 shows, select Script object-level permissions and Script SQL Server logins (Windows and SQL Server logins). Select Create one file and click OK.) But to run the script, I used osql. You could also execute this script from VB, but it's more difficult. Just remember that ADO doesn't recognize the GO operator that separates sections of the script. You'll have to break up the script to deal with these delimiters if you run the script from VB. Be sure to quote the application path because it might contain embedded spaces. After you start the osql script, you have to wait for it to finish because your subsequent operations depend on it. As before, using the Windows Sleep API to build in a slight delay is essential here.

In the next step, the application attempts to connect to the MSDE server by using the user ID and passwords you're installing. To give the server time to respond, have the application wait a few seconds. To code this short delay, call the Windows Sleep API to free the application's thread for n seconds (8 in this case), depending on how fast the batch runs on your system. Don't just loop within your application; that wastes CPU time that the server process needs.

When the routine reawakens, the code refreshes the Server.Databases collection and searches for the newly installed database. If the database still isn't in the list, the code waits for an additional 10 seconds (1 second at a time), then gives up, assuming that an error occurred in the script. You'll want to tune these timings for your own target database and system. If you're installing on a slower system or a background application is bogging it down, these operations might take longer.

Note that there isn't an easy mechanism to capture the errors that osql generates, except in the osql script itself. Use Query Analyzer to work out the bugs in your script before testing and running it on your target system.

The code at callout E shells out to osql to run the script. Note that this script file is in the application's path directory, as indicated by the app.path property. I framed the path with quotes so that the DOS-based osql utility could parse the input script filename. I run this script in a visible window to debug the script, but running it minimized would be better for the user. In any case, they'll only see this DOS window the first time the application runs.

The error handler traps a variety of errors that SQL-DMO and VB generate. Most users aren't equipped to deal with these errors. To help you figure out what went wrong, use an error log like the one that the code at callout F shows. The code calls the RecordError routine, which I wrote to simply dump error and status information to a diagnostic ASCII file.

An alternative to using SQL-DMO to start your system is to simply start SQL Server automatically by using the SQL Service Manager when the system boots. However, because your application is probably one of many that will be running on the user's system, starting SQL Server on the off chance that the application will need it that session doesn't make sense.

ADO doesn't have any properties that change state when the server crashes or becomes unavailable for other reasons, so you won't be able to trap an event if the server goes down. I've found that the simplest way to discover whether the connection is valid (and the engine is still running) is to execute a simple non-rowset­returning query or use adExecuteNoRecords with the Connection object's Execute method along with a simple SELECT. If the query succeeds, your server is up—or it was when the query executed. There's no guarantee that it will stay up a microsecond longer after the engine processes your query (which might have killed it). You need error-handling code to deal with the possibility that the server is down and needs restarting. Starting a server that's not running can only be done programmatically with SQL-DMO.

Shutting Down the MSDE Server

If you know your application is the only piece of code that's using the MSDE engine and you don't expect this to change, it makes sense to shut down MSDE when the user is finished. The SQL Server Agent can wake up SQL Server when it needs to perform an automated procedure such as backing up the server, then put it back to bed.

The code at callout G shuts down the server. Be sure to include On Error Resume Next to sidestep any error messages returned when the engine gets shut down. Of course, you'll want to make sure all operations are completed first.

I used the SQL-DMO Server.Shutdown method, which defaults to "wait"—meaning the server finishes any uncommitted transactions and performs an orderly shutdown. The application calls this routine in the Form.Unload event handler after testing a checked option that gives the program the option of disabling this shutdown.

Backing Up the Database

Any application that uses a DBMS needs to help users protect their data and recover from disasters. The backup strategy I describe here is only part of a more comprehensive backup regimen that includes periodically copying the backup file to an offsite device. SQL Server (MSDE) does a great job of protecting the data from corruption caused by unexpected crashes or power outages. However, it doesn't protect you from more catastrophic failures. Be sure to move the backup file to another (offsite) location to prevent a disaster from destroying the backup file along with the database.

You can use several techniques to back up an MSDE database. One of the easiest ways is to use Enterprise Manager wizards to set up a SQL Server Agent maintenance job. But if you don't want to set up a job, you can easily write the code yourself to back up the database. You can choose to have the user do the backup, or do it yourself without their knowledge.

My sample application lets users choose when to back up, but the application reminds them to back up if they haven't done so for a couple of days. Because this database doesn't have much data and changes are infrequent, skipping a couple of days shouldn't be a problem. The code at callout H shows the routine to back up the database. Note that I'm using an open ADO Connection cn to execute the backup action query. This code doesn't use SQL-DMO; it simply executes T-SQL's BACKUP DATABASE command with a few options to make sure the database backup file is initialized each time it's backed up. If you have a more complex or more heavily used database, you probably won't want to use the WITH INIT option but instead will let the BACKUP command append each backup to the existing backup set. That way, you can restore the database from any of the existing backups. The sample code also records the date of the last backup to the registry so that you can keep track of when to prompt for another backup.

When you set up your database script, be sure the user has rights to back up and restore the database. If you set up a special admin account, you'll have to use this account to open another connection before attempting the backup.

Restoring the Database

Saving the current state of the database to a file is only half (or less) of the backup/restore process: A backup is no good if you can't restore the data later. Unfortunately, the restore process is a little more complex. SQL Server requires that no users be connected to the database during the restore operation. In MSDE for SQL Server 7.0, you have to jump through the hoops I describe below to get the server stopped and restarted in single-user mode. In SQL Server 2000, switching to single-user mode isn't nearly as tough—you can simply execute an ALTER DATABASE command to change the database state. Callout I's ALTER DATABASE command, which my colleague Kimberly Tripp-Simonnet showed me, completes all transactions (or rolls them back) and disconnects all users from the database. This command tells the server to stop further processing on the server, stop any pending transactions, and roll back the operations if they don't end within 10 seconds. To execute this T-SQL command, you need to have sa or db-creator permissions or to log on as a member of the backup operators role.

Note that shutting down a live production server isn't a good idea unless you know it's a single-user DBMS. MSDE can run in multiuser mode, so if yours does, be sure to get all other users logged off when maintenance operations are taking place.

If the MSDE you're using is based on SQL Server 7.0, you need to switch the server to single-user mode. I've tried several techniques to accomplish this switch, but they don't always work consistently. Although you can shut down all ADO connections (including Enterprise Manager, Query Analyzer, and other tools), the OLE DB session pool keeps connections open for a minute or longer after you "close" them. To get around this problem, I had SQL-DMO shut down the server, wait a few seconds, then restart it. If you try to restart SQL Server too soon after shutting it down, it seems to stall.

After the server is shut down and you've waited a couple of seconds, you can attempt a restart. Again, you might want to adjust the timing for your target system. To execute the RESTORE command, you'll have to log on as sa, db-creator, or a member of the backup operators role. After restarting, you'll probably want to wait several seconds to let the server come up to speed. The routine at callout J restarts, waits, then executes the RestoreDatabase query. The error handler at callout K is ready to trap Server not found errors, then wait another couple of seconds and try again.

Next, the code at callout L sets the working database's SingleUser DBOption to True to prevent other connections from interfering with the restore operation. This setting is a requirement for SQL Server 7.0's RESTORE command. With your server in single-user mode, you can execute the RESTORE DATABASE command. In this case, you use the SQL-DMO Server.ExecuteImmediate method, which simply executes the action query that callout M shows—the T-SQL RESTORE DATABASE command.

If the restore succeeds, you can return the database to multiuser mode immediately. After closing the SQL-DMO Server object, you're ready to return to the application. Tell the user you're finished, then shut down the application to bring in the newly restored data. The code at callout N takes care of all these steps.

Packaging

When it's time to deploy your application, you'll need to include several additional pieces in your compressed cabinet format (CAB) deployment package that the Visual Studio Installer or the VB Package and Deployment Wizard created for you. The setup routine you created through the Package and Deployment Wizard needs to copy these files to your app.path directory. In addition, your code needs to extract these files out of this to-be-determined app.path directory. These files include

I expect most of the existing SQL-DMO functionality to appear in the new ADO.NET SqlClient provider before too long, so you won't have to use a separate data-access interface to manage your MSDE server. I also expect to see MSDE take an increasingly important role in the coming years as developers search for a more robust and better performing replacement for the Microsoft Access (Jet) database engine. But today, using SQL-DMO can help make your MSDE experience more productive.