table replication

Last Post 12 Mar 2011 10:31 AM by russellb. 10 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
samuelh
New Member
New Member

--
10 Mar 2011 11:45 AM
Hi all,

I have two SQL servers, one in West Coast and one in East Coast.

West Coast has database A

East Coast has Database B

These are completely different databases. But now I have a table that i created on the West Coast Database. It is WestCoast.DatabaseA.Table1 that I want to replicate to EastCoast.DatabaseB.Table2.

Table WestCoast.DatabasA.Table1 will be updated daily and therefore I want to make sure that it's replicated to EastCoast.DatabaseB.Table2. What's the best way to handle this? SSIS? I really don't want to replicate the whole database for this one table. If I do SSIS then, I only want to grab the latest data. Can I do that using SSIS?

thanks in advance



gunneyk
New Member
New Member

--
10 Mar 2011 03:03 PM
What version of SQL Server do you have? If you have 2008 then take a look at CHANGE DATA CAPTURE and CHANGE TRACKING in BooksOnLine. This link will show you the difference and you can decide which is best for your situation.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/9cd665ce-18de-48ee-a887-633b377ad637.htm
samuelh
New Member
New Member

--
10 Mar 2011 03:16 PM
currently we're on SQL server 2005 Standard version
russellb
New Member
New Member

--
10 Mar 2011 04:24 PM
In replication, you don't replicate an entire database. You pick which objects you want to replicate -- in your case, a single table (for now).

Are the two data centers connected via VPN?
samuelh
New Member
New Member

--
10 Mar 2011 05:17 PM
yes VPN connection.
So SSIS not a good idea? Rather replicate tables? I know that there are always issues with replication. I stay away from it. but if that is the only choice, then I'll go for it.

Thanks again for the inputs

gunneyk
New Member
New Member

--
10 Mar 2011 06:33 PM
Replication isn't the only only option it is the one that requires virtually no code on your part. You can write your own auditing system to track changes to the table and update the other table accordingly but you would have to code it. Updates and Inserts to the original table are easy to do if you add a timestamp column. Then look for any rows that have a newer timestamp than the last time you did the sync. Bring those rows over to the other server and If the PK matches you update that row. If there is no match you know it was a new row on the original table so insert it there. But the trick is what about rows that were deleted on the original table. Unless you track deletes you will not know if it is deleted unless you compare all the rows for missing ones. You can create a trigger on that table and log to a table any deletes and use that as part of your sync process. It all depens on your requirements and how fancy you need to be. Or you could use replication and have it take care of all that for you. If it were me and it was a single table and wont keep adding more tables I would probably just code it but I have done that many times before so it wouldnt take me that long. YMMV.
rm
New Member
New Member

--
11 Mar 2011 06:21 AM
Also depends on if you like to get changes over in real time.
samuelh
New Member
New Member

--
11 Mar 2011 01:50 PM
Yes real time changes is what they want.
samuelh
New Member
New Member

--
11 Mar 2011 01:51 PM
and I really appreciate your input on this guys!
rm
New Member
New Member

--
12 Mar 2011 06:30 AM
I'll try replication first if doesn't have heavy change on the table.
russellb
New Member
New Member

--
12 Mar 2011 10:31 AM
Me too. And I have to disagree with "I know that there are always issues with replication"

It is pretty easy to setup and relatively maintenance free.
You are not authorized to post a reply.

Acceptable Use Policy