Tutorial: Offer highly available SQL databases

As an Azure Stack Operator, you can configure server VMs to host SQL Server databases. After a SQL hosting server is successfully created, and managed by Azure Stack, users who have subscribed to SQL services can easily create SQL databases.

This tutorial shows how to use an Azure Stack quickstart template to create a SQL Server AlwaysOn availability group, add it as an Azure Stack SQL Hosting Server, and then create a highly available SQL database.

What you will learn:

  • Create a SQL Server AlwaysOn availability group from a template
  • Create an Azure Stack SQL Hosting Server
  • Create a highly available SQL database

In this tutorial, a two VM SQL Server AlwaysOn availability group will be created and configured using available Azure Stack marketplace items.

Before starting the steps in this tutorial, ensure that the SQL Server resource provider has been successfully installed and the following items available in the Azure Stack marketplace:

Important

All of the following are required for the Azure Stack quickstart template to be used.

  • Windows Server 2016 Datacenter marketplace image.
  • SQL Server 2016 SP1 or SP2 (Enterprise, Standard, or Developer) on Windows Server 2016 server image. This tutorial uses the SQL Server 2016 SP2 Enterprise on Windows Server 2016 marketplace image.
  • SQL Server IaaS Extension version 1.2.30 or higher. The SQL IaaS Extension installs necessary components that are required by the Marketplace SQL Server items for all Windows versions. It enables SQL-specific settings to be configured on SQL virtual machines. If the extension is not installed in the local Marketplace, provisioning of SQL will fail.
  • Custom script extension for Windows version 1.9.1 or higher. Custom Script Extension is a tool that can be used to automatically launch post-deployment VM customization tasks.
  • PowerShell Desired State Configuration (DSC) version 2.76.0.0 or higher. DSC is a management platform in Windows PowerShell that enables deploying and managing configuration data for software services and managing the environment in which these services run.

To learn more about adding items to the Azure Stack marketplace, see the Azure Stack Marketplace overview.

Create a SQL Server AlwaysOn availability group

Use the steps in this section to deploy the SQL Server AlwaysOn availability group by using the sql-2016-alwayson Azure Stack quickstart template. This template deploys two SQL Server Enterprise, Standard or Developer instances in an Always On Availability Group. It creates the following resources:

  • A network security group
  • A virtual network
  • Four storage accounts (one for Active Directory (AD), one for SQL, one for file share witness and one for VM diagnostics)
  • Four public IP addresses (one for AD, two for each SQL VM, and one for public load balancer bound to SQL AlwaysOn listener)
  • One external load balancer for SQL VMs with Public IP bound to the SQL AlwaysOn listener
  • One VM (Windows Server 2016) configured as Domain Controller for a new forest with a single domain
  • Two VMs (Windows Server 2016) configured with SQL Server 2016 SP1 or SP2 Enterprise, Standard, or Developer Edition and clustered. These must be marketplace images.
  • One VM (Windows Server 2016) configured as the file share witness for the cluster
  • One availability set containing the SQL and file share witness VMs
  1. Sign in to the administration portal:

    • For an integrated system deployment, the portal address will vary based on your solution's region and external domain name. It will be in the format of https://adminportal.<region>.<FQDN>.
    • If you’re using the Azure Stack Development Kit (ASDK), the user portal address is https://adminportal.local.azurestack.external.
  2. Select + Create a resource > Custom, and then Template deployment.

    Custom template deployment

  3. On the Custom deployment blade, select Edit template > Quickstart template and then use the drop-down list of available custom templates to select the sql-2016-alwayson template, click OK, and then Save.

    Select quickstart template

  4. On the Custom deployment blade, select Edit parameters and review the default values. Modify the values as necessary to provide all required parameter information and then click OK.

    At a minimum:

    • Provide complex passwords for the ADMINPASSWORD, SQLSERVERSERVICEACCOUNTPASSWORD, and SQLAUTHPASSWORD parameters.
    • Enter the DNS Suffix for reverse lookup in all lowercase letters for the DNSSUFFIX parameter (azurestack.external for ASDK installations).

      Custom deployment parameters

  5. On the Custom deployment blade, choose the subscription to use and create a new resource group or select an existing resource group for the custom deployment.

    Next, select the resource group location (local for ASDK installations) and then click Create. The custom deployment settings will be validated and then the deployment will start.

    Custom deployment parameters

  6. In the administration portal, select Resource groups and then the name of the resource group you created for the custom deployment (resource-group for this example). View the status of the deployment to ensure all deployments have completed successfully.

    Next, review the resource group items and select the SQLPIPsql<resource group name> Public IP address item. Record the public IP address and full FQDN of the load balancer public IP. You will need to provide this to an Azure Stack Operator so they can create a SQL hosting server leveraging this SQL AlwaysOn availability group.

    Note

    The template deployment will take several hours to complete.

    Custom deployment parameters

Enable automatic seeding

After the template has successfully deployed and configured the SQL AlwaysON availability group, you must enable automatic seeding on each instance of SQL Server in the availability group.

When you create an availability group with automatic seeding, SQL Server automatically creates the secondary replicas for every database in the group without any other manual intervention necessary to ensure high availability of AlwaysOn databases.

Use these SQL commands to configure automatic seeding for the AlwaysOn availability group.

On the primary SQL instance (replace with the primary instance SQL Server name):

ALTER AVAILABILITY GROUP [<availability_group_name>]
    MODIFY REPLICA ON '<InstanceName>'
    WITH (SEEDING_MODE = AUTOMATIC)
GO

Primary SQL instance script

On secondary SQL instances (replace <availability_group_name> with the AlwaysOn availability group name):

ALTER AVAILABILITY GROUP [<availability_group_name>] GRANT CREATE ANY DATABASE
GO

Secondary SQL instance script

Configure contained database authentication

Before adding a contained database to an availability group, ensure that the contained database authentication server option is set to 1 on every server instance that hosts an availability replica for the availability group. For more information, see contained database authentication.

Use these commands to set the contained database authentication server option for each SQL Server instance in the availability group:

EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO

Set contained database authentication

Create an Azure Stack SQL Hosting Server

After the SQL Server AlwayOn availability group has been created, and properly configured, an Azure Stack Operator must create an Azure Stack SQL Hosting Server to make the additional capacity available for users to create databases.

Be sure to use the public IP or full FQDN for the public IP of the SQL load balancer recorded previously when the SQL AlwaysOn availability group's resource group was created (SQLPIPsql<resource group name>). In addition, you need to know the SQL Server authentication credentials used to access the SQL instances in the AlwaysOn availability group.

Note

This step must be run from the Azure Stack administration portal by an Azure Stack Operator.

With the SQL AlwaysOn availability group's load balancer listener Public IP and SQL authentication login information, an Azure Stack Operator can now create a SQL Hosting Server using the SQL AlwaysOn availablity group.

Also ensure that you have created plans and offers to make SQL AlwaysOn database creation available for users. The operator will need to add the Microsoft.SqlAdapter service to a plan and create a new quota specifically for highly available databases. For more information about creating plans, see Plan, offer, quota, and subscription overview.

Tip

The Microsoft.SqlAdapter service will not be available to add to plans until the SQL Server resource provider has been deployed.

Create a highly available SQL database

After the SQL AlwaysOn availability group has been created, configured, and added as an Azure Stack SQL Hosting Server by an Azure Stack Operator, a tenant user with a subscription including SQL Server database capabilities can create SQL databases supporting AlwaysOn functionality by following the steps in this section.

Note

Run these steps from the Azure Stack user portal as a tenant user with a subscription providing SQL Server capabilities (Microsoft.SQLAdapter service).

  1. Sign in to the user portal:

    • For an integrated system deployment, the portal address will vary based on your solution's region and external domain name. It will be in the format of https://portal.<region>.<FQDN>.
    • If you’re using the Azure Stack Development Kit (ASDK), the user portal address is https://portal.local.azurestack.external.
  2. Select + Create a resource > Data + Storage, and then SQL Database.

    Provide the required database property information including name, collation, maximum size, and the subscription, resource group, and location to use for the deployment.

    Create SQL database

  3. Select SKU and then choose the appropriate SQL Hosting Server SKU to use. In this example, the Azure Stack Operator has created the Enterprise-HA SKU to support high availability for SQL AlwaysOn availability groups.

    Select SKU

  4. Select Login > Create a new login and then provide the SQL authentication credentials to be used for the new database. When finished, click OK and then Create to begin the database deployment process.

    Create login

  5. When the SQL database deployment completes successfully, review the database properties to discover the connection string to use for connecting to the new highly available database.

    View connection string

Next steps

In this tutorial you learned how to:

  • Create a SQL Server AlwaysOn availability group from a template
  • Create an Azure Stack SQL Hosting Server
  • Create a highly available SQL database

Advance to the next tutorial to learn how to: