ASP.NET Tricks

Problem

From the practice lessons I’ve learned I came to the conclusion that Access databases can be used for simple CMS (Content Management Systems).
In most cases Access is not appropriate for creating complicated business logic. The case that I’m writing is about migrating an Access database to MS SQL Server 2000. The main goal was to achieve stable and scalable application based on MS SQL Server 2000.

Research

After some research I came upon different methods for migrating to MS SQL Server 2000. Below I’ll describe in details three of them:

• By using DTS (Data Transformation Services) in SQL Server 2000
• By using additional software which is not free (SSW Upsizing Wizard Pro, MSSQL Migration Toolkit, etc.)
• By using the “Upsizing Wizard” - an MS Access utility

Now I’ll describe the pros and cons according to my experience for each of the methods:

DTS usage

Pros:

• All the tables are migrated successfully

Cons:

• The relations between the tables are not migrated
• The indexes are not migrated
• The views are not migrated

Use of additional software (not free)

The pros and cons here depends on the software. The tools I tried are:

• SSW Upsizing Wizard Pro - This software checks the Access database structure in depth and gives suggestions for improving the database tables. Implementing all the suggestions guarantee that Access Upsizing Wizard utility will be executed without errors.
• MSSQL Migration Toolkit - Here you can find some tools for migrating from one database to another. The cons are almost the same as for using DTS

MS Access Upsizing Wizard

Pros:

• All the tables are migrated successfully
• All the relations are migrated successfully
• All the CASCADE DELETE and CASCADE UPDATE definitions are migrated successfully
• All “default value” column definitions are migrated successfully
• All NULL column constraints are migrated successfully

Cons:

• The views are not migrated at all
• If there is a cyclic CASCADE definition between some tables, the relations between them are not migrated correctly.
  Tip: Triggers can be used in MS SQL Server 2000 to create the functionality of the CASCADE definitions that cannot be migrated
• Some of the indexes appear more than once so all of them must be checked and cleared
• PRIMARY KEY index must be set manually as CLUSTERED

Database structure migration

Considering the above pros and cons I decided to use the MS Access Upsizing Wizard utility and check after that which objects migrated successfully and which not.

Below are the steps that I executed after the Upsizing wizard had done its job:

• I created all the views manually
• I created some triggers because of the cyclic CASCADE definitions between two tables.
• I checked and created (where needed) manually the relations between the tables with the cyclic CASCADE definitions
• I cleared the redundant indexes

Data migration

I used simple DTS transformation of type “Copy column” for each table and each column in it.

How much time?

All of the research and migration took me about 3-4 days.
The database consisted of 30 tables and 30 views.

If you’re interested in databases and SQL you can read my previous post to find out How to refresh database views in SQL Server 2000/2005.

dts, migrate, ms access, ms sql, upsize upsizing wizard