存储库报告服务 Windows 2008 R2 Windows 2008 系统管理员 T-SQL 报告 SSRS SQL Server 2000 SQL Server 2012 DBA SQL Server 2008R2 SQL Server 2008 高级 SQL Server 2005 中级开发 SQL Server SQL Windows

使用一个 SSRS 报告查找所有 Microsoft SQL Server、过时版本和 Service Pack

Mark W Solomon

4.95/5 (11投票s)

2016 年 2 月 11 日

CPOL

29分钟阅读

31654

380

在本文中，我开发了一种方法，可以根据需要确定我们环境中的所有 SQL Server，并将这些信息筛选到 Microsoft 的 Microsoft SQL Server 停用报告中，并在 SSRS 报告中呈现所有这些信息。

下载 SQLServerSunset.zip - 19.2 KB

关注点

我们使用 SQL Server 2014、Power Shell、SQL Server Reporting Services (SSRS)、Excel 和 Microsoft 评估和规划工具包 (MAP) 来收集和报告环境中 SQL Server 的相关信息，包括其各自的版本和 Service Pack。此报告对于希望为客户提供服务的顾问以及希望衡量其组织中 SQL Server 安装健康状况的 IT 专业人员来说非常有价值。当然，其他产品也报告了本文中的信息——我们提供的方法只是一种（也是一种很好的）方法。

引言

如果您拥有或管理组织内的 SQL Server，您可能不知道您的许多 SQL Server 可能已不再支持 Microsoft 的产品或 Service Pack。此外，如果您有多个 DBA 和经理，您的支持结构可能会被分割，以至于您不确定您组织中可能拥有哪些 SQL Server——即使您认为您有一个好的想法。在我们的组织中，作为众多 DBA 之一，我一直对我一直感觉到的 SQL Server 蔓延感到不安。使用本文中的 SSRS 报告，我能够找到比以前多 66% 的我不知道的 SQL Server，以及超过 80% 的 SQL Server 过时（通过 Service Pack 或 SQL 版本）或不受 Microsoft 支持。如图 A 和 B 所示，我对报告的发现印象深刻，但对我们组织维护和记录 SQL Server 的能力并不满意。

在本文中，我们开发了一种方法，可以根据需要确定我们环境中的所有 SQL Server，并使用 Microsoft SQL Server 的 Microsoft 停用数据对此信息进行筛选，并在 SSRS 报告中呈现所有这些信息。作为一项附加好处，我们还存储了服务器的处理器数量和核心数以及许多其他有价值的数据，这样您就可以确定您的组织是否正确许可了您的 SQL Server（我们可能会在未来作为本文的附加/第二部分撰写一篇关于 SQL Server 核心的 SSRS 报告）。正如您所看到的，我们有 189 个 SQL Server 和 14 个 SQL Server 版本。或者，在 Microsoft 的支持生命周期页面 (https://support.microsoft.com/en-us/lifecycle?C2=1044)上手动检查每个服务器的停用数据将是一个耗时的过程。但手动查找网络上的所有 SQL Server 将更加耗时。请继续关注我们，我们将向您展示一种使用 SQL Server 2014、Power Shell、SQL Server Reporting Services (SSRS)、Excel 和 Microsoft 评估和规划工具包 (MAP) 在几分钟内收集所有这些信息的更简单的方法。

注意

在本文中，我们确实提供了大量信息。话虽如此，我们尽量早地提供“操作指南”，以便您稍后阅读“额外”内容。此外，虽然我们的报告可能对某些人来说看起来不错，但 SSRS 并不是重点，而是如何轻松获取 SSRS 报告所显示的数据。

图 A：SQL Server 停用报告摘要

图 B：SQL Server 停用报告详细信息

报告内容

报告由主要执行摘要和关于所有 SQL Server 的报告详细信息（子报告）组成。执行摘要识别 SQL Server 的总数、SQL Server 版本总数以及不受支持的版本数。它显示了最新的 SQL Server、过期的 SQL Server 以及不受支持的服务器的数量和占 SQL Server 总数的百分比。条形图显示按 SQL 版本划分的 SQL Server 计数。之后，子报告显示 SQL Server 名称、实例名称、SQL Server 版本、SQL Server 支持的最后日期和 SQL Server Service Pack 支持的最后日期。最后两列显示 SQL Server 版本和当前 SQL Server 所处 Service Pack 可用的最高 Service Pack 级别。在图 B 的服务器详细信息中，第一列显示一个 SQL 指示器，根据红色、黄色或绿色对当前服务器进行评分。红色表示 SQL Server 版本已过时且 Service Pack 未更新。黄色表示 Microsoft 仍支持 SQL 版本，但 Service Pack 级别不支持。如果 SQL Server 评为绿色，则 SQL 版本和 Service Pack 级别都良好。图 A 执行摘要中的文本提供了更多可用于解释报告的详细信息。

好消息/坏消息

首先，我们有一些好消息和一些坏消息。好消息是您可以轻松创建我们的 SQL Server SunSet 数据库和 SSRS 报告，以获取大部分代码，而无需筛选大量复杂性。从操作指南的角度来看，这是很棒的——这占本文的 80%。对于坏消息，其实也不是真正的坏消息，我们想给您更多。我们想向您展示如何收集您甚至不知道的 SQL Server 数据，如何使用 Power Shell 和 Microsoft Excel 清理这些数据，最后，使用 SQL Server 和 SSRS 处理这些信息。正如您将看到的，这个“坏消息”涉及使用 Power Shell 和额外的 Power Shell 功能，以及 Microsoft SQL Server 和额外的 Microsoft Office 功能。这听起来很棒，直到您意识到这些功能一直是许多文章和博客的主题，内容是如何让它首先正常工作——而我们只有时间和资源来呈现这篇文章，只对这些步骤进行简要介绍。但是，在适当的时候，我们将提供我们发现最有效的解决问题的资源的链接。希望我的演示能做到位，您不必去那些链接！

背景

为了重复我们的步骤来生成此报告，您将需要以下内容

Active Directory 域
Active Directory 域帐户
Microsoft Assessment and Planning Kit 9.3
Microsoft Excel
Microsoft SQL Server 2014 Express，您的域帐户具有 sysadmin 权限
Report Builder for Microsoft SQL Server 2014 version 3.0
Windows Server 2008 R2 with Power Shell 2.0，您的域帐户具有本地管理员权限

注意：您“可能”可以使用任何近期的 Windows 和 SQL Server 版本来执行本文中的步骤，但我们尚未测试这些组合。我个人目前没有看到任何问题，但结果可能因人而异。

准备收集您的 SQL Server 和报告信息

为了获取我们的 SQL Server SunSet 报告，我们仔细安装和配置了各种组件，以帮助我们收集数据并生成图 A 中显示的报告。仅此一句警告。正如我们提到的，人们写了整篇博客和文章，展示了如何在我们向您展示的这种方式下让 Excel 和 Power Shell 与 SQL Server 正确工作。我们已尽力提供我们认为效果最好的，但格式非常精简。但是，我们也提供了指向一些最有用的文章和博客的链接，以便在您遇到我们提供的技术信息问题时提供帮助。

简而言之，我们使用了一台安装了 Power Shell 2.0 的 Windows 2008 R2 服务器。然后，我们安装了 SQL Express 2014 和 SQL 2014 功能包中 Power Shell 所需的组件。此外，我们安装了 Microsoft Access Database Engine 2010。然后，我们安装了 Microsoft Assessment and Planning 工具包 (MAP)。最后，我们安装了 Microsoft Excel，以帮助我们清理 MAP 输出中的数据——MAP 以 Excel 格式呈现其数据。我们还安装了我们的 SQLSunSet 数据库，该数据库使用表、视图、函数和存储过程将原始 MAP 数据转换为我们的 SSRS 报告。当然，我们都是通过 Windows 服务器的本地管理员权限和 SQL Server 中的 SA 权限来完成这一切的。下面，您将找到我们为实现和配置这些软件功能和产品所采取的简要步骤。现在，您可能会认为这些步骤对于生成报告来说太多了。然而，这些步骤中的大多数都是一次性的实现。正如您稍后将看到的，当一切就绪后，提取新的 MAP 数据并生成 SSRS 报告会非常简单快捷。

首先，您需要安装 Windows Server 2008 R2 Standard Edition x64 和 Power Shell 2.0 或更高版本

安装和配置超出了本文的范围。
其他 Windows 版本可能（而且很可能）会起作用，但我们尚未测试这些情况。

安装 SQL Server Express 2014

您需要 SQL Server Advanced Edition x64。请遵循以下基本说明下载、安装和配置 SQL Server。

下载地址：http://www.microsoft.com/en-us/download/details.aspx?id=42299。
在功能选择窗口中，选择所有功能，然后单击“下一步”。
在实例配置窗口中，将命名实例键入为 MAPS（MAPS，带 S——这不是拼写错误）。
- 注意：如果 MAP 找不到您的 MAPS 实例，它将安装另一个 SQL Server Express 副本。
- 单击“下一步”。
接下来，在服务器配置窗口的服务帐户选项卡中，输入您的 SQL Server 数据库引擎帐户名称和密码，然后单击“下一步”。您会看到，SQL Server 服务需要一个 AD 帐户才能避免本文中使用的 OPENROWSET 错误。
现在，在数据库引擎配置窗口的服务器配置选项卡中，单击“混合模式”，然后为 SQL Server 数据库引擎键入用户名和密码。然后，单击“指定 SQL Server 管理员”旁边的“添加当前用户”按钮，然后使用“添加”按钮添加本地管理员组。
在 Reporting Services 配置窗口中，也单击“下一步”，确认已选择“安装并配置”。
单击“下一步”开始安装过程，等待其成功完成——安装过程可能会要求您重新启动 Windows Server。
最后，打开 SQL Server Configuration Manager，展开“SQL Server 网络配置”。单击“MAPS 的协议”，然后启用 TCP/IP 和命名管道。

安装 SQLSunSet 数据库

在本文提供的 SunSetSQL.zip 文件中，打开 CreateSunSetDBandSampleData.sql 文件，在 SQL Server Studio 中的查询窗口中粘贴代码，然后运行 T-SQL 代码以创建 SQLSunSet 数据库、表、视图、函数、过程和示例数据。

接下来，我们需要导入 SSRS 报告。

打开 Internet Explorer，在安装了 SQL Server Express 的 Windows Server 上访问 https:///Reports_MAPS/Pages/Folder.aspx。
前提是您已将我们的内容下载到本地存储，请单击“上传文件”按钮命令，从我们的 zip 文件中上传以下报告组件
- SQLServerSunsetReportMain
- SQLServerSunsetReportSub
首先运行 SQLServerSunsetReportSub 子报告，以验证它是否可以从 SQLSunSet 数据库中拉取数据。
然后，运行 SQLServerSunsetReportMain 报告，以验证您是否可以看到主报告的第一页和第二页——其中包含子报告的数据。

注意：如果您没有创建 MAPS SQL Server 实例或拼写错误，这些报告将无法正确呈现，本文其余部分的步骤也将无法进行。

恭喜您，如果您正确呈现了报告并检索了数据，您就掌握了本文主要内容的要点。但是，请在此处战略性地暂停一下，评估本文对您的用处。您是否能够成功遵循我们所有的步骤？您喜欢报告中的内容吗？您想在报告中看到自己的数据而不是我们的示例数据吗？如果答案是肯定的，请继续以下步骤以完成本文，从而从您自己的网络和 SQL Server 生成数据。正如您将看到的，我们确实有很多步骤需要完成，但其中大多数都是一次性的。完成本文后，您只需很少的操作即可更新 SSRS 报告中的数据。

下载并安装 Microsoft Access Database Engine 2010 Redistributable（确实是 Microsoft Office 引擎——不仅仅是 MS Access！）

正如您将看到的，我们将在 Power Shell 中调用 Excel，然后再将其传递给 SQL Server。请遵循以下基本说明进行下载和安装

下载地址：https://www.microsoft.com/en-us/download/details.aspx?id=13255
选择 AccessDatabaseEngine._x64.exe 版本并下载文件。
运行可执行文件并接受默认安装过程。

注意：正如您将看到的，这不仅仅适用于 Microsoft Access，还适用于 Microsoft Excel 与 Microsoft SQL Server 的集成。

现在，您需要配置 SQL Server 以使用分布式查询（如您将看到的，使用 OPENROWSET），并配置 SQL Server 以使用我们刚刚安装的 Office 引擎。因此，在 SQL Server Studio 中打开一个查询窗口，然后运行以下代码

列表 A：启用分布式查询运行并配置 OLEDB 选项以允许我们导入 Excel 文档

exec sp_configure 'Show Advanced Options', 1;

RECONFIGURE;

GO

exec sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 

GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

GO

运行此代码可以消除以下错误：“SQL Server 阻止了对组件‘Ad Hoc Distributed Queries’的 STATEMENT ‘OpenRowset/OpenDatasource’的访问，因为此组件已作为此服务器的安全配置的一部分被关闭。系统管理员可以使用 sp_configure 来启用‘Ad Hoc Distributed Queries’的使用。有关启用‘Ad Hoc Distributed Queries’的更多信息，请在 SQL Server Books Online 中搜索‘Ad Hoc Distributed Queries’。”

运行此代码还可以消除以下错误：“‘Microsoft.ACE.OLEDB.12.0’提供程序未在本地计算机上注册。”

下载并安装 2014 SQL Server 功能包的组件

从以下地址下载这些组件：http://www.microsoft.com/en-us/download/details.aspx?id=42295，并按以下顺序安装

ENU\x64\SharedManagementObjects.msi
ENU\x64\PowerShellTools.msi

配置 Power Shell

打开 Power Shell 并运行以下命令

set-executionpolicy unrestricted

出现提示时，选择 Y（是）以允许此更改。

现在，您需要准备 Power Shell 以运行一些我们稍后将使用的 SQL 命令。但是，首先，您需要验证第一行代码中显示的目录是否确实是包含 Microsoft.SqlServer.Management.PSSnapins.dll 和 Microsoft.SqlServer.Management.PSProvider.dll（来自我们安装的 SQL 2014 功能包组件）的目录。如果不是，您需要更改代码以反映这些 DLL 的位置。另外，请注意，这些 DLL 与名为 SqlServerCmdletSnapin120 和 SqlServerProviderSnapin120 的相应 Power Shell 管理单元相关联。在网络上，您会找到添加这些管理单元的各种变体，但请注意，名称中的 120 是对 SQL Server 2014 的引用。

如果目录正确，请将 FindandInstallDlls.ps1 文件从解压缩的 SQLServerSunset.zip 文件拖放到 Power Shell 窗口中。此代码注册 SQL Server DLL 并添加管理单元。仔细查看代码执行过程，确保没有错误发生。

列表 B：FindandInstallDLLS.ps1

cd   "C:\Program Files\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS"

$framework=$([System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory())
Set-Alias installutil "$($framework)installutil.exe"
installutil Microsoft.SqlServer.Management.PSSnapins.dll
installutil Microsoft.SqlServer.Management.PSProvider.dll
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin120 -ErrorAction SilentlyContinue) -eq $null )
{
     Add-PSSnapin SqlServerCmdletSnapin120
}

if ( (Get-PSSnapin -Name  SqlServerProviderSnapin120 -ErrorAction SilentlyContinue) -eq $null )
{
     Add-PSSnapin  SqlServerProviderSnapin120
}

下载并安装 Microsoft Excel
现在，下载并安装任何近期的 Microsoft Excel 版本。找到您可以安装的 Microsoft Excel 副本——不幸的是，它不是免费的，也没有快速版本。对于本文，我们在 Windows Server 上安装了 Excel，以帮助我们使用 Power Shell 进行一些数据清理，这比 SQL 替代方案更容易。

注意:

我们在 Power Shell 打开 Excel 时遇到问题。我们发现通过手动打开 Excel，Microsoft 试图通过一个对话框强制自动更新。我们关闭了对话框并关闭了 Excel，允许 Power Shell 打开 Excel。希望您不会遇到此问题。

下载并安装 Microsoft Assessment and Planning (MAP) Toolkit 9.3
从以下地址下载并安装 Microsoft Assessment and Planning (MAP) Toolkit 9.3：https://www.microsoft.com/en-us/download/details.aspx?&id=7826。按照基本提示安装产品。

配置 Microsoft Assessment and Planning (MAP) Toolkit 9.3
以下是我们用于配置 Microsoft Assessment and Planning Toolkit 的步骤。请按照说明进行操作

从开始菜单启动 MAP。
在第一个窗口中，单击“创建清单数据库”单选按钮，然后在“创建清单数据库”单选按钮下方的“名称”文本框中键入 MAPDB，然后单击“确定”。
在下一个窗口中，单击左侧的“用法跟踪”。
找到 SQL Server 框，然后单击“收集清单数据”链接。
在“清单方案”窗口中，选中“SQL Server”复选框，然后单击“下一步”。
接下来，在“发现方法”窗口中，选择“使用 Active Directory Domain Services (AD DS)”，然后单击“下一步”。
继续，在“Active Directory 凭据”窗口中，键入域、具有域访问权限的域帐户及其关联密码，然后单击“下一步”。
在“Active Directory 选项”窗口中，单击“查找指定域、容器和组织单位中的计算机”。
- 选择您认为最有可能包含 SQL Server 的 AD 容器。
- 单击“下一步”。
现在，在“所有计算机凭据”窗口中，单击“创建”按钮。
- 输入一个 AD 帐户的帐户名和密码，该帐户最有可能能够登录到 AD 中的 SQL Server。
- 单击“保存”。
- 单击“下一步”按钮。
在“凭据顺序”窗口中，单击“下一步”。
在“摘要”窗口中，单击“完成”。
- 在“用法跟踪”部分中，单击“SQL Server”框。
- 单击“生成 SQL Server 用法跟踪报告”。

重要：等待评估完成。

在“摘要”窗口中——完成。
等待“清单和评估”窗口执行数据收集，然后单击“关闭”按钮。
现在，再次单击“用法跟踪”部分中的“SQL Server”框，以查看与我们第一次单击时略有不同的结果。
- 单击“生成 SQL Server 用法跟踪报告”链接。
- 等待报告生成，然后单击“关闭”按钮。
- 打开新创建的 Excel 电子表格，并验证是否可以看到收集到的数据。然后，关闭电子表格，以便 Power Shell 稍后可以无冲突地打开它。

导入收集的 SQL Server 数据并运行报告

您已经创建了一个框架，允许您从 MAP 收集 SQL Server。接下来，我们将向您展示如何将这些数据导入 SQL Server SQLSunSet 数据库，以便我们可以使用 SSRS 报告来解释这些数据。从现在开始，您不必经历上面所有的步骤。相反，您只需运行 MAP 来收集最新的 SQL Server 信息，使用 Power Shell 将这些数据导入 SQL Server，然后运行 SQL Server Sunset Report。

首先，要获取最新的 SQL Server Active Directory 数据，您只需打开 MAP，选择“用法跟踪”，单击“SQL Server”框，然后选择“收集清单数据”链接。我们之所以这样说，是因为，通过上面的步骤，我们已经完成了这个操作。其次，打开 Power Shell，将 CollectSQLServerReportData.ps1 文件拖放到命令行上，然后按 Enter 运行 Power Shell 文件。

为了快速了解代码，请注意它首先检查 SQL Server 的管理单元，以确保它们已注册，如果未注册，则进行注册。然后，代码将 MAP 目录分配给 $dir 变量。代码使用变量 $LatestMapsUsageDir.name 和 $LatestMapsUsageFile.name 在 MAP 目录中找到 MAP 生成的最新用法跟踪 Excel 文件。使用 $excel、$workbook 和 $sheet 变量，代码打开 Excel 并删除电子表格中的前三行，以便 SQL Server 可以正确读取 Excel 文件（令人惊讶的是，这在 Power Shell 中比在 SQL Server 中更容易）。删除这些行后，我们关闭 Excel，退出应用程序，并确保内存已释放。最后，我们运行 invoke-sqlcmd 来运行 SQL Server 中的存储过程，该过程导入我们使用 $sqlvariable 变量命名的 Excel 文件。请注意，sqlcmd 通过 -ServerInstance $ENV:COMPUTERNAME\MAPS 连接到当前服务器的 MAPS SQL Server 实例。

列表 C：CollectSQLServerReportData.ps1

if ( (Get-PSSnapin -Name SqlServerCmdletSnapin120 -ErrorAction SilentlyContinue) -eq $null )
{
     Add-PSSnapin SqlServerCmdletSnapin120
}

if ( (Get-PSSnapin -Name  SqlServerProviderSnapin120 -ErrorAction SilentlyContinue) -eq $null )
{
     Add-PSSnapin  SqlServerProviderSnapin120
}

$dir = "C:\Users\$Env:USERNAME\Documents\MAP\"
$LatestMapsUsageDir=gci $dir | ? { $_.PSIsContainer } | sort CreationTime -desc | select -f 1
$LatestMapsUsageDir.name
$dir = "C:\Users\$Env:USERNAME\Documents\MAP\"+$LatestMapsUsageDir.name+"\SQLServerUsageTracker*.xlsx"
$LatestMapsUsageFile = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Select-Object -First 1
$LatestMapsUsageFile.name
$file="$LatestMapsUsageFile"
$sqlVariable="@MapsExcelFile_SQLServerUsageTracker='$LatestMapsUsageFile'"
write-host $sqlVariable
# Star Excel, hide window
$excel = new-object -com Excel.Application -Property @{Visible = $false}
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $Workbook.WorkSheets.item("SQL Server Instance Details")
if ("Assessment Results for Microsoft SQL Server Database Instances" -like $sheet.Cells.item(1,1).Text)
{
 [void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
 [void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
 [void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
}
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM

invoke-sqlcmd -Query "exec SQLSunSet.dbo.spImportMapSQLServers $sqlVariable" -ServerInstance $ENV:COMPUTERNAME\MAPS

现在，像以前一样，打开 SQL Server Reporting Services 并运行 SQLServerSunsetReportMain 报告。请注意，我们的示例数据现在已被您使用 Microsoft Assessment and Planning Toolkit 收集的数据替换。

要使用最新的 Active Directory 数据重新生成报告，只需重复操作即可。

打开 Microsoft MAP 并选择“用法跟踪”。在“方案”部分中单击“SQL Server”框，然后单击“收集清单数据”。清单完成后，单击“生成 SQL Server 用法跟踪报告”链接。
然后，在 Power Shell 中运行 CollectSQLServerReportData.ps1 文件。
最后，打开、运行并查看 SQLServerSunsetReportMain SSRS 报告。

SSRS 报告的简要说明

有趣的是，您实际上不需要真正了解 SSRS 报告的内部工作原理就可以使用它。但是，为了简要说明，我们确实使用 Report Builder 3.0 for SQL Server 2014 构建了我们的报告。正如您可能猜到的，我们的报告实际上是主报告（如图 C 所示）和子报告（如图 D 所示）的组合。主报告包含各种数据源，它们直接从 SQL Server 拉取数据以填充精美的图表和图形。子报告只有一个数据源，用于查询 SQL Server 以获取我们的 SQL Server 信息。

图 C：主报告

图 D：子报告

正如您在列表 D 到 L 中看到的，我们从以下 SQL 函数中派生了您看到的大部分值——我们稍后将对其进行描述。

GoodVersion_GoodSP
GoodVersion_BadSP
BadVersion_BadSP

例如，如果您打开主报告并查看数据集，您将看到与列表 D 中的相应 T-SQL 代码关联的 GoodServersGoodSp、GoodServersBadSp、BadServersBadSp。

列表 D：主报告 GoodServersGoodSp、GoodServersBadSp 和 BadServersBadSp 的三个主要数据集的查询

select dbo.GoodVersion_GoodSp() as GoodServers

select dbo.GoodVersion_BadSp() as BadSp

select dbo.BadVersion_BadSp() as BadSp

主报告

列表 E 到 M 显示了这些函数用于直接显示在 SSRS 主报告上的各种方式。列表 N 到 P 主要返回 SSRS 主报告中版本的图表数据。

列表 E：总服务器字段由 GoodVersion_GoodSP、GoodVersion_BadSP 和 BadVersion_BadSP 数据集及其 GoodServers、BadSp 和 BadSp 的相应值相加而成

=Sum(Fields!GoodServers.Value, "GoodServersGoodSp")+Sum(Fields!BadSp.Value, "GoodServersBadSP")+Sum(Fields!BadSp.Value, "BadServersBadSp")

列表 F：总版本字段是通过计算 SQLVersion 数据集中的 SQLVersion 来生成的

=Count(Fields!SQLVersion.Value, "SQLVersion")

列表 G：不受支持的版本是通过对 UnsupportedEditions 数据集中的 ID 进行求和来生成的

=Sum(Fields!ID.Value, "UnsupportedEditions")

列表 H：最新服务器（百分比）是一个包含 GoodVersion_GoodSP、GoodVersion_BadSP 和 BadVersion_BadSP 从其相应数据集中操作的方程

=Sum(Fields!GoodServers.Value, "GoodServersGoodSp")/(Sum(Fields!BadSp.Value, "BadServersBadSp")+Sum(Fields!BadSp.Value, "GoodServersBadSP")+Sum(Fields!GoodServers.Value, "GoodServersGoodSp"))

列表 I：最新服务器对 GoodServersGoodSp 数据集中的 GoodServers 字段进行求和

=Sum(Fields!GoodServers.Value, "GoodServersGoodSp")

列表 J：过期服务器（百分比）是一个包含 GoodVersion_GoodSP、GoodVersion_BadSP 和 BadVersion_BadSP 从其相应数据集中操作的方程

=Sum(Fields!BadSp.Value, "GoodServersBadSP")/(Sum(Fields!BadSp.Value, "BadServersBadSp")+Sum(Fields!BadSp.Value, "GoodServersBadSP")+Sum(Fields!GoodServers.Value, "GoodServersGoodSp"))

列表 K：过期服务器对 GoodSErversBadSP 数据集中的 BadSp 字段的值进行求和

=Sum(Fields!BadSp.Value, "GoodServersBadSP")

列表 L：不受支持的服务器是再次一个包含 GoodVersion_GoodSP、GoodVersion_BadSP 和 BadVersion_BadSP 从其相应数据集中操作的方程

=Sum(Fields!BadSp.Value, "BadServersBadSp") / (Sum(Fields!BadSp.Value, "BadServersBadSp") + Sum(Fields!BadSp.Value, "GoodServersBadSP") + Sum(Fields!GoodServers.Value, "GoodServersGoodSp"))

列表 M：不受支持的服务器对 BadServersBadSp 数据集中的 BadSp 字段的值进行求和

=Sum(Fields!BadSp.Value, "BadServersBadSp")

列表 N：这是我们用于从 vServerDetails 视图中提取 SQL Server 版本的 SQL 版本数据集，稍后我们将对此进行解释

SELECT
  count(*) as VersionCount, vServerDetails.SQLVersion
FROM
  vServerDetails
group by vServerDetails.SQLVersion
order by VersionCount Desc

列表 O：这是我们用于从 vSQLAgingReport 视图中提取各种 SQL 版本的不受支持的版本数据集，稍后我们将对此进行解释

SELECT
count(*)
from
(  
      select  [SQLServer] + ' ' + [dbmsversion] + ' Edition' as SQLVersion
     
      ,[sql_extendedsupportenddate]
     
  FROM [dbadmin].[dbo].[vSQLAgingReport]

  group by [SQLServer] + ' ' + [dbmsversion] + ' Edition',[sql_extendedsupportenddate]
  having [sql_extendedsupportenddate] <= getdate()
  ) t1

条形图轴数据

简而言之，我们提到了 SSRS 报告中条形图的数据点。图 E 显示报告聚合/求和版本，这些版本按分组显示，仅仅表示我们按 SQL Server 的版本对版本计数进行求和。

图 E：图表数据

子报告

这是我们用于生成子报告数据的查询。请注意，它从 vSQLSunsetReport 视图中选择数据，并结合 33、66、99、100 个用于 SSRS 指示器的指示器数字作为子报告的第一列。我们做了一些 T-SQL 调整以获得所需的正确排序。

列表 P：主子报告 SQL 查询

SELECT
  upper(vSQLSunSetReport.ServerName) as ServerName
  ,vSQLSunsetReport.InstanceName
  ,vSQLSunsetReport.SQLServer
  ,vSQLSunsetReport.dbmsversion
  ,vSQLSunsetReport.sql_extendedsupportenddate
  ,vSQLSunsetReport.sp_endsupportdate
  ,vSQLSunsetReport.LatestServicePack
  ,vSQLSunsetReport.servicepack
,case
when getdate() > vSQLSunsetReport.sql_extendedsupportenddate then 33
when getdate() > vSQLSunsetReport.sp_endsupportdate and getdate() <= vSQLSunsetReport.sql_extendedsupportenddate then 66
when (getdate() <= vSQLSunsetReport.sp_endsupportdate or vSQLSunsetReport.sp_endsupportdate  is null) and 
(getdate() <= vSQLSunSetReport.sql_extendedsupportenddate or vSQLSunsetReport.sp_endsupportdate is null) then 99

end gauge
,100 gaugetotal
FROM
  vSQLSunsetReport

ORDER BY  (CASE WHEN sp_endsupportdate IS NULL THEN 1 ELSE 0 END), SQLCompliance asc, SPCompliance asc, sql_extendedsupportenddate asc

SQLSunSet 数据库的注意事项和内部工作原理

恭喜您，您已完成我们关于检索和查看您的 SQL Server/Active Directory 数据和 Service Pack 信息的指南。但是，您可能需要注意 SQLSunSet 数据库中的一些事项。第一个注意事项显示了我们如何以编程方式推断服务器属于哪个集群，以及这在我们的组织中如何运作，但可能不适用于您。第二个注意事项只是解释您需要不时手动更新 SQL Server 停用数据。

我们将简要解释我们的 SQL Server 代码的作用。在 SQL SunSet 数据库中，我们有三个表、九个视图、一个存储过程和四个函数。表包含您组织的服务器以及来自 Microsoft 的 SQL Server 停用数据。视图对数据进行处理，使其准备好报告。存储过程导入数据，函数为报告创建特定值。

注意事项一：MAP 数据不代表 Windows 集群组

首先，Microsoft Assessment and Planning Toolkit 没有很好的方法来配对集群中的 SQL Server 节点。因为它不知道，所以当我们查看 MAP 数据时，我们也不知道。但是，我们可以通过编程方式假设哪些服务器是集群节点，因为我们公司的服务器的计算机名称以 V 开头。这意味着您的集群服务器可能未在我们的 SSRS 报告中准确显示。例如，如果您有一个活动/活动集群，如 SERVERA\INSTANCEA 和 SERVERB\INSTANCEB，您可能会看到四种服务器组合，如下所示：

VSERVERA\INSTANCEA
VSERVERB\INSTANCEB
VSERVERB\INSTANCEA
VSERVERA\INSTANCEB

而不是仅仅

VSERVERA\INSTANCEA
VSERVERB\INSTANCEB

正如您可以想象的那样，一个 10 节点的活动/活动 SQL Server 集群将为您提供 100 种服务器组合。我们希望 MAP 能够准确反映和记录 Windows 集群组以避免此问题，但它没有。即便如此，MAP 提供的数据仍然让我感到惊讶！请注意，如果您在服务器名称前面看到一个不需要的 V，那是因为我们的代码正在为“我们的”SQL Server 集群环境做出假设。请修改我们的代码以做出您认为有益于您的更改。

注意事项二：SQL 停用信息是静态的，必须定期更新

另外，本文中我们没有提到的是，我们 painstaking 地浏览了 Microsoft 的 SQL Server 停用日期（来自 https://support.microsoft.com/en-us/lifecycle?C2=1044），并将它们输入到两个表中：SQL Support 和 SQLSupportServicePack。这两个表在 vSQLSupport 视图中合并，将此数据合并为更易读的格式。坏消息：当 Microsoft 更新其停用日期时，您需要更新这些表以反映其最新更改。但至少，您的更改将是增量的。

例如，SQLSupport 表包含关于 SQL Server 版本的一般信息，而 SQLSupportServicePack 表包含每个 Service Pack 的 Service Pack 信息。请记住，SQLSupportServicePack 中的 FK_SQLSupport 是与 SQL Support 表中的主键匹配的外键，在创建 SQLSupportServicePack 中的新行时需要手动输入。

使用 T-SQL 代码

所有数据均来自三个表：SQLSupport、SQLSupportServicePack 和 TempServerMap；前两个表是静态的。我们提供前两个表中的数据，而我们将向您展示如何使用 Microsoft MAP 数据填充 TempServerMap。最初，我们确实填充了 TempServerMap 的一些静态数据，以便您可以从我们提供的 zip 文件中初始查看报告。我们提供九个视图来聚合和筛选数据，使其成为可报告的数据。spImportMapSQLServers 存储过程从 MAP Excel 文件导入数据到 TempServerMap 表。此外，我们提供了一些函数，BadVersion_BadSP、GoodVersion_BadSP、GoodVersion_GoodSp，以帮助将数据直接呈现到 SSRS 报告。最后，我们有一个名为 MaxServers 的函数，它帮助我们确定每个 SQL Server 集群中存在的最大服务器数量。如前所述，我们用于确定 SQL Server 集群服务器的计算“可能不适用于您的环境”，因为我们是从我们非常具体的集群环境命名约定来确定集群服务器的。此外，如前所述，MAP 不提供集群的集群组，因此很难确定哪些服务器属于哪个 Windows 集群。希望将来会有所改变。此外，我们不使用默认实例 MSSQLSERVER 设置活动/活动集群，如列表 D 和 E 所示，我们的代码也假定如此——这可能不适用于您的环境。我们集群中的服务器名称相同，只是最后一个字母不同。正如您在列表 D 中看到的，我们使用CHARINDEX('.',[COMPUTER NAME]) - 1)) 来查找那些名称相同的服务器，以及列表 R 中所示的 MaxServers 函数来计算这些服务器以进行进一步计算。结果可能因人而异，但我们并不乐观认为我们的命名约定和集群的报告数据会符合您组织的方针。

SQL Server 表

正如我们提到的，我们使用了一小组表 SQLSupport、SQLSupportServicePack 和 TempServerMap，您可以在下面看到它们的结构。SQLSupport 包含有关 SQL Server 版本、Microsoft 的生命周期支持日期、版本和子版本号的数据。SQLSupportServicePack 包含每个 Service Pack 的 Service Pack 号码、支持开始日期和支持结束日期。稍后您将看到，我们将这两个表连接起来以获得 SQL Server 版本及其相关 Service Pack 的完整视图。TempServerMap 表包含从 Microsoft MAP 导入的数据——顺便说一句，它非常适合计算 SQL Server 的许可要求，因为它有一个包含每个服务器核心数的列。

SQLSupport

SQLSupport 表包含 SQL Server 的常规生命周期开始日期、主流日期和扩展支持日期以及解析后的 SQL 版本信息。

列表 Q：SQLSupport 表　

USE [SQLSunSet]
GO

/****** Object: Table [dbo].[SQLSupport] Script Date: 1/4/2016 11:03:16 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SQLSupport](
[PK] [int] IDENTITY(1,1) NOT NULL,
[SQLServer] [varchar](50) NULL,
[LifeCycleStartDate] [date] NULL,
[MaintstreamSupportEndDate] [date] NULL,
[ExtendedSupportEnddate] [date] NULL,
[Version] [int] NULL,
[SubVersionStart] [int] NULL,
[SubVersionEnd] [int] NULL,
CONSTRAINT [PK__SQLSuppo__321507876D5093CD] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SQLSupport] WITH CHECK ADD CONSTRAINT [FK_SQLSupport_SQLSupport] FOREIGN KEY([PK])
REFERENCES [dbo].[SQLSupport] ([PK])
GO

ALTER TABLE [dbo].[SQLSupport] CHECK CONSTRAINT [FK_SQLSupport_SQLSupport]
GO

SQLSupportServicePack

SQLSupportServicePack 表包含 SQL Server Service Pack 信息，例如 Service Pack 号码、版本和支持日期。

列表 R：SQLSupportServicePack 表

USE [SQLSunSet]
GO

/****** Object: Table [dbo].[SQLSupportServicePack] Script Date: 1/4/2016 11:03:29 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SQLSupportServicePack](
[PK] [int] IDENTITY(1,1) NOT NULL,
[ServicePack] [varchar](50) NULL,
[ServicePackNum] [int] NULL,
[StartSupportDate] [date] NULL,
[EndSupportDate] [date] NULL,
[FK_SQLSupport] [int] NULL,

CONSTRAINT [PK__SQLSuppo__32150787380BD012] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SQLSupportServicePack] WITH CHECK ADD CONSTRAINT [FK_SQLSupportServicePack_SQLSupport] FOREIGN KEY([FK_SQLSupport])
REFERENCES [dbo].[SQLSupport] ([PK])
GO

ALTER TABLE [dbo].[SQLSupportServicePack] CHECK CONSTRAINT [FK_SQLSupportServicePack_SQLSupport]
GO

TempServerMap

TempServerMap 表包含从 SQL Server 用法跟踪报告导入的数据。此表是一个保存容器，用于接收来自 MAP Excel SQL Server 用法文件的原始数据。每次运行导入时，此表都会被删除并重新创建。

列表 S：TempServerMap 表

/****** SELECT INTO creates this table structure within the stored procedure******/
USE [SQLSunSet]
GO

/****** Object: Table [dbo].[TempServerMap] Script Date: 1/4/2016 11:03:44 AM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TempServerMap](
[Computer Name] [nvarchar](255) NULL,
[Machine Type] [nvarchar](255) NULL,
[Physical Machine Name] [nvarchar](255) NULL,
[SQL Server Product Name] [nvarchar](255) NULL,
[SQL Server Edition] [nvarchar](255) NULL,
[SQL Server Version] [nvarchar](255) NULL,
[SQL Service Pack Level] [float] NULL,
[Instance Name] [nvarchar](255) NULL,
[Operating System] [nvarchar](255) NULL,
[Operating System Architecture Type] [nvarchar](255) NULL,
[CPU] [nvarchar](255) NULL,
[Machine Serial Number] [nvarchar](255) NULL,
[Number of Processors] [float] NULL,
[Number of Total Cores] [float] NULL,
[Number of Logical Processors] [float] NULL,
[Number of Host Processors] [nvarchar](255) NULL,
[Clustered?] [nvarchar](255) NULL,
[SQL Service State] [nvarchar](255) NULL,
[IP Address] [nvarchar](255) NULL,
[WMI Status] [nvarchar](255) NULL
) ON [PRIMARY]

SQL Server 视图

我们使用几个视图来处理来自 TempServerMap 表或 SQLSupport、SQLSupportServicePack 表的数据。SQLAllServers 包含从以下视图联合的数据：SQLClusterActiveActive、SQLClusterActivePassive、SQLStandAlone。正如您可能猜到的，SQLClusterActiveActive 包含我们的活动/活动 SQL 集群服务器，SQLClusterActivePassive 包含我们的活动/被动 SQL 集群服务器，SQLStandAlone 包含我们的独立服务器。正如我们已经提到的，集群视图可能无法反映您环境中的集群 SQL Server，因为 Microsoft MAP 收集的集群信息不足。它对我们有效，因为我们使用特定的命名约定来命名我们的集群 SQL Server。

vSQLSupport 视图格式化来自 SQLSupport 表和 vSQLSupportServicePack 视图的数据，使其更易读。vSQLSupportServicePack 在 SQLSupportServicePack 表中添加了一个行号，以便我们可以对行号进行计算。vSQLSupportMaxServicePack 显示每个 SQL Server 版本的最高 Service Pack。vSQLSunsetReport 实际上是我们用于生成 SSRS 报告详细信息的查询——它直接或间接包含来自 SQLSunSet 数据库中所有其他视图的数据。

SQLClusterActiveActive
SQLClusterActivePassive
SQLStandAlone
SQLAllServers
vServerDetails
vSQLSupportServicePack
vSQLSupport
vSQLSupportMaxServicePack
vSQLSunsetReport

SQLClusterActiveActive

我们环境的 SQLClusterActiveActive 视图包含代表活动/活动集群配置的 SQL Server。同样，此功能是专门针对我们的环境和服务器名称硬编码的，因为 MAP 不记录 Windows 集群组。

列表 T：SQLClusterActiveActive 视图

USE [SQLSunSet]

GO

/****** Object: View [dbo].[SQLClusterActiveActive] Script Date: 1/4/2016 10:47:44 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[SQLClusterActiveActive]

AS

/*

This query makes certain assumptions about the cluster group for each server. Microsoft MAP does not provide the cluster group.
The only reason we know which cluster group to choose in CCHMC for this reporting is that we have a naming convention.
Here, we make the assumption that each computer in a cluster is seperated by a unique letter at the end of the computer name.
To find this grouping of servers we use:
SUBSTRING([COMPUTER NAME],0,CHARINDEX('.', [COMPUTER NAME])-1)
and also where [instance name] not like 'mssqlserver' and [clustered?] like 'yes'
Also, with the MAP reporting, Microsoft multiples the number of clusters and instances together so a 3 node active/active cluster
will show up in MAP as 9 server combinations. This duplication is not helpful.
What this view does is seperates the combinations of servers and instances. For example suppose we had a 3 node cluster with four
instances. MAP would give 12 combinations of servers and instances--not very helpful since an instance usually just resides on
one node. What this view does is seprates the servers 3(A,B,C) from the
instances (1,2,3,4) and them combines them like so:
Server: Instance:
A 1
B 2
C 3
C 4
This get's rid of the multiplcation done by Microsoft MAP-- and somewhat resolves that lack of storing a cluster group.*/

SELECT SUBSTRING(SQLServerName, 0, CHARINDEX('.', SQLServerName) ) SQLServerName,
[instance name], t2.[Computer Name], t2.[Machine Type], t2.[Physical Machine Name], t2.[SQL Server Product Name], t2.[SQL Server Edition],
t2.[SQL Server Version], t2.[SQL Service Pack Level]/* ,[Instance Name]*/ , t2.[Operating System], t2.[Operating System Architecture Type],
t2.[CPU]/* ,[Machine Serial Number]*/ , t2.[Number of Processors], t2.[Number of Total Cores], t2.[Number of Logical Processors], t2.[Number of Host Processors],
t2.[Clustered?]/* ,[SQL Service State]*/ , t2.[IP Address], t2.[WMI Status]
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY SUBSTRING([COMPUTER NAME], 0, CHARINDEX('.', [COMPUTER NAME]) - 1)
ORDER BY SUBSTRING([COMPUTER NAME], 0, CHARINDEX('.', [COMPUTER NAME]) - 1)) AS Row, *
FROM (SELECT DISTINCT
'V' + [Computer Name] SQLServerName, [Computer Name], [Machine Type], [Physical Machine Name], [SQL Server Product Name], [SQL Server Edition],
[SQL Server Version], [SQL Service Pack Level]/* ,[Instance Name]*/ , [Operating System], [Operating System Architecture Type],
[CPU]/* ,[Machine Serial Number]*/ , [Number of Processors], [Number of Total Cores], [Number of Logical Processors], [Number of Host Processors],
[Clustered?]/* ,[SQL Service State]*/ , [IP Address], [WMI Status]
/* ,[SQLServerName]*/ FROM tempservermap
WHERE [clustered?] LIKE 'yes' AND [instance name] NOT LIKE 'mssqlserver') t1)
t2
/*We had to trick T-SQL into joining Row 1 for servers with instances beyond the count of servers
(i.e. 3 servers, 4 instances means 1 instance beyond the server count). Here, we wanted to join 1 from the servers
to subsequent instances beyond the server count. It may sound strange, but really we have now way of knowing how many
instances are on a cluster and where those instances are supposed to be. Here we simply tack on the "extra" instances
to the last node in the cluster. Before that each instance is matched alphabetically with a clustered server node.
Our company follows this kind of naming procedure so it works well for what we do here.*/
JOIN
(SELECT [computer name], [instance name], CASE WHEN row > dbo.maxservers([computer name]) THEN dbo.maxservers([computer name]) ELSE row END AS row
FROM (SELECT [computer name], [instance name], ROW_NUMBER() OVER (PARTITION BY [computer name]
ORDER BY [instance name]) AS Row
FROM (SELECT DISTINCT SUBSTRING([COMPUTER NAME], 0, CHARINDEX('.', [COMPUTER NAME]) - 1) AS [computer name], [instance name]
FROM tempservermap
WHERE [instance name] NOT LIKE 'mssqlserver' AND [clustered?] LIKE 'yes') t1) t2) t3 ON charindex(t3.[computer name], t2.[computer name]) > 0 AND
t2.row = t3.row

GO

SQLClusterActivePassive

我们环境的 SQLClusterActivePassive 视图包含代表活动/被动集群配置的 SQL Server。

列表 U：SQLClusterActivePassive 视图

/* Again, this code makes another assumption that we know to be true in our environment. That active/passive configurations use the
default mssqlserver instance whereas active/active configurations do not. If this is not true in your environment you will need
to modify this code to suit your needs. */

select
SQLServerName
,[instance name]
,t1.[Computer Name]
,t1.[Machine Type]
,t1.[Physical Machine Name]
,t1.[SQL Server Product Name]
,t1.[SQL Server Edition]
,t1.[SQL Server Version]
,t1.[SQL Service Pack Level]
-- ,[Instance Name]
,t1.[Operating System]
,t1.[Operating System Architecture Type]
,t1.[CPU]
-- ,[Machine Serial Number]
,t1.[Number of Processors]
,t1.[Number of Total Cores]
,t1.[Number of Logical Processors]
,t1.[Number of Host Processors]
,t1.[Clustered?]
-- ,[SQL Service State]
,t1.[IP Address]
,t1.[WMI Status]
from tempservermap t1
join
(SELECT dISTINCT 'V'+ SUBSTRING([COMPUTER NAME],0,CHARINDEX('.', [COMPUTER NAME])-1) +'A' as SQLServerName FROM tempservermap where [clustered?]='yes' and [instance name] like 'MSSQLSERVER') t2
on charindex(right(SQLServerName, len(SQLServerName)-1),[computer name]) > 0


GO

SQLStandAlone

SQLStandAlone 视图显示了我们的代码将其视为独立 SQL Server 的所有服务器。

列表 W：SQLStandAlone 视图

SQLAllServers

SQLAllServers 视图使用 union 语句组合了 SQLClusterActiveActive、SQLClusterActivePassive 和 SQLStandAlone 视图，以方便使用。

列表 V：SQLAllServers 视图

USE [SQLSunSet]
GO
/****** Object: View [dbo].[SQLStandAlone] Script Date: 1/4/2016 10:56:08 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create view [dbo].[SQLStandAlone]
as
select
SUBSTRING([COMPUTER NAME],0,CHARINDEX('.', [COMPUTER NAME])) SQLServerName
,[instance name]
,[Computer Name]
,[Machine Type]
,[Physical Machine Name]
,[SQL Server Product Name]
,[SQL Server Edition]
,[SQL Server Version]
,[SQL Service Pack Level]
-- ,[Instance Name]
,[Operating System]
,[Operating System Architecture Type]
,[CPU]
-- ,[Machine Serial Number]
,[Number of Processors]
,[Number of Total Cores]
,[Number of Logical Processors]
,[Number of Host Processors]
,[Clustered?]
-- ,[SQL Service State]
,[IP Address]
,[WMI Status]
from tempservermap where [clustered?] like 'no'
GO

vServerDetails

vServerDetails 视图将来自 SQLAllServers 视图的 SQL Server 数据准备成更易读的列名，并解析和创建 SQL Server 版本列，以便更容易地用于数学方程式。

列表 X：vServerDetails 视图　

USE [SQLSunSet]
GO

/****** Object: View [dbo].[vServerDetails] Script Date: 1/4/2016 11:01:31 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vServerDetails]
AS
SELECT SQLServerName AS ServerName, [instance name] AS InstanceName, [Computer Name] AS ComputerName, [Machine Type] AS MachineType,
[SQL Server Product Name] + ' ' + [SQL Server Edition] + ' ' + 'Edition' AS SQLVersion, [SQL Server Product Name] AS SQLServerProductName,
[SQL Server Edition] AS DBMSVersion, [SQL Server Version] AS ProductNumber, [SQL Service Pack Level] AS ServicePack, [Operating System] AS OperatingSystem,
[Operating System Architecture Type] AS OSArchitecture, CPU, [Number of Processors] AS Processors, [Number of Total Cores] AS Cores,
[Number of Logical Processors] AS LogicalProcessors, [Number of Host Processors] AS HostProcessors, [Clustered?] AS isClustered,
CASE LEFT(CONVERT(varchar(255), [SQL Server Version]), 1) WHEN '8' THEN CONVERT(int, parsename([SQL Server Version], (3))) ELSE CONVERT(int,
parsename([SQL Server Version], (4))) END AS cVersion, CASE LEFT(CONVERT(varchar(255), [SQL Server Version]), 1) WHEN '8' THEN CONVERT(int,
parsename([SQL Server Version], (2))) ELSE CONVERT(int, parsename([SQL Server Version], (3))) END AS cSubVersion, CASE LEFT(CONVERT(varchar(255),
[SQL Server Version]), 1) WHEN '8' THEN CONVERT(int, parsename([SQL Server Version], (1))) ELSE CONVERT(int, parsename([SQL Server Version], (2)))
END AS cSPLevel
FROM dbo.SQLAllServers

GO

vSQLSupportServicePack

vSQLSupportServicePack 视图从 SQLSupportServicePack 中提取一部分数据，并增加了一个有价值的补充——它向混合体中添加了一个 row_number，用于计算我们集群环境中的服务器数量。

列表 Y：vSQLSupportServicePack 视图　

USE [SQLSunSet]
GO

/****** Object: View [dbo].[vSQLSupportServicePack] Script Date: 1/4/2016 11:01:53 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[vSQLSupportServicePack]
as
select row_number() over(order by FK_SQLSupport, servicepack) as row,
[ServicePack]
,[ServicePackNum]
,[StartSupportDate]
,[EndSupportDate]
,[FK_SQLSupport]
FROM [SQLSunSet].[dbo].[SQLSupportServicePack]

GO

vSQLSupport:

vSQLSupport 视图将 SQLSupport 表与 SQLSupportServicePack 合并，以显示一个反规范化但信息丰富的视图。

列表 Z：vSQLSupport 视图

USE [SQLSunSet]
GO

/****** Object: View [dbo].[vSQLSupport] Script Date: 1/4/2016 11:02:10 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--sp_refreshview vsqlsupport

CREATE VIEW [dbo].[vSQLSupport]
AS
SELECT SS.SQLServer, SS.LifeCycleStartDate AS SQL_LifeCycleStartDate, SS.MaintstreamSupportEndDate AS SQL_MaintstreamSupportEndDate,
SS.ExtendedSupportEnddate AS SQL_ExtendedSupportEndDate, SS.Version AS SQL_Version, SS.SubVersionStart AS SQL_SubVersionStart,
SS.SubVersionEnd AS SQL_SubVersionEnd, SP.ServicePack, SP.StartSupportDate AS SP_StartSupportDate, SP.EndSupportDate AS SP_EndSupportDate,
SP.ServicePackNum AS SP_VersionStart,
(SELECT ServicePackNum - 1 AS Expr1
FROM dbo.vSQLSupportServicePack AS r
WHERE (row = SP.row + 1) AND (SP.FK_SQLSupport = FK_SQLSupport)) AS SP_VersionEnd, SP.FK_SQLSupport
FROM dbo.SQLSupport AS SS FULL OUTER JOIN
dbo.vSQLSupportServicePack AS SP ON SS.PK = SP.FK_SQLSupport

GO

vSQLSupportMaxServicePack

vSQLSupportMaxServicePack 视图计算每个 SQL Server 版本的最高 Service Pack 级别。

列表 AA：vSQLSupportMaxServicePack 视图

USE [SQLSunSet]
GO

/****** Object: View [dbo].[vSQLSupportMaxServicePack] Script Date: 1/4/2016 11:02:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[vSQLSupportMaxServicePack]
as
select distinct t.sqlserver, t.servicepack, [SQL_ExtendedSupportEndDate],t.sql_version, t.sql_subversionstart,t.sql_subversionend from vsqlsupport v
join
(
SELECT [SQLServer],sql_version, sql_subversionstart,sql_subversionend,max([ServicePack]) as servicepack
FROM [SQLSunSet].[dbo].[vSQLSupport]
group by sqlserver,sql_version, sql_subversionstart,sql_subversionend
) t
on v.sqlserver = t.sqlserver

GO

vSQLSunsetReport

vSQLSunsetReport 视图将 vServerDetails、vSQLSupport 合并以生成最终的 SSRS 报告。

列表 BB：vSQLSunsetReport 视图　

USE [SQLSunSet]
GO

/****** Object: View [dbo].[vSQLSunsetReport] Script Date: 1/4/2016 11:02:46 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[vSQLSunsetReport]
as
SELECT TOP 100 percent
[ServerName],
[InstanceName],
v.SQLServer,
s.dbmsversion,
v.sql_maintstreamsupportenddate,
v.sql_extendedsupportenddate,
v.sp_versionstart,
v.sp_versionend,
v.sp_endsupportdate,
[ProductNumber]
,[cVersion]
,[cSubVersion]
,[cSPLevel]
,v.servicepack
,vm.SQL_ExtendedSupportEndDate LatestSupportDate
,vm.servicepack LatestServicePack,
case
when v.sql_extendedsupportenddate< getdate() and v.sql_extendedsupportenddate is not null then 0
else 1
end SQLCompliance,
case when v.sp_endsupportdate < getdate() and v.sp_endsupportdate is not null then 0
else 1
end SPCompliance
FROM [SQLSunSet].[dbo].[vServerDetails] S left
join vSQLSupport V
on v.sql_version=s.cversion and s.csubversion between v.sql_subversionstart and v.sql_subversionend
and s.cSPLevel between isnull(v.sp_versionstart,0) and isnull(v.sp_versionend,v.sp_versionstart)
left join vSQLSupportMaxServicePack VM on
vm.sql_version=s.cversion and s.csubversion between vm.sql_subversionstart and vm.sql_subversionend
-- where servername like 'MCSQLTEST1'
--where sp_endsupportdate <=getdate()

-- ORDER BY v.sql_extendedsupportenddate desc,(CASE WHEN v.sp_endsupportdate IS NULL THEN 1 ELSE 0 END),V.sp_endsupportdate desc

GO

SQL Server 过程

我们只有一个存储过程 spImportMapSQLServers，我们用它来将 Microsoft MAP 电子表格数据导入 TempServerMaps 表。

spImportMapSQLServers

spImportMapSQLServers 存储过程将 Excel SQL Server 用法跟踪报告导入 TempServerMaps 表。我们使用之前安装的 Microsoft Office 引擎安装的 Microsoft.Ace.OLEDB.12.0。

列表 CC：spImportMapSQLServers 存储过程

USE [SQLSunSet]
GO

/****** Object: StoredProcedure [dbo].[spImportMapSQLServers] Script Date: 1/4/2016 11:03:58 AM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[spImportMapSQLServers]
@MapsExcelFile_SQLServerUsageTracker varchar(1000)
as
declare @cmdstring varchar(8000) ='select * into SQLSunSet.dbo.TempServerMap from OPENROWSET (''Microsoft.Ace.OLEDB.12.0'', ''Excel 12.0;Database=<MapsExcelFile_SQLServerUsageTracker>'', ''select * from [SQL Server Instance Details$]'')'
set @cmdstring = replace(@cmdstring,'<MapsExcelFile_SQLServerUsageTracker>',@MapsExcelFile_SQLServerUsageTracker)
if exists (select * from sysobjects where type ='u' and name ='TempServerMap') drop table TempServerMap
--print (@cmdstring)
exec (@cmdstring)

GO

SQL Server 函数

我们使用以下函数将数字或百分比直接显示在我们的 SSRS 主报告上

BadVersion_BadSP
GoodVersion_BadSP
GoodVersion_GoodSP
MaxServers

BadVersion_BadSP

BadVersion_BadSPSQL 函数查找版本和 Service Pack 不受支持的 SQL Server。

列表 DD：BadVersion_BadSP 函数

USE [SQLSunSet]
GO

/****** Object: UserDefinedFunction [dbo].[BadVersion_BadSP] Script Date: 1/4/2016 11:04:14 AM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[BadVersion_BadSP]()
returns int
as
begin
declare @BadVersion_BadSP int
select @BadVersion_BadSP=count(*) FROM [SQLSunSet].[dbo].[vSQLSunsetReport] where sql_extendedsupportenddate <= getdate()
return(@BadVersion_BadSP)
end

GO

GoodVersion_BadSP

GoodVersion_BadSP SQL 函数查找版本良好但 Service Pack 不受支持的 SQL Server。

列表 EE：GoodVersion_BadSP 函数

USE [SQLSunSet]
GO

/****** Object: UserDefinedFunction [dbo].[GoodVersion_BadSP] Script Date: 1/4/2016 11:04:26 AM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[GoodVersion_BadSP]()
returns int
as
begin
declare @GoodVersion_BadSP int
select @GoodVersion_BadSP=count(*) FROM [SQLSunSet].[dbo].[vSQLSunsetReport] where sp_endsupportdate <= getdate() and sql_extendedsupportenddate > getdate()
return(@GoodVersion_BadSP)
end
GO

GoodVersion_GoodSP

GoodVersion_GoodSP SQL 函数查找版本和 Service Pack 都可接受的 SQL Server。

列表 FF：GoodVersion_GoodSP 函数

USE [SQLSunSet]
GO

/****** Object: UserDefinedFunction [dbo].[GoodVersion_GoodSP] Script Date: 1/4/2016 11:04:39 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[GoodVersion_GoodSP]()
returns int
as
begin
declare @GoodVersion_GoodSP int
select @GoodVersion_GoodSP=count(*) FROM [SQLSunSet].[dbo].[vSQLSunsetReport] where (sp_endsupportdate > getdate() or sp_endsupportdate is null) and (sql_extendedsupportenddate > getdate() or sql_extendedsupportenddate is null)
return(@GoodVersion_GoodSP)
end

GO

MaxServers

MaxServers 函数返回给定计算机名称的集群服务器计数。鉴于我工作的环境，这是一种计算方式，因为它适用于我们根据标准命名约定命名的集群服务器。如果您的命名约定与我们不同，此计数可能不准确——如前所述。

列表 GG：MaxServers 函数　

USE [SQLSunSet]
GO

/****** Object: UserDefinedFunction [dbo].[maxservers] Script Date: 1/4/2016 11:05:36 AM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[maxservers](@computername varchar(255))
returns int
as
begin
declare @maxservers int
select @maxservers= count(*) from
(

select distinct [computer name] as dummy ,SUBSTRING([COMPUTER NAME],0,CHARINDEX('.', [COMPUTER NAME])-1) as [computer name]
from tempservermap where [instance name] not like 'mssqlserver' and [clustered?] like 'yes'
) t1
group by [computer name] having charindex([computer name],@computername) >0
return(@maxservers)
end
GO

有用的链接，帮助了我们

让本文中的所有软件组件协同工作最初是一个挑战，因为这是我们第一次真正接触 Power Shell。我们发现以下链接对于让 Excel 导入与 SQL Server 一起工作以及 SQL 组件与 Power Shell 一起工作非常有用。希望您也觉得这些链接有用。

Excel/ 'Microsoft.ACE.OLEDB.12.0' provider:

http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

Power Shell

让 invoke-sqlcmd 工作

http://blog.sqlauthority.com/2014/11/23/sql-server-fix-the-term-invoke-sqlcmd-is-not-recognized-as-the-name-of-a-cmdlet/

http://stackoverflow.com/questions/12521239/powershell-does-not-recognize-invoke-sqlcmd

http://serverquestions.com/questions/dzlu/invoke-sqlcmd-not-working-after-powershell-4-install

让 DLL 和管理单元工作

http://guidestomicrosoft.com/2015/01/13/install-sql-server-powershell-module-sqlps/

https://markcz.wordpress.com/2009/12/19/how-to-register-powershell-sql-server-snapin-in-windows-preinstalled-powershell/

https://msdn.microsoft.com/en-us/library/hh231286.aspx?f=255&MSPPError=-2147217396

结论

在本文中，我们向您展示了如何收集有关您环境中 SQL Server 的数据，并在 SSRS 报告中显示这些信息。我们相信，这份报告显示了您关于 SQL Server 的最完整信息，因为它涉及收集、显示和聚合您的 SQL Server 信息、SQL 版本和 Service Pack。我们向您展示了一种使用 SQL Server 2014、Power Shell、SQL Server Reporting Services (SSRS)、Excel 和 Microsoft 评估和规划工具包 (MAP) 在几分钟内收集所有这些信息的快速方法。