So you have downloaded SQL CoPilot and now you want to get started checking, analysing and investigating issues on your SQL Servers.
SQL CoPilot is designed to be very easy and intuitive to use, without the need for help files or detailed documentation. This article is a quick guide to getting started so you can get on using it as quickly as possible.
Download and Install
First download and unzip the zip file. It contains just one file - Install.exe. This will install SQL CoPilot very quickly. It relies on version 4.5.2 of the .NET framework, and will download and install this if not already present.
SQL CoPilot performs in-depth checks, for which membership of the sysadmin role is required.
However, if you have lesser permissions SQL CoPilot will still work and provide the information available to your level of access.
On each page of information, a check is made and a message is displayed if your permissions are insufficient. See Permissions for more information.
Add SQL Server Instances
The first time you run SQL CoPilot you will be taken to the Manage Instances page where you can start adding SQL Server instances.
There are currently 3 ways to do this.
- If someone else in your company is using SQL CoPilot already and has taken a backup of their configuration. You simply restore from that backup.
- The second option is to add instances manually, one at a time. This is the approach to take if you have only a few instances to look after.
- The third option is to import the instances from a text file or regsrvr file.
One new feature in version 2 is the ability to associate tags with instances. This makes it easy to filter the instance list to quickly find the instance you want, and is especially useful if you have a lot of servers.
SQL CoPilot comes with a few tags preloaded but you can modify or delete these and add more of your own. There is no limit to the number of tags you can use or create.
The final piece of configuration you can perform is to fine tune the thresholds that are used to highlight warnings and critical issues as you navigate through the application.
Finally, make sure to backup the work you have just done so that it can be restored in the event of disk failure, etc, or if another copy is installed on a different PC.
This is where you add, edit and remove SQL Server instances in SQL CoPilot.
While the All Instances tab is selected, click on Configuration in the navigation and it will expand to show several options. The first of these is Manage Instances.
Clicking on this displays a page listing all instances that SQL CoPilot knows about. This will be empty when you first get started.
Add a New Instance
To add a new instance, click on the Add Instance button in the toolbar above the instance list.
The dialog that is displayed is self-explanatory, with the exception of the tags multiselect. Tags are used in SQL CoPilot to group and organize instances. Select one or more from the multiselect and click the Add button. The instance is now displayed in the list.
For more about tags, see below.
Instead of typing the names of hundreds of instances into SQL CoPilot you can import them from a file.
Click on the Import Instances button and a file dialog is displayed from where you can select your file.
There are 2 types of file to import from:
- The best option is to import from a regsrvr file. This is the file created when you export Registered Servers in Management Studio.
The benefit of this is that a tag is created for each folder and automatically assigned to the correct instances.
- The other option is to create a text file containing one SQL Server instance on each line.
This will import the instances without any tags assigned, so they will have to be assigned manually if you wish to use them.
For both types of file, duplicates are ignored, as are instances that have already been added previously.
For all imported instances SQL CoPilot makes the assumption that Windows authentication will be used (which is best security practise) but you can edit any instance if you need to specify a SQL login and password.
In the Manage Instances page click on an instance to edit it (change the name, switch between Windows and SQL authentication, and assign tags) or to delete it.
SQL CoPilot comes preconfigured with a number of common tags, but it is likely that you will want to delete or edit some of these and add new tags to properly reflect your estate.
To add a tag click on the Add Tag button, enter the description in the dialog and choose a color.
To edit or delete a tag click on it in the list. You can then modify the description or color and click Save or click Delete to remove it permanently from SQL CoPilot. This will affect all instances that are associated with the tag.
As you navigate through the many pages within SQL CoPilot you will see that some values are highlighted in orange (for warnings) and red (for critical issues that may affect performance or data integrity). The thresholds for these alerts are supplied with sensible defaults but you will probably want to fine-tune some of them to properly reflect your estate.
The Manage Thresholds page displays all of these thresholds in a list. You cannot add more thresholds or delete existing thresholds, but you can edit the value of any one by clicking on it. Modify the value in the dialog and click Save. The change is effective immediately, and will show the next time a relevant page is visited or refreshed.
The instances you have added to SQL CoPilot, along with the tags and thresholds, are all stored in a file in the roaming profile for your logged on user. This is fine unless you share your PC with someone else - perhaps you would like to share your settings. Or you need to install SQL CoPilot on a colleague's PC. Or you just want a backup in case your hard disk fails.
Backup/Restore is where you can backup to a .bak file for safe keeping or restore from one you or someone else has previously created.
Click on the appropriate button and select the backup file.
The Instance List
The instance list is the first page you see after starting SQL CoPilot (apart from from the first time, when Manage Instances is shown) and is the hub of the application.
Use the filters at the top of each column to limit the displayed instances by version, edition or tag. This is where you reap the benefit of creating and assigning tags to instances.
You will notice a blank entry at the top of each filter list - select this to clear the filter.
The version and edition columns may be empty for instances that have recently been added. This information is fetched from each instance the first time it is clicked on, or when it is included in one of the multi-instance reports (see Reports).
Click on an instance to show a summary screen showing some configuration details, metrics from current activity and a list of all databases.
Assuming you have the necessary permissions, you will now notice two things.
- The instance summary has been placed in its own tab, which can be closed by clicking on the x in the tab heading. Switching back to the instance list allows you to click on another instance which also opens in its own tab. Several instances can be kept open in this way allowing easy switching between them
- The list of navigation options in the left column has now expanded. This is how you navigate around the application, as well as by clicking on links on each page. Additional navigation will appear as appropriate, for example when viewing database tables or indexes
The instance summary is just one of several dashboards available, all under the Dashboards heading in the navigation menu. Clicking on different areas in the page takes you to other pages within the application, as do the navigation options on the left.
Try clicking around and investigate what is available.
New to version 2 is the ability to quickly query multiple instances and store the results in a report. These reports are always available and can be exported to Excel.
With the All Instances tab selected click on the report in the navigation sidebar.
Currently there are 4 reports available:
- Instance Report - listing all instances with information such as version, edition, server collation, etc
- Database Report - listing all databases in each instance, with size, owner, collation, compatibility level, and more
- Best Practices Report - performs a check of over 50 best practices and list instances where they may not be followed. See SQL Server Best Practices for full list of best practices in the report.
- System Health Report - lists instances with potential issues. See SQL Server Health Check for full list of issues that may be included in the report.
Click on the Generate Report button in the toolbar and you are presented with a list of instances similar to the Instance List with filters at the top of each column.
If you want to generate a report for a subset of these instances, use the filters to limit what is displayed in the list. This is where tags are especially useful. It is worth spending the time adding tags and assigning them to instances so that filtering lists like this becomes easier.
Click on the Generate button in the dialog that is displayed and wait for the report to be generated.
SQL Server Monitoring
Now that you have added your instances and familiarised yourself with some of the features of SQL CoPilot, you can configure it to monitor your instances silently in the background, and let you know when it finds any issues.
Go to Setup SQL Monitoring to learn how.