February 16, 2007 at 8:11 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. The production data is read only.
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 18, 2007 at 12:43 am
Snapshot to a different db and restore the backup of snapshot db on to the production db...
1. Snapshot replication...
2. Configure snapshot replication from ServerA.Testdb to ServerB.TestdbSnapshot
3. Backup ServerB.TestdbSnapshot
4. Restore ServerB.TestdbSnapshot backup on to ServerB.TestdbBackup db...
5. Kill all connections from ServerB.Testdb ...rename ServerB.Testdb db to ServerB.TestdbOld and rename ServerB.TestdbBackup db to ServerB.Testdb...
OR
You can configure on way merge replication...
MohammedU
Microsoft SQL Server MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply