Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Recommended replication type

Author  Topic 

liquifusion
Starting Member

3 Posts

Posted - 2007-07-27 : 12:11:13
We are replicating data from server1 to server2. We expect the connection between servers to be reliable, but we can not always guarantee uptime on both ends. We do not need real-time data access on server2. What type of replication would be best? The downside we see to snapshot is that the data will be growing over time and that means the amount replicated will continue to grow. Can we setup transaction replication and then schedule the updates so it only replicates transactions since the last update? Does this present any problems if the connection is lost at any time between the servers? At this time, we will not be making any changes to the data on server2 so it does not need to be updated on server1.

evjo
Starting Member

20 Posts

Posted - 2007-07-27 : 23:22:48
Sounds like you should try Merge Replication. You can schedule the synchronization at a time when you know both servers to be up.

Whenever I see an old lady slip and fall on a wet sidewalk, my first instinct is to laugh. But then I think, what if I was an ant, and she fell on me. Then it wouldn't seem quite so funny.

- Jack Handey
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-27 : 23:56:49
Keep in mind that merge replication may add additional timestamp column in replicated table. You can set transactional replication to replicate at scheduled time also.
Go to Top of Page

liquifusion
Starting Member

3 Posts

Posted - 2007-07-28 : 19:27:14
So transactional rep doesn't use a timestamp column? Also - does transactional rep require constant connection between servers or only when the rep takes place (when scheduled)?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-28 : 20:32:06
You can set queued replication with transactional replication, but replicated table needs pkey.
Go to Top of Page

homebrew
Posting Yak Master

114 Posts

Posted - 2007-08-15 : 15:58:27
I would suggest transactional replication if your tables have PKs. The subscriber job can run at whatever schedule you want. If there's no connection between servers, the job will fail, and try again at the next interval.
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 16:24:50
Step by Step merge replication


http://www.databasejournal.com/features/mssql/article.php/1438231

Ashley Rhodes
Go to Top of Page

homebrew
Posting Yak Master

114 Posts

Posted - 2007-08-15 : 16:39:49
Merge Replication is not needed in this case.

"Can we setup transaction replication and then schedule the updates so it only replicates transactions since the last update?"
YES - sort of. You schedule the job, and it runs when you want it to, regardless of there being updates or not.

Does this present any problems if the connection is lost at any time between the servers?
NO
Go to Top of Page
   

- Advertisement -