Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-11-06
3
Medium Priority
?
397 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 1000 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 1000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

810 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