November 7, 2009 at 3:54 am
Hello,
We have a system which connects to SQL server 2000 database. The database server has almost 100 + scheduled jobs and around 25 DTS packages to generate different types of MIS.It also has transactional replication set with remote servers.The scheduled jobs have many batch files which use isql command.
We are planning to migrate the database server to a new server hardware. Since we have taken a production down time for shifting the databases to a new hardware we are also planning to upgrade SQL server version to SQL server 2008.Since we have many functionalities which are discontinued in SQL Server 2008, is it advisable to go for SQL Server 2008. Or should there be a test migration first on the test server before having it implemented on the production server? what should be the best way to carry out migration? i.e. manually replacing all the discontinued commands in DTS packages and batch files (like isql with SQLCMD).or using migration advisor?we cannot afford any scheduled jobs failing post migration. What all the things we should be careful about while migrating to SQL2K8?
Please suggest the best way to migrate production databases from SQL server 2000 to SQL server 2008?
Thanks in advance.
Regards,
Rohit
November 7, 2009 at 7:21 am
If you are planning to migrate into 2008 directly, then you are inviting disaster.
Let me explain, your application has been running on SQL Server 2000, but your post does not indicate for any testing whatsoever. What is the chances that the same application is going to be running smoothly on SQL 2008, since there have been so many changes on SQL Server side.
First thing you need to do is, let your manager, that we need to set up a test DB Server with SQL2008 and test the application functionalities, try to move the Jobs and DTS and see if they have any issues.
Use Upgrade Advisor and run it on the current SQL2000 System and check for the reports.
You would not believe you will have your hands full of issues to solve before the Migration at which time you can take the Downtime and do it.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 8, 2009 at 7:25 am
Migrating between 2000 and 2008 is quite simple... assuming you've done the testing and validation to ensure that it will be. There are deprecated functions, changed methods, and all sorts of things that you need to test for before upgrading. You ask if you should set up a test system before upgrading your production box, yes. Absolutely, yes. You should be doing this anyway for any change to production, but for something so fundamental as an upgrade across two whole versions and eight years worth of development, heck yes. That really needs testing.
As was mentioned above, run the Upgrade Advisor and fix what it suggests fixing first. Then test the daylights out of the upgrade prior to running it on your production system. If your client is running old versions of ADO or ADO.NET, they need to be tested on the new version. There are changes that can break some functionality.
"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
November 9, 2009 at 5:58 am
I agree that you absolutely must test this upgrade. If you just run it in production without any previous testing you should expect that your applications will not work after the upgrade.
There are likely to be some things you must change in order to upgrade to SQL 2008, but DTS is not one of them. You can install the optional DTS Designer component from the SQL 2008 Feature Pack and run your DTS unchanged. Ultimately you should plan to rewrite your DTS as SSIS, but this can be done at a time convenient to you.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 9, 2009 at 8:07 am
Hi Guys...
This year we have migrated from 2000 to 2008..and I had the joy managing this.
Top of the list is Test, Test, Test.....oh and one other thing....Test
It sounds cliched but there will be something when you upgrade. What you need to do is minimise the impact. This can range from TSQL changes, configuration, DTS, security...you name it...it has be checked. Even if it's a quick tick sheet.
We re-wrote our few DTS packages to SSIS, but we had the time and appropriate Beta environment to do that.
We actually did an inplace upgrade after extensive testing. We detached the databases and associated tasks, jobs and replication. We only had to upgrade the engine then reattach and build all the processes back up in 2008. This worked fine. One thing to remember here is compatibility. If you want to use all the new features make sure your compatibility is set to 100. Leaving it at 80 will however allow your 2000 code to run with less issues. I could go on :)...
I agree with the other guys a straight upgrade between these versions without any testing is the wrong way to go.
HTH
Graeme
November 10, 2009 at 7:47 am
If your DTS packages are not just simple some of them will NOT upgrade to SQL 2008. That would be the very first thing to check to make sure they function as they did in SQL 2008. YES ABSOLUTELY get a test server and test, test, test, test, test, test........ Since you are jumping OVER one release of SQL Server there is even more chance that access paths or something will not function as you expect. Also... one big note... once you migrate your databases run the Update Statistics on ALL user databases before you do anything. The SQL Server optimizer is alot different and you must update stats under SQL 2008.
November 10, 2009 at 12:33 pm
If you can perform a side-by-side upgrade with new hardware then you will be infinitely better off. For a start this allows you to perform multiple cutover tests (and multiple is the key here, get your process and procedure set in stone, setup all your scripts etc and run through 3 or 4 times from there just so you are comfortable). You can also then point test versions of your applications against this upgraded version of the databases.
As regards DTS, I've been slowly cutting over DTS to SSIS packages. Having a separate SQL instance on another server which handles all the jobs has been a huge advantage in this. If you can perform a side-by-side upgrade then you don't have to ditch the old and can just change the connection strings to point to the new server. Again, test greatly beforehand, although I have found far less issues with DTS than I have with any of the applications that needed to be repointed.
November 11, 2009 at 11:36 pm
I will try upgrading today.
However, am trying it out on a stand alone sql instance..No application as such..
January 14, 2010 at 3:48 pm
http://www.sql-server-performance.com/articles/dba/migrating_databases_checklist_part1_p1.aspx
worth reading - then add version 100 to the version 90
There were several more things that I did becaue Microsoft recommended it.
Hope you were successful.
My test server and test Access 2003 with 80 linked tables is working fine. My final migration is end of Jan 2010 over a weekend.
February 18, 2010 at 10:50 am
Thank you for all the great inputs!
I set up a SQL 2008 Test Server and took an old PC for the Access 2003 runtime client.
Wow, there were several "gotchas'!" that I found before conversion day.
The big one is the new 2008 ODBC driver for clients, that comes with the SQL 2008 manager.
But, a little Access VBA scripting to update the connections strings, a few other small issues discovered... and it was a great day.
In fact.... I also found a couple of "unknown" Access databases around the office that I was never told about.
I posted the script for automating all the user names/ profiles on a different post.
Also..
My SQL database was a 2000 version moved over to SQL 2005 a while back. But... they never updated the Parameters! Not sure that all of the great features were being used. And, some 2000 parameters are just not recommended by MS for 2008. I think Torn Page Detection comes to mind.
Bottom line: On Super Bowl Sunday, I had my list of things to do and had a flawless migration. 100% success with every client workstation, Linked Server, and job creation. The users had zero downtime. 😎
Bottom Line: It pays to plan and test.
February 18, 2010 at 10:56 am
Glad to hear things went well. Thank you for the excellent follow-up post too.
"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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply