SQL database Implement a geo-distributed database

Implement a geo-distributed database

Contributors
Carl Rabeler  Kris Crider
In this tutorial, you configure an Azure SQL database and application for failover to a remote region, and then test your failover plan. You learn how to:

Create database users and grant them permissions
Set up a database-level firewall rule
Create a geo-replication failover group
Create and compile a Java application to query an Azure SQL database
Perform a disaster recovery drill
If you don't have an Azure subscription, create a free account before you begin.

Prerequisites
To complete this tutorial, make sure the following prerequisites are completed:

Installed the latest Azure PowerShell.
Installed an Azure SQL database. This tutorial uses the AdventureWorksLT sample database with a name of mySampleDatabase from one of these quick starts:

Create DB - Portal
Create DB - CLI
Create DB - PowerShell
Have identified a method to execute SQL scripts against your database, you can use one of the following query tools:

The query editor in the Azure portal. For more information on using the query editor in the Azure portal, see Connect and query using Query Editor.
The newest version of SQL Server Management Studio, which is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database for Microsoft Windows.
The newest version of Visual Studio Code, which is a graphical code editor for Linux, macOS, and Windows that supports extensions, including the mssql extension for querying Microsoft SQL Server, Azure SQL Database, and SQL Data Warehouse. For more information on using this tool with Azure SQL Database, see Connect and query with VS Code.
Create database users and grant permissions
Connect to your database and create user accounts using one of the following query tools:

Get more information on IOS from OnlineITGuru azure online training 

The Query editor in the Azure portal
SQL Server Management Studio
Visual Studio Code
These user accounts replicate automatically to your secondary server (and be kept in sync). To use SQL Server Management Studio or Visual Studio Code, you may need to configure a firewall rule if you are connecting from a client at an IP address for which you have not yet configured a firewall. For detailed steps, see Create a server-level firewall rule.

In a query window, execute the following query to create two user accounts in your database. This script grants db_owner permissions to the app_admin account and grants SELECT and UPDATE permissions to the app_user account.

SQL

Copy
CREATE USER app_admin WITH PASSWORD = 'ChangeYourPassword1';
--Add SQL user to db_owner role
ALTER ROLE db_owner ADD MEMBER app_admin;
--Create additional SQL user
CREATE USER app_user WITH PASSWORD = 'ChangeYourPassword1';
--grant permission to SalesLT schema
GRANT SELECT, INSERT, DELETE, UPDATE ON SalesLT.Product TO app_user;
Create database-level firewall
Create a database-level firewall rule for your SQL database. This database-level firewall rule replicates automatically to the secondary server that you create in this tutorial. For simplicity (in this tutorial), use the public IP address of the computer on which you are performing the steps in this tutorial. To determine the IP address used for the server-level firewall rule for your current computer, see Create a server-level firewall.

In your open query window, replace the previous query with the following query, replacing the IP addresses with the appropriate IP addresses for your environment.

SQL

Copy
-- Create database-level firewall setting for your public IP address
EXECUTE sp_set_database_firewall_rule @name = N'myGeoReplicationFirewallRule',@start_ip_address = '0.0.0.0', @end_ip_address = '0.0.0.0';
Create an active geo-replication auto failover group
Using Azure PowerShell, create an active geo-replication auto failover group between your existing Azure SQL server and the new empty Azure SQL server in an Azure region, and then add your sample database to the failover group.

Important

These cmdlets require Azure PowerShell 4.0. This sample requires the Azure PowerShell module version 5.1.1 or later. Run Get-Module -ListAvailable AzureRM to find the version. If you need to install or upgrade, see Install Azure PowerShell module. Run Login-AzureRmAccount to create a connection with Azure.

Populate variables for your PowerShell scripts using the values for your existing server and sample database, and provide a globally unique value for failover group name.

Get more information on IOS from OnlineITGuru azure online Training Hyderabad 

PowerShell

Copy
$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"
$myresourcegroupname = "<your resource group name>"
$mylocation = "<your resource group location>"
$myservername = "<your existing server name>"
$mydatabasename = "mySampleDatabase"
$mydrlocation = "<your disaster recovery location>"
$mydrservername = "<your disaster recovery server name>"
$myfailovergroupname = "<your unique failover group name>"
Create an empty backup server in your failover region.

PowerShell

Copy
$mydrserver = New-AzureRmSqlServer -ResourceGroupName $myresourcegroupname `
   -ServerName $mydrservername `
   -Location $mydrlocation `
   -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$mydrserver 
Create a failover group between the two servers.

PowerShell

Copy
$myfailovergroup = New-AzureRMSqlDatabaseFailoverGroup `
   –ResourceGroupName $myresourcegroupname `
   -ServerName $myservername `
   -PartnerServerName $mydrservername  `
   –FailoverGroupName $myfailovergroupname `
   –FailoverPolicy Automatic `
   -GracePeriodWithDataLossHours 2
$myfailovergroup 
Add your database to the failover group.

PowerShell

Copy
$myfailovergroup = Get-AzureRmSqlDatabase `
   -ResourceGroupName $myresourcegroupname `
   -ServerName $myservername `
   -DatabaseName $mydatabasename | `
 Add-AzureRmSqlDatabaseToFailoverGroup `
   -ResourceGroupName $myresourcegroupname ` `
   -ServerName $myservername `
   -FailoverGroupName $myfailovergroupname
$myfailovergroup 
Install Java software
The steps in this section assume that you are familiar with developing using Java and are new to working with Azure SQL Database.

Mac OS
Open your terminal and navigate to a directory where you plan on creating your Java project. Install brew and Maven by entering the following commands:

bash

Copy
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew install maven
For detailed guidance on installing and configuring Java and Maven environment, go the Build an app using SQL Server, select Java, select MacOS, and then follow the detailed instructions for configuring Java and Maven in step 1.2 and 1.3.

Linux (Ubuntu)
Open your terminal and navigate to a directory where you plan on creating your Java project. Install Maven by entering the following commands:

bash

Copy
sudo apt-get install maven
For detailed guidance on installing and configuring Java and Maven environment, go the Build an app using SQL Server, select Java, select Ubuntu, and then follow the detailed instructions for configuring Java and Maven in step 1.2, 1.3, and 1.4.

Windows
Install Maven using the official installer. Use Maven to help manage dependencies, build, test, and run your Java project. For detailed guidance on installing and configuring Java and Maven environment, go the Build an app using SQL Server, select Java, select Windows, and then follow the detailed instructions for configuring Java and Maven in step 1.2 and 1.3.

Create SqlDbSample project
In the command console (such as Bash), create a Maven project. bash mvn archetype:generate "-DgroupId=com.sqldbsamples" "-DartifactId=SqlDbSample" "-DarchetypeArtifactId=maven-archetype-quickstart" "-Dversion=1.0.0"
Type Y and click Enter.
Change directories into your newly created project.

bash

Copy
cd SqlDbSamples
Using your favorite editor, open the pom.xml file in your project folder.

Add the Microsoft JDBC Driver for SQL Server dependency to your Maven project by opening your favorite text editor and copying and pasting the following lines into your pom.xml file. Do not overwrite the existing values prepopulated in the file. The JDBC dependency must be pasted within the larger “dependencies” section ( ).

XML

Copy
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
 <version>6.1.0.jre8</version>
</dependency>
Specify the version of Java to compile the project against by adding the following “properties” section into the pom.xml file after the "dependencies" section.

XML

Copy
<properties>
  <maven.compiler.source>1.8</maven.compiler.source>
  <maven.compiler.target>1.8</maven.compiler.target>
</properties>
Add the following "build" section into the pom.xml file after the "properties" section to support manifest files in jars.

XML

Copy
<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 (C:\apache-maven-3.5.0\SqlDbSample\src\main\java\com\sqldbsamples\App.java) and replace the contents with the following contents. Replace the failover group name with the name for your failover group. If you have changed the values for the database name, user, or password, change those values as well.

Java

Copy
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 = "myfailovergroupname";

   private static final String DB_NAME = "mySampleDatabase";
   private static final String USER = "app_user";
   private static final String PASSWORD = "ChangeYourPassword1";

   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 that we can use to find the product again later
   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 that was 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 that is 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.
Compile and run the SqlDbSample project
In the command console, execute to following command.

bash

Copy
mvn package
When finished, execute the following command to run the application (it runs for about 1 hour unless you stop it manually):

bash

Copy
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
Perform disaster recovery drill
Call manual failover of failover group.

PowerShell

Copy
Switch-AzureRMSqlDatabaseFailoverGroup `
-ResourceGroupName $myresourcegroupname  `
-ServerName $mydrservername `
-FailoverGroupName $myfailovergroupname
Observe the application results during failover. Some inserts fail while the DNS cache refreshes.

Find out which role your disaster recovery server is performing.

PowerShell

Copy
$mydrserver.ReplicationRole
Failback.

PowerShell

Copy
Switch-AzureRMSqlDatabaseFailoverGroup `
-ResourceGroupName $myresourcegroupname  `
-ServerName $myservername `
-FailoverGroupName $myfailovergroupname
Observe the application results during failback. Some inserts fail while the DNS cache refreshes.

Find out which role your disaster recovery server is performing.

PowerShell

Copy
$fileovergroup = Get-AzureRMSqlDatabaseFailoverGroup `
   -FailoverGroupName $myfailovergroupname `
   -ResourceGroupName $myresourcegroupname `
   -ServerName $mydrservername
$fileovergroup.ReplicationRole
Next steps
For more information, see Active geo-replication and failover groups.

Feedback
What type of feedback would you like to provide?

Give product feedback   Sign in to give documentation feedback
Our new feedback system is built on GitHub Issues. For more information on this change, please read our blog post. more informastion visit AZURE ONLINE COURSE 

There is currently no feedback for this document.