on May 31, 2017
Collecting perfmon counters from SQL Server databases where you are using database mirroring is a little tricky
The counters won’t behave “normally” until after you set up mirroring for a database.
Before you set up mirroring, the counters are only half there
Before you set up mirroring, you can typically see the “Database Mirroring” object and individual counter names in Performance Monitor in Windows. But when you add the counters and click OK, they don’t show up in your graph!
Also, when you run this query against your SQL Server instance, you get zero rows back:
select *
from sys.dm_os_performance_counters
where object_name like '%Database Mirroring%'
GO
To get the counters to work, you need to set up mirroring… then restart Windows
Here’s the bummer: after setting up database mirroring, I’ve never been able to query the counters or see the instances in perfmon without restarting Windows.
I’ve tried lots of things: restarting SQL Server? Tried that. Reloading the perf counters and restarting services (as described in this SQL PFE post)? Tried that too!
On multiple versions of SQL Server and Windows, I’ve had to restart the whole computer. (Most recently I’ve tested this with SQL Server 2008R2 on Windows Server 2008R2, and SQL Server 2016 on Windows Server 2012 R2.)
There might well be a very specific dance you can do to get these counters to show without a full restart, but I haven’t found it! If you know the magic formula, please tell in the comments.
The good news: Availability Group counters are built differently
For availability groups, you can add or query the SQLServer: Availability Replica and SQLServer:Database Replica counters and collect “Total” before you ever set up an AG, or even enable AlwaysOn High Availability in the SQL Server configuration manager.