Code Library

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.

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.

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.

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.

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-scale-introduction/. As of this writing, many of the elastic database features of SQL Database are offered in a preview capacity.

Source of Information : Microsoft Azure Essentials Fundamentals of Azure Second Edition