Create and manage an elastic pool with C#

This topic shows you how to create and manage scalable elastic pools with C#. You can also create and manage an Azure elastic pool with the Azure portal, PowerShell, or the REST API. You can also create and move databases into and out of elastic pools using Transact-SQL.

Note

Many new features of SQL Database are only supported when you are using the Azure Resource Manager deployment model, so you should always use the latest Azure SQL Database Management Library for .NET (docs | NuGet Package). The older classic deployment model-based libraries are supported for backward compatibility only, so we recommend you use the newer Resource Manager based libraries.

This topic describes how to use C# to create and manage an Azure SQL elastic pool with the Azure SQL Database Library for .NET. To create a single SQL database, see Use C# to create a SQL database with the SQL Database Library for .NET.

The Azure SQL Database Library for .NET provides an Azure Resource Manager-based API that wraps the Resource Manager-based SQL Database REST API.

Note

Many new features of SQL Database are only supported when you are using the Azure Resource Manager deployment model, so you should always use the latest Azure SQL Database Management Library for .NET (docs | NuGet Package). The older classic deployment model libraries are supported for backward compatibility only, so we recommend you use the newer Resource Manager based libraries.

Prerequisites

To complete the steps in this article, you need the following:

  • An Azure subscription. If you need an Azure subscription simply click FREE ACCOUNT at the top of this page, and then come back to finish this article.
  • Visual Studio. For a free copy of Visual Studio, see the Visual Studio Downloads page.

Create a console app and install the required libraries

  1. Start Visual Studio.
  2. Click File > New > Project.
  3. Create a C# Console Application and name it: SqlElasticPoolConsoleApp

Create a SQL database

To create a SQL database with C#, load the required management libraries (using the package manager console):

  1. Click Tools > NuGet Package Manager > Package Manager Console.
  2. Type Install-Package Microsoft.Azure.Management.Sql -Pre to install the Microsoft Azure SQL Management Library.
  3. Type Install-Package Microsoft.Azure.Management.ResourceManager -Pre to install the Microsoft Azure Resource Manager Library.
  4. Type Install-Package Microsoft.Azure.Common.Authentication -Pre to install the Microsoft Azure Common Authentication Library.
Note

The examples in this article use a synchronous form of each API request and block until completion of the REST call on the underlying service. There are async methods available.

Create a SQL elastic pool - C# example

The following sample creates a resource group, server, firewall rule, elastic pool, and then creates a SQL database in the pool. See, Create a service principal to access resources to get the _subscriptionId, _tenantId, _applicationId, and _applicationSecret variables.

Replace the contents of Program.cs with the following, and update the {variables} with your app values (do not include the {}).

using Microsoft.Azure;
using Microsoft.Azure.Management.ResourceManager;
using Microsoft.Azure.Management.ResourceManager.Models;
using Microsoft.Azure.Management.Sql;
using Microsoft.Azure.Management.Sql.Models;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;

namespace SqlElasticPoolConsoleApp
{
    class Program
        {

        // For details about these four (4) values, see
        // https://azure.microsoft.com/documentation/articles/resource-group-authenticate-service-principal/
        static string _subscriptionId = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}";
        static string _tenantId = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}";
        static string _applicationId = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}";
        static string _applicationSecret = "{your-password}";

        // Create management clients for the Azure resources your app needs to work with.
        // This app works with Resource Groups, and Azure SQL Database.
        static ResourceManagementClient _resourceMgmtClient;
        static SqlManagementClient _sqlMgmtClient;

        // Authentication token
        static AuthenticationResult _token;

        // Azure resource variables
        static string _resourceGroupName = "{resource-group-name}";
        static string _resourceGrouplocation = "{Azure-region}";

        static string _serverlocation = _resourceGrouplocation;
        static string _serverName = "{server-name}";
        static string _serverAdmin = "{server-admin-login}";
        static string _serverAdminPassword = "{server-admin-password}";

        static string _firewallRuleName = "{firewall-rule-name}";
        static string _startIpAddress = "{0.0.0.0}";
        static string _endIpAddress = "{255.255.255.255}";

        static string _poolName = "{pool-name}";
        static string _poolEdition = "{Standard}";
        static int _poolDtus = {100};
        static int _databaseMinDtus = {10};
        static int _databaseMaxDtus = {100};

        static string _databaseName = "{elasticdbfromcsarticle}";



        static void Main(string[] args)
        {
            // Authenticate:
            _token = GetToken(_tenantId, _applicationId, _applicationSecret);
            Console.WriteLine("Token acquired. Expires on:" + _token.ExpiresOn);

            // Instantiate management clients:
            _resourceMgmtClient = new ResourceManagementClient(new Microsoft.Rest.TokenCredentials(_token.AccessToken));
            _sqlMgmtClient = new SqlManagementClient(new TokenCloudCredentials(_subscriptionId, _token.AccessToken));


            Console.WriteLine("Resource group...");
            ResourceGroup rg = CreateOrUpdateResourceGroup(_resourceMgmtClient, _subscriptionId, _resourceGroupName, _resourceGrouplocation);
            Console.WriteLine("Resource group: " + rg.Id);


            Console.WriteLine("Server...");
            ServerGetResponse sgr = CreateOrUpdateServer(_sqlMgmtClient, _resourceGroupName, _serverlocation, _serverName, _serverAdmin, _serverAdminPassword);
            Console.WriteLine("Server: " + sgr.Server.Id);

            Console.WriteLine("Server firewall...");
            FirewallRuleGetResponse fwr = CreateOrUpdateFirewallRule(_sqlMgmtClient, _resourceGroupName, _serverName, _firewallRuleName, _startIpAddress, _endIpAddress);
            Console.WriteLine("Server firewall: " + fwr.FirewallRule.Id);

            Console.WriteLine("Elastic pool...");
            ElasticPoolCreateOrUpdateResponse epr = CreateOrUpdateElasticDatabasePool(_sqlMgmtClient, _resourceGroupName, _serverName, _poolName, _poolEdition, _poolDtus, _databaseMinDtus, _databaseMaxDtus);
            Console.WriteLine("Elastic pool: " + epr.ElasticPool.Id);

            Console.WriteLine("Database...");
            DatabaseCreateOrUpdateResponse dbr = CreateOrUpdateDatabase(_sqlMgmtClient, _resourceGroupName, _serverName, _databaseName, _poolName);
            Console.WriteLine("Database: " + dbr.Database.Id);


            Console.WriteLine("Press any key to continue...");
            Console.ReadKey();
        }

        static ResourceGroup CreateOrUpdateResourceGroup(ResourceManagementClient resourceMgmtClient, string subscriptionId, string resourceGroupName, string resourceGroupLocation)
        {
            ResourceGroup resourceGroupParameters = new ResourceGroup()
            {
                Location = resourceGroupLocation,
            };
            resourceMgmtClient.SubscriptionId = subscriptionId;
            ResourceGroup resourceGroupResult = resourceMgmtClient.ResourceGroups.CreateOrUpdate(resourceGroupName, resourceGroupParameters);
            return resourceGroupResult;
        }

        static ServerGetResponse CreateOrUpdateServer(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverLocation, string serverName, string serverAdmin, string serverAdminPassword)
        {
            ServerCreateOrUpdateParameters serverParameters = new ServerCreateOrUpdateParameters()
            {
                Location = serverLocation,
                Properties = new ServerCreateOrUpdateProperties()
                {
                    AdministratorLogin = serverAdmin,
                    AdministratorLoginPassword = serverAdminPassword,
                    Version = "12.0"
                }
            };
            ServerGetResponse serverResult = sqlMgmtClient.Servers.CreateOrUpdate(resourceGroupName, serverName, serverParameters);
            return serverResult;
        }


        static FirewallRuleGetResponse CreateOrUpdateFirewallRule(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverName, string firewallRuleName, string startIpAddress, string endIpAddress)
        {
            FirewallRuleCreateOrUpdateParameters firewallParameters = new FirewallRuleCreateOrUpdateParameters()
            {
                Properties = new FirewallRuleCreateOrUpdateProperties()
                {
                    StartIpAddress = startIpAddress,
                    EndIpAddress = endIpAddress
                }
            };
            FirewallRuleGetResponse firewallResult = sqlMgmtClient.FirewallRules.CreateOrUpdate(resourceGroupName, serverName, firewallRuleName, firewallParameters);
            return firewallResult;
        }



        static ElasticPoolCreateOrUpdateResponse CreateOrUpdateElasticDatabasePool(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverName, string poolName, string poolEdition, int poolDtus, int databaseMinDtus, int databaseMaxDtus)
        {
            // Retrieve the server that will host this elastic pool
            Server currentServer = sqlMgmtClient.Servers.Get(resourceGroupName, serverName).Server;

            // Create elastic pool: configure create or update parameters and properties explicitly
            ElasticPoolCreateOrUpdateParameters newPoolParameters = new ElasticPoolCreateOrUpdateParameters()
            {
                Location = currentServer.Location,
                Properties = new ElasticPoolCreateOrUpdateProperties()
                {
                    Edition = poolEdition,
                    Dtu = poolDtus,
                    DatabaseDtuMin = databaseMinDtus,
                    DatabaseDtuMax = databaseMaxDtus
                }
            };

            // Create the pool
            var newPoolResponse = sqlMgmtClient.ElasticPools.CreateOrUpdate(resourceGroupName, serverName, poolName, newPoolParameters);
            return newPoolResponse;
        }




        static DatabaseCreateOrUpdateResponse CreateOrUpdateDatabase(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverName, string databaseName, string poolName)
        {
            // Retrieve the server that will host this database
            Server currentServer = sqlMgmtClient.Servers.Get(resourceGroupName, serverName).Server;

            // Create a database: configure create or update parameters and properties explicitly
            DatabaseCreateOrUpdateParameters newDatabaseParameters = new DatabaseCreateOrUpdateParameters()
            {
                Location = currentServer.Location,
                Properties = new DatabaseCreateOrUpdateProperties()
                {
                    CreateMode = DatabaseCreateMode.Default,
                    ElasticPoolName = poolName
                }
            };
            DatabaseCreateOrUpdateResponse dbResponse = sqlMgmtClient.Databases.CreateOrUpdate(resourceGroupName, serverName, databaseName, newDatabaseParameters);
            return dbResponse;
        }



        private static AuthenticationResult GetToken(string tenantId, string applicationId, string applicationSecret)
        {
            AuthenticationContext authContext = new AuthenticationContext("https://login.windows.net/" + tenantId);
            _token = authContext.AcquireToken("https://management.core.windows.net/", new ClientCredential(applicationId, applicationSecret));
            return _token;
        }
    }
}

Create a service principal to access resources

The following PowerShell script creates the Active Directory (AD) application and the service principal that we need to authenticate our C# app. The script outputs values we need for the preceding C# sample. For detailed information, see Use Azure PowerShell to create a service principal to access resources.

# Sign in to Azure.
Add-AzureRmAccount

# If you have multiple subscriptions, uncomment and set to the subscription you want to work with.
#$subscriptionId = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
#Set-AzureRmContext -SubscriptionId $subscriptionId

# Provide these values for your new AAD app.
# $appName is the display name for your app, must be unique in your directory.
# $uri does not need to be a real uri.
# $secret is a password you create.

$appName = "{app-name}"
$uri = "http://{app-name}"
$secret = "{app-password}"

# Create a AAD app
$azureAdApplication = New-AzureRmADApplication -DisplayName $appName -HomePage $Uri -IdentifierUris $Uri -Password $secret

# Create a Service Principal for the app
$svcprincipal = New-AzureRmADServicePrincipal -ApplicationId $azureAdApplication.ApplicationId

# To avoid a PrincipalNotFound error, I pause here for 15 seconds.
Start-Sleep -s 15

# If you still get a PrincipalNotFound error, then rerun the following until successful. 
$roleassignment = New-AzureRmRoleAssignment -RoleDefinitionName Contributor -ServicePrincipalName $azureAdApplication.ApplicationId.Guid


# Output the values we need for our C# application to successfully authenticate

Write-Output "Copy these values into the C# sample app"

Write-Output "_subscriptionId:" (Get-AzureRmContext).Subscription.SubscriptionId
Write-Output "_tenantId:" (Get-AzureRmContext).Tenant.TenantId
Write-Output "_applicationId:" $azureAdApplication.ApplicationId.Guid
Write-Output "_applicationSecret:" $secret

To complete the steps in this article, you need the following items:

Move a database into an elastic pool

You can move a stand-alone database in or out of an elastic pool.

// Retrieve current database properties.

currentDatabase = sqlClient.Databases.Get("resourcegroup-name", "server-name", "Database1").Database;

// Configure create or update parameters with existing property values, override those to be changed.
DatabaseCreateOrUpdateParameters updatePooledDbParameters = new DatabaseCreateOrUpdateParameters()
{
    Location = currentDatabase.Location,
    Properties = new DatabaseCreateOrUpdateProperties()
    {
        Edition = "Standard",
        RequestedServiceObjectiveName = "ElasticPool",
        ElasticPoolName = "ElasticPool1",
        MaxSizeBytes = currentDatabase.Properties.MaxSizeBytes,
        Collation = currentDatabase.Properties.Collation,
    }
};

// Update the database.
var dbUpdateResponse = sqlClient.Databases.CreateOrUpdate("resourcegroup-name", "server-name", "Database1", updatePooledDbParameters);

List databases in an elastic pool

To retrieve all databases in an elastic pool, call the ListDatabases method.

//List databases in the elastic pool
DatabaseListResponse dbListInPool = sqlClient.ElasticPools.ListDatabases("resourcegroup-name", "server-name", "ElasticPool1");
Console.WriteLine("Databases in Elastic Pool {0}", "server-name.ElasticPool1");
foreach (Database db in dbListInPool)
{
    Console.WriteLine("  Database {0}", db.Name);
}

Change performance settings of an elastic pool

Retrieve existing the pool properties. Modify the values and execute the CreateOrUpdate method.

var currentPool = sqlClient.ElasticPools.Get("resourcegroup-name", "server-name", "ElasticPool1").ElasticPool;

// Configure create or update parameters with existing property values, override those to be changed.
ElasticPoolCreateOrUpdateParameters updatePoolParameters = new ElasticPoolCreateOrUpdateParameters()
{
    Location = currentPool.Location,
    Properties = new ElasticPoolCreateOrUpdateProperties()
    {
        Edition = currentPool.Properties.Edition,
        DatabaseDtuMax = 50, /* Setting DatabaseDtuMax to 50 limits the eDTUs that any one database can consume */
        DatabaseDtuMin = 10, /* Setting DatabaseDtuMin above 0 limits the number of databases that can be stored in the pool */
        Dtu = (int)currentPool.Properties.Dtu,
        StorageMB = currentPool.Properties.StorageMB,  /* For a Standard pool there is 1 GB of storage per eDTU. */
    }
};

newPoolResponse = sqlClient.ElasticPools.CreateOrUpdate("resourcegroup-name", "server-name", "ElasticPool1", newPoolParameters);

Latency of elastic pool operations

  • Changing the min eDTUs per database or max eDTUs per database typically completes in five minutes or less.
  • Time to change the pool size (eDTUs) depends on the combined size of all databases in the pool. Changes average 90 minutes or less per 100 GB. For example, if the total space of all databases in the pool is 200 GB, then the expected latency for changing the pool eDTU per pool is 3 hours or less.

Additional Resources