Configure Historic Database Connection

When setting up historic monitoring, Database Health Reports uses a database called DBHealthHistory on the SQL Server that you specify as the monitoring server.  Step 1 is to establish a connection to the server that will hold the monitoring database. Step 2 is to tell the monitoring database which databases to monitor. Once those two steps are complete you then just wait for historic data to be collected.

Step 1: Create or connect to an existing monitoring instance.

When you have not yet configured any historic reporting, the database overview and server overview pages will show the following panel.  This is an indication that there is reporting available that hasn’t yet been turned on.

HistoricConnectionUnconfigured

Click the “Click here to configure…. ” message and the following dialog will be shown to allow you to set up the historic monitoring instance.

HistoricConnection-Step1

When you click the “Connect to or Create a Monitoring Instance” button you will be prompted for database connection credentials. These credentials can be either Windows Authentication or SQL Server authentication. This connection will be used to connect to the database instance and create a database called DBHealthHistory. This database will be used to track the historic data over time for your reports.

When setting up a monitoring instance it is best to use a SQL Server besides the one being monitored. This was the historic tracking data will have less of an impact on your production server. The historic monitoring database will not work with a SQL Express database because the SQL Agent is needed to do the monitoring, and there is no SQL Agent with SQL Express.

HistoricConnection-Step1a

Once you have entered your credentials and the DBHealthHistory database has been created you will see the following message on the configure dialog stating that the “Monitoring instance is configured”.

HistoricConnection-Step2

At this point you are ready to specify which databases are going to be monitored.

Step 2: Set the credentials to use for monitoring.

The second step is to set up the credentials that will be used to monitor any database. If you see the following dialog, you know you are ready to go on to step 2.

HistoricConnection-Step2

You will be prompted for credentials to use for monitoring. Here SQL Server authentication is the only supported option. Specify a SQL Server username and password that will be used when connecting from the monitoring server to the server that is being monitored.

HistoricConnection-Step2Credentials

After entering the SQL Server Authentication credentials you will see the “Credentials configured for monitoring” message. Click OK, monitoring will now begin.

HistoricConnection-Step2Complete

Test Your Connection

Once the connection has been set up it usually takes about 4 to 5 minutes before the monitoring begins. You can use the “Test Connection button” to confirm that everything is in place to monitor from one SQL Server to another.

HistoricConnectionComplete

Wait for data to collect over time.

Depending on the load of your SQL Server you may see the following message for some time. For a busy SQL Server, you will usually see reporting data available within a couple hours of when the reporting was turned on. For a lighter load SQL Server it may take a couple days to a week to see any significant wait data.

HistoricConnectionComplete


Enroll Today!
SteveStedman5
SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

16 Comments on “Configure Historic Database Connection

  1. Works great with the exception of the ‘configure historic database’.
    Sometimes it works, and sometimes I have to close and restart the application multiple times to get it to work.

    • If you are referring to configuring the historic monitoring twice, you can check the [instanceMonitor] table in the [DBHealthHistory] database. If there is more than one entry for your server you can just delete one of the rows from that table.

      If you are referring to connecting twice in the main application, you can just right click on the database name, and choose “disconnect”.

      Let me know if this helps.

  2. What rights need this account ?

    Specify a SQL Server username and password that will be used when connecting from the monitoring server to the server that is being monitored.

  3. Hi. This works great on two servers but the job on the third server just keeps producing this error:

    Executed as user: NT SERVICE\SQLSERVERAGENT. Server ‘SERVER_NAME’ is not configured for DATA ACCESS. [SQLSTATE 42000] (Error 7411). The step failed.

  4. Hey Steve, I happened to try to create this historic feature for the first time (my other machine ran SQL Express and so I could not). But I find that I am seeing a prompt that this post does NOT indicate.

    Besides the option you discuss here (“Connect to or Create a Monitoring Instance”), I see also “Use the Current Instance”. What not clear is if that is saying “use the DB of the machine from which DatabaseHealth is currently running”. I THINK that’s what it’s saying, and indeed it would be my preference. Some of us using the tool may want NOT to have any impact on the server being monitored, and so are running DBHealth from a local workstation of something, which we ARE ok with impacting. 🙂

    (I don’t find any other discussion of the feature, beyond this post. I did look before asking.)

    I do see that this post is from 2013, so perhaps you added that ability since then. Could you clarify, preferably not just here in the comment but also by updating the blog post itself? It is linked to directly from the feature page, when we are presented these options, so I’m sure others have or would come looking for this discussion.

    Thanks as always.

    • Charlie – the “Use the current instance” option will create a monitoring database on the instance that is being monitored. If you want to monitor from another instance you can connect to a different instance and it will create the monitoring database and jobs on the other server, not the one being monitored or watched. I hope this clarifies things.

  5. Hi Steve, I started using your monitoring tool about 3 months ago. The tool is very helpful, however since the configuration was done, the waits table is still empty and also other tables are still empty. The server is very busy too.

  6. I cannot see any historic wait information for any of the 10+ servers being monitored.
    Two of them have the ‘Not enough wait data yet’ message, when I click on ‘Test Connection’ all works OK
    All the rest have blank ‘days’ showing.
    I am using a central server for the stats.
    Do you have any suggestions?

  7. Hey Steve, a couple of things. First about a challenge, and then about a seeming need to update the screens on this post.

    First, I am not finding a way to CHANGE the historic db for a monitored instance, from being on the instance itself to being some other instance. Is it possible? I don’t see it in the UI, and I don’t see it discussed on this page.

    This is a follow-on from my comment above from last June 12. At the time, I indicated wanting to monitor a remote SQL Express instance. Before I got your response on June 18, I had told it to use the “current instance”, and since it’s SQL Express, DBHealth would not create the history DB there due to the lack of SQL Agent. I understand that’s “not allowed”.

    Again, what I’m asking here is: once one has done that and gets that error, there seems no way to change the history tracking to use a DIFFERENT instance.

    Along those same lines, I notice that the second image above (“configure historic database”) doesn’t reflect how you did later add the option (on that screen) to EITHER “use the current instance” or “connect to or create” another instance. Such an update image could be helpful to update, since the app has a link on that screen to this blog post.

    Finally, there’s a minor typo here: “this was the historic tracking data will have less”, which as you can see now should be “this way”. 🙂

    As always, just trying to help, and grateful for the tool and all the info you share.

  8. I cannot see any historic wait information for my SQL server. I’m seeing a ‘Not enough wait data yet’ message. But The Monitor has been running for 6 days. When I click on ‘Test Connection’ all works OK.
    How can I fix this?

Leave a Reply to Fred Botts Cancel reply

Your email address will not be published. Required fields are marked *

*

To prove you are not a robot: *