The Tenant Ring

A tenant ring in the Windows Fabric is nothing but a physical Windows Azure cluster, where each node of the cluster is designed to run an application. In this case, the application being run is of type DBService, which is basically a SQL Server engine service or the Hekaton (in-memory OLTP) engine service. Each DBService application is bound to its own memory, CPU, and IO.

When customers provision an Azure SQL Database, they create a logical server (if it doesn’t already exist) and a database. They are actually creating a DBService (or DBSvc) in the backend. This DBService application runs on one of the nodes of the Windows Azure cluster. The DBService can use the local storage on the VM (or the host) and remote storage (see Figure 8-1) for storing the database.

Figure 8-1. DBService running on Windows Azure clusters

The tenant ring architecture allows for the creation and functioning of multiple DBServices, belonging to one or more customers (tenants). Since each DBService runs in its own context and is independent of other DBService applications running on the same node in the tenant ring, this architecture allows the same node/VM to host multiple customer databases.

The following services form the core of the DBService application:

• SQL Server Executable

• Watchdog.exe

There might be additional services when Hekaton (in-memory OLTP) or full text is also required. Additionally, there might be other services that are needed when Azure SQL Database forms the backend to Azure SQL DW.

The Control Ring

The control ring functions to provide management, provisioning, and redirecting services (see Figure 8-2). It helps determine the location of the databases in the tenant rings and routing connections to the right tenant databases. Control ring provides the following major services: Control Management Nodes, Management Services Nodes, and Redirector Services Nodes.

Figure 8-2. Control ring architecture

Elastic Database Pool

Azure SQL Database also allows for creation and management of multiple databases in an Elastic Database Pool . To understand Elastic Database Pool in more details, consider an example.

Let’s say there is an ISV that provides SaaS (software as a service) services to multiple customers. For each of the customers, this ISV has to provision a database to be used as the backend of the software service. Each of these customers has varying peak usage times and requirements. Let’s say for a customer A, the peak usage varies from 200 connections per second to 1000 connection per second. This variation in load might not be bound to specific times of the day. Under such circumstances it becomes difficult to provision a database, under a single service tier, which would allow optimal performance and be cost effective at the same time. Since the user load may not be time-dependent, the ISV will more likely have to provision a database with the highest service tier, which meets the peak load requirements. This obviously is not a cost-effective solution .

Elastic Database Pool provides a solution for such problems. In simple terms, Elastic Database Pool provides a set of shared DTUs (eDTUs to be more precise) associated with the pool, which can be utilized by the databases in the pool. For example, consider a standard S3 tier of elastic pool that has about 800 DTUs. A user can create a maximum of 400 databases in this pool, which would allow these databases to share and consume DTU resources without needing to assign a specific performance level to the databases in the pool. The arrangement allows multiple databases with varying workloads to optimally use the available DTUs in the pool. Figure 8-5 provides a listing of various limits for elastic pool in the different service tiers.

Figure 8-5. Elastic pool limits for various service tiers

Service Tiers: Limits and Capabilities

Each of the services tiers available with the Azure SQL Database has specific limits , some of which are mentioned in Figure 8-6 and in the following list. Again, as mentioned earlier, updated information can be found in the Azure Documentation on MSDN.

Figure 8-6. Service tiers capabilities and limits

• Maximum Database Size. Specifies the max size limit on the database. As mentioned in the table, the maximum limit for a basic tier database is 2GB, while the max for a Premium P11 database is 1TB.

• Automatic Backups and Point-In-Time Restore. Azure SQL Database provides automatic full and log backup features. These backups can be restored to any point in time in the retention period as specified by the service tiers. For example, the standard tier DB backups are retained for 14 days by default and hence can be restored to any point in time in the last 14 days. We will be covering high availability, business continuity, and disaster recovery in full in the Chapter 9.

• Max In-Memory OLTP Storage. Specifies the maximum amount of storage allowed for storing in-memory OLTP objects. This is only applicable for premium tiers since the other tiers do not support in-memory OLTP optimizations.

• Max Concurrent Requests. The maximum number of concurrent user or application requests that can execute on the database. Readers familiar with Microsoft SQL Server might remember that SQL Server exposes a DMV sys.dm_exec_requests, which lists all the active requests running on the server. Similar DMVs can be used with WASD to get the total number of requests active on the database at any point in time.

• Max Concurrent Logins. This represents the limit of the number of users or applications allowed to log in to the database at the same time. This limit is not applicable to Elastic Database Pool.

Azure Portal

Azure Portal is a web based application that provides capabilities to create, delete, restore, and manage Azure SQL Database and the associated logical server. It also provides capabilities to monitor database performance, configure security and high availability, and the ability to change the service tier of the database.

Let’s look at a couple of important tasks that can be done using Azure Portal: creating databases and managing database properties.

Create Database

When creating the database using Azure Portal (this example uses the New Azure Portal), we get the choice of choosing an existing server or creating a new logical server. If a new server is being created as part of the DB creation, users have the option to choose the data center in which the server and the database will reside. (See Figure 8-7, which indicates that the new database would be created on a logical server tnkl47icl in the Southeast Asia data center.)

Figure 8-7. Choosing the service tier and performance level while creating a new SQL database

Users can also choose the service tier and the performance level of the database being created, as illustrated in Figure 8-7. The service tier and the performance level of a database can be changed using the Management portal, after creating the database.

Managing Database Properties

Azure Portal can be used to enable, disable, or change the properties of the database. As illustrated in Figure 8-8, the Azure Portal can also be used to restore, export, change the service tier, and enable or disable auditing and monitoring on the database.

Figure 8-8. Database details and management options available through the Azure Portal

One of the most important uses of the Azure Portal is to configure firewall exceptions for connections from client IPs. Azure by default blocks all incoming traffic to the SQL database. An exception should be added before the database can be accessed by any of the client tools like SQL Server Management Studio (SSMS) or SQL Server Data Tools (SSDT).

This setting is performed at the server level, as illustrated in Figure 8-9.

Figure 8-9. Configuring firewall options for an Azure SQL database server

Note

Auditing, security, monitoring, high availability, and performance troubleshooting are covered in the later chapters.

SQL Server Management Studio

SQL Server Management Studio, a very well-known management utility available with SQL Server since 2005, can be utilized to manage and perform development of the Azure SQL Database. As illustrated in Figure 8-10, SSMS can be used to connect to the Azure SQL Database and perform management or other operations on the database.

Figure 8-10. Connecting to SQL Database using SQL Server Management Studio (SSMS)

Azure SQL databases support both SQL Authentication and Windows Authentication (when using AD federation between on-premises AD and an Azure AD).

When connecting to Azure SQL Database, you need to specify the name of the logical SQL Server created as part of the database creation. The name of the logical SQL Server database is always in the format shown in Figure 8-10.

Figure 8-11. Exploring SQL Database using SQL Server Management Studio

SSMS can be utilized to perform a lot of activities, such as:

• Monitoring and management using Extended Events

• Database creation, design, and development

• Managing security

The following T-SQL command can be used to create an Azure Azure SQL database:

CREATE DATABASE MyTestDatabase3
(
   MaxSize = 1 GB, ---> Maximum Size of the Database. This value cannot be grater than the size supported by the Service Tier
   Edition = 'Standard', --> The Service Tier for the Database
   Service_Objective = 'S0' --> Performance Level For the Database.
)                                                   

In this example, we are creating a database in the standard service tier, with a performance level of S0 (10 DTUs). We are also specifying that the max size of the database will be 1GB.

SQL Server Data Tools (SSDT)

SSDT is a free downloadable utility that can be used to build SQL Server relational databases, Azure SQL databases, SSIS packages, SSAS data models, and SSRS reports. With SSDT, you can design and deploy any SQL Server content type with the same simplicity as you develop an application in Visual Studio.

SSDT is primarily a development environment that can be used to design the Azure SQL Database. The connection strings used to connect to the SQL Database are the same as used with SSMS. Figure 8-12 illustrates how to connect to an Azure SQL Database using SSDT.

Figure 8-12. Using SSDT to connect to and design an Azure SQL database

Command-Line Utilities and REST APIs

Command-line utilities like PowerShell can be used to create and manage Azure SQL databases. Similarly, Azure exposes a set of REST APIs that can be utilized to work with Azure SQL databases.

PowerShell can be utilized to perform almost all operations related to an Azure SQL database. Listing 8-1 uses the Azure PowerShell cmdlets to perform the following tasks:

1. Log in to Azure.

2. Select a subscription to work with.

3. Select an existing ResourceGroup or create a new one. When you’re creating a new ResourceGroup, the script allows users to select the data center.

4. Select an existing logical server or create a new one.

5. Configure the firewall rules to allow connections to the logical server from the client machine.

6. Configure the SQL Database. The script configures a database in the basic service tier.

Listing 8-1. Using PowerShell to Create and Configure an Azure SQL Database

#Login to your Azure Subscription.
Add-AzureRmAccount

#Get a list of all the Subscriptions associated with the login
$subscriptions = Get-AzureRmSubscription
#Choose a Valid Subscription from the list returned by the Previous Command                                                
$count = 1
Foreach ($subscription in $subscriptions)
{
    Write-Host $count.ToString() " -:" $subscription.SubscriptionName
    $count +=1
}
Write-Host "Choose the Subscription where the databases has to be deployed -" -ForegroundColor Yellow
$SubsChoice = Read-Host
$Arrayindex = $SubsChoice-1
Select-AzureRmSubscription -SubscriptionId $subscriptions[$Arrayindex].SubscriptionId

#Get the Azure ResourceGroups available in the Subscription or create a New ResourceGroup

$resourceGroupName = ""
$DCLocation = ""
$option = Read-Host "Do you want to use an existing Resource Group (y/n) :-"
if($option -eq "y")
{
    $ResGroups = Get-AzureRmResourceGroup | Where-Object{$_.ResourceGroupName -NotLike "Default*" }
    #Choose a Valid Subscription from the list
    $count = 1
    Foreach ($ResGroup in $ResGroups)
    {
        Write-Host $count.ToString() " -:" $ResGroup.ResourceGroupName
        $count +=1
    }                                                                              
    Write-Host "Choose the ResourceGroup to which the databases has to be deployed -" -ForegroundColor Yellow
    $ResGrChoice = Read-Host
    $Arrayindex = $ResGrChoice-1
    $resourceGroupName = $ResGroups[$Arrayindex].ResourceGroupName
    $DCLocation = $ResGroups[$Arrayindex].Location
 }
 Else
 {                                                                              
    Write-Host "Enter the New ResourceGroup Name" -ForegroundColor Yellow
    $resourceGroupName = Read-Host

    $locations = Get-AzureLocation
    $count=1
    Foreach ($location in $locations)
    {
        Write-Host $count.ToString() " -:" $location.Name
        $count +=1
    }
    Write-Host "Choose a Data Center Location " -ForegroundColor Yellow
    $LocChoice = Read-Host
    $Arrayindex = $LocChoice-1
    $DCLocation = $locations[$Arrayindex].Name

    try
    {
        $resourceGroup = New-AzureRmResourceGroup -Name $resourceGroupName -Location $DCLocation
    }
    catch
    {}
}

#Select either an existing logical server or create a new logical server for the database.
$SQLDBServerName = ""
$option = Read-Host "Do you want to use an existing SQL Database Server (y/n) :-"
if($option -eq "y")
{
    $logicalServers = Get-AzureRmSqlServer -ResourceGroupName $resourceGroupName
    #Choose a Valid SQLDatabase Server from the list
    $count = 1
    Foreach ($logicalServer in $logicalServers)
    {
        Write-Host $count.ToString() " -:" $logicalServer.ServerName
        $count +=1                                                 
    }
    Write-Host "Choose the Logical SQLDatabase Server where the databases has to be deployed -" -ForegroundColor Yellow
    $SrvChoice = Read-Host
    $Arrayindex = $SrvChoice-1
    $SQLDBServerName = $logicalServers[$Arrayindex].ServerName   
}
Else
{                                                                              
    Write-host "Enter the name for the SQL Server :-" -ForegroundColor Yellow
    $SQLDBServerName = Read-Host
    $SQLDBServerName = $SQLDBServerName.ToLower()
    $admin = Read-Host "Enter the Admin Account -:"
    $password =  Read-host "Enter the Admin Password -:" -assecurestring
    $Pscred = New-Object System.Management.Automation.PSCredential ($admin,$password)
    $DbServer = New-AzureRmSqlServer -ServerName $SQLDBServerName -SqlAdministratorCredentials $Pscred -Location $DCLocation -ResourceGroupName $resourceGroupName
}

#Add Firewall Rules to allow connections from the local machine
$FirewallRuleName = "Rule1"
$FirewallStartIP = "125.16.230.6"
$FirewallEndIp = "125.16.230.6"
$FirewallRule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $SQLDBServerName -FirewallRuleName $FirewallRuleName -StartIpAddress $FirewallStartIP -EndIpAddress $FirewallEndIp

#Add the SQL Database
$DatabaseName = "MyDatabase2"
$DatabaseEdition = "Basic"
$DatabasePerfomanceLevel = "Basic"
$SqlDatabase = New-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $SQLDBServerName -DatabaseName $DatabaseName -Edition $DatabaseEdition -RequestedServiceObjectiveName $DatabasePerfomanceLevel

SQLPackage.exe

SQLPackage is a command-line utility available as part of the SQL Server or Visual Studio installation. The executable can be located in the following folders:

SQL Installations- C:Program Files (x86)Microsoft SQL Server120 DACin
Visual Studio - C:Program Files (x86)Microsoft Visual Studio 12.0Common7IDEExtensionsMicrosoftSQLDBDAC120

The folders might vary depending on the version of SQL Server or Visual Studio installed on your server.

SQLPackage.exe supports the following actions (the complete documentation can be found at https://msdn.microsoft.com/library/hh550080.aspx):keez

1. Extract. Creates a database snapshot (*.dacpac) of a SQL Server or Azure SQL Database.

2. Export. Exports a database to a *.bacpac file.

3. Import. Imports a *.bacpac file to a database.

4. Publish. Publishes the contents of a .dacpac file to the target database.

5. DeployReport. Creates an XML report of the changes that would be made by a publish action.

6. DriftReport. Creates an XML report of the changes that have been made to a registered database since it was last registered.

7. Script. Creates a script of all the changes to be updated on the target database.

To get the list of potential issues that might block your efforts of migrating a database to Azure SQL Database, use the following command:

sqlpackage.exe /Action:Export /ssn:"SQLServerName" /sdn:"DatabaseName" /tf:"Target Bacpac File" > "OutputFile" 2>&1

Where the parameter 2>&1 indicates that we want to log the errors and output in the same file.

Listing 8-2 shows sample output from the execution.

Listing 8-2. Output of SQLPackage.exe

Connecting to database 'SQLNexus' on server 'SQLServerName'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Validating schema model
Validating schema model for data package
Validating schema
Exporting data from database
Exporting data
Processing Export.
Processing Table '[dbo].[tbl_PERF_STATS_SCRIPT_RUNTIMES]'.
Processing Table '[dbo].[tblNexusInfo]'.
Processing Table '[dbo].[tbl_BLOCKING_CHAINS]'.
Processing Table '[dbo].[tblDiagScan]'.
..........

Processing Table '[dbo].[tblDiagScanLookup]'.
Processing Table '[dbo].[CounterDetails]'.
Processing Table '[dbo].[tbl_RUNTIMES]'.
Processing Table '[dbo].[tbl_SYSINFO]'.
Processing Table '[dbo].[tbl_Reports]'.
Successfully exported database and saved it to file 'C: Target.bacpac'.

In this case, there were no issues detected with the database.

SQL Server Management Studio

Using SQL Server Management Studio , users can create a bacpac file export of their database, as illustrated in Figures 8-13 through 8-15. The Export Data Tier Application option from SSMS can be utilized for creating a bacpac file for the existing on-premises database, which then can be imported as an Azure SQL Database. A bacpac file encapsulates both the data and schema of the database .

Figure 8-13. Launching the Export Data Tier Application wizard in SSMS

SSMS Export Data Tier Application allows users to choose which objects (tables, procedures, triggers, etc.) to include in the bacpac file .

Figure 8-14. Configure the export settings for the Export Data Tier Application wizard

Figure 8-15. Choosing the objects to be included in the bacpac file

If SQL Server identifies any migration blockers during the creating of the bacpac file, those blockers are logged in the Export report at the end of the Export Data Tier operation (see Figure 8-16).

Figure 8-16. Export Data Tier Application execution

If no errors are identified in the Export Data Tier Application report, the database can be migrated to an Azure SQL Database without any issues. If errors are identified, they need to resolved before the database can be migrated to an Azure SQL database.

Performing the Database Migration

The actual database migration can be done using any of the following options.

• Export/import a bacpac file.

• Use the SSMS Deploy to Azure SQL Database wizard.

• Use Transaction Replication.

Export/Import a Bacpac File

An existing database can be exported to a bacpac file using SSMS or SQLPackage.exe, as described earlier. This exported file can then be imported to an Azure SQL database using either SQLPackage.exe or the Import Data-Tier Application wizard in SSMS, as illustrated in Figure 8-17.

Figure 8-17. Importing a bacpac file using SSMS

The wizard allows users to choose an existing bacpac file to import into the Azure SQL Database as well as to specify a new name, service tier, and performance level for the database (see Figure 8-18).

Figure 8-18. Configuring the Azure SQL Database options during a bacpac import operation

When you click finish, the wizard will import the contents of the bacpac file to the Azure SQL Database.

The SQLPackage.exe import action can also be used to import a bacpac file to Azure SQL Database. The command syntax would look like this:

sqlpackage.exe /Action:Import /SourceFile:<bacpac file to be imported> /ssn:<AzureSQLDatabaseLogicalServer> /tdn:<DatabaseName> /tu:"AdminUserName" /tp:<AdminUserPassword> > "OutputFile" 2>&1                                                

The SSMS Deploy to Azure SQL Database Wizard

The SSMS Deploy to Azure SQL Database wizard can be used to directly migrate an existing SQL Server database to Azure SQL Database, without having to explicitly create a bacpac file and then import it. Although behind the scenes, this wizard creates temporary bacpac files which are then imported to the Azure SQL database. As with the Import Data Tier wizard mentioned earlier, users can choose which server and what service tier (and performance level) the database will be created in.

You can access this wizard by right-clicking on the database and then choosing Tasks, Deploy to Microsoft Azure SQL Database, as illustrated in Figure 8-19.

Figure 8-19. Launching the Deploy Database to Microsoft Azure SQL Database wizard

Figure 8-20 illustrates the different configuration options available when the database is being deployed. The wizard allows users to connect to the logical server, where the new database would be hosted, and to specify the service tier and the performance level.

Figure 8-20. Configuring the database options during a deploy Azure SQL database operation

Note

The migration will fail if the size of the database is larger than the maximum allowed size of the service tier.

Once the wizard completes execution, the new Azure SQL Database with the specified service tier and performance level is created on the specified logical server (see Figure 8-21).

Figure 8-21. Deploy database to Microsoft Azure SQL Database wizard summary page

Using Transaction Replication

Transaction Replication is a data (or schema) replication feature of Microsoft SQL Server that replicates transactions on the source database (publisher) to the multiple target databases (subscribers), through an intermediate distribution database. The Log Reader agent reads the transaction log file from the publisher database and sends the information to the distribution database. Once the information is available with the distribution database, it is send to the subscribers using the distribution agents.

SQL Server Transaction Replication allows users to configure Azure SQL databases as a subscriber. During the initial setup of transactional replication, the subscriber database is synced using a snapshot of the publisher database. After the initial sync, any changes made to the primary database (on-premises) are captured by logRead.exe and stored in the distribution database. From the distribution database, these changes are sent to the Azure SQL database subscriber. This flow is illustrated in Figure 8-22.

Figure 8-22. Execution details of Transaction Replication to Azure SQL Database

Azure SQL Database cannot be configured as a distributor or as a publisher for a transactional replication setup. This feature can be used as an effective option for database migration, especially in scenarios where minimal or no downtime is required.