Database Objects Migration
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
(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.
|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
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
|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 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"
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
(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.
|Svr_Dev_1||Pubs||Authors||T||Jeff Apr.1, 2005|
(1) Objects from the source databases will be migrated to the target
databases with same names, i.e. from "pubs" (source) -->
(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)
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.
(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)
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.
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.