澳门太陽城集团登录网址SQL Server 的哪个版本

你正在使用
SQL Server 的哪个版本?
  

贴士:作为一个SQL
Server数据库管理者或维护、支持人员,应该会经常问自己这样一个问题:我当前SQL
Server版本号是?当前版本已经有的累计更新、安全更新包有哪些?这么多包要选哪个?等等,会遇到类似心烦的问题。这里给大家梳理一下关于如何方便的获取SQL
Server数据库版本信息,希望在日常运维中有所帮助。

 

 

介绍方法前,版本信息中相关名词的概念稍作解释,便于版本信息解读


 

**1. The
edition 
版本,如:企业版、标准版等**

2. The product
version 
澳门太阳集团城网址, 标准产品版本号MM.nn.bbbb.rr

如: 10.0.1600.22——-major.minor.build.revision 

  • MM – 主版本–major
  • nn – 次版本 minor
  • bbbb – 内部版本号 build
  • rr – 内部修订版本号 revision

 

3. The
ProductMajorVersion
产品主版本号 如:

12为 SQL SERVER 2014

11为 SQL SERVER 2012

10.5为SQL SERVER 2008R2

10 为SQL SERVER 2008

9为SQL SERVER 2005

4. **The product level**
 实例版本级别,如:

‘RTM’ = Original release version 最初发布版本

SPn’ = Service pack version 服务包版本

‘CTPn’, = Community Technology Preview version 社区技术预览版

 

5. The
ProductBuildType 产品当前构建类型,如

OD = OD  为特殊客户发布到web

GDR = GDR 通过windows更新发布

NULL = Not applicable 无

 6. *澳门太陽城集团登录网址,*The
ProductBuild    ** 产品构建,如:4425

 

7. The ProductUpdateLevel      
  产品更新级别,如

CUn = Cumulative Update累计更新,如CU1、CU2、CU3

NULL = Not applicable 无

 

8. The
ProductUpdateReference
      产品更新参考

如:KB3094221

 

9. The ProductMinorVersion    
次版本号

 

 

获取**SQL Server
**及其组件的版本、版本类别和更新级别的八种方法

 


 

 

方法一:

连接SQL
Server Management Studio利用Object
Explorer显示的主要版本号信息,如图(一)显示当前实例产品版本号事11.0.6020

澳门太陽城集团登录网址 1

图(一)

 

方法二:

通过日志获取版本号,打开SQLServer默认安装目录的Log文件下找到ErrorLog文件如图(二),打开后可看到版本信息如(图三),此方法适合SQLServer服务停止情况下查看版本号信息

澳门太陽城集团登录网址 2

图(二)

澳门太陽城集团登录网址 3

图(三)

或者打开SSMS 在SQLSERVER代理下通过日志查看器查看,如下图(四)所示:

澳门太陽城集团登录网址 4

图(四)

 

 

 澳门太陽城集团登录网址SQL Server 的哪个版本。方法三:

通过执行select
@@version获取版本号信息,如下图(五)所示,

澳门太陽城集团登录网址 5

图(五)

 

 

方法四:

在 SQL
Server Management Studio
中通过执行[sys]澳门太陽城集团登录网址SQL Server 的哪个版本。.[xp_msver]获取版本号信息,如下图(六)所示

 

澳门太陽城集团登录网址 6

图(六)

 

 

方法五:

在 SQL
Server Management Studio
中通过执行下面脚本获取版本号信息,结果如下图(七)所示

SELECT
SERVERPROPERTY(‘BuildClrVersion’) AS BuildClrVersion –Version of the
Microsoft.NET Framework common language runtime (CLR) that was used
while building the instance of SQL Server.
,SERVERPROPERTY(‘Collation’) AS Collation –Name of the default
collation for the server.
澳门太陽城集团登录网址SQL Server 的哪个版本。,SERVERPROPERTY(‘CollationID’) AS CollationID –ID of the SQL Server
collation.
,SERVERPROPERTY(‘ComparisonStyle’) AS ComparisonStyle –Windows
comparison style of the collation.
,SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) AS
ComputerNamePhysicalNetBIOS –NetBIOS name of the local computer on
which the instance of SQL Server is currently running.
,SERVERPROPERTY(‘Edition’) AS Edition –Installed product edition of the
instance of SQL Server. Use the value of this property to determine the
features and the limits, such as Compute Capacity Limits by Edition of
SQL Server. 64-bit versions of the Database Engine append (64-bit) to
the version.
,SERVERPROPERTY(‘EditionID’) AS EditionID –EditionID represents the
installed product edition of the instance of SQL Server. Use the value
of this property to determine features and limits, such as Compute
Capacity Limits by Edition of SQL Server.
澳门太陽城集团登录网址SQL Server 的哪个版本。,SERVERPROPERTY(‘EngineEdition’) AS EngineEdition –Database Engine
edition of the instance of SQL Server installed on the server.
,SERVERPROPERTY(‘HadrManagerStatus’) AS HadrManagerStatus –Applies to:
SQL Server 2012 through SQL Server 2016. Indicates whether the AlwaysOn
Availability Groups manager has started.
,SERVERPROPERTY(‘InstanceDefaultDataPath’) AS InstanceDefaultDataPath
–Applies to: SQL Server 2012 through current version in updates
beginning in late 2015.Name of the default path to the instance data
files.
,SERVERPROPERTY(‘InstanceDefaultLogPath’) AS InstanceDefaultLogPath
–Applies to: SQL Server 2012 through current version in updates
beginning in late 2015.Name of the default path to the instance data
files.
,SERVERPROPERTY(‘InstanceName’) AS InstanceName –Name of the instance
to which the user is connected.
,SERVERPROPERTY(‘IsAdvancedAnalyticsInstalled’) AS
IsAdvancedAnalyticsInstalled –Returns 1 if the Advanced Analytics
feature was installed during setup; 0 if Advanced Analytics was not
installed.
,SERVERPROPERTY(‘IsClustered’) AS IsClustered –Server instance is
configured in a failover cluster.
,SERVERPROPERTY(‘IsFullTextInstalled’) AS IsFullTextInstalled –The
full-text and semantic indexing components are installed on the current
instance of SQL Server.
,SERVERPROPERTY(‘IsHadrEnabled’) AS IsHadrEnabled –Applies to: SQL
Server 2012 through SQL Server 2016.AlwaysOn Availability Groups is
enabled on this server instance.
,SERVERPROPERTY(‘IsIntegratedSecurityOnly’) AS IsIntegratedSecurityOnly
–Server is in integrated security mode.
,SERVERPROPERTY(‘IsLocalDB’) AS IsLocalDB –Applies to: SQL Server 2012
through SQL Server 2016.Server is an instance of SQL Server Express
LocalDB.
,SERVERPROPERTY(‘IsPolybaseInstalled’) AS IsPolybaseInstalled –Applies
to: SQL Server 2016.Returns whether the server instance has the PolyBase
feature installed.
,SERVERPROPERTY(‘IsSingleUser’) AS IsSingleUser –Server is in
single-user mode.
,SERVERPROPERTY(‘IsXTPSupported’) AS IsXTPSupported –Applies to: SQL
Server (SQL Server 2014 through SQL Server 2016), SQL Database.Server
supports In-Memory OLTP.
澳门太陽城集团登录网址SQL Server 的哪个版本。,SERVERPROPERTY(‘LCID’) AS LCID –Windows locale identifier (LCID) of
the collation.
,SERVERPROPERTY(‘LicenseType’) AS LicenseType –Unused. License
information is not preserved or maintained by the SQL Server product.
Always returns DISABLED.
,SERVERPROPERTY(‘MachineName’) AS MachineName –Windows computer name on
which the server instance is running.
,SERVERPROPERTY(‘NumLicenses’) AS NumLicenses –Unused. License
information is not preserved or maintained by the SQL Server product.
Always returns NULL.
,SERVERPROPERTY(‘ProcessID’) AS ProcessID –Process ID of the SQL Server
service. ProcessID is useful in identifying which Sqlservr.exe belongs
to this instance.
,SERVERPROPERTY(‘ProductBuild’) AS ProductBuild –Applies to: SQL Server
2014 beginning October, 2015. The build number.
,SERVERPROPERTY(‘ProductBuildType’) AS ProductBuildType –Applies to:
SQL Server 2012 through current version in updates beginning in late

  1. The build Type.
    ,SERVERPROPERTY(‘ProductLevel’) AS ProductLevel –Level of the version
    of the instance of SQL Server.
    ,SERVERPROPERTY(‘ProductMajorVersion’) AS ProductMajorVersion –Applies
    to: SQL Server 2012 through current version in updates beginning in late
  2. The major version.
    ,SERVERPROPERTY(‘ProductMinorVersion’) AS ProductMinorVersion –Applies
    to: SQL Server 2012 through current version in updates beginning in late
  3. 澳门太陽城集团登录网址SQL Server 的哪个版本。The minor version.
    ,SERVERPROPERTY(‘ProductUpdateLevel’) AS ProductUpdateLevel –Applies
    to: SQL Server 2012 through current version in updates beginning in late
  4. ,SERVERPROPERTY(‘ProductUpdateReference’) AS ProductUpdateReference
    –Applies to: SQL Server 2012 through current version in updates
    beginning in late 2015.
    ,SERVERPROPERTY(‘ProductVersion’) AS ProductVersion –Version of the
    instance of SQL Server, in the form of’major.minor.build.revision’.
    ,SERVERPROPERTY(‘ResourceLastUpdateDateTime’) AS
    ResourceLastUpdateDateTime –Returns the date and time that the Resource
    database was last updated.
    ,SERVERPROPERTY(‘ResourceVersion’) AS ResourceVersion –Returns the
    version Resource database.
    ,SERVERPROPERTY(‘ServerName’) AS ServerName –Both the Windows server
    and instance information associated with a specified instance of SQL
    Server.
    ,SERVERPROPERTY(‘SqlCharSet’) AS SqlCharSet –The SQL character set ID
    from the collation ID.
    ,SERVERPROPERTY(‘SqlCharSetName’) AS SqlCharSetName –The SQL character
    set name from the collation.
    ,SERVERPROPERTY(‘SqlSortOrder’) AS SqlSortOrder –The SQL sort order ID
    from the collation
    ,SERVERPROPERTY(‘SqlSortOrderName’) AS SqlSortOrderName –The SQL sort
    order name from the collation.
    ,SERVERPROPERTY(‘FilestreamShareName’) AS FilestreamShareName –The name
    of the share used by FILESTREAM.
    ,SERVERPROPERTY(‘FilestreamConfiguredLevel’) AS
    FilestreamConfiguredLevel –The configured level of FILESTREAM access.
    For more information, see filestream access level.
    ,SERVERPROPERTY(‘FilestreamEffectiveLevel’) AS FilestreamEffectiveLevel
    –The effective level of FILESTREAM access. This value can be different
    than the FilestreamConfiguredLevel if the level has changed and either
    an instance restart or a computer restart is pending. For more
    information, see filestream access level.
    GO

澳门太陽城集团登录网址 7

图(七)

 

 

方法六:

通过“SQL
SERVER 安装中心”获取版本号信息

 

开始菜单,找到 澳门太陽城集团登录网址 8点击后打开如图(八)所示,点击
“已安装的SQLServer功能发现报告”后,显示相关的版本信息,如图(九)。

澳门太陽城集团登录网址 9

图(八)

澳门太陽城集团登录网址 10

图(九)

 

 

方法七:

今年11月18日, Microsoft OneScript
团队发布了最新一版在 SQL Server Management Studio
中运行的脚本,可以帮助我们获取更详细的版本信息

推荐使用此法>>>>>>>>>>>>>>>

该脚本有以下几点:

  1. SQL Server 的主要版本、服务级别和版本类别 

  2. 已安装SP包、累计更新CU,历史更新的QFE\ GDR  

  3. 推荐当前SP包可以安装最新的CU,并给到相关资源地地址(这个很有帮助)

如Cumulative Update CU6 of SQL Server 2012 SP3.

  1. 推荐可升级到其他产品,如:

    SQL Server 2014 Enterprise

    SQL Server 2014 Business Intelligence

    SQL Server 2016 Enterprise

    SQL Server 2016 Business Intelligence

  1. 服务支持生命周期阶段

有关此脚本的详细信息,请到 TechNet 库中的下载:下载点我 

 

执行后结果显示如图(十):

澳门太陽城集团登录网址 11

图(十)

 

方法八:

Microsoft
OneScript 团队页发布了通过Powershell获取版本信息的脚本

Powershell脚本下载地址:点我

 

部分脚本如图(十一):

澳门太陽城集团登录网址 12

图(十一)

 

执行方法是:

1.确保SQL Server服务正常运行

2.用administrator启动Windows PowerShell

3.执行Execution Set-ExecutionPolicy unrestricted -Force

4: 输入 DetermineVersionOfComponents.ps1 并回车

5: 输入登录数据库的用户名和密码

结果显示如下图(十二)

澳门太陽城集团登录网址 13

图(十二)

 

SQL Server版本更新的维护列表

请点击下面链接下载,包括2005~2016所有版本的构建版本号,KB号、发行日期、SP\CU等信息,如下图(十三)所示:

下载链接:点我

澳门太陽城集团登录网址 14

  图(十三)

SQL Server相关的所有版本的产品支持周期信息 

请点击下面链接查询:点我

 

澳门太陽城集团登录网址 15

 

 

 


 

 

小结,SQL
Server版本维护是日常运维中重要部分,我们支持的数据库实例可能很多,因业务或历史原因数据库的版本也可能不一样,搞清并建立每个数据库版本档案是件重要的事情,为后期的版本升级、迁移提供正确信息,降低过程中风险。

 

 

 

 

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图