Migrating a user database to Azure

SQL Server provides a variety of mechanisms to help you migrate some or all of the user objects and data from both Microsoft SQL Server and non-Microsoft SQL Server databases to an Azure environment:

 Migrating from a SQL Server source database: You can migrate from a SQL Server 2005–2016 database to an Azure environment, but you may have compatibility issues to resolve either before the migration can be completed or after the migration but before the database is ready to use.

 Migrating from a recent version of SQL Server to a supported version of SQL Server in an Azure virtual machine will require few if any changes at the user database level.

 Migrating from an older version of SQL Server database to a newer version of SQL Server may have breaking changes that you will have to resolve before, during, or after the migration.

 When you are migrating from a SQL Server database to Azure SQL Database, you may have objects that are not compatible that you will need to fix prior to the migration.

 Microsoft provides a number of mechanisms to minimize downtime during the migration, depending upon both your source environment and your destination.

 Migrating from a non–SQL Server source database: You can migrate from the following non–SQL Server source databases into an Azure environment:

• Access
• DB2
• MySQL
• Oracle
• Sybase

When you are migrating from a non–SQL Server database, SQL Server provides mechanisms to convert a substantial percentage of the database objects automatically and will assist you in identifying the objects that cannot be converted, providing you guidance in how to convert those remaining objects. These changes will have to be made either before or during the migration. In most cases, you cannot complete the migration until you have established compatibility with the destination environment.

Regardless of the migration method, you will have to manage metadata that is stored outside the user databases, plan to minimize user downtime, and determine how to redirect users from the old database to the new database.

After the migration is complete, you will want to set the appropriate database compatibility level for the best performance and to take advantage of the newest database capabilities and provide partial backward compatibility with earlier versions of SQL Server until you have converted applications that use deprecated and discontinued functionality. By default, a migrated database retains its existing compatibility if it is at least equal to the minimum supported by the instance to which the database is being migrated.

Finally, how you perform the migration will be affected by your tolerance for downtime in your existing production environment. You will need to plan your migration to handle the amount of downtime required. For example, you may perform an initial migration of a copy of your database, perform the required testing and development work on the copy, and later migrate the production system by using one of the mechanisms discussed later in this chapter to minimize downtime. Or, you may discover that your database requires minimal or no changes and can be migrated during a scheduled maintenance window by using one of the simple migration methods.

Source of Information : Migrating SQL Server Databases to Azure