Wednesday, October 31, 2012

Database Migration from SQL Server 2000 to 2008/2008 R2/2012

1.    Background:
The purpose of this article is to lay out the structure for database migration from SQL 2000/2005 to SQL 2008/R2/2012. This article describes migration process in a simpler way for database only which includes pre and post migration steps also.

2.    What is Upgrade?
Upgrade is an automated process in which the upgrade tool, called Setup, moves an old instance of SQL Server to a new instance while maintaining the data and metadata of the old instance. At the end of the upgrade, the old instance is no longer available and the new instance has the same name as the old instance.

3.    What is Migration?
Migration is a manual process in which the DBA installs a new instance of SQL Server and copies the metadata and data from an old instance of SQL Server to the new instance. Migration provides access to two instances of the system, letting you verify and compare the two systems. During migration, both the old and new systems remain online until migration to the new instance is complete. At the end of the migration, all applications are directed to access the new instance and the old instance is manually removed.

4.    High Level Architecture:


5.    Before Migration

5.1 What to Migrate
This step includes surveying environment to determine SQL Server components that will be migrating and then the best technical option(s) can be determined. Here we are planning to migrate SQL Server database.

  5.2 Upgrade prerequisites
1) Verify that SQL Server 2008/R2/2012 environment meets hardware and software requirements. 
2)  Ensure that SQL Server 2008/R2/2012 environment is capable of supporting future needs from a user and transaction perspective. (using SQL Server 2008 Upgrade Advisor to determine potential issues)
3)   Set up an environment to test the migration process as well as front-end applications and dependent (upstream and downstream) systems' functionality.

   5.3 SQL Server 2012 Upgrade Advisor
Perhaps the most important tool of the several tools typically used for upgrade planning is Upgrade Advisor. Upgrade Advisor smoothes the transition to SQL Server 2012 by predicting issues in your legacy instances of SQL Server 2000 and SQL Server 2005. It analyzes objects and code within legacy instances and produces reports detailing upgrade issues, if there are any, organized by SQL Server component. The resulting reports show detected issues and provide guidance about how to fix the issues or work around them. The reports are stored on disk, and you can review them by using Upgrade Advisor or export them to Microsoft Excel for further analysis.
In addition to analyzing data and database objects, Upgrade Advisor can analyze Transact-SQL scripts and SQL Server Profiler/SQL Trace traces. Upgrade Advisor examines SQL code for syntax that is no longer valid in SQL Server 2008/R2/2012. It generates a report listing the code in question, together with links to where you can find more information to help resolve the questionable code.
5.4 Requirements for running Upgrade Advisor 2012 are as follows:
1)    Windows Vista SP1, or SP2, Windows 7 and Windows Server 2008 R2.
2)  Windows Installer beginning with version 4.5. You can install Windows Installer from the Windows Installer Web site.
3) Microsoft .NET Framework 4. .NET Framework 4 is available on the SQL Server 2012 product media, and from the .NET Framework 4 download page.
4)  SQL Server 2000 Decision Support Objects (DSO) if analyzing SSAS (you can use SQL Server 2000 Setup to install DSO)
5)   SQL Server 2000 client components if analyzing DTS (you can use SQL Server 2000 Setup to install the SQL Server 2000 client components)
6)  Pentium III-compatible processor or a later version, with a processor speed of at least 500 MHz
7)  Available Disk Space should be 15MB.

Whether you choose an in-place upgrade or a side-by-side upgrade, run Upgrade Advisor on your legacy systems. You can run Upgrade Advisor from a local or remote server, and you can execute it from the Command Prompt window by using a configuration file name as an input parameter.
Note: You can run the SQL Server 2008/2012 Upgrade Advisor only against instances of SQL Server 2000 and SQL Server 2005. You cannot run it against instances of SQL Server 2008/2012 or on SQL Server 7.0.

Upgrade Advisor is a separate download. The most recent downloadable version is available as part of the below link:

5.  Key SQL Server 2012 migration considerations

Here are additional technical considerations that are keys to the migration:
1) Execute DBCC CHECKDB to validate that the previous environment (SQL Server 2000 or 2005) is free of corruption prior to the upgrade. 

2) Issue final backups and ensure a rollback plan is in place to continue business operations in case an unforeseen problem is encountered. 

3) Develop enterprise standards for SQL Server 2005 configurations, operations (security, maintenance, etc.) and development. 

4)   Archive unneeded data and perform data cleansing for the migrated data. 

5) Ensure all code is operating accurately prior to migration so that migration is the blame for broken code. 

6) Perform database maintenance such as UPDATE STATISTICS, DBCC CHECKDB and index rebuilds post upgrade. 

7) Validate that the SQL Server 2000/2005 configurations and database compatibility modes are accurate post upgrade.

6. What are the reasons for Migration to SQL Server 2008/R2/2012?
There are several reasons to migrate to SQL Server 2008/R2/2012 of which are involves server level improvements that are not performance related. SQL Server 2008/2012 offers certain reliability and availability improvements, such as the following:

1)     SQL Server 2008/2012's database mirroring is more robust and easier to configure and manage. 

2)    Policy-based management can be a tremendous feature for improving the consistency of SQL Server's configuration and ongoing management, especially with compliance or other restrictive requirements. 

3)     A new data auditing feature that provides more granular auditing of SQL Server activity. 

4)    Transparent Data Encryption (TDE) lets you turn on encryption without requiring massive application-level changes and can help meet specific security and compliance requirements. 

5)   The data collector helps make performance management and tuning easier and more centralized. 

6)   The Resource Governor gives administrators central control over CPU and memory allocation, which is useful for servers that host multiple databases and experience significant resource contention between those applications. 

7)    If you manage multiple servers, Server Group management enables you to run T-SQL queries against multiple servers simultaneously from a central management server. This makes server farm management easier and more consistent.

8)      To avail all the new features of the SQL Server 2008/2012.

7. Different ways of Database Migration (SQL Server 2000/2005 to SQL Server 2012)
 There are several ways to migrate the databases from SQL Server 2000/2005 to SQL Server 2008/2012:

        i. Copy database Wizard:  The Copy Database Wizard lets you move or copy databases and their objects easily from one server to another or one instance to another instance, with no server downtime. Using this wizard, you can do the following:
1)     Pick a source and destination server.
2)     Select databases to move or copy.
3)     Specify the file location for the databases.
4)     Create logins on the destination server.
5)     Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.
6)     Schedule when to move or copy the databases.
In addition to copying databases, you can copy associated metadata, for example, logins and objects from the master database that are required by a copied database

       ii. Database Backup and Restore: You can take the backup the database from the previous version and restore it into the new version of SQL Server (SQL Server 2008).

            iii. Detaching and Attaching: To move a database using detach and attach, you should make the following steps:
1)    Detach the database.
2)    Move the database file(s) to the desired location on another server or disk.
3)    Attach the database specifying the new location of the moved file(s):  After detaching, the database will be removed from SQL Server but will be intact within the data and transaction log files that compose the database. You can use these data and transaction log files to attach the database to any instance of SQL Server, including the server from which the database was detached. After attaching, the database will be available in exactly the same state it was in when it was detached.

6. Post Migration testing:
After migration, we need to compare the previous version’s database to migrate database.
Followings are the things that we need to check:

      1)    Check the total no. of tables, stored procedures, views and their names in the migrated database. It should be same as previous versioned database.
       2)     Check the row counts of the tables


  1. Keep up the good work , I read few posts on this web site and I conceive that your blog is very interesting and has sets of fantastic information. 토토사이트

  2. Great post, you have pointed out some fantastic points , I likewise think this s a very wonderful website. 토토커뮤니티

  3. Thanks for an interesting blog. What else may I get that sort of info written in such a perfect approach? I have an undertaking that I am just now operating on, and I have been on the lookout for such info. 먹튀검증

  4. I am glad you take pride in what you write. This makes you stand way out from many other writers that push poorly written content. website

  5. A very excellent blog post. I am thankful for your blog post. I have found a lot of approaches after visiting your post. 메이저사이트

  6. I really like your writing style, great information, thankyou for posting. 먹튀검증업체

  7. Thankyou for this wondrous post, I am glad I observed this website on yahoo. 먹튀검증

  8. That is the excellent mindset, nonetheless is just not help to make every sence whatsoever preaching about that mather. Virtually any method many thanks in addition to i had endeavor to promote your own article in to delicius nevertheless it is apparently a dilemma using your information sites can you please recheck the idea. thanks once more. 먹튀검증

  9. It’s appropriate time to make some plans for the future and it is time to be happy. I have read this post and if I could I wish to suggest you few interesting things or advice. Perhaps you could write next articles referring to this article. I desire to read even more things about it! textbook solutions

  10. Hi there! Nice stuff, do keep me posted when you post again something like this!

  11. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work. nex777

  12. Thanks for taking the time to discuss that, I feel strongly about this and so really like getting to know more on this kind of field. Do you mind updating your blog post with additional insight? It should be really useful for all of us. 토토사이트

  13. Wow, this is really interesting reading. I am glad I found this and got to read it. Great job on this content. I like it. 파워볼사이트


  15. With so many books and articles coming up to give gateway to make-money-online field and confusing reader even more on the actual way of earning money, Tafsir al ahlam

  16. I got too much interesting stuff on your blog. I guess I am not the only one having all the enjoyment here! Keep up the good work. 파워볼게임

  17. The post is written in very a good manner and it contains many useful information for me. 대전마사지

  18. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post. 현금바둑이

  19. This is also a very good post which I really
    enjoyed reading. It is not every day that I have the possibility to see
    something like this.. interpretation of the dream

  20. Wow, What an Outstanding post. I found this too much informatics. It is what I was seeking for. I would like to recommend you that please keep sharing such type of info.If possible, Thanks. 현금바둑이

  21. Thanks for taking the time to discuss that, I feel strongly about this and so really like getting to know more on this kind of field. Do you mind updating your blog post with additional insight? It should be really useful for all of us.

  22. I am unable to read articles online very often, but I’m glad I did today. This is very well written and your points are well-expressed. Please, don’t ever stop writing.


  23. This is also a very good post which I really
    enjoyed reading. It is not every day that I have the possibility to see
    something like this.. Hero Academia Manga

  24. Hey, this day is too much good for me, since this time I am reading this enormous informative article here at my home. Thanks a lot for massive hard work. 먹튀폴리스

  25. I all around use them just five star parts : you will see these individuals during:

  26. This is head, paying inconsequential cerebrum to it might be head so you can take a gander at the going with site:

  27. Yes, I am entirely agreed with this article, and I just want say that this article is very helpful and enlightening. I also have some precious piece of concerned info !!!!!!Thanks. 먹튀검증

  28. I learn some new stuff from it too, thanks for sharing your information. 온라인바둑이

  29. This is very educational content and written well for a change. It's nice to see that some people still understand how to write a quality post! 온라인바둑이

  30. 바카라사이트
    This is my website and it has been very helpful. How did you come up with this brilliant idea? This is one of the sweetest stories and everyone enjoys it very much. I enjoy visiting many websites.
    You have done a great job. Please visit my website and give your opinion.

  31. thank you for your interesting infomation. textbook answers

  32. You know your projects stand out of the herd. There is something special about them. It seems to me all of them are really brilliant! nursing school test bank

  33. thanks for the tips and information..i really appreciate it.. 먹튀검증

  34. Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. There tend to be not many people who can certainly write not so simple posts that artistically. Continue the nice writing 먹튀검증

  35. 바카라사이트
    Hello I am so delighted I located your blog, This is my website and it was very helpful. I keep looking on the web for articles that may be helpful to me. Definitely something to think about about this. It seems that a lot of attention has been paid to the function as well. Please keep writing good stuff.

  36. Admiring the time and effort you put into your blog and detailed information you offer!.. 벳페어

  37. I just want to let you know that I just check out your site and I find it very interesting and informative.. 인디벳

  38. Nice to be visiting your blog again, it has been months for me. Well this article that i've been waited for so long. I need this article to complete my assignment in the college, and it has same topic with your article. Thanks, great share. 인디벳

  39. Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you. detectives privados infidelidades

  40. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article.

  41. I really loved reading your blog. It was very well authored and easy to undertand. Unlike additional blogs I have read which are really not tht good. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he ejoyed it as well! 먹튀검증

  42. You know your projects stand out of the herd. There is something special about them. It seems to me all of them are really brilliant! 먹튀검증

  43. This is central, paying little cerebrum to it might be key with the objective that you can take a gander at the going with site: 카지노사이트