Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I work in an organization where departments are in different buildings. I have a data centre in the main building. All the in-house applications connect to the SQL Server residing in the server in the main building datacenter.

Sometimes during weekends, if the network fails or the software throws a bug, it is very hard to come back to office and fix the problem.
It was suggested that a local machine be placed in one building (other than the datacenter) and install SQL Server on it. Have this SQL Server store the data first, and then replicate it on main datacenter every few minutes.

This will help in case the network gets disconnected with the main datacenter.

I would like to know if this the only solution we can adopt? What problems would we face with this strategy?

share|improve this question
2  
This doesn't sound like a "disaster recovery plan issue". Your database in the main building will be fine, you can back it up or replicate, regardless the network status. This is more an "accessibility" issue –  Carol Baker West Feb 11 '13 at 13:58

2 Answers 2

up vote 3 down vote accepted

It sounds like what you're talking about is more aligned with High Availability.

If you are already on SQL Server 2012, or you have the ability to upgrade then it sounds like AlwaysOn Availability Groups could be right up your alley. This is a technology that will allow you to create a cluster and you could have two geographically separate servers (i.e. your current database server and your remote database server that you're planning on standing up). If the network latency isn't a performance impact between the two servers you could set up a two node cluster and have each of those nodes hosting a replica of the Availability Group (or Groups). With synchronous commit, you could configure automatic failover when there is a network outage or some other service-hindering event.

By creating an Availability Group Listener you could have the client applications connecting to the listener which will direct traffic to the current primary replica.

If you don't have the ability to get onto SQL Server 2012 to leverage AlwaysOn Availability Groups, then Database Mirroring could also be another high availability solution for you. If the session is operating synchronously with a witness server, you could also have the ability for automatic failover in the event of the principal becoming disconnected.

The reason I am recommending these two is two-fold: replication is pushing out articles, and it sounds like you want your entire database (or databases) to be ready for a failover. Traditional failover clusters could also be another solution but you would have to also consider shared storage between the two nodes. That might get complicated with the geographically separate servers.

if the network fails or the software throws a bug, it is very hard to come back to office and fix the problem.

What do you mean by "the software throws a bug"? There will need to be the proper error handling and error recovery in order to ensure this type of behavior doesn't cause an outage.

share|improve this answer

This really doesn't sound like HA to me. The database is still up, there's just a network problem between them. HA on the network needs to be looked into.

You could resolve this on the SQL Server side of things by using either merge replication or peer to peer replication between the two SQL Servers.

I would start by resolving the network issues and the software bugs before I started working on things from the SQL Server side.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.