After a data centre power outage a decision was made to switch the start up mode of a bunch of our SQL Servers to manual so that the on call DBA could manage the reintroduction of the servers in a controlled fashion.

I jumped onto the first of our Windows Server 2012 R2, SQL Server 2012 SP1 boxes and ran SQLServerManager11.msc to start SQL 2012 configuration manager. Select the SQL engine service, right click and select properties, click the service tab and click the start mode drop down:

The drop down list has no values populated. (okay – I took the screen grab after I’d changed the setting but you get the picture)

Strangely when I opened SQL Configuration manager from the application screen I got the dropdown values as expected:

After a bit of digging around I found that there were two versions of SQLServerManager11.msc installed. One in C:\Windows\System32 and the other in C:\Windows\SysWOW64. The one in System32 doesn’t populate the drop down, the one in SysWOW64 does.

The first value in the list for my path environment variable on the server is? … %SystemRoot%\system32;

Mystery solved (sort of – I still don’t understand why two different versions of the snap in are installed – and more particularly why one doesn’t work).

I toyed with a few different workarounds for this. I could just use the app screen to start configuration manager. In one experiment I added %SystemRoot%\SysWOW64; to the path environment variable – and renamed the snap in in the System32 folder. That worked – but felt like a hack.

Because I’m trying to learn powershell at the moment, and building up my script library of DBA tasks, I ended up making the configuration change with powershell scripts.

Check the settings of all local SQL services:

get-wmiobject win32_service | where{$_.Name -like "*sql*"}

Change the start mode of the default SQL Server engine:

set-service mssqlserver -startup manual

and the SQL Agent service:

set-service sqlserveragent -startup manual

Just a tip – if you are working with named instances you need to escape the $ sign in the name:

set-service mssql`$[instance name] -startup manual