SQL Server 2005 Express Edition - Part 9 - Managing User Instances

Monday Oct 22nd 2007 by Marcin Policht

SQL Server 2005 Express Edition's unique functionality allows you to distribute and implement single-user databases, without the dependency of having administrative privileges or the need for a cumbersome configuration. This article illustrates the practical use of this feature, known as XCopy deployment.

In the previous installment of our series dedicated to SQL Server 2005 Express Edition, we have described its unique functionality that allows you to distribute and implement single-user databases (typically with applications they support) without the dependency of having administrative privileges or the need for a cumbersome configuration. As we explained, this feature, known as XCopy deployment, combines benefits provided by the User Instance methodology and innovative mechanism that facilitates automatic database connectivity. In this article, we will look into an example illustrating the practical use of these concepts.

As we mentioned earlier, you have an option of enabling user instances during the initial setup (on the Configuration Options page of the installation wizard). In case you decided to leave it turned off (to verify the status of this setting, examine outcome of sp_configure 'user instances enabled' stored procedure), you can make it available once the database engine is running by executing the following from the Query Editor of SQL Server Management Studio Express. (In a similar manner, it is possible to control state of the 'user instance timeout' advanced configuration option, which value, by default, is inherited from its parent):

EXEC sp_configure 'user instances enabled','1' 

While user instances are typically employed as part of application development and deployment strategy (involving the use of Visual Studio 2005 or Visual Basic 2005 Express Edition to create .NET-based code that, once invoked, establishes a database connection string with User Instance and AttachDBFilename parameters set to appropriate values), there is a simpler way to demonstrate this behavior, which leverages SQL Server Express Utility, available from the Download section of the Microsoft Web site. The small (about 139kB) executable SSUtilSetup.exe you will find there contains two files (SSEUtil.exe and ReadMe.htm) that are extracted to an arbitrarily designated folder. The utility, developed specifically for managing SQL Server 2005 Express Edition, provides a number of advantages over SQL Server Management Studio Express, in particular when dealing with user instances. It can operate in either console or graphical interface mode (controlled via -console and -consolewnd switches, respectively), with the latter taking the form of SSEUtil Interactive Command Window (divided into two panes, with upper accepting your commands and lower displaying their output).

To begin our exercise, invoke the SQL Server Express Utility in either the command or console mode and connect to an instance of SQL Server 2005 Express Edition running on your local system. This can be accomplished by typing one of the following from the Command Prompt:

SSEUtil -s .\instance_name -console
SSEUtil -s .\instance_name -consolewnd

where instance_name is the name of the target instance (by default set to SQLEXPRESS). This initiates an ADO.NET SQL Client-based connection request to the target database engine, which (with its User Instance parameter set to True) results in the creation of a user instance (with the target instance as its parent). This can be verified in a number of different ways. The quickest one involves launching the Windows Task Manager and reviewing the listing of "sqlservr.exe" entries in the Image Name column on the Processes tab. You should notice at least two of them, one with the name of the SQL Server 2005 Express Edition database engine service account in the User Name column, the other running in the security context of the interactively logged on user (the one that was used to execute the SSEUtil.exe). As we explained in our previous article, the latter is an example of a user instance that operates in a manner resembling an application, rather than a service. Another easily discoverable change is the creation of copies of system databases and their log files (as well as the SQL Server error log) in the Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\instance_name folder within the profile of the current user (which provides necessary database engine support for the user instance).

You have an option of gathering useful information about existing user instances by taking advantage of a number of features offered by the SSEUtil program. For example, to enumerate instances, apply the -childlist parameter to the command line, which will not only display their GUID-based names but also provide you with the value of the local named pipe for each. You can subsequently connect to an arbitrary local user instance via standard administrative tools, such as SQLCMD or SQL Server Management Studio Express (ensure that you use Windows Authentication, since, as you might recall from our previous article, SQL Server authentication is not supported in this scenario), by entering this value into the Server Name textbox in the Registered Servers or Object Explorer window. (Keep in mind, however, that SQL Server Management Studio Express will allow you to only connect to running instances, while with SSEUtil, you can also activate them if they are dormant). In addition, the output generated by applying -childlist parameter also includes the process id associated with the instance (which you can obtain by adding the PID column to the listing on the Processes tab in Windows Task Manager) and its status (alive, in this particular case - and dead if the instance is not active). Information that is even more complete can be derived by querying dynamic management view sys.dm_os_child_instances, intended specifically for named instance support. This will provide you with the values of owning_principal_name (equivalent to a Windows account of the user who launched the instance), owning_principal_sid (Windows Security Identifier, uniquely distinguishing this user account within its domain or local SAM database), owning_principal_sid_binary (binary representation of Windows Security Identifier), already discussed instance_name, instance_pipe_name, and OS_process_id, as well as OS_process_creation_date and heart_beat (equivalent to status information).

Since connecting to individual user instances using standard management utilities requires knowledge of their named pipes (represented by a long string of characters, including their 16-character long GUID), it tends to be rather cumbersome. With SSEUtil, this process is simplified thanks to the -child parameter, which automatically connects to (and activates, if necessary) a user instance created by the currently logged-on account (this default behavior can be modified by adding an arbitrary user name). Furthermore, in case you have multiple parent instances on the local system, you can designate the one whose children you are interested in by employing the -s switch (note that SSEUtil will always attempt connecting to a user instance for a given parent, unless you include the -m switch as one of its parameters). For example, in order to connect via the command console to a child instance of which you are the owner, spawn by SQLEXPRESS_A parent instance, you would execute the following:

SSEUtil -child -console -s .\SQLEXPRESS_A

Once you are connected to a user instance via either SSEUtil, SQL Server Management Studio Express, or SQLCMD, the manner in which you manage its behavior and properties is the same as in traditional scenarios. This allows you, for example, to alter default configuration options (which are not available via SQL Server 2005 Surface Area Configuration, which does not support user instances) using a combination of T-SQL and stored procedures. You can also administer its databases in a familiar fashion, with either Query Editor or the graphical interface of Object Explorer. As we explained, they can be created, attached or detached without dependency on the current user having elevated privileges to the parent instance of SQL Server 2005 Express Edition. All of these actions can be handled with SSEUtil, by running the following from the command prompt:

SSEUtil -create C:\SourceDB\SampleDB.MDF
SSEUtil -s .\SQLEXPRESS_A -attach C:\SourceDB\SampleDB.MDF
SSEUtil -s .\SQLEXPRESS_A -detach C:\SourceDB\SampleDB.MDF

Even though, by default, the name of the attached database is derived from the full path of its MDF file, you have the ability to assign an arbitrary name, by specifying it as the second argument of the -attach switch. Note also that creating a database in the manner illustrated above does not make it automatically available as part of an existing user instance (it still needs to be attached). Once a database is attached, you can view and modify it via SQL Server Management Studio Express (providing that you established a connection to the database engine via local named pipes protocol, as described earlier). To terminate a user instance, invoke the SHUTDOWN command from within existing T-SQL session.

In the next article of our series, we will explore additional characteristics of XCopy deployment that rely on Visual Studio-specific features, such as, for example, "One-Click Install" methodology.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site