Today's #TipTuesday post is for anyone who might be running more than one local instance 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 that somewhat resembles what version of SQL Server 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

For those who run multiple instances of SQL Server for development or test purposes, it's quite likely that all of them don't need to be running all the time. Multiple services can be involved and that simply adds some overhead to the computer if they don't need to be 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$ABCD" where ABCD 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.

A screenshot of Notepad shows the 2 net start commands for SQL and SQL Agent.

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

A screenshot shows the result of the batch file in Command Prompt.

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

A screenshot shows the net stop command in Notepad.

When running this one, note it doesn't specify anything about SQL Agent, it will recognize if SQL Agent is started and prompt 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.

A screenshot shows the result of the net stop command prompting me to confirm the action.

That's it for this tip…