I have one Azure SQL Database in Pre-production which has multiple schemas. For one particular schema, we need to have a mechanism where customers can use it for testing while developers can also keep incorporating the changes received as feedback. Other than creating another database altogether, is there a way how this can be achieved in the same existing database? Something like creating 2 schemas within the same database but then having a mechanism to autosync the structure and data whenever needed.

I have one Azure SQL Database in Pre-production which has multiple schemas. For one particular schema, we need to have a mechanism where customers can use it for testing while developers can also keep incorporating the changes received as feedback. Other than creating another database altogether, is there a way how this can be achieved in the same existing database? Something like creating 2 schemas within the same database but then having a mechanism to autosync the structure and data whenever needed.

• sql
• database
• azure

• Have you tried any approach? – Bhavani Commented Nov 20, 2024 at 4:18
• Is it fine to have a set up like this? – KEB Nelson Commented Nov 20, 2024 at 6:16

1 Answer 1

As you mentioned you want to create data sync in Azure SQL Database for the same database with two different schemas it is not possible.

Because When tables with the same name exist in different schemas within a sync group, SQL Data Sync cannot accurately find which table to update,leading to potential data conflicts and errors.

As a workaround if you need to sync data between different schemas within the same database, You can set up stored procedures within each schema to extract the data you want to sync and use a separate database as a "hub" to facilitate the data transfer between the schemas.

CREATE DATABASE HubDB;

USE Schema1;
CREATE PROCEDURE ExtractDataFromSchema1
AS
BEGIN
    SELECT * FROM YourTable1; -- Replace with your actual table and columns
END;

Do the same for the Schema 2 Within each schema, create stored procedures to extract the required data.

Next,Create stored procedures in the HubDB to pull the data from each schema and insert it into the hub.

USE HubDB;
CREATE PROCEDURE SyncDataFromSchema1
AS
BEGIN
    INSERT INTO HubTable (Column1, Column2, ...)
    EXEC Schema1.dbo.ExtractDataFromSchema1;
END;
CREATE PROCEDURE SyncDataFromSchema2
AS
BEGIN
    INSERT INTO HubTable (Column1, Column2, ...)
    EXEC Schema2.dbo.ExtractDataFromSchema2;
END

In the above created a separate database that will be used as the hub for data transfer. Created Stored Procedures to extracted Data Next you can schedule these stored procedures to run at regular intervals.