Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get And Set SQL Server 2005 Instance Registry Values

By Vince Iacoboni,

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
AgentErrorLogFile YesPath 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.
BackupDirectory YesPath where BACKUP and RESTORE place DISK= files by default.
Collation NoCollation specified at installation.
CurrentVersion NoSQL Server version (seems to be same as Version).
DynamicPorts YesCurrent dynamic port number for All IPs. Set to empty string '' to disable.
Edition NoEdition of SQL Server installed, (e.g. Standard Edition, Enterprise Edition).
ErrorLog Yes-e followed by path where SQL Server writes ERRORLOG files.
FullTextDefaultPath YesPath where Full-Text catalog files are kept. (?)
MasterDataFile No*-d followed by full path of master.mdf.
MasterLogFile No*-l followed by full path of mastlog.mdf
PatchLevel NoAnother flavor of Version, though not as specific. (?)
Port YesStatic port number for All IPs.
RegisteredOwner NoOwner specified at installation.
SQLBinRoot NoFull path to the Binn directory.
SQLDataRoot YesPath to the level above the Data directory where new databases are created.
SQLPath NoFull path to MSSQL level of installation.
SQLProgramDir NoFull path to level above MSSQL.n installation directory.
SerialNumber NoApparently, serial number of installation. Blank on my installations.
Version NoSQL 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.

Total article views: 8201 | Views in the last 30 days: 5
 
Related Articles
FORUM

SQL Server registry entries

Need to delete registry entries

BLOG

Understanding SQL Server related Windows Registries

SQL Server related information from Windows registries The Windows Registry is a hierarchical d...

FORUM

BackupDirectory is not available

Hi, has anyone meet with this problem ? Yeasterday I have installed a new SQL Server 2005 side by si...

FORUM

Read Registry

Could you please share a script to read remote server's registry?

FORUM

MSSQL Server 2005 mirroring

Licence for MSSQL Server 2005 mirroring

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones