Planning an Azure SQL Database Migration Project
Updated: April 28, 2014
This topic describes the best practices for planning and migrating an on-premise SQL Server database to Microsoft Azure SQL Database as part of a migration project. This topic covers the following issues:
Perspectives on Database Migration
The Analysis in which you set project goals for the database migration
Managing the data migration subproject through the Plan and Design, Develop, Test, Stabilization, and Deploymentphases.
Author: Shaun Tinline-Jones
Contributor: Steve Howard
Reviewer: Shawn Hernan
Perspectives on Database Migration
Database migration is a subproject of the larger solution migration project. There are typically integration points and dependencies between the application and database migration projects. But, the database migration can usually proceed in parallel with few bottlenecks.
The approach to a Azure SQL Database migration project should keep in mind three perspectives:
The lifecycle of the project should be agile and iterative in nature. Create an initial plan based on early research. During the planning for a new iteration, refine the plan based on the research done in previous iterations.
The size and complexity of the database and its associated applications drives many factors in a migration project:
The complexity of the database drives the engineering effort required to migrate the database.
The size of the database, the amount of data it contains, drives how long it takes to populate the new database and cut over from the on-premises database to the database hosted in Azure SQL Database.
- The complexity of the database drives the engineering effort required to migrate the database.
Migrating a database to a new platform often drives database changes that affect the other solution tiers using the database. The migration project must coordinate development work across all the affected tiers, and provide a unified deployment of all the changed components.
Each database to be migrated can be classified in to one of four quadrants defined by size and complexity. Which quadrant a database falls into helps you understand the scope of a project required to migrate the database to Azure SQL Database, and to choose a good mechanism for the migration. The quadrants are:
A large database requires a longer cutover to Azure SQL Database as more time is required to transfer data across the Internet. More complexity in the database means a greater chance that changes will be required, driving a higher amount of engineering work.
Understanding the size and complexity of the source database is a key aspect in setting the goal for the migration project.
During the analysis phase, the goal and the vision of the project are set. The overall project goals must include the goals for the database to be migrated.
All databases must meet business requirements, such as availability, recovery, response time, and compliance with security and privacy rules. When you migrate a database to Azure SQL Database, configure the database service so that it complies with these business requirements, or negotiate a new set of requirements that can be met by Azure SQL Database. You may also have to change your administration processes. For example, if you are currently taking nightly database backups, you may need to change to the database copy or data-tier application export features supported by Azure SQL Database.
The business requirements cannot be determined by analyzing the existing database and application code. You must gather the requirements from the stakeholders and administrators, and reviews of process documents such as service level agreements.
Define the Database Subproject Goal
The migration project goals related to database migration must meet the business requirements for the database, and reflect the size and complexity of the database. Complex databases require a larger engineering effort to migrate than simple databases. A project to migrate a complex database can reduce risk by limiting the initial project to migrating the features used in the on-premises database. Incorporating features unique to Azure SQL Database can be done in follow-on projects.
The analysis phase provides the higher-level guidance for the planning and designing phase. It is important to review the full set of issues that might affect the migration in the analysis phase, but do not focus too deeply on details during this phase. The first iteration of the planning and design phases must then dive deeper into details to form more granular designs and plans. Put in place a feedback process to adjust the vision and scoping documents with the results of the early planning and design work.
Assess Project Complexity
Assessing the complexity of a Azure SQL Database migration project means determining the amount of change required to complete a successful migration. Different stages in a Azure SQL Database migration project require increasingly accurate assessments of the scope of the engineering changes driven by the migration. An initial general assessment should be factored into the project goal definition and the decision to launch the project. It also forms the basis of early project planning and design work. The results of more in-depth research done later in the project should be reflected in increasingly detailed project plans, designs, and possibly in adjustments to the project goals.
Dependencies on Features Not Supported by Azure SQL Database
Address all dependencies on features not supported by Azure SQL Database as part of the migration project. Initially identifying these dependencies can be done without requiring access to the production system. This is done by comparing the existing documentation about the features supported by Azure SQL Database against the database design documents or application specs. The documentation can also be reviewed by people familiar with the database and application designs. Later, certain kinds of dependencies can be confirmed by using tools such as the Azure SQL Database Migration Wizard.
Many on-premises databases have dependencies on services outside of the database. Examples include participation in a replication topology, a SQL Server Integration Services extraction process, or recurring maintenance tasks managed by SQL Server Agent. The migration project must include the cost and development time required to change the dependencies on any such services. Remove dependencies on any of the services that do not support Azure SQL Database. You may have to make changes to other systems that do support Azure SQL Database due to the architectural differences between SQL Server and Azure SQL Database.
In addition, on-premises databases may have Transact-SQL objects not supported in Azure SQL Databases. The applications accessing the database, and code in database objects such as stored procedures or triggers, may also be using syntax elements not supported by Azure SQL Database.
The initial assessment of the database complexity can be made by reviewing the database and application designs or code against the issues discussed in the following sources:
General Guidelines and Limitations (Azure SQL Database)
Security Guidelines and Limitations (Azure SQL Database)
SQL Server Feature Limitations (Azure SQL Database)
Tools and Utility Support (Azure SQL Database)
Unsupported Transact-SQL Statements (Azure SQL Database)
Partially Supported Transact-SQL Statements (Azure SQL Database)
Supported Transact-SQL Statements (Azure SQL Database)
The Scope of Application Changes Driven by Database Changes
Migrating a database to Azure SQL Database often requires changes to the applications and systems that use the database.
First you must make the changes required for applications to operate effectively in a Azure SQL Database environment. Azure SQL Database is a web service hosted outside your data center. This means that some database best practices that have little impact when the database server is in the same rack as the application server become important when the database is migrated to Azure SQL Database. Each database hosted in Azure SQL Database is clustered across multiple servers to improve overall availability. However, certain operations, or the failure of your current server, may cause a transient failover event that closes all open connections and rolls back their active transactions. This makes it important for your applications to have robust retry logic that restarts a transaction when the application gets a disconnect error.
For more information about the application changes required to support good performance, see Performance Considerations with Azure SQL Database.
Additional application changes required to operate effectively with Azure SQL Database are discussed in the following documents:
You must also make any applications changes required to adjust to all changes made to the database. For example, if a user-defined aggregate is removed from the database, you must change any application that runs Transact-SQL statements that reference the aggregate.
Plan and Design
Planning and design work should start during the complexity assessment. A key part of this phase is doing increasingly detailed research for the issues covered in these two sections:
Dependencies on Features Not Supported by Azure SQL Database
Scope of Application Changes Driven by Database Changes
As features that need to be changed are identified, formulate the initial estimates of the work required for those changes in the early iterations of the project. Each subsequent iteration should perform a more comprehensive review of the database to identify all changes, and formulate more detailed definitions of the scope of changes required. Set up a feedback process where the project goals and scope can be adjusted to reflect the results of the more detailed research done during the planning and design iterations. The first iteration does not require access to the production environment, but does need a reasonably accurate reflection of what exists in production.
You can achieve this initial planning view by leveraging Azure SQL Database Migration Wizard and ramping up on the differences between on-premises SQL Server and Azure SQL Database. These are discussed in more detail under the respective headings. SQL Server Data Tools (SSDT) is a potentially useful tool at this stage, especially if the Application Lifecycle Management (ALM) of the data tier includes the use of this tool or scripts of the database objects. The effort is low, a little more than the Azure SQL Database Migration Wizard, but not too onerous. The elegance lies in the usual Visual Studio productivity features, such as double-clicking a warning and been taken directly to the offending line.
You can check your assessment for issues such as Transact-SQL support by using a number of tools:
You can connect the Azure SQL Database Migration Wizard to a test or production copy of the on-premises database and generate a report of objects that must be changed to run on Azure SQL Database. For more information, see How to: Use the SQL Azure Migration Wizard.
If all of the objects in the on-premises database are supported by a data-tier application (DAC), you can extract a DAC package and either:
Run the DAC package through the Azure SQL Database Compatibility Assessment Service for a report on required changes (currently in beta).
Import the DAC package to create a database project in SQL Server Data Tools, and set the project target to Azure SQL Database.
For more information, see How to: Use a DAC Package to Migrate a Database to Azure SQL Database.
- Run the DAC package through the Azure SQL Database Compatibility Assessment Service for a report on required changes (currently in beta).
While the assessment tools can help identify features that are not supported in Azure SQL Database, they do not necessarily identify alternative features that can be used to accomplish the same functionality. The project planners must understand the business uses of the functionality and design alternatives that will also support those business uses. The decision to proceed should be based on an assessment of the costs of developing and deploying the alternatives, rather than simply using the missing feature support as a reason to not use Azure SQL Database.
Avoid including non-migration objectives, especially for complex migrations. Adding complexity is a common reason migration projects fail. A common area that gets into scope is the desire to leverage a scale-out database model. Only do this if it is necessary, such as:
You are migrating a database that is larger than the maximum size supported by Azure SQL Database.
The business case is only viable if multi-tenancy is implemented at the outset.
The computing requirements of the database exceed that possible with a single Azure SQL Database database.
Planning and designing should include cost considerations. Cost factors need to be assessed during each phase and with each iteration, and are an important part of each decision on whether to proceed. This element might get excluded from planning and design as the probability of this risk becoming an issue is low, however the severity of underestimating costs can be quite high.
A key success factor is to identify risks, and assign them a probability and severity rating. List all risks, even ones that seem trivial. Ensure that all stakeholders agree that the more likely risks have been appropriately rated. Ensure each likely risk has a mitigation plan with an acceptable level of planning should the risk turn into an issue. Establish a process for adding plans for new risks found during all phases of the migration. Issues found and resolved during the final deployment phase should be recorded as risks for future projects. It is also important to assess the risks as they apply to a Cloud environment, not how they apply in more familiar on-premises environments.
It is important that the project have a comprehensive review of all the features used by the database against the set of features supported in Azure SQL Database, coupled with estimates of the conversion costs and the costs of running the database in Azure SQL Database. This review should occur at an early enough iteration that the project can be canceled if the costs outweigh the benefits. One Azure SQL Database migration project ran in to difficulties relatively late because the project planners were not aware the data compression used by the on-premises database is not supported by Azure SQL Database. This was a substantial change to the projected costs of running the database on Azure SQL Database, and is the type of issue that should be identified relatively early in the project.
Each iteration through the planning and design phase should include a deeper assessment of the changes required in the data tier. For instance, the second iteration may include creating a profiler trace of the functional testing environment, and running that through the SQLAzureMW. A third iteration may progress to the performance testing environment, where Performance Monitoring tools are included in your toolbox of identifying potential areas for getting ready to migrate.
Azure SQL Database does not support SQL Server 2000 and SQL Server 2005 features that were removed from SQL Server 2008 and later versions. For example, Azure SQL Database does not support the *= or =* syntax for specifying joins. Therefore, migrating these databases to Azure SQL Database must also address many of the same issues encountered when upgrading from SQL Server 2000 or SQL Server 2005. You can use tools such as Performance Monitor counters, XEvents, and SQL Server Upgrade Advisors to find these dependencies. For more information about researching these issues, see Upgrade Database Engine.
Development is a distinct operation of performing tasks generated from the planning and design phase. The people assigned to development tasks should not be assigned tasks in other phases of the migration project.
Most databases migrated to Azure SQL Database require changes that impact the application tiers. As soon as things such as data types, number of columns returned, dynamic Transact-SQL or input parameters are changed, then the data tier of the application code will need modification. Even if no database objects are changed by the migration, the Azure SQL Database architecture drives requirements for application changes such as robust retry logic and error handling. In short, database development should be integrated with the application tier development.
The database development work can be done using any database development tools that support SQL Server databases. An advantage of using a tool such as SSDT that has logic for Azure SQL Database is that you can set the database project build target to Azure SQL Database, at which point SSDT will identify incompatible syntax as you write the code. For more information, see Microsoft SQL Server Data Tools. Prior to the release of SSDT, developers have used the Azure Emulation Kit, and connected to SQL Server Express. This adds convenience and provides some sense of offline development, but is still an on-premises feature set and therefore can be misleading in what is possible in Azure SQL Database. A more productive and efficient experience is identifying issues as close to planning time as possible, and by the time you are coding it needs to be as you write the code. If you are not developing using a tool that has logic for Azure SQL Database, then you can start developing against a Azure SQL Database as soon as possible. Offline development tools such as SSDT offer the value proposition of multiple developers working concurrently on the same project. SSDT also integrates with source control and build features, such as that found in Team Foundation Server. If the migration affects both application and database code, the database project can be integrated into the same build environment. If the migration affects both the applications and database, then the SSDT project can be integrated into the same solution as the application projects build processes. These are benefits you can enjoy over and above the obvious connectivity challenges when developing directly against Azure SQL Database.
When the migration requires relatively few data model changes, then import that data model into SSDT and begin applying the changes. The value proposition here is that the individual development resources can work on their respective focus areas and integrate them during the build phase.
In each project iteration, the development team must give the planning team regular feedback and respond to their cycles proactively. Recognize that it is less risky to validate regularly and then fail and recover fast than it is code for long periods without validating. This is not a new paradigm; it's simply that failure to adhere to these paradigms when developing cloud-based solutions can be very costly.
Similar to the other activities in the application lifecycle of a migration project, there are activities and objectives that remain constant irrespective that this is a migration to Azure SQL Database. For testing areas that are importanat for Azure SQL Database, but are common for planners and developers to overlook are:
Responding to throttling
Network latency effects
Pay attention to these as they provide a basis from which functionality and performance test use cases can be derived. There are many testing tools out there, what's important is the ability to isolate the database activity. Functional testing will increase productivity mitigating the immaturity of development tools to precisely catch SQL Server functionality that does not appear in Azure SQL Database. Depending on the development tools and build mechanisms, the functional testing may not produce issues, and merely serve as additional insurance before the more lengthy and costly performance test harnesses.
Testing the migrated solution should address new use cases that had little impact for on-premises implementations. Create tests that force errors that demand the need for transaction re-tries, and test the impact of hitting maximum and peak loads. Good Cloud solutions work to reduce the troubleshooting time. This can be achieved by developing application logic that logs activity and regularly analyzes the current health of the system. Since logging actions in the database can be expensive and limited to writing to another table, the application code that executes the database calls should be logging errors, warnings and durations. For example, a customer spent several hours troubleshooting some servers that were exhibiting poor performance. They attempted to leverage many tools to reactively identify the cause of the issue to that they could resolve it. After several hours the servers suddenly began to work as expected. The issue had nothing to do with their application but rather that a platform upgrade was underway. The effort to resolve the problem could have been significantly reduced if the application had been better engineered to log data points and the administrators were regularly analyzing the solutions health. They could have provided their help desk engineer with better data that made it easier to identify the root cause of the problem. Of course, the future uses of this type of data may include re-directing consumers to a different data center.
An area that can be easily forgotten about is the deployment experience. The testing should include the deployment experience, and provide insights or confidence into how future changes can be applied to an existing environment. Deploying a new database, seeding it and making it ready for production use is quite different from deploying changes to an existing production environment. This is no different to on-premises considerations but is mentioned for 2 reasons:
Deployment actions that worked on-premises may not work for cloud-based solutions.
It's a fair amount of extra effort to include this in the test plan, with few immediate returns.
Testing must also participate in the iterative model, where issues are fed back to the development and planning teams. In the beginning the tests may be quite rudimentary and very similar to the on-premises test harnesses. With each iteration, incorporate more cloud-aware test cases. The test cases should cover the issues identified in the documentation linked to from the Assessing Complexity section above.
Migration complexity may be the result of downtime constraints, which increases the priority of testing the proposed deployment plan.
This stage is no different from its purpose in normal Software Development Lifecycle. For the migration project it's about reaching the point where developers are only working on issues raised by the tests, it's no longer coding the first version of the migrated version of the database.
Similar to stabilization, the deployment phase for migrating to the cloud is no different from on-premises migration projects. Good testing increases the probability of success in this phase.
Good communications is a key success factor for the migration. Regular status updates with an appropriate level of detail for the consumer of the status is vital for a good experience, or at least an experience that can remain aware of the broader business objectives of migrating to the cloud.
Success in the deployment phase depends on the quality of the work done in the previous phases. Poor research, planning, development, and testing greatly increase the risk of encountering problems during the deployment. Sometimes the deployment problems are severe enough to stop the project with a rollback to the on-premises systems. In some cases they have left organizations questioning their ability to use Cloud-based systems. Good research, planning, development and testing usually result in a deployment that runs according to plan. Even if problems are encountered, good planning often has built contingencies that reduce the impact of the problems.