SQL Server replication

Posted on 2012-09-14
Last Modified: 2012-09-17
I am employing a star schema on multiple databases.  Several Dimension tables are spanned across all of the databases.  

For example if I have table1_Dim,  That Dimension table is in Database1, Database2, and Database3 to support three different datamarts.

The issue is that table1_Dim must be equal (schema and data) in all three databases, regardless which database table1_Dim was updated in.

For example:
if Database1.table1_Dim is updated, then Database2.table1_Dim and Database3.table1_Dim must also be updated.  

This is for schema and data.

Can anyone recommend a replication method that works this way?
Question by:Evan Cutler
    LVL 26

    Expert Comment

    If the databases are on the same server you don't need replication. You can build either triggers on each table or a procedure to keep the them in sync and execute it at a predefined interval, say every 5 min, if that works.
    LVL 9

    Author Comment

    by:Evan Cutler
    ok...I've heard of triggers but not anything concrete.
    Got any references?
    LVL 26

    Expert Comment

    LVL 26

    Accepted Solution

    Basically on all three tables you will build 3 triggers:

    tr_table1_Dim_I (for insert)
    tr_table1_Dim_U (for update)
    tr_table1_Dim_D (for delete)

    one for each operation.

    In the insert trigger you will check if the same article already exists in the other 2 tables and if not insert it. You do that one table at a time, of course, check and insert.

    In the delete and update triggers just apply the same operation in the other 2 tables.

    Of course in each trigger you will use the PK column to identify the rows in the other tables.
    LVL 9

    Author Closing Comment

    by:Evan Cutler
    Sorry it took me a while to Grade and award...
    took me some time to get the solution working.

    That was good advice, the performance is so much better.
    Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now