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.

Purchase Order Management

One of our consulting engagements on Microsoft Business Intelligence Stack is with Purchase Order Management for a retail giant in apparels and home goods across the United States. When it comes to the Retail Industry, most people tend to think it as a Point of Sale (POS) solution. Apart from a POS application, the Retail industry also has to cater to the needs of the retail stores for their Order Management. In simple terms a Purchase Order Management System is a system which is used to manage the purchase orders. But what makes a purchase order (PO) a vital document is that, it is a document issued by a buyer to a seller, indicating the type, quantities and agreed prices for products or services the seller will provide to the buyer. Sending a PO to a supplier constitutes a legal offer to buy products or services. Acceptance of a PO by a seller usually forms a once-off contract between the buyer and seller so no contract exists until the PO is accepted. So this makes a PO, very critical document for any retailer.

The company has a Purchase Order Management application suite which is several releases old. This application suite has few client applications built over Microsoft .NET coupled with legacy systems like Mainframes and RS6000 for Retail shops to place orders from their retail outlets.

Our team proposed new application architecture to cater to the needs of the PO Systems, as there were several challenges with upgrading the application, i.e., any change one of the modules in the application involved end-to-end changes in all the layers of the application. The new architecture of the application is based on .NET 3.5, WCF, BizTalk, SharePoint, SQL Server 2008 BI Stack. Now let us discuss about the role of Microsoft BI stack in this engagement.

Scenario:-
As per the existing application architecture, the retail shops and merchandising agents have the flexibility to either fill out a worksheet using a thick client application and transmit them to the server, where the purchase orders are created and moved to the Mainframes, or, the retail shops could place their orders via the merchandising agents, in which case, there were two workflows, one being the Merchandising Agents using the application to fill out a form to generate a PO or, create the PO on the Mainframe. So these two types of the purchase orders are categorized as to Application generated and non application generated purchase orders.

Worksheets are created using the Client Application is stored in the form of an XML document in the SQL Server 2000 (Client Application) and DB2 (Mainframe and RS6000) databases.

Here’s the block diagram that shows the current workflow of Purchase Order (PO) Processing,

Objective:-
The scope of this engagement is widespread, and there are several teams working different modules of the engagement.
Here are few of the modules that we were involved in,

1. Conversion from SQL Server 2000 MSDE to SQL Express 2008 for Client Applications.
2. Implement Integrated Windows Authentication on the Client Application.
3. Generate Statistics and Audit Reports for Application/Feature usage.

Conversion from SQL Server 2000 MSDE to SQL Express 2008 for client application
This task looks simple though but posted a numerous issues at the time of implementation, here are the few of the tasks that were carried out for the same,

1. Silent installation script to install SQL Express 2008 pre requisites and resume installation on system restart.
2. Silent installation batch script to install SQL Express 2008 with a named instance.

Implement Integrated Windows Authentication on the Client Application

Implementing integrated windows authentication was rather simple task. We had to create one LDAP group for the Retail shops/Merchandising Agents and one LDAP group for the administrative users and attach them to the corresponding roles to have data entry or administrative permissions. Thanks to Credential caching feature of Windows, this ensures integrated windows authentication on SQL Express 2008 even in a disconnected mode.

Generate Statistics and Audit Reports for Application/Feature usage

The Statistics and Audit Reports for Application usage is one of the major tasks that we were involved in. There are 3 different reports,

1. A report to determine how many retail shops are using the client application. The selection criteria for this report is flexible to produce the result based on different combinations of divisions (for ex, apparels, home goods, etc), departments (ex, winter wear, men’s footwear, accessories, etc), retail shop and worksheet transmitter, this is also called the productivity report.
2. A report that tells how many PO’s were created for individual departments also called as master report, with the division as selection criteria.
3. An audit report that provides the audit information regarding use of the Lead Time Calculation (LTC) logic, that suggests optimal shipping dates, within the client application, in order to assess the level of use as well as the amount of manual adjustment that is performed after the logic has been applied in the client application. The weekly report illustrates by user the methods by which ship date and ladder plan information was supplied to the client application.

To generate these reports we have used SQL Server Integration Services (SSIS) as ETL tool and SQL Server Reporting Services (SSRS) for the reporting, coupled with ASP.NET 2.0 Website to host the reports and provide selection criteria.

The SSIS packages fetches PO’s from various data sources such as the SQL Server 2000 databases, Mainframe and RS6000 DB2 databases and as per the new architecture of the system, the master data will also be available in a SQL Server 2008 databases. But since there are divisions which are still running on the older version of the application suite, it is necessary to keep the data from the older system as is and provide a switch for the package to start fetching data from SQL Server 2008 databases when all of the divisions are migrated to new version.

The productivity report and the master report are straight forward. These reports count the number of PO’s generated using client application, Mainframes, and RS6000. After getting the count of PO’s these are then stored in the corresponding tables for the reports.

The audit report requires to read through the worksheet and PO XML documents stored in the database to check whether the LTC logic was used or not. SQL Server provides us with XQuery language, using which we can query xml data type. XQuery handles both typed and untyped data. After reading through the XML documents, the values of the required fields are stored in a table. This table contains all the line items of the worksheet, in which the LTC logic was switched on/off.

Apart from these reports there are other BI requirements in this application suite which are planned, for example, analyzing quarterly sales results and forecasting revenues, using SQL Server Analysis Service. Soon we will have more updates on this blog, with more data related to Microsoft BI technologies used in this engagement.