Multi-Tenant SQL Server Replication to the Edge with SymmetricDS

Short answer: Multi-tenant SQL Server replication with SymmetricDS syncs each tenant database to its own edge database, bi-directionally, with no customer data crossing tenant boundaries. You match each edge node's external ID to its tenant database name, and one routing rule serves every tenant.

Key takeaways

• Each edge node's external ID must equal the name of the central tenant database it replicates with.
• One routing rule serves every tenant because the router uses the edge node's external ID to pick the tenant database.
• The setup uses two node groups: one for the central tenant databases and one for the edge nodes.
• Running a SymmetricDS engine at each customer site gives the best performance.
• You can export a working configuration from the SymmetricDS console and import it into any other installation.

Goal & Prerequisites: Syncing Isolated Tenant Databases to the Edge

By the end of this guide, each tenant database in your central multi-tenant SQL Server will replicate bi-directionally to its own on-premise or edge database, with no customer data crossing tenant boundaries. Because every tenant database sits on the same SQL Server connection, you manage loads, maintenance, and node health from one central dashboard.

Prerequisites:

• A SQL Server instance hosting the tenant databases
• SymmetricDS server installed
• An edge SQL Server database for each tenant
• Credentials with replication permissions on all databases
• Network connectivity between central and edge nodes

With the prerequisites in place, you can configure the node groups, external IDs, and routing that tie each tenant to its edge database.

Step-by-Step: Configuring Multi-Tenant Replication

This setup follows standard SymmetricDS configuration, so we won't repeat the basics here. The steps below cover only what makes the multi-tenant design work.

1. Use two node groups. Define one group for the central tenant databases and one for the edge nodes. Link them for bi-directional sync, with the edge pushing to central and central pulling to the edge.

2. Install SymmetricDS centrally and at each client. Running an engine at each customer site gives the best performance. The edge node syncs against its local database and talks to the central engine only for batches.

3. Match the external ID to the tenant database name. This is the key assumption in the design. When you set up (personalize) each edge engine, its external ID must equal the name of the central database it replicates with. A central database named tenant1 needs an edge node with the external ID tenant1.

4. Route on the external ID. The router configuration uses the edge node's external ID to pick the matching tenant database. Each tenant's changes go only to the edge node with the same ID, so one routing rule serves every tenant.

5. Update the edge to central router. Use the external id of the node sending the change to load data into the matching target database.

6. Register the edge nodes and load. Open registration for each edge node, then send the initial load from central.  Once the edge nodes are registered and loaded, confirm that data flows in both directions and stays within each tenant.

Verification: Confirming Sync and Isolation

Start in the central dashboard. Each edge node should show as registered and enabled, with its external ID matching a tenant database name.  Next, test both directions.

• Insert a test row in a tenant table at central and confirm it arrives at the edge.
• Update that row at the edge and confirm the change replicates back to central.
• Check the batch list for errors, since every batch should read OK.
• Finally, query tenant2's edge database for tenant1's test row. It should never appear.

When all four checks pass, you can save that working setup and reuse it elsewhere through export and import.

Export / Import Configuration

You can set this scenario up on a local machine with a single installation of SymmetricDS, or in a lab with a simple central node and two edge nodes. Once it works, export the configuration from the SymmetricDS console and import it into any other installation. The configuration holds in a production deployment without changes. Many tenant databases at central can replicate to many customer edge nodes, each running its own remote SymmetricDS installation. Build and test small, then export the same node groups, group links, channels, triggers, and routers to scale out. From there, the work shifts to growing and operating the deployment.

Next Steps: Operating at Scale

Once your first tenant is syncing, onboarding additional tenants, running data loads from the central console, and monitoring node health all follow the same pattern. For guidance on growing this setup, read our post on scaling SymmetricDS for many clients. You can also browse the SymmetricDS documentation or start a free trial.

Frequently asked questions