Implementare un database con distribuzione geografica

In questa esercitazione vengono configurati un database SQL di Azure e l'applicazione per il failover in un'area remota e viene quindi testato il piano di failover. Si apprenderà come:

  • Creare utenti del database e concedere loro le autorizzazioni
  • Configurare una regola del firewall a livello di database
  • Creare un gruppo di failover con replica geografica
  • Creare e compilare un'applicazione Java per eseguire query su un database SQL di Azure
  • Eseguire un'esercitazione sul ripristino di emergenza

Se non si ha una sottoscrizione di Azure, creare un account gratuito prima di iniziare.

Prerequisiti

Per completare questa esercitazione, verificare che siano soddisfatti i prerequisiti seguenti:

Creare utenti del database e concedere autorizzazioni

Connettersi al database e creare account utente usando uno degli strumenti di query seguenti:

  • Editor di query nel portale di Azure
  • SQL Server Management Studio
  • Visual Studio Code

Questi account utente vengono replicati automaticamente nel server secondario e vengono mantenuti sincronizzati. Per usare SQL Server Management Studio o Visual Studio Code potrebbe essere necessario configurare una regola del firewall, in caso di connessione da un client con un indirizzo IP per il quale non si è ancora configurato un firewall. Per la procedura dettagliata, vedere Creare una regola del firewall a livello di server.

  • In una finestra di query eseguire questa query per creare due account utente nel database. Questo script concede autorizzazioni db_owner all'account app_admin e autorizzazioni SELECT e UPDATE all'account app_user.

    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;
    

Creare il firewall a livello di database

Creare una regola del firewall a livello di database per il database SQL. Questa regola del firewall a livello di database viene replicata automaticamente nel server secondario creato in questa esercitazione. Per semplicità, in questa esercitazione usare l'indirizzo IP pubblico del computer in cui si eseguono i passaggi dell'esercitazione. Per determinare l'indirizzo IP usato per la regola del firewall a livello di server per il computer corrente, vedere Creare una regola del firewall a livello di server.

  • Nella finestra di query aperta sostituire la query precedente con la query seguente, sostituendo gli indirizzi IP con gli indirizzi IP appropriati per l'ambiente in uso.

    -- 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';
    

Creare un gruppo di failover automatico con replica geografica attiva

Usando Azure PowerShell, creare un gruppo di failover automatico con replica geografica attiva tra il server SQL di Azure esistente e il nuovo server SQL di Azure vuoto in un'area di Azure e quindi aggiungere il database di esempio al gruppo di failover.

Importante

Per questi cmdlet è necessario Azure PowerShell 4.0. Questo esempio richiede il modulo Azure PowerShell 4.0 o versioni successive. Eseguire Get-Module -ListAvailable AzureRM per trovare la versione. Se è necessario eseguire l'installazione o l'aggiornamento, vedere come installare il modulo Azure PowerShell. Eseguire Login-AzureRmAccount per creare una connessione con Azure.

  1. Inserire le variabili per gli script di PowerShell usando i valori del server esistente e del database di esempio e specificare un valore univoco globale come nome del gruppo di failover.

    $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>"
    
  2. Creare un server di backup vuoto nell'area di failover.

    $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   
    
  3. Creare un gruppo di failover tra i due server.

    $myfailovergroup = New-AzureRMSqlDatabaseFailoverGroup `
       –ResourceGroupName $myresourcegroupname `
       -ServerName $myservername `
       -PartnerServerName $mydrservername  `
       –FailoverGroupName $myfailovergroupname `
       –FailoverPolicy Automatic `
       -GracePeriodWithDataLossHours 2
    $myfailovergroup   
    
  4. Aggiungere il database al gruppo di failover.

    $myfailovergroup = Get-AzureRmSqlDatabase `
       -ResourceGroupName $myresourcegroupname `
       -ServerName $myservername `
       -DatabaseName $mydatabasename | `
     Add-AzureRmSqlDatabaseToFailoverGroup `
       -ResourceGroupName $myresourcegroupname ` `
       -ServerName $myservername `
       -FailoverGroupName $myfailovergroupname
    $myfailovergroup   
    

Installare il software Java

Le procedure descritte in questa sezione presuppongono che si abbia familiarità con lo sviluppo con Java ma non con il database SQL di Azure.

Mac OS

Aprire il terminale in uso e passare alla directory in cui si prevede di creare il progetto Java. Installare brew e Maven immettendo i comandi seguenti:

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew install maven

Per indicazioni dettagliate sull'installazione e la configurazione dell'ambiente Java e Maven, andare alla pagina Build an app using SQL Server (Creare un'app con SQL Server), selezionare Java e quindi MacOS e infine seguire le istruzioni dettagliate per la configurazione di Java e Maven riportate nei passaggi 1.2 e 1.3.

Linux (Ubuntu)

Aprire il terminale in uso e passare alla directory in cui si prevede di creare il progetto Java. Installare Maven immettendo i comandi seguenti:

sudo apt-get install maven

Per indicazioni dettagliate sull'installazione e la configurazione dell'ambiente Java e Maven, andare alla pagina Build an app using SQL Server (Creare un'app con SQL Server), selezionare Java e quindi Ubuntu e infine seguire le istruzioni dettagliate per la configurazione di Java e Maven riportate nei passaggi 1.2, 1.3 e 1.4.

Windows

Installare Maven tramite il programma di installazione ufficiale. Usare Maven per gestire le dipendenze e compilare, testare ed eseguire il progetto Java. Per indicazioni dettagliate sull'installazione e la configurazione dell'ambiente Java e Maven, andare alla pagina Build an app using SQL Server (Creare un'app con SQL Server), selezionare Java e quindi Windows e infine seguire le istruzioni dettagliate per la configurazione di Java e Maven riportate nei passaggi 1.2 e 1.3.

Creare il progetto SqlDbSample

  1. Nella console dei comandi (ad esempio, Bash) creare un progetto Maven. bash mvn archetype:generate "-DgroupId=com.sqldbsamples" "-DartifactId=SqlDbSample" "-DarchetypeArtifactId=maven-archetype-quickstart" "-Dversion=1.0.0"
  2. Digitare Y e premere INVIO.
  3. Passare alla directory del progetto appena creato.

    cd SqlDbSamples
    
  4. Usando l'editor preferito, aprire il file pom.xml file nella cartella del progetto.

  5. Aggiungere la dipendenza di Microsoft JDBC Driver per SQL Server al progetto Maven aprendo l'editor di testo preferito e copiando e incollando le righe seguenti nel file pom.xml. Non sovrascrivere i valori esistenti già inseriti nel file. La dipendenza JDBC deve essere incollata all'interno della più estesa sezione "dependencies".

    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
     <version>6.1.0.jre8</version>
    </dependency>
    
  6. Specificare la versione di Java in base alla quale compilare il progetto aggiungendo la sezione "properties" seguente nel file pom.xml, dopo la sezione "dependencies".

    <properties>
      <maven.compiler.source>1.8</maven.compiler.source>
      <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
  7. Aggiungere la sezione "build" seguente nel file pom.xml, dopo la sezione "properties", per supportare i file manifesto in file JAR.

    <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. Salvare e chiudere il file pom.xml.
  9. Aprire il file App.java (C:\apache-maven-3.5.0\SqlDbSample\src\main\java\com\sqldbsamples\App.java) e sostituire il contenuto con il contenuto seguente. Sostituire il nome del gruppo di failover con il nome del proprio gruppo di failover. Se si sono modificati i valori per il nome del database, l'utente o la password, modificare anche tali valori.

    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;
       }
    }
    
  10. Salvare e chiudere il file App.java.

Compilare ed eseguire il progetto SqlDbSample

  1. Nella console dei comandi eseguire questo comando:

    mvn package
    
  2. Al termine, eseguire questo comando per eseguire l'applicazione (a meno che non venga arrestata manualmente, l'esecuzione dura circa 1 ora):

    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
    

Eseguire un'esercitazione sul ripristino di emergenza

  1. Chiamare il failover manuale del gruppo di failover.

    Switch-AzureRMSqlDatabaseFailoverGroup `
    -ResourceGroupName $myresourcegroupname  `
    -ServerName $mydrservername `
    -FailoverGroupName $myfailovergroupname
    
  2. Osservare i risultati dell'applicazione durante il failover. Alcuni inserimenti avranno esito negativo durante l'aggiornamento della cache DNS.

  3. Individuare il ruolo eseguito dal server di ripristino di emergenza.

    $mydrserver.ReplicationRole
    
  4. Effettuare il failback.

    Switch-AzureRMSqlDatabaseFailoverGroup `
    -ResourceGroupName $myresourcegroupname  `
    -ServerName $myservername `
    -FailoverGroupName $myfailovergroupname
    
  5. Osservare i risultati dell'applicazione durante il failback. Alcuni inserimenti avranno esito negativo durante l'aggiornamento della cache DNS.

  6. Individuare il ruolo eseguito dal server di ripristino di emergenza.

    $fileovergroup = Get-AzureRMSqlDatabaseFailoverGroup `
       -FailoverGroupName $myfailovergroupname `
       -ResourceGroupName $myresourcegroupname `
       -ServerName $mydrservername
    $fileovergroup.ReplicationRole
    

    Passaggi successivi

Per altre informazioni, vedere l'articolo relativo a gruppi di failover e replica geografica attiva.