At MS Ignite this week, several details were revealed about the changes in Availability Groups that will ship in SQL Server 2016. I wanted to provide a very quick list of bullets on the major highlights at a high level, to get you excited about these AG enhancements:
- Optional setting to fail over based on database failure – in 2012 and 2014, failover is determined almost entirely at the instance level. If a database goes offline, suspect, or corrupt, the AG keeps humming along. In SQL Server 2016, you will be able to have certain database metrics to initiate failover for the entire group.
- Distributed Transaction Coordinator support – in current versions, MSDTC is not supported for AG databases, but it will be fully supported in SQL Server 2016 (it will require an operating system update as well – it is possible that you will need the most recent version of Windows Server for full support across all scenarios).
- Group Managed Service Accounts are fully supported – these "worked" in SQL Server 2012/2014, but were not fully supported, and had some issues (see background information here, here, and here).
- Load Balancing for Readable Secondaries – you will be able to use a round-robin mechanism for routing read-only requests through the listener to take balanced advantage of all secondaries, versus the current approach of requests always going to the "first" available secondary.
- Additional automatic failover targets – you'll be able to specify up to three total secondaries for automatic failover; this matches the number of synchronous replicas allowed.
- Improved log transport performance – this entire pipeline was overhauled and refactored for lower CPU usage and higher throughput.
- Basic Availability Group – this has finally been confirmed as of CTP 3.2 to be an official option for Standard Edition customers in SQL Server 2016. For feature details and limitations, see Overview of AlwaysOn Basic Availability Groups .
- Domainless Availability Groups – as Microsoft has described here, you will be able to host AGs across domains with no trust and with no domain at all. (Note that this change requires Windows Server 2016 as well.)
I love the face that AGs will finally be supported with standard edition. With that said, I really don't like that only a single database can reside in an availability group.
It's a great news!!. Does it require two node windows cluster and supports on multi-subnet? or work as sql mirroring before with new name in SQL 2016.
Well, John, they can't give you everything. And really, having multiple databases in a single group is mostly just a convenience thing; they're not guaranteed to be transactionally consistent to the same single point in time in the event of a failover anyway. So let's say you have three databases either in a single group or 3 separate groups – since most failovers will continue to be due to instance-level events rather than database-level events, all three databases will fail over in the same way in both configs.
Ashok yes, it will still require an underlying WSFC, and I am not sure if multi-subnet will be supported in Standard.
Good point, it is nice to see the Async is included. That is a really nice bonus.
Thanks Aaron. Is SQL Mirroring completely removed in sql 2016 or 2018?
No announcements on that as far as I know. Typically (with the notable exception of Notification Services), deprecation announcements mean that the feature *might* be removed no earlier than three full releases. So I suspect that 2016 and the next version after that will be safe. But that is just me speaking as a casual, outside observer.
I do not know of any removal of database mirroring yet, for SQL 2016. I'll ask internally.
Cannot add dns listener (feature) to this SQL server version. Is this correct (intended)?
Evert, sorry, it's not clear to me – where exactly are you trying to add this and what is the exact error message you're receiving? Have you confirmed the exact same scenario / code is supported in 2012/2014? I don't know of any feature they took away…
Aaron,
Need to clarify; just adding a basic availability group does not provide for the option to add a dns listener. But when creating an availability group with database level health and dtc support checked along with the basic setup does give me the abillity to configure a dns listener. So, for now I'm still doing fine.. Need MSDTC support
Aaron,
Error appeared after configuring a 'basic' availability group, and basic only. When creating a basic availability group with database level health check and/or dtc support a dns listener can be added. Error message "dns listener not supported by this version of SQL server"
Evert Jan
The db mirroring supports mirroring across different domains – which AG doesn't support since AG uses windows clustering which doesn't work across domains. So I would say mirroring can't be deprecated that soon without a viable alternative for the above scenario. We have provided this feedback to the product group several times.
Take a look of this new feature in Windows Server vNext
https://technet.microsoft.com/en-us/library/dn765474.aspx#BKMK_multidomainclusters
Hi Aaron. How would it work to have three secondaries for automatic failover? Would there be a preferred secondary?
I imagine possibly something like the read-only routing list for readable secondaries – it would try the next one in the list (and you could define the order of the list). However, that was a bullet on a slide deck in May, and what we end up getting in RTM may be completely different.
I'm the Program Manager behind AlwaysOn. Here are answers to your questions:
@Evert – We removed the Listener feature from Basic Availability Group (SQL Standard), we'll undo this change, and support Listeners with Basic Availability Groups in the next CTP (December)
@Mike – Availability Groups can now be created across different domains, or even without domains, using Windows Server 2016 (Windows Cluster now allows this by authenticating nodes using certificates)
@Howard – The priority of auto-failover targets is defined based on the order in which these replicas are added to the Availability Group (via T-SQL or in SSMS). Unfortunately we don't yet support changing the priority in SQL Server, you'll need to do change the priority of the replicas (possible owners) in Windows Cluster.
@Aaron – Multi-subnet failover will be supported in Basic AGs (SQL Standard). BTW, with the latest .NET driver you don't need to specify "MultiSubnetFailover=true" (it automatically parallelizes connections to the Listener IPs in the different subnets)
@Ashok – Database Mirroring is still in SQL 2016. It'll be removed in the following version (it was announced as deprecated in SQL 2012)
Regarding the item 1 in the bullet points:
I thought that the failover unit was at the HA group level, not at the instance in SQL 2012…. was I wrong?
Well, yes and no. Failover for *all* of the AGs in an instance is determined by instance-level health in SQL Server 2012 and 2014. There is no Availability Group level in terms of health or failover triggers. That's what is being added in 2016, the ability to react to certain database-level indicators to fail over a single AG, rather than fail over *all* AGs on an instance.
Can we have some Microsoft documentation that says what to put the Recovery properties at for a SQL service (SQL 2012) that is utilizing AlwaysOn for “First failure”, “Second failure”, and “Subsequent failures”
I think you're asking this in the wrong place – we don't write or publish Microsoft documentation, nor do we have any say over what information they provide in their documentation.
@Luis,
I am configuring Basic Avail. Group with CTP 3.2 and the option of a listener is still not available. Can you confirm that support is still planned? Also, I understand that a BAG only supports a single database but are you aware of any limitations (or drawbacks is possible) of configuring multiple BAG's on the same primary/secondary replica?
Thanks!
Is it a hard set limit that you can only use Always On for 1 Database in 2016 standard?
or is it a case that it's 1 Availability group with multiple databases?
if it's a limit to 1 DB in Std then it's a really crappy license restriction to enforce.
Fair enough having a DB limit but 1 is a bit extreme, 3 would be fairer.
Well, the point is that it serves as a replacement for database mirroring, with a lot of the other benefits of Availability Groups. You couldn't group databases together in database mirroring, even with Enterprise Edition, either. If multiple databases in a group is really important to you (not sure why, because they're not guaranteed to be transactionally consistent together anyway), you'll have to pony up, sorry.
"Additional automatic failover targets – you'll be able to specify up to three total secondaries for automatic failover; this matches the number of synchronous replicas allowed."
Any more information on this? I've looked through the CTPs and while I do see three synchronous replicas, it looks like there will only be 2 that can be configured for automatic failover.
Howard, this is what I have been told privately and that's as much as I have been allowed to relay publicly (probably because it's not set in stone and may change). You'll have to wait for official documentation or blog posts from the team if you want more info (it may come in CTP 3.3, which should be shipping soon).
Aaron, thanks for the reply. I figured that the SQL Server 2016 features were already set in stone. Interesting that there may be more announcements to come.
Howard, from the CTP 2.0 docs (you'll need to search this page for this phrase]:
That text does not mention sync/async directly, but that does match the number of sync replicas allowed. There are probably areas in the documentation that haven't caught up; such is the nature of betas/CTPs.
Synchronous connections would all support automatic failover because they are synchronous but currently the CTP and public writing from Microsoft do not hint at any additional automatic failover replicas. It seems at least for now that only two replicas can be set to automatic failover. This late in the development I'd be surprised to see such a big feature with no press or functionality built into the current CTP. I hope I'm wrong because a failover order for AG like they have for FCI would be great!
Howard, I'm not sure why you say no public writing from Microsoft hints at this; the documentation I referenced above says explicitly that SQL Server 2016 will support three automatic failover replicas.
Also, don't take lack of functionality in CTPs that are already public as any indication of final release. Features that you haven't seen yet are still shipping, believe me.
Just read Luis's post below. That's exciting to have multiple automatic failover targets even if the functionality to prioritize them is not yet available. In an earlier CTP, it seemed I could only check two replicas for automatic failover. Maybe that is expanded in a future preview?
my gripe with it being a single DB is say in the case you have a pretty basic Website that uses SQL for authentication.
In this case you would have 2 DB's, 1 for credentials and the other DB would be for the Data.
If the server failed both DB's would fail over and ok you might loose some data but the Site would be up.
I get why for a license reason they need to have a break point between Std and Ent but a single DB seems a bit restrictive.
Especially since its is a passive copy and not like Enterprise which is active active.
Not sure I follow your gripe.
1. You know that you will be able to have multiple availability groups, right? Even though each "group" is limited to one database each? This should cover your scenario just fine.
2. You also know that each database maintains its own LSN, which means it is possible now in existing versions and Enterprise Edition for two databases in the same AG to not be transactionally consistent to the exact same point in time in the event of a failover? So the two DBs being out of sync is already something that's theoretically possible even for two databases in the same AG in Enterprise Edition. The group is not some kind of guarantee that all databases are consistent, it is just a container for convenience.
3. Given that, other than the convenience of only having one AG name to manage, what do you plan to gain from having an AG in Standard Edition with more than one database? Remember, too, that this is a replacement for database mirroring (which has absolutely no concept of groups), not adding an Enterprise feature to Standard Edition.
My only gripe is that the feature in standard edition should just be called
With the release yesterday of CTP 3.3, I immediately tried out the "additional automatic failover targets" that Aaron referenced and Luis confirmed. I'm very happy to say that the functionality is now included in CTP 3.3.
The AG creation wizard still states "Automatic Failover (Up to 2) / Synchronous Commit (Up to 3)" but it did let me check all three of the synchronous replicas for Automatic Failover.
After the AG creation completed, I opened the AG dashboard and it listed all three replicas for automatic Failover.
Test:
I restarted SERVER1 and the listener automatically moved to SERVER2. When SERVER1 came back online, I restarted SERVER2 and the listener returned to SERVER1. I then restarted SERVER2 and SERVER1 together and as hoped, the listener automatically moved to SERVER3. As Luis stated, the failover order can be configured through the Preferred Owner of the role in Failover Cluster Manager.
Yes, CTP3 supports 3 automatic failover replicas.
BTW, There are a couple of final features coming soon:
– Loosely-coupled AGs: Allow synchronizing replicas in different Windows Clusters
– Streaming seeding: Allow seeding a secondary by streaming the log, instead of backup/restore.
Sorry about the stale documentation, we'll be updating it this month.
Write your feedback/questions in this blog.
The lack of Listener in BAG was a bug.
It's fixed in CTP3.3.
No aware of issues due to hosting multiple BAGs (primary/secondary)
Aaron,
thanks for clearing up some of that. I had misunderstood the 1 DB limit and the way availability groups would be done in Std edition.
I will have to get playing with CTP 3.3 and see if this will work with some of the internal apps we have in the company.
Merci Beacoup
Hello, I am the new Engineer for the Always On Tools experience.
Just one small clarification, listener support for Basic AGs will be in RC0, not CTP3.3. We were not able to get all of the changes completed in time for the CTP 3.3 cutoff.
Sorry for any confusion.
I have filed a work item to address the confusion in the AG wizard. Thank you.
This fix has been approved for the RC0 release of SQL 2016.
@luis/@Dom,
It appears that RC1 does not have the option in the AG Wizard to create a Basic Avail. Group (which existed in the CTP releases) and the Standard Edition is not available to install. How can I test Basic Availability Groups with RC1?
Thanks
The option is no longer provided, but defaults automatically for Standard Edition Servers. Basic AGs are no longer available for editions other than Standard.
I cannot speak to the availability of Standard Edition Servers for the RC, but the behavior you are experiencing is the new experience for BAG with other editions of SQL Server.
@Dom. I definitely see the need for the change but unfortunately neither the evaluation or MSDN downloads are being offered in anything but evaluation edition which is developer/enterprise thus, we are unable to actually test the Basic Availability Group feature. I specifically have been waiting for the listener bug to be fixed which I cannot test as the required edition is not available to test. Thanks..
I just added a listener to a BAG configuration. In fact I added a 2nd BAG and added a listener for this BAG also
Hi reading through these comments have been very helpful. Can someone clarify that automatic fail over is possible with two standalone SQL 2016 Standard Edition Servers, without shared storage. These would not be a Cluster with shared storage. Is automatic fail over possible with the new added AG feature in Standard Edt, and is automatic fail over possible with Mirroring in Standard Edition?