SQLServerCentral Article

Get And Set SQL Server 2005 Instance Registry Values

,

Get And Set SQL Server 2005 Instance Registry Values

There's no way to sugar-coat this: working with SQL Server registry keys in SQL Server 2005 has become harder than ever. There are two major reasons for this.

First, each instance of a SQL Server product (e.g. Database Server, Reporting Services, Integration Services, etc.) is given a MSSQL.n directory name at installation. This "installation number" is also used to store registry keys. The instance name to installation directory lookup can be performed, but its effect is to further hide already obscure yet important pieces of data.

Secondly, Microsoft has tightened the security of its undocumented but widely-used extended procedures to read and write the registry. xp_regread and xp_regwrite (and the new arrivals xp_instance_regread and xp_instance_regwrite, meant to simplify access to instance-dependent registry keys given the above point) will sometimes throw Access Denied errors unless executed by a sysadmin. I say sometimes because my own testing on these procedures appear to be inconsistent, and I can find no solid documentation on these procedures. But Microsoft's recommendation is clear - stay away from these procs, but if you have to use them, do so only as a sysadmin.

Yet and still, the SQL Server registry keys have some good stuff. Some of this information is available through sanctioned front ends and function calls, such as the SQL Server Configuration Manager and @@version. But often it is good to see all the information at once. Especially is this so when comparisons need to be made across server instances on the same hardware.

To meet my own need for this, I wrote the Perl utility SqlRegKeys.pl. Find the code here (Rename this as .pl when you download it). Copy and paste it into your favorite editor and save it to a file called SqlRegKeys.pl.

In order to use the utility, you'll need to install ActivePerl if you haven't done so already. We've always had Perl on our dev and prod server machines and have had no issues as a result. But of course, do the installations on your development servers first. Or, since remote machine names can be specified, simply install Perl on your workstation (as long as you have the rights to examine the server's registry remotely.) Go to www.activestate.com/Products/ActivePerl, press the Get ActivePerl button, and choose the Windows MSI download.

The basic premise of SqlRegKeys.pl is to examine one or the entire subset of registry keys for one or for all SQL instances on a local or remote server. It is also possible to set the registry value for a single server instance and key.

SqlRegKeys In Action

Let's see it in action. Entering Perl plus the script name in a command window produces the following help screen:

C:\>Perl SqlRegKeys.pl

SqlRegKeys.pl - Retrieve and Set Instance-specific SQL Server registry keys

syntax: Perl SqlRegKeys.pl [\\machine\]instance parm [new value]

where

machine = optional remote server machinename

instance = name of SQL instance (* or all uses all instances)

parm = one of the following (* or all allowed):

AgentErrorLogFile

AgentWorkingDirectory

BackupDirectory

Collation

CurrentVersion

DynamicPorts

Edition

ErrorLog

FullTextDefaultPath

MasterDataFile

MasterLogFile

PatchLevel

Port

RegisteredOwner

SQLBinRoot

SQLDataRoot

SQLPath

SQLProgramDir

SerialNumber

Version

new value = optional value to set key (use '' to set to empty).

Here are some samples from my workplace (slightly edited for privacy):

C:\>Perl SqlRegKeys.pl \\MyServer\GEN Sqldataroot

GEN SQLDataRoot = E:\MSSQL\GEN.

C:\>Perl SqlRegKeys.pl \\MyServer\GEN Version

GEN Version = 9.2.3042.00.

C:\>Perl SqlRegKeys.pl \\MyServer\ALL Version

CONNECT Version = 9.2.3042.00.

CMS Version = 9.2.3042.00.

CONNECT_ST Version = 9.2.3042.00.

CMS_QA Version = 9.2.3042.00.

CMF Version = 9.2.3042.00.

COMPREP Version = 9.2.3042.00.

COMPREP_QA Version = 9.2.3042.00.

GEN Version = 9.2.3042.00.

The last example shows one of the most powerful features of this little utility - its a snap to compare one instance's registry keys to another. In this case, I've verified they're all running SQL 2005 service pack 2.

I've found this especially helpful for the path related entries. I like to keep the executables on the C: drive, but the data and various logs more visible on an E: drive and backups on F:. Let's take a gander at the BackupDirectory:

C:\>Perl SqlRegKeys.pl \\MyServer\ALL backupdirectory

CONNECT BackupDirectory = F:\Mssql\CONNECT\Backup.

CMS BackupDirectory = F:\Mssql\CMS\Backup.

CONNECT_ST BackupDirectory = F:\Mssql\CONNECT_ST\Backup.

SURVEILLANCE BackupDirectory = F:\Mssql\SURVEILLANCE\Backup.

CMS_QA BackupDirectory = F:\Mssql\CMS_QA\Backup.

SURVEILLANCE_QA BackupDirectory = F:\Mssql\SURVEILLANCE_QA\Backup.

CMF BackupDirectory = F:\Mssql\CMF\Backup.

COMPREP BackupDirectory = F:\Mssql\COMPREP\Backup.

COMPREP_QA BackupDirectory = F:\Mssql\COMPREP_QA\Backup.

GEN BackupDirectory = C:\Program Files\Microsoft SQL Server\MSSQL.11\MSSQL\Backup.

Oops! Looks like I never set up my last instance like the others. We could fire up regedit.exe to fix it, but this utility is supposed to get us out of that. Fortunately we can add the last parm to set the value:

C:\>Perl SqlRegKeys.pl \\MyServer\GEN backupdirectory F:\MSSQL\GEN\Backup

GEN BackupDirectory = C:\Program Files\Microsoft SQL Server\MSSQL.11\MSSQL\Backup.

BackupDirectory set to F:\MSSQL\GEN\Backup.

Another example I've found worthwhile is setting a fixed port. While dynamic ports are cool from a security standpoint, they wreak havoc with firewalls and JDBC-based connections that require a fixed port. To set a fixed port, I'd run the following commands and restart the server instance:

C:\>Perl SqlRegKeys.pl \\MyServer\GEN DynamicPorts ''

GEN DynamicPorts = 2512.

DynamicPorts set to .

C:\>Perl SqlRegKeys.pl \\MyServer\GEN Port 4518

GEN Port = .

Port set to 4518.

Using all instances and the Port key, we can easily check if our fixed ports are in the range we want for all of our instances. To see all values for all instances on the local server, just use ALL (or *) for both parameters:

C:\>Perl SqlRegKeys.pl ALL ALL

CONNECT AgentErrorLogFile = E:\Mssql\CONNECT\Log\SQLAGENT.OUT.

CMS AgentErrorLogFile = E:\Mssql\CMS\Log\SQLAGENT.OUT.

CONNECT_ST AgentErrorLogFile = E:\MSSQL\CONNECT_ST\LOG\SQLAGENT.OUT.

CMS_QA AgentErrorLogFile = E:\MSSQL\CMS_QA\LOG\SQLAGENT.OUT.

CMF AgentErrorLogFile = D:\Program Files\Microsoft SQL Server\MSSQL.7\MSSQL\LOG\

SQLAGENT.OUT.

COMPREP AgentErrorLogFile = E:\MSSQL\COMPREP\LOG\SQLAGENT.OUT.

COMPREP_QA AgentErrorLogFile = E:\MSSQL\COMPREP_QA\LOG\SQLAGENT.OUT.

GEN AgentErrorLogFile = E:\MSSQL\GEN\LOG\SQLAGENT.OUT.

CONNECT AgentWorkingDirectory = E:\Mssql\CONNECT\JOBS.

CMS AgentWorkingDirectory = E:\Mssql\CMS\JOBS.

CONNECT_ST AgentWorkingDirectory = E:\Mssql\CONNECT_ST\JOBS.

CMS_QA AgentWorkingDirectory = E:\Mssql\CMS_QA\JOBS.

CMF AgentWorkingDirectory = E:\Mssql\CMF\JOBS.

COMPREP AgentWorkingDirectory = E:\Mssql\COMPREP\JOBS.

COMPREP_QA AgentWorkingDirectory = E:\Mssql\COMPREP_QA\JOBS.

GEN AgentWorkingDirectory = E:\MSSQL\GEN\JOBS.

CONNECT BackupDirectory = F:\Mssql\CONNECT\Backup.

CMS BackupDirectory = F:\Mssql\CMS\Backup.

CONNECT_ST BackupDirectory = F:\Mssql\CONNECT_ST\Backup.

CMS_QA BackupDirectory = F:\Mssql\CMS_QA\Backup.

CMF BackupDirectory = F:\Mssql\CMF\Backup.

COMPREP BackupDirectory = F:\Mssql\COMPREP\Backup.

COMPREP_QA BackupDirectory = F:\Mssql\COMPREP_QA\Backup.

GEN BackupDirectory = F:\MSSQL\GEN\BACKUP.

CONNECT Collation = SQL_Latin1_General_CP1_CI_AS.

CMS Collation = SQL_Latin1_General_CP1_CI_AS.

CONNECT_ST Collation = SQL_Latin1_General_CP1_CI_AS.

...

What Do These Registry Values Do, Exactly?

Good question. And good luck finding an authoritative answer. Microsoft doesn't typically document the registry keys that are used and which components use them. They don't tell you which are safe to tweak and which aren't. They do use the following scary-sounding language whenever a support article dares to utter "registry" or "Regedit.exe":

Important This article contains information about how to modify the registry. Make sure that you back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 (http://support.microsoft.com/kb/256986/) Description of the Microsoft Windows registry.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

So, as much as I would like to supply an authoritative listing of what each key does, I can't. What I can supply is my COMPLETELY UNOFFICIAL, TRIAL AND ERROR ONLY, POSSIBLY WRONG explanation of what I BELIEVE the registry keys to be.

After Microsoft's dire warnings and my own disclaimers of ignorance, are you still reading? Great! As I said, there's good stuff in there.

ParameterOk To Set?Apparent Usage
AgentErrorLogFileYesPath where SqlAgent service puts its SQLAGENT.OUT log file.
AgentWorkingDirectory YesPath where SqlAgent stores temporary files. Possibly also default path for job step log files.
BackupDirectoryYesPath where BACKUP and RESTORE place DISK= files by default.
CollationNoCollation specified at installation.
CurrentVersionNoSQL Server version (seems to be same as Version).
DynamicPortsYesCurrent dynamic port number for All IPs. Set to empty string '' to disable.
EditionNoEdition of SQL Server installed, (e.g. Standard Edition, Enterprise Edition).
ErrorLogYes-e followed by path where SQL Server writes ERRORLOG files.
FullTextDefaultPathYesPath where Full-Text catalog files are kept. (?)
MasterDataFileNo*-d followed by full path of master.mdf.
MasterLogFileNo*-l followed by full path of mastlog.mdf
PatchLevelNoAnother flavor of Version, though not as specific. (?)
PortYesStatic port number for All IPs.
RegisteredOwnerNoOwner specified at installation.
SQLBinRootNoFull path to the Binn directory.
SQLDataRootYesPath to the level above the Data directory where new databases are created.
SQLPathNoFull path to MSSQL level of installation.
SQLProgramDirNoFull path to level above MSSQL.n installation directory.
SerialNumberNoApparently, serial number of installation. Blank on my installations.
VersionNoSQL Server version (seems to be same as CurrentVersion).

*Rather than set the MasterDataFile and MasterLogFile entries directly, use my utility featured in the article Moving The SQL Server 2005 System Databases. That will ensure that the MsSqlResourceDb is moved in conjunction with the master data and log files.

Customizing the Registry Entries

I merely picked out the list of registry entries that I cared about. You might care about others. So can you tweak the script to see the keys you want? Of course, Perl makes it easy to do.

The main data structure is a Perl hash called RegKeys. A Perl hash is similar to a VB Collection - loosely, an array indexed by a string. The index of the hash is the name of the key we use when reporting on the value, such as DynamicPorts. The hash value for that index is the slash-delimited registry path, followed by two slashes and the registry value name. We start with the common registry path of HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/, concat the instance's directory name (MSSQL.n), then add the value of the hash key to get the final part of the registry path and the value name.

To add your own registry keys, append to the end of the RegKeys hash. Its OK to leave an extra comma at the end - unlike SQL, Perl is forgiving about that kind of thing. The syntax listing will automatically include any entries that are added.

One suggestion for customization is to expand the DynamicPorts and Port entries. Currently, they only read the IpAll key. That means that all IP addresses defined for the computer will be listening on the static or dynamic port provided. If you wish only one of several IP addresses to listen, you could create Port1 and DynamicPort1, Port2 and DynamicPort2, etc. that examined the IPn keys. IPAll was good enough for me, but it doesn't have to be good enough for you.

Conclusion

The registry is an important resource for nearly all Windows executables. Looking under the hood and tinkering isn't recommended for the average passenger, but to the skilled professional it can unlock otherwise hidden areas of power and control. The SqlRegKeys utility makes examining, comparing, and tweaking important SQL Server registry keys easy.

Let me know if the comments if you find it of use.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating