- Download the ZIP file and copy the "HealthCheck" subdirectory to the desired location.
- Open DBList.txt and replace server and database names as required.
For example:
Server1\InstanceA; DB1
Server2\InstanceB; DB2
For servers that don’t have the SQL Browser service enabled, you can specify the port number delimited by a back tick and a comma, as follows:
Server1`,1433; DB1
- Execute HealthCheck.bat. The report can be found in the \HealthCheck\Reports directory.
The batch file reads a list of SQL Server instance and database names and passes them as parameter values to a PowerShell script, which it executes in a loop, once for each database. The PowerShell script then executes a set of diagnostic T-SQL and PowerShell scripts, exporting metadata to CSV files. Finally, a VBA macro is executed from PowerShell to merge the CSV files into an Excel spreadsheet and then formats the report.
The following software is required to run the health check:
- PowerShell version 2.0 or higher (comes with Windows 7 and Windows Server 2008 R2)
- Microsoft Excel 2007 or higher.
- SQLPS PowerShell module, which is installed with SQL Management Tools.
A windows account must be used with the following access to the target server and database:
Server Scope
- View Server State
Database Scope
- View Definition in target database.
- ddl_admin to run DBCC SHOW_STATISTICS if the target server is pre-SQL 2012 SP1, otherwise db_datareader is enough.
Remote WMI access is required to run the ServerInformation.ps1 script. The following article has details on how to grant this access to non-admin users.
http://www.adilhindistan.com/2013/10/permissions-to-access-wmi-remotely.html
If the user does not have the necessary access, the report will still run but will not include the server information worksheet.