MSDE to SQL Express 2008 Migration

One of our Consulting engagements demanded for an MSDE to SQL Express 2008 database upgrade to occur at the time of the client application installation. So we proposed a solution for using SQL Management objects (in short SMO) to accomplish this task. SMO is defined as a collection of objects that are designed for programming all aspects of managing SQL Server. SMO are industry standard practice to provide an abstraction to the end user with the Databases. Here’s our experience with SQL Management Objects on this engagement.


Objectives:-
Our customer happens to be a giant in the retail industry. The customer had a Purchase Order Management Client application which was several releases old built on the .NET Framework 2.0 and SQL Server 2000 Microsoft Data Engine (MSDE). The client application uses a SQL Server 2000 Desktop Database Engine (MSDE) database to store most data, consisting of lookup data synchronized with the central server and worksheet data that are created and modified by the end user. The current security architecture of the application uses a SQL defined account. In order to support the latest release for the client application our development team decided to upgrade from MSDE to SQL Express 2008, since SQL Express 2008 had better security features than MSDE.

Challenges:-
The major challenges that we faced during this engagement was to come up with a strategy so that the end user/Technical support Staff had very minimal amount of task while performing this upgrade. So our strategies included,

1. A silent installation script for installing SQL Express 2008 with a named instance
2. When the application is upgraded, move the old worksheet data from the old MSDE instance to new SQL Express 2008 instance.

Silent Installation of SQL Express 2008
Few pre requisites for SQL Express 2008 installation are,

1. .NET Framework 2.0 SP2 or higher
2. Windows Installer 4.5

Since our new application was being built on .NET 3.5, we had to upgrade all the retailers’ machines with .NET Framework 3.5 SP1, but this does not ensure that we have Windows Installer 4.5 installed on the machine.
So we had to write a custom MS DOS batch script which checks for the Windows Installer 4.5 and proceed further with SQL Express installation.

Database Migration from MSDE to SQL Express 2008
Now comes the interesting part of the whole migration where, we had to come up with a strategy to migrate the existing database from MSDE to SQL Express 2008. To this the SQL Management Objects (or SMO) came in very handy for us to do the development.
Here is flow chart of actions that needs to be taken in order to migrate the database from MSDE to SQL Express 2008,


Here is the code snippet where we do the database migration from MSDE to SQL Express 2008.
First of all we must add the following references for SMO, The Class diagram for the same is as below,
Class Constructor:-
The class constructor is being used to initialize the Server objects and to obtain a handler to the MSDE and SQL Express Instances.

Getting physical file names of the database in MSDE:-
This function gets all the physical files names of the database, a database can include several Data files and log files collections, The FileGroupCollection objects is a collection of all the data files associated with the database, while the LogFileCollection object as the name suggests has a collection of all the log files of the database. It becomes essential for us to obtain all the physical files of the database before migrating it to SQL Express 2008.

Detaching the database from MSDE and attaching it to SQL Express 2008
The Server object gives us methods to detach and attach a database; these methods are used as below:

We hope the information was useful. We will come up with such information more in the future.