Implementing Upgrade of SQL Server 2005 Express Edition

June 23, 2008

SQL Server 2005 Express Edition - Part 25

In the previous installment of our series dedicated to SQL Server 2005 Express Edition, we presented a variety of reasons that might influence your decision to upgrade to one of the full-fledged members of the SQL Server 2005 family (Workgroup, Standard, or Enterprise). In this article, we will discuss the actual implementation of this process, pointing out additional factors (such as conversion of User Instance databases or altering some of its restrictive default settings) that might affect its complexity.

In general, there are two approaches to upgrading SQL Server 2005 Express Edition. The first one involves an in-place procedure, which replaces an existing instance with another product. The second one preserves the current installation, resulting in both programs running concurrently side-by-side. The choice between them depends primarily on whether you intend to continue using the same instance name (which is frequently the case, especially when its databases are used by third party software, whose processing needs to grow beyond the limits supported by SQL Server 2005 Express Edition). However, your decision has other implications, determining, for example, whether migration of databases and custom server settings (such as logins, server roles, backup devices, linked servers, or replication subscriptions) takes place automatically or will have to be handled manually. It also affects the fallback strategy if you encounter issues with a newly installed product. While either approach is relatively straightforward, since the underlying database engine and common features are, for the most part, identical across editions, there are several caveats that might have to be taken into account.

The first one concerns naming conventions. By default, SQL Server 2005 Express Edition assigns the name SQLEXPRESS to its first installation (which is different from the behavior of its Workgroup, Standard, or Enterprise counterparts, where a typical setup yields a default, non-named instance). Named instance are also common when using third party installers, which deploy applications with embedded databases. Since this practice is followed rather widely (incidentally, Visual Basic 2005 Express Edition also assumes SQLEXPRESS name in its database projects targeting SQL Server 2005 Express Edition), in-place upgrades will likely result in named instances (and you will have to deal with a side-by-side installation if you want to avoid it).

Special arrangements are necessary when dealing with User Instances, since this feature is unique to SQL Server 2005 Express Edition. First, you need to ensure that their databases become part of the new product. This can be accomplished by attaching their MDF and LDF files (by using the Attach... option in the context sensitive menu of the Databases node in the Object Explorer window of SQL Server Management Studio or by running sp_attach_db stored procedure). This change needs to be reflected by modifications to applications that target these databases. More specifically, you have to alter their connection strings, by assigning False to the User Instance parameter (or removing it altogether) and replacing AttachDbFilename with Initial Catalog entry, setting it to the name of the newly attached database, which yields the following:

connectionString="Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=AdventureWorksLT_Data;Integrated Security=True"

Assuming that your application leverages Windows authentication to interact with the database AdventureWorksLT_Data hosted on SQLEXPRESS instance running on SERVERNAME server (for more information on User Instances, including a more detailed description of their creation and configuration procedures, refer to earlier articles of this series). These modifications can be applied via a direct edit of the app.config file or by using the Settings section of the project Properties window within the Microsoft Visual Studio interface. (Note that you will not be able to use Visual Basic 2005 Express Edition for this purpose, which restricts its data sources to Access or User Instances-based databases of SQL Server 2005 Express Edition). Finally, you should also adjust security settings (which might require creating server logins and corresponding database users, as well as granting appropriate permissions to objects such as tables, views, or stored procedures) to allow multi-user access (since the User Instance-based mechanism, providing full database access to anyone who was permitted to launch an application associated with it, does not function in combination with any of full fledged SQL Server 2005 products).

In order to perform an upgrade, launch the SQL Server 2005 setup program with the SKUUPGRADE parameter. This procedure is applicable to both unattended and interactive installation modes. In the case of the former, run the following at the Command Prompt from the directory where that setup.exe is located (assuming that you are upgrading SQL Server Database Engine and Client Components features, that the target instance name is named SQLEXPRESS, and that you want to be able to view dialog boxes and error messages displayed throughout the installation process):

setup.exe ADDLOCAL=SQL_Engine,Client_Components INSTANCENAME=SQLEXPRESS UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

If you include the SKUUPGRADE parameter on the command line when invoking the interactive installation (by running SETUP.EXE SKUUPGRADE=1), verify first that the Edition Change Check entry listed on the System Configuration Check page of the SQL Server 2005 Setup Wizard has a Success value in the Status column. While most of the process resembles a standard install, you will also be given an option to specify the upgrade target on the Instance Name page. Regardless of the mode of execution, the outcome should be an instance of the new product, with its system level settings and all user databases inherited from its predecessor.

Note that these steps need to be repeated for every local instance. In addition, keep in mind that adding SQL Server 2005 Management Studio to the list of components to be installed will effectively preclude the ability to launch its Express equivalent (running both of them side-by-side is not supported). However, considering that the former contains a superset of features of the latter, you should be able to use it for managing all local (as well as remote) database engine instances. Finally, do not forget that the procedure described above will leave your new installation at a patch level determined by the source media, so ensure that you run the Windows Update and apply all missing service packs and hotfixes following its completion.

Another factor to take into account during an upgrade is a unique network configuration associated with SQL Server 2005 Express Edition. Since its most common purpose is to provide database services to individual, interactively logged-on users (and their applications), by default, only local access is permitted (via shared memory). To change this setting (and allow named pipes or TCP/IP-based remote connections), use either SQL Server 2005 Surface Area Configuration before the installation of a full-fledged edition or SQL Server Configuration Manager (more specifically, its SQL Serve Network Configuration node) afterwards (alternatively, you can also take advantage of the DISABLENETWORKPROTOCOLS command line switch of the setup program). In addition, you might need to modify the firewall configuration to allow SQL Server 2005-related communication (for more information about this subject, refer to the Microsoft Knowledge Base articles 841251 and 914277).

Among other configuration settings inherited from the SQL Server 2005 Express Edition that you should consider changing are accounts used by the SQL Server services (including, depending on installed components, SQL Server, SQL Server Browser, SQL Server FullText Search, and SQL Serve Reporting Services), which default to NT AUTHORITY\NetworkService. This means that their network access privileges are established based on the security context of the computer account hosting the instance. In general, such configuration is not advised due to its security implications and, instead, a designated domain account is commonly recommended as the preferred option. Such change can be applied from the SQL Server 2005 Services node of the SQL Server Configuration Manager console, from the Services Control Panel applet, or via the SQLACCOUNT command line option of the SQL Server 2005 setup program.

Similarly, keep in mind the unique defaults of SQL Server 2005 Express Edtion in regard to the recovery model (set to Simple) and trace (disabled) database options, which, while well-suited for single user environments, are most likely not appropriate in situations demanding improved reliability and management capabilities. To adjust these settings, use the Options section in the Properties dialog box of individual databases within SQL Server Management Studio console. Alternatively, you can accomplish the same goal by executing the ALTER DATABASE T-SQL statement (with SET RECOVERY FULL or SET RECOVERY BULK_LOGGED options) and by launching the sp_configure stored procedure with the 'default trace enabled' parameter. Note also that CLR integration status will be preserved during an upgrade. The easiest method of changing it from the SQL Server 2005 Express Edition 'disabled' default involves SQL Server 2005 Surface Area Configuration (in particular, its Configuration for Features link). It is also possible to use the sp_configure stored procedure with 'clr enabled' parameter for this purpose, followed by the RECONFIGURE WITH OVERRIDE statement.

If you decide to follow the side-by-side installation, you will need to apply the desired settings and copy required databases between the instances manually (however, you have the benefit of performing this process at your own pace, with proper testing, and easy fallback that does not require reinstallation or restore). The latter can be accomplished by detaching them from the original installation and subsequently attaching them (via graphical interface of SQL Server Management Studio or with CREATE DATABASE... FOR ATTACH T-SQL statement) to the new one.

This concludes our discussion on various upgrade scenarios involving SQL Server 2005 Express Edition. In the next article of our series, we will focus on some of its more advanced functionality.

» See All Articles by Columnist Marcin Policht