ssms .net3.5_SSMS 17.5的新增功能；数据发现和分类等-CSDN博客

ssms .net3.5

SQL Server Management Studio aka SSMS is the default integrated graphical user interface tool that is used to access, administrate, configure, manage, query and monitor the different SQL Server instances that are hosted locally at the user machine, remotely on a server or anywhere in the cloud. It is a comprehensive environment that allows us to edit, debug and deploy scripts written in different languages such as T-SQL, DAX, MDX, XML and more.

SQL Server Management Studio（又名SSMS）是默认的集成图形用户界面工具，可用于访问，管理，配置，管理，查询和监视在用户计算机本地，服务器上或服务器中任何位置托管的不同SQL Server实例。云端。它是一个全面的环境，使我们可以编辑，调试和部署以不同语言（例如T-SQL，DAX，MDX，XML等）编写的脚本。

In my previous articles, we followed the enhancements to the existing SSMS features, the fixes to the bugs found and reported in the previous SSMS versions and the new features added to each new release. These new features that are introduced in the previous versions include the Connections dialog box new options, the embedded Performance Dashboard, Showplan node search, Import Flat File Wizard, the XEvent Profiler and Always On Availability Group dashboard new additions, which are described deeply in these articles.

在我以前的文章中，我们介绍了对现有SSMS功能的增强功能，对在先前SSMS版本中发现和报告的错误的修复以及在每个新发行版中添加的新功能。先前版本中引入的这些新功能包括“连接”对话框的新选项，嵌入式性能仪表板，Showplan节点搜索，“导入平面文件向导”，“ XEvent Profiler”和“始终在线可用性”仪表板新增加的功能，这些功能中对此进行了详细说明。文章。

Recall that, starting from SQL Server 2017 version, the SSMS versioning track is separated from the SQL Server Engine versioning. Where SQL Server 2017 has version number starting with 14.0, but the SSMS assigned version number starting from 17.X. In addition, starting from SQL Server 2016, Microsoft SSMS tool installation media is separated from the SQL Server Engine installation media, that requires you to download it from the Microsoft Download page to install it to your machine or server.

回想一下，从SQL Server 2017版本开始，SSMS版本控制轨道与SQL Server Engine版本控制是分开的。其中SQL Server 2017的版本号从14.0开始，但SSMS分配的版本号从17.X开始。此外，从SQL Server 2016开始，Microsoft SSMS工具安装媒体与SQL Server Engine安装媒体是分开的，这要求您从Microsoft下载页面下载它以将其安装到计算机或服务器上。

SSMS 17.5 is released now, and can be downloaded from the Microsoft Download page, with the ability to download SSMS 17.5 complete installation file to install it to your machine for the first time, or download the 17.5 upgrade package file, which is smaller, to upgrade the current 17.X SSMS to the latest 17.5 SSMS version as shown in the installation media below:

SSMS 17.5现在已经发布，可以从Microsoft下载页面下载，可以下载SSMS 17.5完整安装文件以将其首次安装到您的计算机上，或者下载较小的17.5升级软件包文件来如下所示的安装媒体所示，将当前的17.X SSMS升级到最新的17.5 SSMS版本：

The SSMS 17.5 installation or upgrade process is very easy, double-click on the installation media and the below window will be displayed, from which you can click on the Install option to start the installation, with the ability to install it side by side with earlier SSMS versions:

SSMS 17.5的安装或升级过程非常简单，双击安装媒体，将显示以下窗口，您可以从中单击Install（安装）选项开始安装，并可以与之并行安装早期的SSMS版本：

Once completed successfully, the below Setup Completed window will be shown, if there is no issue faced during the installation process:

成功完成后 ，如果在安装过程中没有遇到任何问题，将显示下面的“ 安装完成”窗口：

You can check that the latest SSMS version is installed on the machine with build number equal to 14.0.17224.0 by browsing the About option from the Help menu as shown clearly below:

您可以通过浏览“ 帮助”菜单中的“ 关于”选项，检查内部版本号等于14.0.17224.0的计算机上是否安装了最新的SSMS版本，如下所示：

SSMS 17.5 provides full support for all features on SQL Server 2008 version through the SQL Server 2017 latest version and the latest cloud features available in Azure SQL Database and Azure SQL Data Warehouse, with the ability to connect to SQL Server instances installed on Linux servers using SSMS 17.5 version.

SSMS 17.5通过SQL Server 2017最新版本以及Azure SQL数据库和Azure SQL数据仓库中可用的最新云功能全面支持SQL Server 2008版本上的所有功能，并能够使用以下方法连接到安装在Linux服务器上SQL Server实例： SSMS 17.5版本。

In SSMS 17.5, bugs that have been reported in the previous SSMS versions were fixed. These fixed bugs include:

在SSMS 17.5中，修复了先前SSMS版本中已报告的错误。这些固定的错误包括：

Inaccurate Live Query Statistics elapsed time
实时查询统计信息不正确的经过时间
Showplan was not able to recognize the Apply logical operator
Showplan无法识别“应用”逻辑运算符
An SPID issue when throwing an error such as “Input string was not in correct format” if the query enables the “SET SGOWPLAN_ALL” option
如果查询启用了“ SET SGOWPLAN_ALL”选项，则抛出诸如“输入字符串的格式不正确”之类的错误时，SPID问题
Managed Compression would fail for indexes 托管压缩将失败
An issue with the Merge Audit Files
合并审核文件存在问题

SSMS 17.5 comes also with two major enhancements to the existing features. The first one is related to the Query Editor that allows us to skip a specific number of rows when loading a delimited text files into the Azure SQL Data Warehouse using the SkipRow option of the Delimited Text External File Format. The second enhancement in that SSMS version, is related to the ShowPlan feature, that enhanced by enabling the display of the Estimated Plan button for the Azure SQL Data Warehouse. SSMS 17.5 comes with one new valuable feature, the Data Discovery and Classification feature, that we will describe deeply in this article.

SSMS 17.5还对现有功能进行了两个主要增强。第一个与查询编辑器有关，当使用分隔的文本外部文件格式的SkipRow选项将分隔的文本文件加载到Azure SQL数据仓库中时，查询编辑器使我们可以跳过特定数量的行。该SSMS版本中的第二个增强功能与ShowPlan功能有关，该功能通过启用Azure SQL数据仓库的“ 估计计划”按钮的显示而得到增强。 SSMS 17.5带有一项新的重要功能，即数据发现和分类功能，我们将在本文中对其进行详细介绍。

数据发现和分类 (Data Discovery and Classification)

The 17.5 version of SSMS introduces a new SSMS built-in security tool with a set of advanced services, that is used to scan, discover, classify, label and report the sensitive data in the database. These sensitive data include business, financial and healthcare information. The Data Discovery and Classification tool helps us protecting our data, by meeting the data privacy standards and controlling the access to such data. This feature also helps us in the Data Compliance process by meeting the regulatory compliance requirements and generate reports for auditing purposes.

SSMS的17.5版本引入了一个新的SSMS内置安全工具，该工具带有一组高级服务，该服务用于扫描，发现，分类，标记和报告数据库中的敏感数据。这些敏感数据包括业务，财务和医疗保健信息。数据发现和分类工具通过满足数据隐私标准并控制对此类数据的访问来帮助我们保护数据。此功能还可以通过满足法规遵从性要求并为审核目的生成报告来帮助我们进行数据遵从性过程。

Using the Data Discovery and Classification, the classification engine will scan the database in order to identify the list of columns that contain potentially sensitive data and provide you with that list in order to review it and apply the recommended classification, or manually apply your own customized classification policy. After that, these classification attribute labels will be tagged on the columns that contain sensitive data, and then can be viewed with the database classification state in a detailed report, with the ability to export or print it for different purposes such as auditing purposes.

使用数据发现和分类，分类引擎将扫描数据库，以识别包含潜在敏感数据的列的列表，并向您提供该列表，以便对其进行检查和应用推荐的分类，或者手动应用您自己的自定义分类政策。之后，这些分类属性标签将被标记在包含敏感数据的列上，然后可以在详细报告中与数据库分类状态一起查看，并能够出于不同目的（例如审计目的）导出或打印。

To perform the data discovery and classification process on your database, right click on the database you plan to classify and from the Tasks list choose the Classify Data… option, as shown:

要在数据库上执行数据发现和分类过程，请右键单击要分类的数据库，然后从“ 任务”列表中选择“ 分类 数据…”选项，如下所示：

Directly, the classification engine will scan the chosen database for all columns that contain sensitive data and notify us with the number of these matches columns in a notification box as shown:

直接，分类引擎将扫描所选数据库中包含敏感数据的所有列，并在通知框中将这些匹配列的数目通知我们，如下所示：

To view the list of recommended classifications for the detected columns, click on the displayed notification box, and a window similar to the below will be displayed:

要查看检测到的列的推荐分类列表，请单击显示的通知框，然后将显示类似于以下的窗口：

The recommended classification list provides us with the name of the columns that contains sensitive data, the schema and the name of the table on which these columns located. In addition, the type of data stored in that column will be shown in the Information Types drop down list, that consider that column data as sensitive data. List of all sensitive data types provided in the Information Type drop down list shown below:

推荐的分类列表为我们提供了包含敏感数据的列的名称，架构以及这些列所在的表的名称。此外，该列中存储的数据类型将显示在“ 信息类型”下拉列表中，该列表将该列数据视为敏感数据。信息类型下拉列表中提供的所有敏感数据类型的列表如下所示：

And finally, the recommended classification attribute, that is used to define the sensitivity of the data stored in the specified column, will be also displayed on the Sensitivity Label drop down list, with all possible attributes shown below:

最后，推荐的分类属性（用于定义指定列中存储的数据的敏感性）也将显示在“ 灵敏度标签”下拉列表中，所有可能的属性如下所示：

After reviewing the recommended classifications, you can change the Information Type of the Sensitivity Label values provided by the classification engine with any value that fits your company policies or standards. To apply that classification, select all recommendations or set of recommendations on specific columns, by checking the checkbox on the left of these columns then click on the blue Accept selected recommendations button, as shown:

查看建议的分类后，您可以将分类引擎提供的“灵敏度标签”值的“信息类型”更改为适合您公司政策或标准的任何值。要应用该分类，请选中特定列上的所有建议或一组建议，方法是选中这些列左侧的复选框，然后单击蓝色的“ 接受所选建议”按钮，如下所示：

()

After accepting the recommended classification on the selected column, a notification message will be displayed, showing that the changes will not be updated until you save all changes, as shown:

在选定的列上接受建议的分类之后，将显示一条通知消息，显示更改将不会更新，除非您保存所有更改，如下所示：

If there is another column that you manage to classify and not listed in the recommended classifications, you can manually add it by clicking on the Add Classification button in the top menu of the window, as shown:

如果还有其他一列要分类并且没有在推荐的分类中列出，则可以通过单击窗口顶部菜单中的“ 添加分类”按钮来手动添加它，如下所示：

In the opened window at the right part of the SSMS, choose the name of the table that contains the column to be classified, the schema of that table, the name of the column to be classified, the Information Type of that column and finally the Sensitivity Label of that column. Then click on the Add button at the bottom of the window, as shown:

在SSMS右侧的打开的窗口中，选择包含要分类的列的表的名称，该表的模式，要分类的列的名称，该列的信息类型，最后选择该列的灵敏度标签。然后单击窗口底部的“添加”按钮，如下所示：

To complete the classification process and tag the database columns with the classification sensitivity label, click on the Save button in the top menu of the window and the classification changes will be updated as shown:

要完成分类过程并用分类敏感性标签标记数据库列，请单击窗口顶部菜单中的“ 保存”按钮，分类更改将更新，如下所示：

Data Discovery and Classification feature allows us also to generate a report, that summarizes the classification state of the database. After performing the previous classifications, click on the View Report button in the top menu of the window, as shown:

数据发现和分类功能使我们还可以生成报告，以总结数据库的分类状态。执行之前的分类后，单击窗口顶部菜单中的“ 查看报告”按钮，如下所示：

The displayed SQL Data Classification Report, will summarize the classification state of the selected database. We can derive from the generated report below that:

显示的“ SQL数据分类报告”将汇总所选数据库的分类状态。我们可以从下面生成的报告中得出：

The number of classified columns in that database is 5 columns, from the 84 database columns
该数据库中的分类列数为84列中的5列
There are 3 tables, from the total of 24 tables in that database, have sensitive data
该数据库中的24个表中有3个表具有敏感数据
The classified columns contain 3 types of sensitive information
分类列包含3种类型的敏感信息
A list of the 5 classified columns with the sensitive information type and sensitivity labels are shown also in details in the report, as shown:
报告中还详细显示了带有敏感信息类型和敏感度标签的5个分类列的列表，如下所示：

The previously generated report can be easily exported to PDF, Excel or Word format or printed directly as an auditing or compliance evidence. It is clear now how the SQL Server Data Discovery and Classification feature is useful in identifying the sensitive data stored in your database as the first step in securing and protecting the company sensitive data, taking into consideration that this needs your touches in classifying the rest of columns that contains sensitive data, but the names of these columns are not chosen well to indicate the type of data stored in these columns. Enjoy this new feature and stay tuned for the new SSMS releases!

先前生成的报告可以轻松导出为PDF，Excel或Word格式，或直接打印为审核或合规性证据。现在已经很清楚，SQL Server数据发现和分类功能对于识别存储在数据库中的敏感数据如何有用，这是保护和保护公司敏感数据的第一步，同时考虑到这需要您对其余部分进行分类包含敏感数据的列，但这些列的名称选择不当，无法指示存储在这些列中的数据类型。享受这项新功能，并随时关注新的SSMS版本！

Previous articles in this series:

本系列以前的文章：