Chapter Review: The Utility Database (by Chris Shaw) and how it applies in Cloud Computing

Sometimes you end up reading a great chapter and feel like writing a review about it… well here it is! Smile  Chris Shaw (@SQLShaw, http://chrisshaw.wordpress.com ) wrote a chapter in the PRO SQL Server 2012 Practices book published by Apress (http://www.apress.com/databases/sql-server/9781430247708).  Chris wrote about one of his habits as a SQL Server consultant: the Utility Database. At first I wasn’t sure what this was going to be about. But after reading the first few lines I could immediately relate to my past experience as a DBA. In a nutshell, DBAs constantly face the same dreaded question:  “what just happened?”. It could be failed backup operations, sudden poor performance, jobs not running… and so forth.

Chapter Overview

The chapter about the Utility Database helps you understand how to properly build a monitoring mechanism that watches your database(s) and helps you identify problems before they occur. The premise of Chris’ chapter is that you are better off being proactive than reactive when database issues occur. Because this is a book on SQL Server Chris walks you through many of the concerns that apply on that platform specifically, including AlwaysOn, Clustering, Jobs and so forth. He shows you how to structure your tables depending on the type of data you are collecting and how to configure your system to start data collection.

Generally speaking Chris recommends that you evaluate and monitor important areas such as (partial list):

Data Growth
Security
Error Logs
Indexing
Fail Overs
Disk Space

Chris also discusses the importance of KPI (Key Performance Indicators). KPIs are not systems metrics; they are considered business metrics. However identifying specific business metrics and keeping track of them can be just as important as environment metrics. Last but not least Chris tells us that every database system implementation is different, so the recommendations in the book should be used a guidelines and customized to you own needs.

Utility Database for Windows Azure SQL Databases

Equipped with Chris’ recommendations we can discuss how they apply to cloud computing, and more specifically to SQL Database. With SQL Database, many of the concerns that make sense for SQL Server do not apply. For example you can’t monitor the disk space or the CPU on SQL Database. Although certain concepts do not apply, others only apply to SQL Database and not to SQL Server, such as Federations, Error Logs (different kinds), data consumption (used for billing) and firewall settings to name a few. And because performance is just as critical in the cloud due to the throttling behavior of SQL Database, indexing and data growth as extremely important for monitor. So even if Chris’ chapter is strictly covering SQL Server, many of the concepts and recommendations apply with SQL Database.

In addition to indexing and long running stored procedures and T-SQL statements, some of the information you may want to track in SQL Database specifically include:

Database Size
Event Table (in master: sys.database_connection_stats and sys.event_log)
Federations (history data)
Version Engine
Firewall settings

There is one minor detail to discuss however: how to collect the information. The chapter discusses how the Utility database is deployed and how to use Scheduled Jobs to run scripts automatically. In essence, the Utility database is deployed on every server in your farm and a job runs stored procedures on every database in SQL Server.

The first issue is that there is no such thing as SQL Agent in the cloud, so you can’t run scripts natively in the cloud. But don’t despair. Microsoft recently announced support for Linked Servers from an on-premise SQL Server database to one or more SQL Database instances. So now you can create jobs locally in SQL Server and execute stored procedures located in SQL Database instances.

The second issue is that there isn’t a concept of a “database server” in SQL Database; a SQL Database server in a logical grouping of database, each of which can be found on different servers. But that’s not really an issue by itself; you can simply create the Utility Database within each database by creating its objects in a separate schema container (let’s call it the Utility schema). This way the Utility Database is stored within each database; the scheduled jobs will need to programmatically enumerate through each database in the cloud (which is possible by querying the sys.databases table in master) and run the stored procedures inside the Utility schema.

If you are using Federations the concept is the same; simply deploy the Utility schema to every federation member, et Voila! Your job would also need to detect all the federation members programmatically, but that not a problem because you can access this information by querying sys.federations.

In summary, the Utility Database presented by Chris Shaw has many benefits for both on-premise SQL Server and cloud SQL Database environments. While some of the concerns are the same, you may need to customize the Utility Database accordingly to adapt Chris’ work to SQL Database instances.

About Herve Roggero

Herve Roggero, Windows Azure MVP in South Florida, is the founder of Blue Syntax Consulting, a company specialized in cloud computing products and services. 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 and runs the Azure Florida Association (on LinkedIn: http://www.linkedin.com/groups?gid=4177626). For more information on Blue Syntax Consulting, visit http://www.bluesyntax.net/.

Twitter