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 😀