SQL ConstantCare® Quick Start

“10TB of data on a Commodore 64, interesting”

1. Set up the SQL ConstantCare® app.

Don’t install this on the SQL Server itself – just install it on your own workstation or a jump box works fine too. The box doesn’t need to be up 24/7 – data collection happens once a day on the schedule you choose, and as long as you’re logged in at that time, it’ll fire off and run.

  • Download and extract the SQL ConstantCare® installer (ZIP)
  • Run setup.exe by double-clicking it. It’ll flash by really fast – that’s okay. We’re quick like that.
  • Open a command prompt window as an administrator
  • Type cd %localappdata%\ConstantCare and hit enter
  • Type dir and hit enter
  • Note the directory that matches the format of app-x.x.x
  • Type app-x.x.x\constantcare.exe (using the same folder name you just saw above). This will ask for your email address – this is the email that you want the reports to go to. You don’t have to use the exact same email address you purchased with – you can use a distribution list, for example, if you want other people to get the emails while you’re on vacation. However, if you use a distribution list, make sure it will accept emails from sqlconstantcare@brentozar.com.
  • Type this to get into the current version directory to start adding your servers:
    cd current

2. Add one (or more) of your SQL Servers.

Go to a command prompt, get to the ConstantCare folder, and run:

You have to specify the time zone because it’s not necessarily correct on the server, and we wanna make sure we get your data right. Here are some common time zone examples:

  • Etc/UTC
  • America/New_York
  • America/Denver
  • America/Chicago
  • America/Los_Angeles
  • America/Sao_Paulo
  • Australia/Sydney
  • Asia/Tokyo
  • Asia/Shanghai
  • See the rest at Wikipedia

You can repeat the process for as many of your servers as you like – your SQL ConstantCare® subscription is priced per user, not per server. (We wanted to make sure you got insight on all your stuff, development and disaster recovery included.) We support Microsoft SQL Server 2008 and newer, including Amazon RDS. (No SQL 2005 or prior.)

If you have fancy characters in your server name, like commas, colons, slashes, or Johnny Depp, then you need to enclose the server name in double quotes exactly as shown in the command above.

If you don’t want to send your plan cache in for advice, leave off the –sendplancache true parameter.

Special configurations: SQL auth, Azure, query plans

If you use SQL authentication, add parameters for userid and password as shown below. Passwords with special characters like & need quoting. If you’re creating a custom login for SQL ConstantCare, it needs SA permissions to get things like CHECKDB history. You can use VIEW SERVER STATE instead, but be aware that you’ll get a lot of warnings during collection, and your advice from us will include a lot of false alarms (since we can’t see things like CHECKDB history.)

If you’re in Azure SQL DB, also include the –userdb parameter and the name of your user database. Only send in data for one database per server, though. (We don’t support multiple databases per Azure SQL DB server, and if you send in data for multiple databases, you won’t get accurate performance advice.)

If you use a non-default port number (not 1433), add a comma and the port number in the data source, like this:

3. Send in data and schedule a daily collection.

After you’ve added a server or two, test the connection by running:

That’ll connect to our servers and upload your first files. If you get any errors – especially if you see “SQL connectivity failed” – hit the Troubleshooting page. Just because you see a lot of text flying by doesn’t mean we connected to your servers!

While you’re there at the command prompt, this will set up a scheduled task to collect data daily – but note the file path:

You’ll need to change the file path to the folder where ConstantCare.exe lives, like change the “Brent” to your own account:

Why in the afternoon? Because a lot of SQL Servers clears out their in-memory diagnostic data they’re under heavy memory pressure, like overnight during backups & CHECKDB. We run really low-impact, non-blocking queries, so it shouldn’t be a problem. You can pick a different day and time if you like – anytime is completely okay. You only have to schedule one, though – one collection gets data from all of your servers.

Trigger the scheduled task to test it:

If a new command window pops up with a lot of text flying by, congratulations, you’re done! You’ll get a prescription email for each server soon. You should get an automated email within an hour or so – if you don’t, odds are, you didn’t actually add any servers in step 2 above, or you left my name in your task setup path. For more help, hit the Troubleshooting page.

As long as your machine is up at the right schedule time, it’ll send your SQL Server’s diagnostic data in at the time you picked. If you’re setting up collection from a machine where you’re not regularly logged in, edit the scheduled task manually in Task Scheduler to check the box to run even when you’re not logged in.

For more common tasks, like adding a server, removing a server, or listing the connections, check out the full list of collector parameters by running:

If you get an error message, or if you don’t get a new command window popping up, hit the Troubleshooting page.

Within around an hour,
you’ll get a prescription email.

In about the same time it’ll take your pizza to arrive (note: we are not actually ordering a pizza for each server’s data), we analyze your data, then give you advice with what we’d recommend doing to make your SQL Server faster and more reliable.

The emails will be from SQLConstantCare@BrentOzar.com. If you’ve got questions about the contents, or if you want consulting help to get the problems fixed, just let us know. The other side of the screen is real human beings. (Well, maybe surreal.)