Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Server replication

Posted on 2012-09-14
Medium Priority
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
  • 3
  • 2
LVL 27

Expert Comment

ID: 38399227
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.

Author Comment

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

Expert Comment

ID: 38399297
LVL 27

Accepted Solution

Zberteoc earned 2000 total points
ID: 38399309
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.

Author Closing Comment

by:Evan Cutler
ID: 38406694
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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

577 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