March 12, 2010 at 1:14 pm
Hello Experts,
We are going to be starting a project for migration of one of the existing application that's currently based on SQL server 2000. There are about 15 databases and several DTS packages to pull/push data to/from other sources. These DBs altogether are about 100 GB in size.
Now, what are the things that can be planned and done to make the migration of this server to SQL 2008?
I'm sure sql server 2008 upgrade adviser is going to be one of the main tools for this migration but what are some other tools that can be utilized to make this migration as smooth as possible? Based on others experience with this kinds of projects, what are the suggestions/recommendations to go about it?
Any input will much appreciated...
March 12, 2010 at 1:24 pm
An important one! The code which runs behind .... check with developer, which syntax and T-SQL code are using. It changed for SQL2008, a lot. To mention just one, SQL2008 accepts standard JOINS now, on SQL2000 you were able to use <* or *> for OUTER JOINS. You should rely all the code stuff on the developer otherwise you will have to deal with that as well 🙂 ... but it depends of the division of work on your company.
DTS packages should be re-designed to SSIS.
Another one... schemas and security. On SQL2005/2008 now users do not own objects directly so after migration, you will have to take a look on that but Upgrade Advisor maybe will warn you about it.
Do not forget run DBCC UPDATE STATISTICS, is a must after SQL2000>2008 upgrade and... BACKUP all your SQL2000 database PRIOR UPGRADE!!!
March 12, 2010 at 1:27 pm
Nice link for backward compatibility stuff and possible issues
March 12, 2010 at 1:38 pm
Thanks iamthemanx, yes, it will be joint effort various groups including developers so l'm sure I won't have to deal much with the application code. I can imagine finding issues in application code during testing phase which would have to be fixed by devs.
March 12, 2010 at 1:52 pm
I'm actually researching this right now because one of our core apps is running on SQL 2000 and we're planning on moving it to SQL 2008 in the upcoming months. I'm attaching the technical document I pulled from Microsoft's site last month (can't find direct URL) and it's been invaluable.
Luckily the server we hope to upgrade only has one SQL instance and about 4 Databases, but we do have LOTS of DTS packages I'm working on rewriting into SSIS packages mainly because the person who created the DTS packages was no DBA and wrote most of the processes in nasty ActiveX Script.
Since it'll take me a while to rewrite these DTS packages I'm planning on doing a Side-By-Side upgrade where I install SQL Server 2008 as a new instance on the database server, copy the databases over keeping them in Compatibility Mode 80, then redirecting our app and the DTS packages (running in old SQL 2000 Instance) to use the new databases. Then as I rewrite the DTS packages as SSIS packages I'll slowly pick away at the old SQL 2000 stuff until it's ready to be shut down.
I haven't ran the Best Practices Analyzer or Upgrade Adviser yet, so there may be other fires to put out before I get much further, but the doc I attached seems to cover things pretty well.
Take care, and if you'd like I don't mind us keeping tabs on each others migrations outside of the message forum since it looks like we're in the same boat 🙂
Sam
March 12, 2010 at 3:53 pm
Here is the quick check list for migration.
Have Server guys Disable IIS on Application Servers
Set All user dbs to RESTRICTED_USER
Verify no user connections
Disable SQL Agent
Script out DB_Mail profiles
Script out SQL Jobs
Script out Users/Logins
Script out DB_Mail profiles
Save out All DTS packages
Save out All SSIS packages
Script out, Linked Server connections, Operators, Alerts, Proxies.
Full Backup of all DB's
Remove(Script out) All replication, publisher, subscriber and distributor
Shut down the SQL 2000 box
Copy Install files to new server.
Start SQL 2008 32\64 bit Ent install
Apply latest SP
Restore only user databases (Don't restore System databses)
Run add user connections
Run link_users script
Install DB_Mail profiles from script.
Run script to add link servers
test link servers
Install Operators, Alerts, Proxies from script.
install DTS packages
Install SSIS packages
Install jobs from script
Test at least one job
Run publisher replication script
Run subscriber replication script - If this server is also a subscriber.
re-initialize subscribers.
Run row counts on both publisher and subscriber.
Enable IIS on on application servers.
Test Test Test
March 12, 2010 at 6:23 pm
I'm surprised there's no utility to do the DTS to SSIS conversion? So I really have to re-write the SSIS packages? 🙁
Also the new SQL server 2008 server is going to be on Windows 2008 64bit...does this add any more complexities?
Would I be able to easily extract logins and pwds from 2000 using revlogin script and apply on 2008?
March 12, 2010 at 10:11 pm
Asif-414084 (3/12/2010)
I'm surprised there's no utility to do the DTS to SSIS conversion? So I really have to re-write the SSIS packages? 🙁Also the new SQL server 2008 server is going to be on Windows 2008 64bit...does this add any more complexities?
Would I be able to easily extract logins and pwds from 2000 using revlogin script and apply on 2008?
You can migrate DTS to SSIS in SQL 2008, There are some 3rd party tools also, check this
http://msdn.microsoft.com/en-us/library/cc768544.aspx
Yes, moving logins shouldn't be any problem, make sure you run link orphan users script after moving logins.
March 13, 2010 at 7:04 am
There is a third party tool available for migrating DTS packages to SSIS. Go check out DTS Exchange[/url]. We've been using it and it's a great tool.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 3:50 pm
Thanks sam.alexander for uploading the doc... this is indeed a nice document that can be followed as a baseline for the upgrade.
May 28, 2010 at 1:58 am
I'm glad I've found this thread. I've got about 40 servers with about 230 databases that need to be migrated... and I've got a big mix of service pack levels and different editions to take into account. And, to cap it all, most of the applications that reference these databases are not built in-house...
I suspect these last are going to be the most painful.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply