SQLServerCentral Article

When the reporting team needs an assist...

,

Let me introduce a reporting-related situation and the way I handled it. The situation: my company provides several services to several customers. Within our company there is a dedicated reporting team whose job is to provide different kinds of up-to-date data in the form of reports for our customers on a monthly basis. Most of these reports are related to the resources used by the servers that host the services. These reports are significant because our customers want to know what they pay for. The data necessary for the reports should be easily provided by the monitoring team because of the nature of this data. So far so good, but here comes the bad part.

Unfortunately, our monitoring environment is not well-designed and does not work properly, so from time to time our reporting team do not get the necessary data. The truth is that the main job of the reporting team is to use the data and not to chase it each and every month. I was asked to solve the part of the issue concerning the SQL Server.

As a database administrator my job was to help the reporting team by finding up the missing SQL Server related data for them. It may not help so much, after all this is just a part of the missing data, but I think in situations like this we have to act like a team. Having a team attitude, by helping each other, is very important. Who knows when you will need help as well.

Let's focus on the task and the solution. Our task was to collect and then provide data defined by the reporting team from all of the SQL Server instances. The most important questions are as follows:

  1. Exactly what data is needed?
  2. How can we collect the data from each and every instance easily?

What to collect?

In this case, our reporting team needed the following data:

  • server name
  • instance name
  • sum of the size of all databases hosted by the instance (data+log regardless of their locations)
  • number of SQL Server login
  • max memory the instance can use

Here is a simple query for what we needed:

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS servername
      , SERVERPROPERTY('ServerName') AS instancename
      , SUM(size) * 8 / 1024 AS dbsize_mb
      , ( SELECT COUNT(principal_id)
            FROM sys.server_principals
            WHERE type IN ( 'S', 'U', 'G' )
        ) AS logins
      , ( SELECT value_in_use
            FROM sys.configurations
            WHERE name LIKE 'max server memory%'
        ) AS max_memory
      , GETDATE() AS date
    FROM sys.master_files

How to collect?

The main idea was to choose and dedicate a server to be a so-called 'Central SQL Server'. We decided to add every other instance to this Central SQL Server as a linked server so all the data can be easily queried from one place. It can also be also collected in a dedicated 'Report' database hosted on this Central SQL Server.

During the development of this solution I tried to make the database easy to move, in case the 'Central SQL Server' has to be moved to another instance. I wanted the system to be easy to manage as well.

The first step was to create an SQL Authentication server login on each SQL Server instance with minimal permissions. These were the 'view any definition' and 'view server state' server permissions in this case. I used this code:

USE [master]
GO
CREATE LOGIN [LS_Report] 
WITH PASSWORD=N'VeryH@rdP@$$w0rd!!!', 
DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [master]
GO
GRANT VIEW ANY DEFINITION TO [LS_Report]
GO
GRANT VIEW SERVER STATE TO [LS_Report]
GO

I needed a table to store and maintain all of the SQL Server instances' information like instance name, version, edition, collation and so on. This was very useful because I had options to filter the servers on which anyone wanted to run the query. The instance table looked like this one:

The following query created a simple instance table:

CREATE DATABASE CentralSQLServerDB
GO
USE CentralSQLServerDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_instances](
  [instance_id] [smallint] IDENTITY(1,1) NOT NULL,
  [instance_name] [nvarchar](255) NOT NULL,
  [product_name] [nvarchar](128) NULL,
  [product_version] [nvarchar](128) NULL,
  [porduct_level] [nvarchar](128) NULL,
  [edition] [nvarchar](128) NULL,
  [language] [nvarchar](128) NULL,
  [collation] [nvarchar](128) NULL,
  [remarks] [nvarchar](max) NULL,
 CONSTRAINT [PK_tbl_instances] PRIMARY KEY CLUSTERED 
(
  [instance_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
/*
--My instances in this example.
  INSERT INTO tbl_instances (instance_name) VALUES ('MSSQLTEST')
  GO
  INSERT INTO tbl_instances (instance_name) VALUES ('MSSQLTEST\SQL2005EXPRESS')
  GO
  INSERT INTO tbl_instances (instance_name) VALUES ('TEST02')
  GO
  */ 
I intentionally inserted only the instance names into the table. You only need the names to implement this solution. As you will see the rest of the data can be easily made up later on by using this solution. Homework for you 🙂

The next step was to create the linked servers on the 'CentralSQLServer'. This is the code for creating one linked server:

use master
go
EXEC master.dbo.sp_addlinkedserver @server = 'LS_MSSQLTEST', @srvproduct=N'SQLSERVER', @provider=N'SQLOLEDB', @datasrc='MSSQLTEST'
GO
EXEC master.dbo.sp_serveroption @server= 'LS_MSSQLTEST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'LS_MSSQLTEST', @locallogin = NULL , @useself = N'False', @rmtuser = N'LS_Report', @rmtpassword = N'VeryH@rdP@$$w0rd!!!'
GO

If you have 10 SQL Server instances you need to run this code 10 times. Each time you need to replace the instance name in the script with the new instance. You also need to replace the linked server name, which has 15 occurrences in the script. Today there are a lot of very good and useful text-replacing tools (SSMS has this option too), but why would you use such a tool when you can do the whole work altogether in one step by using my universal foreach stored procedure (additional link)? 🙂 Like this:

EXEC master.dbo.sp_uforeach 
@table_name='select ''LS_''+REPLACE(instance_name, ''\'', ''__'') as name from CentralSQLServerDB.dbo.tbl_instances', 
@column_name='name', 
@command='use master
go
DECLARE @lserver nvarchar(max)
select @lserver=REPLACE(REPLACE(''?'', ''__'', ''\''),''LS_'','''')
EXEC master.dbo.sp_addlinkedserver @server = ''?'', @srvproduct=N''SQLSERVER'', @provider=N''SQLOLEDB'', @datasrc=@lserver
GO
EXEC master.dbo.sp_serveroption @server= ''?'', @optname=N''collation compatible'', @optvalue=N''false''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''data access'', @optvalue=N''true''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''dist'', @optvalue=N''false''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''pub'', @optvalue=N''false''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''rpc'', @optvalue=N''true''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''rpc out'', @optvalue=N''true''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''sub'', @optvalue=N''false''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''connect timeout'', @optvalue=N''0''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''collation name'', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''lazy schema validation'', @optvalue=N''false''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''query timeout'', @optvalue=N''0''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''use remote collation'', @optvalue=N''true''
GO
EXEC master.dbo.sp_serveroption @server=''?'', @optname=N''remote proc transaction promotion'', @optvalue=N''true''
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = ''?'', @locallogin = NULL , @useself = N''False'', @rmtuser = N''LS_Report'', @rmtpassword = N''VeryH@rdP@$$w0rd!!!''
GO', 
@print_command_only= 1,
@debug=0

After running this script with @print_command_only = 1 you get the code you need. Remember in this example I have 3 servers including the CentralSQLServer.

  1. MSSQLTEST /CentralSQLServer/
  2. MSSQLTEST\SQL2005EXPRESS
  3. TEST02

You can filter it out but I think it is unnecessary to do so. Here is the output:

/* For table: [##C7495362-9561-41E2-932A-CDD438C82D28], column: name, value: LS_MSSQLTEST: */use master
go
DECLARE @lserver nvarchar(max)
select @lserver=REPLACE(REPLACE('LS_MSSQLTEST', '__', '\'),'LS_','')
EXEC master.dbo.sp_addlinkedserver @server = 'LS_MSSQLTEST', @srvproduct=N'SQLSERVER', @provider=N'SQLOLEDB', @datasrc=@lserver
GO
EXEC master.dbo.sp_serveroption @server= 'LS_MSSQLTEST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'LS_MSSQLTEST', @locallogin = NULL , @useself = N'False', @rmtuser = N'LS_Report', @rmtpassword = N'VeryH@rdP@$$w0rd!!!'
GO
/* For table: [##C7495362-9561-41E2-932A-CDD438C82D28], column: name, value: LS_MSSQLTEST__SQL2005EXPRESS: */use master
go
DECLARE @lserver nvarchar(max)
select @lserver=REPLACE(REPLACE('LS_MSSQLTEST__SQL2005EXPRESS', '__', '\'),'LS_','')
EXEC master.dbo.sp_addlinkedserver @server = 'LS_MSSQLTEST__SQL2005EXPRESS', @srvproduct=N'SQLSERVER', @provider=N'SQLOLEDB', @datasrc=@lserver
GO
EXEC master.dbo.sp_serveroption @server= 'LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_MSSQLTEST__SQL2005EXPRESS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'LS_MSSQLTEST__SQL2005EXPRESS', @locallogin = NULL , @useself = N'False', @rmtuser = N'LS_Report', @rmtpassword = N'VeryH@rdP@$$w0rd!!!'
GO
/* For table: [##C7495362-9561-41E2-932A-CDD438C82D28], column: name, value: LS_TEST02: */use master
go
DECLARE @lserver nvarchar(max)
select @lserver=REPLACE(REPLACE('LS_TEST02', '__', '\'),'LS_','')
EXEC master.dbo.sp_addlinkedserver @server = 'LS_TEST02', @srvproduct=N'SQLSERVER', @provider=N'SQLOLEDB', @datasrc=@lserver
GO
EXEC master.dbo.sp_serveroption @server= 'LS_TEST02', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server='LS_TEST02', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'LS_TEST02', @locallogin = NULL , @useself = N'False', @rmtuser = N'LS_Report', @rmtpassword = N'VeryH@rdP@$$w0rd!!!'
GO

This was exactly what we needed. A movable solution, remember? So far I have chosen an SQL Server instance to be the CentralSQLServer, MSSQLTEST in our case. I then created a CentralSQLServerDB with a table (tbl_instances) containing data from my SQL Server instances. I have also created a login named LS_Report on all instances (this is the boring or so-called 'droid work' part of this solution) and last but not least I have created automatically all of the linked servers in one step.

The last step is to check the connections by querying the data from the linked servers. Of course the sp_uforeach (additional link) stored procedure can help here as well.

EXEC master.dbo.sp_uforeach 
@table_name='select ''LS_''+REPLACE(instance_name, ''\'', ''__'') as name from CentralSQLServerDB.dbo.tbl_instances', 
@column_name='name', 
@command='
select * from openquery( ?,''select 
    SERVERPROPERTY(''''ComputerNamePhysicalNetBIOS'''') as servername, 
    serverproperty(''''ServerName'''') as instancename, 
    sum(size)*8/1024 as dbsize_mb, 
    (select count(principal_id) from  sys.server_principals where type in (''''S'''',''''U'''',''''G'''')) as logins, 
    (select value_in_use from sys.configurations where name LIKE ''''max server memory%'''') as max_memory, 
    getdate() as date 
    from sys.master_files'')
', 
@print_command_only= 1,
@debug=0

Running the result of the script or just simply running the code above with @print_command_only=0, you are done.

You can easily automate this solution by creating a job for the script and completing it to insert the results into a table. Please keep in mind that the sys.servers system table can be used for filtering purposes as well.

If you have any remarks, questions or you have any ideas, a real life example when sp_uforeach could come in handy, please feel free to contact me, leave a comment or drop me a mail /robertATsqlapprenticeDOTnet/.

Resources

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (13)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (13)

You rated this post out of 5. Change rating

Related content