解析迁移到 SQL 数据库的过程中的 Transact-SQL 差异Resolving Transact-SQL differences during migration to SQL Database

从 SQL Server 将数据库迁移到 Azure SQL 数据库时,可能会发现需要对数据库进行一些重新设计才能迁移 SQL Server。When migrating your database from SQL Server to Azure SQL Database, you may discover that your SQL Server database requires some re-engineering before it can be migrated. 本文提供相关指南来帮助你执行此重新设计和了解重新设计是必需的基本原因。This article provides guidance to assist you in both performing this re-engineering and understanding the underlying reasons why the re-engineering is necessary. 若要检测不兼容性,请使用 Data Migration Assistant (DMA)To detect incompatibilities, use the Data Migration Assistant (DMA).

概述Overview

SQL Server 和 Azure SQL 数据库都完全支持应用程序使用的大多数 Transact-SQL 功能。Most Transact-SQL features that applications use are fully supported in both SQL Server and Azure SQL Database. 例如,核心 SQL 组件(如数据类型、运算符、字符串、算术、逻辑和光标函数等)在 SQL Server 和 SQL 数据库中的工作方式相同。For example, the core SQL components such as data types, operators, string, arithmetic, logical, and cursor functions, work identically in SQL Server and SQL Database. 但是,DDL(数据定义语言)和 DML(数据操作语言)元素中的一些 T-SQL 差异导致存在仅部分受支持的 T-SQL 语句和查询(我们会在本文后面的内容中介绍)。There are, however, a few T-SQL differences in DDL (data-definition language) and DML (data manipulation language) elements resulting in T-SQL statements and queries that are only partially supported (which we discuss later in this article).

此外,还有一些功能和语法根本不受支持,因为 Azure SQL 数据库的设计使其隔离功能与 master 数据库和操作系统的依赖项。In addition, there are some features and syntax that isn't supported at all because Azure SQL Database is designed to isolate features from dependencies on the master database and the operating system. 因此,大多数服务器级活动不适用于 SQL 数据库。As such, most server-level activities are inappropriate for SQL Database. T-SQL 语句和选项在配置服务器级选项、操作系统组件或指定文件系统配置时不可用。T-SQL statements and options aren't available if they configure server-level options, operating system components, or specify file system configuration. 需要此类功能时,通常是以某种其他方式从 SQL 数据库或从其他 Azure 功能或服务获取相应的替代项。When such capabilities are required, an appropriate alternative is often available in some other way from SQL Database or from another Azure feature or service.

例如,高可用性已内置于使用类似于 Always On 可用性组的技术的 Azure SQL 数据库中。For example, high availability is built into Azure SQL Database using technology similar to Always On Availability Groups. SQL 数据库不支持与可用性组相关的 T-SQL 语句,也不支持与 Always On 可用性组相关的动态管理视图。T-SQL statements related to availability groups are not supported by SQL Database, and the dynamic management views related to Always On Availability Groups are also not supported.

有关 SQL 数据库支持和不支持的功能的列表,请参阅  Azure SQL 数据库功能比较For a list of the features that are supported and unsupported by SQL Database, see Azure SQL Database feature comparison. 此页上的列表对该“准则和功能”一文进行了补充,并重点介绍了 Transact-SQL 语句。The list on this page supplements that guidelines and features article, and focuses on Transact-SQL statements.

具有部分差异的 Transact-SQL 语法语句Transact-SQL syntax statements with partial differences

核心 DDL(数据定义语言)语句可用,但某些 DDL 语句具有与磁盘放置和不支持功能相关的扩展。The core DDL (data definition language) statements are available, but some DDL statements have extensions related to disk placement and unsupported features.

  • CREATE 和 ALTER DATABASE 语句具有超过 36 个的选项。CREATE and ALTER DATABASE statements have over three dozen options. 这些语句包括文件定位、FILESTREAM 以及仅适用于 SQL Server 的服务中转站选项。The statements include file placement, FILESTREAM, and service broker options that only apply to SQL Server. 如果在迁移前创建数据库,这可能不是问题,但如果要迁移用于创建数据库的 T-SQL 代码,应将 CREATE DATABASE(Azure SQL 数据库)CREATE DATABASE (SQL Server Transact-SQL) 中的 SQL Server 语法进行比较,以确保所用的所有选项都受支持。This may not matter if you create databases before you migrate, but if you're migrating T-SQL code that creates databases you should compare CREATE DATABASE (Azure SQL Database) with the SQL Server syntax at CREATE DATABASE (SQL Server Transact-SQL) to make sure all the options you use are supported. Azure SQL 数据库的 CREATE DATABASE 语句还具有服务目标和仅适用于 SQL 数据库的弹性缩放选项。CREATE DATABASE for Azure SQL Database also has service objective and elastic scale options that apply only to SQL Database.
  • CREATE 和 ALTER TABLE 语句具有不能在 SQL 数据库上使用的 FileTable 选项,因为不支持 FILESTREAM。The CREATE and ALTER TABLE statements have FileTable options that can't be used on SQL Database because FILESTREAM isn't supported.
  • SQL 数据库支持 CREATE 和 ALTER login 语句,但未提供所有选项。CREATE and ALTER login statements are supported but SQL Database doesn't offer all the options. 要使数据库更易于移植,SQL 数据库建议尽可能使用包含的数据库用户,而不是使用登录名。To make your database more portable, SQL Database encourages using contained database users instead of logins whenever possible. 有关详细信息,请参阅 CREATE/ALTER LOGIN管理登录名和用户For more information, see CREATE/ALTER LOGIN and Manage logins and users.

Azure SQL 数据库不支持的 Transact-SQL 语法Transact-SQL syntax not supported in Azure SQL Database

除了与  Azure SQL 数据库功能比较中所述的不支持功能相关的 Transact-SQL 语句外,也不支持以下语句和语句组。In addition to Transact-SQL statements related to the unsupported features described in Azure SQL Database feature comparison, the following statements and groups of statements aren't supported. 因此,如果要迁移的数据库使用以下任一功能,请重新设计 T-SQL 以消除这些 T-SQL 功能和语句。As such, if your database to be migrated is using any of the following features, re-engineer your T-SQL to eliminate these T-SQL features and statements.

  • 系统对象的排序规则Collation of system objects
  • 相关连接:终结点语句。Connection related: Endpoint statements. SQL 数据库不支持 Windows 身份验证,但支持类似的 Azure Active Directory 身份验证。SQL Database doesn't support Windows authentication, but does support the similar Azure Active Directory authentication. 某些身份验证类型要求使用最新版本的 SSMS。Some authentication types require the latest version of SSMS. 有关详细信息,请参阅使用 Azure Active Directory 身份验证连接到 SQL 数据库或 Azure SQL 数据仓库For more information, see Connecting to SQL Database or Azure SQL Data Warehouse By Using Azure Active Directory Authentication.
  • 使用三个或四个部分名称的跨数据库查询。Cross database queries using three or four part names. (使用弹性数据库查询支持只读跨数据库查询。)(Read-only cross-database queries are supported by using elastic database query.)
  • 跨数据库所有权链接, TRUSTWORTHY 设置Cross database ownership chaining, TRUSTWORTHY setting
  • EXECUTE AS LOGIN 改用“EXECUTE AS USER”。EXECUTE AS LOGIN Use 'EXECUTE AS USER' instead.
  • 支持加密,但可扩展密钥管理除外Encryption is supported except for extensible key management
  • 事件:事件、事件通知、查询通知Eventing: Events, event notifications, query notifications
  • 文件位置:与数据库文件定位、大小以及 Azure 自动管理的数据库文件相关的语法。File placement: Syntax related to database file placement, size, and database files that are automatically managed by Azure.
  • 高可用性:与通过 Azure 帐户管理的高可用性相关的语法。High availability: Syntax related to high availability, which is managed through your Azure account. 这包括备份、还原、Always On、数据库镜像、日志传送、恢复模式的语法。This includes syntax for backup, restore, Always On, database mirroring, log shipping, recovery modes.
  • 日志读取器:依赖于在 SQL 数据库上不可用的日志读取器的语法:推送复制、更改数据捕获。Log reader: Syntax that relies upon the log reader, which isn't available on SQL Database: Push Replication, Change Data Capture. SQL 数据库可以是推送复制项目的订阅服务器。SQL Database can be a subscriber of a push replication article.
  • 函数:fn_get_sqlfn_virtualfilestatsfn_virtualservernodesFunctions: fn_get_sql, fn_virtualfilestats, fn_virtualservernodes
  • 硬件:与硬件相关的服务器设置(例如,内存、工作线程、CPU 相关性、跟踪标志)有关的语法。Hardware: Syntax related to hardware-related server settings: such as memory, worker threads, CPU affinity, trace flags. 请改用服务层级和计算大小。Use service tiers and compute sizes instead.
  • KILL STATS JOB
  • OPENQUERYOPENROWSETOPENDATASOURCE 和由四部分构成的名称OPENQUERY, OPENROWSET, OPENDATASOURCE, and four-part names
  • .NET Framework:CLR 与 SQL Server 集成.NET Framework: CLR integration with SQL Server
  • 语义搜索Semantic search
  • 服务器凭据:改用数据库范围的凭据Server credentials: Use database scoped credentials instead.
  • 服务器级别项:服务器角色,sys.login_tokenServer-level items: Server roles, sys.login_token. GRANTREVOKEDENY 的服务器级权限不可用,某些权限已替换为数据库级权限。GRANT, REVOKE, and DENY of server level permissions aren't available though some are replaced by database-level permissions. 一些有用的服务器级 DMV 具有等效的数据库级 DMV。Some useful server-level DMVs have equivalent database-level DMVs.
  • SET REMOTE_PROC_TRANSACTIONS
  • SHUTDOWN
  • sp_addmessage
  • sp_configure 选项和 RECONFIGUREsp_configure options and RECONFIGURE. 可以通过 ALTER DATABASE SCOPED CONFIGURATION 使用某些选项。Some options are available using ALTER DATABASE SCOPED CONFIGURATION.
  • sp_helpuser
  • sp_migrate_user_to_contained
  • SQL Server 代理:依赖于 SQL Server 代理或 MSDB 数据库的语法:警报、运算符、中央管理服务器。SQL Server Agent: Syntax that relies upon the SQL Server Agent or the MSDB database: alerts, operators, central management servers. 改用脚本,如 Azure PowerShell。Use scripting, such as Azure PowerShell instead.
  • SQL Server 审核:改用 SQL 数据库审核。SQL Server audit: Use SQL Database auditing instead.
  • SQL Server 跟踪SQL Server trace
  • 跟踪标志:某些跟踪标志项已移至兼容模式。Trace flags: Some trace flag items have been moved to compatibility modes.
  • Transact-SQL 调试Transact-SQL debugging
  • 触发器:服务器作用域或登录触发器Triggers: Server-scoped or logon triggers
  • USE 语句:要将数据库上下文更改为不同的数据库,必须与新数据库建立新连接。USE statement: To change the database context to a different database, you must make a new connection to the new database.

完整的 Transact-SQL 引用Full Transact-SQL reference

有关 Transact-SQL 语法、用法和示例的详细信息,请参阅 SQL Server 联机丛书中的  Transact-SQL 参考(数据库引擎) 。For more information about Transact-SQL grammar, usage, and examples, see Transact-SQL Reference (Database Engine) in SQL Server Books Online.

有关“适用于”标记About the "Applies to" tags

Transact-SQL 参考包含从 SQL Server 2008 到最新版本的相关文章。The Transact-SQL reference includes articles related to SQL Server versions 2008 to the present. 文章标题下面有一个图标栏,其中列出了四个 SQL Server 平台,并指明了适用性。Below the article title there's an icon bar, listing the four SQL Server platforms, and indicating applicability. 例如,SQL Server 2012 中引入了可用性组。For example, availability groups were introduced in SQL Server 2012.  CREATE AVAILABILITY GROUP 一文指明该语句适用于 SQL Server(从版本 2012 开始)The CREATE AVAILABILITY GROUP article indicates that the statement applies to SQL Server (starting with 2012). 该语句不适用于 SQL Server 2008、SQL Server 2008 R2、Azure SQL 数据库、Azure SQL 数据仓库或并行数据仓库。The statement doesn't apply to SQL Server 2008, SQL Server 2008 R2, Azure SQL Database, Azure SQL Data Warehouse, or Parallel Data Warehouse.

在某些情况下,产品中可能使用了某篇文章的常规主旨,但产品之间存在细微差异。In some cases, the general subject of an article can be used in a product, but there are minor differences between products. 在适当的情况下,我们会在文章的中间位置指出该差异。The differences are indicated at midpoints in the article as appropriate. 在某些情况下,产品中可能使用了某篇文章的常规主旨,但产品之间存在细微差异。In some cases, the general subject of an article can be used in a product, but there are minor differences between products. 在适当的情况下,我们会在文章的中间位置指出该差异。The differences are indicated at midpoints in the article as appropriate. 例如,CREATE TRIGGER 文章在 SQL 数据库中可用。For example, the CREATE TRIGGER article is available in SQL Database. 但服务器级触发器的 ALL SERVER 选项指示不能在 SQL 数据库中使用服务器级触发器。But the ALL SERVER option for server-level triggers, indicates that server-level triggers can't be used in SQL Database. 请改用数据库级触发器。Use database-level triggers instead.

后续步骤Next steps

有关 SQL 数据库支持和不支持的功能的列表,请参阅  Azure SQL 数据库功能比较For a list of the features that are supported and unsupported by SQL Database, see Azure SQL Database feature comparison. 此页上的列表对该“准则和功能”一文进行了补充,并重点介绍了 Transact-SQL 语句。The list on this page supplements that guidelines and features article, and focuses on Transact-SQL statements.