SQL Server 2005 Express Edition – Part 8 – XCopy Deployment

In the previous installments of our series dedicated to SQL Server 2005
Express Edition, we have documented its installation and initial configuration
process, as well as described some of its basic features. Based on the
information we have presented so far, you might be tempted to conclude that
this edition is simply a scaled down rendition of its full-fledged counterparts
(such as Standard or Enterprise). However, some of its unique characteristics
make it a superior choice under certain circumstances, demonstrating its
relevance in the SQL Server 2005 product line. One of the most prominent
examples in this category is support for XCopy deployment, facilitating
distribution of single-user databases. In our article, we will provide an
overview of User Instance model (also known as “Run As Normal User” or simply
RANU) and automatic database connectivity, which serve as two primary enablers
of this methodology.

XCopy deployment accommodates the requirement for a portable, local storage
for Visual Studio .NET, single-user applications, which is one of the more
common implementation scenarios involving SQL Server 2005 Express Edition. Its
main goal is to simplify transfer of databases by treating them as regular
files, which can be therefore copied or moved using standard file system
management utilities or delivered to intended recipients as standard e-mail
attachments. (It is also possible to use Visual Studio ClickOnce methodology,
which we will cover in more details later) for this purpose, without the need
for installation or configuration actions that would depend on having
administrative privileges.

Note that transferring a database between two installations of Microsoft SQL
Server 2000 (including its Desktop Edition) as well as any full-fledged SQL
Server 2005 edition, is typically done by detaching it from the source instance
and attaching it to a target. This process involves activities which not only
are relatively cumbersome but also require an elevated level of privileges
(with the database owner initiating attach or detach procedures and a member of
System Admin role creating logins associated with database user accounts and
granting appropriate level of privileges to each). The concept of User
Instances combined with enhanced database auto-attaching and auto-closing
features (leveraging existing capabilities of SQL Client managed ADO.NET
provider) introduced in SQL Server 2005 Express Edition were designed to address
these issues.

SQL Server 2005 Express Edition instances can operate in one of two modes. The
first mode, which we have been dealing with so far, is based on the database
engine running as a service, in the security context of an account designated
at the installation time (set by default to built-in Network Service account)
and modifiable either with SQL Server Configuration Manager or using methods
described in the Microsoft Knowledge Base article 283811 (this is the same mode
available in other editions of SQL Server 2005 as well as in earlier versions
of the product). With this traditional approach, the majority of the features
we have presented so far are fully supported, including multi-user local and
remote access (controlled via a number of configuration options, such as
allowed network protocols or firewall settings), flexible authentication
(allowing a choice between Windows and internal SQL logins) and authorization
(granular, independently configurable model with server and database-level
roles as well as with server logins mapping to database users), offering robust
security model and a variety of graphical and command-line administrative
utilities. The second mode, with the database engine running as a user instance,
is specific to SQL Server 2005 Express Edition and operates in a considerably
different manner.

User instances take the form of child processes (in essence, functioning
like a regular user application) of a standard (described above) instance of
SQL Server 2005 Express Edition (known as the parent instance), generated on
demand and running in the security context of a user that initiated their
creation by submitting connection requests to the parent instance via ADO.NET
SQL Client. They are terminated automatically after a period of inactivity
(which is configurable). Each user instance has its own replicas of system
databases (master, tempdb, and msdb), which are copied from the Template Data subfolder (located under
the Program FilesMicrosoft SQL ServerMSSQL.n
folder, where n is a
unique integer counter, assigned to each newly installed instance) to Documents and Settings%username%Local
SettingsApplication DataMicrosoftMicrosoft SQL Server Data<parent_instance_name>
,
however, it shares executables and SQL Server 2005-specific registry keys with
the parent instance (unlike traditional multi-instance installations, which
create their own, dedicated copies of both data and program files). Still, each
of them operates independently of others, with their creator (along with
members of the local Administrators group) holding System Admin role.
Authentication mechanism is limited strictly to Windows accounts (SQL
authentication is automatically disabled). Instance and database privileges are
determined based on the file system permissions. In order to create their own
instances, users need to be able to read from and write to folders where target
database and log files reside. (A log file is typically not included in XCopy
deployment, but instead, is auto-created in the same folder as the
corresponding database file). Users also need to have Full Control permissions
to the files themselves (which grants them System Admin privileges on the
instance level). Obviously, employing this mechanism in protecting database
content requires that underlying volumes be formatted with NTFS. Security is
further enhanced by restricting database access solely to the user who created
the instance (which implies that two instances cannot simultaneously attach the
same database) and by disabling remote access via TCP/IP or Named Pipes
(allowing only local Named Pipes). However, by default, this still allows (as
intended), any member of built in local Users group (with sufficient
permissions to a database file and a folder it resides in) to access SQL Server
2005 Express Edition executables, connect to the parent instance, and initiate
a dedicated user instance.

Each user instance is capable of utilizing a single CPU and up to 1 GB of
buffer memory, independently of processors and memory being used by other
instances, including the primary (non-user) instance of the database engine
(with up to 16 instances on the same computer), which is sufficient for the majority
of single-user applications. Improved security is accomplished through lowering
the level of privileges required to load new databases (so users do not need to
resort to operating with administrative privileges). Software maintenance
overhead is not increased, since user instances leverage executables of their
parent instance and do not need to be separately patched. On the other hand,
though, you need to be aware of their limitations. For example, as we mentioned
earlier, they are applicable to single-user, local, Windows
authentication-based scenarios only and are lacking some of the features
available in traditional SQL Server 2005 Express Edition databases, such as
replication, Full Text Search, or SQL Server Service Broker. Some of the
standard management utilities (such as Surface Area Configuration) are not user
instance-aware; others (such as SQL Server Management Studio Express) make
their administration cumbersome since they require knowledge of their
GUID-based names during initial connection. Furthermore, you might experience
some undesired delays in their response time as they are loaded on demand,
after being offloaded following periods of inactivity, .

While the User Instance model eliminates the need for elevated privileges
when working with single-user, SQL Server 2005 Express Edition-based databases,
automation of tasks associated with attaching and detaching their files (as
well as terminating hosting the instances) necessary for streamlining XCopy
deployments has been accomplished by leveraging the following features:

  • AttachDBFilename – an option in the ADO.NET SQL Client connection
    entry, which indicates that the target parent instance (specified as the value
    of connectionString option) should attempt loading the database specified as
    its value. The value contains a path to the database (in absolute or relative
    format) as well as its file (mdf) name. If successful, this leads to the creation
    of a user instance (with the target database loaded) in the security context of
    the user who invoked the SQL Client (assuming that the User Instance option in
    the connection string was set to True) and establishment of a database
    connection.
  • Autonaming of database and log files – SQL Server 2005 Express
    Edition automatically generates a unique logical name for each loaded database.
    This name matches a full file system path to the database file, as long as its
    length does not exceed 127 characters, (otherwise a string representing the
    path is truncated and prefixed with characters derived from its hash, in order
    to guarantee uniqueness). Log file name is created by appending "_log.ldf"
    character string to the name of the database (.mdf) file.
  • AutoClose – automatically removes a handle on the .mdf file in the
    absence of active connections (typically after a 10-minute period). While this
    feature is available in other editions of SQL Server 2005 (as well as the
    previous product release), only SQL Server 2005 Express Edition has it enabled
    by default. While this lowers resource utilization and allows the database file
    to be used by another instance or copied, these benefits come at the price of
    increased response time caused by reopening the database during subsequent
    connections. In addition, keep in mind that even after the database is closed,
    the user instance remains operational for the period of time determined by the
    ‘user instance timeout’ advanced configuration option (set by default to 60
    minutes).

In our next article, we will take a closer look at actual implementation of
the XCopy deployment and User Instances, as well as explore their management
and configuration characteristics.

»


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