Solved

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

Posted on 2013-11-06
3
362 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 84
Abstract Express Replacement Software 12 22
Loop to go backward 90 days 2 18
Query Syntax 17 31
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

815 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

7 Experts available now in Live!

Get 1:1 Help Now