Welcome to our blog post on SQL Server concepts and ideas! In this article, we will discuss the process of setting up a SQL Overview package and creating reports based on the collected data. This package is designed to run on a SQL Server Default Instance, but can be executed from a remote client with some modifications.
Setting up the Package
To begin, make sure that the SQL Agent service on the host server is running with a Windows ID that has access to all the instances that will be accessed. Create a database called SQL_Overview if it does not already exist. Next, download and extract the SQL_Overview_Part3.zip file to a directory on the server.
Once extracted, execute the SQL_Overview_Part3_Tables.sql script to create the necessary tables in the SQL_Overview database. If you encounter any warnings about index length, you can safely ignore them. If necessary, you can drop the index without affecting the performance of the package.
Next, execute the SQL_Overview_Part3_Tempdb.sql script to create the TEMPDB tables that will be used by the package. It is important to enable OLE Automation on each SQL Server 2005 instance to collect disk space information.
Review the contents of the SSIS_ServerList table and add the servers and instances that you want to include in the package. You can skip a server by setting the Skip_SQL_Overview column to true. If the table is empty, you can load it using the provided script.
Open the solution on the server using SQL Server Business Intelligence Development Studio and fix any yellow or red icons by following the instructions provided. Save the solution and run the package by pressing F5.
Outline of Tasks
The SQL Overview package includes several tasks that collect various information from the SQL Server instances. These tasks include:
- Truncate Tables – cleans out tables before they are loaded by the package
- Populate ADO Variable – reads the SSIS_ServerList table and passes the server\instance names to each container
- Collect Backup History – gathers each database’s backup history for the last 31 days
- Collect Database Info – gathers information about the databases and the files allocated to each of them
- Collect Database Status – gathers the status of each database
- Collect Disk Space – gathers the space usage on each drive
- Collect ErrorLog – gathers the last 2 days of SQL Server Error Logs
- Collect Job Info – gathers setup information about the SQL Agent jobs from each instance
- Collect Job History – gathers the SQL Agent job history for the last 7 days
- Collect Server Info – gathers SQL Server instance information
Each task collects specific information that can be used for various purposes.
Reports
After the data has been collected from all the instances, it’s time to generate some reports. The sample reports included in this article focus on error messages captured during the execution of the SQL Overview package and instances that have been restarted since the last execution of the job.
To create these reports, three jobs are executed through SQL Agent. The first job executes the SQL Overview package, while the other two jobs are responsible for generating the reports. The reports are sent via Database Mail, so make sure it is set up before running the jobs.
The SQL Overview package is expected to be executed each morning, followed by the creation of the reports. The reports are simple but provide a framework for adding additional reports in the future.
Conclusion
In this article, we explored the process of setting up a SQL Overview package and creating reports based on the collected data. The package collects a wide range of information that can be used for various purposes. The sample reports provided serve as a starting point for creating your own set of reports.
Remember, this package is designed for SQL Server 2005 and later versions. If you are using SQL Server 7, you can create a scaled-down version of the package specifically for SQL Server 7 instances.
Thank you for reading our blog post on SQL Server concepts and ideas. We hope you found it informative and helpful. Stay tuned for more articles on SQL Server!