SQL Server會寫入哪些註冊表？

在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)