November 30, 2009 at 8:58 am
I'm a sysadmin that does a lot of support for SQL (with the exception of the "internals" which is handled by our DBA). I'm trying to upgrade SQL 2000 (SP4) to SQL 2005 on a new server. A few notes about the destination:
- I need to keep the same server name (which means I can't have the old and new box online at the same time - at least not with the same name)
- The destination will be running either Windows 2003R2 or Windows 2008
What's the best way to do this? Moving normal databases is easy enough, and I've found info on migrating roles and logins, but my DBA just wants to do an in-place upgrade. He's concerned that otherwise he can't move the legacy DTS packages and he doesn't have the time to rewrite and test them for SSIS. He also raises the spectre of other mysterious things that could go wrong.
I would have figured that something like this was pretty straightforward, so either my DBA is paranoid or I'm just plain wrong. Has anyone else tackled this and if so, what was your approach?
November 30, 2009 at 9:58 am
As you know, no 2 implementations are exactly same, so let me give some of my thoughts.
First no matter what upgrade path you are thinking, testing is a must. In that case what is your testing plan?
I see you are thinking to move to Windows Server 2008. There have been lot of changes in terms of security in Windows Server 2008 and you need to know if the older applications are going to work smoothly in Windows 2008.
Regrading DTS packages, you need not rewrite them in SSIS to begin with.
You can use package migration wizard in SSIS that migrates the DTS packages OR
You can execute the DTS packages inside an SSIS package. Neither of this steps need much time with respect to development / deployment.
Whichever way you decide, I suggest you start by using the SQL 2005 upgrade advisor on the current SQL 2000 machine.
You mentioned that you are upgrading to new servers (new hardware), so what so you mean by in-place upgrade here?
I am always towards a side-by-side migration since it give me an option to test thoroughly and move to the new environment. The old systems will be available at any point of time, if there is failure in actual upgrade and you need to fall back.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 30, 2009 at 3:57 pm
There is a runtime for DTS packages in 2005/2008 that will allow you to run your old packages
December 2, 2009 at 6:42 am
Speaking from a pure paranoia standpoint, I'd want to be able to have both servers online at the same time. That way you can leave your existing system in place and running, upgrade everything on the new system, test it in place, then, when you know that your upgrade was successful, you can switch off the old system.
Have you run Microsoft's Upgrade Advisor against the 2000 system? That will identify all the major issues that you might hit on the database side of things. If you haven't run it, do so, fix the things it identifies. The only way to be sure on the application side is to test, test, test. Be sure after you move the databases (backup & restore is frequently the best method) that you change the compatibility level to 9.0 and that you update all statistics on the databases that you've migrated.
"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
December 2, 2009 at 10:10 am
As well as Grant's recommendations, personally I'd also set the Auto Update Statistics Asynchronously to True if you're using Auto Update Statistics
December 2, 2009 at 11:07 am
I hate in place upgrades. It's a gamble to me, and one that's not usually worth taking. Better to have a second system in place.
December 2, 2009 at 2:56 pm
So, if I understand correctly, the general approach for moving from one server to another is:
- build the second box
- backup dbs on the first
- restore on the second
- run a script to migrate the roles/logins
- install the DTS legacy code on the second box
- migrate the dts packages
Is that generally it? I'm being told that the roles/logins/dts packages are the "messy" part of all of this which is why the in-place upgrade is being pushed. That, and the fact that the default database paths change from one version of SQL to the next and somehow these paths are embedded in other places. I would have figured that a backup/restore would rectify that.
Also, with regards to the server name, is it fairly simple to rename a server with SQL on it? If so, I could use that as an argument to have two boxes up at the same time - I can just rename the second box after everyone is satisfied with the testing.
December 3, 2009 at 5:45 am
thefourthdoctor (12/2/2009)
So, if I understand correctly, the general approach for moving from one server to another is:- build the second box
- backup dbs on the first
- restore on the second
- run a script to migrate the roles/logins
- install the DTS legacy code on the second box
- migrate the dts packages
Is that generally it? I'm being told that the roles/logins/dts packages are the "messy" part of all of this which is why the in-place upgrade is being pushed. That, and the fact that the default database paths change from one version of SQL to the next and somehow these paths are embedded in other places. I would have figured that a backup/restore would rectify that.
Also, with regards to the server name, is it fairly simple to rename a server with SQL on it? If so, I could use that as an argument to have two boxes up at the same time - I can just rename the second box after everyone is satisfied with the testing.
And you need to update the statistics and change the compatibility mode on the databases.
I'm not sure what the problem would be with data paths. You should be able to make them identical and, as you say, the restore will take that into account & fix everything. They might be referring to something else.
Logins are actually very easy. DTS is the true sticking point. Depending on the complexity of the packages, you may have to rewrite them. They won't all work automagically.
Renaming the servers is actually pretty difficult (at least it has been for me, maybe I've been doing it wrong). That might be a sticking point for you. But the fact is, in place upgrades are inherently more dangerous. You're risking serious downtime on the server and I'm a little surprised that's what your DBA wants to do. The simple fact is, the upgrade might screw up. If it does, you're not talking about simply restoring a database, you need to restore the entire system. You can't rely on uninstall. That means your business is offline for X amount of time while you fix the issues. With a side-by-side upgrade, you can do all the work with no down time until you're completely satisfied that everything is tested, good to go & working. Then you make the switch over with very minimal down time for the business. That sure seems like the preferred method to me.
"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
December 6, 2009 at 8:56 pm
DTS is the true sticking point. Depending on the complexity of the packages, you may have to rewrite them. They won't all work automagically.
I don't think this statement is truly accurate to say. As Bru mentioned, you can create an SSIS package to run the DTS package with virtually no coding changes to the DTS package. You'd need to do the following:
1. Save the DTS packages to the new SQL Server, and update destinations connections to point to the new SQL Server.
2. Create an SSIS package to run the "DTS 2000" package you just saved to the new SQL Server.
3. Schedule that SSIS package, effectively running the DTS package, with the only modification being the SQL Server names within the DTS destination objects and where the DTS package lives.
In the end, you have SQL Agent Job > SSIS Package > DTS Package -- all running from and pointing to the new SQL Server.
Although, the general recommendation by many of us here is to re-write them instead of using that method. The reason being that they will never get re-written to SSIS if developers are allowed to continue to use the DTS, resulting in more headaches for us down the road. (Feel like I've had many arguments about this topic in my company, :cool:)
Regards,
Steve
December 7, 2009 at 6:12 am
S.K. (12/6/2009)
DTS is the true sticking point. Depending on the complexity of the packages, you may have to rewrite them. They won't all work automagically.
I don't think this statement is truly accurate to say. As Bru mentioned, you can create an SSIS package to run the DTS package with virtually no coding changes to the DTS package. You'd need to do the following:
1. Save the DTS packages to the new SQL Server, and update destinations connections to point to the new SQL Server.
2. Create an SSIS package to run the "DTS 2000" package you just saved to the new SQL Server.
3. Schedule that SSIS package, effectively running the DTS package, with the only modification being the SQL Server names within the DTS destination objects and where the DTS package lives.
In the end, you have SQL Agent Job > SSIS Package > DTS Package -- all running from and pointing to the new SQL Server.
Although, the general recommendation by many of us here is to re-write them instead of using that method. The reason being that they will never get re-written to SSIS if developers are allowed to continue to use the DTS, resulting in more headaches for us down the road. (Feel like I've had many arguments about this topic in my company, :cool:)
Regards,
Steve
Having several hundred DTS packages that have had to be moved to SSIS, trust me, they don't all move the way you describe. Most do, absolutely, but not all. The one's that do not are usually complex packages that will require time to rebuild in SSIS. Testing, as with all other aspects of the upgrade, is the key. Don't assume everything worked, test it.
"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
December 7, 2009 at 8:26 am
Can't the DTS packages run with the Runtime in 2005 without modification?
December 7, 2009 at 8:31 am
Steve Jones - Editor (12/7/2009)
Can't the DTS packages run with the Runtime in 2005 without modification?
Not everyone we tested. A few barf. Usually when they involve scripting.
"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
December 7, 2009 at 8:53 am
Steve Jones - Editor (12/7/2009)
Can't the DTS packages run with the Runtime in 2005 without modification?
Basic ones work well Steve, but there have been problems when the DTS packages had Activex Scripting tasks etc.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply