Drop down boxes not working in SQL Server 2012 Configuration Manager

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:

NoValues

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:

Values

 

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
Advertisements
This entry was posted in Powershell, SQL Server. Bookmark the permalink.

2 Responses to Drop down boxes not working in SQL Server 2012 Configuration Manager

  1. Paulo Morgado says:

    If what you’re trying to change is the service settings, you can do so using the services.msc, although I would prefer the easily repeatable PowerShell option.

    The worst is setting ports and other stuff. I hade some success using WMI on some systems but on a few I had to go directly to the registry.

  2. dave says:

    One is for 64 bit and one is for 32 bit. The System32 version is the 64 bit one and the syswow64 is the 32 bit version.
    Same thing goes for odbc administrator which is how I found this out.
    I don’t know why Microsoft decided to break with the convention started with windows 3.0:
    windows 3.xx – windows\system folder contains the working modules – 16 bit stuff
    windows 95 (and all 32 bit vers.)- windows\system32 – contains the working modules – 32 bit stuff
    64 bit windows – you’d think there would be a windows\system64 but NO. windows\system32 – contains the working modules – 64 bit stuff, syswow64 contains the 32 bit stuff.
    same with the registry, 32 bit programs read their keys from the new syswow64 sub hives.
    it means “System Windows On Windows64”
    Happy motoring.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s