SQL Clone
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 (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)

Group: Administrators
Points: 277577 Visits: 19896
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1012 Visits: 224
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1188 Visits: 312
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1906 Visits: 1847

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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1906 Visits: 1847
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19594 Visits: 5501
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
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53633 Visits: 12406
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19594 Visits: 5501
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 (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)SSC Guru (277K reputation)

Group: Administrators
Points: 277577 Visits: 19896
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
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41066 Visits: 6562
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