sql azure 语法_Azure Data Studio中SQL Server架构比较扩展

sql azure 语法

This article explores the SQL Server Schema Compare extension in the Azure Data Studio.

本文探讨了Azure Data Studio中SQL Server架构比较扩展。

介绍 (Introduction)

We might have different copies of a database in different environments such as production, QA, development, etc. Sometimes we get a requirement to compare these databases for metadata such as tables, views, stored procedures, indexes and share the report with development teams. It helps to synchronize these databases and test the development codes.

我们可能在生产，质量保证，开发等不同环境中拥有不同的数据库副本。有时我们需要比较这些数据库的元数据（例如表，视图，存储过程，索引），并与开发团队共享报告。 它有助于同步这些数据库并测试开发代码。

You might think of using third-party tools such as Redgate SQL Compare, Apex SQL compare. We might use complex stored procedures for these schema comparisons.

您可能会考虑使用第三方工具，例如Redgate SQL Compare，Apex SQL比较。 我们可能会使用复杂的存储过程来进行这些模式比较。

Azure Data Studio概述 (Overview of Azure Data Studio)

Azure Data Studio is a cross-platform tool with many useful extensions to make the DBA and developer life more comfortable. We have covered many such useful features in previous articles. Azure Data Studio provides SQL Server Schema Compare extension for comparison of .dacpac files and databases. It also provides an option to apply any difference from source to a target database.

Azure Data Studio是一个跨平台工具，具有许多有用的扩展，使DBA和开发人员的生活更加舒适。 在先前的文章中，我们介绍了许多此类有用的功能。 Azure Data Studio提供了SQL Server架构比较扩展，用于比较.dacpac文件和数据库。 它还提供了一个选项，可以将源中的任何差异应用到目标数据库。

Before we proceed with this article, you can go ahead and download the November release of Azure Data Studio for the appropriate OS from the link.

在继续本文之前，您可以继续从链接下载适用于相应操作系统的Azure Data Studio十一月发行版。

If you have previous versions of Azure Data Studio installed on your system, I would recommend you upgrade it to November release because it contains the General Availability of Schema Compare and SQL Server .dacpac extensions.

如果您的系统上安装了以前版本的Azure Data Studio，我建议您将其升级到11月发行版，因为它包含架构比较的常规可用性和SQL Server .dacpac扩展。

测试环境设置 (Test environment setup)

For the demonstration in this article, let’s create two sample databases with the following script:

对于本文中的演示，让我们使用以下脚本创建两个示例数据库：

• Create [SourceDB] and [TargetDB] in SQL instance

  USE Master;
  CREATE DATABASE SourceDB;
  GO
  CREATE DATABASE TargetDB;
  GO
  

  在SQL实例中创建[SourceDB]和[TargetDB]

• Create a SQL table and stored procedure in both [SourceDB] and [TargetDB]

  CREATE TABLE Employee
  (id      INT IDENTITY, 
   EmpName VARCHAR(50)
  );
  GO
  CREATE PROC USP_EmpName(@EmpID INT)
  AS
      BEGIN
          SELECT EmpName
          FROM Employee
          WHERE id = @EmpID;
      END;
  GO
  

  在[SourceDB]和[TargetDB]中创建一个SQL表和存储过程

安装SQL Server架构比较扩展 (Install SQL Server Schema Compare extension)

Search for Schema Compare extension in the Marketplace. You can see a white star in the upper left corner of extension that shows it is a recommended extension by Azure Data Studio:

在市场中搜索“模式比较”扩展。 您可以在扩展名的左上角看到一个白星，表明它是Azure Data Studio推荐的扩展名：

Click on the Install button, and it quickly installs the SQL Server Schema Compare. We do not need a restart of Azure Data Studio:

单击安装按钮，它会快速安装SQL Server Schema Compare。 我们不需要重新启动Azure Data Studio：

探索模式比较扩展 (Explore Schema Compare extension)

We can launch a Schema Compare extension from the database context menu.

我们可以从数据库上下文菜单中启动“模式比较”扩展。

Right-click on the source database and then choose Schema Compare:

右键单击源数据库，然后选择“ 模式比较” ：

It launches Schema Compare with a populated source (source database from which we launched wizard) and blank target database:

它使用填充的源（我们从中启动向导的源数据库）和空白目标数据库启动Schema Compare：

We need to right-click on the ellipsis near the target connection:

我们需要右键单击目标连接附近的省略号：

It opens the following Schema Compare window:

它打开以下“模式比较”窗口：

Here, you get options to compare databases or data-tier application files (DACPAC). We can compare the schema in the following ways:

在这里，您可以选择比较数据库或数据层应用程序文件（DACPAC）。 我们可以通过以下方式比较架构：

Source	Destination
Database	Database
Database	DACPAC file
DACPAC file	DACPAC file
DACPAC file	database

资源	目的地
数据库	数据库
数据库	DACPAC文件
DACPAC文件	DACPAC文件
DACPAC文件	数据库

源（数据库）到目标（数据库）架构比较 (Source (Database) to destination (Database) schema comparison)

Let’s compare both source (SourceDB) and destination (TargetDB) database; we created earlier in the article:

让我们比较源数据库（SourceDB）和目标数据库（TargetDB）。 我们在本文前面创建的：

Click OK, and we can see both source and target database details in the below screenshot:

点击OK ，我们可以在下面的屏幕截图中看到源数据库和目标数据库的详细信息：

Click on Compare, and it starts the comparison, as shown below. It might take longer depending upon the database size and objects:

点击Compare ，它开始比较，如下所示。 它可能需要更长的时间，具体取决于数据库的大小和对象：

We do not see any schema difference between both the databases. Schema Compare also highlights no schema difference:

我们看不到两个数据库之间的任何架构差异。 模式比较也没有显示任何模式差异：

In the source database, let’s make the following changes:

在源数据库中，进行以下更改：

• Drop the existing table Employee and stored procedure [USP_EmpName]

  Drop table [dbo].[Employee]
  Drop procedure [dbo].[USP_EmpName]
  

  删除现有表Employee和存储过程[USP_EmpName]

• Create an Employee table with an additional column [EmpLocation]

  CREATE TABLE [dbo].[Employee]
  ([id]      [INT] IDENTITY(1, 1) NOT NULL, 
   [EmpName] [VARCHAR](50) NULL, 
   [Country] [VARCHAR](30));
  

  创建带有附加列[EmpLocation]的Employee表

• Create a Stored procedure to retrieve this additional column

  CREATE PROC [dbo].[USP_EmpName](@EmpID INT)
  AS
      BEGIN
          SELECT EmpName,Country
          FROM Employee
          WHERE id = @EmpID;
      END;
  GO
  

  创建一个存储过程来检索此附加列

Again, click on Compare, and you can see the output in Azure Data Studio Schema Compare.

再次，单击Compare ，您可以在Azure Data Studio Schema Compare中看到输出。

In the following screenshot, we can see it shows the difference in both the table and stored procedure:

在下面的屏幕截图中，我们可以看到它显示了表和存储过程的差异：

Click on the table, and it shows the comparison details as per the following screenshot:

单击表格，并按照以下屏幕截图显示比较详细信息：

Here, we can see that the source table contains [EmpName] and [Country] columns while the target contains [EmpName] columns only.

在这里，我们可以看到源表包含[EmpName]和[Country]列，而目标表仅包含[EmpName]列。

浏览模式比较菜单选项 (Explore Schema Compare menu options)

Let’s go over to menu options in Schema Compare extension of Azure Data Studio:

让我们转到Azure Data Studio的Schema Compare扩展中的菜单选项：

• Compare: As shown earlier, it is used to compare the source and target specified
  比较：如前所示，它用于比较指定的源和目标
• Stop: Schema comparison might take longer depending upon the schema and objects. We can stop the comparison using this button
  停止：模式比较可能需要更长的时间，具体取决于模式和对象。 我们可以使用此按钮停止比较

• Generate Script: We can use this option for generating the script of changes that we can directly apply to the target database. We cannot use this option while comparing two DACPAC’s

  Let’s click on Generate script for the comparison we performed between the source and destination database. It generates the script for the targetDB

  

  In the problem area, we can see problems with the line number, pointer to the line. We can click on any problem, and it takes you to a problematic line.

  It might make you wonder why Azure Data Studio is generating a problematic code.

  It is an SQLCMD code, and SQL Server does not recognize it. We can execute this script in SQLCMD mode only. Click on Enable SQLCMD as highlighted below. It changes the option from Enable SQLCMD to Disable SQLCMD:

  

  Once we enable the SQLCMD mode, we do not see any problems for generating script:

  Click on Run, and it executes the script in the target database:

  

  After the applied script, both database schemas should be in synchronized status. Go back to Schema Compare and again start the comparison process for validation purposes. In the following screenshot, we can verify that there is no schema difference between the source and target database:

  生成脚本：我们可以使用此选项来生成可以直接应用于目标数据库的更改脚本。 比较两个DACPAC时，我们无法使用此选项

  让我们单击Generate脚本 ，以进行源数据库和目标数据库之间的比较。 它为targetDB生成脚本

  

  在问题区域，我们可以看到行号，指向该行的指针的问题。 我们可以单击任何问题，它会将您带到有问题的线路。

  这可能使您想知道Azure Data Studio为什么生成有问题的代码。

  它是一个SQLCMD代码，并且SQL Server无法识别它。 我们只能在SQLCMD模式下执行此脚本。 单击启用S​​QLCMD，如下突出显示。 它将选项从“启用SQLCMD”更改为“禁用SQLCMD”：

  

  启用S​​QLCMD模式后，看不到生成脚本的任何问题：

  单击Run ，它将在目标数据库中执行脚本：

  

  在应用脚本之后，两个数据库模式都应处于同步状态。 返回到Schema Compare，然后再次启动比较过程以进行验证。 在以下屏幕截图中，我们可以验证源数据库和目标数据库之间没有架构差异：

• Apply: We can directly apply the changes in the target database using this option. Let’s click on it and verify it. Click on Yes in following the prompt dialog

  

  In the Tasks, it gives the message that schema changes are successful

  You can verify it by again generating the comparison report

  应用：我们可以使用此选项直接在目标数据库中应用更改。 让我们单击它并进行验证。 在提示对话框中单击“ 是”

  

  在“任务”中，该消息指示架构更改成功

  您可以通过再次生成比较报告来进行验证

• Options: We get multiple configuration options for schema comparison. For example, Block on possible data loss, Drop constraints not in source, ignore permissions, and ignore partitions. We should be careful while making changes in these rules as it might affect the schema comparison in both the databases and can generate inconsistent results

  

  选项：我们有多个配置选项用于架构比较。 例如，阻止可能的数据丢失，丢弃约束不在源中，忽略权限和忽略分区。 在更改这些规则时，我们应该小心，因为这可能会影响两个数据库中的架构比较，并且可能产生不一致的结果

• Switch Direction: It swaps the source and target connections. For example, at present, I have the following source and destination

  

  Click on Switch direction. It swaps the connections and performs schema comparison as well. In the following screenshot, we can see swap connections. We do not see any differences in the schema; therefore, it gives a message that no schema differences were found:

  切换方向：交换源连接和目标连接。 例如，目前，我有以下来源和目标

  

  单击切换方向。 它交换连接并执行模式比较。 在以下屏幕截图中，我们可以看到交换连接。 我们在架构上看不到任何差异。 因此，它给出一条消息，指出未找到任何模式差异：

• Open .scmp\Save .scmp: Schema comparison extension also allows you to save the comparison in a XML format. We can refer to this XML later once required. Click on Save. SCMP first and provide a location with a file name. The XML file looks like as shown below. It contains the version, source, target and the current state of all the options. We can open this .scmp file using the open .scmp option and provide the path of the file

  

  打开.scmp \ Save .scmp：模式比较扩展名还允许您以XML格式保存比较。 以后需要时，我们可以参考此XML。 点击保存。 首先使用SCMP并提供带有文件名的位置。 XML文件如下所示。 它包含所有选项的版本，源，目标和当前状态。 我们可以使用open .scmp选项打开此.scmp文件，并提供文件的路径

源（数据库）到目标（.DACPAC）架构比较 (Source (Database) to destination (.DACPAC) schema comparison)

DACPAC file contains database model including all database objects like table, views, logins, and procedures. It is an abbreviation of the Data-tier Application package. You can read more about DACPAC in Microsoft docs.

DACPAC文件包含数据库模型，其中包括所有数据库对象，例如表，视图，登录名和过程。 它是d ATA层的缩写甲 PPLIÇ通货膨胀PAC卡格。 您可以在Microsoft文档中阅读有关DACPAC的更多信息。

Let’s generate a DACPAC file for the target database. Connect to SQL instance in SSMS.

让我们为目标数据库生成一个DACPAC文件。 连接到SSMS中SQL实例。

Right-click on the database |Menu |Tasks| Extract Data-tier Application:

右键单击数据库|菜单|任务| 提取数据层应用程序：

It opens the extract data-tier application wizard. It automatically takes input for application name, target version. Specify a location for this file and click on Next:

它打开提取数据层应用程序向导。 它会自动输入应用程序名称，目标版本。 指定此文件的位置，然后单击“ 下一步” ：

View the summary and click on Next:

查看摘要，然后单击下一步 ：

It creates and saves the DACPAC file in the destination directory:

它将创建DACPAC文件并将其保存在目标目录中：

Now go back to Schema Compare in Azure Data Studio and change the target to data-tier application package as shown below:

现在回到Azure Data Studio中的Schema Compare，然后将目标更改为数据层应用程序包，如下所示：

It gives a warning message because we want to compare schema from the source database to target a DACPAC file:

它发出警告消息，因为我们要比较源数据库中的架构以目标DACPAC文件为目标：

Click on Yes, and it starts the comparison process. We can see that it gives the comparison result similar to database schema comparison:

单击是 ，它开始比较过程。 我们可以看到它给出的比较结果类似于数据库模式比较：

Let’s do a few more schema comparison tests. Previously, we compare the database with the same object with a difference in structure.

让我们再做一些模式比较测试。 以前，我们将数据库与具有相同结构的相同对象进行比较。

Now, add a new table in the SourceDB:

现在，在SourceDB中添加一个新表：

USE SourceDB; 
GO
CREATE TABLE Customers
(id              INT, 
 CustomerName    VARCHAR(30), 
 CustomerOrderID INT
);

Go back to Azure Data Studio, start the Schema Compare wizard again between source and destination database and observe the result.

返回到Azure Data Studio，再次在源数据库和目标数据库之间启动“架构比较”向导，并观察结果。

In the following screenshot, we can see two types of actions:

在以下屏幕截图中，我们可以看到两种类型的动作：

• Change: It shows that the object exists in both the source and target database. We need to execute alter statements on the target database for synchronization purposes
  更改：它表明该对象同时存在于源数据库和目标数据库中。 我们需要在目标数据库上执行alter语句以实现同步
• Add: It shows that a particular object does not exist in the target database. We need to add this object to the target database for synchronization
  添加：表明目标数据库中不存在特定对象。 我们需要将此对象添加到目标数据库以进行同步

在架构同步过程中排除对象 (Exclude an object in the schema synchronization process)

We can exclude a particular object in the synchronization process or generate a script wizard. Remove the check from include column for an object that we want to exclude.

我们可以在同步过程中排除特定对象或生成脚本向导。 从包含列中删除我们要排除的对象的检查。

In the following screenshot, we have excluded [Employee] and [USP_EmpName] objects:

在以下屏幕截图中，我们排除了[Employee]和[USP_EmpName]对象：

Let’s generate the script, and in the script, we can see that it includes T-SQL for CREATE TABLE statement only:

让我们生成脚本，在脚本中，我们可以看到它仅包含T-SQL的CREATE TABLE语句：

进行更改之前备份目标数据库 (Backup target database before making changes)

It is always advisable to take a database backup before making any changes. It helps us to move back to the current state of the database if required.

始终建议在进行任何更改之前进行数据库备份。 如果需要，它可以帮助我们回到数据库的当前状态。

We might want the Schema Compare extension to take care of this requirement. Click on Options in Schema Compare and put a check on the Backup Database Before Changes:

我们可能希望Schema Compare扩展可以满足这一要求。 单击“模式比较”中的“ 选项” ，然后在“更改之前备份数据库”上进行检查：

Click OK, and it gives the following warning message because the options have changed:

单击“ 确定” ，由于选项已更改，因此会显示以下警告消息：

Click Yes, and it compares the schema with the modified option. Click Yes for applying the changes in the target database:

单击“ 是” ，它将模式与修改后的选项进行比较。 单击“ 是”以在目标数据库中应用更改：

It applies the changes to the target database, but it is not as per our requirement, right?

它将更改应用到目标数据库，但这不是我们的要求，对吗？

We have enabled the option to take a database backup before making changes. We did not get any message or prompt for database backup.

我们启用了在进行更改之前进行数据库备份的选项。 我们没有收到任何消息或提示进行数据库备份。

Let’s check database backup history from the MSDB system database using the following query:

让我们使用以下查询从MSDB系统数据库中检查数据库备份历史记录：

SELECT s.database_name, 
    m.physical_device_name, 
    s.backup_start_date,
    CASE s.[type]
        WHEN 'D'
        THEN 'Full'
        WHEN 'L'
        THEN 'Transaction Log'
    END AS BackupType, 
    s.recovery_model
FROM msdb.dbo.backupset s
  INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'TargetDB';

We can see a full database backup in the default backup directory.

我们可以在默认备份目录中看到完整的数据库备份。

Note: You should be careful before enabling the backup option before making a change to the database. In the case of the vast database, we might face an issue due to backup drive space. It might also take longer as well for database backup:

注意：在对数据库进行更改之前，启用备份选项之前应格外小心。 对于庞大的数据库，由于备份驱动器空间，我们可能会遇到问题。 数据库备份也可能需要更长的时间：

结论 (Conclusion)

In this article, we explored the Schema Compare extension in Azure Data Studio. We can compare databases, DACPAC files together using this extension. I find it useful, and you should give a try to compare objects without complex T-SQL or third-party tools.

在本文中，我们探讨了Azure Data Studio中的Schema Compare扩展。 我们可以使用此扩展名将数据库，DACPAC文件一起比较。 我发现它很有用，您应该尝试在没有复杂的T-SQL或第三方工具的情况下比较对象。

翻译自: https://www.sqlshack.com/sql-server-schema-compare-extension-in-azure-data-studio/

sql azure 语法