AlwaysOn Basic Availability Groups provide a high availability solution for SQL Server 2016 Standard Edition. This new functionality will replace database mirroring, which is a deprecated feature. Basic Availability Groups are managed and created in the same way like the traditional Enterprise AlwaysOn Availability Group, but there are more limitations which are almost equally when you should run Database Mirroring on a Standard Edition.
Limitations
Basic availability groups use a subset of features compared to advanced availability groups on SQL Server 2016 Enterprise Edition. Basic availability groups include the following limitations:
- Limit of two replicas (primary and secondary).
- No read access on secondary replica.
- No backups on secondary replica.
- No integrity checks on secondary replicas.
- No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).
- Support for one availability database.
- Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.
- Basic availability groups are only supported for Standard Edition servers.
- Basic availability groups can not be part of a distributed availability group.
Prerequisites
The prerequisites are the same as for a traditional Enterprise AlwaysOn Availability Group. You still need to Create a Windows Failover Cluster and Enable AlwaysOn Availability Groups on each instance.
Storage for Both servers will be unique
Shared folder need to be created to add Database in AG.
Windows Fail over CLUSTERING FEATURE TO BOTH WINDOWS SERVERS.
SQL Server’s AlwaysOn Availability Groups is built on top of Windows Failover Clustering Services, and you have to join your AG replicas into a single Windows cluster.
this is nowhere near as scary as it sounds, and it can be done after-the-fact to an already-in-production SQL Server. (I’d recommend you set up a lab or staging environment first to get used to it, though.)
After installing those clustering prerequisites, open Server Manager and click Manage, Add Roles and Features, select your server, and on the Features list, check the Failover Clustering box:
You can move on to the next step after this feature has been installed on all of the servers that will be involved in the Availability Group.
Validate your cluster candidates.
Open Server Manager and click Tools, Failover Cluster Manager. (I know – you thought Failover Cluster Manager would be under Manage instead of Tools. Click Validate Configuration on the right hand side to start the wizard:
Put in the names of all the servers that will be participating in the Availability Group, and hit Next. It’s going to ask if you want to run all tests – this is another one of those gotchas where, in theory, you could do this in production without an outage if you chose to run specific tests.
Click the View Report button, and Internet Explorer will launch because why wouldn’t we take every opportunity to open a browser on a production server:
Some errors are completely okay to ignore. The most common two are:
- Single network interface warnings – if you’re doing network teaming or virtualization, you’re probably protecting your network through means that the clustering wizard can’t detect.
- No disks found for cluster validation – if you’re building an AlwaysOn configuration with no shared disk, like failover clustering on a UNC path or a pair of Availability Group replicas with local disk, this isn’t a problem.
But otherwise, read this report really carefully and fix the warnings before you go on to the next step. For example, if the two nodes have different Windows patch levels, get the missing updates installed first. (If you’re having difficulty getting some of the patches to apply, there’s your first sign that this isn’t a good candidate for a reliable AlwaysOn implementation
Create the Cluster.
After a successful validation report, leave the “Create the cluster now” checkbox checked, and click Finish – your cluster will be created:
Permissions (Windows System)
To administer a WSFC, the user must be a system administrator on every cluster node.
Install SQL server 2016 standard edition on both standalone server (windows server 2016 )
and enable always on feature for both standalone instances
Please make sure SQL services should be running with unique domian account on Both servers
Once always on feature enabled we can see AG option in management studio.
