使用ssms异机还原数据库_使用SSMS 18中的查询优化助手向导进行数据库升级
使用ssms异机还原数据库
This article will cover managing a SQL Server database upgrade using new features in SQL Server Management Studio 18 including the query tuning assistant wizard, database upgrade feature, query store and more
本文将介绍使用SQL Server Management Studio 18中的新功能（包括查询调整助手向导，数据库升级功能，查询存储等）来管理SQL Server数据库升级。
With the release of each new SQL Server version, a Database administrator’s tasks are often to plan the next database upgrade. The most important part of the upgrade part is the query tuning. When we perform a database upgrade or move the database to a SQL Server running with a higher version, we need to upgrade the SQL Server compatibility level to use the latest enhancements in the query optimizer features and, in general, all the new functionality of the recent release. There might be some queries or workloads, which might not run properly under the new database compatibility model, which will require fine-tuning that query.
随着每个新SQL Server版本的发布，数据库管理员的任务通常是计划下一次数据库升级。 升级部分中最重要的部分是查询调整。 当我们执行数据库升级或将数据库移至运行更高版本SQL Server时，我们需要升级SQL Server兼容性级别，以使用查询优化器功能中的最新增强功能，以及通常情况下的所有新功能。最新版本。 可能会有一些查询或工作负载，在新的数据库兼容性模型下可能无法正常运行，这将需要对该查询进行微调。
SQL Server 2016 introduced Query Store that helps to identify performance issue with the workload. We can do analysis of the execution plan change for any particular query and if required, we can revert to the previous execution plan to get a better performance. The Query store can always be helpful for any database level changes, SQL Server restart, database upgrade, index changes etc.
SQL Server 2016引入了查询存储，可帮助确定工作负载的性能问题。 我们可以分析任何特定查询的执行计划更改，并且如果需要，我们可以恢复到以前的执行计划以获得更好的性能。 查询存储对于任何数据库级别的更改，SQL Server重新启动，数据库升级，索引更改等始终可以有所帮助。
Suppose we want to upgrade ‘WideWorldImporters’ database from SQL Server 2017(Compatibility level 140) to SQL Server 2019 (Compatibility level 150). We can use the Query store as shown below.
假设我们要将'WideWorldImporters'数据库从SQL Server 2017（兼容性级别140）升级到SQL Server 2019（兼容性级别150）。 我们可以使用查询存储，如下所示。
Upgrade using the query store
使用查询存储升级
- SQL 2017 WideWorldImporters database – compatibility level 140 SQL 2017 WideWorldImporters数据库–兼容性级别140
- SQL 2019 WideWorldImporters database – compatibility level 140 SQL 2019 WideWorldImporters数据库–兼容性级别140
- SQL 2019 Create a baseline without changing the compatibility level SQL 2019在不更改兼容性级别的情况下创建基准
- SQL 2019 Upgrade the compatibility level to 150 SQL 2019将兼容性级别升级到150
- SQL 2019 View query store reports and force the ‘last known good plan’ if required SQL 2019查看查询存储报告并在需要时强制执行``最后一个好的计划''
You can see here, first we captured the baseline before changing the compatibility mode to the recent version as SQL Server 2019 (150). We changed the compatibility level and viewed the built-in Query Store reports later, forcing the last known good plan if there is any change in the query performance.
您可以在这里看到，首先我们捕获了基线，然后将兼容模式更改为最新版本SQL Server 2019（150）。 我们更改了兼容性级别，并在以后查看了内置的“查询存储”报告，如果查询性能发生任何更改，将强制执行最后一个已知的良好计划。
In SQL Server 2017, we come across Understanding automatic tuning in SQL Server 2017. The Automatic tuning feature allows SQL Server to compare the execution plan before and after the change and then forced the plan automatically.
在SQL Server 2017中，我们遇到了了解SQL Server 2017中的自动调整的问题 。 自动调整功能允许SQL Server在更改前后比较执行计划，然后自动强制执行计划。
Once you have installed SSMS 18.0. you can find the SSMS properties from the help section from SSMS.
一旦安装了SSMS 18.0。 您可以从SSMS的帮助部分找到SSMS属性。
Connect to the SQL Server instance and go to Tasks -> Database upgrade.
连接到SQL Server实例，然后转到“任务”->“数据库升级”。
SSMS 18 Database upgrade
SSMS 18数据库升级
- SQL 2017 WideWorldImporters database – compatibility level 140 SQL 2017 WideWorldImporters数据库–兼容性级别140
- SQL 2019 WideWorldImporters database – compatibility level 140 SQL 2019 WideWorldImporters数据库–兼容性级别140
- SQL 2019 Create a baseline without changing the compatibility level SQL 2019在不更改兼容性级别的情况下创建基准
- SQL 2019 Upgrade the compatibility level to 150 SQL 2019将兼容性级别升级到150
- SQL 2019 Collect workload data for the upgraded database and get the recommendation SQL 2019收集升级数据库的工作负载数据并获得建议
- SQL 2019 Apply the recommendation and monitor performance. Roll back if required SQL 2019应用建议并监视性能。 如果需要回滚
For our example, we will be using the ‘WideWorldImporters’ having compatibility level 150.
对于我们的示例，我们将使用兼容级别为150的“ WideWorldImporters”。
Before we proceed, let me explain this ‘Database Upgrade’ feature in SSMS 18.5
在继续之前，让我解释一下SSMS 18.5中的“数据库升级”功能
In this database upgrade method, it starts the Query Tuning Assistant. The query tuning assistant contains below steps:
在这种数据库升级方法中，它将启动查询优化助手。 查询调优助手包含以下步骤：
- The configuration of the Query Store 查询存储的配置
- Start Workload to collect baseline data of Query Store 启动工作负载以收集查询存储的基准数据
- Upgrade database compatibility 升级数据库兼容性
- Collect the workload data again to perform the comparison and regression detection 再次收集工作量数据以执行比较和回归检测
- Apply the recommendations and view the performance. If required, we can perform a rollback up as well 应用建议并查看性能。 如果需要，我们也可以执行回滚
Let us start the Database upgrade for our sample database. The first screen is to set up the workload duration and the target database compatibility level.
让我们开始对示例数据库进行数据库升级。 第一个屏幕是设置工作负载持续时间和目标数据库兼容性级别。
Workload Duration: Workload duration should be in days. Let us try to put the duration for less than 1 day i.e. 0.10 days.
工作量持续时间：工作量持续时间应以天为单位。 让我们尝试将持续时间少于1天，即0.10天。
Target Database Compatibility Level: It shows the target database compatibility level. For our instance, it automatically selects the available compatibility level 150.
目标数据库兼容性级别：显示目标数据库兼容性级别。 对于我们的实例，它会自动选择可用的兼容性级别150。
In the next screen, we can configure the Query Store so performance data can be captured. It gives the current state of the query store recommendation along with the recommendations. We can either use the recommendation or specify custom settings.
在下一个屏幕中，我们可以配置查询存储，以便可以捕获性能数据。 它给出了查询存储推荐的当前状态以及这些推荐。 我们可以使用建议，也可以指定自定义设置。
Now let go to the next step and review the setting. You can see the message written on this that it will not execute any workload instead you need to run the workload and monitor session actions from the Tasks -> Database upgrade menu options.
现在，转到下一步并查看设置。 您可以看到上面写着的消息，它不会执行任何工作负载，而是您需要从“任务”->“数据库升级”菜单选项运行工作负载并监视会话操作。
Click on the ‘Finish’ button to configure the session. Take a note of the error as shown below. It just says ‘Failed to create new action session. Most likely there is an existing active session. Please close or delete it before creating a new active session.’
单击“完成”按钮以配置会话。 记录下错误，如下所示。 它只是说“无法创建新的动作会话。 最有可能存在现有的活动会话。 在创建新的活动会话之前，请先关闭或删除它。”
This action does not give any particular error message. It is due to the number of days we specified on the first screen. A minimum number of days we can specify are 1 day. Go to the previous screen and specify the number of days as one.
此操作不会给出任何特定的错误消息。 这是由于我们在第一个屏幕上指定的天数。 我们可以指定的最少天数为1天。 转到上一个屏幕，然后将天数指定为1。
Once we have configured the Query tuning new session, go to ‘Tasks -> Database Upgrade -> Monitor Sessions’
配置好查询调整新会话后，请转到“任务->数据库升级->监视会话”
It opens the Query Tuning Assistant with five steps
它通过五个步骤打开查询优化助手
- Setup 建立
- Data Collection 数据采集
- View Analysis 查看分析
- View Findings 查看结果
- Verification 验证
步骤1：设置：在设置屏幕中，它显示创建的会话以及其状态，描述和开始时间。 (Step 1: Setup: In the setup screen, it shows the sessions created along with its status, description and the time started. )
Click on the particular session and then on the details.
单击特定的会话，然后单击详细信息。
步骤2.数据收集 (Step 2. Data Collection)
It comes to the baseline data collection. We can execute the workload against the database so that the Query store can store the baseline data.
它涉及基线数据收集。 我们可以针对数据库执行工作负载，以便查询存储区可以存储基线数据。
Once the workload is finished, we can click on the ‘Done with workload run’.
工作负载完成后，我们可以单击“运行工作负载完成”。
This will upgrade the compatibility level of the desired database. You can give confirmation by clicking on ‘Yes’
这将升级所需数据库的兼容性级别。 您可以通过单击“是”进行确认
You can see the confirmation message that the database compatibility level is upgraded successfully.
您会看到确认消息，说明数据库兼容性级别已成功升级。
You can verify the database compatibility level from the database properties. Compatibility level is now changed to SQL Server 2019 (150).
您可以从数据库属性验证数据库兼容性级别。 兼容性级别现已更改为SQL Server 2019（150）。
Go to ‘next’ step and you can see the list of regressed queries. If the workload is running, you can ‘Refresh’ the data. By default, it shows 20 queries. We can change the metric and aggregation criteria from the drop-down values.
转到“下一步”步骤，您可以看到回归查询的列表。 如果工作负载正在运行，则可以“刷新”数据。 默认情况下，它显示20个查询。 我们可以从下拉值中更改指标和汇总条件。
In the regressed queries, you can see queries ‘Tunable’ as True or False. Click on ‘Done with workload run’ and click on Next.
在回归查询中，您可以将查询“可调”查询为True或False。 单击“完成运行工作负载”，然后单击下一步。
步骤3：检视分析 (Step 3: View Analysis)
In this step, you can select the queries which we want to tune. You cannot select the queries for which Tuneable is set to false.
在此步骤中，您可以选择我们要调整的查询。 您不能选择将Tuneable设置为false的查询。
You can click on ‘Yes’ to go ahead. As mentioned in the screenshot, you cannot return to this page.
您可以单击“是”继续。 如屏幕截图中所述，您无法返回此页面。
步骤4：查看结果 (Step 4: View Findings )
In this step, we can deploy the recommendation generated in the previous step as plan guide. You can note that value in the last column ‘Can Deploy’ as true or false.
在这一步中，我们可以将上一步中生成的推荐部署为计划指南。 您可以注意到最后一列“可以部署”中的值为true或false。
The status is ‘Initial’ and we can find the baseline metric, observed metric, and the percentage change.
状态为“初始”，我们可以找到基准指标，观察到的指标和百分比变化。
In the ‘Query Option’ you can find the Microsoft docs link for the proposed hint which will improve the query performance. You can see the opened web page after clicking on a link.
在“查询选项”中，您可以找到建议的提示的Microsoft文档链接，这将改善查询性能。 单击链接后，您可以看到打开的网页。
Click on ‘Deploy’ and it moves to the last step.
单击“部署”，然后移至最后一步。
步骤5：验证 (Step 5: Verification)
In the verification step, you can find the status of the queries after we clicked on ‘Deploy’. In the below image, we can see the status as ‘Deployed’ along with the % change as 100%.
在验证步骤中，单击“部署”后，您可以找到查询的状态。 在下图中，我们可以看到状态为“已部署”，而百分比变化为100％。
We can still do a rollback. The value in the last column ‘Can Rollback’, true shows that we can roll back the changes in this query. It will remove the plan guide for the selected queries.
我们仍然可以回滚。 最后一列“可以回滚”中的值true表示我们可以回滚此查询中的更改。 它将删除所选查询的计划指南。
For our demo, let us select one query from the list and click on ‘Rollback’.
对于我们的演示，让我们从列表中选择一个查询，然后单击“回滚”。
This action does not provide any message about the rollback performed. However, it removes that particular query from the list as shown here.
该操作不提供有关执行回滚的任何消息。 但是，它会从列表中删除该特定查询，如下所示。
Click on ‘Sessions’ takes you to the initial screen where you can find a list of sessions.
单击“会话”将带您到初始屏幕，您可以在其中找到会话列表。
If we can want to remove the session, we can click on ‘Delete Session’. However, please note that if we delete a session, it does not perform rollback for the changes performed previously. In addition, we cannot do roll back with the query tuning advisor after removing the particular session.
如果我们想要删除会话，我们可以单击“删除会话”。 但是，请注意，如果我们删除会话，则该会话不会对以前执行的更改执行回滚。 此外，在删除特定会话之后，我们无法使用查询调整顾问进行回滚。
结论 (Conclusion)
Database Upgrade is a helpful feature to upgrade the databases from lower version to higher version. We can easily capture the baseline, apply recommendations and monitor the performance after the upgrade. If required, we can rollback recommendations too.
数据库升级是将数据库从较低版本升级到较高版本的有用功能。 升级后，我们可以轻松获取基准，应用建议并监控性能。 如果需要，我们也可以回滚建议。
翻译自: https://www.sqlshack.com/database-upgrade-using-the-query-tuning-assistant-wizard-in-ssms-18/
使用ssms异机还原数据库