Where do you locate your monitoring service?

SQL Server monitoring tools generally require somewhere to install the service that does the monitoring.

In SQL Response 1, we required you to install an Alert Repository – a combination of a Windows service to collect data from your monitored servers and a repository for storing the collected data. In SQL Response 2, it’s likely that you will be able to have more control over the location and properties of the data store, but you will still need to install the service that actually runs the monitoring somewhere.

The key attribute for this service is that, quite logically, it requires access to all the servers you want to monitor – it needs to be able to login to both the physical server and to the SQL Server instance for everything you want to monitor.

Some questions about setting up the monitoring service

  • Do you have a dedicated box for this kind of thing? Or do you tend to install the monitoring service on one of your less important servers that may run one or two less critical instances?
  • Do you have an account set up that can access all servers specifically for the purpose of using a monitoring tool?
  • Have you run into difficulties in the past trying to install and configure a monitoring tool? What was the biggest issue? Separate domains? Credentials issues?
  • How much control do you want over the storage of monitoring data? Control the type, location, size, name, permissions of the data store and so on?
  • Do you want to run your own custom reporting against the data store?

Or tell us anything else about how you’d like to configure the overall topography of your monitoring solution.

This entry was posted in Uncategorized. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.


  1. PDinCA
    Posted October 14, 2009 at 8:03 pm | Permalink

    We installed SR1 on the instance that handles Production Reporting Services as it’s in the same zone as the primary SQL cluster and has easy access to all other SQL instances.

    Small shop, “limited SQL knowledge” among the sysadmins, so the sledgehammer approach of “whichever account will get us to the SQL and Win components will have to do” is what we have… Would definitely like a dedicated account, and here’s an opportunity for Red Gate to do better than MS: provides scripts, T-SQL and perhaps even some PowerShell, that we can run (T-SQL) or give to sysadmins (PS) to set the precise permissions for each SQL and Win login… Maybe while we set up each instance to monitor we could record the basics then “click here for T-SQL/PowerShell script” and come back later to finish registering the server after the logins have been created… (2-stage instance registration: pending and complete)

    Had difficulties accessing the Win artifacts… MS login requirements for SS2008 on WS2008 not understood by sysadmin, who created a login but it didn’t have some of the required directory and service permissions. I couldn’t help – no clue in that world! Used the “all powerful” SQL Server Service account for the SQL artifacts! It worked, but it may have been overkill…

    Historical retention control, by severity perhaps, “all” or “class” of instance, automatic or manual purging/archival would be helpful. Speaking for myself, as long as the data are in a SS DB that I can treat like any other Production DB, I’m happy.

    If there’s a way to infer patterns of events that could point to maintenance issues, and they come out-of-the-box, small shops will be grateful… Real DBA’s may not care – the press-ganged corps need as much educated guidance from tool vendors as can reasonably be included. At the least, perhaps decodified views that can be queried in RS, perhaps YOU write some RS Reports and charts :-) , would be most welcome.

    If the repository connection is “lost”, perhaps due to server maintenance on the host box, please keep trying to reconnect, don’t give up… Even if the pollong rate is lower after X minutes, keep polling… Or allow us to tell the client that there’s a scheduled outage so it’s ready for it and goes into “slower mode”. Coming in to a white-screen saying “no connection to repository” is a little worrisome but avoidable it would seem…

  2. John
    Posted October 15, 2009 at 7:21 pm | Permalink

    * Do you have a dedicated box for this kind of thing?
    We have a dedicated SQLAdmin01 box

    * Do you have an account set up that can access all servers?
    Yes we created a domain account named SQLMonitor01 and use that

    * Have you run into difficulties in the past trying to install and configure a monitoring tool? What was the biggest issue? Separate domains? Credentials issues?
    Agents that had to be installed on production servers… Becomes a nightmare for upgrades. Win NT rights that agents required.

    * How much control do you want over the storage of monitoring data? Control the type, location, size, name, permissions of the data store and so on?
    Yes for all these… I may want only 30 days for dev\qa but want 1 yr for prod… Maybe store the detail for X Days and rollup to store as longer term archive.

    * Do you want to run your own custom reporting against the data store?
    Yes because there is always something I need that has to be an add on like today I tie each SQL Database to a department and product reports showing SQL DB disk usage by “Department”, Server, etc.. I also want to do trending for when I need to upsize disk, cpu, IO etc.

  3. jonathan allen
    Posted October 16, 2009 at 11:25 am | Permalink

    With the current implementation we have the repository on a production server that doesnt host our core system databases. We are short on licenses so dont have a spare/dedicated server available and I didnt want to introduce a new Express instance.

    As we are moving forward with our upgrade to 2008 that project is incorporating a configuration management server so I would expect to place SQL Response 2 on that, it ties in with the role of the instance in monitoring and cataloging rather than having to do that as a sideline to its main business. It will host SSRS and my DBA metadata database too so I can access my reports on server states, security changes, database size trends etc. Custom reporting on the repository would be great – trending stats on types of alert, alerts per server and so on; Yes please.


    I am lucky in that we only have one domain here and no clustering! Installation was a breaze once I had the AD team create me the relevant service account.

  4. Mike Correa
    Posted October 20, 2009 at 5:55 pm | Permalink

    I installed the Alert Repository in a dedicated server where I thought it performed flawlessly. No issues with permissions.

    It would be very useful, however if I could run my own custom reports against the stored data. The ability to generate custom reports from the repository would allow me to quickly manage my manager’s needs so I can get back to managing my databases.

  5. Brian.Harris
    Posted October 22, 2009 at 4:25 pm | Permalink

    Thanks for the extremely useful comments, everyone.

    @PDinCA – managing account permissions is not currently in scope for Response v2; it’s an area we investigated a while ago, but decided it wasn’t a viable application. We do plan to implement log-ins and ‘roles’ in Response to address security issues, so that different types of users can access various types of information. Automatic purging of historical data is definitely in scope for V2, (it’s a feature currently in V1), allowing you to retain data older than x days or perhaps keep the DB size below x GB. The data repository will be a SQL Server db, so you will be able to do whatever you want on it – back it up, run queries, generate reports etc. Inferring patterns is something we’ve talked about a lot, as are defined maintenance windows. Certainly allowing a DBA to specify a scheduled outage – both one-off or recurring – is a feature that we’d like to implement at v2.

    @John – SQL on Response won’t require an agent (or anything in fact) to be installed any of your servers, so you should avoid that nightmare. Storing data for different periods of time depending on the type of server is an interesting idea, and may be part of the config settings for each monitored instance. We’ll consider this carefully when we think in more detail about configuration options. Custom reporting will be possible against the data repository for SQL Response 2, as it’s a SQL Server database. We’ll provide the data schema to help with this.

    @Jonathan – glad to hear that you have a dedicated box for monitoring/reporting, and no domain issues to worry about! Custom reporting against the Response db will be possible, but how much of this will be available via the Response GUI is not yet determined.

    @Mike – see above; the ability to run custom reports is something we’ve been asked for from numerous users, so as a first step, storing the collected data in an accessible database with an explanation of the schema, will definitely be in Response v2.

    Thank you all for your great contributions. It’s helping us no end.

  6. Steve Wales
    Posted October 27, 2009 at 8:36 pm | Permalink

    We have a dedicated box for central db infrastructure monitoring, for tools like Response and other db-centric mgmt tools, separate from our SCOM monitoring for all Windows servers, even though do some SQL monitoring with SCOM as well.

    We have at least one account per tool/service that can access each SQL server using a pessimistic security model.

    yes, all our install/configure difficulties in the past with monitoring tools have been credentials issues. vendors don’t often fully document the needs of their tools required permissions, and you end up breaking the install and having to start over from scratch. very frustrating. hasn’t been my experience with redgate, though, thankfully!

    yes, darn near full control over everything: type, location, size, name, permissions of the data store, etc.

    I want solid, basic reporting out of the box, utilizing built-in SQL 2008 SSMS tools, or ported to SSRS, and I want to develop our own to fit our requirements.

Post a Comment

Required fields are marked *

Add an Image


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>