Always ON

SQL Server Query Store Stuck in ERROR State: How to Fix it

by

A customer emailed me a couple of weeks ago with something that sounded, on the face of it, like a haunting. He’d been running ALTER DATABASE … SET QUERY_STORE = ON. The command succeeded. About ten seconds later, Query Store was off again. He’d tried this several times over several days and got the same … Read more

Always On vs. Read Scale Availability Groups

by

In this article we will try to understand differences of SQL Server Always On Availability Groups vs. Read Scale Availability Groups. Designing HA/DR Solution and Customer’s Question: Recently, we were working on designing SQL Server HA/DR database solution for one of our customer. After considering customer’s requirement, we suggested a multi subnet three node Always … Read more

Impact of domain change of servers in availability group

by

Recently I faced an issue where secondary replica was showing as disconnected in always on availability group.  Below troubleshooting was done to fix the issue. In summary, DBA should ensure to update endpoint URLs in availability group configuration if server’s domain is changed to avoid any issue. This does not require a downtime . Hope … Read more

Always On Secondary replica database in Synchronized/In Recovery mode

by

Recently while working on one alert, when I logged onto the server and connected SQL instance, noticed that on one of secondary replica in AlwaysOn availability group was having databases in “Synchronized/In Recovery” mode. This is not the usual status for secondary replica databases. Replica was configured in synchronous commit mode and databases were not … Read more

Fixing Application Latency and Timeout issues in Multi-Subnet Always ON SQL Server Setup

by

Issue Application Team complained about latency in connecting to database through listener name. They also complained about application taking more time to connect to database after Always ON Group failover. Sometimes they observed application timeouts as well. Underlying database was part of Multi-Subnet Always ON SQL Server. Application was connecting to database using Listener IP. … Read more

Always ON Group Failover not happening in SQL Server 2017

by

We had one activity planned which was related to windows hostname and SQL server name change. DB Servers Setup Information: It was a 2 node windows cluster, Node1 & Node2. SQL server Always On was configured on this setup. 4 Synchronous Always On Groups (AG1, AG2,AG3,AG4) were setup and Node1 was acting as Primary. Always … Read more

LOG_REUSE_WAIT_DESC shown as Availability_Replica even though DB is removed from AG

by

Issue: One of our customers have a AG server with 2 nodes and 4 databases added to availability group. He got an alert saying that log file is full for one of the bigger database.

Read more

Avoid deadlock scheduler situation in AlwaysOn setup

by

Scenario Kindly consider below AlwaysOn setup between three servers. Server A — Primary ReplicaServer B — Secondary synchronous commit replicaServer C — Secondary asynchronous commit replicaEach server is having 8 logical CPUs thus having 576 max worker threads.SQL instance is having 150 user databases across 5 AGs with 30 databases in each AG. With above … Read more

CDC job fails: Invalid object name ‘msdb.dbo.cdc_jobs’. [SQLSTATE 42S02] (Error 208)

by

Issue: In an always on setup, CDC jobs should be created on all the replicas of always on availability group. Sometime you may find that CDC capture and cleanup job is running successful on some of replicas but failing on other replicas with below error: Error message:Executed as user: Domain\user. Invalid object name ‘msdb.dbo.cdc_jobs’. [SQLSTATE … Read more

SQL Server Always On with Log-Shipping (Configuration and Considerations)

by

Scenario: Consider we have a primary data center where we want to configure HA solution using Always On availability group and want to configure log shipping to DR site as a DR solution. In this post we will discuss on how to configure log shipping for a database involved in AG and some limitations and … Read more