SQL Server monitoring. Learn to monitor quickly and efficiently
What does SQL stand for? This is the first question we have to ask ourselves before talking about monitoring. Well, SQL stands for “Structured Query Language” which, by the way, is a standard and interactive programming language used for obtaining or updating information from a database.
At the same time, SQL is also an ANSI and an ISO standard; it is true that there are many database products that support SQL with proprietary extensions from the standard language. Therefore, consultations have a command language format that allows you to select, so that you can insert, update and find out the location of the data, and some other things that we will see later on.
Now, the big question is: what does SQL Server mean? It’s just a relational database management system, “RDBMS” from Microsoft that is designed for the business environment. Furthermore, we have to mention that SQL runs on T-SQL or, if preferred, on Transact-SQL, which is a collection of Microsoft and Sybase programming extensions, that has a lot to do with transaction control, as well as the handling of errors, along with declared variables and with row processing.
About the Relational Database Management System or “RDBMS”, let’s say that it is a program that allows us to create, update and manage a relational database, just like most of the commercial RDBMS make use of a structured query language, which, as mentioned earlier, is the same as SQL. They use that language, to access the respective database. Although SQL was created or invented after the development of the relational model, its use is not necessary.
On the other hand, let’s say that the most important RDBMS products are DB2 (from IBM), Oracle and, of course, Microsoft SQL S. We need to bear in mind the challenges proposed by technologies from competitors and also, the recurring statements of some experts, who say that none of the current RDBMS has fully applied the relational principles; anyway most of the new business databases are created and managed with an RDBMS.
Free SQL Server tools that will make your life easier
We must remember that we are talking about “open” source monitoring software, which is a freely developed and distributed program. Even though we will talk about software that is not free (paid versions), there are free versions of each software.
1. SQL S. Management Studio “SSMS” Add-ons (100% free)
SQL S. Management Studio or “SSMS” is an integrated environment that is used to manage any SQL infrastructure, and it can be used to access, configure, manage and develop all SQL S components from SQL Data Warehouse and Azure SQL Database. In addition, the SSMS provides a comprehensive utility and it also combines an extensive group of graphic tools with various script editors, in order to provide database administrators and developers access to SQL S.
In order to use this monitoring tool, you must download SQL S. Management Studio or “SSMS”, and then proceed to download SQL S. 2016 Developer and finally download Virtual Studio. The components of SQL S. Management Studio are:
- The object explorer
- The template browser
- The solution explorer
- And the visual database tools
Regarding the object explorer, let’s say that it should be used to view and manage all the objects at once in SQL S. also, Template Explorer is used to compile and manage repetitive text files, which can be used to speed up the query and script development. About the solution explorer, we have to say that it is used to create projects used for “managing administration elements”, as in the case of scripts and queries.
About the use of Visual Database Tools (included in Management Studio). SQL S. Management Studio includes visual designers with the purpose of creating queries, Transact-SQL diagramming databases as well as tables. So, Let’s begin with the Visual Database tools, we will use:
- Database diagram tools
- Visual table design tools
- Visual consultations designer
- Visual design tools (the ones included)
- The language editor of (Management Studio), to compile and debug queries and scripts, interactively.
1.1. SQL Server Management Studio tutorial
This tutorial applies to SQL S., to SQL Azure Database, to SQL Azure Data Warehouse and to the Parallel Data Store. SQL S. Management Studio, offers a graphical interface to configure, monitor and manage instances of SQL S., while also providing language editors Transact-SQL, DMX, MDX and XML, for editing and debugging scripts.
We will learn about the presentation of information in SSMS and how to get the most out of those specific characteristics. So, let’s say that the best way to be comfortable with the SSMS is through practice. Therefore, we will see how we can manage the components of SSMS and, also, how to find the features offered.
First, we have to install:
- the latest version of SQL Server Management Studio (SSMS).
- SQL S. 2016 or a later version with the database as an example.
- AdventureWorks must also be installed using AdventureWorks2014 (OLTP). It is also possible to install AdventureWorksDW2014 (Data Warehouse).
From all these downloads and installations that we have just mentioned; only AdventureWorks2014 (OLTP) deserves an additional explanation. Therefore, we can see that this version contains back-up copies of complete databases, in addition to projects for AdventureWorks2014, and scripts. We also have to say that these are used with SQL S. 2014 and later versions.
- AdventureWorks2014 sample databases are an update from the 2012 versions.
- AdventureWorks2014 sample databases also work in later versions. However, no specific updates were made to support new features in those versions.
Downloads of AdventureWorks2014 (OLTP):
- adventure-works-2014-oltp-full-database-backup.zip: this is a sample of an OLTP database. It is also a (complete) backup of the database (.back) of AdventureWorks2014.
- adventure-works-2014-oltp-script.zip: source and script data files to build and install AdventureWorks2014.
- adventure-works-2014-oltp-readme.txt: contains the instructions to install AdventureWorks2014, from the complete backup of the database or, with the script.
- adventure -works-2014-oltp-in-memory-sample.zip: here we have a sample in memory for SQL 2014. It’s essential to install the OLTP version of AdventureWorks2014.
2. Paessler (a traditional monitoring company) offers the following services:
Paessler offers excellent supervision software for SQL, which is a complete tool to supervise databases. It’s very useful, regarding Microsoft SQL, Oracle SQL, MySQL, PostgreSQL, while giving us the opportunity to reduce downtime, optimize performance and make important SQL queries, at a glance. In addition, no additional software is required, so you just need to have a computer with Windows or VM. After the installation of the program, it scans the network and all the sensors that are needed for monitoring that network are created by “Auto-Discovery”.
It’s essential to know that there are two versions, (bear in mind that we are talking about open source monitoring tools). There is a “Free Trial version, available for download, unlimited for 30 days” but, also, there is a “Freeware download” which, in addition to being always free, offers us 100 sensors, which is quite good. Additionally, we have to mention that this Paessler software is suitable for all versions of Windows.
Source: PRTG MANUAL: WMI MICROSOFT SQL SERVER 2012 SENSOR
We also need to say that we can obtain monitoring graphs for free, simultaneously; we can monitor the mobile network, thanks to the apps for smartphones and tablets, which means extra comfort. So, we can receive all the information from our network, on our mobile devices (Android, iOS and Windows Phone).
3. Administration and SQL Server monitoring, using Applications Manager
The monitoring feature from SQL S. of Applications Manager helps database administrators monitor the performance and also the availability of production databases. It is, then, a monitoring solution without agents of any kind, which gives performance metrics to be used, ensuring that the SQL server is executed efficiently.
(Applications Manager) provides a web client that, allows you to view and manage farms of MSSQL server databases while providing deep SQL monitoring data, which monitor patterns of use, notify impending problems, and plan capacity. Then, the root cause analysis window helps DBA from SQL S. solve, very quickly, the performance drawbacks.
Additionally, the grouping capacity helps you group your databases according to the compatible commercial process while helping the team focused on the operations to prioritize the respective alarms. SQL server monitoring is able to connect to the origin of the database and, at the same time, to monitor various column values of the system table. Also, it is capable of collecting data and notifying when the features of the database system have reached a certain threshold, by email or SMS.
Let’s see, now, some of the most important components that are monitored in MSSQL databases:
- Connection statistics.
- Buffer Manager statistics.
- Memory usage.
- Blocking details.
- Cache details.
- Closing details.
- Base details.
- Details for the access method.
- SQL Statistics.
- Scheduled jobs.
Applications Manager is free for companies, this is shown on the website: “During installation, you can choose Professional or Enterprise Edition. Once the 30-day free trial ends, your installation will automatically be converted to the free edition, unless you apply for a business license.”
In addition, it is available for Windows, for Linux and in a large number of countries. This is all you need to know about SQL server monitoring. At this point, you can visit the Pandora FMS Library, where you can find more valuable complementary information.
About Pandora FMS
Pandora FMS is a flexible monitoring system, capable of monitoring devices, infrastructures, applications, services and business processes.
Of course, one of the things that Pandora FMS can control is the hard disks of your computers.
Would you like to know more about what Pandora FMS can offer you? Discover it by entering here: https://pandorafms.com
If you have more than 100 devices to monitor, you can contact us through the following form: https://pandorafms.com/en/contact/
Also, remember that if your monitoring needs are more limited you have at your disposal the OpenSource version of Pandora FMS. Find more information here: https://pandorafms.org
Rodrigo Giraldo, redactor técnico freelance. Abogado y estudiante de astrobiología, le apasiona la informática, la lectura y la investigación científica.