Microsoft SQL Server 2008 is nearing the end of its extended service period. If you are still using 2008 or 2008 R2, it’s time to get off now. Or maybe you have 2012 or 2014, but want to take advantage of the most up to date processes and security features. It can be a daunting task to think about upgrading your SQL servers. There are so many pieces to juggle. If you’ve been putting it off, I don’t blame you. But with the speed of innovation, it’s important to stay up to date and ahead of trends. So today we’re talking about some things to keep in mind when you upgrade your SQL Server.
Check Compatibility – If you are currently running SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 (11.x), SQL Server 2014 (12.x), or SQL Server 2016 (13.x) then you can upgrade directly to SQL Server 2017, assuming the same platform specifications. If you have 2005, then you will need to migrate first. Read more about migration here.
Use Microsoft Data Migration Assistant – It’s important to read about which features will change from your current version to understand potential issues from depreciated or new features. This tool replaces the Upgrade Advisor from previous versions. It analyzes your environments for potential compatibility issues and allows you to resolve them before the upgrades. We recommend running the tool and correcting any issues before proceeding with upgrading. The more kinks you can work out ahead of time, the smoother your upgrade will go.
If you’re ready to upgrade, and you’ve determined compatibility, then it’s time for a plan. To make the process smoother and more efficient, try strategizing like you would any other IT project.
Don’t Rush – Plan a generous timeline so that you can adequately prepare your various instances appropriately. It takes time to assess your database system, devise and implement a plan, and identify and resolve issues along the way. If you try to rush, you could end up losing time with lengthy rollbacks or other frustrating setbacks.
Assess Your Environment – Take an inventory of your SQL instances to prepare for the upgrade. Make notes of what types of instances you have and where they are housed. Your Analysis Services will have different needs than your Database Engine. Gather the appropriate documentation for your particular services before you get started. Identify where your mission-critical workloads are and where your development and testing environments are.
Gather Validation Data – You want to be sure your data if migrated accurately. Prepare a selection of data and report samples to use for post upgrade validation. If you can, collect performance measurements as well.
Make A Plan – Write out a plan that you will follow throughout the process. This will keep you on track and make sure nothing is missed. There are methods of upgrade, and you will want to assess each instance to determine how it should be treated.
- In-Place Upgrades are good for development and testing environments without high availability. This process creates a new version directly on top of your existing version. The binary data and metadata is compatible, so your data doesn’t have to be migrated or repopulated, it’s automatically there. This process is faster and easier, but it has some drawbacks. The database will have some downtime, and you really don’t want to have to rollback if you can help it. We don’t recommend in-place for mission-critical workloads.
- Side-by-Side Upgrades take a bit more of your time, but allow for testing and validation at every step, with easy rollbacks if a problem is detected. For this process, a new instance is created separate from the original instance. This can be done on the same hardware, or on a different machine. You will need to manually transfer the data and objects to your new version, so it can be more time consuming, but the continuity and peace of mind are worth it. We recommend side-by-side for most production databases since it minimizes risk and downtime.
- Assess your instances from your inventory list and determine which upgrade method to use for each. Write out the strategy and order of upgrade so that you are organized and don’t miss stuff.
Try Not to Stress – This isn’t like upgrading your windows version. There are a lot of complexities in the SQL Servers, and you will probably hit some bumps in the road. If you take it step by step with an appropriate time horizon, you don’t need to stress about it. Finish your checklist, and celebrate.
