To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed. – when moving files on an Availability Group secondary.

Here's a little error that I stumbled across the other day when trying to migrate the data files of an Availability Group secondary node onto a nice new SAN. I started off by running the following command against the secondary server. ALTER DATABASE SQLUndercover MODIFY FILE (NAME = 'AG01', FILENAME = 'E:\DATA\AG01.mdf') There's nothing wrong... Continue Reading →

Jun 6, 2019 0

Is Your Availability Group ‘REALLY’ Ready To Failover?

You know what it's like, you need to fail your AG over but is it safe to fail over? Perhaps you've clicked on 'failover' for the AG and there's a little green tick and no data loss reported... Or maybe you've checked out sys.dm_hadr_database_replica_cluster_states and 'is_failover_ready' is reporting a 1. So, you're cool to failover,... Continue Reading →

Apr 29, 2019 1

Availability Groups: When Your Synchronous Secondary isn’t Synchronous

During a recent DR scenario I came across a slight misinterpretation in how availability groups with replicas in synchronous commit behave. How An Availability Group With A Synchronous Secondary Should Behave Under Normal Circumstances Normally, if we've got a secondary replica in synchronous commit mode, our primary can't commit any transactions until that transaction has... Continue Reading →

Jan 14, 2019 1

‘Object Reference Not Set to an Instance of an Object’ When Failing Over an Availability Group Using SSMS <= 17.5

Here's a quickie that I've just stumbled across. I've just tried to initiate a manual failover of an availability group and received the following error. After a spot of head scratching and thinking that there was something wrong with my AG setup, it turns out that there's a bug in SSMS. I was running SSMS... Continue Reading →

Mar 29, 2018 1

TempDB Filling Up On Secondary Replicas

Have you ever had an issue where TempDB was filling up on your secondary replicas? Do those secondaries happen to be read only replicas? When I have an issue with tempdb filling up the first thing that I usually do is try to figure out exactly what the space has been allocated to. You can... Continue Reading →

Mar 21, 2018 2

UNDERCOVER TOOLBOX: sp_WhatsMyAG

Here's a quick one for you. If you happen to be managing SQL Servers with a large number of databases and availability groups, it can sometimes be difficult to keep track of which database belongs to which availability group. sp_WhatsMyAG will tell you just that. You can either provide it with the database name and... Continue Reading →

Mar 7, 2018 1

Creating a SQL Server Test Lab On Your Workstation – Part Three, Creating a Windows Failover Cluster and a SQL Server Availability Group

Welcome along to the third part of our series 'Creating a SQL Server Test Lab On Your Workstation'. If you've followed along with the first two parts, you should have a domain setup and a SQL Server joined to it. In this part, we're going to look at building a cluster and creating an Availability... Continue Reading →

Jan 24, 2018 5

UNDERCOVER TOOLBOX: sp_AGReconfigure – Manage Always on Sync & Failover settings from a single stored procedure.

08 Jan 2018 - V1.1 released - Details here Most of the time the goto is to use the GUI which is a nice graphical way of doing it which can make configuring the settings somewhat easier , but this comes with a cost - the cost being time! I have lost count of the... Continue Reading →

Dec 8, 2017 5

Failed to perform AlwaysOn Manual Failover using the wizard – Availability-group DDL Operations are permitted only when you are using the master database .

Routine maintenance rolls around and it's time to failover your AG's to new Primary's to allow for patching and restarts... the weapon of choice will be the SSMS Failover wizard - and why not its does the job nicely and give us some nice visuals as it progresses through each failover. On this... Continue Reading →

Oct 4, 2017 0

Up ↑

Tag: Availability Groups

To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed. – when moving files on an Availability Group secondary.

Is Your Availability Group ‘REALLY’ Ready To Failover?

Availability Groups: When Your Synchronous Secondary isn’t Synchronous

‘Object Reference Not Set to an Instance of an Object’ When Failing Over an Availability Group Using SSMS <= 17.5

TempDB Filling Up On Secondary Replicas

UNDERCOVER TOOLBOX: sp_WhatsMyAG

Creating a SQL Server Test Lab On Your Workstation – Part Three, Creating a Windows Failover Cluster and a SQL Server Availability Group

UNDERCOVER TOOLBOX: sp_AGReconfigure – Manage Always on Sync & Failover settings from a single stored procedure.

Failed to perform AlwaysOn Manual Failover using the wizard – Availability-group DDL Operations are permitted only when you are using the master database .

David Fowler

Adrian Buckman

Archives

Follow me on Twitter

Recent Posts From The Undercover DBAs