List instances hosting similarly named databases

,

Applies to SQL Server 2008 R2.
We have several servers hosting several operational data stores and I wanted a list of which server hosted which ODS (one is hosted per country, and there are currently 10 countries).  The way the servers are thought of is that the very first one that saw production is the "master", and the others scaled out to over time are the "slaves".  The slaves are numbered, for example "ABC_SLAVE01_DEF", "ABC_SLAVE02_DEF".  The servers are connected to via linked servers, and in turn the linked servers are connected to server aliases.
The script returns the name of the instance hosting the ODS, the linked server name (which matches the alias set up under SQL Server Configuration Manager), and the ODS database name.
Thanks to Irene Daniel for improving my original script by adding the test to only include databases that are online.
set nocount on;

declare @sqlcmds table
        (
         srvname nvarchar(128),
         sqlcmd varchar(1024)
        );
--
declare @sqlcmd_concat varchar(max) = '';
--
insert  into @sqlcmds
select  srvname,
        sqlcmd = 'select instance = (select name from ' + srvname + '.master.sys.servers where server_id = 0), linked_server = ''' + srvname
        + ''', dbnm = name from ' + srvname + '.msdb.sys.databases where name like ''%ODS%''' + ' and state_desc = ''ONLINE'''
from    master.dbo.sysservers
where    (srvname like '%[_]MASTER[_]%'
             or srvname like '%[_]SLAVE__[_]%');
--
select  @sqlcmd_concat += case @sqlcmd_concat
                            when '' then ''
                            else ' union all '
                          end + sqlcmd
from    @sqlcmds;
--
exec(@sqlcmd_concat);

Rate

2 (4)

Share

Share

Rate

2 (4)