Database Objects Migration

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.

According to DBA Law 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 DBA Law 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.

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.

Migration Protocol Definition:

(1) [Server] column: Source server where source databases are located.

(2) [Database] column: Source Databases

(3) [Object] column: The name of the object you are going to migrate

(4) [Type] column: T = Table, V = View, SP = Stored Procedure, J = Job, UDF =

User Defined Function ...

(note: DTS package cannot be migrated with this approach because DTS package

cannot be scripted)

(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
---------------------------------------------------------------------------------------

option explicit
dim oRS, oCnn, sqlCmd, strCnn
dim DB_Old_T, DB_New_T, DB_Old_V, DB_New_V
dim  blnWriteUseDB ' control whether to write a "Use <DB>
Go" statement.in the script
Const SQLDMOScript_DRI_PrimaryKey = 268435456
Const SQLDMOScript_DRI_ForeignKeys = 134217728
Const SQLDMOScript_OwnerQualify = 262144
Const SQLDMOScript_IncludeHeaders = 131072
Const SQLDMOScript_NoDRI = 512
Const SQLDMOScript_AppendToFile = 256
Const SQLDMOScript_Default = 4
Const SQLDMOScript_Drops = 1
Const SQLDMOScript_ToFileOnly = 64
Const ForAppending = 8
set oCnn = CreateObject("ADODB.Connection")
'assume we use the stand SQL Server authentication method
strCnn = "Provider=sqloledb; data source = MyServer; initial catalog =
Maint; user id=jeffrey.yao; password = jy"
oCnn.open strCnn
set oRS = CreateObject("ADODB.Recordset")
sqlCmd = "select server, db, object, type, note from MigrateObject "
'order by server, db, type
set oRS = oCnn.execute (sqlCmd)
DB_Old_T = ""
DB_Old_V = ""
blnWriteUseDB = 0
blnWriteUseDB = 0 '0=Not write "USE <DB> GO" in the script,
1=Write
if not oRS is nothing then
do while not oRS.eof
        DB_New_T =
cstr(oRS.fields("DB").value)
        DB_New_V = DB_New_T
        if DB_New_T <> DB_Old_T and
trim(oRS.fields("Type")) = "T" then 
            blnWriteUseDB
= 1
            DB_Old_T =
DB_New_T
        end if
        if DB_New_V <> DB_Old_V and
trim(oRS.fields("Type")) = "V" then 
            blnWriteUseDB
= 1
            DB_Old_V =
DB_New_V
        end if
    select case trim(ucase(oRS.fields("Type").value ))
        case "T" call TableScript(
oRS.fields("Server"), oRS.fields("DB"),
oRS.fields("object"), oRS.fields("Note"), _
                              
             
"c:\temp\migration_tbl.sql", "c:\temp\migration_ind.sql",
"jeffrey.yao", "jy", blnWriteUseDB)
        case "V" call ViewScript(
oRS.fields("Server"), oRS.fields("DB"),
oRS.fields("object"),   oRS.fields("Note"), 
_ 
                              
              
"c:\temp\migration_vw.sql" , "jeffrey.yao", "jeffreyy",
blnWriteUseDB)
       case else msgbox ("Invalid object type
is encountered")
    end select
    blnWriteUseDB = 0
    oRS.MoveNext
   loop
end if
set oRS = nothing
set oCnn = nothing
'-----------------------------
------------------------------
-----------------
'Function: Based on the input parameter, the proc will write two files
'-----------------------------
------------------------------
-----------------
Sub TableScript (byval Server, byval DB, byVal Object, byVal Note, byVal
TableFileUNC, byVal IndexFileUNC, byVal uid, byval pwd, byval WriteUseDB)
dim oServer, oDB, oTbl
dim fso, fl, fl_idx
if isnull(Note) then Note = ""
if trim(cstr(Note)) <> "" or cint(WriteUseDB) = 1 then
    set fso = CreateObject("Scripting.FileSystemObject")
    set fl = fso.OpenTextFile( cstr(TableFileUNC), ForAppending,
true )
    set fl_idx = fso.OpenTextFile( cstr(IndexFileUNC),
ForAppending, true )
    if trim(cstr(Note)) <> "" then
        fl.writeline ( "--" &
cstr(Note) )
    end if
    if cint(WriteUseDB) = 1 then
        fl.writeline ( "use " &
cstr(DB) & vbCrLf & "go" )
        fl_idx.writeline ( "use "
& cstr(DB) & vbCrLf & "go" )
    end if
    fl.close
    set fl = nothing
    set fl_idx = nothing
    set fso = nothing
end if
set oServer = CreateObject("SQLDMO.SQLServer")
OServer.connect cstr(Server), cstr(uid), cstr(pwd)
set oDB = oServer.databases(cstr(DB) )
set oTbl = oDB.tables(cstr(object) )
oTbl.script SQLDMOScript_IncludeHeaders + SQLDMOScript_ToFileOnly +
SQLDMOScript_Drops + SQLDMOScript_AppendToFile + SQLDMOScript_Default +
SQLDMOScript_DRI_PrimaryKey + SQLDMOScript_OwnerQualify, cstr(TableFileUNC)
oTbl.script SQLDMOScript_IncludeHeaders + SQLDMOScript_ToFileOnly +
SQLDMOScript_AppendToFile + SQLDMOScript_DRI_ForeignKeys +
SQLDMOScript_OwnerQualify, cstr(IndexFileUNC)
set oTbl = nothing
set oDB = nothing
set oServer = nothing
end sub
'-----------------------------
------------------------------
-----------------
'Function: Based on the input parameter, the proc will write to one file
'-----------------------------
------------------------------
-----------------
Sub ViewScript (byval Server, byval DB, byVal Object, byVal Note, byVal
ViewFileUNC, byVal uid, byval pwd, byval WriteUseDB)
dim oServer, oDB, oView
dim fso, fl
if isnull(Note) then Note = ""
if trim(cstr(Note)) <> "" or cint(WriteUseDB) = 1 then
    set fso = CreateObject("Scripting.FileSystemObject")
    set fl = fso.OpenTextFile( cstr(ViewFileUNC), ForAppending,
true )
    if trim(cstr(Note)) <> "" then fl.writeline (
"--" & cstr(Note) )
    if cint(WriteUseDB) = 1 then fl.writeline ( "use "
& cstr(DB) & vbCrLf & "go" )
    fl.close
    set fl = nothing
    set fso = nothing
end if
set oServer = CreateObject("SQLDMO.SQLServer")
OServer.connect cstr(Server), cstr(uid), cstr(pwd)
set oDB = oServer.databases(cstr(DB) )
set oView = oDB.views(cstr(object) )
oView.script SQLDMOScript_IncludeHeaders + SQLDMOScript_ToFileOnly +
SQLDMOScript_Drops + SQLDMOScript_AppendToFile + SQLDMOScript_Default +
SQLDMOScript_OwnerQualify, cstr(ViewFileUNC)
set oView = nothing
set oDB = nothing
set oServer = nothing
end sub

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? )

Migrate.bat is illustrated in the following,

@echo off
time /T
osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\migration_tbl.sql
osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\migration_ind.sql -o c:\temp\ErrIdx.txt
osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\migration_vw.sql
time /T

Clean.bat is illustrated in the following
@echo off
osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\drop_tbl.sql
osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\drop_vw.sql

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.