Azure Databases

A persistent data store is at the heart of many applications. As you migrate existing applications to the Azure cloud or create new applications, you will likely find yourself needing to interact with a database. The Azure platform provides several options from which to choose. You can choose from relational database offerings such as Azure SQL Database, SQL Server running in Azure Virtual Machines, or non-Microsoft databases such as Oracle or MySQL. If a non-relational (or NoSQL) database fits your application needs better, services such as DocumentDB and Azure Table Storage might be a good fit. Furthermore, with Azure Virtual Machines you can install a wide range of database platforms (see Chapter 3, "Azure Virtual Machines," for more information).

When the time comes to determine a data storage approach for your application, the Azure platform offers a variety of database choices, enabling you to balance reduced friction and management with fully customizable virtual machines.

Azure SQL Database

Azure SQL Database provides a relational database as a service, targeted at online transaction processing (OLTP; that is, data entry and retrieval transactions) workloads. This falls firmly in the platform as a service (PaaS) category of cloud computing. Using SQL Database enables you to give up the physical management responsibilities of a database server but retain the vast majority of logical management and administrative responsibilities. SQL Database provides many attractive features, such as elastic scale, predictable performance, business continuity, near-zero maintenance, and the use of familiar development languages and tools.

It's important to understand that with SQL Database you do not get a physical server that you can manage. Because SQL Database is a database as a service, the underlying physical implementation details are outside your control. There are still logical services when working with SQL Database, but these are not the same as the Microsoft SQL Servers you may be used to working with on-premises. More will be included later in this chapter about SQL Database servers.

SQL Database is available in two different, yet similar, models: elastic database pools and single databases. Elastic database pools enable you to manage multiple databases in a pool, scaling performance up and down as demand changes while maintaining a predictable budget. One of the key features of the elastic pool model is the ability to share performance across many databases in the pool. Alternatively, if you have only a handful of databases, the single database model might be more appropriate. Both models allow you to adjust performance as necessary with no downtime and provide a 99.99 percent service level agreement (SLA).

Both the elastic database model and single database model are available in three service tiers: Basic,

Standard, and Premium. Within these tiers, performance is expressed in database throughput units (DTUs). A DTU is a synthetic measure that allows a quick comparison of the relative performance of the various database tiers. Within each tier, there are also performance levels (for Standard, they are S0, S1, S2, and S3). These performance levels provide a way to increase or decrease the DTUs available within the tier. The maximum database size will vary across tiers, ranging from 2 GB to 1 TB. Table 6-1 lists some of the pertinent details for each of the database tiers.

Table 6-1 SQL Database tiers and performance levels

Service tier

Performance level

Maximum database size

DTUs

Target usage scenario

Basic

2 GB

5

Small databases with minimal concurrent operations. Typically development or test usage scenarios.

Standard

S0

250 GB

10

Cloud applications with multiple concurrent transactions.

S1

 

20

S2

 

50

S3

 

100

Premium

P1

500 GB

125

Mission-critical, enterprise-grade applications with high transaction rates and advanced business continuity features.

P2

 

250

P4

 

500

P6

 

1,000

P11

1 TB

1,750

Note Web and Business editions for SQL Database were retired in September 2015. For more information, please reference https://azure.microsoft.com/documentation/articles/sql-databaseweb-business-sunset-faq/.

Determining which service tier to use often depends on monitoring your application performance and then adjusting SQL Database tiers. You can start with a Basic tier, and based on performance indicators, scale up to a Standard or Premium tier if needed. Integrated tools such as Database Advisor (formerly known as Index Advisor), Query Performance Insight, and Query Store can help you gain a better understanding of how to best tune either the application or the database. Adjusting service tiers and performance levels is an online operation, so you can continue to use the database while the operation completes. When doing so, some database connections might be dropped. Be sure to include retry logic in your application to be resilient to such transient errors.

If you are migrating an existing on-premises Microsoft SQL Server database, you can use a third-party tool, the SQL Database DTU Calculator (available at http://dtucalculator.azurewebsites.net/) to estimate the performance level and service tier needed for a SQL Database instance. The tool works for both single and elastic databases, providing recommendations and DTU requirements.

See Also For more information on SQL Database tiers and performance levels, including DTUs and monitoring database performance, please visit https://azure.microsoft.com/documentation/articles/sql-database-service-tiers/.

SQL Database V12

In December 2014, Microsoft released to public preview a new version of SQL Database, dubbed V12. The V12 version of SQL Database reached general availability status in July 2015. One of the primary goals for the V12 version was to improve compatibility with Microsoft SQL Server. For example, previously unavailable features such as Change Tracking, Transparent Data Encryption (TDE), and Full-Text Search are available with SQL Database V12.

You should plan to use V12 for your SQL Database needs because new features will be added to V12 and not to V11.

For more information on SQL Database V12, including how to determine your version and upgrade information, please see https://azure.microsoft.com/documentation/articles/sql-database-v12whats-new/.

This chapter will assume the usage of SQL Database V12.

It is important to understand the relationship between a SQL Database server and a database. When you create a SQL Database server, you are creating a logical server that hosts a Tabular Data Stream (TDS) endpoint. TDS is the same communication protocol that's used with SQL Server. The logical server endpoint is identified by a URI, for example, contoso.database.windows.net. Each logical server can contain zero or more SQL database instances.

Creating a new SQL Database is a quick operation. To create a new SQL Database using the Azure portal, click the New button in the navigation pane and then select the Data + Storage category. From there, select the SQL Database option. This will open a new blade that provides an overview of SQL Database, along with links to additional helpful information. Clicking the Create button will open a new blade, as seen in Figure 6-1, allowing you to configure key information for SQL Database.

Create a new SQL Database instance.

On the SQL Database blade, you can enter several key pieces of information, including the following:

  • Name: Provide the name for the new database.
  • Server: Select an existing SQL Database server or create a new server. When creating a new server, you will be able to provide the server name (for example, contoso.database.windows.net), the administrative login and password, and the Azure region.
  • Source: Select the source for the new database: a blank or empty database, a restore from a previous database backup, or a sample AdventureWorksLT database.
  • Pricing Tier: Select one of the available service tiers (Basic, Standard, or Premium) and associated performance levels.
  • Collation: Set the collation used for rules related to sorting and comparing data.
  • Resource Group: Select an existing resource group or create a new one where the SQL database will reside. Resource groups are helpful for grouping related Azure resources.
  • Subscription: Select the desired Azure subscription.

When finished, click Create. It might take a few minutes for Azure to provision the new SQL database. If you're creating a new database on an existing server, the new database likely will be ready within a few seconds.

As indicated in Table 6-1, the maximum size for a SQL Database instance is 1 TB at the P11 level. If your data needs exceed the capacity of a single database, you will need to use an alternative strategy to persist the necessary data. One such strategy is to spread the data across multiple databases, a process referred to as database sharding. The ability to create new database shards quickly allows for elastic scale. Application owners can decide how and when to create new database shards to scale out quickly, thereby enabling an application to scale out across multiple databases. For more information on elastically growing and shrinking databases, please read the guidance on SQL Database Elastic Database features at https://azure.microsoft.com/documentation/articles/sql-database-elastic-scaleintroduction/. As of this writing, many of the elastic database features of SQL Database are offered in a preview capacity.

Administration

One of the attractive features of SQL Database is the near-zero maintenance it provides. Microsoft handles all patching, server configuration, load balancing, and database platform upgrades automatically. Additionally, SQL Database handles management of system tables and filegroups automatically. You are required to perform common administrative tasks such as managing logical aspects of the database, including logins, tuning indexes, and query optimization.

When using SQL Database, you can use the same tools, programming languages, and frameworks you are accustomed to using with SQL Server. SQL Database and SQL Server are similar in many ways, although not every SQL Server feature is available in SQL Database (see the section "Comparing SQL Database with SQL Server in Azure Virtual Machines" later in this chapter). However, the two do share one very important feature: both use TDS as the client protocol. This allows tools such as SQL Server Management Studio (SSMS) to connect to SQL Database.

Firewall settings

Before you can connect to SQL Database from any tool, including SQL Server Management Studio, you will need to adjust a firewall setting. SQL Database is preconfigured with firewall settings that will explicitly deny access from any IP address, even those originating from within Azure.

When creating a new SQL Database server in the Azure portal, the default is to allow any Azure service (such as your Azure Web App) in any Azure subscription to access the server, as you can see in Figure 6-2.

It's generally not recommended to allow server access (via firewall rules) to all Azure services. Instead, it's recommended to enable access to only specific IP addresses that require access.

Note The act of creating a SQL Database instance via the Azure portal will automatically enable access to the database from other Azure services. This is not the case when creating a SQL Database instance via other means, such as by using PowerShell or an Azure Resource Manager template.

To access the SQL Database server from outside Azure—for example, from SQL Server Management Studio—you will need to modify the server firewall to allow access from the desired IP address (or range). From the SQL Server blade, in the Essentials section, click the Show Firewall Settings link, as seen in Figure 6-3.

SQL Database server firewall settings.

Doing so opens a new blade that allows you to manage various aspects of the SQL Database server firewall settings. On the Firewall Settings blade, you can enable or disable access to the server for Azure services or provide rules to allow access to the server from a specific IP address (including your specific client IP address). If you are hosting other services in Azure (such as Azure Web Apps or Cloud Services) that need access to the SQL Database instance, you will need to set Allow Access To Azure Services to On.

Note It is also possible to set database-level firewall rules in addition to the server-level firewall rules available in the Azure portal. Database-level firewall rules can be set programmatically via TSQL statements. For more details, see https://azure.microsoft.com/documentation/articles/sqldatabase-firewall-configure/#creating-database-level-firewall-rules.

Connect using SQL Server Management Studio

Once you have added your IP address to the list of allowed IP addresses, open SQL Server Management Studio. You will need to know the full name of the SQL Database server. You can find the full server name in the Essentials section of the SQL Database blade, along with other key information, as shown in Figure 6-4.

Database Essentials section.

When the Connect To Server dialog box in SQL Server Management Studio opens, as seen in Figure 65, enter the full server name, select SQL Server Authentication, and provide the administrative login and password you set when creating the database.

Figure 6-5 Connect to SQL Database from SQL Server Management Studio.

Note It is also possible to connect to SQL Database by using Azure Active Directory authentication, thus providing a centralized location for management of database users and other Microsoft services. For more information on connecting to SQL Database via Azure Active Directory authentication, please see https://azure.microsoft.com/documentation/articles/sql-database-aadauthentication/.

Billing

Because SQL Database is sold as a service, there is not a separate SQL Server license like you might find with on-premises SQL Server or with SQL Server in an Azure VM. Instead, you are charged per hour based on the highest SQL Database service tier and performance level used during the hour (recall you can change tiers at any time). For example, if you start at 3 PM with an S1 and at 3:20 PM change to an S3, you are charged the S3 rate for the entire hour. The pricing change becomes effective when the change in tier or performance level is completed. For a detailed breakdown of SQL Database pricing, please visit http://azure.microsoft.com/pricing/details/sql-database/.

See Also Please reference the SQL Database FAQ at https://azure.microsoft.com/documentation/articles/sql-database-faq/ for additional helpful information.

In addition to understanding how SQL Database is priced, it's important to understand how many databases you can get. After all, the number of databases you have will directly affect the price you pay. By default, you can have up to six logical SQL Database servers per Azure subscription. Each server can host a maximum of 5,000 databases and 45,000 DTUs. The default limits are soft limits, and they can often be raised by submitting a support ticket with Azure Support.

Note When planning capacity for a SQL Database logical server, it is important to note the specific SKU (S0, P1, and so on) being used. Each SKU has associated DTUs. The DTU capacity is the real limit on the maximum number of databases supported.

As performance is highly workload dependent, it is important to test your solution thoroughly— don't assume 5,000 databases on a SQL Database server will all perform equally well.

Business continuity

SQL Database provides several options to address business continuity requirements. One way in which SQL Database provides protection is through infrastructure redundancy. At any time in an Azure datacenter, there could be a hardware failure (such as hard drive, network, or entire servers). SQL Database provides high availability in the case of such hardware failures by keeping copies of the data on physically separate nodes. Three database nodes, or replicas, are always running: one primary replica and two secondary replicas. For write operations, data is written to the primary and one of the secondary replicas before the write transaction is considered complete. In the event of a failure of the primary replica, SQL Database detects the failure and fails over to a secondary replica. If needed, a new replica is then created.

Furthermore, business continuity with respect to databases often includes two categories: database recovery and disaster recovery. Database recovery refers to the ability to mitigate risk and recover from database corruption or an unintentional modification or deletion of data. To assist with database recovery, SQL Database provides a feature called Point-in-Time Restore. Point-in-Time Restore allows you to restore a database to any previous point. The timeframe from which you can restore varies based on the selected SQL Database tier: 7 days for Basic, 14 days for Standard, and 35 days for Premium.

To restore a database to a previous point, first select the desired database in the Azure portal and then click Restore, as shown in Figure 6-6.

Clicking the Restore button opens a new blade, as shown in Figure 6-7, that allows you to enter the name for the restored database (or keep the autogenerated default name) and the restore point (date and time, at one-minute intervals).

The restore operation might a long time to complete. The exact time to restore can be difficult to predict because it depends on several factors, including the size of the database, the restore point in time selected, and the activity log that needs to be replayed to get to the restore point. For some large databases, this process could take several hours.

If you have deleted a database, you can restore the entire database. To do so, first select the SQL Database server that contained the database and then select Deleted Databases in the Operations group of the SQL Server blade. This opens a new Deleted Databases blade, as shown in Figure 6-8.

If there are multiple deleted databases, select the database to restore. On the resulting Restore blade, provide a name for the database to be restored, as shown in Figure 6-9. The database can only be restored to the point at which it was deleted. After you click Create, the restore request will be submitted. Just like a point-in-time restore, the process to restore a deleted database could take a long time to complete.

Settings for restoring a deleted database.

Point-in-Time Restore is helpful when you need to recover a database to a known good point, which is often a result of user error. However, this is only one aspect of business continuity, the other being disaster recovery. Disaster recovery refers to the ability to restore operations to a working state in the event a disaster renders the primary region unrecoverable or unavailable for an extended period of time. SQL Database provides additional features that can be helpful in preparing a disaster recovery plan: Geo-Restore, Standard Geo-Replication, and Active Geo-Replication.

Geo-Restore

The Geo-Restore feature in SQL Database allows you to restore a SQL database from a backup to any SQL Database server in any Azure region. The time to restore will vary based on size of the database, performance level, and number of concurrent restore requests in the target Azure region. Both Pointin-Time Restore (as discussed earlier in this chapter) and Geo-Restore are possible because SQL Database automatically creates backups of every database. Full backups are performed once a week, differential backups once a day, and transaction log backups every five minutes. The backup data is persisted in Azure Blob storage (RA-GRS) in a geo-redundant paired region (for example, East US and West US, North Europe and West Europe), as seen in Figure 6-10.

Figure 6-10 SQL Database configured to automatically back up to geo-replicated storage.

To restore from a backup, start by following the same steps you would if creating a new SQL

Database. Instead of choosing the source database to be blank or a sample, select the Backup option. Selecting Backup as the source will enable you to then select one of the available backups, as seen in Figure 6-11.

Figure 6-11 Select the desired backup as the source for a new SQL Database.

Complete the remaining sections by entering the desired new database name, target server (either select an existing one or create a new one), resource group, and other information.

Standard Geo-Replication

Available for Standard and Premium databases, Standard Geo-Replication enables you to create a single offline secondary database in the paired region of the primary database. The secondary database is unavailable for client connections until the region hosting the primary database fails. The secondary database is charged at 75 percent of the primary.

Note In April 2016, Microsoft announced the retirement of Standard Geo-Replication effective in March 2017.

Active Geo-Replication

Active Geo-Replication, which is available for all database tiers, enables you to create up to four readable secondary databases across multiple Azure regions. It is up to you to determine when to fail over one of the secondary databases (unlike Standard Geo-Replication). Each readable secondary is charged at the same rate as the primary.

To enable Standard or Active Geo-Replication, use the Azure portal and select the desired database. From the Geo Replication group or the link available under Geo-Replication Role in the Essentials section, shown in Figure 6-12, you will be able to see a map displaying any existing secondary database or an option to configure geo-replication if none has been configured.

Select the Geo-Replication part (displaying the map) to open a new Geo-Replication blade. On this blade, you can view all of the potential secondary locations and then select the desired location. Note that for a Standard database, only the paired region will be available. For a Premium database, you can select from any of the available locations.

Click the location in the map, as seen in Figure 6-13, or select from the list of Target Regions displayed on the blade. This will open a new Create Secondary blade to enable you to configure properties related to the secondary database.

Figure 6-13 Geo-replicated secondary configuration settings.

Alternative configuration options

In addition to using the Azure portal to configure geo-replication, it is possible to configure georeplication using Transact-SQL (T-SQL), PowerShell, or a REST API. To find out more about these options, go to:

For Standard Geo-Replication, the Secondary Type will default to Non-Readable only. For Active GeoReplication, the Secondary Type will default to Readable and you will be able to select from multiple Azure regions for the region.

Design considerations

You'll need to familiarize yourself with several important concepts when determining which SQL Database business continuity feature to use. The business continuity features for SQL Database across the service tiers, including RTO and RPO, are described in the following list and are depicted in Table 6-2.

  • Estimated Recovery Time (ERT): Indicates the estimated time needed before the database is fully available after a restore or failover request
  • Recovery Time Objective (RTO): Indicates the maximum downtime before the application is functional after a disaster
  • Recovery Point Objective (RPO): Indicates the maximum amount of recent data loss (in terms of time) before the application is functional after a disaster

Table 6-2 Business continuity options for SQL Database tiers

Business continuity feature

Basic tier

Standard tier

Premium tier

Point-in-Time Restore

Last 7 days

Last 35 days

Last 35 days

Geo-Restore

ERT < 12 hours

RPO < 1 hour

ERT < 12 hours

RPO < 1 hour

ERT < 12 hours

RPO < 1 hour

Active Geo-Replication

N/A

N/A

ERT < 30 seconds

RPO < 5 seconds

Note It is important to understand that the ERT and RPO values provided above are not part of the official service level agreement for SQL Database. They are engineering goals only and provided for guidance and planning.

Please see the guidance available at https://azure.microsoft.com/documentation/articles/sqldatabase-business-continuity-design/ for a detailed breakdown of business continuity design considerations with respect to SQL Database.

Service level agreement

Microsoft provides a 99.99 percent database connectivity service level agreement for Basic, Standard, and Premium tiers. The service level agreement only applies to being able to connect to the database, not to any performance targets with respect to the various tiers.

Applications connecting to SQL Database

When writing applications that need to connect to SQL Database, you can use popular programming languages such as .NET, PHP, Java, and many more. Entity Framework, starting with .NET Framework 3.5 Service Pack 1, is also supported. One of the first things you'll need is the connection string. You can obtain the connection string from the Azure portal by clicking the Show Database Connection Strings link in the Essentials group for the desired SQL Database instance. Doing so opens a new Database Connection Strings blade, as seen in Figure 6-14, displaying the connection string in multiple formats, including ADO.NET, PHP, JDBC, and ODBC (which works for Node.js applications).

The connection string for SQL Database is similar to what you would use for SQL Server. For example, for ADO.NET, the connection string format is as follows:

ADO.NET Database connection string

Server=tcp:{your_db_server_name_here}.database.windows.net,1433;Database={your_db_name_here};User ID={your_username_here}@{your_db_server_name_here};Password={your_password_here};Encrypt=True;Tru stServerCertificate=False;Connection Timeout=30;

Note that the connection string sets the TrustServerCertificate property to False and the Encrypt property to True. This is to provide additional protection while accessing SQL Database over the Internet. Doing so helps thwart potential man-in the-middle attacks. SQL Database will force the connection to be encrypted regardless of the setting.

When writing code against SQL Database, it is important to defend your code against transient errors.

Transient errors are errors that are intermittent and likely will be resolved if the command is retried. These errors are more common with SQL Database than with databases accessed via a local area network (LAN). This is due to the inherently unreliable network that is the Internet and the fact that as a managed service, SQL Database might periodically undergo maintenance activities that could cause connections to drop temporarily. Applications should plan for and defend against transient errors by incorporating retry logic when creating connections or executing commands against SQL Database. Be sure to choose a retry strategy that provides sufficient time for the platform to attempt to recover from whatever caused the initial failure, does not saturate the platform with a rapid succession of retry attempts, and has a maximum number of retry attempts and/or retry duration. For example, an exponential back-off approach with a preset maximum number of attempts is often suitable—be sure to validate for your application scenario.

For .NET applications using Entity Framework, Entity Framework 6 contains connection resiliency/retry logic that will detect transient errors from SQL Database and retry the command. For other .NET applications, Enterprise Library 5 and 6 (see http://msdn.microsoft.com/library/ff648951.aspx) from Microsoft Patterns & Practices contain an application block called the Transient Fault Handling Application Block. This library can also be used to detect transient errors and retry commands.

SQL Database connection and retry guidance

Microsoft no longer maintains Enterprise Library 6, and its source code has been made available to the public. Microsoft does provide example retry logic at https://azure.microsoft.com/documentation/articles/sql-database-develop-csharp-retry-windows/. Either approach is valid.

Please reference the SQL Database best practices and design guidelines available at https://azure.microsoft.com/documentation/articles/sql-database-connect-centralrecommendations/ for more detailed information and recommendations on connecting to SQL Database.

Finally, Microsoft's Patterns & Practices team has published detailed retry guidance for a variety of Azure services, including SQL Database. Please reference the guidance at https://azure.microsoft.com/documentation/articles/best-practices-retry-service-specific/ for more information, including code samples.

There is much more to designing a resilient cloud-based application than implementing basic retry logic. It may also be necessary to reconsider how an application processes data. For instance, it is often helpful to implement an eventually consistent solution, potentially using queues and background tasks to complete requests. It may also be worth considering breaking read and write operations across separate databases, potentially using the Active Geo-Replication feature in SQL Database to replicate operations on the write database to the read database.

SQL Server in Azure Virtual Machines

Although SQL Database provides database as a service with enterprise-grade features and virtually no administration, there are still situations in which running your own SQL Server deployment may be necessary. A common reason is the requirement to use features that are not available in SQL Database.

As discussed in Chapter 3, Azure Virtual Machines provides the ability to host and manage your own virtual machines (VMs). What you use the VM for is largely your responsibility, and this includes using it to install, configure, and manage your own full SQL Server VM or cluster of SQL Server VMs.

Billing

When running your own SQL Server deployment on Azure Virtual Machines, you must understand three important cost factors. First is the cost of the Windows VM itself. Recall that Azure VMs are charged on a per-minute usage model. Second is the SQL Server license cost. When using a SQL Server image from the Azure Marketplace, you will pay an additional per-minute SQL Server license cost, which varies according to the version of SQL Server (Web, Standard, or Enterprise) and the target size of the VM. Finally, you'll also pay for the Azure Storage cost. Azure Storage (specifically page blobs) is used as the persistence mechanism for Azure Virtual Machines disks. To summarize, the cost for SQL Server in Azure Virtual Machines can be represented as Total cost = Windows Server cost + SQL Server license cost + Azure Storage cost.

If you have your own SQL Server license, you can use that instead of paying the per-minute charge associated with using a SQL Server license obtained from an Azure Virtual Machines image. In this case, you pay only for the Windows Server license and any related Azure Storage costs. The ability to use your own SQL Server license is a feature of License Mobility through Microsoft's Software Assurance on Azure program. For more information, see http://azure.microsoft.com/pricing/licensemobility/.

Virtual machine configuration

When configuring SQL Server in Azure Virtual Machines, take into consideration the following:

  • VM considerations
  • Use a DS2 VM or higher for SQL Server Standard or Web edition.
  • Use a DS3 VM or higher for SQL Server Enterprise edition.
  • Storage considerations
  • Use Azure Premium Storage.
  • If using multiple data disks to store database data and log files, disable geo-replication because consistent write order across disks is not guaranteed.
  • Disk considerations
  • Use at least two P30 disks: one for log files and one for data files.
  • Don't store database data and log files on the D drive. The D drive is a physical temporary disk and is not persisted to Azure Blob storage. However, if you're using a D-series or G-series VM, you might consider storing the tempdb database on the D drive. D-series and G-series VMs use an SSD drive for the D drive, and thus tempdb performance could be improved.
  • For workloads that exceed the IOPS limit for a single data disk, you may attach multiple data disks (up to the maximum allowed by the VM size) and use disk striping as a way to increase IOPS.

See Also For a comprehensive review of performance best practices for SQL Server in Azure Virtual Machines, please read the MSDN guidance at https://azure.microsoft.com/documentation/articles/virtual-machines-sql-server-performance-bestpractices/.

Business continuity

Most of the high availability and disaster recovery (HADR) solutions you might run for on-premises SQL Server deployments are also available when running SQL Server in Azure Virtual Machines. But why do you need to be concerned about HADR for SQL Server in Azure Virtual Machines? As discussed in Chapter 3, Azure provides high-availability features for the VMs, but not necessarily for SQL Server running on the VM. It is possible for the VM to be online but the SQL Server instance to be offline, unhealthy, or both. Additionally, it is possible for the VM to unavailable due to hardware failure or software upgrades. Therefore, a practiced HADR strategy should be considered.

SQL Server in Azure Virtual Machines supports many of the same HADR technologies that are available for on-premises SQL Server deployments: AlwaysOn, database mirroring, log shipping, and backup to and restore from Azure Blob storage (available in SQL Server 2012 and SQL Server 2014). Depending on the technology used, it might be possible to establish a hybrid topology to allow the HADR technology to span between an Azure region and an on-premises datacenter. Some options, such as SQL Server AlwaysOn, allow for a topology that can even span multiple Azure regions.

See Also For more detailed information on how to configure various HADR solutions with SQL Server in Azure Virtual Machines, please refer to the guidance at https://azure.microsoft.com/documentation/articles/virtual-machines-sql-server-high-availabilityand-disaster-recovery-solutions/.

Comparing SQL Database with SQL Server in Azure Virtual Machines

The decision to use SQL Database or SQL Server in Azure Virtual Machines can be difficult. On the one hand, SQL Database is ideal for reducing the administrative cost related to provisioning and managing relational databases because many tasks such as upgrades, patching, backups, and business continuity scenarios are handled automatically. On the other hand, SQL Server in Azure Virtual Machines provides the option to migrate or extend existing on-premises SQL Server workloads to Azure. Even though there are additional administrative costs with running SQL Server in Azure Virtual Machines, the ability to maintain fine-grained control over those tasks could be worthwhile for some users and scenarios.

Although SQL Database and SQL Server are similar in many areas, some key differences exist, most notably SQL Server features that are not currently supported in SQL Database, such as the following:

  • Windows authentication.
  • FILESTREAM data.
  • Database mirroring.
  • Extended stored procedures.
  • SQL Server Agent/Jobs.
  • SQL Server Reporting Services (SSRS) and SQL Server Integration Services (SSIS) are not supported. Alternatively, run a SQL Server on-premises or in an Azure VM and connect to a SQL database.
  • T-SQL features
  • USE statement is not supported. To change databases, a new connection must be established.
  • Common language runtime (CLR).
  • Cross-database queries using three or four part names.

Note The limitations listed above are only a few of those of which you should be aware when working with SQL Database. For a complete list, please see the guidance on MSDN at https://azure.microsoft.com/documentation/articles/sql-database-general-limitations/.

You can also find the related T-SQL statement references at https://azure.microsoft.com/documentation/articles/sql-database-transact-sql-information/.

Additionally, elastic database query provides limited support for querying across databases in SQL Database. Please see https://azure.microsoft.com/blog/querying-remote-databases-in-azure-sqldb/ for additional information.

There are many factors to consider when choosing between SQL Database and SQL Server in Azure Virtual Machines: database size, existing application versus new application, level of administrative control (including hardware infrastructure), business continuity strategy, and hybrid scenarios, just to name a few. SQL Database is often the right solution for cloud-designed applications that are not using unsupported features and for which near-zero administration is a key priority. SQL Server in Azure Virtual Machines is often the right choice for new or existing applications that require a high level of control and customization (that is, full compatibility with SQL Server) and for which there is a desire to no longer maintain on-premises hardware.

Database alternatives

There are many database options available in the market today. The Microsoft Azure platform makes it easy to run a wide range of popular databases—you don't have to run SQL Database or Microsoft SQL Server. As discussed in Chapter 3, you can run the software of your choosing on an Azure VM, including the database platform you desire. You also have the option to run MySQL as a service via Microsoft's partnership with SuccessBrick's ClearDb offering. If a relational database management system (RDBMS) is not what you're after, using a NoSQL service such as DocumentDB or Azure Table storage is also an option.

MySQL

Another popular relational database is MySQL. Microsoft has collaborated with SuccessBricks to bring SuccessBricks' ClearDb database as a service for MySQL to the Azure platform.

To get started, open the Azure portal and click the green New button in the upper-left corner. From the Data + Storage category, find the MySQL Database feature in the list of available services, as shown in Figure 6-15.

When the New MySQL Database blade opens (Figure 6-16), you'll have the opportunity to provide the necessary details about your new MySQL database, including the following:

  • Database Name: The name for the new database.
  • Subscription: The desired Azure subscription.
  • Database Type: Select Shared for lightweight database workloads and Dedicated for a Premium tier database hosted in single-tenant clusters.
  • Resource Group: Select an existing group or create a new logical group where the new MySQL Database will reside. Resource groups are helpful for grouping related Azure resources.
  • Location: The desired Azure region.
  • Pricing Tier: Select one of the available pricing tiers. These tiers are not related in any way to the SQL Database tiers or performance levels.
  • Legal Terms: Agree to the legal terms, which detail that the service is provided by SuccessBricks and not Microsoft, to continue.

Figure 6-16 New MySQL Database blade settings.

When finished, click the blue Create button at the bottom of the blade to submit the request to create the new MySQL database. Once the database is created, the database blade will automatically open. Clicking the Properties option on the Settings blade will open a new blade, as seen in Figure 6-17, allowing you to view details about the new MySQL database, such as the full hostname, username, password, connection string, and more.

Furthermore, the Azure Marketplace provides numerous additional database options, such as MongoDB, Bitnami's MySQL, and DataStax Enterprise, as seen in Figure 6-18. All are available to be hosted using Azure Virtual Machines.

Figure 6-18 Database options available in the Azure Marketplace.

NoSQL options

You don't always need a relational database management system (RDBMS)–based data storage solution such as SQL Database or SQL Server in Azure Virtual Machines. The proper choice, based on specific application needs, might be a NoSQL solution. Azure provides two NoSQL options: DocumentDB and Azure Table storage.

DocumentDB

DocumentDB is a fully managed, highly scalable, NoSQL document database service available on the Azure platform. DocumentDB is designed to natively support JSON documents, and it automatically indexes all JSON documents added to the database. You use familiar SQL syntax to query the documents.

See Also For more information on DocumentDB, please see http://azure.microsoft.com/services/documentdb/.

Table storage

Azure Table storage is a cost-effective, highly scalable, key/value NoSQL store available on the Azure platform. Table storage is capable of storing up to 500 TB per storage account, with a single subscription supporting 100 storage accounts. Table storage is a semi-structured NoSQL data store that uses two keys, a Partition Key and a Row Key, as the primary composite index for the table.

See Also For more information on Azure Table storage, please see https://azure.microsoft.com/documentation/articles/storage-introduction/#table-storage.