Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the knowledge points about always on in sqlserver". In the daily operation, I believe that many people have doubts about the knowledge points about always on in sqlserver. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions about "what knowledge points about always on in sqlserver". Next, please follow the editor to study!

The biggest advantage of Alwayson over database mirroring is the readable copy, which not only brings the readable copy, but also adds a new function, which is to configure read-only routing to achieve read-write separation.

AlwaysOn technology combines the advantages of failover clustering, database mirroring and log shipping, but they are different. The unit of the failover cluster is the SQL instance, the unit of database mirroring and log shipping is a single user database, while the unit supported by AlwaysOn is the availability group, each group can include one or more user databases. That is, once a switch occurs, all data groups in the availability group are switched as a whole.

The underlying layer of AlwaysOn still uses the mechanism of Windows failover clustering for monitoring and transfer, so the Windows Cluster needs to be established first, but the databases in the availability group do not have to be stored on shared storage. It can be stored on a local disk.

SQL Server in stand-alone mode is recommended for each replica, and the database copy is stored on the local disk on which the instance node is running; if the availability copy is a cluster instance, the database copy is stored on the shared disk.

From the Windows cluster point of view, an availability group is a SQL-level cluster resource, in which all databases fail over between nodes as a whole, of course, excluding system databases, which cannot be added to a high availability group.

Because Windos clusters are required for monitoring and migration, AlwaysOn is subject to some limitations:

All availability replicas in an availability group must run on a single Windows cluster, and SQL Server instances across different Windows clusters cannot be configured as an AlwaysOn availability group.

All available replicas in an availability group must run on different nodes of the Windows cluster. Two different instances running on the same node cannot be used as copies of the same availability group.

A database can only belong to one availability group.

AlwaysOn can support up to five replicas, but only one database running on an available replica is readable and writable. This readable database is called the master database (PrimaryDatabase), and this available copy is called the master replica (primaryreplica). The remaining replicas are called secondary replicas (secondaryreplica), and the databases on the secondary replicas may be inaccessible or can only accept read-only operations (depending on the configuration of the availability group). These databases are called secondary databases. In the event of a failover, any secondary copy can become the new primary replica instance. The master copy will constantly send the data changes on the primary database to the secondary copy to achieve database synchronization between replicas.

Some basic concepts

For FCI:Failover Cluster Instance failure cluster instances, FCI is at the instance level and always on is at the database level. The concept of FCI is similar to ORACLE's RAC, but only one instance of the actual FCI can read and write.

FCI operates at the instance level, while AlwaysOn operates at the library level.

FCI is migration server hardware and does not provide migration of single or multiple databases. Database mirroring is required, but mirroring is "single database" and unreadable. AlwaysOn available groups can be migrated in multiple libraries as a unit, and the backup database is readable.

FCI has been a commonly used high-availability technology for SQL Server for a long time. It can fail over between any available nodes in the cluster. The only drawback is storage. Because of the need for shared storage, the storage subsystem becomes a risk point for a single point of failure. FCI is an instance of SQL Server installed on WSFC, whether it is a default instance or a named instance. This example requires at least these resources: IP address, network name, shared hard disk (N), SQL Server service, SQL Server proxy service. Of course, these resources are the same for individual instances, except that the IP address and network name come from the local machine, and the hard disk belongs to the local machine, while FCI is different. In a two-node FCI, the SQL Server instance uses shared storage that is available to both WSFC nodes as SQL Server storage. Usually, this storage is delineated in SAN. The deployment of LUN,FCI is roughly divided into two steps.

1. Run the SQL Server installation wizard on the first node of FCI and select New SQL Server failover Cluster installation. After completing the first step, you can start the second step.

2. Run the SQL Server installation wizard on the other participating nodes of WSFC and select add nodes to SQL Server failover cluster and complete the installation.

WSFC:Windows Server Failover Cluster windows service failover clustering, pure OS-level stuff

It is the core component of Microsoft High availability Technology (HA). Compared with FCI and AlwaysOn, WSFC is more of a function of Windows Server, while the latter two are functions of SQL Server. At the same time, WSFC is more low-level, so you need to deploy and configure WSFC before creating highly available technologies such as SQL Server Failover Cluster Instance and SQL Server AlwaysOn.

WSFC can combine multiple computer nodes (pure physical machine, pure virtual machine, physical machine hybrid virtual machine) together and provide highly available services to external applications. An application on the server, such as SQL Server, can run on any node of the cluster by providing a virtual access point (consisting of a unique IP address and a unique machine name, or a "virtual network name") to the client program as a link through cluster. As a "resource group" of an application, addresses and virtual names are transmitted as tokens between multiple participating nodes. When the active node has a serious failure, it will make the active node stop external service. At this point, the cluster service automatically attempts to restart the resource group of the current node or partner node. From a high-level point of view, the client's access point is transmitted along all hard drives and service origins in the failover partner node. When a clustered instance fails over, the client connection is disconnected and then reconnected as soon as the other nodes are available.

Availability group: the group name of the DB-level cluster

Each availability group defines a collection of two or more failover partners called availability replicas. Availability copy is a component of an availability group. Each availability copy hosts a copy of the availability database in the availability group. For a given availability group, the availability copy must be on a separate SQL Server instance on different nodes of a WSFC cluster.

Availability replicas: members of a DB-level cluster, including primary replicas, secondary replicas, each consisting of some databases

For each availability group, a given instance can only host one availability copy. However, each instance can be used for multiple availability groups. The given instance can be a stand-alone instance or an SQL Server failover cluster instance (FCI).

Each availability copy is assigned an initial role ("protagonist color" or "secondary role"), which is inherited by the availability database of that copy. The role of a given copy determines whether it hosts a read-write database or a read-only database. One of the replicas (called the "master copy") is assigned the primary role, which hosts the read-write database (called the "master database"). At least one other copy (called a "secondary copy") is assigned a secondary role. The secondary copy hosts a read-only database (called a secondary database).

Listener

After the AlwaysOn is created, the client needs to connect. In order for the application to connect transparently to the master copy without being affected by failover, we need to create a listener. The listener is a virtual network name through which the availability group can be accessed, regardless of which node is connected. It will automatically forward the request to the master node. When the master node fails, it will automatically forward the request to the master node. The secondary node becomes the primary, and the listener automatically listens to the primary.

A listener includes three elements: virtual IP address, virtual network name, and port number. Once created, the virtual network name is registered in DNS, and the IP address resource and network name resource are added to the availability group resource. Users can use this name to connect to the availability group. Unlike a failover cluster, in addition to using a virtual network name, the real instance name of the primary replica can also be used to connect.

The principle of Always on

1. There is a thread called Log Writer in any SQL Server. When any SQL user submits a data modification transaction, it will be responsible for recording the log information of this modification in a log buffer in memory, and then writing to the physical log file (log solidification), so for any database, there will be records of all data changes in the log file.

2. For a database configured as the master copy of AlwaysOn, SQL Server will set up a worker thread called Log Scanner, which is specially responsible for reading log records from the log buffer or log file, packaging them into log blocks, and sending them to each secondary copy. Because of its uninterrupted work, the data changes on the primary copy can be continuously transmitted to the secondary copy.

3. On the secondary copy, there will also be two threads that complete the corresponding data update actions, which are Harden and Redo. The solidification thread writes the log blocks sent by the primary copy Log Scanner to the log file on the disk of the secondary copy (this process is called "solidification"). The redo thread, on the other hand, is responsible for reading log blocks from disk, translating log records into data modification operations, and completing them on the database of the secondary copy. When the redo thread finishes its work, the database on the secondary copy will be the same as the primary copy. AlwaysOn uses this mechanism to keep replicas synchronized. At regular intervals, the redo thread communicates with the master copy to inform it of its own progress. The master copy will be able to know how far the data gap between the two sides is. These threads work independently in order to achieve higher efficiency. Log Scanner is responsible for sending log blocks without waiting for Log Writer to complete log solidification; after the secondary copy completes log solidification, it sends a message to the master copy informing you that the data has been transferred without having to wait for the redo to complete. The design goal is to minimize the performance impact of additional operations brought about by AlwaysOn on normal database operations.

4. the maintenance mode of synchronous commit mode: after receiving the transaction from the client, the primary copy writes the transaction log to the transaction log and sends the log record to the secondary copy. After the log record is written to the transaction log of the primary database, the transaction cannot be undone unless it fails over to a secondary copy of the log that has not been received at this time. The primary copy will wait for confirmation from the synchronous submission of the secondary copy. The secondary copy forces writing to the log (solidified) and returns the confirmation message to the primary copy. Upon receipt of the confirmation from the secondary copy, the master copy completes the submission process and sends a confirmation message to the client. In synchronous commit availability mode, after the replica is connected to an availability group, the secondary database will be consistent with the corresponding primary database and enter the SYNCHRONIZED state. As long as data synchronization is ongoing, the secondary database remains in the SYNCHRONIZED state. This ensures that each transaction committed to the primary database is also applied to the corresponding secondary database. After each secondary database on the secondary replica is synchronized, the overall synchronous running state of the secondary replica will be HEALTHY.

5. The maintenance mode of asynchronous commit mode: if each secondary copy runs in asynchronous commit mode, the primary copy will not wait for any secondary copy to be forced to write to the log, but will immediately send the transaction confirmation to the client after the log record is written to the local log file. Because the primary copy does not wait for confirmation from the secondary copy, problems on the secondary copy never affect the primary copy, and the secondary database remains in the SYNCHRONIZING state. Asynchronous commit mode is useful in situations where the primary and secondary copies are far apart and you do not want small errors to affect the disaster recovery scenario of the primary, or where performance is more important than synchronous data protection. Asynchronously commit a secondary copy attempts to be consistent with the log records that receive the autonomous copy, but the asynchronous commit secondary database tends to remain out of sync, usually the time difference between the asynchronous commit secondary database and the corresponding primary database is small. However, this time difference can become larger if the workload of the server hosting the secondary copy is too high or the network speed is slow.

6. Session timeout mechanism: because soft errors cannot be detected directly by the server instance, soft errors may cause one availability copy to wait indefinitely for a response from another availability copy in the session. To prevent this from happening, the Always On availability group implements a session timeout mechanism based on the condition that the connected availability copy sends ping at regular intervals on each open connection. Ping is received during the timeout period to indicate that the connection is still open and that the server instance is communicating over this connection. Upon receipt of the ping, the copy resets the timeout counter on this connection. The primary and secondary replicas ping each other to indicate that they are still active, and the session timeout limit is a user-configurable replica property with a default value of 10 seconds. If you do not receive a ping from another copy within the session timeout period, the connection times out and the connection closes, and the timed copy enters the DISCONNECTED state. Even if it is a copy of synchronous commit mode, the transaction will temporarily switch the secondary copy to asynchronous commit mode without waiting for the copy to reconnect. After the secondary copy is reconnected to the primary copy, they will resume synchronous commit mode.

There are three ways to configure arbitration:

1. No arbitration witness is configured: the cluster will provide services only when the minority is subordinate to the majority and the number of normal nodes is in the majority, otherwise the service will be stopped. For example, for a cluster with 5 nodes, the normal number of nodes must be at least 3 before the cluster can provide services.

2. Configure disk witness: for clusters with even nodes, arbitration disks will be taken into account when calculating the legal number. For example, a cluster with 4 nodes + 1 arbitration disk nodes can be regarded as a cluster with 5 nodes. In this case, the number of normal nodes must be at least 3 before the cluster will provide services.

3. Configure shared file witness: it is similar to configuring disk witness, but the disk is changed to share files in the folder.

Construction of Always on

The website of the tutorial on the Internet is https://www.linuxidc.com/Linux/2017-01/139766.htm.

1. All servers of primary and secondary node instances must first install the failover cluster Failover clustering feature on os. Once one of the servers is not installed, an error the server 'XX' does not have the failover clustering feature installed will be reported when creating a failover cluster. Server manager--tools--Failover cluster manager will only occur if Server Manager--Manage--add roles and feature--feature--failover clustering is installed in OS with failover clustering.

2. The servers of primary and secondary node instances need to join the same domain.

3. When creating a Windows server failover cluster (Windows Server Failover Cluster), you can only create one of the servers, such as the server of the primary node instance, give the cluster a name and assign an ip, and add all node servers to the failover cluster (these joined servers need to be suffixed with a domain name). Do not check "add all eligible storage to the cluster" at this time. Otherwise, the storage directory originally mounted on the server of the primary and secondary node instances will disappear.

4. When configuring cluster arbitration to choose shared file arbitration, you cannot use the local directory of any node server (File share associated with file share witness resource cannot be hosted by this cluster or any of its nodes)

5. The sqlserver service of each node needs to enable the function of always on. When this function is enabled, you need to restart the sqlserver service to take effect.

Sql Server Configuration Manager--SQL Server Serivces--SQL Server (MSSQLSERVER)-- right-select Properties--Awayson High Availability--Enable AlwaysOn Availability Groups

6. Configure always on on the master node database instance, and right-click the instance-Always On High Availability-- to select New Availablity Group Wizard to create a new availability group.

7. When the database joins the always on availability group, right-click the high availability group name-add database, but you must perform full backup and log backup of the database of the instance of the primary node, and restore the full backup and log backup to the instance of the secondary node in norecovery mode

Note: you need to pay attention to whether the sectors of the primary replica machine and each secondary replica machine are consistent. If the sector inconsistency may lead to slow synchronization, then it is best not to build an AlwaysOn.

Summary of Always on

1. The tables and indexes created by the primary node database are automatically synchronized to the secondary node database.

2. Always on requires that the operating system version corresponding to each node must be the same, but the database version can be different. For example, one database is sqlserver2014 sp2 and the other is sqlserver2014 sp3.

3. When building an always on, the instance name @ @ servername of each node does not need to be consistent.

4. With regard to IP, one is the ip,OS-level IP of the windows failover cluster, through which the outside can log in to any server in the failover cluster. One is alwayson listening IP, which is a database instance-level IP, through which external users can connect to any database instance of alwayson, similar to oracle's scan ip.

5. None of the databases of primary or secondary nodes can perform offline operations, and an error will be reported when performing offline operations: The operation cannot be performed on database 'XX' because it is involved in a database mirroring session or an availability group

6. None of the databases of primary or secondary nodes can perform a detach operation, and an error will be reported when performing a detach operation: The database 'XX' is currently joined to an availability group,before you can drop the database,you need to remove it from the availability group

7. The status display behind the database of the primary node (Synchronized), and that of the database of the secondary node (Synchronizing)

8. A data file is added to the database of the primary node, and a data file is also added to the database of the secondary node, and the path is exactly the same as that of the database of the primary node. Even if the default path is set for the database of the secondary node, it will be ignored. For example, the default path for the database of the secondary node is G:\ DEFAULT.DATA,primary node, and the path for the new file is L:\ data1.dbf. The file path of the secondary node database is also L:\ data1.dbf, rather than G:\ DEFAULT.DATA\ data1.dbf, so the primary node database adds a data file, the secondary node database server does not have the same path, the secondary node database will report an error, and the synchronization of always on will be interrupted

9. Always on does not synchronize normally. The specific way to deal with it is to first check the instance sqlserver log logs of primary and secondary nodes to see what the problem is.

10. Remove a database from the availability group of Always on

The following two items are operated in the primary group

1. Find the database in the availability group and right-click to pause data transfer.

2. Find the database in the availability group and right-click to remove AG.

Or

ALTER DATABASE database_name SET HADR OFF

11. The primary node removes the database from the AG, and the database cannot be seen in the AG of the secondary node, but the database of the secondary node still exists and the status is displayed (Not Synchronzing), which means that the database of the secondary node is still in AG. The primary node executes the ALTER DATABASE dbname SET HADR OFF error and says that the database does not exist. The secondary node does not report an error but waits for a background process.

Cause 1: the log disk space of the primary node is larger than that of the secondary node. As a result, the log of the primary node is not fully synchronized to the secondary node, and the disk space of the secondary node explodes, and the primary node has no way to shrink the log, so the database can only be cancelled from the AG. After the primary node removes the database in AG, the secondary node cannot see the database in the AG. But the secondary node the database still exists and the status is displayed (not synchronizing is not synchronized)

Cause 2: there is a need to remove a database from the AG, back it up in the master database, restore it from the slave library, and continue to add it to AG. At this time, the database is no longer in the availability group, and there is no Synchonized behind the database on the master database, but it is also displayed behind the database from the slave database (Not Synchonizing). As a result, the slave database cannot be deleted and the database is not restore. Error message from the library: unable to accesss availability database 'XXX' because the database replica is not in the primary or secondary role.

Treatment method 1: there is no good way, can only wait, wait for the secondary node the database state becomes (restoring recovery), if the waiting time is too long to consider method 2

Method 2: restart the slave library, display the database status of the slave library (Not Synchonizing/In Recovery), and then execute ALTER DATABASE database_name SET HADR OFF in the slave library. At this time, the database status display (restoring) of the slave library can be deleted.

12. A database of the secondary node displays Not Synchonizing. In the AG of the secondary node, the database shows an exception and a red mark appears. Right-click cannot display resume. Solution: remove it directly on the primary node, and then add it.

The database displays synchronized on the primary node instance

On the secondary node instance, the database displays not synchronizing, the secondary node-Always On High Availability-Availability Groups-AG name (Secondary)-Availability Databases- database name-the lower right corner of the database shows red, right-click this database cannot display resume, only suspend and remove

13. The status of all databases in the master or replica of always on is Not Synchonizing, the availability group shows resolving, and a copy below it also shows resolving. The solution is to restart the server with the status of resolving.

14. A database of the secondary node displays Not Synchronizing In Recovery, and under the AG of the secondary node, the database displays the blue mark: find the database that reported the error under the secondary node Always On High Availability--Availability Groups--Availability Databases and display it as a blue mark, right-click and select Resume Data Movement. At this time, the database that reported the error is displayed as a red flag, and after a while it will begin to synchronize.

15. A database in the secondary node shows the general idea of how to deal with Not Synchronizing. As long as the log transferred to the secondary copy is not lost, the master copy still retains the log. This problem is very simple. Either select Resume Data Movement on the secondary node as above 14, or remove the database on the primary node, then take the log of the primary node database to the secondary node to restore,restore, and then add it again.

16. Option readable secondary can read the secondary copy: although the primary node also chooses no to indicate that the select statement cannot be executed, the primary node can still execute select, because this option is only valid for the secondary copy and is not valid for the primary copy

17. When the main library of always on makes incremental backups, it unexpectedly makes the logs of the main library unreusable, causing the logs to be truncated and the logs soar. The reason is that the occurrence of "active backup or restore" in the second field of select name,log_reuse_wait_desc from sys.databases, "active backup or restore", will affect log truncation, which has nothing to do with always on itself.

18. Windows's MDSTC service failed after always on was cancelled: the case of MSDTC on server 'XX' is unavailable

Component Services-> computer-> my computer, showing the processing flow of the red arrow

18.1. After the primary node cancels the always on, it is best not to change the configuration of the windows OS, that is, do not turn off the status of the primary node in the windows failover cluster

18.2. After the primary node cancels always on, if the primary node displays offline in the windows failover cluster, but the secondary node displays online in the windows failover cluster, try to display the primary node as online in the windows failover cluster to see if the red arrow disappears.

18.3.If the above 2 fails, shut down the entire windows failover cluster at the primary node to see if both nodes display offline and see if the red arrow disappears

18.4. If the above 3 fails, start the windows failover cluster at the primary node to see if both nodes display online and see if the red arrow disappears

18.5. If the above 4 fails, try to restart the primary node server and see if the red arrow disappears after restarting.

19. When the secondary copy of AG is executing the backup log of logshipping, the manual execution of backup log in the master copy will be blocked because it is waiting for the process that the event type is HADR_BACKUP_QUEUE. In fact, the main copy of AG backs up the log at the same time.

20. The secondary copy instance of AG is configured with logshipping to other servers, and the backup log job on this secondary copy instance reports an error.

The backup operation on database 'TESTDB' was skipped because it is part of an availability group and not its preferred backup replica.

Solution method

20.1. Make sure that the instance name of the secondary copy matches the machine name

20.2. Set to any copy in the backup preferences in the always on availability group, and the backup priority of the secondary copy is higher than that of the primary copy, for example, set the secondary copy to 51 and the primary copy to 50

21. The master and replica node A1 of always on builds logshipping to C server and reports an error The backup operation on database 'DB' was skipped because it is part of an availability group and not its preferred backup replica. (because it is part of an availability group and not its preferred backup copy, it skips backup operations on database 'database name')

The reason for the error: A1 is the master replica node, and the backup preference set by AG is prefer secondary, so A1 has no way to back up the log, so you need to build a logshipping to C server on A2, a secondary copy.

Continuing problem: now A2 starts logshipping backup logs, but A1 can also execute BACKUP LOG [DB] TO DISK = N'\\ log\ DB_LOG_YYMMDDMi.bak'

The problem continues. The C server has two restore job, but the restore job from A2 has an error * Error: Could not log history/error message. (Microsoft.SqlServer.Management.LogShipping) *

* Error: The specified agent_id 27A07B67-19A6-4BA1-A05D-52CC968B479C or agent_type 2 do not form a valid pair for log shipping monitoring processing. (.net SqlClient Data Provider) * *

The final solution is

21.1 and A1 also build logshipping, but all the job on A1 is disable, but the job on C server is enable

21.2. A2 also builds logshipping,A2. All job is enable, but job on C server is disable.

21.3. Backup log is enabled for A1 instance, but logshipping database msdb.dbo.log_shipping_primary_databases is excluded from the backup script.

22. The logshipping built by AG is like example 21 above. The backup job of the auxiliary copy A2 logshipping reported an error First attempt to backup database 'ECMDB' to file'\\ YY\ ZZ.trn' failed because Log backup for database "ECMDB" on a secondary replica failed because the last backup LSN (0x000cb1e0:02c34336:0001) from the primary database is greater than the current local redo LSN (0x000cb1e0:02c342e1:0155). No log records need to be backed up at this time. Retry the log-backup operation later. After checking that the log transfer between the two AG nodes is normal and without delay, restart the sqlserver service of the secondary replica node A2 and find that the backup job of the logshipping of the secondary replica node A2 also reports the same error The backup operation on database 'ECMDB' was skipped because it is part of an availability group and not its preferred backup replica as the master replica node A1.

The reason found so far is that there may be problems in the transmission between the AG master replica nodes and the secondary replica nodes, resulting in the secondary replica node A2 being unable to keep up with the primary replica node A1, the solution.

22.1. Remove the ECMDB database from the AG and back up the log of the master node A1.

22.2. Take the log backed up by the primary replica node A1 to the secondary replica node A2 for recovery, and also take it to server C of logshipping to restore.

22.3. Add the ECMDB database to AG again, so that the backup job of the logshipping of the auxiliary replica node A2 is normal.

Backup of Always on

Question: with regard to log backup, we all know that transaction log backup will truncate the log chain. if I perform a log backup on any copy, will the logs of other copies be truncated as well?

Answer: yes, one copy performs log backup, and the other copies will be truncated automatically. As long as the primary node communicates normally with the secondary node, no matter how it is set up, the log can be backed up, either at the primary node or at the secondary node, but not at the same time. No matter which node it is backed up, it will truncate the logs of all nodes.

In fact, you can back up and truncate the transaction log as long as you "back up the transaction log" on the database instance specified by "backup preferences" (availability group, right button, properties,).

The backup of the master copy of AG is performed as follows, all normal

Backup database alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db1_alwayson1_full.bak'

Backup database alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db1_alwayson1_diff.bak' WITH DIFFERENTIAL

Backup log alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db1_alwayson1_log2.bak'

Types of backups supported on the AG secondary copy

1. BACKUP DATABASE: only replicated full backups of databases, files, or filegroups are supported in the secondary. Note that only replicating the backup does not affect the log chain and does not clear the differential bitmap.

2. Differential backup is not supported in the secondary copy (but it is found that it can also be backed up if with differential,copy_only is added)

3. BACKUP LOG only supports regular log backups (log backups on secondary copies do not support the COPY_ONLY option).

4. To back up the secondary database, the secondary copy must be able to communicate with the primary copy and the status must be SYNCHRONIZED or SYNCHRONIZING. Otherwise, an error Cannot backup from a HADRON secondary because it is not in Synchronizing or Synchronized state will be reported.

Note: in a distributed availability group, you can perform backups on a secondary copy in the same availability group as the active primary copy, or on a primary copy of any secondary availability group. A backup cannot be performed on a secondary replica in a secondary availability group because the secondary copy communicates only with the primary copy in its availability group. Only copies that communicate directly with the global primary copy can perform backup operations.

The backup of the secondary copy of AG is performed as follows with an error (the database cannot be backed up)

Backup database alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db2_alwayson1_full.bak'

Backup database alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db2_alwayson1_diff.bak' WITH DIFFERENTIAL

The following error occurred:

This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.

The backup of the secondary copy of AG is performed as follows. Normal backup (log can be backed up)

Backup log alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db2_alwayson1_log.bak'

The backup of the secondary copy of AG is performed as follows with an error (log backup does not support COPY_ONLY)

Backup log alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db2_alwayson1_log.bak' with copy_only

The backup of the secondary copy of AG is performed as follows, and all are normal (database backup only supports copy_only)

Backup database alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db2_alwayson1_full.bak' with copy_only

Backup database alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db2_alwayson1_diff.bak' WITH DIFFERENTIAL, copy_only

Backup log alwayson1 to disk ='\\ woncntestdb1\ alwayson\ db2_alwayson1_log2.bak'

Instance-Always On High availability-availability Group-availability Group name-right-attribute-backup preferences

Preferred secondary copy (default option)

Specifies that the backup should occur on the secondary copy, and when no secondary copy is available online, the backup should occur on the primary copy.

Secondary copy only

Specifies that the backup occurs only in the secondary copy, and does not occur when there is no secondary copy available online.

Master copy

Specifies that the backup should always occur on the master copy.

Any copy

Specify the role that you want the backup job to ignore the availability copy when selecting the copy to perform the backup. There is also a backup priority setting for replicas below this option, with 1 bit as the lowest and 100 as the highest. The default is 50 for both the primary and secondary replicas.

Above, the results of the four options can also be queried through the sys.fn_hadr_backup_is_preferred_replica function, which returns 1 if the current database is the preferred backup copy.

For example, if the SELECT sys.fn_hadr_backup_is_preferred_replica ('testdb') result is 1, then the testdb of this instance is the preferred backup copy in the availability group.

At this point, the study of "what are the knowledge points about always on in sqlserver" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.