Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Migration Checklist


The Migration Checklist

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: Administrators
Points: 52356 Visits: 19006
Comments posted to this topic are about the item The Migration Checklist

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Stefan LG
Stefan LG
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 112
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.
Robert Sterbal-482516
Robert Sterbal-482516
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 247
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!
phonetictalk
phonetictalk
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1669

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. BigGrin



Leonard
Madison, WI
phonetictalk
phonetictalk
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1669
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
Chris Harshman
Chris Harshman
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4043 Visits: 3780
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.

Eric M Russell
Eric M Russell
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9749 Visits: 10359
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Chris Harshman
Chris Harshman
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4043 Visits: 3780
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: Administrators
Points: 52356 Visits: 19006
Eric M Russell - Thursday, March 9, 2017 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.

Only thing I know of. Not sure how well it works: Omnicompare

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gary Varga
Gary Varga
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13703 Visits: 6467
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!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search