AlwaysOn: primary sync or async?

Question

Hi, I succesfully setup an AlwaysOn between two sql2014 servers for development purposes. I configured both the primary and the secondary as synchronous. Now developers say that it is slower than one single server, and they are right because this configuration causes delays. So, I know I can set the secondary replica as asynchronous, but the wizard lets me to set also the primary as asynchronous, and I can't understand this. Honestly, when I set up the environment, I didn't focus on it but now I'm asking: why should the primary replica be async? If it is the primary (let's say the "most important") it should be sync by default. Who should be the primary async with?

Answer 1

Think of this as a contract between the Primary and it's replicas. In order for synchronous replication to take place a primary AND secondary must be set to synchronous. Having one set as synchronous is not enough for automatic failover OR to avoid data loss.

In your scenario having the current primary set to synchronous is irrelevant - you get no level of protection from that.

Are you sure the synchronous commit was causing your performance problems? Did you see HADR_SYNC_COMMIT waits?

 

Answer 2

It should be synchronous to the local replica and asynchronous to the DR site.

What may happen is that the latency with an AG can get significant which apparently is what you were seeing in your dev environment.

This should not happen on a topology with ample network bandwidth - but that been said it can happen depending on your workload.

HTH

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 3

In your scenario having the current primary set to synchronous is irrelevant - you get no level of protection from that.

Ok, this is the answer I was looking for.

But: let's say that a strange AG has only the primary replica... why is given the possibility to set is asynchronous? It makes no sense. I thought the primary should always commit synchronously, since it is the only one. The attribute sync/async should be a property of the secondary replicas, which I can decide to set sync or async based on location, performance needed and other parameters. Furthermore, the sync/async property is configured on each host, not between two hosts. Am I wrong? What am I missing?

ps. thank you for your advices about the performance, but this is not a problem at the moment, our developers are only approaching AlwaysOn :)

Answer 4

Hi Maurice

I agree that it might seem counter-intuitive that it allows you to set the primary to async and not control that purely from the secondary.

However you could think of this of an attribute of the host/replica *location* rather than of the primary/secondary role. When you have failover the sync/asyn setting stays with the host.
So imagine a DR situation where you've had to failover to an async remote site then eventually fail back to what you consider your 'primary' location. For the initial failover you only want async from that site as it's remote, however when you fail back you'll want it to be sync for HA.

In theory you could (temporarily at least) have your 'primary' in a location where sync isn't possible due to bandwidth restrictions.

Does that make sense? 

Edit:
By coincidence I noticed this when I was joining a replica on a DEV system this morning, SQL Server does actually warn you via the UI when you're joining an async primary to a "sync" secondary

Answer 5

When you run synchronously under high load you may find that you slow down the workload on the primary. In these situations you might want to run the primary using asynchronous.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Ok, so you mean that also the setting on the primary is important... but how does it work al low level? Let's imagine that only a primary replica is part of the AG, and it works in asynchronous mode; an application submits a bulk insert to the listener, instructions are written in the buffer, and then? What is the difference between this situation (AG with only primary replica in async mode) and the case we have a standalone database and submitting queries to it? What does the async mode cause comparing to the standalone database?

Answer 7

This is how is works for synchronous.

Change is written to the log buffer on the primary. Change is written to the log buffer on the secondary/replica, Acknowledgement is sent back to the primary and the app. The app can then do more writes.

For asynchronous a change is written to the log buffer on the primary. The app can then do more writes. The log buffer on the primary is read and changes are sent to the secondary log buffer.  Look at the image on this link for a more complete description of how it works.

http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/07/sql-server-2012-alwayson-what-is-it.aspx

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941