We have a co-located (web) server at an external site. Our company database (in SQL Server 7) is continuously replicated to this site, essentially allowing our web sites and company systems all to run from one data source. SQL Merge replication works great and provides us a mechanism where data captured from the web arrives almost instantaneously in our internal systems, and conversely changes to data in-house are reflected immediately on our data-driven sites.
There is a BUT! Adding fields to replicated tables is impossible whilst replication is running. Stopping replication allows changes at the publisher but SQL stops us making changes to the subscriber. We end up deleting the replication job completely, deleting the subscriber database and re-replicating the whole thing from scratch. This is not a huge problem at the moment because we have a good connection to our web server (DSL) and the size of the database is not huge. Over time though, the database could increase greatly in size and re-replicating may become impractical - bear in mind our web sites have to be stopped whilst this operation is completing. Naturally we would like to limit 'downtime' on our sites to minutes at most - going down for an hour or more is unacceptable.
Does anyone have any step-by-step ideas on at least a better way of tackling this problem? Any alternative to re-copying the entire database would be great, and I'm sure there's a better solution out there.
There is a BUT! Adding fields to replicated tables is impossible whilst replication is running. Stopping replication allows changes at the publisher but SQL stops us making changes to the subscriber. We end up deleting the replication job completely, deleting the subscriber database and re-replicating the whole thing from scratch. This is not a huge problem at the moment because we have a good connection to our web server (DSL) and the size of the database is not huge. Over time though, the database could increase greatly in size and re-replicating may become impractical - bear in mind our web sites have to be stopped whilst this operation is completing. Naturally we would like to limit 'downtime' on our sites to minutes at most - going down for an hour or more is unacceptable.
Does anyone have any step-by-step ideas on at least a better way of tackling this problem? Any alternative to re-copying the entire database would be great, and I'm sure there's a better solution out there.