Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the web site. When this happens, is there a way on the SQL Server backend to update/insert that value into Database02?

share|improve this question
1  
Databases don't directly have columns. Are you talking about databases, or perhaps tables? –  Max Vernon Nov 19 '13 at 4:16
    
I see you have added the trigger tag. Have you tried using a trigger to solve this problem? –  Max Vernon Nov 19 '13 at 4:17
    
Whoops! Then I have two tables. Table01 in Database01 and Table02 in Database02. I haven't used triggers before and want to see if I am on the right track. Would this be best practice? –  gmatteson Nov 19 '13 at 4:38
    
The answer provided by @billinkc accurately recaps the choices. The third choice would be to modify the website code to update both tables. –  Max Vernon Nov 19 '13 at 5:07
add comment

2 Answers 2

There are two options that come to mind. The first you have identified as a trigger that you could implement in TSQL for real-time synchronization of data. Depending on the activity of your system, the quality of your hardware (particularly I/O subsystem).

Synchronous approach

A trigger, implemented something like this. You would know better specifically what logic you need but the heart will be an AFTER trigger on the table in the system of record.

CREATE TRIGGER dbo.tr_TableSync
ON dbo.Table1
AFTER INSERT, UPDATE
BEGIN
    SET NOCOUNT ON;

    -- sync existing
    UPDATE
        T2
    SET
        RefVal = I.Val
    FROM
        T2
        INNER JOIN
            inserted AS I
            ON I.SK = T2.SK;

   -- Add new stuff
   INSERT INTO
       T2
   (
       SK
   ,   RefVal
   )
   SELECT
       I.SK
   ,   I.Val
   FROM
       inserted AS I
   EXCEPT
   SELECT
       T2.SK
   ,   T2.RefVal
   FROM
       T2;

   -- Do we need to account for deleted items?
   -- If so, need to add to declaration above 
   -- and code here
END

Asynchronous approach

The challenge around the trigger is that it can have a heavy footprint on performance to data manipulation operations in that table. SQL Server offers two approaches for asynchronous operations that provide a lighter footprint. You can look at implementing Service Broker or creating a CLR trigger.

Service Broker is a message queue. You'd create a message in your AFTER trigger (unless you can guarantee all manipulation is only performed through a proc or some other codified method) which will then get parsed when the system has a "chance to breathe" but it is guaranteed to happen just not RIGHT NOW DO IT NOW I'M WAITING ARE YOU DONE like the synchronous approach above forces.

I've never had cause to implement a CLR trigger before but it's possible and in your case, it'd be much like implementing your own message queue.

Final thought

Everyone loves to say "it needs to be instantaneous/real-time" but when they learn what that can cost to implement correctly, near-real-time of a few minutes to a day (yes, I've had people go from it must be accurate to the nano-second to as long as it's within 24 hours, we'll be fine). If that can satisfy your business needs, then you have even more doors opened (a SQL Agent job that fires SQL statements or SSIS or a custom .NET app every N interval).

share|improve this answer
    
Wow, excellent writeup! I appreciate the time you took. Very insightful. Question for you, if I had to chose between data integrity and performance, I would rather have data integrity between the two databases and tables. Which one would you lean towards? –  gmatteson Nov 19 '13 at 20:44
add comment

In situations like this, I would go for Service Broker. Inserting data in another db via Trigger is also an option but very risky. As if the insert fails, whole transaction will rollback.

share|improve this answer
    
Great to know. Thanks. When you mean the whole transaction, do you mean the insert statement for database01/table01 and database02/table02 would fail? or would just the data being inserted into the second database/table? –  gmatteson Nov 19 '13 at 20:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.