資安更新作業需確認 SQL Server 詳細版本資訊,需細到 Service Pack 及累積安全更新,爬文查到超好用的 SQL Server 版本資訊偵測腳本。這組自動偵測腳本由微軟OneScript Team提供,可顯示版號、產品名稱、產品層級(Service Pack 版本)、版本別(Express、Enterprise、Developer…)、32 / 64 位元、Service Pack 版本、累積更新(Cumulative Update)等資訊。

更厲害的來了,偵測結果還包含後續若要更新,最新版 GDR(General Distribution Release)及 QFE(Quick Fix Eningeering)的下載網址。

【小辭典】

SQL Server 更新的種類超多,順手整理如下: 參考

  • QFE,Quick Fix Engineering,也常被稱為 Hotfix
    較未經過大量測試的修正,經索取才提供(留下 Email 才寄送下載連結,也方便事後發現問題通知),通常用於較緊急狀況。
  • COD,Critical On-Demand
    針對影響嚴重、範圍層面廣或安全相關問題所出的緊急更新,通常會收納到下次 CU/SP,COD 有時會包含多個 QFE。
  • CU,Cumulative Update
    在 SP 與 SP 之間的更新,通常以 60 天為單位匯集發行,測試完整度不如 SP,安裝到正式環境前應先測試,但微軟鼓勵積極更新,可省去追查一些已被修復的問題。延伸閱讀 (傳統上 CU 測試與驗證完整度不如 SP,但近年來 CU 的可靠性已接近 SP
  • GDR,General Distribution Release
    一些重大或安全相關更新的累積更新,若沒有額外資訊測試 CU,GDR 是風險較低的選擇。
  • SP,Service Pack
    可視為特殊標示的 CU,包含安全更新、Bug 修復,有時還會包含新功能。

以下為版本偵測結果範例:

---------------------------------------------------------------------------------------------------------
--//Your current Microsoft SQL Server information:
---------------------------------------------------------------------------------------------------------
Product Version:          11.0.5343.0
Product Name:             SQL Server 2012
Product Level:            SP2 + Security update(GDR)
Product Edition:          Developer Edition (64-bit)
---------------------------------------------------------------------------------------------------------
Note, if you want to know information about CU, please read this KB below.
KB321185, 
---------------------------------------------------------------------------------------------------------
Support Lifecycle stage: Mainstream Support Phase. For additional information refer to 
https://support.microsoft.com/en-us/lifecycle/search?sort=PN&alpha=SQL%20Server&Filter=FilterNO, and Q6, Q18
in the FAQ section of Support Lifecycle page at: https://support.microsoft.com/en-us/lifecycle#gp/lifePolicy
---------------------------------------------------------------------------------------------------------
Full information:
Microsoft SQL Server 2012 - 11.0.5343.0 (X64) 
	May  4 2015 19:11:32 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

---------------------------------------------------------------------------------------------------------
--//Recommended updates: 
--### RTM -> QFE or GDR
--### SP  -> QFE or GDR
--### QFE -> QFE
--### GDR -> GDR or QFE
---------------------------------------------------------------------------------------------------------
Install the latest service pack:              SP3, 
Install the latest Cumulative Update (CU) of SP3:  CU7, 
 
---------------------------------------------------------------------------------------------------------
###### QFE branch updates
---------------------------------------------------------------------------------------------------------
11.0.2376 (SQL Server 2012 RTM QFE) http://support.microsoft.com/en-us/kb/2716441
11.0.3513 (SQL Server 2012 SP1 QFE) https://support.microsoft.com/en-us/kb/3045317
11.0.5613 (SQL Server 2012 SP2 QFE) https://support.microsoft.com/en-us/kb/3045319
---------------------------------------------------------------------------------------------------------
###### GDR branch updates
---------------------------------------------------------------------------------------------------------
11.0.2218 (SQL Server 2012 RTM GDR) https://support.microsoft.com/en-us/kb/2716442
11.0.3153 (SQL Server 2012 SP1 GDR) http://support.microsoft.com/kb/2977326/en-us
11.0.3156 (SQL Server 2012 SP1 GDR) https://support.microsoft.com/en-us/kb/3045318
11.0.5343 (SQL Server 2012 SP2 GDR) https://support.microsoft.com/en-us/kb/3045321
---------------------------------------------------------------------------------------------------------
Note, if you don't want to upgrade to latest service pack right now, we recommend you install the latest
Cumulative Update CU16 of SQL Server 2012 SP2.
Install the latest Cumulative Update (CU) of SP2: CU16, 


---------------------------------------------------------------------------------------------------------
--//You can upgrade to any of the following product(s):
---------------------------------------------------------------------------------------------------------
SQL Server 2014 Developer
SQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2016 Developer


For additional information about supported version and edition upgrades refer to:
https://technet.microsoft.com/en-us/library/ms143393(v=sql.120).aspx

Comments

# by Slant

黑大,目前【SQL Server 版本資訊偵測腳本】已無法下載,可以請您分享嗎?

# by Jeffrey

to Slant, 有找到備份 - https://github.com/beakerflo/EnterMI/blob/master/TSQL/DetermineVersionOfSQLServer.sql ,但版本只到 2017,我猜是因為程式已未再維護無法偵測新版本,所以從網站下架了。

# by Slant

To 黑大,感謝分享。

# by Mars

To Jeffery 感謝幫了大忙~

Post a comment