You could be forgiven for not looking at your error logs….but only once….after all, look what happened to Krypton!
In this multi part series, we walk through how to transform the volumes of mundane SQL Error logs for your entire SQL Server fleet, into insightful, integrated and simplified 30,000 foot view dashboards. Consolidating your error logs and creating Powerful Centralised Dashboards on a common Sharepoint 2010 platform then creates other flow on benefits, such as delegating those monitoring tasks to someone else – like the helpdesk !
In this first part of a multi part series we consolidate the error logs for your entire SQL Server fleet into one table. I decided not to reinvent the wheel here….after all, Rodney Landrum has already done an outstanding job of creating a SSIS package for you already – download the source code here.
Rodney also has published many other useful methods of monitoring your SQL Server databases and an excellent Ebook to download called SQL Server Tacklebox full of work smarter not harder solutions, but enough of the Rodney plug…
Skills required for this walkthrough are Database and SSIS. If you are new to business intelligence then this will be a perfect end to end learning tool, allowing you to apply these techniques to data you are very familiar with….SQL Server Error Logs ! Lets move on with staging and consolidating the data we are interested in.
1. To get started, download the original package here and extract the solution files to a common folder where you will be able to add additional solutions from this series later. If you are having trouble with downloading Rodney’s package just Download All My Part 1 Files Here containing the SSIS solution, the specific database objects required and the entire database backup. Whilst SQL 2012 now released I have used SQL 2008 R2 versions of files for better compatibility.
2. Create yourself a database that will contain your consolidated error logs, then create the database objects required. You can either create the database objects manually or just restore the starter database both of which are available in my Part 1 Download Link.
4. Populate the SSIS_ServerList configuration table to include one row for each server you want to manage.
As an example you could build a series of insert statements
INSERT dbo.ServerList_SSIS (Server, Connect, DMZ) VALUES ('YourSQLInstanceName',1,0)
5. Open the SSIS solution and update the Connection Manager named “DBA_Rep” to reflect the correct connection string of your new database.
6. Set the Package Protection Level property accordingly (e.g. don’t save sensitive) then save the package.
7. Create a SQL Server Agent Job that runs this package. This will require adding a job step that runs the SSIS package either from file location or MSDB database (whatever deployment method you choose for the package). If unsure just go for the file location option. A good general reference for running SSIS packages through SQL Server Agent jobs is outlined here.
8. Schedule the package to run once daily (say, 7am). This will capture the overnight logs which will set us up well for Part 2 of this series.
9. Run the package and make sure the package runs successfully. Note – before we move onto Part 2 of the series, you need to ensure there are rows in the consolidated error log table for each of the servers you configured in the ServerList_SSIS table.
SELECT Server, COUNT(1) AS CountErrorLogRows FROM SQL_ErrorLog GROUP BY Server ORDER BY Server
In Part 2 of this Xray Vision for SQL Server Error Logs series – we use a second SSIS package to funnel our consolidated error logs into a predesigned Kimball Star Schema data model. Stay tuned !
If you have any quesitons please Get in Touch on Linked In.