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

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.
goprasadAsked:
Who is Participating?
 
BanthorCommented:
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
 
goprasadAuthor Commented:
Please advise SQL experts
0
 
PadawanDBAOperational DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.