Get an overview report on your log shipping setups

Linchi Shea — Tue, 22 Dec 2009 02:45:00 GMT

[Updated Dec 23 to include a T-SQL script at the bottom of the post].

You may have an environment where log shipping is set up on various databases between various servers. Would it be handy to have an overview report highlighting which database is being log shipped where? Even if you have some kind of record to keep track of the databases that are being log shipped, wouldn’t it be nice to have an automated means to help validate that record?

By an overview report, I mean a three-column report that lists, for each log-shipped database, its source server, its name, and all the secondary servers.

Obviously, how to accomplish this depends on how your log shipping is set up. Let’s assume that you are using the native log shipping feature of SQL Server 2005/2008. In that case, one of the easiest approaches to collecting the log shipping information would be to run system stored procedures such as sp_help_log_shipping_monitor.

But which servers should you run sp_help_log_shipping_monitor? Well, for the report to be complete, you should just run it against all your SQL 2005/2008 instances. Or else, how do you know you are not missing some log shipped databases.

So before you can scan your environment for log shipped databases, you would, at the bare minimum, have to first provide a list of the SQL Server instances in your environment. Then, it’s just a matter of running sp_help_log_shipping_monitor against each SQL instance on the list, sort out, and combine the results. If a SQL instance contains a database that is being log shipped (regardless where this is the primary or a secondary), sp_help_log_shipping_monitor will return a list of the log-shipped databases along with their primary and secondary SQL instances. If a SQL instance does not have any log-shipped database, sp_help_log_shipping_monitor does not return anything.

By gleaning and collating information from the results of running sp_help_log_shipping_monitor against all the SQL Server 2005/2008 instances in your environment, you can then construct a comprehensive picture of the log shipping configurations in your environment. The attached Perl script does precisely that.

Note that you can’t just download the Perl script and run it against your environment as is. I’ve left a key subroutine—getProdSQL()—unfinished, and you need to fill it with the logic particular to your environment. This usually is just a few lines of code that retrieve from a database the list of the names for the SQL Server instance from that you want to scan for log shipping. I have no way of writing the logic for such routine for you.

Before you can run the script, you also need to change a few SMTP mail related parameters so that they are correct in your environment. The SMTP email routine is included so that the script can email the extract log shipping overview report to you. This is not essential, but convenient.

[Added the following section on Dec 23]

If you have a table that stores the SQL2005/2008 instances in your environment, and you have a utility server that has a linked server configured for each of these SQL2005/2008 instances, you can also use the following simple T-SQL to scan for and retrieve the log shipping configurations in your environment:

if object_id('tempdb..#log_shipping_entries') is not null

drop table #log_shipping_entries

create table #log_shipping_entries (

primary_server sysname,

secondary_server sysname,

database_name sysname

)

if object_id('tempdb..#log_shipping_monitor') is not null

drop table #log_shipping_monitor

create table #log_shipping_monitor (

status bit NULL,

is_primary bit NULL,

server sysname NULL,

database_name sysname NULL,

time_since_last_backup int NULL,

last_backup_file nvarchar(500) NULL,

backup_threshold int NULL,

is_backup_alert_enabled bit NULL,

time_since_last_copy int NULL,

last_copied_file nvarchar(500) NULL,

time_since_last_restore int NULL,

last_restored_file nvarchar(500) NULL,

last_restored_latency int NULL,

restore_threshold int NULL,

is_restore_alert_enabled bit NULL

)

declare @prod_server sysname,

@sql nvarchar(2000)

declare prod_cr cursor

for select distinct ServerName

<… You need to fill in the rest of the query to retrieve the

Server names in your environment>

OPEN prod_cr

FETCH NEXT FROM prod_cr into @prod_server

WHILE @@FETCH_STATUS = 0

BEGIN

select @sql = N'EXEC [' +

@prod_server +

N'].master..sp_help_log_shipping_monitor'

truncate table #log_shipping_monitor

insert #log_shipping_monitor

EXECUTE (@sql)

insert #log_shipping_entries

select distinct t1.server as primary_server,

t2.server as secondary_server, t2.database_name

from (select distinct server, database_name

from #log_shipping_monitor

where is_primary = 1) as t1,

#log_shipping_monitor t2

where t1.database_name = t2.database_name

and t2.is_primary = 0

FETCH NEXT FROM prod_cr into @prod_server

end

close prod_cr

deallocate prod_cr

select * from #log_shipping_entries

Search results matching tags 'Automation', 'Perl', and 'Log shipping'

Get an overview report on your log shipping setups