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.
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.
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
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 #SQLChat James Medlin @jmedlinz
Follow up question, do you allow development/QA time in your
initial deadline? How much time is allotted? John Morehouse @SQLrus
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
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
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
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: 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
