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 Template Data subfolder (located under the Program Files\Microsoft SQL Server\MSSQL.n folder, where n is a unique integer counter, assigned to each newly installed instance) to Documents and Settings\%username%\Local Settings\Application Data\Microsoft\Microsoft 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