none
How to share or sync data in the tables among different databases on the same SQL server RRS feed

  • Question

  • On the database server with the SQL Server 2017 Standard edition, there are some databases, let's say 3 databases, DB1, DB2 and DB3. All table structures among those databases are exactly same. Data in some of the tables will be shared or be synced among them. For example, Table1 exists in all three databases. When data is inserted to the table Table1 in DB1, it should also be inserted to the same table in DB2 and DB3. It is the same thing if data is inserted to the table in DB2 or DB3. Since the SQL server edition is standard, the Peer-to-peer transactional replication feature does not work. Any idea how to achieve the target? Thanks.  

    A Fan of SSIS, SSRS and SSAS

    Friday, December 6, 2019 6:15 PM

All replies

  • If INSERT is the only activity on these tables, you can create an AFTER trigger (with INSERT INTO) on each table that will insert records into target tables from a source table. Note that source and target are relative terms here. However, if UPDATE and DELETE also occur on the tables, then use MERGE statement inside the trigger.
    Friday, December 6, 2019 8:23 PM
  • Trigger may not work. A table Table1 exists in multiple databases. When data is inserted to, updated to or deleted from the table in DB1, the same action should happen in DB2 and DB3. If the action happens in DB2, it should also occur in DB1 and DB3.

    A Fan of SSIS, SSRS and SSAS

    Friday, December 6, 2019 9:18 PM
  • is it necessary to have table in all three DB's ?

    can you centralized the table in one DB or separate one and create view in DB1/2/3 and point to that.

    If the inserts\updates are straight forward and affecting only one table, you can do that using views.


    Hope it Helps!!


    • Edited by Stan210 Friday, December 6, 2019 10:13 PM
    Friday, December 6, 2019 10:09 PM
  • As always, the question is: how did you end up here? That is, what is the rationale for wanting this behaviour?

    What are the requirement? Does it need to be synchronous, and the operation should fail if the data is not committed to all three databases? Or is it acceptable with some delay?

    Only inserts? Or also updates and deletes?

    What is the structure for the replicated tables? Do they have some datetime or rowversion column which is set automatically? A monotonically increasing id?

    What are the security settings? Do the databases have the same owner? Would it be acceptable to enable cross-database ownership chaining?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 6, 2019 10:22 PM
  • Among those databases, all of the setting are the same. Each database has more than 2000 tables. Maybe half of them will be shared with data. When a user logs on one database to add, update or delete data, the same operations should happen in all of other databases. Basically those shared tables should have the same data. Those tables should have the datetime column (I need to verify it).

    A Fan of SSIS, SSRS and SSAS

    Friday, December 6, 2019 10:42 PM
  • Is that possible to redesign it and put under the different schema in the single database?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 9, 2019 5:01 AM
    Moderator
  • Hi Guoxiong,

     

    According to your description, I suggest you use the CDC to sync data.  Once you enable CDC for the specific tables that you need to get the changes.  The records are then stored in a changed table created by CDC. Then you can push the changes to the other database. For more details, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 9, 2019 6:37 AM
  • Is that possible to redesign it and put under the different schema in the single database?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thank you. If that is a case, how can data be synced? For example, there are tables s1.Table1, s2.Table2 and s3.Table1. 

    A Fan of SSIS, SSRS and SSAS

    Monday, December 9, 2019 2:28 PM
  • Thank you Dedmon. We actually want to sync data among the different databases, but not focus on recording the changes. We have some temporal tables to record the changes of data.

    A Fan of SSIS, SSRS and SSAS

    Monday, December 9, 2019 2:37 PM
  • Among those databases, all of the setting are the same. Each database has more than 2000 tables. Maybe half of them will be shared with data. When a user logs on one database to add, update or delete data, the same operations should happen in all of other databases. Basically those shared tables should have the same data. Those tables should have the datetime column (I need to verify it).

    Updates and deletes as well. Then you will need to handle conflicts as well. Ouch.

    Permit me to be curious to ask how you ended up in this quagmire? I could have understood this if the three databases had been on different servers in different corners of the world, but on the same server?

    Anyway, there were a lot more questions in my previous post. I will not repeat them. But nor will I make suggestions until I know more.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 9, 2019 10:31 PM
  • Same database? Simple trigger...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 10, 2019 5:00 AM
    Moderator
  • The feature not only records changes, but also syncs those changes

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, December 10, 2019 6:29 AM
  • Thank you Erland for your responses.

    I know this is a very tough task. We already have the application which uses a database. But now a client would like to use this application for multiple branches, i.e., one for USA, one for Canada and one for Europe. Each branch has its own database and they are all on the same SQL server. Let's say there are 100 tables in each database. 50 of them must have the same data. For example of the table "Size", when an user logs on to DB_US and adds a new size, this new size must be added to the same table in DB_CA and DB_EU. If an user logs on to DB_EU and adds a new size, it should also be added to the same table on DB_US and DB_CA.

    The actions include INSERT, UPDATE and DELETE. I think those tables act as the dimensional and are still relational. If data is not committed to all three databases, the transaction should be rolled back. Delay within a minute should be okay.

    Those tables have the primary key and some of them have the IDENTITY column as the primary key. Each table should have a datetime column, otherwise we can add one.

    All settings are the same among those three databases. I think that enabling cross-database ownership chaining should be acceptable.


    A Fan of SSIS, SSRS and SSAS

    Tuesday, December 10, 2019 10:41 PM
  • The actions include INSERT, UPDATE and DELETE. I think those tables act as the dimensional and are still relational. If data is not committed to all three databases, the transaction should be rolled back. Delay within a minute should be okay.


    There is a contradiction. "If data is not committed to all three databases, the transaction should be rolled back" - this calls for a synchronous solution. "Delay within a minute should be okay" - this accepts a asynchronous solution.

    If we go by the first, there is really only one solution and that is triggers. And this will more or less require that you enable ownership chaining. This is OK if there is no one who only have elevated permissions on database level. That is, the server-level DBAs are also the database-level DBAs. The databases should have the same owner.

    If ownership chaining is not OK, but an asynchronous solution is acceptable, I would consider a Service Broker solution. Certainly more complex and requires more coding, but less heavy artillery than CDC.

    The great thing with a synchronous solution is that conflicts can not really occur. If there are simultaneous updates in two databases, #2 will win - it is that simple.

    But with type of data you indicate, updates to the same row might be rare, and the risk for conflicts may be small.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, December 11, 2019 11:07 PM