Thursday, February 3, 2011

Choosing the right SQL server replication type

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

  • 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.

    Ben : @mrdenny - is it just that 2000 isn't supported or is 2005+ a prerequisite? (note that the subscriber will be 2005+).
    Farseeker : 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.
    mrdenny : @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.
    From mrdenny

0 comments:

Post a Comment