|
Return to Newsletter Contents...
Using the SQL Server Migration Assistant
by: Mike Mullin, Consultant, MCAD
Microsoft wants you to switch to SQL
Server 2005. So they have provided a FREE tool to help you migrate. The SQL Server
Migration Assistant (SSMA) can be downloaded from the SQL Server 2005 website. Besides
requiring the java runtime to be installed, it requires no special software.
The SSMA is installed on a client machine
of the database, not on the server. SSMA depends on the presence of client software
for the DBMS you are migrating from, such as Oracle. Before you install the software,
you must install the Java Runtime Engine, which is available for free from Sun Microsystems.
The zip file which you download from
Microsoft will contain two installation files. The first is the SSMA. The second
installation file contains stored procedures and other extensions which are specific
to the original database. This is why there are separate SSMA packages for the various
vendors.
The tool provides four services. The
first is the generation of an assessment report which estimates the number of man-hours
required to migrate the targeted database to SQL Server. The report lists each database
object and an estimate for that object. I have no idea how accurate these estimates
will prove to be. We will probably have to wait a year to get that answer.
Once you have decided to proceed, the
SSMA can convert the database schema. After SSMA has prepared the schema conversion,
you can compare the existing schema from within the tool. For example, if there
is a complex view, you can display the original view’s SQL code in one panel while
comparing it to the SQL code to be used in the new SQL database.
Once you have reviewed the code and
tweaked it to your satisfaction, you can push the schema out to your new SQL database.
The last step is to transfer the data
to the new database. There is one aspect of the tool which is not quite intuitive.
When you transfer either the schema or the data to SQL Server it is a two step process.
First you perform the transfer and then “Synchronize Databases”. During the transfer,
SSMA with create some work tables in the new database which contain the information
being transferred. It is the “Synchronize Database” command which then extracts
the information from these work tables and creates or populates the database objects.
Please note SSMA transfers data and
schema. It does not update your applications. If your applications use stored procedures,
these will be transferred. However if you have buried in-line SQL statements in
your code, or hard coded your connection strings, you will have to resolve the problems
yourself.
Go to Top |
Return to Newsletter Contents
|
