Implementing Upgrade of SQL Server 2005 Express Edition

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=SERVERNAMESQLEXPRESS;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 AUTHORITYNetworkService. 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

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