October 27, 2017 at 7:39 am
Hi,
Please help on below issues
I have registered my all server in CMS. Some of servers configured with Log Shipping. Now I need to check
1. which servers configured with log shipping in side of the server how many databases configured with log shipping.
2. databases status like(stand by or no recovery).
in single query i need to fetch above results using CMS.For Replication
1. which servers configured with Replication and there Databases
in single query i need to fetch results using CMS
Thanks & Regards,
Babu
October 27, 2017 at 8:23 am
You need to do this for one instance (please use instance, not server). Determine how to query those items. The CMS will send this query to multiple instances.
If you examine sys.databases in master, there is an is_published and is_subscribed flag you can query for each database. This will let you know about gross replication setup. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql
Log shipping is more complex because this is really just a normal backup and restore of the logs. You can certainly query for databases that are in restoring states, but that doesn't let you know the source. In fact, the only way I know of to query log shipping is to look in msdb for the log_shipping tables and then you'd have to piece the information back together. You can see the procedures or DMVs to query here: https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/monitor-log-shipping-transact-sql
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply