Share via

SQL Server 2008 end of support

  • Article

July 9, 2019 will be the end of support for the 2008 and 2008 R2 versions of SQL Server. There is still time to migrate your servers to the new SQL Server version. And why not take the opportunity to migrate to Azure. And try new Azure service, SQL Managed Instance (MI), now available in "GA" since October 2018, and allows a near 100% compatibility with a SQL Server installed on-premise.

Finally, in the case of applications that cannot be migrated in the coming months, there is the possibility of hosting them in Azure virtual machines, by using SQL Server template, and to benefit for an additional support extension.

In this article, we will see, depending on the situation, what are the options to continue using your applications in the best conditions of support and features.

 

Below, here are the 4 migration possibilities:

  • On-premise migration to the 2017 version (When possible for your applications)
  • Migration to SQL Database (PaaS) (When possible for your applications)
  • Migration to Azure SQL Database Managed Instance, which offers nearly 100% SQL Server compatibility
  • For applications that are difficult to migrate in the coming months, deploying to an Azure VM with SQL server pre-installed (via virtual machine template)

 

Prerequisites

In general, the migration process looks like the steps illustrated below. Even if in the end, there are many ways to execute a migration plan, it remains a project in its own:

The discovery part can be realized with MAP Toolkit:

/en-us/previous-versions//bb977556(v=technet.10)

However, whatever the solution considered, it is a good idea to perform the following tasks before any migration:

 

 

On SQL Server 2008 or 2008 R2:

  • Check that the last service pack is correctly installed
  • Rebuild indexes (if the indexes are very fragmented)
  • Check the integrity of your database with DBCC CHECKDB () (or on a copy, as this can take a long time)
  • Note if the database is not linked to a maintenance plan
  • Note if linked servers exist
  • Note if the SQL Agent is used
  • Do a full backup

 

 On a target:

 

Migrate to SQL Server 2017 (On-premise)

Here we are in a classic migration scenario. With SQL Server 2017, it is possible to either go through a backup and then restore the database or use the data Migration Assistant (DMA) tool.

In addition to the prerequisites detailed earlier in this article, on the target server that hosts SQL server 2017, check the following points:

Finally, also check the deprecated features in the SQL Server version 2017:

/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017

 

For the migration itself, the following methods can be used:

database backup and restore

 

Data Migration Assistant

 

 

Migrate to SQL Database (PaaS)

This is another possibility of migration, and the opportunity to modernize your data infrastructure with Cloud solutions. In addition, you will benefit from all the advantages of a managed service (no hardware management, 3 copies of your data, transparent updates, elasticity, …).

Again, there are several ways to migrate your SQL Server databases to SQL database. But mainly, the 3 methods illustrated below can be used, after consulting the detailed prerequisites above. Of course, double check the prerequisites section as a first step:

In order to measure the impact of migration, it is possible to use the Database experimentation Assistant (DEA) tool. This tool allows you to capture traces, on an existing server, to be replayed on SQL Database, SQL managed instance or SQL on Linux. DEA can be downloaded here: https://www.microsoft.com/en-us/download/details.aspx?id=54090

 

Finally, as I mentioned earlier, there are other possible ways to migrate its databases such as using SQL Server integration Services (SSIS) or Azure Data Factory v2.

 

 

Migrate to Azure SQL Database Managed Instance (PaaS)

SQL database Managed Instance (MI) is a managed service (PaaS) that provides SQL Server compatibility close to 100%, while supporting features that are not present in SQL database such as:

  • Native backup/restore function
  • Linked Server
  • CLR
  • SQL Agent
  • DB Mail (external SMTP)
  • ...

In addition, SQL database MI works in its own VNet, which helps to enhance the security of your database.... But at the expense, today, of a lack of integration with, for example, other Azure services.

 

A complete SQL database MI overview is available on this Web page: /en-us/azure/sql-database/sql-database-managed-instance#sql-features-supported

Again, after double check migration prerequisites, several ways are possible to migrate to SQL Database MI:

Overall the backup from a URL will follow the following path:

More details for all migration path are available here:

/en-us/azure/sql-database/sql-database-managed-instance-migrate

Pay attention to the following points:

 

 

Migrate to an Azure Virtual Machine with SQL server pre-installed (IaaS)

If it is not yet possible to migrate your SQL Server 2008 databases to SQL Server 2017, and to remain covered by support, there is the ability to move your databases to a SQL Server 2008 hosted in a Azure virtual machine (VM). To do this, you can directly provision a virtual machine with SQL Server 2008 from an existing Azure VM Template. This saves time and makes sure that you have the right technology base to move your databases.

Below is an example of the models available from the Azure portal.

Once the virtual machine is provisioned from templates, it is no more or less a migration to another SQL server, with a difference anyway, it is that we will have to transfer the data to Azure, so that they are accessible by the virtual machine . Below, all possible ways to move your data bases to an Azure virtual machine with SQL Server:

  • Perform on-premises backup using compression and manually copy the backup file into the Azure virtual machine
  • Perform a backup to URL and restore into the Azure virtual machine from the URL
  • Detach and then copy the data and log files to Azure blob storage and then attach to SQL Server in Azure VM from URL
  • Convert on-premises physical machine to Hyper-V VHD, upload to Azure Blob storage, and then deploy as new VM using uploaded VHD
  • Ship hard drive using Windows Import/Export Service
  • If you have an AlwaysOn deployment on-premises, use the Add Azure Replica Wizard to create a replica in Azure and then failover, pointing users to the Azure database instance
  • Use SQL Server transactional replication to configure the Azure SQL Server instance as a subscriber and then disable replication, pointing users to the Azure database instance

 

I would like to finish this article by a big thanks to Frédéric Pichaut and Nicolas Soukoff who have completed and detailed the prerequisite part, strong of their great experience with SQL Server. One MS Power! Merci!

For those who will be in Montreal, I will meet you on November 28th, 2018 at Tech Summit:https://www.microsoftevents.com/profile/web/index.cfm?PKwebID=0x1023303abcd&wt.mc_id=AID737011_QSG_SCL_276355

Franck Mercier