Sybase to SQL Server migration – Part II

In continuation to my earlier posting on Sybase to SQL Server migration – Part I here is what I did with the wealth of information I found.

I prepared a list of issues that would come up during the migration or probably the issues I should look out for while migrating.

But before all these I decided to prepare a list of migration steps that would be involved by any DBA. Here is the list you guys want to have a look at.

Category	Activity	Work on	What work?
Instance	Gather and map the parameters, configurations wrt new instance	Old Instance	Admin
	Create scripts for parameters, configurations from old instance	Old Instance	Admin
	Create new SQL Server Instance with mapped parameters, configurations	New Instance	Admin
	Create scripts for logins from old instance	Old Instance	Admin
	Gather and map logins, roles wrt new instance	Old Instance	Admin
	Create scripts for application role, user defined roles from old instance	Old Instance	Admin
	Create logins on new instance	New Instance	Admin
	Create roles (application, user define) on new instance	New Instance	Admin
	Gather and map backup devices wrt new instance	Old Instance	Admin
	Create scripts for backup devices from old instance	Old Instance	Admin
	Create backup devices on new instance	New Instance	Admin
	Gather configuration details of linked servers wrt new instance	Old Instance	Admin
	Create linked servers on new instance	New Instance	Admin
Database	Gather and map database parameters, configrurations wrt new instance	Old Instance	Admin
	Create scripts for database parameters, configurations from old instance	Old Instance	Admin
	Create database on new instance	New Instance	Admin
	Gather and map user defined datatypes wrt new instance	Old Instance	Object definition
	Create scripts for user defined data types from old instance	Old Instance	Object definition
	Create user defined data types on new instance	New Instance	Object definition
	Gather and map partitions wrt new instance	Old Instance	Object definition
	Create scripts for partitions from old instance	Old Instance	Object definition
	Create partitions on new instance	New Instance	Object definition
	Gather and map datatypes, defaults, constraints (check, primary, foreign), ruels wrt new instance	Old Instance	Object definition
	Gather, identify and replace reserved keywords wrt new instance	Old Instance	Object definition
	Create tables with appropriate datatypes, defaults, constraints (check, primary, foreign key), rules from old instance	Old Instance	Object definition
	Create tables with appropriate datatypes, defaults, constraints (check, primary, foreign key), rules on new instance	New Instance	Object definition
	Gather, identify indexes and replace appropriate indexes (index names) wrt new instance	Old Instance	Object definition
	Create scripts with indexes (with, without partitions) from old instance	Old Instance	Object definition
	Create indexes (with, without partitions) on new instance	New Instance	Object definition
	Create scripts for stored procedures from old instance	Old Instance	Object definition
	Create stored procedures on new instance	New Instance	Object definition
	Create scripts for views from old instance	Old Instance	Object definition
	Gather, identify and replace appropriate keywords, syntax, un-supported functions, datatypes, transaction handling, etc. wrt new instance	Old Instance	Object definition
	Create views on new instance	New Instance	Object definition
	Gather, identify and replace appropriate keywords, syntax, un-supported functions, datatypes, transaction handling, etc. wrt new instance	Old Instance	Object definition
	Create scripts for triggers from old instance	Old Instance	Object definition
	Create triggers on new instance	New Instance	Object definition
	Gather, identify and replace appropriate keywords, syntax, un-supported functions, datatypes, transaction handling, etc. wrt new instance	Old Instance	Object definition
	Create scripts for functions (scalar, in-line) from old instance	Old Instance	Object definition
	Create functions (scalar, in-line) on new instance	New Instance	Object definition
Migrating data	Entity mapping, Attribute mapping, Relationship mapping, GAP analysis	Old – New Instance	Data
	Disable constraints (primary, foreign key, check)	New Instance	Data
	Disable indexes	New Instance	Data
	Extract data out of old database using BCP, SSIS packages	Old Instance	Data
	Load data in to new database using Bulk Insert, BCP, SSIS packages (Dry run, Validate, Refine)	New Instance	Data
	Enable constraints (primary, foreign key, check)	New Instance	Data
	Enable indexes	New Instance	Data
Testing	Check functionality of the database thro’ the application	New Instance	Test
	Check functionality of the instance involving linked servers	New Instance	Test
	Check functionaility of the instance, database performance using applications	New Instance	Test
Deployment	Release for end-user use	New Instance	Go live

And after this listing on step by step methodology I went ahead and identified the issues that could put the whole migration in difficulty. Due the exhaustiveness and size of the issue list I am unable to share it in this post. I shall however try fitting it into some other posts or share the critical ones with you guys!

Until then happy reading!

Sybase to SQL Server migration – Part I

As promised in my earlier post Sybase to SQL Server 2005 migration here is the first post (of several posts) of the migration that was done.

The requirements were like,

1) Sybase version 12.5.4 running on Sun Solaris OS 5.8 to SQL Server 2005 on Windows 2003
2) Migrate objects like tables, stored procedures, triggers, indexes and migrate data from tables

Well, the moment I saw the requirements I was pretty comfortable assuming that this could be a straight forward migration. But yes, my inner thoughts knew that there are going to be of issues that are going to come up and I will have to resolve them one after the other thru’ help from forums, documentation, experiences from my earlier migrations, and so on…..

With this thoughts I started off doing search in my favorite (everybody’s favorite, I hope nobody will deny this at least ) search engine Google. There were good number of links that showed up but now I had the task cut out – to filter one by one to best relevance, and save the experiences of the forum members into a document of little relevant links.

After several rounds of searching, reading, assimilating and understand the contents of each reference link I would like to present bunch of important one’s that you guys wouldn’t want to miss.

Links for documentation on migration from sybase to sql server:

• Chapter 6 – Migrating Sybase Databases to SQL Server 2000

Although the link shows up the migration from Sybase to SQL Server 2000, but this is one of the good documents that Microsoft has under SQL Server 2000 resource kit and this turned out to be “Encyclopedia” for my migration strategy]

• Solution Guide for Sybase/UNIX to SQL Server 2000: Database Engine Migration and Application Interoperation Guide

This link talks about how to deploy the migration strategy on production system or mock production systems. You could download the copy in word format from here.

Few other links which gave me showed the path of experience apart from above links on theory were

1. Making the Move from Sybase to SQL Server by Sayed Geneidy

2. Migrating from Sybase to SQL Server by Denis Goby

Although the links talk about the migration from different Sybase versions to SQL Server versions, I was having the needed information to kick off my work. And in my next post I am going to talk about what all I had to do with the collected information from these links. Until then, enjoy reading!

Sybase to SQL Server 2005 migration

Hello Guys,

As per my promise given in my earlier blog post Back Again!, I shall be sharing my experiences when I had to do Sybase to SQL Server 2005 migration. This would be multi-series posts, and I am intending to cover lot of technical details along with experiences.

Keep watching the blog from tomorrow.

Happy reading!