**Background** We are looking into building a multiple standby hadr cluster. The Auxiliary standby is in a geographically separate location. from [Developer works](https://www.ibm.com/developerworks/community/blogs/DB2LUWAvailability/entry/log_archiving_in_an_hadr_environment?lang=en) >In a multiple standby system, the archived log files can be scattered among >all databases' (primary and standbys) archive devices. A shared archive >is preferred because all files are stored in a single location. and [Developer works](https://www.ibm.com/developerworks/community/wikis/home/wiki/DB2HADR/page/HADR%20config?lang=en§ion=LOGARCHMETH1_and_LOGARCHMETH2___Log_Archive_Device) >Share archive for databases at the same site is recommended. For remote >sites, you will need to make a decision based on the network speed, >reliability, and ease of management **My Question** What method/solution can be used to create a shared archive. We are running on X86 SLES 11.4 with HDS san. Primary and Standby is in the same datacentre but Aux-standby is 30km away. Would a simple NFS share be used and replication to the Aux. Thank you

our Availability Groups in Azure are currently 2 nodes, both on the same subnet, with a load balancer in front of them. I'd like to add an async node in another region for DR purposes. This will be on a different subnet. In an ideal world I'd split the current AG nodes onto different subnets, get rid of the LB and update all the connection strings with multisubnetfailover= true. However we have a very old code base and I've been told there isn't budget\time to locate, update and test all the connection strings. If, instead, I updated the cluster with RegisterAllProvidersIP=0, will it work with the LB's and the new DR node? Will failovers between the 2 nodes on the same subnet still have to wait for the DNS TTL to failover?

Browsing around some sys tables, I see: sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name and sys.dm_hadr_availability_replica_cluster_nodes.replica_server_name = sys.availability_replicas.replica_server_name and sys.dm_hadr_availability_replica_cluster_nodes.node_name = sys.availability_replicas.replica_server_name and sys.dm_hadr_database_replica_states.group_id = sys.availability_groups.group_id Could someone please clarify for me whether the "_hadr_" tables are duplicating data or not? For example, sys.dm_hadr_availability_replica_nodes and sys.avilability_replicas sound like they're describing the same thing. I guess the question is on how safe it is to join these tables given I can see data values equate - because after all, the same value (something like 'SQL01') could conceivably be used to describe different things. Better yet - is there no entity-relationship diagram published by Microsoft for these sys tables? I can find old posts where people ask the same question about ERDs and there are answers regarding older versions of SQL Server, but all links to diagrams seem outdated and defunct.

I am using dba_CopyLogins stored proc to sync logins on replicas in Distributed Availability groups, but the database level permissions are not transferred due to database sync operation is going on.. Is there any way to sync all logins and permissions from global primary to forwarder and other replicas? Also, how to sync SQL agent jobs using any automated way?

we're looking at DR options for our Azure SQL dedicated pool. Most documentation refers to backup and restore solutions, but it looks like I can add it to a failover Group, however I can't find any docs that refer to it as an option. The storage costs for our SQL pool aren't huge, so replicating it from a cost point of view would be ok. Has anyone else used a Failover group with a dedicated pool? Does it work? Cheers Alex

If I set up a failover Group for an Azure SQL Database (not a Managed Instance or SQL Server on VM), is it possible to do a test failover, so there is a copy of the Azure SQL Database available for testing, whilst a copy of the production SQL instance is maintained? Cheers Alex

we are building Async AG nodes on SQL in VM in Azure, for DR purposes. To save money the compute resource is a quarter of what the sync nodes are, with the idea that if we ever implement DR the compute will be increased. Could the under powered third node impact performance of the 2 sync nodes.

This will be my first project with Basic availability group so I thought would check before implementing . As per Microsoft documentation below it says we will need to have Software assurance for failover servers for disaster recovery / high availability. https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15 Will greatly appreciate if someone can clarify on this as I will need to have the capabilities to do automatic / manual failover without the need to purchase software assurance

We are building a multi-subnet, always-on availability group. I initially requested the below IPs. For Failover Cluster 192.168.1.129 192.168.2.129 For Listener 192.168.1.130 192.168.2.130 My network team provided the IPs below. For Failover Cluster 192.168.1.129 192.168.2.130 For Listener 192.168.1.131 192.168.2.132 To my understanding, the above IPs should work as they belong to different subnets, but I want to be 100% sure that the IPs do not need to be identical except for the subnet. As long as the IPs belong to separate subnets, it should be fine.

Good afternoon, We have 4 node SQL Server configure in HA/DR. Periodically, we purge the old data to free up the space. Recently, after the SQL Job to purge the data was executed, we notice an increase of Freespace in the transaction log file size. Please review the following image. Questions: 1. How to release the Freespace without causing any disruption in HA? 2. Do we need to perform the operation to each node in HA/DR? 3. The auto growth is configured as follow: 4. It is our OLTP (busy) database. Do we need to perform any pre-requisite steps before releasing the Freespace? 5. Does it cause any fragmentation? Please provide your valuable input to resolve this issue. Let us know if you need any further information. We are using SQL Server 2016 Enterprise edition.

I can use below statements to suspend or resume HADR for a database.

ALTER DATABASE test SET HADR SUSPEND;
ALTER DATABASE test SET HADR RESUME;

How can I check a database is in HADR suspend or resume state using TSQL?

There is 2 node AlwaysON AG in synchronous mode. In the morning (09:00 am) we noticed that queue from application side started to grow, in SQL Server there were high HADR_SYNC_COMMIT wait type. Using the article https://techcommunity.microsoft.com/t5/sql-server-blog/troubleshooting-high-hadr-sync-commit-wait-type-with-always-on/ba-p/385369 we configured Extended Events session on Primary and on Secondary, gathered data for 10 minutes, then changed AlwaysOn AG to asynchronous mode and issue is gone. Here's what we got in Extended Events session. Did analysis like in article above. On primary: On secondary: As you see the biggest latency here is on primary in hadr_capture_log_block between mode 2 and 3 ~ 249 ms. As far as I understand the bottleneck was in "Queue of DbMgrPartner" - it was processing too long. The question is what the root cause ? Network metrics (Bytes sent, bytes received) in perfmon didn't change after switching to async mode. One interesting point in perfmon on Primary: 3 identical lines here: Bytes Sent to Replica/sec, Bytes Sent to Transport/sec and Log Bytes Flushed/sec 10:35 - time when we switch to async CPU consumption for this period of time Top 5 Waits for periods of 5 minutes:

We have a 4 node availability group with 3 nodes at primary site and 1 node at the DR. Suppose the Primary site entirely goes down and we have to do a forced failover at the DR. Now if there is no connectivity between the two sites and suddenly the primary site comes up and in this case since it has the majority hence it will form a quorom and now we have to writable primaries , one at the primary and one at the DR. This is where I am confused. What are the step can I take to avoid this split brain scenario. As soon as I failover should I inform my sysadmin/network team not to bring all the nodes up together and to make sure to resume the connectivity between data center first and then bring the nodes one at a time so that it can catch up and avoid split brain.

I am analysing a HA DR solution. There are 2 sql servers (A and B) on-site and one sql server (C) in the remote location. Synchronous AG is configured between A and B. Asyncronous AG is configured between A and C. 1. If there was a clustering setup then 1 IP address could have been used that will auto connect to the active sql server. In absence of clustering, and in case of the above AG setup, is it possible for application to use 1 sql server ip address? 2. This isn't a true DR solution because unlike clustering (which uses 1 SAN), the logins, jobs are not syncronized between the sql servers. How can I sync this data with AG?

We have a 4 node availability group with 3 node on the primary data center and one node on the secondary data center. We want to add a file share witness and since we dont have a third data center and not allowed to go with cloud witness, where would the file share witness be ? At the primary or secondary. Will putting on the secondary helps as the majority of the nodes are in the primary data center

Currently we have several databases with a HADR configuration where the primary databases are on a Linux Server "A", with the standby all on Linux Server "B". DB2 version is 9.7. We are attempting to relocate the primary database of one of these databases (with the intent of moving all later) to a new Linux Server "C". Efforts to find something similar have just brought back results of HADR takeover, that is not what we are aiming to do. Lets call this database MYDB. I have taken the steps below, and while HADR will start between the two by issuing the relevant start HADR commands first on the standby then on the primary, issuing 'db2pd -db MYDB -hadr' is showing them as disconnected with 'S0000000.log' as the log file on the opposite end. The correct log is being displayed locally. **STEPS TAKEN** 1. Quiesced the database and then stopped HADR on the primary, confirmed on the secondary there was now a log gap, stopped HADR on the Standby and deactivated. 2. Took an offline backup on the current primary database and sent that to the new server "C", where an identical version of DB2 is already set up. 3. Created a new database 'MYDB' and restored from the backup sent over. 4. Updated the relevant database configurations: On new server C : >db2 update db cfg for mydb using HADR_LOCAL_HOST C >db2 update db cfg for mydb using HADR_LOCAL_SVC hadr_mydb_c On the existing standby B: >db2 update db cfg for mydb using HADR_REMOTE_HOST C >db2 update db cfg for mydb using HADR_REMOTE_SVC hadr_mydb_c >db2 update alternate server for database mydb using hostname c port 3700 'hadr_mydb_c' has been added to /etc/services on both 'B' and 'C' with a defined port of 3734 'C' has been added to both 'B' and 'C' hosts files. Log locations ect have been created to match the existing server 'A'. 5. At this stage we have done a db2stop and start on the backup, then when we issue 'db2 start hadr on db mydb as standby' we get a confirmation that HADR has started. 6. On the new primary we issue 'db2 start hadr on db mydb as primary' we again get a confirmation that HADR has started. db2pd shows HADR as active but the databases not connected. Issuing the following command on the backup instance to see HADR details I can't see the MYDB database listed along with the other databases.

"SELECT SUBSTR(DB_NAME, 1, 8) AS DBNAME, HADR_ROLE, HADR_STATE,HADR_SYNCMODE, HADR_CONNECT_STATUS,HADR_HEARTBEAT,HADR_TIMEOUT,HADR_LOG_GAP FROM TABLE (SNAP_GET_HADR (CAST (NULL as VARCHAR(128)), 0)) as T"

Luckily reverting the HADR config back to what they were previously we can reconnect HADR between the old primary and backup. Any ideas of how best to proceed?

I have seen the following messages in Db2diag.log but I do not know what they are. I cannot find any documentation about LFL or XHDR. I know it is related to HADR and log archiving, but what exactly?

2021-07-20-06.26.20.280271-240 E264696A655          LEVEL: Info
PID     : 6947266              TID : 5757           PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000           DB   : SAMPLE
HOSTNAME: db2server
EDUID   : 5757                 EDUNAME: db2lfr.0 (SAMPLE) 0
FUNCTION: DB2 UDB, recovery manager, sqlplfrFMReadLog, probe:2825
DATA #1 : SQLPLFR_SCAN_ID, PD_TYPE_SQLPLFR_SCAN_ID, 8 bytes
LFR Scan Num            = 898596933
LFR Scan Caller's EDUID = 20147
MESSAGE : Reached the max number of retries waiting for the LFL in XHDR. Moving
          to next extent.
DATA #2 : SQLPG_EXTENT_NUM, PD_TYPE_SQLPG_EXTENT_NUM, 4 bytes
702955

Do you know one of these terms and where can I get more information?

Looking to build a 4-node (servers A, B, C & D) AlwaysOn Windows 2016 failover cluster with 2 nodes in the primary datacenter for HA, and 2 other replica nodes in the DR datacenter for disaster/reporting purposes. Since this is a multisite cluster, is there a way to configure to have two listeners (one for the Production/primary instance, the other for the DR instance) under the one AlwaysOn Availability Group. Also, would SQL on all four nodes need to run under the same service account? Or can servers A, B be on one account while servers C, D run under another account. Is this even feasible/possible? Thank you in advance for your help!! Hope everyone is safe & well during this time.

I am trying to set up HADR to run on my DB2 10.5 DB2 servers. I believe I have followed the steps correctly, but I'm getting the following error: [db2insh1@rslvm20 OneView]$ db2 restore database onevwhad DB20000I The RESTORE DATABASE command completed successfully. [db2insh1@rslvm20 OneView]$ db2 -vtf update_config_HADR_Secondary.sql UPDATE DB CFG FOR ONEVWHAD USING HADR_LOCAL_HOST rslvm20.dub.usoh.ibm.com HADR_LOCAL_SVC DB2_db2insh1_hadr HADR_SYNCMODE NEARSYNC HADR_REMOTE_HOST rslvm19.dub.usoh.ibm.com HADR_REMOTE_SVC DB2_db2insh1_hadr HADR_REMOTE_INST db2insh1 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. [db2insh1@rslvm20 OneView]$ db2 start hadr on db onevwhad as standby SQL1767N Start HADR cannot complete. Reason code = "1". The Knowledge Center says this about that error: Start HADR cannot complete. The explanation corresponding to the reason code is: 1: The database was not in roll forward-pending or roll forward-in-progress state when the START HADR AS STANDBY command was issued. The user response corresponding to the reason code is: 1: Initialize the standby database from a backup image or a split mirror of the primary database, then reissue the START HADR AS STANDBY command. But I restored the database right before I updated the HADR config, and then tried to start HADR. There were no intervening steps, so I am confused. I'm fairly sure I have to update the config between restoring the DB and starting HADR. Any idea what I'm doing wrong? ========================================================================================== The 'LOGARCHMETH1' option is set to 'OFF'. Here are the applicable contents from db2diag.log. It doesn't tell me (with my limited knowledge) anything other than the -1767 error code. 2014-12-04-11.46.29.565620-300 I228560E692 LEVEL: Event PID : 32654 TID : 140560786515712 PROC : db2sysc 0 INSTANCE: db2insh1 NODE : 000 DB : ONEVWHAD APPHDL : 0-200 APPID: *LOCAL.db2insh1.141204164629 AUTHID : DB2INSH1 HOSTNAME: rslvm20.dub.usoh.ibm.com EDUID : 22 EDUNAME: db2agent (ONEVWHAD) 0 FUNCTION: DB2 UDB, base sys utilities, sqleCalculateDbHeaps, probe:70 MESSAGE : RLMS - DB Memory Set for Resident Member DATA #1 : String, 10 bytes totalBytes DATA #2 : unsigned integer, 8 bytes 236519424 DATA #3 : String, 11 bytes dbHeapBytes DATA #4 : unsigned integer, 8 bytes 164298752 2014-12-04-11.46.29.594451-300 I229253E673 LEVEL: Error PID : 32654 TID : 140560786515712 PROC : db2sysc 0 INSTANCE: db2insh1 NODE : 000 DB : ONEVWHAD APPHDL : 0-200 APPID: *LOCAL.db2insh1.141204164629 AUTHID : DB2INSH1 HOSTNAME: rslvm20.dub.usoh.ibm.com EDUID : 22 EDUNAME: db2agent (ONEVWHAD) 0 FUNCTION: DB2 UDB, data protection services, sqlpPrepareAndValidateEnvForHadr, probe:540 MESSAGE : ZRC=0x8010006D=-2146434963=SQLP_RC_CA_BUILT "SQLCA has been built and saved in component specific control block." DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes -1767 2014-12-04-11.46.29.594815-300 I229927E600 LEVEL: Error PID : 32654 TID : 140560786515712 PROC : db2sysc 0 INSTANCE: db2insh1 NODE : 000 DB : ONEVWHAD APPHDL : 0-200 APPID: *LOCAL.db2insh1.141204164629 AUTHID : DB2INSH1 HOSTNAME: rslvm20.dub.usoh.ibm.com EDUID : 22 EDUNAME: db2agent (ONEVWHAD) 0 FUNCTION: DB2 UDB, data protection services, sqlpinit, probe:1050 MESSAGE : ZRC=0x8010006D=-2146434963=SQLP_RC_CA_BUILT "SQLCA has been built and saved in component specific control block." 2014-12-04-11.46.29.595098-300 I230528E936 LEVEL: Severe PID : 32654 TID : 140560786515712 PROC : db2sysc 0 INSTANCE: db2insh1 NODE : 000 DB : ONEVWHAD APPHDL : 0-200 APPID: *LOCAL.db2insh1.141204164629 AUTHID : DB2INSH1 HOSTNAME: rslvm20.dub.usoh.ibm.com EDUID : 22 EDUNAME: db2agent (ONEVWHAD) 0 FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:9831 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1767 sqlerrml: 1 sqlerrmc: 1 sqlerrp : sqlpPrep sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: DATA #2 : Boolean, 1 bytes false ------ Here are the config values for HADR on my standby, if they matter: UPDATE DB CFG FOR ONEVWHAD USING HADR_LOCAL_HOST rslvm20.dub.usoh.ibm.com HADR_LOCAL_SVC DB2_db2insh1_hadr HADR_SYNCMODE NEARSYNC HADR_REMOTE_HOST rslvm19.dub.usoh.ibm.com HADR_REMOTE_SVC DB2_db2insh1_hadr HADR_REMOTE_INST db2insh1 And on the primary: UPDATE DB CFG FOR ONEVWHAD USING HADR_LOCAL_HOST rslvm19.dub.usoh.ibm.com HADR_LOCAL_SVC DB2_db2insh1_hadr HADR_SYNCMODE NEARSYNC HADR_REMOTE_HOST rslvm20.dub.usoh.ibm.com HADR_REMOTE_SVC DB2_db2insh1_hadr HADR_REMOTE_INST db2insh1 BTW, I think the restore is working, because I can see the data from the primary in the tables in the standbay.

DC1: we have two DB servers (DB1 and DB2) on top of WSFC DC2: 1 DB server (standalone DB machine). I have configured SQL Always-On on DC1 from DB1 to DB2. Now I have to sync/replicate data from DC1-DB1-->DC2-DB3 or DC1-DB2-->DC2-DB3. Please advise how to achive this. Customer is not ready to provide one more DB box on DC2, if i get one more DB machine on DC2, i can use Distributed SQL Always-on for sync from DC1 to DC2 on top of two WSFC's. I am using MS SQL 2017 Ent edition.