March 7, 2017 at 9:44 pm
Comments posted to this topic are about the item The Migration Checklist
March 8, 2017 at 2:16 am
Steve, this is a good list. I will keep it for future reference!
When you mention "side-by-side" migration I assume you are using the same host name for the new server and SQL Server instance?
For me, this has been the biggest challenge in these type of migrations before.
In virtual environments you can setup private networks to isolate the new server from the production network.
But sometime there are many third-party applications that must be tested before the new server can go on-line.
Some external systems also use hosts files where the IP address/host name is hard-coded.
I am looking forward to see the responses on this forum.
March 8, 2017 at 6:21 am
Checklists are one of the most powerful tools in our arsenal of methods and processes. It would be good to create a resource area for them.
Thanks!
March 8, 2017 at 10:51 am
Over the last 2 upgrade cycles (2012-2014 and 2014-2016), I've developed checklists that I follow. They're more fleshed out than your list, and much more tailored to my environments.
However, each upgrade has been different too. E.g. in 2014, the cardinality estimator changed, so I paid more attention to particular queries & jobs that might be affected. In 2016, SSRS & SSAS tabular changed quite substantially, plus Cozyroc changed a ton, so a lot more time was spent upgrading & configuring those components than before. So I simultaneously feel that (a) my checklist wouldn't be comprehensive for the next upgrade, and (b) there are things on my checklist that might be overkill and I could safely remove them now I'm already on SQL 2016.
I would love to see better checklists, along with reasons why a particular thing should be checked. For example, a lot of people suggest updating all statistics after an upgrade. I can see the logic in this. But, updating all statistics can take 5+ hours. Should I really do that as part of the upgrade? Is it safe to bring systems back online, then do this the next night? It's easy to recommend "update all statistics" but more contextual information would help when faced with the practicalities of fitting that into an upgrade window.
I'm in two minds about in-place vs. side-by-side upgrades too. Side-by-side does allow for a new O/S, new hardware, etc. But, it also increases the risk. Suddenly I have to migrate a ton of things that I wouldn't otherwise have to worry about (operators, alerts, linked servers, agent jobs, SSIS packages, file/folder permission quirks, firewall settings, SMTP permissions, master keys, certificates, application connection strings, etc.) Obviously it's good to know about all these little things (in case of a real disaster), but it does add a lot of overhead to an upgrade (and a risk that something goes wrong not because of the new version, but because of the new server).
Another upgrade question I've had recently is regarding cumulative updates. There's no clear checklist for those. A full version side-by-side upgrade seems overkill for a cumulative update. Even, for example, the SSISDB catalog. Do I need to update the catalog for every cumulative update? Or is it ok that SSISDB catalog is on SP1 CU0, yet I've applied CU2. I haven't been able to find a clear answer.
I'll just end by saying that upgrades are not the most favourite part of my job. 😀
Leonard
Madison, WI
March 8, 2017 at 10:55 am
On a side note, this is one resource I used as a starting place when building my own: https://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
Leonard
Madison, WI
March 9, 2017 at 7:26 am
Stefan LG - Wednesday, March 8, 2017 2:16 AM...When you mention "side-by-side" migration I assume you are using the same host name for the new server and SQL Server instance?
For me, this has been the biggest challenge in these type of migrations before.
In virtual environments you can setup private networks to isolate the new server from the production network.
But sometime there are many third-party applications that must be tested before the new server can go on-line.
Some external systems also use hosts files where the IP address/host name is hard-coded...
A technique I've used over the years to handle the side-by-side same name situation, is to have an extra DNS entry that clients use to connect to instead of the physical server name itself. Then, once the new server has been setup and tested, and you're ready for cutover, it's just a matter of changing the IP address of that extra entry. Physically changing the name of the box that SQL Server is running on can work too, but I find the extra entry a bit more elegant.
I've only worked at one place that clients connected to the SQL Servers by IP address, and while that was only 7 years ago it seemed so... 1990's. I remember we created Aliases on the clients in SQL Server Configuration Manager to help deal with that situation.
March 9, 2017 at 12:44 pm
Does anyone know of a SQL Server schema comparison tool that will script out server level settings, permissions, and objects? From what I've seen, RedGate SQL Compare or Microsoft SQL Server Data Tools only compare at the database level along with logins.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 9, 2017 at 1:47 pm
Probably the only thing I'd change would be the order of a couple of things to make sure dependencies between those items are met. For example, in my environment there are a number of SQL Agent jobs that use the DB Mail profiles, so I'd ensure DB Mail profiles and accounts were setup before SQL Agent operators, jobs, and alerts.
March 10, 2017 at 8:43 am
Eric M Russell - Thursday, March 9, 2017 12:44 PMDoes anyone know of a SQL Server schema comparison tool that will script out server level settings, permissions, and objects? From what I've seen, RedGate SQL Compare or Microsoft SQL Server Data Tools only compare at the database level along with logins.
Only thing I know of. Not sure how well it works: Omnicompare
March 15, 2017 at 7:13 am
It'd like to see a Business Prerequisites and an Environment section. Business Prerequisites are for things like licences (I have seen production servers withdrawn when management discover that they are not properly licensed) and Environment for actions external to the SQL Server host machine e.g. firewall settings (which Leonard mentioned).
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply