Looking to consolidate several different older SQL serves into one AG with an ent lic when Sql Server 2016 is released. Plan is to have a primary, a sync passive secondary, and an async third instance used for reporting. That third instance I want to configure using a Std lic w/CALs to save on costs. Any issue with mixing Ent and Std in the same Availability group?
There are 2 High Availability types:
- Advanced HA on Enterprise edition
- Standard or Basic HA on Standard edition (or BI edition)
Capabilities, prerequisites and restrictions
You can look for SQL Server 2014 Capabilities at the top of page 5: SQL Server 2014 Licensing Guide.
If you look at the 2nd row from the bottom of the Compare edition table (SQL Server 2016) on SQL Server Editions page, only the Enterprise edition includes this:
Advanced high availability (AlwaysOn, multiple, active secondaries; multi-site, geo-clustering)
If you look at Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) you will find a similar restriction among others:
Each server instance must be running the Enterprise Edition of SQL Server 2016.
Since you are consolidating several instances and servers, you should also look at other restrictions such as collation, etc.
SQL Server Standard
Now using Standard edition with only a Basic High Availability group is posible with some of the limitations found on Overview of AlwaysOn Basic Availability Groups (SQL Server):
- Limit of two replicas (primary and secondary).
- No read access on secondary replica.
- No backups on secondary replica.
- Support for one availability database.
Some of these restrictions are ~similar~ to the deprecated mirroring functionnalities.
Consolidation with Standard licence
Because you are consolidating servers and instances, you obviously have several databases.
You could create X Availability Groups for your X databases with a single database in each AG. However you will have to create them using the
WITH BASIC option. You will then fall under the other restrictions of Basic AG and you won't be able to use more than 2 replicas, hence no instance 3.
Therefore a Standard licence does not seem to be an option in your case.
HA aside, if Instance 1 is using a single Enterprise option, then an Enterprise licence is needed on all 3 instances.
Please note that since SQL Server 2016 is not yet available, some of the links and information are based on SQL Server 2014 licencing model.
Instance 1 Licence:
This is your main instance with several databases. It requieres an Enterprise licence.
Instance 2 Licence:
- If this instance is really passive, then it may already be covered by the licence on instance 1 as a free passive server:
Beginning with SQL Server 2014, each active server licensed with SA coverage allows the installation of a single passive server used for fail-over support. The passive secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive. If it is serving data, such as reports to clients running active SQL Server workloads, or performing any “work” such as additional backups from secondary servers, then it must be licensed for SQL Server.
- If this instance is used and accessed (reporting, backups, etc.) or is not covered then it also need it own Enterprise licence.
Instance 3 Licence:
This instance is used (reporting) and host several databases. It needs its own Enterprise licence.