Solved

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

Posted on 2013-11-06
3
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

696 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