SQL Server 2005 Express Edition - Part 8 - XCopy Deployment
October 12, 2007
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
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:
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.