August 30, 2011 at 10:54 am
Hello everyone,
Looking for some expert advice regarding this scenario:
Tables on one server need to be updated with deltas on another.
Only deltas will be inserted into the replicated and or mirrored table.
There are only three tables in the database that need to be mirrored on the other server.
The bandwidth of the server is very slow between servers.
1). Should I use SSIS to copy deltas from one table to another.
2). Link the servers and use a script to copy deltas across.
3). Set up transactional replication between these tables.
Anytime data is loaded into the principal table the load date is updated.
Jonathan
August 30, 2011 at 11:11 am
any of these could work. I tend to like replication since it picks up by itself if the connection is down or delayed, but you need to administer it a bit more.
SSIS is very portable, and can easily be enhanced if you need to transform things.
Not a big linked server fan. Seems flakier to me and more of a hassle when things change.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 31, 2011 at 4:53 am
+1 on replication too, however you need to consider the schedule for reinitializing the subscriptions due to the nature of connectivity between the servers. In terms of manageability Replication is better as compared to SSIS & Linked servers.
-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network
August 31, 2011 at 9:40 am
Excellent. Will delve deeper into transactional replication.
I do have the database being mirrored where those table deltas need to be copied.
Should I replicate from the partner database or from the principal database?
September 1, 2011 at 2:24 am
You can only use PRINCIPAL database in this case (best too) and make sure you have tested the setup. Also a http://msdn.microsoft.com/en-us/library/ms151799.aspx refereince for interoperability between the features.
-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network
September 6, 2011 at 1:22 pm
Thank you that has helped with my research.
As of right now I do not have a remote distributor and or witness server meaning the principal server would have to serve as the distributor for the replication. If I can get a 3rd box to be the remote distributor can this box also server as a witness server to the mirroring?
Jonathan
September 6, 2011 at 3:30 pm
I'm looking to setup database mirroring with replication.
Database mirroring is already setup.
Is it possible for a server to act as a distributor and witness?
Principal Server A is syncing with Partner server B
I would like Principal Server A to replicate some tables to server C.
So this would require me to have a distributor and ideally that distributor to be a remote. Can the remote server also act as a witness?
Need to request another server to accommodating this setup.
What do you guys think?
Jonathan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply