February 16, 2007 at 8:05 am
We have test, staging and production SQL Server 2000 environments. The production envrironment is spread out among 15 call centers, some of which are in India.
Twice a week, we migrate data and once a month we may make schema changes.
Currently, we use a DTS package to move data from test to staging. A snapshot replication job runs nightly to synch the staging database with the 15 call center databases.
We occasionally have problems with the replication jobs. The entire 300 MB database gets replicated and on some of the WAN links this can take over an hour. If there is a network problem while the replication job is running, we'll end up with an empty database and a production outage.
We are about to move to SQL 2005 and I was wondering if there is a better way to go about this. I'm considering SSIS, log shipping, and replication to keep the call center databases in synch.
February 16, 2007 at 9:07 am
Here's another idea I had: Use Visual Studio for Database Professionals to generate schema and data change scripts. The scripts could be applied with a SQL Agent job.
March 21, 2007 at 3:08 pm
We have databases getting on for a TerraByte and have to be very careful with replication.
If you generate a snapshot then while the snapshot is being generated the tables get locked.....for a very long time.....and at £30K per minute downtime that isn't nice.
What we do is script the article and subscriptions using the nosync option and don't run the snapshot.
It means that schema changes have to be applied manually and the replication stored procs need to be edited manually but it stops replication trying to create a 1TB snapshot.
March 22, 2007 at 9:31 am
How about using transactional replication instead. Initialise from a backup instead of a snapshot and that way you avoid the overhead on the SQL Server while the snapshot is being generated/applied. It also avoids truncating/ locking tables while replication takes place.
Kindest Regards,
Frank Bazan
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply