2016-08-31

Data Wrangling: Linked Servers and the DBA

SQL Server Linked Server

In my previous post, I defended the use of linked servers for administrative purposes. Now let's look at some practical uses. The basic concept is to connect to the CMS, iterate through sys.servers, select data from the remote servers, then return the data for all linked servers as a single data set. Let's begin with an example that returns a list of databases on all of our linked server SQL instances.

DECLARE @TSql NVARCHAR(MAX) = '';

SELECT @TSql = @TSql + 'SELECT ''' + data_source + ''' AS LinkedServerName, name AS DatabaseName ' + CHAR(13) + CHAR(10) +
    'FROM [' + data_source + '].master.sys.databases d UNION ALL' + CHAR(13) + CHAR(10)
FROM sys.servers
WHERE data_source <> @@SERVERNAME
AND product = 'SQL Server'
ORDER BY data_source;

--Trim off last occurrence of UNION ALL' + CHAR(13) + CHAR(10)
SET @TSql = LEFT(@TSql, LEN(@TSql) - 12);

PRINT(@TSql);
EXEC (@TSql);

Here, we didn't even need a cursor. I built a long TSQL string by selecting from the sys.servers DMV. It's a "query that writes a query". The resulting query string has this pattern:

SELECT 'SQL-Instance-1' AS LinkedServerName, name AS DatabaseName 
FROM [SQL-Instance-1].master.sys.databases d UNION ALL
SELECT 'SQL-Instance-2' AS LinkedServerName, name AS DatabaseName 
FROM [SQL-Instance-2].master.sys.databases d UNION ALL
SELECT 'SQL-Instance-3' AS LinkedServerName, name AS DatabaseName 
FROM [SQL-Instance-3].master.sys.databases d UNION ALL
...
SELECT 'SQL-Instance-N' AS LinkedServerName, name AS DatabaseName 
FROM [SQL-Instance-N].master.sys.databases d
There is a total of "N" linked servers. The query selects from each one and combines the results via UNION ALL. Here's a sample of the output on my CMS:

SQL Server Linked Server - Results 1

What we've seen so far could be used as a template for other queries, such as a backup report, a database files report, etc. The code could be put in a stored procedure and run as needed for automated tasks. I've used similar code to retrieve data for an SSRS report.

Let's examine a problem you're likely to encounter by including @@SERVERNAME within the query:

DECLARE @TSql NVARCHAR(MAX) = '';

SELECT @TSql = @TSql + 'SELECT @@SERVERNAME AS ServerName, ''' + data_source + ''' AS LinkedServerName, name AS DatabaseName ' + CHAR(13) + CHAR(10) +
    'FROM [' + data_source + '].master.sys.databases d UNION ALL' + CHAR(13) + CHAR(10)
FROM sys.servers
WHERE data_source <> @@SERVERNAME
AND product = 'SQL Server'
ORDER BY data_source;

--Trim off last occurrence of UNION ALL' + CHAR(13) + CHAR(10)
SET @TSql = LEFT(@TSql, LEN(@TSql) - 12);

PRINT(@TSql);
EXEC (@TSql);
The resulting query string has this pattern:
SELECT @@SERVERNAME AS ServerName, 'SQL-Instance-1' AS LinkedServerName, name AS DatabaseName 
FROM [SQL-Instance-1].master.sys.databases d UNION ALL
SELECT @@SERVERNAME AS ServerName, 'SQL-Instance-2' AS LinkedServerName, name AS DatabaseName 
FROM [SQL-Instance-2].master.sys.databases d UNION ALL
SELECT @@SERVERNAME AS ServerName, 'SQL-Instance-3' AS LinkedServerName, name AS DatabaseName 
FROM [SQL-Instance-3].master.sys.databases d UNION ALL
...
SELECT @@SERVERNAME AS ServerName, 'SQL-Instance-N' AS LinkedServerName, name AS DatabaseName 
FROM [SQL-Instance-N].master.sys.databases d
And our output now looks like this:

SQL Server Linked Server - Results 2

Is the "ServerName" column what you expected? The @@SERVERNAME function is evaluated on the local/CMS server, not on the remote servers. This can be problematic and you'll see it for other functions like @@VERSION, CURRENT_TIMESTAMP, GETDATE(), SERVERPROPERTY(), etc. For those to return values relative to the remote linked servers, they must be "passed through". One way to do that is with OPENQUERY. Here is an example:

DECLARE @TSql NVARCHAR(MAX) = '';

SELECT @TSql = @TSql + 'SELECT TOP(1) *' +CHAR(13)+CHAR(10)+
    'FROM OPENQUERY([' + data_source + '],' +CHAR(13)+CHAR(10)+
    '''SELECT CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP], ' +CHAR(13)+CHAR(10)+
    'dec.local_net_address,' +CHAR(13)+CHAR(10)+
    'REPLACE(LEFT(@@VERSION, PATINDEX(''''% - %'''', @@VERSION)), ''''Microsoft SQL Server '''', '''''''') AS [@@VERSION],' +CHAR(13)+CHAR(10)+
    'SERVERPROPERTY(''''ProductLevel'''') AS ProductLevel, ' +CHAR(13)+CHAR(10)+
    'SERVERPROPERTY(''''Edition'''') AS Edition, ' +CHAR(13)+CHAR(10)+
    'SERVERPROPERTY(''''ProductVersion'''') AS ProductVersion,' +CHAR(13)+CHAR(10)+
    'CAST(SERVERPROPERTY(N''''MachineName'''') AS VARCHAR) AS MachineName' +CHAR(13)+CHAR(10)+
    'FROM sys.dm_exec_connections AS dec' +CHAR(13)+CHAR(10)+
    'WHERE dec.session_id = @@SPID;'') UNION ALL' + CHAR(13) + CHAR(10)
FROM sys.servers
WHERE data_source <> @@SERVERNAME
AND product = 'SQL Server'
ORDER BY data_source;

--Trim off last occurrence of UNION ALL' + CHAR(13) + CHAR(10)
SET @TSql = LEFT(@TSql, LEN(@TSql) - 12);

PRINT(@Tsql);
EXEC (@Tsql);
Below is the result. You can see the different times for CURRENT_TIMESTAMP, which reflects the different time zone settings for the remote servers. Other items vary too, indicating different SQL versions, product levels, editions, etc.

SQL Server Linked Server - Results 3

Advanced Techniques

This last example returns memory utilization info and throws in several new wrinkles. There's a temp table created on the CMS instance. It is populated with the names of the remote servers. Then we dynamically build an UPDATE query string to populate the major version number of each remote server. Next, a cursor is used to iterate through the temp table. Row-by-row, the temp table columns are updated via another dynamically created query string with OPENQUERY(). The inner OPENQUERY() query string may be different, depending on the version of SQL on the remote server. This is one way of dealing with linked servers representing multiple versions of SQL, for which a single TSQL syntax will not work.

CREATE PROCEDURE SSRS.SqlMemoryUtilization
/*
 Purpose: 
 Returns information about memory utilization for multiple SQL instances.
 
 Inputs: none

 History:
 07/18/2016 DMason Created
*/
AS
CREATE TABLE #Servers (
    InstanceName SYSNAME PRIMARY KEY,
    MajorVersion INT,
    [Max SQL Server Memory (MB)] INT,
    [Host Server Memory (GB)] NUMERIC(10, 2),
    [Page Life Expectancy] INT,
    [Page Reads/sec] NUMERIC(10, 2)
)
--Populate server names from linked servers.
INSERT INTO #Servers (InstanceName)
SELECT name InstanceName
FROM master.sys.servers 
WHERE name <> @@SERVERNAME
AND product = 'SQL Server'

--Populate the major version.
DECLARE @Tsql NVARCHAR(MAX) = ''
SELECT @Tsql = @Tsql +
    'UPDATE #Servers SET 
        MajorVersion = CAST(MajorVer AS INT)
        FROM OPENQUERY([' + InstanceName + '],
            ''
    DECLARE @MajorVer SMALLINT
    SET @MajorVer = CAST(PARSENAME(CAST(SERVERPROPERTY(''''ProductVersion'''') AS VARCHAR), 4) AS SMALLINT)
    SELECT @MajorVer AS MajorVer
            '')
        WHERE InstanceName = ''' + InstanceName + ''';' + CHAR(13) + CHAR(10)
FROM #Servers

EXEC sp_executesql @Tsql

DECLARE @Instance SYSNAME
DECLARE @MajorVer INT
DECLARE curInstance CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT InstanceName, MajorVersion FROM #Servers

OPEN curInstance
FETCH NEXT FROM curInstance INTO @Instance, @MajorVer

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Tsql = 
        'UPDATE #Servers SET 
            [Max SQL Server Memory (MB)] = CAST(MaxMemMB AS INT),
            [Host Server Memory (GB)] = CAST(HostServerMemory_GB AS NUMERIC(10, 2)),
            [Page Life Expectancy] = CAST(PageLifeExpectancy AS INT),
            [Page Reads/sec] = CAST(PgReadsPerSec AS NUMERIC(10, 2))
            FROM OPENQUERY([' + @Instance + '],
                ''

        DECLARE @HostServerMemory_GB NUMERIC(10, 2)
        DECLARE @Tsql NVARCHAR(MAX) = 
'

    IF @MajorVer >= 11    --SQL 2012 or higher
        SET @Tsql = @Tsql + '''''SELECT TOP(1) @Mem = CAST(ROUND(i.physical_memory_kb / 1024.0 / 1024 , 2) AS NUMERIC(10, 2))
    FROM sys.dm_os_sys_info i''''
        EXEC sp_executesql @Tsql, N''''@Mem NUMERIC(10, 2) OUTPUT'''', @HostServerMemory_GB output WITH RESULT SETS NONE'
    ELSE    --SQL 2008 R2 or less
        SET @Tsql = @Tsql + '''''SELECT TOP(1) @Mem = CAST(ROUND(physical_memory_in_bytes / 1024.0 / 1024 / 1024, 2) AS NUMERIC(10, 2))
    FROM sys.dm_os_sys_info''''
        EXEC sp_executesql @Tsql, N''''@Mem NUMERIC(10, 2) OUTPUT'''', @HostServerMemory_GB output'

SET @Tsql = @Tsql + '

--Memory, Page Life Expectancy, Page Reads/sec
SELECT c.value AS MaxMemMB, @HostServerMemory_GB HostServerMemory_GB, ple.PageLifeExpectancy,
    CAST(prs.cntr_value / 1.0 / i.SecondsSinceStartup AS NUMERIC(10, 2)) PgReadsPerSec
FROM sys.configurations c
CROSS APPLY
(
    SELECT TOP(1) 
        DATEDIFF(ss, sqlserver_start_time, CURRENT_TIMESTAMP) AS SecondsSinceStartup
    FROM sys.dm_os_sys_info
) i,
(
    SELECT object_name, counter_name, cntr_value AS PageLifeExpectancy
    FROM sys.dm_os_performance_counters
    WHERE [object_name] LIKE ''''%Buffer Manager%''''
    AND [counter_name] = ''''Page life expectancy''''
) ple,
(
    SELECT object_name, counter_name, cntr_value
    FROM sys.dm_os_performance_counters 
    WHERE [object_name] LIKE ''''%Buffer Manager%''''
    AND [counter_name] = ''''Page reads/sec''''
) prs
WHERE c.name like ''''Max_server memory%'''';

                '')
            WHERE InstanceName = ''' + @Instance + '''' + CHAR(13) + CHAR(10)
    --PRINT @Tsql
----------------------------------------------------
    BEGIN TRY
        EXEC sp_executesql @Tsql
    END TRY
    BEGIN CATCH
        PRINT @Instance
        PRINT ERROR_MESSAGE()
        PRINT CHAR(13) + CHAR(10)
    END CATCH

    FETCH NEXT FROM curInstance INTO @Instance, @MajorVer
END

CLOSE curInstance
DEALLOCATE curInstance

SELECT s.InstanceName, s.[Max SQL Server Memory (MB)], s.[Host Server Memory (GB)], 
    s.[Page Life Expectancy], s.[Page Reads/sec]
FROM #Servers s
DROP TABLE #Servers
GO

This time around, I wrapped the code in a stored procedure. I use this in one of my data centers. It's invoked by an SSRS report. Here's what the output looks like from SSMS:

SQL Server Linked Server - Results 4

Push Or Pull?

All of the examples in this post "pull" data from the remote servers. Some of the techniques may seem like a lot of work, which is understandable--some of us simply don't care for all the TSQL gymnastics. "Pushing" the data from the remote servers to the CMS is certainly an option. Both designs have their pros and cons. There are many factors that may go into deciding which to use, including personal preference. For me, I wanted to avoid storing data on the CMS and the requisite periodic data "refreshes". It just made more sense to go get the data on demand, knowing it would always be current. If you like this approach too, hopefully some of these examples will be of use. Happy coding!


SHARE