I am running two Windows Servers 2012 R2 with SQL Server 2014 Standard Edition. I have strange issue on mirroring. I configured mirroring on for 15 databases, no Witness server. The Principal server show all database is Principal, Synchronized and the Mirror is Mirror, Synchronized/Restoring. When I open Database Mirror Monitor I see Role Principal is in Mirroring State Synchronized but the Mirror's Role is not connected. I have fail over in both directions and the database came up on the mirror server normally. data is replicating for troubleshooting purpose I have done below steps. 1. I have seen in error log in principle nothing is related to mirroring traced I can see most of the errors in principle are Error: 18456, Severity: 14, State: 38. 2. I have seen errors in mirroring server for all the databases are Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://server.company.com:5022'. Error: 1474, Severity: 16, State: 3. I have tried to resolve by stop and start the endpoint its was not resolved. 4. I have tried pause and resume it is also not resolved. 5. I have verified TCP/IP it is enabled on both the servers. 6. I have tried to configure alias it is also not resolve. 7. I have telnet from each other, it seems have no communication issues 8. I have verified connect rights to domain level same service account for both principle and mirror. 9. Earlier it is SQL server 2014 sp2 ,today I have updated with latest service sp3 even though still I can see same in mirror monitor Not connected to mirror. Please help on this issue, how to resolve this?

I have read the SQL Server 2022 Licence Guide with great care. As far as I can tell, the following is a perfectly valid licence configuration if you have **two licences and Software Assurance**. Assume that I am talking about the same AG throughout. |Server Name | Location | Setup | Covered by Licence | |:---- |------:| -----:| -----:| |Prod Primary | Main data centre | Production Primary, hosting exactly one AG | Licence A - Primary | |Prod HA | Main data centre | Automatic failover AG replica, synchronously replicated | Licence A - Free HA server | |Prod Manual Failover | Main data centre | Manual failover AG replica, asynchronously replicated | Licence A - Free DR server| | DR Readable | DR site | Readable AG secondary, failover/replication don't matter | Licence B | |DR HA | DR site | Automatic failover AG replica, synchronously replicated | Licence B - Free HA server | |DR Manual Failover | DR site | Manual failover AG replica, asynchronously replicated | Licence B - Free DR server| I find it surprising that this seems allowed. **Am I correct that it is allowed?** Obviously, Microsoft has the final say on anything like this. However, what I'm describing here *must* be so common that the professionals here have either seen it before or already know why it does not work.

I'm very new to Galera Cluster and is exploring a potential setup with reasonable resiliency to node failure and network failure. Looking at the very bottom part of this documentation , the **Weighted Quorum for a Primary and Secondary Site Scenario** is quite promising. For ease of reading, I've extracted the setup from the document as follows: > When configuring quorum weights for primary and secondary sites, use > the following pattern: > > Primary Site: > node1: pc.weight = 2 > node2: pc.weight = 2 > > Secondary Site: > node3: pc.weight = 1 > node4: pc.weight = 1 > > Under this pattern, some nodes are located at the primary site while > others are at the secondary site. In the event that the secondary site > goes down or if network connectivity is lost between the sites, the > nodes at the primary site remain the Primary Component. Additionally, > either node1 or node2 can crash without the rest of the nodes becoming > non-primary components. But there seems to be two drawbacks: 1. If there are two failed nodes and one of them happened to be on the primary site, the quorum will be <= 50% and the remaining two nodes will become non-primary components. 2. Despite pc.weight is a dynamic option that can be changed while the server is running, flipping between primary site and secondary site requires modification on all nodes, which is a bit troublesome. So I've come up with another idea in mind - leave the weight as 1 for all nodes, and in the primary site add a Galera Arbitrator. In this case: - The primary site will remain the Primary Component on network issue, just like the original setup. - The cluster still functions even if two nodes failed. - Flipping between primary and secondary site just require a move of the Galera Arbitrator. May I know if there's anything wrong with my idea, or if there will be any practical difficulties? Appreciate if you can share your thoughts with me.

Our real-time communication application relies on database access, and it cannot be disconnected from the DB when running. Recently our customer’s DB maintenance team notified us that the DB will be occasionally disconnected from the app, possibly also during application working hours - due to security upgrade reasons. The blackout gap can be c.a. 2-minute. The question is: Is it possible with SQL Server Always On setup (this is what they have) to maintain **uninterrupted** DB access (from application perspective) during upgrading nodes of the SQL cluster ? Our understanding is that SQL Server **Always On** setup (as the name indicates) should be always available, regardless of a single node failure, or in case of intentionally executed upgrade on subsequent individual nodes (even if taking these offline, if required). We believe this should be possible with some kind of setup. Or alternatively does the “Always” mean “**almost** always” (with e.g. 2 minute time required to fail over to some standby instance, etc..) ? Can anyone confirm possibility to update software of **individual nodes** of the cluster (Windows Updates + SQL Server engine updates) one-by-one, eventually upgrading all nodes – with maintaining uninterrupted database access by the application during this process ? Could anyone share a link to documentation or article describing how to configure this capability? (We plan to share these links with the DB maintenance team).

1.)According to brent ozar's HA and DR chart ,for Failover Cluster Instance - SQL Server minimum version supported and SQL Server minimum edition required is given as 'any'. Does that mean it works with sql express? or am I misinterpreting the meaning of 'any'? According to microsoft it doesn't work for sql express. 2.)Same chart also says that sql server Replication can automatically failover. Here is what I found . How does the automatic failover happen in case of replication if it is possible?

We have setup a 2 node Synchronous AG with automatic failover to provide HA for our ERP application (Dynamics GP) The GP desktop client loads a number of shared reports and dictionaries from a file share currently located on one of the database servers. In order to provide complete high availability this share needs to be available in the event of a failure of the primary replica. At first I thought I would just copy the folder and setup shares on the secondary replica but realized SMB is not accessible through the AG listener. My second thought was to move the share to another location however this still introduces a single point of failure and defeats the purpose of HA. My only 2 real choices are to somehow add the folder as a resource to the cluster, however the 2 nodes are virtual machines and creating a shared SAN disk would be complex Or a messier option would be to create a task that checks the server name and in the event of a failover would update DNS to direct clients via UNC to the correct server share. What would be the recommended approach for this?

I am a newbie to this Replication concept and I am trying to build some scripts to automate our Build Process where we will be disabling/re-enabling Replication on a regular basis. **Background:** We have an Application Database Server which is on High Availability (Primary & Secondary Nodes). Our Staging Database Server will be communicating with the HA Group either through a Load Balancer or Listener. **Requirement:** We would like to enable Replication at the Table level and not the Database level from HA Group to the Staging Server as we are only interested in few tables. Is this even possible? If so, I am looking for some scripts (from your Toolbox, if you have any) to create Publisher, Distributor, and Subscriber and then disable/re-enable them on a regular basis during our Build process. Note: This would be more of a "Pull" and I am looking for Scripts that can be executed from the HA Group as part of the Build Process. Your help with this is very much appreciated. Thanks!

We are using PostgreSQL version 12.1. Currently we have high availability and automatic failover using Patroni and it's working fine. Now we are planning for disaster recovery and we are planning to use WAL-G for this. We read some article to know that on which machine we should install WAL-G master, slave or both? But couldn't get a clear idea. Can you please help us to know on which machine should we set up WAL-G?

My organization is planning to implement a high-availability PostgreSQL cluster using Patroni and etcd. However, we only have two data center sites available, which makes deploying a standard 3-node etcd cluster across separate failure domains challenging. We understand that running only 2-node etcd cluster increases the risk of split-brain or unavailability if one site becomes unreachable, due to the lack of a quorum. to address this, we come with the following topology: DC (Primary Site): - 192.168.30.80: PostgreSQL node running Patroni (initial master) - 192.168.30.83: etcd node DRC (Disaster Recovery Site): - 192.168.30.81: PostgreSQL node running Patroni (replica) - 192.168.30.82: backup etcd node each site runs a single-node etcd cluster, we have tested that failover still works in this setup, we use etcd mirror maker feature to continuously relay key creates and updates to a separate cluster in the DRC. We then use keepalived to manage a floating IP between the etcd clusters, which is used by Patroni on both nodes to access etcd. My questions are: - What are the risks are involved in running this kind of setup? - Would it be better to add a lightweight third etcd node in separate site (e.g., the cloud) to form a proper quorum?

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 trying to ease the monitoring of availability groups across multiple SQL servers. Came across few dmvs which got me the data. But cannot figure out some important columns which i am seeing in SSMS AG Dashboard report. How can i get this data via SQL query. We cannot use PS unfortunately as its blocked to get data remotely Columns in SSMS dashboard which i cannot find in dmv are below. Do we know from where these metrics are getting populated? > Estimate Data Loss (seconds) > > Estimated Recovery Time (seconds) > > Synchronization Performance (seconds) > > Issues: > > Connection state > > Last connection error no:

I am a newbie to SQL Server. I got an error when trying to truncate the database: >Database can not shrink until all secondaries have moved past the point where the log was added. When I check the status of the secondary on the primary machine (Always On High Availability Dashboard), it has >heath: good and >Synchronization state: Synchronizing. But the status of database on secondary is NULL not ONLINE. When I check on the Always On High Availability of the Secondary machine, it's state is No data available on secondary replicas. **Question:** What should I do and what is going on with the database..? (I want to truncate because the free space is no more enough. I use E:\ for all databases file has used 421.5Gb and free 78.5Gb.) Here is the nearly I check the status wiht the query below with two result on primary machine(pic 1) and secondary machine(pic 2):

SELECT d.name, r.replica_server_name, ars.role_desc, ars.operational_state_desc,
		drs.synchronization_state_desc, ars.connected_state_desc,
		drs.last_hardened_lsn, d.log_reuse_wait_desc
FROM sys.availability_databases_cluster AS adc 
JOIN sys.databases AS d ON adc.group_database_id = d.group_database_id 
JOIN sys.dm_hadr_database_replica_states AS drs ON d.database_id = drs.database_id 
JOIN sys.availability_replicas AS r ON drs.replica_id = r.replica_id 
JOIN sys.dm_hadr_availability_replica_states ars ON r.replica_id = ars.replica_id

I have tried suspending, off and re-join the secondary replica databases on the secondary machine (APEX-SQL2) but nothing has change... >Additionally, I ran 3 query below: *Thank you for reading. Pls help..*

We have a wordpress environment however this is not a wordpress question. Our scenario is that front Ends connect to a MYSQL backend (MYSQL VM1) which is replicated to a backup vm (MYSQL VM2). The replication is Master - Master. What we're looking for is a MYSQL HA cluster with automatic failover where if VM1 dies / is unresponsive, connection fails over to VM2. Anything you'd recommend ?

I've configured a MySQL InnoDB Cluster for high availability with two servers on a Windows system (Server 1: h01xxxxxxxxx1, IP: 10.xxx.xx.61, Server 2: h01xxxxxxxxx2, IP: 10.xxx.xx.71), running smoothly under the cluster name 'H01xxxxxE.'. When one instance goes down, the other takes charge and becomes primary. Here's my current cluster status:

**MySQL  h01xxxxxxxx1:3306 ssl  JS > cluster.status();**
{
    "clusterName": "H01xxxxxE",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "h01xxxxxxxx1:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "h01xxxxxxxx1:3306": {
                "address": "h01xxxxxxxx1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.34"
            },
            "h01xxxxxxx2:3306": {
                "address": "h01xxxxxxx2:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.34"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "h01xxxxxxx1:3306"
}

To enhance the high availability of my setup, I'm now seeking to ensure the availability of my MySQL Router. My objective is to create a robust, high-availability setup by installing a second MySQL Router on the second instance. The aim is to have them work together, monitoring each other's health, and automatically taking over if one router fails. After doing some research, I found a potential solution: clustering the routers together. Here's what I've done so far: 1. Ensured that MySQL Routers are correctly installed and running on each instance. 2. Configured each router with its own unique configuration file (mysqlrouter.conf). Here are snippets of the important parameters: **First instance**

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=C:/ProgramData/MySQL/MySQL Router/log
runtime_folder=C:/ProgramData/MySQL/MySQL Router/run
data_folder=C:/ProgramData/MySQL/MySQL Router/data
keyring_path=C:/ProgramData/MySQL/MySQL Router/data/keyring
master_key_path=C:/ProgramData/MySQL/MySQL Router/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=C:/ProgramData/MySQL/MySQL Router/data/state.json
client_ssl_cert=C:/ProgramData/MySQL/MySQL Router/data/router-cert.pem
client_ssl_key=C:/ProgramData/MySQL/MySQL Router/data/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
unknown_config_option=error

[logger]
level=INFO

[metadata_cache:bootstrap]
cluster_type=gr
router_id=1
user=mysql_router1_zw288yxxxxx
metadata_cluster=H01xxxxxE
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0


[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://H01xxxxxE/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://H01xxxxxE/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

**Second instance**

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=C:/ProgramData/MySQL/MySQL Router/log
runtime_folder=C:/ProgramData/MySQL/MySQL Router/run
data_folder=C:/ProgramData/MySQL/MySQL Router/data
keyring_path=C:/ProgramData/MySQL/MySQL Router/data/keyring
master_key_path=C:/ProgramData/MySQL/MySQL Router/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=C:/ProgramData/MySQL/MySQL Router/data/state.json
client_ssl_cert=C:/ProgramData/MySQL/MySQL Router/data/router-cert.pem
client_ssl_key=C:/ProgramData/MySQL/MySQL Router/data/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
unknown_config_option=error

[logger]
level=INFO

[metadata_cache:bootstrap]
cluster_type=gr
router_id=2
user=mysql_router2_a5sng6xxxxxx
metadata_cluster=H01xxxxxE
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6450
destinations=metadata-cache://H01xxxxxE/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://H01xxxxxxE/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

Each router needs a unique 'router_id' and 'user'. The 'metadata_cluster' in both routers should match the cluster name, which is "H01MYSQLE." In the [routing:bootstrap_rw] section, the 'destinations' line should also match the cluster name. The 'bind_port' in [routing:bootstrap_rw] must be unique port number and not used by other routing sections. These configurations ensure the routers collaborate effectively to provide high availability for database connections." Now, I've reached a point where I need to test failover in my setup to check if it's working. But before that, I needed to know which MySQL Router is currently the primary one. To identify the primary router, I used the command “SELECT @@global.read_only”. Here's the problem: When I connect directly to the MySQL instances without using routers, I correctly see one instance as primary (read-write) and the other as not primary (read-only). **First Node:**

MySQL  h01xxxxxxxxx1:3306 ssl  SQL > SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.0003 sec)

**Second Node:**

MySQL  h01xxxxxxxxx2:3306 ssl  SQL > SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.0004 sec)

However, when I connect via the routers (e.g., h01xxxxxxxxx1:6446 for primary and h01xxxxxxxxx2:6450 for secondary), I see both as primary (read-write), which is not the expected behavior. **First Node:**

MySQL  h01xxxxxxxxx1:6446 ssl  SQL > SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.0008 sec)

**Second Node:**

MySQL  h01xxxxxxxxx2:6450 ssl  SQL > SELECT @@global.read_only;
+--------------------+


| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.0005 sec)

In a correctly functioning setup, I should observe different results for @@global.read_only depending on whether I connect to the primary or secondary router. What could be causing both routers to appear as primary (read-write) when connected via the routers? Is there a specific step, parameter, or configuration that I might have overlooked, preventing proper failover recognition and primary router identification? Are there alternative best practices and methods for achieving high availability with MySQL Routers on Windows.

Trying to mirror an on-premise SQL Server instance to EC2 instance, but not much information is available on this topic. The closest one I managed to find is: Implement SQL Server High Availability and Disaster Recovery on Amazon EC2 - Part 4 Has any one done this? If so is there any documentation available? My intention is to make the EC2 as a backup for the HA in case my on-premise DB crashes. Please note that this is not for AWS RDS, nor EC2 SQL Server to EC2 SQL Server; but on-premise SQL Server to AWS EC2 SQL Server. The DB version is not a constraint, it can be SQL Server 2017 too.

Our SSIs package running via SQL Agent Job on a dedicated Reporting server had been running fine since deployed more than 3 weeks ago. Due to performance issue we had to fail over the database to Secondary. Since we failed over the SQL Agent Job is failing with error in package indicating that the login failed for domain user (DOMAIN\etl.user. We also noticed this error: The login is from an untrusted domain and cannot be used wit Integrated authentication. Below is the visual of our Architecture and explanation: More details: 1. Connection string is set at the SQL Agent job and the connection is made using Listener. 2. The SQL Agent job is ran as Proxy user as we do not want the user to have SysAdmin privilege. 3. I have ensured that the domain login on secondary server exist. Anyone has any idea why the package would fail?

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the site database. When you install a new site, you can direct setup to use the availability group instead of a normal instance of SQL Server." They then note "Successful configuration and use of availability groups requires you to be comfortable with configuring SQL Server availability groups, and relies on documentation and procedures provided in the SQL Server documentation library." Currently, the best practice for creating fault tolerance and to avoid a single point of failure for the Configuration Manager Site Database is to install SQL replicas at each management point. I've watched several overview videos (from 2013) to get a better understanding of how AlwaysOn works. In the DBA community, is AlwaysOn a better solution for providing fault tolerance and avoiding a single point of failure over SQL replicas? Full discloser: I'm learning Configuration Manager and have very basic knowledge of SQL Server so any advice you have or suggestions would be greatly appreciated.

I have SQL Server on Azure VM which I want to replicate across a separate region. I will only be using 2 servers, each in a separate region. I have the following questions as I need to clear all doubts about this implementation 1. Since they are in separate region, i found in microsoft docs and several online document that an asynchronous commit is recommended, what if i try to use a synchronous commit so that i can implement automatic failover? Are there any possible issues with synchronous commit considering my scenario 2. I also know that to avoid split brain issues I will need to configure Azure cluster Cloud witness to achieve the odd quorum votes. Considering my scenario of just 2 servers in different region, which region is better to configure this cloud witness. 3. Since I am using SQL Server Azure VM will I need a load balancer and consdering the information on this site https://www.sqlservercentral.com/articles/configuring-sql-server-always-on-azure-serversiaas Do I really need a load balancer? Thanks for your assistance I have read Microsoft documents to see the requirements for I have further questions

Our production database is on 11.2.0.4. It needs to be upgraded to 19c. Can I setup physical standby which is at 19c and keep it in sync with primary ? Thereafter - during a weekend maintenance, the standby database will be designated switched over to 19c.

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