Migrating Database Objects

Introduction

Recently, I "dug out" two laws to be a happy DBA, so I cannot wait
to share with all DBA's here to hear your feedback. :-)

DBA Law One: The amount of fun from a DBA job is
directly proportional to how much you can make simple work complex and
complex work simple.

DBA Law Two: The amount of leisure time you can enjoy
is proportional to the percentage of the collective team work you can
leverage.(note: I wish to see more laws contributed here by other DBA's)

The following example illustrates how I use the two laws as my guidelines to
tackle a real world issue.

To me, database migration is no fun, esp. when I have to migrate a few hundred objects out of several thousand objects in different databases created by a bunch of teams through many months of work. What? You say backup and restore? No, if it were that simple, I would be laughing in my dream as I would have more time to surf online. For example, with data warehousing projects becoming more popular these days, more than often, you will find new projects are created based on the previous work, which means you only add new dimension tables, views, stored procedures in the old database developed in a project before last. What cause you to scratch your head is that the tables and views and their relationships may not be created by you, but by other developers / DBA's in various project teams. Now it is your task to migrate several projects to the UAT (User Acceptance Test) environment from several unit test environments and to production environment when the UAT is done.

Generally speaking, there are three ways I can choose to do the migration, each has its advantages and disadvantages.

Method

Advantage

Disadvantage

Gather the original scripts that create the tables in the
test environment, massage them to make it work as a whole for generating
all needed tables

Easy to collect scripts, if SP and views are encrypted,
this may be the ideal way to choose.

Pretty heavy work because you have to combine the DRI
scripts by copying & pasting, shuffling or modifying the scripts
wherever necessary.
Some objects, esp. jobs, are rarely created using scripts, so you
still have to generate the script for these objects.

Use EM by right-clicking the table and generate the script
for all database objects

Easy to learn by anyone

Boring and no fun, you learn nothing from what you have
done

Use DTS Copy DB object task

GUI interface; easy to maintain

Not flexible sometime, for example, the migration of the
product will be done by a client DBA, who prefers pure scripts.
Jobs cannot be migrated this way
It is a pain to pick a few hundred objects out of thousands of objects
from various databases.

Script the all the objects with VBScript and DMO according
to self-defined migration rules.

Most flexible; easy to share;
You can define your protocol on how to script the objects and leverage
your team work, and make the labor-intensive work more enjoyable

If SP's and views are encrypted, no way to generate the
scripts with this approach

According to DBALaw One, I need to choose
an approach that makes the labor-intensive and complex work simple like a breeze
to get the fun.
According to DBALaw Two, I need to find a way to
leverage as much work as possible done by others to save for my "leisure"
work time.
Combining these two criteria together, I decide to choose the last approach mentioned above.

Features of the migration script package

The approach actually contains two key component, one is a
Excel sheet file and another is a VBScript program. The technical details will
be outlined later in part 3. My designed feature for the migration package is as
follows:

(1) The package can drop the created database objects when necessary, so if
there is error occurring after you created 300 objects while there are still 400
left, you do not have to manually delete the 300 created objects.

(2) The package can migrate objects to different databases automatically.

(3) The package should be well self-documented, which means the migration
scripts can be easily read and understood.

To achieve the goals mentioned above, I created an Excel sheet file to define
my own migration protocol.

Server

DB

Object

Type

Note

Svr_Dev_1

Pubs

Authors

T

Jeff Apr.1, 2005

Svr_Dev_2

NorthWind

Invoices

V

Key view

Migration Assumption:

(1) Objects from the source databases will be migrated to the target
databases with same names, i.e. from "pubs" (source) -->
"pubs" (target)
(2) Database objects are only migrate to ONE target server each time.

(5) [Note] column: Comment you want to add to your script for that specific
object, such as who created the object when etc. (I'll explain more later)

Actually, we can add more columns to define our specific needs, for example,
we can add another column [Initialization], which we want to add some codes for
initializing the newly created tables by inserting some records; or we can add a
new column [NewName], which will change the newly created objects to new names,
or we can add a column [TargetDB], which will migrate the object to the targeted
db (if this is the case, the assumption 1 mentioned above can be omitted)

Implementation

Step 1. Load the data in the Excel file (or CSV) file into a table (say
MigrateObject) in a temporary database. (Actually, I always create a database [Maint] for my personal use, such as logging my own events, my work log etc. ).
The easy and straightforward way is to use a DTS package to finish the work, of
course, bcp and bulk load can also be used.

Step 2. Create a VBScript program, which I called "Migration Prep
Engine", to loop through the MigrateObject table and generate the SQL
scripts for migration.

Some explanations:

(1) A table is scripted into two parts, one is the basic table structure
creation script together with other keys (PK, Unique keys etc) but not foreign
keys. The other part is to create a script that will generate table FK's. The
reason is that we do not need to care about the table creation sequence by doing
this way. Otherwise, we have to create parent tables before children tables can
be created, which refer to the parent tables.
(If a table has triggers, we need to create a third part dedicated to the
triggers for the table)

(2) The script does not consider security content around database objects. My
experience is usually security in one environment (test or development) is not
the same as in another environment (production). Usually, a DBA will define,
create and modify the security policy manually (such as add or delete one login
or modify a role etc)

(3) This script excerpt servers as an example, I only consider scripting two
objects, one is table (without trigger) and another is view. For all other
objects, only a few minor changes are needed.

Rem
--------------------------------------------------------------------------------------
Rem MigrateObject.vbs is used together with the Excel sheet file
MigrateObject.xls, which
Rem is loaded into the table Maint.dbo.MigrateObject table
Rem Created: Jeffrey Yao Dec.12, 2004
Rem
---------------------------------------------------------------------------------------

After running command "cscript MigrateObject.vbs" in command line
(in Window 2000/XP prof) , we will get three SQL script files in c:\temp folder, they are migratation_tbl.sql, migration_ind.sql and migration_vw.sql.

3. (optional) Compose two batch files for real migration purpose, one is to clean the migrated objects and another is to migrate objects.

One note here is that when we run the clean.bat, usually we will get errors indicating that one table cannot be dropped because it is referenced by another table, do not worry, just run it again and again until no such errors occur because each time we run it, it will drop the "leaf level" tables that are not being referenced, and if we continue to run it, it will eventually delete the "root level" tables. (Yes, I can write a script that drop all tables by running only once, but I am not that kind of perfectionist as I'd rather explore other interesting fields than spend the time to make it a perfection. Remember that 80 / 20 rule? )

Note: drop_tbl.sql can be extracted from migration_tbl.sql by only copying
the header part of each object generation script, it includes these two lines
for each table:
If exists (select * from dbo.sysobjects where id =
object_id(N'Author') and objectproperty(id, N'IsUserTable') = 1)
drop table
Author
go
Same for drop_vw.sql. I actually use VBScript to generate the drop_tbl.sql and
drop_vw.sql, all I need to do is use SQLDMOScript_Drops only in the VBScript to
generate these scripts.

Usage In Real World

Step 1. I will put the Excel sheet file in a shared network drive and ask
people who are responsible for all new database objects to fill in the sheet
what need to be migrated. (Is not this a great team work? It is less likely one
object will be missed than if I were responsible to fill in the sheet)

Step 2. I will run the "Migration Prep Engine" (MigrateObject.vbs)
to generate the migration scripts.

Step 3. Run the two batch files against my own environment to test the script.

Step 4. Run the batch files to implement the migration to a target server.

Step 5. Save the scrip and the MigrationObjet.xls Excel sheet file into VSS and
label it for future reference.

By adopting this new method, it seems it won't take me an hour to migrate
tens of hundreds of objects, and here I really enjoy the benefits of the two
"DBA laws" mentioned above.

Summary

In this article, I introduced a new approach to migrate database objects with
self-defined migration rules (protocol) in a Excel sheet file, which can be
shared by all team members. This approach aims to leverage the team work and
make the migration objects well documented. It is flexible and may be easily
customized to suit various migration scenarios so long as you modify the
"Migration Prep Engine" to adapt to your new migration protocol in the
Excel sheet file.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.