Today’s #TipTuesday post is for anyone who might be running more than one local instances of SQL Server. I often have at least a couple of instances on my laptop, as historically I would want to have the ability to mimic some client installs, as close as I can. While I am no longer a consultant, I will continue to do this so that I can have an instance the somewhat resembles what version of SQL and Dynamics GP we use at work + the latest version of GP with all the latest things installed. For blogging alone, I want to have the latest and greatest to play with and learn what’s new.

Running multiple instances of SQL

If you do run multiple instances of SQL Server, it’s quite likely that you don’t actually need them running all the time. There are multiple services that can be involved and that simply adds some overhead to your computer if you otherwise don’t need it running all the time.

So… I set up batch files for starting and stopping the instances of SQL that I’ve got running. When I want to spin up an install of Dynamics GP, I simply execute the batch file to turn on the SQL instance I need for that version and when I’m done, I run another one to stop it. It’s quick and painless.

Batch File structure

The format for starting SQL Server looks like this: “net start MSSQL$XXXXXX” where XXXXX is the instance name. I put in the net start command for SQL Agent too since I’m often testing things where I want to test jobs or other agent tasks as well.

Running this batch file produces this, but the window closes automatically when the command is complete:

The format for stopping SQL Server looks like this: “net stop MSSQL$XXXXX” and again, the XXX is the instance name.

When running this one, which you’ll note doesn’t specify anything about SQL Agent, it will recognize if SQL Agent is started and prompt you to stop it as part of this. I left it this in intentionally although if I don’t want to have to type Y every time I could have put a net stop line in here for the agent too.

That’s it for this tip…