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
connectionString="Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=AdventureWorksLT_Data;Integrated Security=True"
Assuming that your application leverages Windows
authentication to interact with the database
In order to perform an upgrade, launch the SQL Server 2005 setup program
setup.exe ADDLOCAL=SQL_Engine,Client_Components INSTANCENAME=SQLEXPRESS UPGRADE=SQL_Engine SKUUPGRADE=1 /qb
If you include the
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
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
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
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
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.