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

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' 
GO
RECONFIGURE
GO

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
SettingsApplication DataMicrosoftMicrosoft SQL Server Datainstance_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:SourceDBSampleDB.MDF
SSEUtil -s .SQLEXPRESS_A -attach C:SourceDBSampleDB.MDF
SSEUtil -s .SQLEXPRESS_A -detach C:SourceDBSampleDB.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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles