SQLServer 版本之八大方法搞清 "我是谁"彩民

2019-10-13 03:23 来源:未知

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

 -- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured

 

-- max server memory (MB) (set to an appropriate value, not the default)

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

-- This gives you a lot of useful information about your instance of SQL Server,

彩民之家高手论坛 1

-- Some columns will be NULL on older SQL Server builds

GDR = GDR 通过windows更新发布

Query #9 SQL Server Agent Alerts

 

 

  图(十三)

Query #3 is Server Properties.

图(四)

SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version];

Powershell脚本下载地址:点我

 

11为 SQL SERVER 2012

-- polybase network encryption

执行方法是:

 ORDER BY sj.name OPTION (RECOMPILE);

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

   -- 7 is Standard Server Edition

 

   -- 8 is Datacenter Server Edition

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

   -- 10.0 is either Windows 10 or Windows Server 2016

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

SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion], 

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

   -- Using SQL Server in Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 environments

    SQL Server 2016 Business Intelligence

SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],

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

-- optimize for ad hoc workloads (should be 1)

 

last_startup_time, service_account, is_clustered, cluster_nodename, [filename]

方法八:

-- You want to see 0 for process_virtual_memory_low

4: 输入 DetermineVersionOfComponents.ps1 并回车

Query #4 is Configuration Values. 


-- You want to see an equal number of schedulers on each NUMA node

如Cumulative Update CU6 of SQL Server 2012 SP3.

SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],              -- Is this a GDR or OD hotfix (NULL if on a CU build)

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

 

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

 

 

--

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

SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],

 

-- max degree of parallelism (depends on your workload and hardware)

在 SQL Server Management Studio 中通过执行[sys].[xp_msver]获取版本号信息,如下图(六)所示

SELECT name, event_source, message_id, severity, [enabled], has_notification,

方法七:

Query #10 Windows Info

10.5为SQL SERVER 2008R2

available_page_file_kb/1024 AS [Available Page File (MB)],

 

 

彩民之家高手论坛 2

 FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)

    SQL Server 2014 Enterprise

FROM msdb.dbo.sysalerts WITH (NOLOCK)

 

  Get socket, physical core and logical core count from the SQL Server Error log.


-- SQL Server Services information (Query 7) (SQL Server Services Info)

方法六:

-- backup compression default (should be 1 in most cases)

彩民之家高手论坛 3

-- New options for SQL Server 2016

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

 

图(六)

 

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

-- clr enabled (only enable if it is needed)

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

Query #2 is Core Counts. 

彩民之家高手论坛 4

          windows_sku, os_language_version

 

EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';

彩民之家高手论坛 5

   

图(五)

-- The behavior of TF 1118 and 2371 are enabled in SQL Server 2016 by default

下载链接:点我

SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],

8. The ProductUpdateReference      产品更新参考

-- Gives you some useful information about the composition and relative load on your NUMA nodes

 

 -- Look for Agent jobs that are not owned by sa

 

-- remote data archive (to enable Stretch Databases)

 

   -- 

 

  1. 本文内容来自Glenn Berry ,原文中对查询做了简单的解析和说明。此查询是针对SQL Server 2016的,但在不涉及新特性的时,2008~2016皆可用。
  2. 从事DBA工作一些年后,每个人都会有自己的Toolkit。我在整理脚本时,发现这个系列的脚本很实用和具有启发性,就收集整理出来了。
  3. 关于系统DMV的使用,需要知道:

    (a)这些数据都是上次实例启动以来的积累数据

    (b)利用它们来诊断时,很多情况下需要运行多次收集数据,再分析。

    (c)对于性能指标,不要迷信所谓的推荐值。你的系统运行正常,满足你的用户,满足企业要求,就是正常值。所以说平时收集基线数据,是一件很重要的事情。

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

-- Read more about Agent Alerts here:

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

-- Get SQL Server Agent Alert Information (Query 9) (SQL Server Agent Alerts)

 

   FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);

 

 SELECT sj.name AS [JobName], sj.[description] AS [JobDescription], SUSER_SNAME(sj.owner_sid) AS [JobOwner],

彩民之家高手论坛 6

-- Watch out if SQL Server 2016 Standard Edition has been installed on a machine with more than 16 physical cores

 

   -- SQL Server 2014 requires Windows Server 2012 or newer

 

-- cost threshold for parallelism (depends on your workload)

 

-- SQL Server NUMA Node information

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

 

彩民之家高手论坛 7

-- It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.

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

SERVERPROPERTY('ProcessID') AS [ProcessID],

 

-- Get SQL Server Agent jobs and Category information (Query 8) (SQL Server Agent Jobs)

 

Query #5 Global Trace Flags

 

 LEFT OUTER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)

彩民之家高手论坛 8

Query #7 SQL Server Services Info

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

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

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

SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],

图(二)

Query #11 SQL Server NUMA Info

NULL = Not applicable 无

-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log

开始菜单,找到 彩民之家高手论坛 9点击后打开如图(八)所示,点击 “已安装的SQLServer功能发现报告”后,显示相关的版本信息,如图(九)。

-- remote admin connections (should be 1)

 

 INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)

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

SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 

NULL = Not applicable 无

available_physical_memory_kb/1024 AS [Available Memory (MB)],

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

SERVERPROPERTY('ProductLevel') AS [ProductLevel],                -- What servicing branch (RTM/SP/CU)

9. The ProductMinorVersion     次版本号

SELECT @@SERVERNAME AS [Server Name], @@VERSIONAS [SQL Server and OS Version Info];

该脚本有以下几点:

-- If no global trace flags are enabled, no results will be returned.

2. The product version  标准产品版本号MM.nn.bbbb.rr

 -- //msdn.microsoft.com/en-us/library/ms189817.aspx

图(八)

   SELECT windows_release, windows_service_pack_level, 

部分脚本如图(十一):

SERVERPROPERTY('Edition') AS [Edition], 

10 为SQL SERVER 2008

SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced

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

SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], 

图(三)

   -- 4 is Enterprise Edition

 

Query #12 System Memory

图(十一)

   -- 1033 for os_language_version is US-English

彩民之家高手论坛 10

   -- Hardware and Software Requirements for Installing SQL Server 2016

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

SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,

'RTM' = Original release version 最初发布版本

 

方法四:

ORDERBY name OPTION (RECOMPILE);

    SQL Server 2014 Business Intelligence

SERVERPROPERTY('ServerName') AS [ServerName],  

 方法三:

avg_load_balance :Average number of tasks per scheduler on this node.

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

SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],

图(十二)

SERVERPROPERTY('Collation') AS [Collation], 

图(七)

process_physical_memory_low, process_virtual_memory_low

 

FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

彩民之家高手论坛 11

-- Focus on these settings:

 

-- Good basic information about OS memory amounts and state

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.
,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.
,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.
,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

-- Windows information (Query 10) (Windows Info)

 

 -- MSDN sysjobs documentation

 

FROM sys.configurations WITH (NOLOCK)

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

-- Gives you major OS version, Service Pack, Edition, and language info for the operating system

彩民之家高手论坛 12

SERVERPROPERTY('ProductVersion') AS [ProductVersion],

图(一)

   -- 

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

FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE); 

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

   -- 48 is Professional Edition

 6. **The ProductBuild    ** 产品构建,如:4425

-- Shows whether you are running on a failover cluster instance

2.用administrator启动Windows PowerShell

 

 

   -- Windows SKU codes

如:KB3094221

active_worker_count, avg_load_balance, resource_monitor_state

SPn' = Service pack version 服务包版本

-- lightweight pooling (should be zero)

12为 SQL SERVER 2014

 ON sj.category_id = sc.category_id

 

 ON sj.job_id = js.job_id

 

Query #6 Process Memory

结果显示如下图(十二)

-- This indicates that you are not under external memory pressure

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

-- automatic soft-NUMA disabled (should be 0 in most cases)

 

-- Shows the process_id, when they were last started, and their current status

图(十)

large_page_allocations_kb, locked_page_allocations_kb, page_fault_count,

 

WHERE node_state_desc <> N'ONLINE DAC'OPTION (RECOMPILE);

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

-- (shows whether locked pages is enabled, among other things)

彩民之家高手论坛 13

-- SQL Server Process Address space info (Query 6) (Process Memory)

 

SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],

方法一:

-- Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags)

9为SQL SERVER 2005

SERVERPROPERTY('ProductBuild') AS [ProductBuild], 

 

   -- 10 is Enterprise Server Edition

 

system_memory_state_desc AS [System Memory State]


-- Get instance-level configuration values for instance

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

SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],

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

-- backup checksum default (should be 1)

方法二:

-- You want to see "Available physical memory is high" for System Memory State

方法五:

SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled],    -- New for SQL Server 2016

 

-- Common trace flags that should be enabled in most cases

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

 

图(九)

total_page_file_kb/1024 AS [Total Page File (MB)],

 

SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], 

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

  SQL and OS Version information for current instance

彩民之家高手论坛 14

-- priority boost (should be zero)

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

   -- 6.3 is either Windows 8.1 or Windows Server 2012 R2 

  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. 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

 -- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)

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

   -- 

 

-- You want to see 0 for process_physical_memory_low

    SQL Server 2016 Enterprise

-- Tells you the account being used for the SQL Server Service and the SQL Agent Service

 

-- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes

SQL Server版本更新的维护列表

-- such as the ProcessID for SQL Server and your collation

 

system_cache_kb/1024 AS [System Cache (MB)],

 

SERVERPROPERTY('InstanceName') AS [Instance], 

彩民之家高手论坛 15

SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build

3.执行Execution Set-ExecutionPolicy unrestricted -Force

SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],    -- Within a servicing branch, what CU# is applied

-- This indicates that you arenotunder internal memory pressure

FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);


 js.next_run_date, js.next_run_time

   -- 6.0 is either Windows Vista or Windows Server 2008

SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion], 

SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],

--

delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time

Query #1 is Version Info. 

 

Query #8 SQL Server Agent Jobs

FROM sys.dm_os_nodes WITH (NOLOCK)

   Get selected server properties.

-- Gives you some basic information about your SQL Server Agent Alerts (which are different from SQL Server Agent jobs)

 sj.date_created, sj.[enabled], sj.notify_email_operator_id, sj.notify_level_email, sc.name AS [CategoryName],

memory_utilization_percentage, available_commit_limit_kb,

SELECT  SERVERPROPERTY('MachineName') AS [MachineName], 

-- hadoop connectivity

ORDERBY name OPTION (RECOMPILE);

SERVERPROPERTY('IsClustered') AS [IsClustered], 

 -- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)

   -- 6.1 is either Windows 7 or Windows Server 2008 R2

 

DBCC TRACESTATUS (-1);

SELECT servicename, process_id, startup_type_desc, status_desc,

   -- Quick-Start Installation of SQL Server 2016

   -- 6.2 is either Windows 8 or Windows Server 2012

版权声明:本文由彩民之家高手论坛发布于彩民之家高手论坛,转载请注明出处:SQLServer 版本之八大方法搞清 &quot;我是谁&quot;彩民