Database Migration Automated

By -

Part 1:-
  • Configure the Distributor, Publisher and enable the database to synchronize from backup ( to make sure I would not need to generate a full snapshot to initialize, as the business requirement was to do this task with least overhead on the live systems)
  • Create an empty publication with no articles. (again I could add all the articles but I choose not to, so I can have control over which article I would like to replicate across)
  • Backup database to be migrated across
  • Verify the backup taken above is valid
  • Copy the backup across the network to the destination server ( scripted Robocopy and used multi threading (MT: 100), flows like a breeze on a dedicated network )
  • Restore the database on the destination server.
  • Create a push subscription
The script creates a log file for each task organizing it in the folder structure with ServerName and a subfolder with DatabaseName then creates a txt log file with results of each step.
The script then prompts the user to check the log file to make sure there are no errors, and press any key to continue migration.


Part 2:-
  • Copy across logins with SID to destination server (I did this using SSIS package, I could use the sp_help_revlogin)
  • Copy across agent Jobs and SSIS packages ( again did this using SSIS package)
  • Copy across Linked Servers
  • Automated LogShipping setup.
Issues faced:
Faced no unusual issues other than things you would face normally when setting up transactional replication using backup to initialize. We could have avoided these issues if we had enough time to analyze the databases prior to actually working on the migration. I was put on the task with no lead time to investigate.
A subset of the issues I faced:
  • Errors with TimeStamp Column when initializing using backup; The business requirement was not to change any database schema, so converting TimeStamp column to varbinary was ruled out. I got lucky as the databases with this issue were all small databases ranging upto 8 GB and I could easily initialize delivering a full snapshot for these without any concerns.
  • Tables without Primary Keys, obviously replication would only work for tables with primary keys defined on them. I prepared a script that would provide me a row count of each table of every user database on each of the source and destination servers I was migrating. I pulled this data into an excel, prepared a pivot table and there it was , very easy to compare which tables were out of sync. Thanks to redgate data compare and SSIS package I could easily sync these tables without any issues. the trick is to compare the tables selecting all the columns for comparison.
  • Ever changing business requirements
  • Application teams were busy working towards their release and had BLOB data imports into the database I was replicating across in preparation of the Migration CutOver. Had to increase the max text repl size to let the application import BLOB data.
  • Had a couple of Logshipping databases on the source included in the list to perform this premigration replication activity. Later identified as to setup Log shipping separately for these two databases. Now the replication would not allow you remove the subscription and publication as the subscriber was a logshipped read only database. Had to manually remove replication on both publisher and subscriber individually.
  • SQL Servers evaluation Editions were installed on the destination servers. Had to upgrade SQL Server Evaluation Edition to Enterprise Edition. Had to Manage SQL Server Active – Passive Windows Cluster.
  • I had to move the distribution database to another drive at some point in the pre migration process due to lack of disk space on the servers.

Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more