Sql-server – Disaster recovery plan – Having multiple servers

data-warehouserestoresql server

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?

Best Answer

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.

Related Solutions

SQL Server – Aggregate ID Pairs into Groups

Well this worked for the given sample data but not the cycles scenario / may not work for a more complex data set. Thought I would post anyway as a starter for 10:

DECLARE @t TABLE ( id1 INT, id2 INT )

INSERT INTO @t VALUES
    ( 1, 2 ),
    ( 1, 3 ),
    ( 3, 4 ),
    ( 3, 5 ),
    ( 6, 7 )

    --( 1, 2 ),
    --( 2, 3 ),
    --( 3, 1 )

;WITH cte AS
(
SELECT ROW_NUMBER() OVER( ORDER BY id1 ) group_id, id1 id 
FROM @t a
    CROSS APPLY ( SELECT TOP 1 id2 FROM @t c WHERE a.id1 = c.id1 ) d 
WHERE NOT EXISTS ( SELECT * FROM @t b WHERE a.id1 = b.id2 )
  AND a.id2 = d.id2
UNION ALL
SELECT group_id, t.id2
FROM cte c
    INNER JOIN @t t ON c.id = t.id1
)
SELECT *
FROM cte
ORDER BY 1, 2

SQL Server – How to Replicate Database Over Two Geographical Sites

Based on the answers in the comment section, you are looking at a bidirectional transactional replication.

Microsoft document its implementation at this location: https://support.microsoft.com/en-us/kb/820675

Using the name "Bidirectional Transactional Replication" in your research should help you identify how-tos and step by step tutorials.

Since it's bidirectional replication, you will experience conflicts (a row updated here and there at the same time).

The document explains all of that very well. It's a good start.

Best Answer

Related Solutions

SQL Server – Aggregate ID Pairs into Groups

SQL Server – How to Replicate Database Over Two Geographical Sites

Related Question