Migration of Database
One of the usual tasks done by the DB developers is DB Migration. Migration is nothing but transfer of data from one database to another. Its not always easy if the data need to be migrated to be appended on the target database.
Appending means nothing but add the unavailable data from first database in to target database.
Steps for migration
1. First step for migration understands the Source database. The process includes drawing of a simple Entity relationship diagram for the source database. There will be an important table with an important column which spread across the database. May be more than one table. Our first duty is to identify such tables and the relations. Also we need to identify the static tables. which may be migrated manually .And identification of identity columns and turning on the IDENTITY_INSERT property also important.
2. On next step we have to create an SP for migration. Most important variable which can be taken as the parameter. Select all the columns from the related tables can be put into temporary tables .Dont forget to add conditions while putting the values in temporary tables.
After that we can write the insert statements. and last we can drop the temporary tables.
For eg.
CREATE PROCEDURE PRO_NAME
(
PARAMETER1
)
AS
SELECT
COL1,
COL2….
INTO #TEMPTABLE
FROM SOURCE TABLE
WHERE CONDITIONS
SELECT
COL1,
COL2….
INTO #TEMPTABLE
FROM PARENT TABLE
WHERE CONDITIONS
INSERT INTO DEST TABLE
(
COL1,
COL2….
)
SELECT
COL1,
COL2….
FROM #TEMPTABLE
WHERE CONDITIONS
DROP ALL TEMPEROTY TABLES
Test it with a single value check whether any truncations happenings on the output
3. We can automate the migration using cursors
SELECT col1 INTO temp FROM source
DECLARE @V1 VARCHAR(200)
DECLARE @V2 VARCHAR(200)
DECLARE CUR CURSOR
FOR SELECT col1 FROM # temp
OPEN CUR
FETCH NEXT FROM CUR INTO @V1
WHILE(@@FETCH_STATUS=0)
BEGIN
exec sp @V1
FETCH NEXT FROM CUR INTO @V1
END
CLOSE CUR
DEALLOCATE CUR
4. We may need to write more than one SP for migration process.
Make sure that all data is migrated successfully .Dont forget to take statistics also.
