October 30, 2002 at 6:43 am
I recently had a SQL server break on me and have now been looking at replication avoid the long down times. I have two (at the moment) SQL servers each running seperate db's. The clients connect to the relavent server using our in-house Visual Basic programs. If perhaps one of these SQL servers were to go down (hardware or software error) could you suggest a way of keeping everything functioning. Maybe replicating the data to and from each server every 15 mins or so? Do this via Snapshot or some other way? Store all data on a main SQL server and replicate to seperate backup server and swap if the main server dies? what kind of overheads to the main server would there be to keep both sets of data accurate to within 15 mins??
Hope you can suggest some ideas.
Thanks
Paul
October 30, 2002 at 8:12 am
We've had several good threads on here about log shipping vs replication, worth reading up on. Overhead depends on transaction volume, network speed, etc. Not a lot of overhead usually.
Andy
October 31, 2002 at 6:55 am
We have had great success with Transactional Replication. There is only about a 10-15 second delay in delivering and posting database changes. We are keeping 3 servers in sync with a central server. Each of the 3 subscribers are using pull subscriptions. The speed is very good as well, I have seen it deliver 700,000 commands in less than 15 minutes. I sure more is possible. I would recommend letting the SQL server generate the initial snapshot and posting the data to the destination server, unless the size of the database is large (over 5 GB for example). I have let the server generate the snapshot on a 30GB database, but this takes about 6-8 hours, and it's logged, so you need a transaction log at least the size of the DB. The advantage to letting SQL server create and post the snapshot is that it will also create all the needed stored procedures on the subscription machine(s) automatically. IMPORTANT: The snapshot agent will REPLACE (i.e. DROP/CREATE) tables on the subscription machine if they already exist there and they are involved in replication.
Good Luck...
-Dan
-Dan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply