4 – Moving to Microsoft Azure SQL Database
.png "patterns & practices Developer Center")
| On this page: | Download: |
|---|---|
| The Premise | Goals and Requirements | Overview of the Solution | PaaS Options for Data Storage | Comparing SQL Server and Azure SQL Database - Limitations of Azure SQL Database | Database Management and Data Backup | Database Connection Reliability - Implementing Retry Logic for Database Connections | Inside the Implementation | Connecting to Azure SQL Database | Handling Transient Connection Failures | Setup, Deployment, Management, and Monitoring | Data for Development and Testing | Data Migration | Data Management | Database Monitoring | More Information |
.png "Download code samples")
|
.png "Download PDF")
|
This chapter describes the third step in Adatum’s migration of the aExpense application to Microsoft Azure. For the initial migration of the aExpense application to the cloud, Adatum chose the IaaS model because it required the fewest modifications to the existing application. In the previous chapter, you saw why and how Adatum decided to move the aExpense web application to the PaaS deployment model by using Azure Cloud Services instead of Azure Virtual Machines. In this chapter you will see how Adatum reviewed its choice of data store, and moved from using SQL Server hosted in an Azure virtual machine to using Azure SQL Database.
The Premise
At present the aExpense application’s data store still uses the IaaS model in the form of SQL Server installed in an Azure virtual machine. However, the Azure platform also includes cloud services that offer both relational database capabilities and less structured storage capabilities using the PaaS model. Adatum wants to explore the capabilities and the consequent impact on their migration strategy for these alternative data stores.
Goals and Requirements
In this step along its migration path Adatum wants to evaluate whether it can reduce operating and hosting costs for the data storage functions of the aExpense application. The goals are to reduce the direct hosting costs if this is possible, as well as minimizing the amount of management and maintenance required by Adatum’s operators and administrators.
Adatum must first ensure that the data store they choose at this stage is suitable for use with the aExpense application, and it does not have limitations that may prevent the operation or future development of this or other related applications.
.gif "Ff803375.note(en-us,PandP.10).gif") Bharath Says: |
|
|---|---|
.png "Bharath")
|
|
Adatum would like to be able to change just the connection strings that define how the application connects to the data store, and continue to use the same SQL Server tools to manage and interact with its data. However, Adatum must consider how management tasks such as backing up the data can be accomplished when it moves away from using a hosted instance of SQL Server.
Finally, Adatum must ensure that the solution it chooses provides an acceptable level of performance, reliable connectivity, and sufficient data storage and throughput capabilities.
Overview of the Solution
This section of the chapter describes how Adatum evaluated the Azure PaaS services for data storage, and then compares Azure SQL Database to SQL Server. It also describes how Adatum explored the limitations of SQL Database in terms of its impact on the aExpense application.
PaaS Options for Data Storage
Azure PaaS approach offers these two built-in mechanisms for storing data:
- Azure SQL Database. This is a highly available and scalable cloud database service built on SQL Server technologies, and it supports the familiar T-SQL based relational database model. It can be used with applications hosted in Azure, and with other applications running on-premises or hosted elsewhere.
- Azure Storage. This provides the following core services for persistent and durable data storage in the cloud. The services support both managed and REST APIs that can be used from within Azure-hosted or on-premises applications.
- Azure tables provide a non-relational table-structured storage mechanism. Tables are collections of entities that do not have an enforced schema, which means a single table can contain entities that have different sets of properties. This mechanism is primarily aimed at scenarios where large volumes of data must be stored, while being easy to access and update.
- Azure blobs provide a series of containers aimed at storing text or binary data. Block blob containers are ideal for streaming data, while page blob containers can be used for random read/write operations.
- Azure queues provide a mechanism for reliable, persistent messaging between role instances, such as between a web role and a worker role. They can also be used between roles and non-hosted applications and services, though Azure Service Bus messaging is typically more suited to this scenario.
- Azure drives provide a mechanism for applications to mount a single volume NTFS VHD as a page blob, and access it as though it were a local hard drive. They are aimed at scenarios where applications rely on access to a physical file system.
Adatum previously discounted using Azure storage during the early steps of the migration due to the requirement to change the data access mechanism to work with the table and blob storage APIs. At this stage, Adatum wants to be able to continue using standard SQL Server tools, and change only the database connection strings. Therefore, the logical choice for this step is to use Azure SQL Database.
Azure SQL Database is SQL Server for the cloud. Therefore, Adatum should be able to move the aExpense data from SQL Server running in an Azure Virtual Machine to SQL Database without requiring changes to the way that the application works.
|
Bharath Says: |
|
|---|---|
|
|
|
Using Azure SQL Database provides a compelling option for storing data because it removes the requirement to manage the operating system and database software. Developers can simply migrate their application data to the Azure SQL Database hosted database service. They can use the Management Portal to configure the database, create and manage the tables, run scripts, and monitor the database. In addition they can use SQL Server Management Console, Visual Studio, scripts, or any other compatible tools to migrate and interact with the database.
Note
The MSDN topic. "Migrating Databases to Azure SQL Database (formerly SQL Azure)" provides an overview of how to migrate a database to Azure SQL Database. You can also use the Migration Wizard to help you to migrate your local SQL Server databases to Azure SQL Database.
Comparing SQL Server and Azure SQL Database
Given the similarities between SQL Server and Azure SQL Database, why should Adatum choose one over the other? The key to this choice is to understand the advantages and limitations of the IaaS and PaaS models. In the cloud, SQL Server is available if you opt for the IaaS model while and SQL Database is available if you opt for the PaaS model.
|
Bharath Says: |
|
|---|---|
|
|
|
If you choose to run SQL Server on a virtual machine in the cloud, you are responsible for managing both the operating system and the SQL Server installation; and ensuring that they are kept up to date with patches, that they are securely configured, and so forth. Azure keeps copies of VMs for resilience and you can choose to have the storage account that holds these copies geo-replicated across different datacenters, but you do pay for the blob storage that holds these copies.
If you choose SQL Database, Microsoft handles all of this management for you. However, taking the PaaS route does mean that you give up some level of control over your environment.
In terms of cost, the situation is more complex because you have three basic choices for a SQL data store:
- License SQL Server yourself, then install and run it in an Azure Virtual Machine. In this scenario, you may be able to reuse an existing license, but the SQL Server costs are all upfront and you must try to ensure at the start of your project that you choose and license the best version of SQL Server for your application.
- Use an Azure virtual machine image with SQL Server pre-installed. In this scenario, you pay by the hour for using SQL Server; you can stop at any time, and move to a larger or smaller virtual machine at any time. This is still an IaaS solution because you are responsible for managing the operating system and the SQL Server installation.
- Use Azure SQL Database. In this scenario, you also have the flexibility of the pay-as-you-go pricing model, but you now have a PaaS model for deploying a SQL data store in Azure. After you create a logical SQL Database server instance you can create multiple databases easily and quickly without needing to manage the server itself. Azure automatically manages allocation of databases across all the physical servers.
|
Poe Says: |
|
|---|---|
.png "Poe")
|
|
Limitations of Azure SQL Database
Azure SQL Database does have some limitations compared to SQL Server. Developers will not be able to use free text search, some XML handling capabilities, procedures that require common language runtime (CLR) programmability, scheduled tasks, and distributed queries that rely on the SQL Service Broker. In addition, some data types (including user-defined CLR types) are not supported.
However, Azure SQL Database does support stored procedures and the majority of Transact-SQL functions and operators, and you may also be able to implement workarounds for some of the limitations, such as using a separate mechanism such as the Quartz scheduler in a Cloud Service role to drive scheduled tasks.
SQL Server may also provide better performance under certain high load conditions and complex query execution than Azure SQL Database, which can be subject to throttling under very high load conditions. However, SQL Database supports a mechanism called federations that allows you to partition data by a specific key across multiple databases while still being able to interact with the data using standard queries.
Note
See “Federations in Azure SQL Database” for more information about using SQL Database federations.
One other major difference is that Azure SQL Database automatically replicates the data to multiple datacenters to provide resilience against failure. However, this is not a complete backup solution because it does not provide access to previous versions of the database. A database backup routine must be established for critical data when using both a hosted SQL Server and Azure SQL Database. The section “Data Management” later in this chapter provides an example of a simple backup technique.
Note
For more details of these differences, see “Azure SQL Database Overview” and “Guidelines and Limitations (Azure SQL Database)”.
After considering all of the options available, and their impact on the migration of the aExpense and other applications, the developers decided that the simplicity offered by Azure SQL Database was the most attractive. The few limitations it imposes will not prevent them from using it with the existing data and procedures in the aExpense application, and in other applications that Adatum intends to create in the future.
Database Management and Data Backup
Azure SQL Database exposes remote management connectivity that is compatible with SQL Server management tools and other third party tools. Administrators, operators, developers, and testers can use SQL Server Management Console, Visual Studio, scripts, or any other compatible tools to connect to a SQL Database server instance in the cloud to migrate data, perform management tasks, and to and interact with the database and the data it holds. This familiar approach makes changing from SQL Server to SQL Database a relatively pain-free process.
Azure SQL Database automatically maintains three replicas of database in different locations within the same datacenter to protect against hardware failure that may affect an individual node within the datacenter. However, it’s important to recognize that this is not a backup solution in the same sense that administrators and operators perform backup procedures for an on-premises or cloud-hosted SQL Server database.
For example, Adatum’s operators cannot access a specific replica of the data stored in SQL Database. The platform automatically maintains the replicas and uses the most current should a failure occur. If data is changed in error or lost in a database (for example, due to an operator error, code fault, or malicious action) that change to, or loss of data will also be applied to the database replicas.
|
Poe Says: |
|
|---|---|
|
|
|
Therefore it is vital that Adatum adopts a backup strategy that works with SQL Database. For example, Adatum may choose to back up the data to an on-premises database or a separate cloud-hosted database by using replication or the Azure database import and export feature; by using the SQL Server Management Studio to create a SQL Server Backup for the data; or by using third party tools to copy the data between Azure SQL Database and blob storage. These tools can also be used for initializing the data in a database from a backup copy.
Note
See “Azure SQL Database Backup and Restore” and “Business Continuity in Azure SQL Database” for information about how you can back up and restore Azure SQL Databases.
Database Connection Reliability
Adatum has noticed that the application running in the cloud very occasionally suffers from a failed connection to the database. While the application was running on premises these kinds of events were very rare, mainly due to the high speed physical connection between the application servers and the database servers within Adatum’s datacenter. When the application was moved to cloud, the data was deployed in a hosted SQL Server running in an Azure Virtual Machine, and so connectivity between the application and the database was no longer over a dedicated network managed by Adatum.
While the connectivity within the Azure datacenters is much faster and more reliable than is available on the Internet, Adatum can no longer manage traffic allocation and so occasional connection failures may occur. In addition, when Adatum moves from using SQL Server to the shared data storage mechanism that is Azure SQL Database, there are additional factors to consider. For example, Azure SQL Database will attempt to connect for thirty seconds, but may fail when the database encounters severe load conditions, or if the server is automatically recycled following a failure.
|
Poe Says: |
|
|---|---|
|
|
|
Adatum must address the issue of how to handle these kinds of transient faults in its applications’ data access code. If the timeout expires before establishing a connection, or if a connection to Azure SQL Database drops while an application is using the connection, an error occurs; which the application must handle. How the application handles these errors depends on the specific circumstances, but possible strategies include immediately trying to re-establish the connection, keep retrying the connection until it succeeds, report the error to the user, or log the error and move on to another task.
If a database transaction was in progress at the time of the failure, the application should retry the transaction. It is possible for a connection to fail between sending a message to commit a transaction and receiving a message that reports the outcome of the transaction. In this circumstance, the application must have some way of checking whether the transaction completed successfully in order to determine whether to retry it.
Whereas Adatum wanted to avoid making unnecessary changes to the application code in previous migration steps, the developers have realized that they must resolve these issues now that Adatum has chosen to use Azure SQL Database by implementing retry logic in the aExpense application.
Implementing Retry Logic for Database Connections
It is possible to create your own logic for retrying failed or dropped connections by writing a suitable delegate. The blog post "SaveChangesWithRetries and Batch Option" describes how to use the RetryPolicy delegate in the Microsoft.WindowsAzure.StorageClient namespace for this. The post describes using this delegate to retry saving changes to Azure table storage, but you could adapt this approach to work with a context object in LINQ to SQL or ADO.NET Entity Framework.
However, a simpler option is to use the Transient Fault Handing Application Block that is part of the patterns & practices Enterprise Library. This application block exposes methods that allow you to execute code that connects to a wide range of Azure services including SQL Database, Azure Service Bus, Azure Storage, and Azure Caching Service.
You can configure a range of different retry policies, and write code to handle the success and failure events for the connection. You can easily add the Transient Fault Handling Application Block to an existing application; it requires very few code changes in your application, and you can customize its behavior to meet your specific requirements through configuration. Customization options include the choice of retry strategy to use in different circumstances; for example, you can choose between fixed interval, incremental interval, and exponential interval retry strategies.
This is the solution that the developers at Adatum chose to adopt.
Note
For more information see “The Transient Fault Handling Application Block.” The block is extensible, which enables you to add support other for other cloud services. You can find guidance on how to extend the block in “Extending and Modifying the Transient Fault Handling Application Block.”
Inside the Implementation
Now is a good time to walk through this step in the process of migrating aExpense into a cloud-based application in more detail. As you go through this section, you may want to download the Visual Studio solution from https://wag.codeplex.com/. This solution contains an implementation of the aExpense application (in the Azure-CloudService-WADB folder) after the migration step described in this chapter. If you are not interested in the mechanics, you should skip to the next section.
The Hands-on Labs that accompany this guide provide a step-by-step walkthrough of parts of the implementation tasks Adatum carried out on the aExpense application at this stage of the migration process.
Connecting to Azure SQL Database
In all of the previous migration steps, Adatum used a hosted SQL Server database to store the data for the aExpense application. In this phase, the team moved the database to Azure SQL Database and the data access code in the application remained unchanged. The only thing that needs to change is the connection string in the configuration file.
Connecting to Azure SQL Database instead of SQL Server requires only a configuration change.
<add name="aExpense" connectionString=
"Data Source={Server Name};
Initial Catalog=aExpense;
UId={User Id};
Pwd={User Password};
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;"
providerName="System.Data.SqlClient" />
Note
The values of Server Name, User Id, and User Password are specific to your Azure SQL Database account.
There are two things to notice about the connection string. First, notice that, because Azure SQL Database does not support Windows Authentication, the credentials for your Azure SQL Database account are stored in plain text. You should consider encrypting this section of the configuration file as described in Chapter 3, “Moving to Azure Cloud Services.”
The second thing to notice about the connection string is that it specifies that all communications with Azure SQL Database are encrypted. Even though your application may reside on a computer in the same data center as Azure SQL Database, you have to treat that connection as if it was using the internet.
Any traffic within the data center is considered to be “Internet” and should be encrypted.
|
Bharath Says: |
|
|---|---|
|
|
You can also add protection to your Azure SQL Database instance by configuring the firewall in the Management Portal. You can use the firewall to specify the IP addresses of the computers that are permitted to connect to your Azure SQL Database server and control if other Azure services can connect to your SQL Database. |
Handling Transient Connection Failures
When you try to connect to Azure SQL Database, you can specify a connection timeout value in your data access connection strings. Azure SQL Database will attempt to connect for thirty seconds, and so you should set the connection timeout in your code to at least this value. Adatum set the connection timeout to thirty seconds in the aExpense application.
Note
Azure SQL Database removes idle connections after thirty minutes, and also imposes throttling to protect all users of the service from unnecessary delays. For a detailed explanation of how this works, and how you can optimize your data access connections, see “Azure SQL Database Connection Management.”
In addition, Adatum decided to use the Enterprise Library Transient Fault Handling Application Block to implement a consistent and configurable retry strategy in the aExpense application. The recommended retry interval for Azure SQL Database is ten seconds.
Adatum added a configuration section for the Transient Fault Handling Application Block to the Web.config file for the aExpense.Azure project. This configuration defines several retry strategies that the application can use, and identifies the "Fixed Interval Retry Strategy" as the default.
<configuration>
<configSections>
<section name="RetryPolicyConfiguration"
type="Microsoft....RetryPolicyConfigurationSettings,
Microsoft.Practices...TransientFaultHandling,
Version=5.0.1118.0, Culture=neutral,
PublicKeyToken=31bf3856ad364e35"
requirePermission="true"/>
...
</configSections>
<RetryPolicyConfiguration
defaultRetryStrategy="Fixed Interval Retry Strategy">
<incremental name="Incremental Retry Strategy"/>
<fixedInterval name="Fixed Interval Retry Strategy"/>
<exponentialBackoff name="Exponential Backoff Retry Strategy"
maxRetryCount="3"/>
</RetryPolicyConfiguration>
<typeRegistrationProvidersConfiguration>
...
<add sectionName="RetryPolicyConfiguration"
name="RetryPolicyConfiguration"/>
</typeRegistrationProvidersConfiguration>
...
</configuration>
For more information about how to configure these strategies, see “Specifying Retry Strategies in the Configuration” on MSDN.
|
Poe Says: |
|
|---|---|
|
|
Adatum chose to define their retry strategies in a configuration file rather than in code to make it easier to modify them in the future. |
The following code sample from the ExpenseRepository class shows how Adatum uses a retry strategy when the application tries to save an expense item to the SQL Database database. The application uses the default retry policy when it invokes the SubmitChanges method.
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure
.TransientFaultHandling;
using Microsoft.Practices.TransientFaultHandling;
using Model;
...
private readonly RetryPolicy sqlCommandRetryPolicy;
...
public void SaveExpense(Model.Expense expense)
{
using (var db = new DataAccessLayer.ExpensesDataContext
(this.expenseDatabaseConnectionString))
{
var entity = expense.ToEntity();
db.Expenses.InsertOnSubmit(entity);
foreach (var detail in expense.Details)
{
var detailEntity = detail.ToEntity(expense);
db.ExpenseDetails.InsertOnSubmit(detailEntity);
}
this.sqlCommandRetryPolicy.ExecuteAction(
() => db.SubmitChanges());
}
}
Setup, Deployment, Management, and Monitoring
This section discusses the way that Adatum manages the setup and deployment of the Azure SQL Database server and the databases it contains, and how administrators and operators at Adatum manage and monitor the database.
Data for Development and Testing
It is possible to create separate databases within your SQL Database server instance for testing and for use during development. Alternatively, as when using SQL Server hosted in a virtual machine, you can use separate server instances to minimize the chances of corrupting live data. Adatum has a separate Azure subscription that is uses for development and testing, as described in Chapter 3 of this guide. The keys for accessing and deploying to the production subscription are not available to testers and developers.
Data Migration
To migrate an existing database schema to Azure SQL Database, you can use SQL Server Management Studio to export the schema as a Transact-SQL script, and then run the script against Azure SQL Database. To move data to Azure SQL Database, you can use SQL Server Integration Service. The SQL Server Migration Wizard is also very useful for migrating both schema and data.
One issue that Adatum faced is that the aspnet_regsql.exe tool used to create the tables for the ASP.NET profile provider will fail to run against an Azure SQL Database. For information about this incompatibility and how to work around it, see “Updated ASP.net scripts for use with Microsoft SQL Azure.”
|
Markus Says: |
|
|---|---|
.png "Markus")
|
The ASP.NET providers are not designed to retry operations when they encounter a transient fault. Adatum is investigating alternative solutions to storing the profile data for the aExpense application. |
When the team at Adatum migrated the live application they created scripts that create the required tables in SQL Database, and transfer data from the on-premises version of SQL Server to Azure SQL Database.
Note
For more information about data migration and the tools that are available to help, see “Data Migration to Azure SQL Database Tools and Techniques” and “Choosing Tools to Migrate a Database to Azure SQL Database.”
Data Management
To enable backup and restore functionality, Adatum plans to create a copy of the aExpense database daily using the following Transact-SQL command and maintain three rolling copies of the previous three days.
CREATE DATABASE aExpense_copy_[date] AS COPY OF [source_server_name].aExpense
This technique creates a copy of the aExpense database that includes all of the transactions that committed up to the time when the copy operation completed. Adatum can restore to a previous version of the database by renaming the copy, such as aExpense_copy_080612 to aExpense. However, Adatum will be charged for the storage required to maintain multiple copies of the database
|
Poe Says: |
|
|---|---|
|
|
This technique does not allow for point-in-time restore operations as you can do with SQL Server by running the RESTORE command. |
Adatum also plans to investigate using the SQL Database Import/Export Service to create a backup of the aExpense database in a different data center from the one where it hosts the aExpense application. This will ensure that Adatum has a copy of the aExpense data in the event of a disaster that leads to the data center where the aExpense application is hosted becoming unavailable.
Database Monitoring
SQL Database enables a subset of the dynamic management views that operators can use to diagnose performance problems in the database. Adatum plans to use these views in the aExpense database.
For more information, see “Monitoring Azure SQL Database Using Dynamic Management Views” on MSDN.
More Information
For information about Azure SQL Database, see “Azure SQL Database Overview.”
TechNet describes the differences between SQL Server and Azure SQL Database.
The MSDN topic “Migrating Databases to Azure SQL Database (formerly SQL Azure)” provides an overview of how to migrate a database to Azure SQL Database. You can also use the Migration Wizard at to help you to migrate your local SQL Server databases to Azure SQL Database.
See “Azure SQL Database Backup and Restore” and “Business Continuity in Azure SQL Database” for information about how you can back up Azure SQL Databases.
To encrypt your SQL connection string in the Web.config file, you can use the Pkcs12 Protected Configuration Provider that you can download from. The sections “Best Practices on Writing Secure Connection Strings for SQL Database” and “Create and deploy an ASP.NET application with Security in mind” in the post “Azure SQL Database Connection Security” discuss using this provider.
For more information about retrying database connections by using Enterprise Library, see “The Transient Fault Handling Application Block.”
“Overview of Options for Migrating Data and Schema to Azure SQL Database” contains useful information about migrating data to Azure SQL Database.