A Short Recap

In Part 1: An Introduction, we discussed why it may

be necessary to centralize the monitoring of all our SQL Servers. There are

too many and frankly, they're doing your head in. It's a free-for-all where

you are and the monkeys you work with are swinging and screaming across the

server room, pressing buttons and tugging at cables.You may be pleased to know

that you're not the only one. For these articles aim to carry us to our ultimate

goal of not panicking in such an environment, by setting up a suite of reports

that displays information about the state of all our servers. Thus giving us

back control and allowing us to be super super cool (figure1) in such situations.

Figure 1 Our Ultimate Goal

Before we can use the SCOME technique (discussed in article one), we must first set up all

SQL Servers to be monitored as "Linked Servers" from our monitoring

server, "SQL_Admin" . This article demonstrates how to do so.

Ingredients

For today's article you will need...

1 x local SQL Server called SQL_Admin from which you will be doing your monitoring

1 x database call DBA_Admin, setup on SQL_Admin

1...n x remote SQL Servers to monitor (Payroll_Server, Finance_Server, Bunnytown_Server,

CRM_Server etc)

1 x user with Read access to the Master and msdb databases on the remote servers

1 x user, an owner of DBA_Admin, on SQL_Admin

1 x table in DBA_Admin to hold linked servers' names (see below for definition).

Access to change Component Services settings (see below) on each remote server

if the remote servers operating system is Windows 2003 SP1 or greater

Access to create SQL Server login accounts on each remote server

50g of sugar to justify "Sweeeeeet" when it all comes together at

the end

Before you can setup each linked server

If any of your

remote SQL Servers (the ones you will be monitoring) reside on a Windows

Server 2003 SP1 or later, you'll need to make a few configuration

changes to the windows server (that the remote SQL Server resides on), in order

for any communication to take place. Service Pack 1 (SP2 on Windows XP)

saw some changes made to the default security settings in the Windows environment.

Of these changes were those made to the MSDTC service and it's this service

we need to configure.

The MSDTC settings

dialog box can be accessed via the Control Panel -> Administrative Tools

-> Component Services (or dcomcnfg from the Run box). Then within the MMC

window, expand the Component Services node, expand the Computers node, expand

the MyComputer icon (wait for the actual icon of a computer to appear, otherwise

it will give false results) and right click the MyComputer icon. Select Properties

from the context menu that has just appeared, select the MSDTC tab and click

the security configuration button. You should see a window similar to the one

below:

Figure

2: MSDTC Security Settings

You now need

to change the settings to that in the above screenshot for us to be able to

create a linked server connection from SQL_Admin, but before you do, here's

an explanation of each option and any implications, according to Microsoft:

Network

DTC Access

The Network DTC Access check box lets you determine whether the Distributed

Transaction Coordinator service can access the network. The Network DTC Access

check box must be selected together with one of the other check boxes under

the Network DTC Access check box to enable network Distributed Transaction Coordinator

transactions. As we need to select the other options, we will need this

to be checked.

Allow Inbound

The Allow Inbound check box lets you determine whether to allow a distributed

transaction that originates from a remote computer to run on the local computer. By

default, this setting is turned off, but clearly we need it on so that our monitoring

queries can be run on the remote SQL Server.

Allow Outbound

When enabled, Allow Outbound will allow the local computer to flow

transactions to a remote computer; this is typically needed on the “client”

box, where the transaction is initiated. Currently we do not need this, but

a later script for a new report may require that we enable it.

Mutual Authentication

Required
Mutual Authentication Required sets the greatest security

mode that is currently available for network communication. This mode requires

the remotely accessing component to provide an authenticated connection with

the local computer. This authentication is verified by impersonation on the

local computer. Additionally, if the remote access communication is performed

between two Distributed Transaction Coordinator services, this authentication

information must specify a computer account that matches the remote transaction

mode computer's host name. Microsoft recommends this transaction mode for server

computers that are running Windows Server 2003 SP1 i.e. if anyone of your remote

SQL Servers are prior to Server 2003 SP1, then don't select this option.

Incoming

Caller Authentication

The Incoming Caller Authentication Required transaction mode only requires

the remote connection to be authenticated. Additionally, if the remotely accessing

component is a Distributed Transaction Coordinator service, the authentication

information must be for a computer account.

No Authentication

Required

The No Authentication Required transaction mode does not validate an

authenticated connection or verify whether an authenticated connection is being

established. This was how servers were set up by default before SP1 but is now

accepted as having a greater security risk then the other options. I choose

this mode myself, firstly because many of my remote servers reside on windows

boxes prior to 2003 SP1 and secondly because I am confident that there is minimal

risk of untrusted access in my working environment, but you may choose

one of the other modes. If you do though, bear in mind that for our purposes,

you will need to set up trusted users (Windows Authentication) for each linked

server (see below) with the adequate rights to run your monitoring stored procedures/queries.

So we've made

those changes on each Windows server that hosts a SQL Server you

will be monitoring remotely. We now need to create each one as a linked server

object on our monitoring SQL Server, SQL_Admin.

Figure 3: The Linked Servers Tree in SQL Server Management Studio

By creating

a Linked Server Object on an instance of SQL Server, we give this instance the

facility to access data from other sources, just as if these sources were actually

on the local server. The linked server can be a SQL Server, allowing us to access

tables on this remote SQL Server and allowing us to issue inserts, updates and

run stored procedures. It doesn't have to be a SQL Server though, it can be

an Oracle, Access or Excel datasource, heck it can even be a text file.

In fact in a later article we be creating a web report that displays data from

other sources (such as the size of Oracle data files). But for the time being

we will only be dealing with linking to SQL Servers.

Connect to

SQL_Admin via SSMS (SQL Server Management Studio) and expand the node Server

Objects. Right click on Linked Servers and click on New Linked Server. A window

like this should appear:

Figure 4: The General tab

The General page
In the General page, enter the details of your remote server, let's call

it PAYROLL_SERVER. The Linked Server box takes any name you

want to give the remote linked server, might as well call it whatever the

actual server is called. Product Name, you can enter Microsoft

OLE DB or SQL Server, data source is what it's called on the network. Chances are

this too is the name of your server.  

The Security page
The Security page is where we set up a user on SQL_Admin that will

pretend to be an existing user on the remote server. So now we will create

both these users on each server using SQL accounts. It's worth noting that if

you intend on using an MSDTC Transaction Communication mode other than

"No Authentication Required", your users will need to be Windows

authenticated users.

Now, I'm not going

to be patronising and insist in big block red letters that YOU

SHOULD NOT USE THE SA USER for this access.You're

the DBA, no one knows your databases and the environment they are in more than

you. But I'll point out that for the user on the remote server, most of the

stored procedures used to retrieve the reports' data need only Read

access to the Master and msdb databases on these remote servers. When

there is a specific object that needs fancy Execute permissions, I will point

out the user permissions to just that object. As for SQL_Admins' user, it needs

to be the owner of the DBA_Admin database as this user will need to execute

DBA_Admin procs and read/write to tables in this database. These are the tables

that our web reports will be getting their data from.

 Figure 5: The security tab

So now we have a user called, I don't know, "MonitoringReportUser" on SQL_Admin who

owns the database DBA_Admin and we have a user called MonitoringReportUser on

PAYROLL_SERVER who has Read access to the Master and msdb databases. We enter

these details into the Security page of the Linked Server setup dialog, with

the password that belongs to MonitoringReportUser on PAYROLL_SERVER. This is

displayed above in figure 5 (but with Your_Login in place of MonitoringReportUser).

The Server Options Page
Finally, set the Server Options as below, where we enable Remote Procedure

Calls to and from the remote server. This is necessary for what we need

to do.

To make sure

that the new linked server was created successfully, refresh the Linked

Servers node in SSMS and you should see a node called PAYROLL_SERVER. Right

click the node and "Test Connection" or better still, expand the tree

so that you can browse the tables of the Master database on PAYROLL_SERVER.

If you get an error, then you need to review the setup.

If at any stage,

you get the following error message when trying to view the linked server object

via your newly created user:

The EXECUTE permission was denied on the object 'xp_prop_oledb_provider'

You can GRANT access to this permission with the following:

GRANT EXECUTE ON xp_prop_oledb_provider TO MonitoringReportUser;

T-SQL

Now you could do all the above OR you could just copy and paste

the T-SQL below and run it to set up a linked server. Saves you clicking

lots. Remember, if you have 50 remote SQL Servers to monitor, then you'll have

to set up 50 linked servers. It's a lot easier running T-SQL and changing the

parameters with CTRL-H where applicable then using the SSMS. Just replace

the keyword 'Payroll_Server', the username and the password.

USE
[Master];
GO
/******
 Part 2: Setting up your remote servers as Linked Servers
 Object Creation: Linked Server Connections
 Purpose: To create a linked server object
 Date: 25/01/2009
 Author: Drew Salem - www.thebuddingdba.com
 For: SQLServerCentral.com
 ******/EXEC
 sp_addlinkedserver
 @server = 'PAYROLL_SERVER',
 @srvproduct = 'Microsoft OLD DB',
 @provider = 'sqloledb',
 @datasrc = 'PAYROLL_SERVER'
EXEC
 master.dbo.sp_serveroption @server=N'PAYROLL_SERVER', 
 @optname=N'rpc', @optvalue=N'true'
 EXEC master.dbo.sp_serveroption @server=N'PAYROLL_SERVER', 
 @optname=N'rpc out', @optvalue=N'true'
-- Next we 
 add the login that takes the following parameters:
-- The name 
 of the remote server to monitor, whether to impersonate the login or 
 not, the local login to
-- map 
 from SQL_Admin,
 -- the user name on remote server to map to and the password to use 
 to log on to the remote server.
 
EXEC
 sp_addlinkedsrvlogin 'PAYROLL_SERVER', 
  'False',
  'MonitoringReportUser', 
  'MonitoringReportUser',
  'mY_P455Word' --Password
GO
--Now login onto SQL_Admin as MonitoringReportUser and run
SELECT
 *
 FROM OPENQUERY(PAYROLL_SERVER, 'SELECT 
 * from spt_server_info')
 GO

If the following message is received:

OLE DB provider "SQLNCLI" for linked server "Payroll_Server" returned message "Deferred prepare could not be completed.".

then chances are the MSSQLServer service account is logging on

as Local System. It needs to be an account that can communicate with other SQL

servers such as a domain user account.

Stored Procedures' accessibility to the names of Linked Servers

When we run our stored procedure against all our remote servers, we'll

need to tell SQL_Admin which server we want it to run the proc on next. We can

do this if we enter the name of each linked server into a table, which the proc

can then read from. We'll call the table [Server_SQL_Details]. Here's

the definition for the table...  

USE
 [DBA_Admin]
          GO
          
          /******
          Part 
          2: Setting up your remote servers as Linked Servers
          Object Creation: Table [Server_SQL_Details]
          Purpose: Used to store data about Linked Servers
          Date: 25/01/2009
          Author: Drew Salem - www.thebuddingdba.com
          For: SQLServerCentral.com
          ******/        CREATE
          TABLE [Server_SQL_Details](
          [ServerID] [int] IDENTITY(1,1) NOT NULL,
          [Servername] [sysname] NOT NULL,
          [ServerVersion] [tinyint] NOT NULL,
          [Active] [bit] NULL,
          CONSTRAINT [PK__Server__LinkedServers] PRIMARY KEY CLUSTERED([ServerID] 
          ASC)
          )

We have an incrementing

ID, the name of the server, it's version (as the proc will need to determine

if the remote server is SQL Server 2000 or 2005 +, so that it knows which system

tables/DMVs to interrogate), and a true/false flag so that we can disable the

engine from running the proc on a particular server if we need to do so. Again,

more on that later).

Now enter

PAYROLL_SERVER into the first row, with the version of SQL Server (8 for 2000,

9 for 2005 etc) and "True" for it to be actively included in the collection

of data. 

You'll now

have to do the same for aaaall your SQL Servers. Don't be put off. Trust me,

once this is setup, you'll never look back... That said, there is a lazy DBAs

option. You can run the below script which uses sp_linkedservers to populate

the Server_SQL_Details table with all your already setup linked server objects.

You'll need to temporarily relax the NOT NULL constraint on the ServerVersion

column though.

USE[DBA_Admin];
          GO
        /******
          Part 2: Setting up your remote servers as Linked Servers
          Purpose: To insert Linked Servers data into Server_SQL_Details 
          table
          Date: 25/01/2009
          Author: Drew Salem - www.thebuddingdba.com
          For: SQLServerCentral.com
          ******/        DECLARE@SQL varchar 
          (1000)
          SELECT @SQL = 'Exec 
          ' + @@servername + '.master.dbo.sp_linkedservers'
        
          CREATE table #temptable (
          srv_name varchar (100),
          srv_providername varchar (100),
          srv_product varchar(100),
          srv_datasource varchar (100),
          srv_providerstring varchar (100),
          srv_location varchar (100),
          srv_cat varchar (100))
        INSERT INTO #temptable EXEC 
          (@SQL) 
        INSERT INTO Server_SQL_Details (Servername) 
          
          Select srv_datasource from #temptable
        UPDATEServer_SQL_Details
          SET Active = 1
        
          DROP TABLE #temptable
          GO
        SELECT* FROM 
          [Server_SQL_Details]
          GO
        --You can now update the version of 
          the server.

We now have our linked servers setup and we have our table Server_SQL_Details which holds a few details about the SQL Servers that SQL_Admin is linked to. This table will be used by the procs to know which servers they should be run on.

That's it for this article. In the next one we'll start on our first monitoring report, one of the more straightforward ones, the Failed_Jobs report, where information regarding any failed jobs will be collected from all SQL Servers being monitored and placed into one table. Then we'll take our first look at Visual Studio to build a web application that will present the failed jobs.

Till then,

Drew