Sql-server – SQL Server Replication questions

replicationsql serversql-server-2012

This is kind of the part 2 of Replicating a SQL Server database & Optimization that I started earlier this week, but I believe it owes to be a different questions now that I have a clear idea of what we are trying to achieve.

Currently we have one box running our application and database server. We about to increase our capacity to two servers. One on DC1 and the other on DC2. Both will have our application server and database running.

What we need:

  1. Not have boxes sitting around doing nothing waiting for a failure.

  2. Automatic fail over. We don't want to be running like headless chickens flipping switches and configuration settings when a database / box fails

  3. Sensible fail-over times (e.g. a five minute fail over is not sustainable)

We can sustain some (sensible) permanent data loss in certain areas of our application. Anything up to a few seconds is fine. If its temporary loss e.g. pointing to an db image valid ten minutes ago is ok as long as the data recorded within those ten minutes make their way to their database at some point.

OPTION 1

  • Point app server to local db. If local db is down then point to remote db. e.g. App Server A points to DB A. App Server B points to DB B. If DB A goes offline then App Server A points to DB B. If Box A goes offline then Box B continues as it is.

Reasons we like this: Does really well on criteria A, B and C

OPTION 2

  • Application servers are pointed to one combined DB. This is the combination of DB in box A and DB in box B. No sweat if one database fails.

Reasons we like this: Does well on criteria A, B and C

But:

  • Disadvantage is that we get Application Server A pointing to box B so increased latency

OPTION 3

  • Application servers are pointed to one DB, say on box A. So application server A points to DB A and application server B is pointing to DB A. We use database B for read only reporting. On DB A failure applications are automatically switching and point to DB B which is now used for everything, not just reporting.

Reasons we like this: Still does well on criteria A, B and C

What I would like to know is how are the options above implemented with a SQL Server 2012 Enterprise. Mind that the two boxes are on different data centers but IF needed we can change that. Also, if there is a better option that we haven't thought about.

Databases image size is currently in excess of 150GB. There are some very frequently updated tables (the analytics tables are inserted sometimes 50-100 times a second).

Let me know what you think

Thanks,
Yannis

Best Answer

IN SQL Server 2012 Enterprise edition, you can use AlwaysON Availability groups to achieve what you want.

If you have 2 servers set up, then you can configure AlwaysON.

Primary server will be your main and secondary (Replica) - when configured as "Readable" will allow you to redirect all "Read Intent" requests to secondary replica as well as the secondary replica is a read-only replica.

Note that, your clients are connected to the Availability Group using Listener, which allows seamless failover for your application.

In case of disaster you have all your concerns covered

A. Not have boxes sitting around doing nothing waiting for a failure.

The secondary will be used for read intent requests and will server as a standby server in case the primary fails.

B. Automatic fail over. We don't want to be running like headless chickens flipping switches and configuration settings when a database / box fails

When you use synchronous mode, it will allow you for automatic failover. In my testing, asynchronous mode (without automatic failover - manual failover) has proven more benefits in terms of performance. SO depending on the data loss allowed by your company and the downtime, you can choose between them.

AlwaysON allows you to Configure Flexible Failover Policy to Control Conditions for Automatic Failover

C. Sensible fail-over times (e.g. a five minute fail over is not sustainable)

AlwaysON will provide failover in terms of seconds, but it depends on your n/w.

EDIT :

If you implement AlwaysON (which fits in your situation), below is my recommendation:

Assuming (from your post) DB1 is the main database and DB2 is for reporting.

Configure DB1 as primary in AlwaysON. You get a warm standby database on the secondary server (say DB1_Replica).

Configure DB1_Replica as readable and have asynchronous-Commit mode configured.

Under asynchronous-commit mode, the secondary replica never becomes synchronized with the primary replica. Though a given secondary database might catch up to the corresponding primary database, any secondary database could lag behind at any point. Asynchronous-commit mode can be useful in a disaster-recovery scenario in which the primary replica and the secondary replica are separated by a significant distance and where you do not want small errors to impact the primary replica or in or situations where performance is more important than synchronized data protection. Furthermore, since the primary replica does not wait for acknowledgements from the secondary replica, problems on the secondary replica never impact the primary replica.

The listener is going to help you with seamless failover.

Refer to below links to get you started :

Related Question