20,000 ft. directional question about technologies and capabilities
Greetings,
My desire is to have my primary db in FL and an online hot standby in both my MI and CA offices. The hardware is in place, as well as T1 size pipes. The solution I'm looking for would have all databases online in case of hardware / environmental failure although the application would only be pointing at the primary (or designated) database server. We have another 3rd party solution available, but I'd like to stay native SQL if possible.
Is replication the way to go?
Does one-to-many work?
Can all the db's be online and available while replicating?
Can I catch up if switched to one of the other db's temporarily?
note:This is 100% SQL 2005.
Thanks -- sorry for the newbie-flavor of the question, but I'm sure a gazillion people have already been down this decision branch.
Dan Ribar
[860 byte] By [
DanRibar] at [2007-12-26]
Hi Dan
SQL 2005 has a new type of replication called Peer-Peer Replication where you can have many active nodes. Updates can be made at any node. The other servers are available and can be used almost instantly in case of failure of the primary server. As is the case with any solution, there are some pros and cons with this one too. Whether its particularly suitable in your case or not, only you can decide. Check out BOL for more info. They have listed out all the pros/cons clearly.
OK -- new direction. P2P looks great, but on our class servers it's about a $40,000 license upgrade to SQL (standard to Enterprise).
Any other ideas that might not be so costly?
Thanks in advance.
Dan Ribar
If you only have two servers, you might want to take a look at bidirectional transactional replication. It only works with the topology of two nodes and you have to use T-SQL to setup. For more information, see this link http://msdn2.microsoft.com/en-us/library/ms151855.aspx.
Actually, if you never do update on the second server, the regular (one-way) transactional replication should just fit your need.
Peng
