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.
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.
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.
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.
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:
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 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 Excel reports (see Excel 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 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 an Excel spreadsheet.
With the All Instances tab selected click on Excel Reports in the navigation sidebar.
Currently there are 2 reports available:
More will be added soon.
Click on the button for the report you want to run 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 specify the file name of the Excel document.