Published on February 8th, 2015 | by Warner Chaves1
Best Practices for a SQL Server 2014 Upgrade
Raise your hand if you’ve ever heard this line “I don’t install Microsoft products in production before a Service Pack has come out”. Unfortunately, a lot of people in the industry still take this position with products like SQL Server and end up missing out on great new features and improvements that could make a big difference in their applications. There are simply too many benefits:
– If you’re in SQL Server 2012: you’re potentially missing out on delayed durability, buffer pool extensions, new cardinality estimator, In-Memory OLTP, updateable Columnstore indexes, Resource Governor for IO, incremental statistics, parallel select into, backup encryption, data quality services, distributed replay and more that I’m forgetting.
– If you’re in SQL Server 2008: you’re potentially missing all of the above plus Availability Groups, backup to URL, cross cluster migrations, non-clustered Columnstore indexes, paging, sequences, and more that I’m forgetting.
As we keep going back there’s even more and more stuff you’re missing of course!
And let’s make this very clear: if you’re in SQL Server 2005 you’re running out of time before you’ll be completely unsupported. Extended support for SQL Server 2005 is done in April 2016, the clock is ticking for you to get this done ASAP! If you’re in SQL Server 2000 then you’ve been playing russian roulette for years now, completely unsupported and you don’t want to wait until the day your lucks run out. Not much more to say about 7.0 and 6.5, I know there are still some in production out there and I shudder at the thought of being a DBA having to deal with them.
OK I’m convinced, so how do we get to 2014
At this point SQL Server 2014 is already up to Cumulative Update #5 (as of the time I’m publishing this) and I very strongly advise that if you’re planning a SQL Server upgrade that you forget about 2012 and go straight to 2014. Your licensing cost will be roughly the same, you’ll get the new features and the code base is very similar to 2012 so there’s very little added risk by skipping 2012.
Now, I understand upgrade processes can be very complex and risky depending on the environment so having an air tight migration plan is the key to minimize that risk. And in that plan you want to follow the best practices I’m going to mention here.
Remember, this is not an upgrade step by step, it’s a list of best practices. Now, let’s get on it!
List of Best Practices for doing a SQL Server 2014 Upgrade:
1) Run the 2014 Upgrade Advisor
You can get the SQL Server 2014 Upgrade Advisor from here. You want to let it run the analysis against your existing system and make note of all the recommendations. Now, the advisor will analyze your T-SQL that lives inside the database but what about the T-SQL that might be coming from applications? That leads us to…
2) Run a profiler to analyze on Upgrade Advisor
With a profiler trace you will be able to trap the application code that lives outside the database. You want to trace during an entire business cycle so you don’t miss any queries. At the same time, if it’s a busy production system, you want to be smart enough about your tracing so you don’t impact performance too much. In regards to what columns and events you should trace, Nacho Ignacio Portillo over at Microsoft made a blog post about it.
3) Test the database but ALSO TEST THE APPLICATION
Sure you have to make sure that the database queries are all running OK on the new version, but you need to do an end to end test. It doesn’t matter if you’re not in charge of the application, as a DBA the upgrade is YOUR responsibility. Make sure that you have application folks available (or you know the application well yourself) that can run a test copy of the application and verify all the use cases.
Many times the database queries run just fine but some kind of dependency on the application is not compatible and your entire upgrade plan must be revised. You don’t want this to happen on the day you’re supposed to go live on the new version! If the application was not developed in-house then make sure the vendor will support SQL Server 2014. And before you go any further: get written sign off from the vendor or your in-house dev team that they have certified it as OK.
4) Do not do an in-place upgrade
If you’re upgrading a production system then don’t do an in-place upgrade. The risk is too high for something to go wrong and then best case you need to restore a server image, worst case you need to rebuild a server from backups. And this leads us to:
5) Do the upgrade side-by-side, on a separate system
Side by side will have lower downtime and faster rollback in case things go wrong. Now, you might be the most prepared DBA in the world but your plans must ALWAYS ALWAYS ALWAYS have a rollback plan. If you’re doing a side by side upgrade then the rollback is very simple: we stay on the older server. Side by side also gives your more granular control over the entire upgrade process.
For example, you can selectively pick some databases or SSIS packages to upgrade first and then do lower priority ones later. Another advantage of side-by-side: you can compare execution plans from the new version to the old in case you’re seeing any regressions (specially with a new cardinality estimator in 2014!). You can use many high availability techniques to minimize downtime when you switch from one system to the other, we’ll mention some further down in this post.
6) Do not deploy anything but the 64 bit version
Sql Server 2014 is available in 64 bit and STILL available in 32 bit if you want it. There is NO REASON to deploy a production SQL Server in 32 bit in the year 2015. None. I understand some legacy systems might have 32 bit dependencies and might have issues running in 64 bit in 32 bit mode. I would advise then that you need to figure out how to remove those dependencies or start to think about upgrading or making those applications end-of-line.
7) Verify your target server configuration is valid for your SQL Server version
Quick example: if you’re upgrading to SQL Server 2014 Standard then make sure your target server doesn’t have more than 16 cores or more than 128GB of RAM. I know this sounds kind of obvious, but I still find cases in the field where a server was built with specs higher than the supported edition of SQL Server and once it has gone live the business never finds a time to repurpose those resources (if possible).
8) Perform an inventory of all your instance and database configurations
You want to be specially thorough with reviewing all settings that are not default. The best example here is the instance and database collation. If you’re using a specific collation and then do a new install and forget to pick it then you’ll create the new instance with the default collation. If you don’t catch this in time you will end up with new system databases with a different collation than your user database which can be a headache down the line (for example, mixing user data with tables on tempdb).
9) Create all your maintenance jobs in the new server ahead of time
As soon as the upgrade is completed your maintenance jobs should be ready to go. This is critical for the backups since you want to have those done as soon as the upgrade is complete. The worst scenario would be completing an upgrade, not kicking off the backups immediately and then being unlucky enough that your server crashes and you have to do everything all over again. On top of backups I also recommend doing a full update stats right after the upgrade.
10) You HAVE to do a dry-run of the upgrade process
I don’t care how airtight you think your plan is, you need to do a dry-run for it. Get everyone on board and run the entire process end to end. Document how long it took for each step and any issues you had to work around. Never go into a production upgrade with a plan that is only on paper and was never actually tested. The dry-run will also let you estimate the real upgrade maintenance window time with much more accuracy.
11) You want to deploy SQL Server 2014 on Windows Server 2012 R2
You can deploy SQL Server 2014 on Windows 2008 R2, but just because you can doesn’t mean you should. There are many improvements between 2008 R2 and 2012 R2 in general, but specially if you’re going to be using clustering or Availability Groups.
12) Make a baseline of your original system
You should be doing this regularly anyway but in case you’re not, you definitely want to do it before an upgrade. Make sure you catch all the hardware related counters (CPU, disk, RAM, network, etc) and also SQL related ones (waits, batches per sec, PLE, etc). In the event of performance issues shortly after the upgrade you can easily throw a perfmon on the new server and compare with the numbers you have from the original one.
13) Plan a high availability strategy to minimize downtime
There are many different options for doing high availability during an upgrade. What the right option might be will definitely depend on each environment and what your requirements are in term of downtime, SQL Server edition, etc. However, here are some basic guidelines I’ve gathered:
Log Shipping: SQL Server 2014 will support backup and restore from previous versions (all the way to 2005) so you can still use this option. Beware that the log shipping wizard might not work so you could end up needing custom log shipping scripts. Log shipping however, works pretty well over high latency WAN connections for example.
Mirroring: technically database mirroring is deprecated in SQL Server 2014 but you can still use it to do a rolling upgrade. For example, you have same datacenter servers, Standard edition. Mirror from source to target and then failover when it’s go-live time.
Availability Groups: if you’re on 2012 Enterprise then you can do the upgrade through Availability Groups. You can upgrade the secondary to 2014, failover, upgrade the former primary and failback if you want.
Storage based: if your SAN admin is on board with the plan you can also do a quick remount of storage on a new server and re-attach the databases or you can do a snapshot of the current storage and mount it on the new server. Make sure your SAN admin understands the entire plan and looks into any known issues with taking snapshots of SQL Server files on your particular SAN model.
14) If you’re dealing with replication, you need to do extra homework
Replication is a whole other back of nails when it comes to doing upgrades. These are the 2 cardinal rules that you always have to keep in mind:
a) The Distributor has to be the same version or later as the Publishers.
b) For transactional replication, the Subscribers have to be at the most, 2 versions behind from the Publisher.
You absolutely must script out the entire replication topology as a backup. Worst case scenario you can always refer back to the script to recreate publications and subscriptions.
Those are the most important ones I can think of for now. Doing upgrades of SSIS, SSAS, SSRS could all be a blog post on their own so I’m leaving that for another time. If you have another tip/trick for doing SQL Server 2014 upgrades then please leave it on the comments!