Monitoring multiple SQL servers

Jul 31, 2008 at 9:43 AM
Edited Jul 31, 2008 at 10:01 AM
Does anyone know if it is possible to monitor nultiple databases on multiple servers using DMV Stats.
I want to collect all the info from these databases on multiple servers in the DMVStatsDB on my PC for performance monitoring and reports .
Jun 29, 2011 at 10:28 PM

Judging by the date of this posting, either you got your answer or gave up.  It appears OTB, that the DMVStats application requires a separate SSRS for each server monitored.

But I thought I'd post what I did to get the DMVStats to use a single report server to monitor multiple servers in case someone like myself stumbles across this question and posting.  The following steps were performed on an non-clustered, default instance of SQL Server 2008 R2 with SSRS installed with defaults.

I performed the following steps (NOTE: make a backup copy of all the files mentioned below prior to making any edits).  <dmvstats location> is the location of the unzipped DMVStats download.

  • See Issue Tracker topic Install error AgentJobs log to address the problem in the creation of DMVStats jobs and 'owner_sid' reported problem.
  • Edit <dmvstats location>\DatabaseScripts\ConfigDMVstatsDB.sql--on SQL 2008, the "MISCELLANOUS" wait_type conflicts with the PK on the DMVConfig.wait_stats_categories table.  I believe this may be unique to SQL 2008.  Find the statement

insert into DMVconfig.wait_stats_categories
(wait_type, wait_category)
select wait_type, NULL
from sys.dm_os_wait_stats

and change to

insert into DMVconfig.wait_stats_categories
(wait_type, wait_category)
select distinct wait_type, NULL
from sys.dm_os_wait_stats
where wait_type not like  'MISCELLANEOUS'

  • Run <dmvstats location>\InstallDMVStatsDBProcs.cmd and manually add a record for MISCELLANEOUS to DMVconfig.wait_stats_categories (wait_type = MISCELLANEOUS, wait_category = MISC)
  • Edit <dmvstats location>\Reports\DeployReports.cmd--change the variable TargetServerURL from http://localhost/reportserver to http://<your report server>/reportserver where <your report server> is the designated server all DMVStats reports are to be run.
  • Edit <dmvstats location>\Reports\DeployDMVreports.rss--Note: The following steps customize the SSRS server with a folder hierarchy of DMVStats\<monitored computer name>.
  1. Add a new variable to get current system nameDim strComputerName as String = My.Computer.Name.ToString
  2. Change contant Const FolderName As String = "DMVStats" to a variable Dim FolderName As String =  strComputerName ' cannot contain "/"--this will create a new folder under which the DMVStats reports, data connections and other code will be placed.  Define according to your own hierarchical structure but as the instructions state, this string cannot contain a folder separator (/).
  3. Create the DMVStats "root" folder and subfolders on the SSRS server--The DMVStats report installation requires that the root folder exist on the report server.  As indicated by the text within DeployDMVReports, this structure can have multiple layers of subfolders--in my installation, I created a single root folder named "DMVStats"
  4. Change the line Const FolderPath As String = "/"  to Const FolderPath As String = <your DMVStats root and subfolder path>-- this is the "root" folder (and subfolders) created in the previous step.  In my installation it was set to Const FolderPath As String = "/DMVStats".
  5. Change the constant Const connectionString As String = "data source=localhost;initial catalog=DMVstatsDB;Integrated Security=SSPI" to the variable Dim connectionString As String = "data source=" & strComputerName & ";initial catalog=DMVstatsDB;Integrated Security=SSPI"--this will create the DMVStatsDB data connection for the server on which the DeployReports.cmd is executed.  The DMVstatsDB data connection can be further edited on the report server.
  6. Copy the full contents of <dmvstats location> to various servers and execute the InstallDMVStatsDBProcs.cmd and DeployReports.cmd