Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aliziad
Starting Member
2 Posts |
 Posted - 2008-01-02 : 13:24:22
|
| Greetings, I am building a system that requires a small database to be replicated. The DB is made up of a few tables that do not change much, primarily by user-input or triggers on those tables, and one constantly growing table for incoming messages with rows constantly being added to it. We require having two systems (System=Hardware&OS/SQLDB/Application) running in parallel where one could act as a failover to the primary system. Both systems would be run in the same site and have a direct network link.My goal is to have both systems maintain identical databases and to be able to "re-synchronize their data after a failure in some recovery state. I am looking at: 1. SQL Server Replication Methods (Publishers/Subscribers) A possible scenario i need to support: ServerA: primary server acts as publisher ServerB: secondary failover server acts as subscriber ServerA: Fails! ServerB: Assumes role of primary server (i.e Publisher) ServerA: recovers later, becomes subscriber to ServerB(secondary) Q: Can Roles Be Switched or A server be a subscriber and a publisher at the same time? 2. Use ServiceBroker Queues and Have Each server SEND a message to the other server whenever it gets incoming data? 3. Use Transact-SQL in ServerA to write data to ServerA and ServerB and vice versa. 4. Not worth replication, just have the application write data to both servers in two seperate requests.Which is the preferred way to accomplish this?Thanks,-ali |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-02 : 15:57:21
|
| Replication is not an automatic failover option. You need to change your code to point to the new server (subscriber) if the primary server fails. And reversing the roles of publisher-subscriber is not automatic either. You need to disable replication, re set it up to go the other way.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-01-02 : 16:03:22
|
| I'd use synchronous database mirroring with automatic failover instead of replication. If you want both databases to be up, then I'd use peer-to-peer replication.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
aliziad
Starting Member
2 Posts |
Posted - 2008-01-02 : 16:52:27
|
| Thanks Tara, the mirroring option appears to be a good fit, i will look further into it. Also, by Peer-To-Peer Replication, do you refer to the publisher/subscriber model in SqlServer between two peers OR to a case where one storedProc writes data to the other server, via a Server link or a second connection created in the StoredProc's code, etc?Dinkar, I never said or assumed anything would be automatic :) merely inquiring about SQL Server data replication options suitable for my usage scenario. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-01-02 : 16:55:30
|
| Peer-to-peer is a type of transactional replication. You can read about it in SQL Server Books Online. I believe it's available in Enterprise Edition only.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Simple Replication