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

• whether you need to start MSDE before running your application
• connecting to the MSDE instance on the target system
• installing the initial database
• setting up user accounts and permissions on your database for the users and the systems administrator (sa)
• shutting down MSDE when your application ends
• backing up the database and restoring it

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.

Starting the MSDE Engine
This article assumes that you've already installed MSDE on your system. An MSDE white paper available at http://www.betav.com/files/content/whitepapers.htm describes the installation process and the issues you'll encounter.

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.