Minette Steynberg

The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2)

April 4, 2018 by

Introduction

As with everything else, all good things must come to an end. After hanging around for a good decade SQL Server 2008 and SQL Server 2008 R2 has started the countdown to its End of Life.

D-day for SQL Server 2008 and SQL Server 2008 R2 is set to the 19th of July 2019. Which is approximately a year from now, give or take a couple of months.

If you are still running SQL Server 2008 or SQL Server 2008 R2, now is definitely the time to upgrade. In this article; I am going to tell you why you would want to upgrade to a newer version of SQL Server (2016 or 2017) or Azure SQL Database. I will also discuss some of the tools that you might use to make the upgrade as painless as possible.

Why should I upgrade?

SQL Server 2008 and 2008 R2 are currently in the extended support, which means that security updates are still made available. If you wanted any other kind of support (not security), you can still get it by paying for it.

When the end of support comes around on the 19th of July 2019 the abovementioned will no longer apply.

You will no longer get critical security updates, which may put your data at risk, and possibly affect your compliance with industry standards and regulations. Regulatory compliance such as PCI-DSS for credit card transactions may no longer be met, which could put your company at risks for fines or penalties and affect your ability to do business in Europe once the GDPR comes into effect on the 25th of May 2018.

Additionally, it will probably result in must higher maintenance costs.

Last but not least, if you upgrade to SQL 2017 or Azure SQL DB or Azure SQL Managed Instance , you will have a whole plethora of new features and functions which will undoubtedly make your life a whole lot easier.

So, what’s new since 2008?

It’s been about a decade so there are so many new features that has been added since then it would be hard for me to make a complete list.

Here are some highlights:

Performance and Security

Data Warehousing and BI

Advanced Analytics

Hybrid Cloud

And of course, the ability to now run on Linux and inside Docker containers.

Upgrade Paths and tools

There are multiple options for upgrading.

  • On-premises 2016 or 2017
  • Azure SQL Database
  • SQL Server on Azure VMs

Regardless of which path you choose, you should start with the Microsoft Database Migration Guide.

Database Migration Guide

The Azure Database Migration Guide, creates a customized report on how to migrate your data sources to your selected target versions by following a solid methodology. Based on your selections the Database Migration Report will provide relevant information such as which migration tools can be used and whether bulk load or replication is a viable option.

Figure 1: The data migration guide

Database Migration Assistant

One of the migration tools which might be suggested is the Data Migration Assistant. The Data Migration Assistant helps you to identify any compatibility issues which might be blocking your upgrade, such as breaking changes, deprecated features of behaviour changes in remaining features.

The Data Migration Assistant supports SQL sources from version 2005 to version 2017, and targets from SQL Server 2012 to SQL Server 2017 on-premises and Azure SQL Database in the cloud.

Figure 2: Migration Assessment Tools

The Data Migration Assistant can be downloaded here: Download Data Migration Assistant

Figure 3: Data Migration Assistant

SQL Server Migration Assistant

Another tool which may be used, especially when migrating from other database systems like Oracle, DB2 MySQL etc; is the SQL Server Migration Assistant which can automate these migrations.

The SQL Server Migration Assistant is used to convert a different database schema to a SQL Server schema.

Once the schema is created the tool can then be used to also then migrate the data to the target SQL Server database.

The SSMA for the supported database systems can be downloaded here:

Database Experimentation Assistant

Once compatibility issues have been identified, the Database Experimentation Assistant can be used to test the performance of the target version for specific workloads.

Figure 4: The Database Experimentation Assistant

You can download the Database Experimentation Assistant here.

To use the tool, you simply create a “Capture” which is essentially a recording of what is happening on the source server at the time. A trace file is created. You can create multiple captures before continuing.

Next you replay your capture against your target server, by creating a “Replay”. Another trace file is created.

Then the trace files are compared and analysed using the Report Analysis Tab. For more details on how to use the tool. Please read: How to use the SQL Server Database Experimentation Assistant tool

Azure Database Migration Service

If you are moving on-premises database to Azure, another option may be to use the Azure Database Migration Service (which is currently in preview as at the writing of this article.)

Figure 5: Architecture Icon for DMS

The DMS has been created to simplify and streamline the process of migrating your on-premises databases to Azure. It is a fully managed database migration service for operational and data warehouses.

The following databases can be migrated using DMS:

Source Destination cloud options
SQL Server Azure SQL Database and Managed Instance
MySQL Azure Database for MySQL
PostgreSQL Azure Database for PostgreSQL
Oracle Azure SQL Database and Managed Instance
Netezza Azure SQL Data Warehouse

Some of the advantages of using the Azure Data Migration Service are:

  • The migration is very resilient and critical applications can be migrated with almost zero downtime.
  • Using one service to manage your cloud migrations reduces complexity to make it easier to plan and execute
  • The process is easy to understand and implement and since it is a guided migration you wont really need specialty skills to be able to do it.

Conclusion

With the number of tools and services available to help you seamlessly migrate your SQL Server 2008 and/or SQL Server 2008 R2 database to either a new modern on-premises version of SQL Server or to Azure, there really aren’t any valid excuses to not do it.

If you take into account all the advantages of newer technologies, the elasticity of the cloud and the risks if you don’t upgrade, upgrading really is in your best interest.

See more

To compare and synchronize SQL Server data, consider ApexSQL Data Diff, a tool that detects data differences and resolves them without errors.

References



Minette Steynberg

Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups.

Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.

View all posts by Minette Steynberg
Minette Steynberg
General

About Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups. Minette currently works as a Data Platform Solution Architect at Microsoft South Africa. View all posts by Minette Steynberg

240 Views