Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Monitoring SQL Server Using Cloud Technology


By:   |   Last Updated: 2017-10-02   |   Comments   |   Related Tips: More > Monitoring

Problem

At our company, we are facing a number of SQL Server issues related to monitoring our environment with servers on premises and in the cloud.  We have a variety of failures related to backups, replication, SQL Server Agent Jobs, etc. as well as performance slowdowns that impact the business.  We are in need of a monitoring solution that allows us to customize certain parameters to trigger alerts to proactively and reactively address issues.  The biggest roadblock my team is facing is the need to provision another server, storage and licensing (i.e. Windows, SQL Server and product).  Are there any cloud based options that can help me?

Solution

The majority of SQL Server monitoring tools require a centralized server for data collection, data storage, data processing, alerting and more.  There are numerous reasons behind this architectural decision, but this additional cost (hardware, storage, Windows license, SQL Server license, etc.) and management for the DBAs is not necessarily factored into the cost of the solution.  There are also some questions you should ask yourself:

  • What is the impact to the organization if the monitoring solution goes down?
  • Do you have high availability on your monitoring solution?
  • Is your monitoring tool capable of monitoring both on premises and cloud instances?
  • What is the associated cost with a new server, storage and licensing (Windows and SQL Server)?
  • How much time will the DBA team need to manage another server?
  • What is the level of effort to build our own solution vs. commercial product?
  • What security aspects should I be concerned about with a monitoring product in the cloud?

When you think about these items, a cloud based SQL Server monitoring solution may be beneficial for your organization.  One solution we would like to introduce is SQLTreeo.

SQL Server Monitoring with SQLTreeo

SQLTreeo Monitoring is a cloud based SaaS (software as a service) solution requiring minimal configuration. To start using SQLTreeo just setup the server IP addresses as well as the login information in the SQLTreeo Monitoring console. Then you can download the client utility which can be installed on any machine as a service or executed as a console application.  The client tool gets the server list from the cloud and starts delivering monitoring information about the SQLTreeo monitored servers. Of course, the machine running the agent must have access to all the servers you intend to monitor.

SQLTreeo Monitoring Security Considerations

You should be concerned about security when monitoring your SQL Servers in the cloud from an external provider. SQLTreeo agent doesnít look at or interact with user data; it only captures the contents of system views. For this reason, this tool doesnít require an account with SQL Server sysadmin rights. You just need an account with the permission VIEW SERVER STATE granted. Furthermore, SQLTreeo complies with ISO27001, ISO9001, AMS-IX, ISAE 3402 and PCI DSS certifications.

User Access in SQLTreeo

SQLTreeo monitoring supports an unlimited number of users without additional cost. It allows you to create different users groups and customize the rights for each group.  Below are the SQL Treeo Access Rights to give you a sense of the granular permissions that can be assigned.

Monitoring Environments with SQLTreeo

An interesting feature of SQLTreeo is that it allows the creation of different monitoring environments.  This is useful when you have to administer servers of different companies or servers in different geographical regions. When you login to SQLTreeo you have the option to select which monitoring environment you want to work on.

SQLTreeo Login - Description: This is the first screen you see when logging in SQLTreeo.

Server Groups

Another feature of SQLTreeo Monitoring is the capability to group servers within the same monitoring environment. For example you can define groups for development, testing and production servers.

SQLTreeo Thresholds, Alerts and Alarms

With SQLTreeo Monitoring you have the freedom to define your own thresholds so you can get insight about the metrics needed on your environment and trigger alerts.

Thresholds - Description: You can create your own thresholds.

In order to receive alerts you must create Actions. SQLTreeo Monitoring allows Email, SNMP and Slack notifications. When you create an action you must choose amongst the three ways of notification and set up the details. For instance, when you select Email you must enter the email address that will receive the notification.  You are not limited to create one action by action type.

Actions - Description: You must create Actions to receive alerts.

Below is a sample email based alert for a cleared blocked process.

Below is a sample Slack channel with four alarms.

Alarms can also be reviewed per server as shown below:

Alarms can also be searched to save you time when reviewing the history of a server.

Dashboards

SQLTreeo Monitoring ships with a number of dashboards to aid monitoring your SQL Server environment.  Letís dive into each one.

SQLTreeo Server Dashboard

The SQLTreeo Server Dashboard shows the status of the servers by group using different colors for a quick view: red, orange and green. It displays five icons by group: Replication, Servers, Alarms, Jobs and Backups.

Server Dashboard - Description: The Server Dashboard serves as a quick view.

You can click on the icons for further information, either by redirection to other dashboards or with pop ups like with the Servers icon.

Servers Dashboard Detail - Description: When you click on Servers icon you get more detailed information by server.

On the previous image you can see the pop ups that are displayed when you click on the Servers icon; each pop up shows information about a specific server. The pop ups have six icons that allow you to get a general overview of the server; and in case you want to dig further you can click on each icon. From left to right the icons represent databases, jobs, replication (the RE), Requests/s, CPU and megabytes transferred by second. Also there is a description at the bottom that aids in troubleshooting

SQLTreeo Jobs Dashboard

When you click the Jobs icon on the Servers Dashboard you are redirected to the Jobs Dashboard, but you can also access it from the navigation panel on the left.

Jobs Dashboard - Description: This is how Jobs Dashboard looks like.

As you can see on the previous screen capture, this dashboard has five color buttons on top and a grid on the bottom with details. Each button when pressed refreshes the grid with the jobs that match the status (Jobs, Disabled, Failed, Retrying or Canceled) of the button description.

SQLTreeo Backups Dashboard

With the same logic and design as the Jobs Dashboard, SQLTreeo has a Backups dashboard that includes all server backups for a simple and comprehensive view. You have a top panel with six buttons that serves as a filter for the lower panel.  The following list enumerates the six buttons with their description:

  • Databases: Shows all backups from all servers.
  • Full backups: Displays all full backups from all servers.
  • Diff backups: Shows all differential backups from all servers
  • Log backups: Displays all transaction log backups from all servers.
  • No LOG backup last 30 mins: Shows all databases that are in FULL recovery mode, but do not have a LOG backup in the last 30mins.
  • Outdating LOG backups: Displays all outdated LOG backups (no logs in past 7 days) from all databases that are in FULL recovery mode.

The following image is a screen capture of the SQLTreeo Backup Dashboard.

Backup Dashboard - Description: This is how the Backup Dashboard looks like.

Notice that on the DATABASE_NAME column, the names of the databases are hyperlinks. If you click on the database name you will see a graphic with the duration of each backup type plotted with different colors.

Backup Dashboard Details - Description: When you click on the database name you can see a graphic with backup durations.

SQLTreeo Thresholds Dashboard

SQLTreeo Monitoring allows you to see the thresholds for each counter applied to each SQL Server instance you created on a single dashboard.

Thresholds - Description: Thresholds Dashboard.

Dashboards Coming Soon

In the following months SQLTreeo will add two more dashboards; one about High Availability and another fully customizable dashboard named Custom Queries.  Stay tuned!

Below is an example of the Server level custom queries.

SQLTreeo Replication Monitoring

Another interesting feature of SQLTreeo Monitoring is the graphic representation used for SQL Server replication monitoring. Take a look at the following screen capture.

Replication - Description: SQLTreeo Monitoring replication view.

On the previous image we have all the information we need in order to get a quick view of replication status, but also the interface allows us to dig further when we need more details.

The graphic interface has a top button that when clicked shows the number of publications, subscribers and publishers as well as the last time the information was updated. Below the replication button there are two folder icons representing the monitoring groups of the servers involved in the replication, in this case Production and Development.

Following down are three buttons with different icons connected by lines, in this case dash lines. The type, strength and color of the lines vary following the legend description on the rightmost top of the screen. Those three buttons from top to down represent the publisher, distributor and subscriber.

Also when you click on any of these buttons a cake chart opens around the button with additional information about the server and its role in the replication scheme.

SQLTreeo Reporting

With SQLTreeo Monitoring you can create reports and export them as Word, Excel, PDF, HTML, CSV, Image and XML files. The image below is a sample Logins report outlining logins to the server, failed logins and more for a single SQL Server instance.

Reporting - Description: This is how SQLTreeo Monitoring reports looks like.

You have a number of reports at your disposal that mimic numerous SQL Server Management Studio (SSMS) nodes. In fact the reports menu is almost identical to SSMS as you can see on the next screen capture, which provides a great deal of flexibility to meet your internal reporting needs.

Reporting Panel - Description: The reporting panel resembles SSMS.

How do I get started with SQLTreeo?

  1. Check out the free resources available for SQLTreeo.
  2. Download SQLTreeo to see how it can help you.
  3. Think about all of the challenges you face with SQL Server monitoring, failed SQL Server Agent Jobs, issues impacting the organization, backup failures, SQL Server Replication out of date, etc. and how SQLTreeo can solve these problems immediately without your team having to reinvent the wheel.
  4. Communicate with your team and management about how you think SQLTreeo will help such as:
    1. Ability to seamlessly monitor SQL Servers on premises and in the cloud with centralized and intuitive dashboards in a secure and safe manner.
    2. Backups, Jobs and Replication are business critical when they directly impact the bottom line.  Donít let an issue go unnoticed that can be easily prevented.
    3. Alerting based on industry counters that can be customized per instance to be most effective.
    4. Streamlined reporting across all of your SQL Server instances related to databases, events, security, server objects, replication and more.
    5. Minimize monitoring efforts across the organization at an affordable price.
    6. No need to purchase an additional server and licensing for monitoring, SQLTreeo provides a flat rate monthly solution to enable you to focus on your core business.
    7. SQLTreeo pricing starts at $6.00 USD per instance per month with an unlimited number of users.  The Replication and High Availability modules are priced per environment
  5. Put SQLTreeo through its paces in your environment, share the results with your team and determine your next steps.
Next Steps
  • Donít let SQL Server monitoring take over your day.  Be sure to find the right solution for your organization.
  • You can take a free 14 day fully functional trial by registering at the following link: https://www.sqltreeo.com/sqltreeo-monitoring-platform. You wonít be asked for your credit card, just an email address and a password to get started.

MSSQLTips.com Product Editorial sponsored by SQLTreeo makers of SQLTreeo Monitoring.



Last Updated: 2017-10-02


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools