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.
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,
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
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
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:
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.
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.
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 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.
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.
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
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.
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
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 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;
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.
Part 2: Setting up your remote servers as Linked Servers
Object Creation: Linked Server Connections
Purpose: To create a linked server object
Author: Drew Salem - www.thebuddingdba.com
@server = 'PAYROLL_SERVER',
@srvproduct = 'Microsoft OLD DB',
@provider = 'sqloledb',
@datasrc = 'PAYROLL_SERVER'
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
-- the user name on remote server to map to and the password to use
to log on to the remote server.
--Now login onto SQL_Admin as MonitoringReportUser and run
FROM OPENQUERY(PAYROLL_SERVER, 'SELECT
* from spt_server_info')
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...
2: Setting up your remote servers as Linked Servers
Object Creation: Table [Server_SQL_Details]
Purpose: Used to store data about Linked Servers
Author: Drew Salem - www.thebuddingdba.com
[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]
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).
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
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
Part 2: Setting up your remote servers as Linked Servers
Purpose: To insert Linked Servers data into Server_SQL_Details
Author: Drew Salem - www.thebuddingdba.com
SELECT @SQL = 'Exec
' + @@servername + '.master.dbo.sp_linkedservers'
CREATE table #temptable (
srv_name varchar (100),
srv_providername varchar (100),
srv_datasource varchar (100),
srv_providerstring varchar (100),
srv_location varchar (100),
srv_cat varchar (100))
INSERT INTO #temptable EXEC
INSERT INTO Server_SQL_Details (Servername)
Select srv_datasource from #temptable
SET Active = 1
DROP TABLE #temptable
--You can now update the version of
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.