As a SQL Server DBA, one of the challenges I face is efficiently monitoring and reporting on the status of multiple SQL Servers. In the past, I relied on a combination of SQL Server Reporting Services and SQL Server Integration Services to generate daily reports. While this solution worked well, it had some limitations that I needed to address.

One of the main limitations was the reliance on a time-consuming SSIS package that only executed three times a day. This meant that the data in the reports could be up to 15 minutes old, leading to potential inaccuracies and delayed decision-making. I needed a solution that would allow me to run ad-hoc queries against multiple SQL Servers simultaneously and get real-time results.

Fortunately, I discovered two Red Gate tools that could help me overcome this limitation: SQL Backup and SQL Multi Script. SQL Backup stores backup status information in a SQL Compact Edition database, which can be accessed using a stored procedure called MASTER..sqbdata. This stored procedure allows me to retrieve backup history and other relevant data.

With SQL Multi Script, I can define a query that collects the backup information I need and pass it as a parameter to the sqbdata stored procedure. I can then run this query across multiple servers, getting a single result set for review. This allows me to quickly identify backup failures and take appropriate action.

For example, I can easily identify backup failures that were not notified via email due to misconfigured email addresses. By analyzing the error codes and messages provided in the result set, I can determine the cause of the failure and take corrective measures.

Another useful feature of SQL Backup and Multi Script is the ability to calculate the space required for backups. By combining these tools, I can execute a query that provides the size of each backup job, taking into account compression. This eliminates the need for manual analysis and estimation, providing accurate and actionable information.

In addition to backup monitoring, I can also use SQL Backup and Multi Script to analyze backup schedules. By querying the backup history, I can determine the time at which backups are occurring and identify any conflicts or performance issues caused by excessive disk activity.

Overall, the combination of SQL Backup and SQL Multi Script has greatly improved my ability to monitor and report on the status of SQL Servers. With real-time data and efficient querying, I can make informed decisions and take proactive measures to ensure the smooth operation of the database environment.

If you’re a SQL Server DBA looking to enhance your reporting capabilities, I highly recommend exploring the features of SQL Backup and SQL Multi Script. These tools can save you time and provide valuable insights into your SQL Server infrastructure.

Disclaimer: This blog post is not sponsored or endorsed by Red Gate. The mentioned tools are used as examples and there may be other similar tools available in the market.