This is going to be a bit of a quick post but one about something that I see coming up time and time again on SQL Undercover's search terms. How do I find an availability group's primary server? A while back, Adrian put together a bunch of queries for Always On Availability Groups, 7 ways... Continue Reading →
If you're running availability groups it may seem like a great idea to offload your read only reporting workloads to a secondary. There are a couple of ways that you could do that, you could use read only routing or simply just point those reports directly at a read only secondary (read only routing is... Continue Reading →
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 →
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 →
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 →
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 →
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 →
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 →
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 →
