Thursday, November 17, 2016

SQL Server Migrations Lessons Learned

With the recent announcements surrounding all the wonderful things available in SQL Server 2016 SP1, I couldn't help but think about migrating my servers NOW.  I must be crazy, right?  I managed a migration from SQL Server 2005 to SQL Server 2014 in March of this year.  It's only been 8 months.  Don't I ever learn?  Actually it turns out, I do.  I learned a lot.  I was asked to share that experience with Idera via their April #SQLChat on Twitter.  

What is #SQLChat? I'm glad you asked.  If you already know, skip ahead to Question 1.  Still here? Good.  #SQLChat is an Idera Software sponsored SQL Community conversation that takes place entirely on Twitter.  A series of questions are asked & answered on a predecided topic.  There's a host (in today's example, I played that role) that came up with the questions & solicits answers. There's the @Idera_Software twitter account that poses the questions.  There's a dozen or more SQL professionals that help answer these questions,  Finally, there are other SQLFamily twitter users that ask follow up questions.  It's less Q&A and more conversation. 

Here's what I did. What worked for you? 
On that note, I need to know a way to solve X. 
Here's a solution that might work. 

If you use TweetDeck or similar Twitter Interface Apps, trying following the #SQLChat hashtag.  If you can participate this month, great!  If not, read over the questions & answers at your leisure.  I promise, you'll learn something every time.

Q1: What are your deciding factors for migrating/upgrading #SQLServer and to what version? #SQLChat

My thoughts:  Would upgrade benefit the business? Are new features worth the time/effort/money? While some companies wait as late as possible, right up until End of Life for a product, others chose to be proactive. How does this benefit us? Is the payoff worth all the time, effort, planning, money, etc? This answer can vary by company and project.

I really loved the answers my SQL colleagues came up with.  They asked some great follow up questions & had great advice.  Answers varied on when they’d make the decision, what the driving factors would be, how they’d handle it, etc.  I’ve compiled some of those here for you.

Am I currently on a supported version? If not, time to upgrade.  Rob Volk @sql_r
"Do you wait for engine to seize before changing oil & filter in car?" ;) #sqlchat Rob Volk @sql_r

Business decision + vendor app support. Go to latest/greatest app will run on, preferably 2012 or 2014. #SQLChat -- Paul Timmerman @mnDBA

If I need\want to use newer features… If I have available licenses (we dont have an EA) #sqlchat – Monica Rathbun @sqlespresso

@Idera_Software How high of a SQL version will the related software support?  --Dave Mason @BeginTry

@Idera_Software Waiting til the last minute can lead to panic, mistakes AND loss of compliance certs #sqlchat  -- Rie Irish @IrishSQL

New features/functionality benefits, whether the system is covered under SA, and highest ver w/ app support. – Jamie Wich @Jamie_Wick

Q2: How do you decide your timeline from initial planning to push to production? #SQLChat

My thoughts:  We actually started with the date we wanted to Go Live & then built our calendar backwards from there.  We had to leave in a little time to spin tires & get stuck in the mud, but that Go Live date was the goal line.  It wasn’t moving.  To accomplish this, we had to do a few things.  We had to get buy in from sales & executive teams.  I’ve found if those guys, the ones with their eye on money want something, it usually happens.  Try to have someone on the development champion the change from inside. This will help drive them to reach their dates.  Get the migration put on their project roadmap early.  This will help guide them when they’re choosing which project to work on.  The one that’s already on the roadmap, of course!  Some people felt they didn’t have a voice in when the change happened but they seemed to keep their sense of humor about it.

Upper Management dictates most of the time .. and I just have to hit the mark #sqlchat – Monica Rathbun @sqlespresso

What date did the PM pull out of thin air and tell everyone else before consulting DBAs?  -- Bob Pusateri @SQLBob

I've never been a decision maker for upgrade timeline. I'm lucky to find out before it happens. – Rob Volk @sql_r

In my pharma jobs, I got RDBMS classified as infra, which gave us more flexibility for upgrading – Joey D’Antoni @jdanton

Regardless, the timeline always ends up shorter than you'd like  James Medlin @jmedlinz

Follow up question, do you allow development/QA time in your initial deadline? How much time is allotted? John Morehouse @SQLrus

Q3: Who do you get involved in the project? What teams have a say? Who leads? #SQLChat

My thoughts:  We started with management.  Not just executives, but all levels of management. We met with Director of Development to discuss what we wanted to do.  He was immediately on board and so was the people on his team doing the work.  We're a Software as a Service shop, so we aren't a vendor shipping out software.  These changes were going to be in-house. 

We identify 3 of our clients each release for a 3 month BETA test. Onsite engagements as well at that time.  --Jim Donahoe @SQLFlipFlopsDBA

Again, I'm not usually a decision maker. I'll add DEV, operations, support if they're not included by mgmt. – Rob Volk @sql_r

Collaborative effort. As a DB architect, we usually lead the charge but definitely get other teams involved. – John Morehouse @SQLRUs

A3: DBA Team is brought into project as a resource. We drive discussions around version of SQL Server. #sqlchat  --Paul Timmerman @mnDBA

A3 It Depends! Totally on the scope of the project, who gets most benefit, etc. #sqlchat -- Mike Essen @AtlSQL

Q4: What's the one thing you'd change or do differently with your next migration? #SQLChat

My thoughts:  So this is the easiest one for me to answer.  Frankly, this blog post may not have enough space to hold what I’d change or do differently.  First, have a project manager.  A good PM will think of all those details your techie brain won’t.  She’ll make sure people are staying on task & help identify what isn’t getting done.  Second, create a flight plan for the actual migration.  I learned this lesson many years ago and it’s stuck with me.  We create an excel spreadsheet with a row for every “to do” item, from building servers to restoring DBs for mirroring.  We provide an estimated start & end time, the person responsible, how we test it, etc. It might seem too detailed but trust me, this attention to detail pays off.  And this should be a living document.  Don’t be afraid to rewrite steps, times, etc. as you move through Staging, UAT and into Production.  Practice.  Repeatedly.  Until you’re sick of it.  Make sure you all know the steps to the dance.  A migration should be like a well-choreographed Broadway show.
Once again, my SQL colleagues didn’t let me down.  They came up with some great advice based on their experience.  Again, they maintained their sense of humor.

A4: Practice. Automate. Practice again. Automate more. Refine until I don't need to be there. #SQLChat –Rob Volk @sql_r

A4: Does "go back in time 5 years and do something differently in the first place" count as an answer? #sqlchat  -- Vicky Harp @VickyHarp

A4:To let somebody else manage it! HA Just to practice and practice until it goes smoothly with little interaction  -- Jim Donahoe @SQLFlipFlopsDBA

This! Having template processes helps a ton. Saves us a bunch of time. – Paul Timmerman @mnDBA

Not having to deal w/ multiple domains would be nice. :)  -- Dave Mason @BeginTry

Q5: Did you do a rolling migration or did you plan downtime? #SQLChat

My thoughts:  We chose to do a rolling migration on the databases but turned off client access to the applications.  Argenis Fernandez (T|B) gives a session on Rolling Upgrades that really cemented this as a choice for us.  The downside in using this method to upgrade versions is rollback.  Once you bring up the new version (with mirroring); you can’t fail back.  If you’ve turned off apps like we did, you wouldn’t lose transactions by bringing up the old DB though. We spent the week, days & hours leading up to the migration restoring backups.  First, restoring the full backup from the prior weekend, next the differential from the night before and finally a day’s worth of t-log backups.

Based on feedback on this question, most people agreed with this method.

A5: I've done both rolling upg. and planned downtime. Downtime is always an option, whether you plan for it or not #SQLChat – Rob Volk @sql_r

A5: I also always try to do rolling upgrade to new hardware/environment, easier and faster to roll back if needed. #SQLChat – Rob Volk @sql_r

I prefer a rolling migration with as little downtime as possible. Time = money usually. #sqlchat – John Morehouse @SQLRUs

A5: AGs are rolling migration. Stand-alone have planned downtime. Unsupported dbs are migrated off at upgrade. #sqlchat – Jamie Wick @JamieWick

A5: Rolling migration with downtime only for last log application. Standard process. #SQLChat – Paul Timmerman @mnDBA

Q6: What role did High Availability play in your decision to migrate? #SQLChat

My thoughts:  Most people agreed that moving to High Availability played a large role in their decision to migrate.  I work for a company that has high transactions with 24/7 uptime.  We needed to modernize the infrastructure and increase availability.  Moving to Availability Groups provided a sense of security & stability that execs required.  Just 2 weeks after migration from 2005 to 2014 (virtualized), we experienced corruption in our main production database.  These things never happen at a reasonable hour, so in the wee hours on a Sunday morning, I was able to use the readable secondary on the AG to repair the damaged data.  It took me a couple of hours, running select statements that attempted to read from the damaged pages, but with each error SQL Server replaced the damaged/missing data with corrected data.  What a payoff!  To be able to go to the executive team at work and say “We had an issue over the weekend that could have resulted in lost client data, but because of the investment YOU made in our infrastructure, we didn’t.”

A6: Huge! Recently were moving from SQL Server 2005. More confidence in newer renditions of HA features. #SQLChat – Paul Timmerman @mnDBA

A6: The new HA features of SQL definitely will play a role. #sqlchat  -- John Morehouse @SQLRUs

A6: HA is major factor for most prod dbs. #sqlchat – Jamie Wick @JamieWick

A6: None, Mirroring & Log Shipping have been around forever. Haven't ventured into AG yet. #sqlchat – Mike Essen @AtlSQL

A6 my biggest problem with AG is WFC requirement. Multiple servers, physical clusters, diverse geography. #SQLChat – Scott @ppcx