title	Implement a geo-distributed solution
description	Learn to configure your database in Azure SQL Database and client application for failover to a replicated database, and test failover.
services	sql-database
ms.service	sql-database
ms.subservice	high-availability
ms.custom	sqldbrb=1, devx-track-azurecli, devx-track-azurepowershell
ms.devlang
ms.topic	conceptual
author	anosov1960
ms.author	sashan
ms.reviewer	mathoma, sstein
ms.date	03/12/2019

Tutorial: Implement a geo-distributed database (Azure SQL Database)

[!INCLUDEappliesto-sqldb]

Configure a database in SQL Database and client application for failover to a remote region and test a failover plan. You learn how to:

[!div class="checklist"]

Create a failover group

Run a Java application to query a database in SQL Database

Test failover

If you don't have an Azure subscription, create a free account before you begin.

Prerequisites

[!INCLUDE updated-for-az]

Important

The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. For these cmdlets, see AzureRM.Sql. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

To complete the tutorial, make sure you've installed the following items:

Azure PowerShell
A single database in Azure SQL Database. To create one use,
[!NOTE] The tutorial uses the AdventureWorksLT sample database.
Java and Maven, see Build an app using SQL Server, highlight Java and select your environment, then follow the steps.

Important

Be sure to set up firewall rules to use the public IP address of the computer on which you're performing the steps in this tutorial. Database-level firewall rules will replicate automatically to the secondary server.

For information see Create a database-level firewall rule or to determine the IP address used for the server-level firewall rule for your computer see Create a server-level firewall.

Create a failover group

Using Azure PowerShell, create failover groups between an existing server and a new server in another region. Then add the sample database to the failover group.

PowerShell

Important

[!INCLUDE sample-powershell-install]

To create a failover group, run the following script:

$admin = "<adminName>"
$password = "<password>"
$resourceGroup = "<resourceGroupName>"
$location = "<resourceGroupLocation>"
$server = "<serverName>"
$database = "<databaseName>"
$drLocation = "<disasterRecoveryLocation>"
$drServer = "<disasterRecoveryServerName>"
$failoverGroup = "<globallyUniqueFailoverGroupName>"

# create a backup server in the failover region
New-AzSqlServer -ResourceGroupName $resourceGroup -ServerName $drServer `
    -Location $drLocation -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
    -ArgumentList $admin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

# create a failover group between the servers
New-AzSqlDatabaseFailoverGroup –ResourceGroupName $resourceGroup -ServerName $server `
    -PartnerServerName $drServer –FailoverGroupName $failoverGroup –FailoverPolicy Automatic -GracePeriodWithDataLossHours 2

# add the database to the failover group
Get-AzSqlDatabase -ResourceGroupName $resourceGroup -ServerName $server -DatabaseName $database | `
    Add-AzSqlDatabaseToFailoverGroup -ResourceGroupName $resourceGroup -ServerName $server -FailoverGroupName $failoverGroup

The Azure CLI

Important

Run az login to sign in to Azure.

$admin = "<adminName>"
$password = "<password>"
$resourceGroup = "<resourceGroupName>"
$location = "<resourceGroupLocation>"
$server = "<serverName>"
$database = "<databaseName>"
$drLocation = "<disasterRecoveryLocation>" # must be different then $location
$drServer = "<disasterRecoveryServerName>"
$failoverGroup = "<globallyUniqueFailoverGroupName>"

# create a backup server in the failover region
az sql server create --admin-password $password --admin-user $admin `
    --name $drServer --resource-group $resourceGroup --location $drLocation

# create a failover group between the servers
az sql failover-group create --name $failoverGroup --partner-server $drServer `
    --resource-group $resourceGroup --server $server --add-db $database `
    --failover-policy Automatic --grace-period 2

Geo-replication settings can also be changed in the Azure portal, by selecting your database, then Settings > Geo-Replication.

Run the sample project

In the console, create a Maven project with the following command:

mvn archetype:generate "-DgroupId=com.sqldbsamples" "-DartifactId=SqlDbSample" "-DarchetypeArtifactId=maven-archetype-quickstart" "-Dversion=1.0.0"

Type Y and press Enter.
Change directories to the new project.
```
cd SqlDbSample
```
Using your favorite editor, open the pom.xml file in your project folder.
Add the Microsoft JDBC Driver for SQL Server dependency by adding the following dependency section. The dependency must be pasted within the larger dependencies section.
```
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
 <version>6.1.0.jre8</version>
</dependency>
```

Specify the Java version by adding the properties section after the dependencies section:

<properties>
  <maven.compiler.source>1.8</maven.compiler.source>
  <maven.compiler.target>1.8</maven.compiler.target>
</properties>

Support manifest files by adding the build section after the properties section:

<build>
  <plugins>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-jar-plugin</artifactId>
      <version>3.0.0</version>
      <configuration>
        <archive>
          <manifest>
            <mainClass>com.sqldbsamples.App</mainClass>
          </manifest>
        </archive>
     </configuration>
    </plugin>
  </plugins>
</build>

Save and close the pom.xml file.

Open the App.java file located in ..\SqlDbSample\src\main\java\com\sqldbsamples and replace the contents with the following code:

package com.sqldbsamples;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.util.Date;
import java.util.concurrent.TimeUnit;

public class App {

   private static final String FAILOVER_GROUP_NAME = "<your failover group name>";  // add failover group name

   private static final String DB_NAME = "<your database>";  // add database name
   private static final String USER = "<your admin>";  // add database user
   private static final String PASSWORD = "<your password>";  // add database password

   private static final String READ_WRITE_URL = String.format("jdbc:" +
      "sqlserver://%s.database.windows.net:1433;database=%s;user=%s;password=%s;encrypt=true;" +
      "hostNameInCertificate=*.database.windows.net;loginTimeout=30;", +
      FAILOVER_GROUP_NAME, DB_NAME, USER, PASSWORD);
   private static final String READ_ONLY_URL = String.format("jdbc:" +
      "sqlserver://%s.secondary.database.windows.net:1433;database=%s;user=%s;password=%s;encrypt=true;" +
      "hostNameInCertificate=*.database.windows.net;loginTimeout=30;", +
      FAILOVER_GROUP_NAME, DB_NAME, USER, PASSWORD);

   public static void main(String[] args) {
      System.out.println("#######################################");
      System.out.println("## GEO DISTRIBUTED DATABASE TUTORIAL ##");
      System.out.println("#######################################");
      System.out.println("");

      int highWaterMark = getHighWaterMarkId();

      try {
         for(int i = 1; i < 1000; i++) {
             //  loop will run for about 1 hour
             System.out.print(i + ": insert on primary " +
                (insertData((highWaterMark + i))?"successful":"failed"));
             TimeUnit.SECONDS.sleep(1);
             System.out.print(", read from secondary " +
                (selectData((highWaterMark + i))?"successful":"failed") + "\n");
             TimeUnit.SECONDS.sleep(3);
         }
      } catch(Exception e) {
         e.printStackTrace();
   }
}

private static boolean insertData(int id) {
   // Insert data into the product table with a unique product name so we can find the product again
   String sql = "INSERT INTO SalesLT.Product " +
      "(Name, ProductNumber, Color, StandardCost, ListPrice, SellStartDate) VALUES (?,?,?,?,?,?);";

   try (Connection connection = DriverManager.getConnection(READ_WRITE_URL);
           PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setString(1, "BrandNewProduct" + id);
      pstmt.setInt(2, 200989 + id + 10000);
      pstmt.setString(3, "Blue");
      pstmt.setDouble(4, 75.00);
      pstmt.setDouble(5, 89.99);
      pstmt.setTimestamp(6, new Timestamp(new Date().getTime()));
      return (1 == pstmt.executeUpdate());
   } catch (Exception e) {
      return false;
   }
}

private static boolean selectData(int id) {
   // Query the data previously inserted into the primary database from the geo replicated database
   String sql = "SELECT Name, Color, ListPrice FROM SalesLT.Product WHERE Name = ?";

   try (Connection connection = DriverManager.getConnection(READ_ONLY_URL);
           PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setString(1, "BrandNewProduct" + id);
      try (ResultSet resultSet = pstmt.executeQuery()) {
         return resultSet.next();
      }
   } catch (Exception e) {
      return false;
   }
}

private static int getHighWaterMarkId() {
   // Query the high water mark id stored in the table to be able to make unique inserts
   String sql = "SELECT MAX(ProductId) FROM SalesLT.Product";
   int result = 1;
   try (Connection connection = DriverManager.getConnection(READ_WRITE_URL);
           Statement stmt = connection.createStatement();
           ResultSet resultSet = stmt.executeQuery(sql)) {
      if (resultSet.next()) {
          result =  resultSet.getInt(1);
         }
      } catch (Exception e) {
       e.printStackTrace();
      }
      return result;
   }
}

Save and close the App.java file.
In the command console, run the following command:
```
mvn package
```

Start the application that will run for about 1 hour until stopped manually, allowing you time to run the failover test.

mvn -q -e exec:java "-Dexec.mainClass=com.sqldbsamples.App"

#######################################
## GEO DISTRIBUTED DATABASE TUTORIAL ##
#######################################

1. insert on primary successful, read from secondary successful
2. insert on primary successful, read from secondary successful
3. insert on primary successful, read from secondary successful
...

Test failover

Run the following scripts to simulate a failover and observe the application results. Notice how some inserts and selects will fail during the database migration.

PowerShell

You can check the role of the disaster recovery server during the test with the following command:

(Get-AzSqlDatabaseFailoverGroup -FailoverGroupName $failoverGroup `
    -ResourceGroupName $resourceGroup -ServerName $drServer).ReplicationRole

To test a failover:

Start a manual failover of the failover group:

Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $resourceGroup `
 -ServerName $drServer -FailoverGroupName $failoverGroup

Revert failover group back to the primary server:

Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $resourceGroup `
 -ServerName $server -FailoverGroupName $failoverGroup

The Azure CLI

You can check the role of the disaster recovery server during the test with the following command:

az sql failover-group show --name $failoverGroup --resource-group $resourceGroup --server $drServer

To test a failover:

Start a manual failover of the failover group:

az sql failover-group set-primary --name $failoverGroup --resource-group $resourceGroup --server $drServer

Revert failover group back to the primary server:

az sql failover-group set-primary --name $failoverGroup --resource-group $resourceGroup --server $server

Next steps

In this tutorial, you configured a database in Azure SQL Database and an application for failover to a remote region and tested a failover plan. You learned how to:

[!div class="checklist"]

Create a geo-replication failover group

Run a Java application to query a database in SQL Database

Test failover

Advance to the next tutorial on how to add an instance of Azure SQL Managed Instance to a failover group:

[!div class="nextstepaction"] Add an instance of Azure SQL Managed Instance to a failover group

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Tutorial: Implement a geo-distributed database (Azure SQL Database)

Prerequisites

Create a failover group

PowerShell

The Azure CLI

Run the sample project

Test failover

PowerShell

The Azure CLI

Next steps

FilesExpand file tree

geo-distributed-application-configure-tutorial.md

Latest commit

History

geo-distributed-application-configure-tutorial.md

File metadata and controls

Tutorial: Implement a geo-distributed database (Azure SQL Database)

Prerequisites

Create a failover group

PowerShell

The Azure CLI

Run the sample project

Test failover

PowerShell

The Azure CLI

Next steps