Script: Monitoring Primary/Secondary Replicas in SQL Server Availability Group

Submitted over 1 year ago

Monitoring a production system, especially when it involves a very read-heavy read-only replica, I am always keeping an eye on the redo-queue and the redo-rate for lag in synchronization and health of the AG. The UI Dashboard provided through SSMS is nice, but the auto-refresh rate is... uh not rapid enough.

Here's a simple script that I use many many times a day.

SELECT 
    ar.replica_server_name, 
    adc.database_name,
    (drs.redo_queue_size / drs.redo_rate) as estimated_recovery_time_seconds,
    drs.redo_queue_size, 
    drs.redo_rate,
    ag.name AS ag_name,
    drs.is_local, 
    drs.is_primary_replica, 
    drs.synchronization_state_desc, 
    drs.is_commit_participant, 
    drs.synchronization_health_desc, 
    drs.recovery_lsn, 
    drs.truncation_lsn, 
    drs.last_sent_lsn, 
    drs.last_sent_time, 
    drs.last_received_lsn, 
    drs.last_received_time, 
    drs.last_hardened_lsn, 
    drs.last_hardened_time, 
    drs.last_redone_lsn, 
    drs.last_redone_time, 
    drs.log_send_queue_size, 
    drs.log_send_rate, 
    drs.filestream_send_rate, 
    drs.end_of_log_lsn, 
    drs.last_commit_lsn, 
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
    ON drs.group_id = adc.group_id AND 
    drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
    ON drs.group_id = ar.group_id AND 
    drs.replica_id = ar.replica_id
ORDER BY 
    ag.name, 
    ar.replica_server_name, 
    adc.database_name;

Hope this helps out others that resort to repeatedly clicking the refresh button on the AG dashboard. I'd rather resort to:

F5KeySmashing

Cheers,

Phil

16 Comments



Ann Weaver

I came across your Phil Schwartz on the Web website and wanted to let you know that we have decided to open our POWERFUL and PRIVATE website traffic system to the public for a limited time! You can sign up for our targeted traffic network with a free trial as we make this offer available again. If you need targeted traffic that is interested in your subject matter or products start your free trial today: http://tdil.co/3p Unsubscribe here: http://priscilarodrigues.com.br/url/11

11 days ago

Ann Weaver

I came across your Phil Schwartz on the Web website and wanted to let you know that we have decided to open our POWERFUL and PRIVATE web traffic system to the public for a limited time! You can sign up for our targeted traffic network with a free trial as we make this offer available again. If you need targeted traffic that is interested in your subject matter or products start your free trial today: http://v-doc.co/nm/jkfq0 Unsubscribe here: http://pcgroup.com.uy/2a

about 1 month ago

Ann Weaver

This is a message to the Phil Schwartz on the Web webmaster. Your website is missing out on at least 300 visitors per day. Our traffic system will dramatically increase your traffic to your website: http://pcgroup.com.uy/15 - We offer 500 free targeted visitors during our free trial period and we offer up to 30,000 targeted visitors per month. Hope this helps :) Unsubscribe here: http://priscilarodrigues.com.br/url/11

about 1 month ago

Ann Weaver

I came across your Phil Schwartz on the Web website and wanted to let you know that we have decided to open our POWERFUL and PRIVATE web traffic system to the public for a limited time! You can sign up for our targeted traffic network with a free trial as we make this offer available again. If you need targeted traffic that is interested in your subject matter or products start your free trial today: http://pcgroup.com.uy/15 Unsubscribe here: http://priscilarodrigues.com.br/url/11

about 2 months ago

Ann Weaver

I came to your Phil Schwartz on the Web page and noticed you could have a lot more traffic. I have found that the key to running a website is making sure the visitors you are getting are interested in your subject matter. We can send you targeted traffic and we let you try it for free. Get over 1,000 targeted visitors per day to your website. Start your free trial: http://pcgroup.com.uy/15 Unsubscribe here: http://pcgroup.com.uy/2a

2 months ago

Sarah Hughes

This is a message to the Phil Schwartz on the Web webmaster. Your website is missing out on at least 300 visitors per day. Our traffic system will dramatically increase your traffic to your site: http://url.laspas.gr/hr - We offer 500 free targeted visitors during our free trial period and we offer up to 30,000 targeted visitors per month. Hope this helps :) Unsubscribe here: http://acortarurl.es/97

3 months ago

Chelsea Wallace

I came across your Phil Schwartz on the Web website and wanted to let you know that we have decided to open our POWERFUL and PRIVATE website traffic system to the public for a limited time! You can sign up for our targeted traffic network with a free trial as we make this offer available again. If you need targeted traffic that is interested in your subject matter or products start your free trial today: http://magc.co/7fe Unsubscribe here: http://acortarurl.es/97

3 months ago

Chelsea Wallace

I came to your Phil Schwartz on the Web page and noticed you could have a lot more traffic. I have found that the key to running a website is making sure the visitors you are getting are interested in your subject matter. We can send you targeted traffic and we let you try it for free. Get over 1,000 targeted visitors per day to your website. Check it out here: http://trucri.me/u5za1 Unsubscribe here: http://acortarurl.es/97

3 months ago

Elisa Brown

I discovered your Phil Schwartz on the Web page and noticed you could have a lot more traffic. I have found that the key to running a website is making sure the visitors you are getting are interested in your subject matter. We can send you targeted traffic and we let you try it for free. Get over 1,000 targeted visitors per day to your website. Start your free trial: http://pcgroup.com.uy/15

4 months ago

Elisa Brown

I discovered your Phil Schwartz on the Web page and noticed you could have a lot more traffic. I have found that the key to running a website is making sure the visitors you are getting are interested in your subject matter. There is a company that you can get traffic from and they let you try it for free. I managed to get over 500 targeted visitors to day to my website. Visit them today: http://shorturl.van.ee/h

4 months ago

4rx

You're a really valuable web site; couldn't make it without ya! http://bit.ly/2p7LTKb

4 months ago

4rx

thank so considerably for your website it assists a whole lot. http://bit.ly/2p7LTKb

4 months ago

Tami Sandoval

Hello my name is Tami Sandoval and I just wanted to drop you a quick note here instead of calling you. I discovered your Phil Schwartz on the Web website and noticed you could have a lot more traffic. I have found that the key to running a popular website is making sure the visitors you are getting are interested in your website topic. There is a company that you can get keyword targeted visitors from and they let you try the service for free for 7 days. I managed to get over 300 targeted visitors to day to my website. http://maak.me/s0p1j

5 months ago

Deanna Brady

Hi my name is Deanna Brady and I just wanted to send you a quick message here instead of calling you. I discovered your Phil Schwartz on the Web website and noticed you could have a lot more hits. I have found that the key to running a successful website is making sure the visitors you are getting are interested in your subject matter. There is a company that you can get keyword targeted traffic from and they let you try their service for free for 7 days. I managed to get over 300 targeted visitors to day to my website. http://www.inflightvideo.tv/a/a9

5 months ago

Evelyn Serrell

This is a memo to the webmaster. I discovered your Phil Schwartz on the Web page by searching on Google but it was hard to find as you were not on the front page of search results. I know you could have more traffic to your website. I have found a site which offers to dramatically improve your website rankings and traffic to your website: http://hud.sn/6sk5 I managed to get close to 500 visitors/day using their services, you can also get a lot more targeted traffic from search engines than you have now. Their service brought significantly more visitors to my website. I hope this helps!

9 months ago

Alisa

Hello my name is Alisa and I just wanted to send you a quick message here instead of calling you. I discovered your Phil Schwartz on the Web website and noticed you could have a lot more hits. I have found that the key to running a popular website is making sure the visitors you are getting are interested in your website topic. There is a company that you can get keyword targeted visitors from and they let you try the service for free for 7 days. I managed to get over 300 targeted visitors to day to my site. http://www.prestonkincaid.com/link/o - Unsubscribe here: http://www.prestonkincaid.com/link/p

10 months ago

Add a comment: