Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

This is the scenario:

  • 1 x SQL Server 2000 database running at remote site (Publisher)
  • Website running at a different site requires mostly read and some write access (<5 tables) to the database
  • Cross Network bandwidth utilization must be kept to a minimum

We would like to use SQL Server replication for this, with a subscriber at the location as the web server. Is merge replication the most appropriate replication type for this?

I did consider Transactional Replication with Updating subscriptions but according to technet, this is being discontinued in the next release of SQL Server.

We would like the replication to happen as real time as possible but network utilization is a consideration.

Thanks Ben

share|improve this question

1 Answer 1

up vote 1 down vote accepted

You'll want to use Merge. That'll be the best option when you need to do updates on both sites.

You might want to upgrade to SQL 2005 or newer as SQL 2000 isn't a supported version any more.

share|improve this answer
    
is it just that 2000 isn't supported or is 2005+ a prerequisite? (note that the subscriber will be 2005+). –  Ben Oct 3 '10 at 19:41
1  
There's a caveat to using Merge replication though, and that's your app needs to have been designed fairly well, in that insert statements without explicitly specified columns will fail because of the rowguid column that gets inserted into the table. I strongly recommend setting up a 2nd database, turning on Merge Replication and testing your app against it before you do it to your live scenario. –  Mark Henderson Oct 3 '10 at 20:13
    
@Ben Merge replication was available in SQL 2000, but it was improved in SQL 2005 and up a lot (as was most everything else as well). To pay careful attention to what @Farseeker posted about the new column being added to every table. Test a ton before trying this in production. –  mrdenny Oct 7 '10 at 18:40

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.