SQL Data Sync - Would be face issue when we have a single metadabase db for prod and dev environment

Nitya V 45 Reputation points
2024-01-11T11:00:46.5433333+00:00

Hi Team, I see that it is documented that we can have only one metadata database for a subscription/per region. When we have production and non production databases on the same region/Subscription with the same schema and object. Would it be a issue as the sync metadata database is the same for both production and non production environments.Can we ensure that production and non production environments dont cause discrepancies' in each others env

Azure SQL Database
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 34,461 Reputation points MVP
    2024-01-11T11:44:07.0433333+00:00

    My recommendation is not to use SQL Data Sync in this scenario, any change in schema you want to test on DEV and the sync will be broken until you replicate those changes to the hub database. Maybe you may consider using Azure Data Factory to refresh the data on the Dev environment whenever you want with the Production data. You can also use Azure Data Factory to apply techniques of data masking (such as static data masking, dynamic data masking, substitution, shuffling, encryption, or hashing) to protect sensitive data coming from Production to the Dev database.


  2. GeethaThatipatri-MSFT 29,517 Reputation points Microsoft Employee
    2024-01-11T16:29:18.1566667+00:00

    @Nitya V Same sync metadata DB should not affect sync itself. The sync Db is just used for storing sync configuration. There is no real data stored in sync Db. So you should not worry about the data integrity. The sync only happened between hub and member db. For your case, please use a dedicated db as sync Db instead of using sync db as hub db.

    Regards

    Geetha


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.