Technology Encyclopedia Home >How to publish and subscribe between the local IDC self-built SQL Server and the cloud database SQL Server?

How to publish and subscribe between the local IDC self-built SQL Server and the cloud database SQL Server?

Created on 2025-05-23 10:03:09
10

To enable publishing and subscribing between a local IDC self-built SQL Server and a cloud database SQL Server, you can use SQL Server Replication. This feature allows data to be copied and distributed from one database (publisher) to another (subscriber), supporting scenarios like disaster recovery, reporting, or data synchronization across environments.

Key Steps:

  1. Configure the Publisher (Local IDC SQL Server)

    • Set up the local SQL Server as the publisher by enabling replication in SQL Server Management Studio (SSMS).
    • Create a publication (e.g., snapshot or transactional replication) for the tables/databases you want to share.

  2. Set Up the Subscriber (Cloud SQL Server)

    • Configure the cloud-based SQL Server as the subscriber. Ensure it has network connectivity to the publisher (e.g., via VPN, ExpressRoute, or public endpoints with security rules).
    • Use SSMS to create a subscription that points to the publisher’s publication.

  3. Network Connectivity

    • The cloud SQL Server must be reachable from the local IDC. For secure communication, use:
      • VPN Gateway or ExpressRoute (for private connectivity).
      • Firewall rules to allow traffic on SQL Server ports (default: TCP 1433).

  4. Synchronization

    • The publisher sends changes to the subscriber based on the replication type (e.g., transactional for near-real-time sync, snapshot for periodic updates).

Example Scenario:

A company’s local SQL Server hosts transactional data, while the cloud SQL Server is used for reporting. Transactional replication ensures reports always reflect the latest data.

Recommended Tencent Cloud Services:

  • Tencent Cloud SQL Server: Managed database service with high availability and scalability.
  • Tencent Cloud Virtual Private Cloud (VPC): Isolated network environment for secure connectivity.
  • VPN Gateway: Establishes a secure tunnel between local IDC and Tencent Cloud.
  • ExpressRoute: Dedicated private connection for low-latency, high-bandwidth replication.

This setup ensures reliable data synchronization while leveraging cloud scalability and local control.