Solved

Migrating 60 DB's from SQL 2005 with Compatibility levels of 80 and 90 to SQL 2008

Posted on 2013-11-06
3
357 Views
Last Modified: 2016-02-11
Hello there,

I am planning to migrate 60 x DB's from SQL 2005 with Compatibility levels of 80 and 90 to SQL 2008.

Will there be any issue? and how should I be going about planning the migration successfully?

Please advise.
0
Comment
Question by:goprasad
3 Comments
 

Author Comment

by:goprasad
ID: 39629132
Please advise SQL experts
0
 
LVL 10

Accepted Solution

by:
Banthor earned 250 total points
ID: 39629251
Data is data, but the applications would be the issue. Since MS SQL server is very backward compatible I have never run into issues.
Just script out all the objects into separate files then test those objects against the new target Server.

Do the same with the data. I use either SSIS or BCP but always create the tables first then fill from source, This will assure the correct collations.
There is a subtle difference in the new collations so be mindful if you are using unicode or Accented languages.
0
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 250 total points
ID: 39630760
From a purely technical standpoint there should be no issue upgrading from 2005 to 2008 with compatibility modes 80 and 90.  If you were moving to SQL Server 2012, you would have issues with the dbs that were compatibility mode 80 (but you could change that prior to migration and would be fine).  

As for what Banthor said, he is absolutely correct.  Most of the work in a migration scenario is finding what connection strings will need to be changes/applications that reference the database.  You'll also want to get your logins and permissions scripted out prior to the move.  If you are using mixed mode authentication and have logins that use sql server authentication, you'll want to use Microsoft's login migration sps to ensure that the SIDs are identical on the new server (so when the db's restore/attach the users for that database map to identical logins as the old environment, otherwise you'll get a case of orphaned users that need to be deleted before you can create the user accounts in the databases).  The MS sp's are here: http://support.microsoft.com/kb/918992.

You'll also want to make sure that the service accounts under which the various SQL Server services run under are either the same or that you find all remote locations that these service accounts will require permissions to are modified appropriately (ie. file shares for transferring backups).

Also, dry runs are never a bad idea (I tested out the migration plan for our 2005->2012 migration a dozen times or so to ensure that everything was working - replication, ssis packages, sql server agent jobs, batch processing applications, etc.)  The devil is in the details, so if you have any specific questions please ask and I'm sure myself and others would be more than happy to help further!

Edit: I would also encourage you to take a look at a question I answered (with a different account) on the methods for migration: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28121746.html#a39148501
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now