Hybrid cloud solution

Overview

The ApsaraDB for SQL Server hybrid cloud solution enables data transfer and data synchronization between your on-premises SQL Server and an ApsaraDB for SQL Server instance. This solution uses SQL Server replication to synchronize data. A typical use case is to write data to your on-premises database and read data from the ApsaraDB for SQL Server instance.

Architecture

How it works

1. Overall architecture

   This is a typical 2+3 solution that provides high availability and high scalability for SQL Server. The primary and secondary servers use mirroring for database synchronization to enable failover. The distributor is placed on a separate server to ensure continuous data synchronization to the subscriber if the publisher fails over. The publisher and distributor are located in your on-premises environment, where you have full administrative control.

   The subscriber is hosted on an ApsaraDB for SQL Server instance. We recommend using a single-node instance for the subscriber instead of a high-availability instance. This makes it easier to scale out later. If you use a high-availability instance for the subscriber, which also uses mirroring for its own high availability, the subscriber cannot synchronize data correctly after a failover.

   Note

   We recommend connecting your on-premises environment and ApsaraDB for SQL Server instance by using a VPN or Express Connect.

2. Distributor

   Important

   You must use a dedicated server for the distributor. Do not install the distributor on the same server as the publisher. Otherwise, the distributor will not function correctly after the publisher fails over.

   1. Install SQL Server. During the installation, make sure to select the replication feature.

   2. Configure the distributor.

      USE master
      EXEC sp_adddistributor @distributor = N'RDS-TEST-DIST', @password = N''
      GO
      EXEC sp_adddistributiondb 
          @database = N'distribution', 
          @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data',
          @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data', 
          @log_file_size = 2,
          @min_distretention = 0, 
          @max_distretention = 72, 
          @history_retention = 48, 
          @security_mode = 1
      GO
      USE [distribution] 
      GO
      IF (
          NOT EXISTS (
              SELECT * 
              FROM sysobjects 
              where name = 'UIProperties' and type = 'U ')
      ) 
          CREATE TABLE UIProperties(id int) 
      IF (
          EXISTS (
              SELECT * 
              FROM ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)
              )
      ) 
          EXEC sp_updateextendedproperty 
              N'SnapshotFolder', 
              N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ReplData', 
              'user', dbo, 
              'table', 
              'UIProperties' 
      ELSE 
          EXEC sp_addextendedproperty N'SnapshotFolder',
           N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ReplData', 
           'user', dbo, 
           'table', 
           'UIProperties'
      GO

   3. If you are not in an Active Directory domain environment, or if automatic mapping is disabled in your domain, you must register an alias for the distributor and the subscriber. Registering the subscriber is more complex. Follow these steps:

      1. Create a logon account on the ApsaraDB for SQL Server instance and grant it the required permissions. For more information, see Create databases and accounts.

      2. View the hostname of the server.

      3. Apply for a public endpoint for the instance. For more information, see Apply for a public endpoint.

      4. Configure an alias in SQL Server Configuration Manager.

         Note

         You must configure the alias for both the 32-bit and 64-bit SQL Native Client.

         In the left pane, expand the SQL Native Client 11.0 Configuration (32bit) node and then click Aliases. In the right pane, configure an alias for each ApsaraDB for SQL Server instance. Fill in the Alias Name, Server (IP address), Protocol (select TCP), and port number.

   4. On the distributor, register the primary and secondary publishers. In Object Explorer, expand the Replication node, right-click it, and open the Distributor Properties - RDS-TEST-DIST dialog box. On the Publishers tab, confirm that RDS-TEST-MASTER and RDS-TEST-SLAVE are added as publishers and that the distribution database for both is distribution. In the Administrative link password section, set the Password and Confirm password.

3. Publisher

   On each publisher, such as rds-test-master and rds-test-slave, perform the following steps:

   1. Configure the distributor and set it to rds-test-dist. In SSMS, expand Object Explorer, right-click the Replication node, and open the Publisher Properties dialog box. Set Distributor to RDS-TEST-DIST and Distribution database to distribution. In the Administrative link password section, enter and confirm the password.

   2. As on the distributor, you must register an alias for each subscriber that maps to its actual hostname.

   3. On the publisher, create a table that contains a primary key.

   4. Create the publication.

      Note

      You can select only transactional publication. We recommend using a SQL Server login to connect to the publisher.

      In the New Publication Wizard, on the Publication Type page, select Transactional publication, and then click Next. In the Snapshot Agent Security dialog box, select Run under the SQL Server Agent service account. In the Connect to the Publisher section, select Using the following SQL Server login, enter the Login and Password, and then click OK.

4. Subscriber

   The subscriber is hosted on an ApsaraDB for SQL Server instance. You can use a Basic or High-availability Edition instance. However, we recommend that the publisher, distributor, and subscriber all use the same SQL Server version.

   Important

   When you create a subscription, note the following:

   • Since the subscriber is on an ApsaraDB for SQL Server instance, you must apply for a public endpoint.

   • You must obtain the actual hostname of the subscriber server. This hostname is required when you create an alias for the subscriber on the distributor and publisher.

   • The subscription must be a push subscription. Pull subscriptions are not supported.

   • You cannot use the SQL Agent Account for the subscription login. Instead, you must use a SQL Server login. In the Distribution Agent Security dialog box, in the Connect to the Subscriber section, select Using the following SQL Server login, enter the Login and Password, and click OK. In the New Subscription Wizard, on the Subscribers page, click the Add Subscriber drop-down button and select Add SQL Server Subscriber....

5. Mirroring with replication

   When using mirroring and replication together, if a failover occurs between the primary and secondary servers, consider the following points to ensure the database remains available:

   1. If the primary server fails and a failover occurs, the log reader agent waits for the mirrored logs to synchronize before publishing. However, if the primary server has a hardware failure, you must enable trace flag 1448 on the new primary server (the former secondary server). This allows the log reader agent to continue distributing data even if mirroring is down.

      Note

      Trace flag 1448 allows the log reader agent to continue reading from the principal database in a transactional replication and database mirroring topology, even if the mirror is unavailable.

   2. You must specify a partner server for the log reader agent and the snapshot agent.

6. Replication with ApsaraDB for SQL Server

   • An ApsaraDB for SQL Server instance can only act as a subscriber; it cannot be configured as a publisher or a distributor.

   • Any version of ApsaraDB for SQL Server can act as the subscriber.