If you deal with SQL Server on a regular basis, or if you are a DBA dealing with production databases, you are most likely using a monitoring tool to gather performance statistics and receive alerts. However when it comes to performing real-time analysis, such as finding out which databases are low in log space, querying all your error logs to search for a specific event, or even to find out which databases contain a specific stored procedure or column name, it can get a bit tricky. Most DBAs will use SQL Server Management Studio (SSMS) to perform real-time queries, and the built-in tool called Central Management Servers; however this tool has multiple short comings. This blog post presents you with an overview Central Management Servers, a new tool called Enzo SQL Manager, and presents pros and cons of both solutions.
Using SSMS Central Management Servers
SSMS offers a good solution for DBAs to query multiple SQL Server databases at once: Central Management Servers (or CMS). CMS is a technology built directly in SSMS and offers the ability to run SQL commands to one or more SQL Servers. You can think of CMS as a sharding technology, which allows you to run the same SQL command on multiple SQL Server instances at the same time, and show an aggregated result as if the command was run on a single server.
For example, in the screenshot below, SSMS is connected to CMS. The Registered Servers window on the left shows you CMS, with localhost\Enzo as the instance that hosts CMS. Within this instance, two servers are registered: DEVLAP02\SQLSERVER2012, and DEVLAP03\SQL2014. From the naming convention of my instances of SQL Server, you can infer that my SQL Server instances are not the same version of SQL Server. Yet, I am able to run a single command (SELECT GETDATE()) and obtain the current datetime information on both instances in the result window. You will also notice that CMS automatically adds the ServerName column (although not specifically requested by the command) so that you know which server the information comes from.
From a configuration standpoint, CMS requires a network login (SSPI), and as a result is unable to connect to Azure SQL Databases. It also means that your SQL Servers must be joined to the same network.
There are other limitations with CMS; for example CMS can only query SQL Server instances; it cannot send the same SQL statement to all databases within an instance (you can run the undocumented sp_msforeachdb command, but this is not a distributed command; it is a loop operation which does not aggregate results). In addition, it is schema sensitive: sending a command to multiple SQL Server instances of various versions could return an error. For example, the following SQL command (SELECT * FROM sys.databases) fails in my setup because the system view “databases” returns slightly different columns in both SQL Server versions:
From an architecture standpoint, you cannot use CMS from a .NET application or any other tool than SSMS, because it is a feature of SSMS and unavailable outside of the application. In other words, you cannot leverage the ability to send parallel queries outside of SSMS.
Using Enzo SQL Manager
Similarly to CMS, Enzo SQL Manager, a solution built on top of Enzo Unified, allows you to run SQL commands against one or more SQL Server instances; it can also run a command against all user databases and will automatically adapt to various schemas. Because Enzo SQL Manager works with database logins, you can include SQL Server instances that are not part of your network, including Azure SQL Databases.
In the screenshot below, SSMS is connected to Enzo SQL Manager running on LOCALHOST,9556 (shown at the bottom of the screenshot), and the command is executed against all the servers registered with Enzo (servers are registered with a separate management interface). Enzo SQL Manager provides views that run built-in SQL commands (or custom-defined commands) against the instances and/or databases; additional columns are also added automatically to identify the machine name, instance name and optionally the database id and name where the data came from. The command below (RowCounts) returns a list of tables in each database with a count of records.
Enzo SQL Manager offers a number of built-in commands, and allows you to extend the list of views by providing a name for the view and the SQL command that should be executed. For example, you can define a new view called CPUActivity which returns the SPID, CPU and LoginName of user processes that have a CPU greater than 0. The checkbox “Execute command against all user databases” allow you to control whether the view will execute on each SQL Server instance, or each user databases within each instance.
Once the view has been defined, you can now run this SQL command against all the registered servers when you are connected to Enzo:
SELECT * FROM MsSqlMgmt.CPUActivity
You can also further filter the results as such:
SELECT * FROM MsSqlMgmt.CPUActivity WHERE loginame <> ‘sa’
Although Enzo SQL Manager does not understand complex queries natively (such as a JOIN or a GROUP BY operation), you can create a custom view with the complex SQL Statement. For example, you could create a custom view that joins Table and Index system tables and make this complex SQL query available through the view.
Since Enzo SQL Manager is a service, you can connect to it using a .NET application or a dashboard, making it easier to create custom monitoring solutions. For example, you could easily add a SQL Job that calls the CPUActivity custom view, and through Enzo Unified make a Phone Call or send a SMS text message when a specific condition has been detected using and SQL statement. For example, the following SQL Job monitors blocking calls against all registered servers and sends a SMS when a blocking issue has been detected. A table variable called tableLocks is declared to store the list of blocking calls returned by Enzo SQL Manager.
DECLARE @tableLocks table (machine_name nvarchar(100), instance_name nvarchar(100), blocked_session_id int)
INSERT INTO @tableLocks
SELECT machine_name,instance_name,blocked_session_id FROM [localhost,9556].bsc.MsSqlMgmt.Blocking
SELECT * FROM @tableLocks
IF (Exists(SELECT * FROM @tableLocks))
DECLARE @message nvarchar(250)
SET @message = 'Blocking issue detected on ' + (SELECT CAST(COUNT(*) as nvarchar(5)) FROM @tableLocks) + ' session(s)!'
EXEC [localhost,9556].bsc.twilio.sendsms 'YOUR PHONE NUMBER', @message
Enzo SQL Manager uses Twilio to send SMS messages; this allows you to send any text message directly from SQL Server by running the Twilio.SendSMS command. This command accepts multiple phone numbers so that you can send a text message to multiple phones. To make this work, you will need to open up an account with Twilio and use the Enzo SQL Management interface to register your Twilio account. This screen is found under Configuration –> Configure Twilio. If you wish to make phone calls from SQL Server, you will also need to make sure Enzo is accessible from the Internet; the Public Enzo Address is the URL where Enzo is accessible from the public Internet. For more information about Twilio, visit http://www.twilio.com.
Pros and Cons Summary
Generally speaking, CMS provides a more comprehensive support for SQL statements; however Enzo SQL Manager supports the creation of views that can contain any valid SQL statement. Enzo Unified supports other capabilities, such as the ability to query databases in parallel, Linked Server (for integration with SQL Jobs for example), automatically adapts to variable schema definitions, is fully extensible and supports Azure SQL Database connectivity.
How To Try Enzo SQL Manager
You can visit our website and download a trial version of Enzo SQL Manager at http://www.enzounified.com/enzo-sql-manager/.
About Herve Roggero
Herve Roggero, Microsoft Azure MVP, @hroggero, is the founder of Enzo Unified (http://www.enzounified.com/). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, aPluralSight author, and runs the Azure Florida Association.
About Enzo Unified
Enzo Unified is a data platform that helps companies reduce development and data integration project timelines, improve data quality, and increase operational efficiency by solving some of the most complex real-time data consumption challenges. For more information, contact email@example.com, or visit http://www.enzounified.com/.