Post reply

Replicating ~3000 tables Will it work?

  • September 8, 2004 at 4:13 pm

    #360862

    We have a project that is using Commerce Server 2002 for an eCommerce store. The project team wants to use Virtual catalogs each of which creates 7 tables, 7 views and a stored procedure.

    The problem is that rebuilding the catalogs (when data changes) results in drop and re-create of the views and stored procedure removing all the permissions. Microsoft recommends replicating the data from a staging server to production to work around this (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/csvr2002/htm/cs_dp_catalogreplication_hisk.asp)

    The problem is that we need in excess of 350 virtual catalogs, which will result in around 3000 tables. These are all small tables so volume of data is not an issue.

    I was wondering if anyone has ever attempted replication on this scale, and if there is any advice (other than don't do it)!

    Thanks in anticipation.

     

  • September 9, 2004 at 6:09 am

    #522275

    I've done over 1000. It gets complex when you have to replicate a lot of data. Replicating a lot of objects is not problematic.

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

     

  • September 9, 2004 at 8:19 am

    #522322

    Thanks for your input Hilary.

    I performed some tests yesterday, I used a while loop to generate a representative sample of 2800 tables and then replicated these to another server. Publisher is a Dual processor with 2GB, and the subscriber a desktop with a single fast processor and 1GB.

    My first hurdle was that Enterprise manager would not bring up the list of tables for me to create the publication, just exited with an error, although it would on a co-workers machine. I ended up generating both publication and subscription through scripts.

    The subscription took an 1.5 hrs to create, the initial snapshot after an initial failure took 2 hrs 45 mins, but interestingly only 10 minutes to apply to the subscriber. The replicated data amounts to 280MB.

    I have replicated a bigger volume previously with less tables, and not had such length times as these. So it seems (as you might expect) that replicating a lot of schema adds considerable overhead to the process.

    Rob Blackmoor

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply