教程:实现地理分散的数据库Tutorial: Implement a geo-distributed database

配置故障转移到远程区域所需的 Azure SQL 数据库和应用程序,然后测试故障转移计划。Configure an Azure SQL database and application for failover to a remote region and test a failover plan. 你将学习如何执行以下操作:You learn how to:

  • 创建故障转移组Create a failover group
  • 运行 Java 应用程序以查询 Azure SQL 数据库Run a Java application to query an Azure SQL database
  • 测试故障转移Test failover

如果没有 Azure 订阅,可在开始前创建一个 1 元试用帐户If you don't have an Azure subscription, create a 1 rmb trial account before you begin.

先决条件Prerequisites

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

Important

PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. 若要了解这些 cmdlet,请参阅 AzureRM.SqlFor these cmdlets, see AzureRM.Sql. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。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:

Important

请务必设置防火墙规则,以便使用要在其上执行本教程中步骤的计算机的公共 IP 地址。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.

有关信息,请参阅创建数据库级防火墙规则;若要确定计算机的用于服务器级防火墙规则的 IP 地址,请参阅创建服务器级防火墙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

使用 Azure PowerShell 创建故障转移组,以便在现有的 Azure SQL Server 和另一区域的全新 Azure SQL Server 之间进行故障转移。Using Azure PowerShell, create failover groups between an existing Azure SQL server and a new Azure SQL server in another region. 然后,将示例数据库添加到故障转移组。Then add the sample database to the failover group.

Important

本示例需要 Azure PowerShell。This sample requires Azure PowerShell. 运行 Get-Module -ListAvailable Az 以查看是否已安装。Run Get-Module -ListAvailable Az to see if it's installed. 如果需要安装,请参阅安装 Azure PowerShell 模块If you need to install, see Install Azure PowerShell module.

运行 Connect-AzAccount -Environment AzureChinaCloud 来创建与 Azure 中国云的连接。Run Connect-AzAccount -Environment AzureChinaCloud to create a connection with Azure China Cloud.

若要创建故障转移组,请运行以下脚本:To create a failover group, run the following script:

 # Set variables for your server and database
 $adminlogin = "<your admin>"
 $password = "<your password>"
 $myresourcegroupname = "<your resource group name>"
 $mylocation = "<your resource group location>"
 $myservername = "<your existing server name>"
 $mydatabasename = "<your database name>"
 $mydrlocation = "<your disaster recovery location>"
 $mydrservername = "<your disaster recovery server name>"
 $myfailovergroupname = "<your globally unique failover group name>"

 # Create a backup server in the failover region
 New-AzSqlServer -ResourceGroupName $myresourcegroupname `
    -ServerName $mydrservername `
    -Location $mydrlocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
       -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

 # Create a failover group between the servers
 New-AzSqlDatabaseFailoverGroup `
    -ResourceGroupName $myresourcegroupname `
    -ServerName $myservername `
    -PartnerServerName $mydrservername  `
    -FailoverGroupName $myfailovergroupname `
    -FailoverPolicy Automatic `
    -GracePeriodWithDataLossHours 2

 # Add the database to the failover group
 Get-AzSqlDatabase `
    -ResourceGroupName $myresourcegroupname `
    -ServerName $myservername `
    -DatabaseName $mydatabasename | `
  Add-AzSqlDatabaseToFailoverGroup `
    -ResourceGroupName $myresourcegroupname `
    -ServerName $myservername `
    -FailoverGroupName $myfailovergroupname

异地复制设置也可在 Azure 门户中更改,方法是:选择数据库,然后选择“设置” > “异地复制”。Geo-replication settings can also be changed in the Azure portal, by selecting your database, then Settings > Geo-Replication.

异地复制设置

运行示例项目Run the sample project

  1. 在控制台中,使用以下命令创建一个 Maven 项目: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"
    
  2. 键入 Y,然后按 EnterType Y and press Enter.

  3. 将目录切换到新项目。Change directories to the new project.

    cd SqlDbSample
    
  4. 使用最常用的编辑器,打开项目文件夹中的 pom.xml 文件。Using your favorite editor, open the pom.xml file in your project folder.

  5. 通过添加下面的 dependency 节来添加 Microsoft JDBC Driver for SQL Server 依赖项。Add the Microsoft JDBC Driver for SQL Server dependency by adding the following dependency section. 此依赖项必须粘贴在更大的 dependencies 节中。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>
    
  6. properties 节添加到 dependencies 节之后,指定 Java 版本: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>
    
  7. build 节添加到 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>
    
  8. 保存并关闭 pom.xml 文件。Save and close the pom.xml file.

  9. 打开 ..\SqlDbSample\src\main\java\com\sqldbsamples 中的 App.java 文件,将内容替换为以下代码: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.chinacloudapi.cn:1433;database=%s;user=%s;password=%s;encrypt=true;" +
          "hostNameInCertificate=*.database.chinacloudapi.cn;loginTimeout=30;", +
          FAILOVER_GROUP_NAME, DB_NAME, USER, PASSWORD);
       private static final String READ_ONLY_URL = String.format("jdbc:" +
          "sqlserver://%s.secondary.database.chinacloudapi.cn:1433;database=%s;user=%s;password=%s;encrypt=true;" +
          "hostNameInCertificate=*.database.chinacloudapi.cn;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;
       }
    }
    
  10. 保存并关闭 App.java 文件。Save and close the App.java file.

  11. 在命令控制台中运行以下命令:In the command console, run the following command:

    mvn package
    
  12. 启动应用程序。如果不手动停止,应用程序将运行大约 1 小时,让你有时间运行故障转移测试。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.

也可使用以下命令,检查灾难恢复服务器在测试过程中的角色:You can also check the role of the disaster recovery server during the test with the following command:

(Get-AzSqlDatabaseFailoverGroup `
   -FailoverGroupName $myfailovergroupname `
   -ResourceGroupName $myresourcegroupname `
   -ServerName $mydrservername).ReplicationRole

若要测试某个故障转移,请执行以下操作:To test a failover:

  1. 启动对故障转移组的手动故障转移:Start a manual failover of the failover group:

    Switch-AzSqlDatabaseFailoverGroup `
       -ResourceGroupName $myresourcegroupname `
       -ServerName $mydrservername `
       -FailoverGroupName $myfailovergroupname
    
  2. 将故障转移组还原到主服务器:Revert failover group back to the primary server:

    Switch-AzSqlDatabaseFailoverGroup `
       -ResourceGroupName $myresourcegroupname `
       -ServerName $myservername `
       -FailoverGroupName $myfailovergroupname