SQL Server 2008 end of support
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:
After restoring your database, check again the integrity of your database with DBCC CHECKDB:
DBCC_CHECKDB WITH DATA_PURITY: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-2017
Do a DBCC UPDATEUSAGE (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017)
Update statistics
Rebuild indexes
Check the compatibility mode (be very careful, the database will use the new Cardinality Estimator introduced with SQL 2014)
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:
- Use a supported operating system: /en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-2017
- Check the space of the different disks (TempDB, Logs, Data)
- Recreate maintenance plans, linked servers and other SQL agents if necessary
Finally, also check the deprecated features in the SQL Server version 2017:
For the migration itself, the following methods can be used:
database backup and restore
- Since version 2017, it is possible to create a database since the backup of a database in version 2005 or higher. Detailed documentation of this feature is available on this Web page: /en-us/sql/relational-databases/databases/copy-databases-with-backup-and-restore?view=sql-server-2017
Data Migration Assistant
- Data Migration Assistant (DMA) is a downloadable tool, which will be used to:
- Do an assessment of your databases to migrate, in order to evaluate the Migration effort. A complete documentation can be found on this web page: /en-us/sql/dma/dma-assesssqlonprem?view=sql-server-2017
- Do the migration. DMA can do the migration automatically. A complete documentation of the migration process is available here: /en-us/sql/dma/dma-migrateonpremsql?view=sql-server-2017
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:
- Transactional Replication: /en-us/azure/sql-database/sql-database-cloud-migrate#method-2-use-transactional-replication
- Import / Export BACPAC File: /en-us/azure/sql-database/sql-database-import
- Use DMA tool. Be careful, this operation will make your database inaccessible during the migration process!
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:
- Azure Database Migration Service (which will use DMA for the assessment part of the migration effort) : /en-us/azure/dms/dms-overview
- Native Restore from a URL. I explained in an article, which dates a little 😊, how to do this type of backup (article in French): https://blogs.technet.microsoft.com/franmer/2013/11/15/sql-server-2014-intgration-avec-le-stockage-azure/. But this article is more up to date: /en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017
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:
SQL Database Managed Instance must be deployed within a virtual network. The following article gives the configuration details: /en-us/azure/sql-database/sql-database-managed-instance-vnet-configuration
The first-time creation of the service can take up to 6h. I made a test in the Central Canada region, and the first creation took about 3 hours and 17 minutes.
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