October 23, 2017 at 2:54 pm
Hi everyone
I have about less than 30 days to migrate move a Databases.
I wanted to get some advice/suggestions for SQL 2005 (yes no support) => 2012 migration... If there are other things links the group can share I'd love to read them.
I've moved servers before but usually have more time... so I don't want to miss things. I am most concerned about SSIS due to the number (200+)
SSIS reads/loads data from the current set of hosts (SQLDEV,SQLUAT, SQLPROD1)
We have 4 existing servers (dev, uat. prod+stand by) , and over 200 SSIS ETL packages that work with those servers. These hosts include Linked Server as well.
e.g existing: SQLDEV, SQLUAT, SQLPROD1, SQLPROD2
new: HSQLDEV01, HSQLUAT01, HSQLPROD01, HPSQLPROD02
We have a SSIS ETL server is not being upgraded.
We also have a request to run these in parallel.. Moving the data, I plan to place databases in on the "NEW SERVERS" apply transaction logs continuously to keep them in sync is what I'm thinking so far. An alternative is a full file copy of all databases... and have a 'down time' .. it would have to be a window of several hours... 12+ maybe.. (I'l have to come with estimates)
However that out as that negates "option 1" below
It also requires 2 sets of SSIS packages... one for new hosts one to continue running for the old hosts.
What do people suggest for migrating SSIS ETL packages to re-point to the new server.
Options I have thought
1) Re-name the the new hosts to the old name to preserve during "Go Live"
(swap new host) HSQLPROD01 => SQLPROD1 (use old name)
That means no changes to SSIS packages.
** but that negates ability to run side by side in parallel...
2) Go through all 200+ packages and change the connections.
I might be able to have developers own some of the work and modify *only those packages they own..
But there might be more risk giving devs ability to make the changes...than if I manually changed each one... in the end I'm still supporting that they run smoothly in prod.
There isn't an easy process, as we are not using SSIS 2012 SSIS DB method... open to suggestions.My solution for Linked Servers I can create a 'datasource' so any stored procs or code using same old linked server via openquery will continue to work.
Thanks for help, figure more 'minds' is better than my one
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
October 24, 2017 at 2:32 pm
That sounds like a short window for the upgrade.
What I would do is build an upgrade plan and rollback plan on paper first and then run through that on one of your dev instances and see what happens.
Upgrading your SSIS packages from 2005 to 2012 I wouldn't expect to be a problem as that is within the 3 version window and thus should be a "supported" upgrade path. I put it in quotes as SQL 2005 is no longer supported.
Since you are keeping your old servers online, you have lower risk during the upgrade process. You can spin up the NEW SSIS server and get things migrated over with no production impact. Once the SSIS packages are migrated to the new host, I would schedule a window to test-run them. I am confident they will run successfully if they did on the old server, but I would still test all of them as there could be some weird hiccups.
If you can switch to the SSIS catalog method, I think you will be much happier long term. Initial setup will be a pain, but having environments (essentially a set of variables that you can apply to multiple SSIS packages) will save you TONS of time with future updates. Imagine you have a SQL Instance SQLDW and that is your data warehouse and all 200 SSIS packages use it in some form. You want to upgrade it to a newer SQL version but keep the old one around so you have shorter downtime so you make an instance SQLDWv2 and you want your ETL load to run against that. With the old method, you need to touch each package to point to the new SQL instance. With an environment, you'd go into each environment (we just have one big one on our live ssis server) and change the one line and all of the packages using that environment would be changed at once.
As for migrating your actual data, that one is a bit more of a headache. But since you have new servers (right?), the way I would do it is spin up the new instances, create all of the instance level objects (logins, roles, etc) and wait for a downtime window and in that downtime detach the old databases and attach them to the new one. That is how we did our upgrades. For us, all of our SQL user databases were on our SAN so we got our IT department to detach the old disk from the old server and attach it on the new one so the downtime was very small. In the event things went badly, we had our backups to run back to.
The biggest things I'd take away from this are:
1 - make an upgrade plan
2 - make a rollback plan
3 - run the plan through step by step on test/dev
4 - have a backup person who can follow your document
The reason why I recommend that is lets say that the plan is to start the upgrade tomorrow morning at 7:00 AM. At 6:30 AM you go to start your car and get nothing. So you try working from home (VPN) and your internet cuts out. You NEED to take the day off unexpectedly. Nothing you can do about it. If you have a good document AND a backup person, somebody can take over and do the upgrade for you in your absense.
Your plan of applying tlogs would work as well, but puts more pressure on you. Having downtime is (in my opinion) a preferred option and produces the least risk. Well, assuming that everyone stays out of your databases. If you can get the downtime, I strongly encourage you to put things in single user mode or take the databases offline as soon as you are able to. Nothing worse than expecting that you are the only one in there and getting errors detaching the database because some idiot decided your "outage" email didn't apply to them.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 25, 2017 at 12:13 pm
There are some things to be aware of when upgrading the packages since not everything does get upgraded. A couple I can think of would be configurations and Execute Package tasks. There are several documentations on the upgrade you are doing which also address those issues I mentioned and suggest some upgrade processes.
A few documents, article you may want to check would be:
Upgrade 2005/2008 SSIS Packages to 2012 Like a Boss!
5 Tips for a Smooth SSIS Upgrade to SQL Server 2012
Upgrading Packages to SSIS 2012
Sue
October 26, 2017 at 1:13 pm
bmg002 - Tuesday, October 24, 2017 2:32 PMThat sounds like a short window for the upgrade.
What I would do is build an upgrade plan and rollback plan on paper first and then run through that on one of your dev instances and see what happens.
Upgrading your SSIS packages from 2005 to 2012 I wouldn't expect to be a problem as that is within the 3 version window and thus should be a "supported" upgrade path. I put it in quotes as SQL 2005 is no longer supported.
Since you are keeping your old servers online, you have lower risk during the upgrade process. You can spin up the NEW SSIS server and get things migrated over with no production impact. Once the SSIS packages are migrated to the new host, I would schedule a window to test-run them. I am confident they will run successfully if they did on the old server, but I would still test all of them as there could be some weird hiccups.If you can switch to the SSIS catalog method, I think you will be much happier long term. Initial setup will be a pain, but having environments (essentially a set of variables that you can apply to multiple SSIS packages) will save you TONS of time with future updates. Imagine you have a SQL Instance SQLDW and that is your data warehouse and all 200 SSIS packages use it in some form. You want to upgrade it to a newer SQL version but keep the old one around so you have shorter downtime so you make an instance SQLDWv2 and you want your ETL load to run against that. With the old method, you need to touch each package to point to the new SQL instance. With an environment, you'd go into each environment (we just have one big one on our live ssis server) and change the one line and all of the packages using that environment would be changed at once.
As for migrating your actual data, that one is a bit more of a headache. But since you have new servers (right?), the way I would do it is spin up the new instances, create all of the instance level objects (logins, roles, etc) and wait for a downtime window and in that downtime detach the old databases and attach them to the new one. That is how we did our upgrades. For us, all of our SQL user databases were on our SAN so we got our IT department to detach the old disk from the old server and attach it on the new one so the downtime was very small. In the event things went badly, we had our backups to run back to.
The biggest things I'd take away from this are:
1 - make an upgrade plan
2 - make a rollback plan
3 - run the plan through step by step on test/dev
4 - have a backup person who can follow your document
The reason why I recommend that is lets say that the plan is to start the upgrade tomorrow morning at 7:00 AM. At 6:30 AM you go to start your car and get nothing. So you try working from home (VPN) and your internet cuts out. You NEED to take the day off unexpectedly. Nothing you can do about it. If you have a good document AND a backup person, somebody can take over and do the upgrade for you in your absense.Your plan of applying tlogs would work as well, but puts more pressure on you. Having downtime is (in my opinion) a preferred option and produces the least risk. Well, assuming that everyone stays out of your databases. If you can get the downtime, I strongly encourage you to put things in single user mode or take the databases offline as soon as you are able to. Nothing worse than expecting that you are the only one in there and getting errors detaching the database because some idiot decided your "outage" email didn't apply to them.
Thanks for all the suggestions/Advice! It's nice to have people on the sqlcentral community give their opinions and perspective.
I've written a prelim build plan for the dev instance which I'll apply to uat and prod. I know I will probably miss things and need to revise as I go. I also wrote out on paper a calendar also of "tasks" to visually see the 'path'
I'll have to perform a network file copy as we do not have a SAN (physical servers) so going with the suggestion of "Downtime" would like to trim down the size of the file copies. Appreciate the suggestion, it would have added more complexity to log ship.
I really like to suggestion of a well documented (I to do this for recovery processes...as ppl are usually under stress at that time..can make mistakes), regardless of who performs it you're right 'never know' ... plus least amount of brain power needed the day of the better to simplify the process.
Thank you! Will keep you posted
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
October 26, 2017 at 1:15 pm
Sue_H - Wednesday, October 25, 2017 12:13 PMThere are some things to be aware of when upgrading the packages since not everything does get upgraded. A couple I can think of would be configurations and Execute Package tasks. There are several documentations on the upgrade you are doing which also address those issues I mentioned and suggest some upgrade processes.
A few documents, article you may want to check would be:Upgrade 2005/2008 SSIS Packages to 2012 Like a Boss!
5 Tips for a Smooth SSIS Upgrade to SQL Server 2012
Upgrading Packages to SSIS 2012Sue
Thanks Sue on those links! I will have to read through those, my plan is to start upgrading packages starting next week those docs will be useful! I am a little worried about all the variables and potential issues I may encounter. Including potentially 3rd part ssis tools embedded in developer packages... Hopefully those will be in the minority.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply