在SQLServer启动过程中首先会从注册表读取启动信息,有时候SQLServer无法启动的时候我们需要修改注册表的问题。但是SQLServer的信息保存在注册表哪里呢?从网上找了很久但是都不是很全。
其实微软在2008 R2 SP1之后的版本中新增加了一个DMV可以查询SQLServer实例中Windows注册表中的配置信息和安装信息,对于每个注册表项返回一行,可以返回主机上可用的 SQL Server 服务或 SQL Server 实例的网络配置值等。。
下面举两个例子:
下面的示例返回当前 SQL Server 实例的网络配置信息值。
SELECT registry_key, value_name, value_data FROM sys.dm_server_registry WHERE keyname LIKE N'%SuperSocketNetLib%';
以下示例返回在启动过程中传递到 SQL Server 实例的参数。
SELECT registry_key, value_name, value_data FROM sys.dm_server_registry WHERE registry_key LIKE N'%Parameters';
关于注册表数值一些解释:
Parameter | Ok To Set? | Apparent Usage |
AgentErrorLogFile | Yes | Path where SqlAgent service puts its SQLAGENT.OUT log file. |
AgentWorkingDirectory | Yes | Path where SqlAgent stores temporary files. Possibly also default path for job step log files. |
BackupDirectory | Yes | Path where BACKUP and RESTORE place DISK= files by default. |
Collation | No | Collation specified at installation. |
CurrentVersion | No | SQL Server version (seems to be same as Version). |
DynamicPorts | Yes | Current dynamic port number for All IPs. Set to empty string '' to disable. |
Edition | No | Edition of SQL Server installed, (e.g. Standard Edition, Enterprise Edition). |
ErrorLog | Yes | -e followed by path where SQL Server writes ERRORLOG files. |
FullTextDefaultPath | Yes | Path 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 | No | Another flavor of Version, though not as specific. (?) |
Port | Yes | Static port number for All IPs. |
RegisteredOwner | No | Owner specified at installation. |
SQLBinRoot | No | Full path to the Binn directory. |
SQLDataRoot | Yes | Path to the level above the Data directory where new databases are created. |
SQLPath | No | Full path to MSSQL level of installation. |
SQLProgramDir | No | Full path to level above MSSQL.n installation directory. |
SerialNumber | No | Apparently, serial number of installation. Blank on my installations. |
Version | No | SQL Server version (seems to be same as CurrentVersion). |
知道了这些值的含义后我们现在修改SQL Server的authentication mode,我们找到注册表
HKLM\Software\Microsoft\Microsoft SQLServer\MSSQL.1\MSSQLServer\LoginMode
然后将值从1改为2,重启SQL Server就可以看到认证模式已经更改。
更多信息参考:sys.dm_server_registry(Transact-SQL)
本文转自 lzf328 51CTO博客,原文链接:http://blog.51cto.com/lzf328/1025051