Always On Availability
Enhancements in SQL Server 2016 Availability Groups
SQL Server 2016 is making some significant improvements to the Always On Availability Groups set of features. There are a number of features, such as:
Round-robin load balancing in readable secondaries
Increased number of auto-failover targets
Enhanced log replication throughput and redo speed
Support for group-managed service accounts
Support for Distributed Transactions (DTC)
Basic HA in Standard edition
Direct seeding of new database replicas
Automatic Seeding in Always On Availability Groups
Automatic Seeding is a feature introduced with SQL Server 2016. We need to add SEEDING_MODE = AUTOMATIC when creating the Availability Group or alter the existing availability group as follows. If you set this feature AUTOMATIC with the following script, each database automatically added to the secondary server by SQL Server.
Enable Automatic Seeding
In order to do this, there should be paths of the same name for the data and log files on the primary and secondary servers.
1
2
3
ALTER AVAILABILITY GROUP [AG_Name]
MODIFY REPLICA ON 'secondary_instance'
WITH (SEEDING_MODE = AUTOMATIC)
Disable Automatic Seeding
We can set it MANUAL with the help of the script below.
ALTER AVAILABILITY GROUP [AG_Name]
MODIFY REPLICA ON 'secondary_instance'
WITH (SEEDING_MODE = MANUAL)
Check Automatic Seeding Status of an Availability Group
You can query the status of automatic seeding with the help of the following queries.
SELECT * FROM sys.dm_hadr_automatic_seeding
SELECT * FROM sys.dm_hadr_physical_seeding_stats;
SQL Server Always On is one of the interesting feature introduced with SQL Server 2012 version. I am listing the Questions with answers which are asked on Always On Feature in the SQL Server DBA interviews.
1. What is Always on in SQL Server 2012?
AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together. An availability group supports a set of read-write primary databases and one of four sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.
Prerequisites are as follows:
Ensure that the system is not a domain controller.
Ensure that each computer is running either x86 (non-WOW64) or x64 Windows Server 2008 or later versions.
Ensure that each computer is a node in a Windows Server Failover Clustering (WSFC) cluster.
Ensure that the WSFC cluster contains sufficient nodes to support your availability group configurations.
Ensure that all applicable Window hotfixes have been installed on every node in the WSFC cluster.
Windows Failover Clusters can have up to 64 nodes with Windows Server® 2012 R2.
2. What are Availability Groups?
A container for a set of databases, availability databases, that fails over together.
3. What are Availability Databases?
A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to four read-only copies (secondary databases).
4. Which SQL Server Editions include AlwaysOn Availability Group functionality?
SQL Server Enterprise Edition
5. What editions on Windows server support Always ON Functionality?
Windows Enterprise Edition
6. How many replicas can I have in an AlwaysOn Availability Group?
Total 5-1 Primary and up to 4 Secondaries.
How many read-write and read-only secondary database replica can you configure in SQL Server 2012 and 2014?
You can only have One Primary Database in both SQL Server 2012 and 2014.
For SQL 2012. Read Only secondary replicas, you can have 2 Sync and 2 A-Sync a total of 4
For SQL 2014, you can have a total of 8 secondary replicas (2 Sync and 6 A-Sync Plus 1 A-Sync Azure Replica)
7. How many AlwaysOn Availability Groups can be configured in Always ON?
Up to 10 availability groups is the recommendation, but it’s not enforced
8. How many databases can be configured in an AlwaysOn Availability Group?
Up to 100 is the recommendation, but it’s not enforced
Suppose the primary database became corrupted and is in suspect mode. Will the AG failover to the secondary replica?
If a Database who is a part of the Always On availability group transitions to a “recovery pending” or “suspect” state in SQL Server 2012 or 2014 on the primary replica, database availability is affected. In this situation, you cannot access the database through the listener or client applications. Additionally, will not be able to drop or remove the database from the availability group.In this case, you need to do a manual failover to the secondary server resolve any problems and fail back to the primary.
What are benefits of using AG? What are the advantages of AG when compared to mirroring & Clustering?
Answer: Always On Availability groups addresses all the major concerns of both Database Mirroring and Failover Clusters.
AG does not need a shared storage, unlike FCI. So the customer needs to invest in expensive SAN to achieve this requirement. Also, the shared disk is the single point of failure.
AG uses a Listener to route traffic to the Primary Server and handles the failover process. For the application, this failover is transparent. Unlike in Database Mirroring, where the “Failover Partner” name needs to be mentioned in the connection string. Else, the failover process is manual.
AG can have 8 secondary replicas and they can be read-only. In database mirroring, you can only have 1 Primary and 1 secondary where the secondary cannot be accessed. The only around it was to take a Database snapshot which is an Enterprise Only feature.
AG takes a couple of seconds to failover. In the case of FCI, the failover time can run into minutes
9. What is Availability mode in Always ON?
The availability mode is a property of each availability replica. The availability mode determines whether the primary replica waits to commit transactions on a database until a given secondary replica has written the transaction log records to disk (hardened the log).
11. Do we need shared storage to configure Always ON?
No, we don’t need shared storage.
12. How many Availability modes are supported by Always ON?
Always ON supports below availability modes.
Asynchronous-commit mode
Synchronous-commit mode
13. What is the Difference between Asynchronous-commit mode and Synchronous-commit mode?
Asynchronous-commit mode
An availability replica that uses this availability mode is known as an asynchronous-commit replica. Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
Synchronous-commit mode
An availability replica that uses this availability mode is known as a synchronous-commit replica. Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.
14. What is called Primary replica?
The availability replica that makes the primary databases available for read-write connections from clients and, also, sends transaction log records for each primary database to every secondary replica.
15. What is called Secondary replica?
An availability replica that maintains a secondary copy of each availability database, and serves as a potential failover targets for the availability group. Optionally, a secondary replica can support read-only access to secondary databases can support creating backups on secondary databases.
16. What is Availability Group listener?
A server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.
Question: Can you configure a single listener for all the availability groups in the SQL Server Instance?
Answer: A Listener is configured per Availability Group. You can have multiple databases as a part of a single Group. But, you cannot use a single listener for multiple availability groups.
Question: What is ENDPOINT? And what its default port number?
Answer: To host an availability replica for an availability group, a server instance must possess a database mirroring endpoint. The server instance uses this endpoint to listen for Always On Availability Groups messages from availability replicas hosted by other server instances.
What is the query to check ENDPOINT?
SELECT type_desc, port FROM sys.TCP_endpoints
17. What are Readable Secondary Replicas?
The AlwaysOn Availability Groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). A readable secondary replica allows read-only access to all its secondary databases. However, readable secondary databases are not set to read-only. They are dynamic. A given secondary database changes as changes on the corresponding primary database are applied to the secondary database.
18. What are the benefits of Readable Secondary Replicas?
Directing read-only connections to readable secondary replicas provides the following benefits:
Offloads your secondary read-only workloads from your primary replica, which conserves its resources for your mission critical workloads. If you have mission critical read-workload or the workload that cannot tolerate latency, you should run it on the primary.
Improves your return on investment for the systems that host readable secondary replicas.
In addition, readable secondaries provide robust support for read-only operations, as follows:
Temporary statistics on readable secondary database optimize read-only queries. For more information, see Statistics for Read-Only Access Databases, later in this topic.
Read-only workloads use row versioning to remove blocking contention on the secondary databases. All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.
19. How many synchronous secondary replicas can I have?
We can have up to 2 synchronous replicas, but we are not required to use any. We could run all Secondaries in Async mode if desired
20. Can we use a secondary for reporting purpose?
Yes. An active secondary can be used to offload read-only queries from the primary to a secondary instance in the availability group.
21. Can we use secondary replicas to take the db backups?
Yes. An active secondary can be used for some types of backups
22. What all types of DB backups are possible on Secondary Replicas?
BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
Differential backups are not supported on secondary replicas.
23. Can we take Transaction log backups on the secondary replicas?
Yes, we can take transaction log backups on the secondary replicas without COPY_ONLY option.
List out some of the requirements to setup a SQL Server failover cluster.
Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.
24. What is “Failover” in Always ON?
Within the context of a session between the primary replica and a secondary replica, the primary and secondary roles are potentially interchangeable in a process known as failover. During a failover the target secondary replica transitions to the primary role, becoming the new primary replica. The new primary replica brings its databases online as the primary databases, and client applications can connect to them. When the former primary replica is available, it transitions to the secondary role, becoming a secondary replica. The former primary databases become secondary databases and data synchronization resumes.
25. How many types of Failover are supported by Always ON?
Three forms of failover exist—automatic, manual, and forced (with possible data loss). The form or forms of failover supported by a given secondary replica depends on its availability mode,
26. What are the Failover types supported by Synchronous-commit mode?
Planned manual failover (without data loss)
Automatic failover (without data loss)
27. What is planned manual failover?
A manual failover occurs after a database administrator issues a failover command and causes a synchronized secondary replica to transition to the primary role (with guaranteed data protection) and the primary replica to transition to the secondary role. A manual failover requires that both the primary replica and the target secondary replica are running under synchronous-commit mode, and the secondary replica must already be synchronized.
28. What is Automatic failover?
An automatic failover occurs in response to a failure that causes a synchronized secondary replica to transition to the primary role (with guaranteed data protection). When the former primary replica becomes available, it transitions to the secondary role. Automatic failover requires that both the primary replica and the target secondary replica are running under synchronous-commit mode with the failover mode set to “Automatic”. In addition, the secondary replica must already be synchronized, have WSFC quorum, and meet the conditions specified by the flexible failover policy of the availability group.
29. Can we configure Automatic failover of Availability Groups with SQL Server Failover cluster instances?
SQL Server Failover Cluster Instances (FCIs) do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover.
30. What are the Failover types supported by under asynchronous-commit mode?
Only form of failover is forced manual failover (with possible data loss), typically called forced failover. Forced failover is considered a form of manual failover because it can only be initiated manually. Forced failover is a disaster recovery option. It is the only form of failover that is possible when the target secondary replica is not synchronized with the primary replica.
31. What is Use the AlwaysOn Dashboard
Database administrators use the AlwaysOn Dashboard to obtains an at-a-glance view the health of an AlwaysOn availability group and its availability replicas and databases in SQL Server 2012. Some of the typical uses for the AlwaysOn Dashboard are:
Choosing a replica for a manual failover.
Estimating data loss if you force failover.
Evaluating data-synchronization performance.
Evaluating the performance impact of a synchronous-commit secondary replica
Replica roll-up state
Synchronization mode and state
Time to restore log
Question: Does AG support automatic page repair for protection against any page corruption happens?
Answer: Yes, Automatic page repair is supported by Always On Availability Groups. After certain types of errors corrupt a page, making it unreadable, an availability replica (primary or secondary) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner or from another replica. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error. Detailed documentation.
Page Types That Cannot Be Automatically Repaired
Automatic page repair cannot repair the following control page types:
File header page (page ID 0).
Page 9 (the database boot page).
Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.
Quorum Status
For cluster type WSFC, displays the quorum status for the availability replica, one of:
In here you will be choosing the replicas that you want to add to your group. Important features here:
Initial Role: You will set the role that each replica will have once that the group becomes available
Failover Mode: Manual or Automatic
Availability Mode: Synchronous commit or Asynchronous commit
Readable Secondary: No, Yes - Read Intent Only and Yes. If you choose No or Yes - Read Intent Only, you won't be available to query your replicas, the Read Intent is when you enable that feature that routes the read operations to your available replicas. With the Yes option you will be able to query the databases in your replica.
Read-intent only -->Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
The endpoints tab will show you the URL and ports each replica is set to.
Backup preferences tab: in here you can configure if you want to take the backups from the primary or the secondary servers.
Listener tab: In this window it lets you set the parameters to configure the availability group listener, however my advice is to do it later, configure your group first and once that is done, configure it later.
Read-Only Routing: tab This lets you configure your read only routing for the read-intent setup, this allows you to load balance the queries so you have only the queries that will Insert, Delete or Update on the primary and all the Select queries routed to your secondary's so they don't consume resources the Primary will use (I'll explain it in another post)
What would happen if you add a new file (database file MDF or transaction log file LDF) to a database which is part of a high availability solution
USE [master]
GO
ALTER DATABASE [MyConDB]
ADD FILE (NAME = N'SQLAuthority_2',
FILENAME = N'E:\SQLAuthority.ndf' ,
SIZE = 4096KB, FILEGROWTH = 1024KB) TO FILEGROUP [PRIMARY]
GO
Whatever modification operation done on the database would be logged to the transaction log file, called as log record. Since database is part of availability group the same command would be sent to all secondary replicas via log record. This means that adding data or log file to primary database essentially roll-forwards the same command to all secondary replicas.
Verify that a connection is to a read-only replica
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');