[Note from Pinal]: In this episode of the Notes from the Field series database expert Mike Lawell talks about his thoughts and observation about SQL Server 2016 and enhancements with AlwaysOn Availability Groups. When AlwaysOn was newly introduced, I was not able to catch up with the feature. I just thought it was just one of the subject which will not be developed by Microsoft, however, as the new versions of the SQL Server are now taking this feature to the next level, I am very delighted that I should have not worried at all. Let us see what Mike says in his own words about Enhancements with AlwaysOn Availability Groups.
In the past several blogs I’ve written, I’ve been highlighting new features in SQL Server 2016. This time I’m going to talk about improvements in an existing that I use frequently for clients.
I’m a consultant that does a lot of high availability implementations, and in several cases we were unable to implement AlwaysOn Availability Groups do to a limitation that was incompatible with Distributed Transaction Coordinator (DTC). Many times a client has no idea they’re using DTC in their application, and during implementation I run a few tests that identify the DTC transactions. Bang, blindsided by DTC, and now the client has to completely reevaluate the implementation. One case they had to put the implementation on hold indefinitely.
Soon, when SQL Server 2016 is released, the implementation can finally be taken off hold and get back on track with Always On Availability Groups (AGs). Now DTC is supported by AGs!
Another really awesome improvement is an increase in the number of auto-failover targets. I have a client who has a multi-site configuration where two replicas are in the same data center and two replicas are in another data center for disaster recovery.
The decision was made to put the auto-failover replica in the disaster recovery data center in case the local data center experienced an event. If the local data center experienced an event and it was the auto-failover replica, an auto-failover would not occur. Kapow! SQL Server 2016 to the rescue… now I can have to auto-failover replicas, one in each data center so that if the primary data center goes down, an auto-failover to the disaster recovery site happens.
But wait, there’s more… Microsoft also introduced round-robin load balancing for readable secondaries. Now I can load balance the reads across multiple secondaries. It doesn’t get any better than that!
Or does it… how about Domain Independent Availability Groups. What? You say? Well, do you still use mirroring because you are limited to your mirror being on a different domain (or no domain) than your principal? Now there is a resolution… you can have replicas in different domains. And in Enterprise Edition, you can have more than one replica! It just doesn’t get better than that… or does it!
Folks, you’re not going to believe this one… it’s too good to be true… but here it is… for the same price as all of the above…. You get enhanced log replication throughput and redo speed. Yes, for the same price… you get faster replication to secondaries.
If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
when i try to open sql server 2012 then error occured ‘the operation could not be completed”. Please help me! I don’t know how to solve.
Hi,
As per the business requirement, i need to copy datas from AlwaysOn secondary replica AG database to non AG database through agent job. By default agent account cannot use the parameter “ApplicationIntent=Readonly”. Anyone please advice how to read the datas from secondary replica.
Thanks,
ManoShankar
Pinal,
we have implemented the load balance between one primary & one secondary
but when we tried to test the load balance using the SQLCMD , unable to route ,when check the read only routing it shows
ALTER AVAILABILITY GROUP [AG]
MODIFY REPLICA ON
N’SERVER2′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG]
MODIFY REPLICA ON
N’SERVER2′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://test1.abc.com:5022′));
ALTER AVAILABILITY GROUP [AG]
MODIFY REPLICA ON
N’SERVER1′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG]
MODIFY REPLICA ON
N’SERVER1′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://test.abc.com:5022′));
ALTER AVAILABILITY GROUP [AG]
MODIFY REPLICA ON
N’SERVER2′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SERVER1′,’SERVER2′)));
ALTER AVAILABILITY GROUP [AG]
MODIFY REPLICA ON
N’SERVER1’ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SERVER2′,’SERVER1’)));
GO
Settings for readable secondary
Primary : Read-intent
Secondary – yes
Can you please help me
Thanks
Santosh
Routing URL should be server FQDN and listening port combination. Based on server name you have given, it should be..
READ_ONLY_ROUTING_URL = N’TCP://SERVER1:1433′));
looks like you have given some random URL.