SQL Server 与 Azure SQL 托管实例之间的 T-SQL 差异T-SQL differences between SQL Server & Azure SQL Managed Instance

适用于:是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Managed Instance

本文汇总并解释了 Azure SQL 托管实例与 SQL Server 之间的语法和行为差异。This article summarizes and explains the differences in syntax and behavior between Azure SQL Managed Instance and SQL Server.

SQL 托管实例可与 SQL Server 数据库引擎高度兼容,且 SQL 托管实例支持大多数功能。SQL Managed Instance provides high compatibility with the SQL Server database engine, and most features are supported in a SQL Managed Instance.

迁移

与 SQL Server 相比,SQL 托管实例中引入了一些 PaaS 限制,并且在行为方面有一些变化。There are some PaaS limitations that are introduced in SQL Managed Instance and some behavior changes compared to SQL Server. 这些差异划分为以下几个类别:The differences are divided into the following categories:

其中的大多数功能都是体系结构约束,代表服务功能。Most of these features are architectural constraints and represent service features.

在 SQL 托管实例中发现并将在将来解决的临时已知问题在“发行说明”页中进行了说明。Temporary known issues that are discovered in SQL Managed Instance and will be resolved in the future are described in release notes page.

可用性Availability

Always On 可用性组Always On Availability Groups

高可用性内置在 SQL 托管实例中,用户无法控制。High availability is built into SQL Managed Instance and can't be controlled by users. 不支持以下语句:The following statements aren't supported:

BackupBackup

SQL 托管实例包含自动备份,因此用户可以创建完整数据库 COPY_ONLY 备份。SQL Managed Instance has automatic backups, so users can create full database COPY_ONLY backups. 不支持差异、日志和文件快照备份。Differential, log, and file snapshot backups aren't supported.

  • 使用 SQL 托管实例,可以只将实例数据库备份到 Azure Blob 存储帐户:With a SQL Managed Instance, you can back up an instance database only to an Azure Blob storage account:
    • 仅支持 BACKUP TO URLOnly BACKUP TO URL is supported.
    • 不支持 FILETAPE 和备份设备。FILE, TAPE, and backup devices aren't supported.
  • 支持大多数常规 WITH 选项。Most of the general WITH options are supported.
    • COPY_ONLY 是必需的。COPY_ONLY is mandatory.
    • 不支持 FILE_SNAPSHOTFILE_SNAPSHOT isn't supported.
    • 不支持磁带选项 REWINDNOREWINDUNLOADNOUNLOADTape options: REWIND, NOREWIND, UNLOAD, and NOUNLOAD aren't supported.
    • 不支持日志特定的选项 NORECOVERYSTANDBYNO_TRUNCATELog-specific options: NORECOVERY, STANDBY, and NO_TRUNCATE aren't supported.

的限制:Limitations:

  • 使用 SQL 托管实例可将实例数据库备份到最多包含 32 个条带的备份,如果使用备份压缩,则这种方法对于不超过 4 TB 的数据库而言已足够。With a SQL Managed Instance, you can back up an instance database to a backup with up to 32 stripes, which is enough for databases up to 4 TB if backup compression is used.

  • 不能在使用服务托管透明数据加密 (TDE) 加密的数据库上执行 BACKUP DATABASE ... WITH COPY_ONLYYou can't execute BACKUP DATABASE ... WITH COPY_ONLY on a database that's encrypted with service-managed Transparent Data Encryption (TDE). 服务托管的 TDE 强制使用内部 TDE 密钥对备份进行加密。Service-managed TDE forces backups to be encrypted with an internal TDE key. 无法导出该密钥,因此无法还原备份。The key can't be exported, so you can't restore the backup. 使用自动备份和时间点还原,或者改用客户管理的 (BYOK) TDEUse automatic backups and point-in-time restore, or use customer-managed (BYOK) TDE instead. 也可以在数据库上禁用加密。You also can disable encryption on the database.

  • 在 SQL 托管实例中使用 BACKUP 命令最大可以设置 195 GB 的备份条带大小(即最大 Blob 大小)。The maximum backup stripe size by using the BACKUP command in SQL Managed Instance is 195 GB, which is the maximum blob size. 增加备份命令中的带状线数量以缩小单个带状线大小,将其保持在限制范围内。Increase the number of stripes in the backup command to reduce individual stripe size and stay within this limit.

    提示

    从本地环境或虚拟机中的 SQL Server 备份数据库时,若要解决此限制,可以:To work around this limitation, when you back up a database from either SQL Server in an on-premises environment or in a virtual machine, you can:

    • 备份到 DISK 而不是 URLBack up to DISK instead of backing up to URL.
    • 将备份文件上传到 Blob 存储。Upload the backup files to Blob storage.
    • 还原到 SQL 托管实例。Restore into SQL Managed Instance.

    SQL 托管实例中的 Restore 命令支持备份文件中的更大 Blob 大小,因为将使用不同的 Blob 类型来存储上传的备份文件。The Restore command in SQL Managed Instance supports bigger blob sizes in the backup files because a different blob type is used for storage of the uploaded backup files.

有关使用 T-SQL 进行备份的信息,请参阅 BACKUPFor information about backups using T-SQL, see BACKUP.

安全性Security

审核Auditing

在审核方面,Azure SQL 和 SQL Server 的主要差异是:The key differences between auditing in Azure SQL and in SQL Server are:

  • 在 SQL 托管实例中,审核在服务器级别执行。With SQL Managed Instance, auditing works at the server level. 在 Azure Blob 存储中存储 .xel 日志文件。The .xel log files are stored in Azure Blob storage.
  • 在 Azure SQL 数据库中,审核在数据库级别执行。With Azure SQL Database, auditing works at the database level. 在 Azure Blob 存储中存储 .xel 日志文件。The .xel log files are stored in Azure Blob storage.
  • 在本地 SQL Server 或虚拟机中,审核在服务器级别执行。With SQL Server, on-premises or in virtual machines, auditing works at the server level. 在文件系统或 Windows 事件日志中存储事件。Events are stored on file system or Windows event logs.

SQL 托管实例中的 XEvent 审核支持 Azure Blob 存储目标。XEvent auditing in SQL Managed Instance supports Azure Blob storage targets. 不支持文件和 Windows 日志。File and Windows logs aren't supported.

Azure Blob 存储审核的主要 CREATE AUDIT 语法差异为:The key differences in the CREATE AUDIT syntax for auditing to Azure Blob storage are:

  • 提供了新语法 TO URL,用于指定 .xel 文件要放到的 Azure Blob 存储容器的 URL。A new syntax TO URL is provided that you can use to specify the URL of the Azure Blob storage container where the .xel files are placed.
  • 不支持语法 TO FILE,因为 SQL 托管实例无法访问 Windows 文件共享。The syntax TO FILE isn't supported because SQL Managed Instance can't access Windows file shares.

有关详细信息,请参阅:For more information, see:

证书Certificates

由于 SQL 托管实例无法访问文件共享和 Windows 文件夹,因此存在以下约束:SQL Managed Instance can't access file shares and Windows folders, so the following constraints apply:

  • 不支持将 CREATE FROM/BACKUP TO 文件用于证书。The CREATE FROM/BACKUP TO file isn't supported for certificates.
  • 不支持 FILE/ASSEMBLY 中的 CREATE/BACKUP 证书。The CREATE/BACKUP certificate from FILE/ASSEMBLY isn't supported. 无法使用私钥文件。Private key files can't be used.

请参阅 CREATE CERTIFICATEBACKUP CERTIFICATESee CREATE CERTIFICATE and BACKUP CERTIFICATE.

解决方法:请勿在创建证书备份后再还原该备份,而应先获取证书二进制文件内容和私钥,将其存储为 .sql 文件,然后从二进制文件创建证书Workaround: Instead of creating backup of certificate and restoring the backup, get the certificate binary content and private key, store it as .sql file, and create from binary:

CREATE CERTIFICATE  
   FROM BINARY = asn_encoded_certificate
WITH PRIVATE KEY (<private_key_options>)

凭据Credential

仅支持 Azure Key Vault 和 SHARED ACCESS SIGNATURE 标识。Only Azure Key Vault and SHARED ACCESS SIGNATURE identities are supported. 不支持 Windows 用户。Windows users aren't supported.

请参阅 CREATE CREDENTIALALTER CREDENTIALSee CREATE CREDENTIAL and ALTER CREDENTIAL.

加密提供程序Cryptographic providers

由于 SQL 托管实例无法访问文件,因此无法创建加密提供程序:SQL Managed Instance can't access files, so cryptographic providers can't be created:

登录名和用户Logins and users

  • 支持使用 FROM CERTIFICATEFROM ASYMMETRIC KEYFROM SID 创建的 SQL 登录名。SQL logins created by using FROM CERTIFICATE, FROM ASYMMETRIC KEY, and FROM SID are supported. 请参阅 CREATE LOGINSee CREATE LOGIN.

  • 支持使用 CREATE LOGIN 语法或 CREATE USER FROM LOGIN [Azure AD 登录名] 语法创建的 Azure Active Directory (Azure AD) 服务器主体(登录名)。Azure Active Directory (Azure AD) server principals (logins) created with the CREATE LOGIN syntax or the CREATE USER FROM LOGIN [Azure AD Login] syntax are supported. 这些登录名是在服务器级别创建的。These logins are created at the server level.

    SQL 托管实例支持使用语法 CREATE USER [AADUser/AAD group] FROM EXTERNAL PROVIDER 的 Azure AD 数据库主体。SQL Managed Instance supports Azure AD database principals with the syntax CREATE USER [AADUser/AAD group] FROM EXTERNAL PROVIDER. 此功能也称为 Azure AD 包含的数据库用户。This feature is also known as Azure AD contained database users.

  • 不支持使用 CREATE LOGIN ... FROM WINDOWS 语法创建的 Windows 登录名。Windows logins created with the CREATE LOGIN ... FROM WINDOWS syntax aren't supported. 使用 Azure Active Directory 登录名和用户。Use Azure Active Directory logins and users.

  • 创建实例的 Azure AD 用户具有不受限制的管理特权The Azure AD user who created the instance has unrestricted admin privileges.

  • 可以使用 CREATE USER ... FROM EXTERNAL PROVIDER 语法创建非管理员 Azure AD 数据库级用户。Non-administrator Azure AD database-level users can be created by using the CREATE USER ... FROM EXTERNAL PROVIDER syntax. 请参阅 CREATE USER ...FROM EXTERNAL PROVIDERSee CREATE USER ... FROM EXTERNAL PROVIDER.

  • Azure AD 服务器主体(登录名)仅支持一个 SQL 托管实例中的 SQL 功能。Azure AD server principals (logins) support SQL features within one SQL Managed Instance only. 无论是在相同还是不同的 Azure AD 租户中,需要跨实例交互的功能都不支持 Azure AD 用户。Features that require cross-instance interaction, no matter whether they're within the same Azure AD tenant or different tenants, aren't supported for Azure AD users. 此类功能的示例包括:Examples of such features are:

    • SQL 事务复制。SQL transactional replication.
    • 链接服务器。Link server.
  • 不支持设置映射到作为数据库所有者的 Azure AD 组的 Azure AD 登录名。Setting an Azure AD login mapped to an Azure AD group as the database owner isn't supported.

  • 支持使用其他 Azure AD 主体模拟 Azure AD 服务器级主体,例如 EXECUTE AS 子句。Impersonation of Azure AD server-level principals by using other Azure AD principals is supported, such as the EXECUTE AS clause. EXECUTE AS 限制包括:EXECUTE AS Limitations are:

    • 当名称不同于登录名时,EXECUTE AS USER 不支持 Azure AD 用户。EXECUTE AS USER isn't supported for Azure AD users when the name differs from the login name. 例如,如果用户是通过语法 CREATE USER [myAadUser] FROM LOGIN [john@contoso.com] 创建的,则会尝试通过 EXEC AS USER = myAadUser 进行模拟。An example is when the user is created through the syntax CREATE USER [myAadUser] FROM LOGIN [john@contoso.com] and impersonation is attempted through EXEC AS USER = myAadUser. 基于 Azure AD 服务器主体(登录名)创建 USER 时,请指定与 LOGIN 中的 login_name 相同的 user_name。When you create a USER from an Azure AD server principal (login), specify the user_name as the same login_name from LOGIN.

    • 只有属于 sysadmin 角色的 SQL 服务器级主体(登录名)可以针对 Azure AD 主体执行以下操作:Only the SQL Server-level principals (logins) that are part of the sysadmin role can execute the following operations that target Azure AD principals:

      • EXECUTE AS USEREXECUTE AS USER
      • EXECUTE AS LOGINEXECUTE AS LOGIN
  • SQL 托管实例中的 Azure AD 用户在使用 SSMS V18.4 或更高版本SQLPackage.exe 时,可以使用 bacpac 文件进行数据库导出/导入。Database export/import using bacpac files are supported for Azure AD users in SQL Managed Instance using either SSMS V18.4 or later, or SQLPackage.exe.

    • 使用数据库 bacpac 文件时,可以使用以下配置:The following configurations are supported using database bacpac file:
      • 在同一 Azure AD 域的不同托管实例之间导出/导入数据库。Export/import a database between different manage instances within the same Azure AD domain.
      • 在同一 Azure AD 域中将数据库从 SQL 托管实例导出以及将其导入 SQL 数据库。Export a database from SQL Managed Instance and import to SQL Database within the same Azure AD domain.
      • 在同一 Azure AD 域中从 SQL 数据库导出数据库以及将其导入 SQL 托管实例。Export a database from SQL Database and import to SQL Managed Instance within the same Azure AD domain.
      • 将数据库从 SQL 托管实例导出以及将其导入 SQL Server(2012 或更高版本)。Export a database from SQL Managed Instance and import to SQL Server (version 2012 or later).
        • 在此配置中,所有 Azure AD 用户都创建为没有登录名的 SQL Server 数据库主体(用户)。In this configuration all Azure AD users are created as SQL Server database principals (users) without logins. 用户类型列为 SQL,在 sys.database_principals 中以 SQL_USER 的形式呈现。The type of users are listed as SQL and are visible as SQL_USER in sys.database_principals). 其权限和角色保留在 SQL Server 数据库元数据中,可以用于模拟。Their permissions and roles remain in the SQL Server database metadata and can be used for impersonation. 但是,它们不能用来通过其凭据访问和登录 SQL Server。However, they cannot be used to access and log in to the SQL Server using their credentials.
  • 只有服务器级主体登录名(由 SQL 托管实例预配进程创建)、服务器角色的成员(例如 securityadminsysadmin)或者在服务器级别拥有 ALTER ANY LOGIN 权限的其他登录名可以在 SQL 托管实例的 master 数据库中创建 Azure AD 服务器主体(登录名)。Only the server-level principal login, which is created by the SQL Managed Instance provisioning process, members of the server roles, such as securityadmin or sysadmin, or other logins with ALTER ANY LOGIN permission at the server level can create Azure AD server principals (logins) in the master database for SQL Managed Instance.

  • 如果登录名是 SQL 主体,则只有属于 sysadmin 角色的登录名才能使用 create 命令来为 Azure AD 帐户创建登录名。If the login is a SQL principal, only logins that are part of the sysadmin role can use the create command to create logins for an Azure AD account.

  • Azure AD 登录名必须是用于 Azure SQL 托管实例的同一目录中的 Azure AD 成员。The Azure AD login must be a member of an Azure AD within the same directory that's used for Azure SQL Managed Instance.

  • 从 SQL Server Management Studio 18.0 预览版 5 开始,Azure AD 服务器主体(登录名)将显示在对象资源管理器中。Azure AD server principals (logins) are visible in Object Explorer starting with SQL Server Management Studio 18.0 preview 5.

  • 允许 Azure AD 服务器主体(登录名)与 Azure AD 管理员帐户重叠。Overlapping Azure AD server principals (logins) with an Azure AD admin account is allowed. 解析主体以及将权限应用到 SQL 托管实例时,Azure AD 服务器主体(登录名)优先于 Azure AD 管理员。Azure AD server principals (logins) take precedence over the Azure AD admin when you resolve the principal and apply permissions to SQL Managed Instance.

  • 在身份验证期间,将应用以下顺序来解析身份验证主体:During authentication, the following sequence is applied to resolve the authenticating principal:

    1. 如果 Azure AD 帐户存在并直接映射到 Azure AD 服务器主体(登录名)(以类型“E”的形式存在于 sys.server_principals 中),则授予访问权限并应用 Azure AD 服务器主体(登录名)的权限。If the Azure AD account exists as directly mapped to the Azure AD server principal (login), which is present in sys.server_principals as type "E," grant access and apply permissions of the Azure AD server principal (login).
    2. 如果 Azure AD 帐户是映射到 Azure AD 服务器主体(登录名)的 Azure AD 组的成员(以类型“X”的形式存在于 sys.server_principals 中),则授予访问权限并应用 Azure AD 组登录名的权限。If the Azure AD account is a member of an Azure AD group that's mapped to the Azure AD server principal (login), which is present in sys.server_principals as type "X," grant access and apply permissions of the Azure AD group login.
    3. 如果 Azure AD 帐户是在门户中配置的、SQL 托管实例的特殊 Azure AD 管理员(不存在于 SQL 托管实例系统视图中),则应用 SQL 托管实例的 Azure AD 管理员的特殊固定权限(传统模式)。If the Azure AD account is a special portal-configured Azure AD admin for SQL Managed Instance, which doesn't exist in SQL Managed Instance system views, apply special fixed permissions of the Azure AD admin for SQL Managed Instance (legacy mode).
    4. 如果 Azure AD 帐户存在并直接映射到数据库中的 Azure AD 用户(以类型“E”的形式存在于 sys.database_principals 中),则授予访问权限并应用 Azure AD 数据库用户的权限。If the Azure AD account exists as directly mapped to an Azure AD user in a database, which is present in sys.database_principals as type "E," grant access and apply permissions of the Azure AD database user.
    5. 如果 Azure AD 帐户是映射到数据库中 Azure AD 用户的 Azure AD 组的成员(以类型“X”的形式存在于 sys.database_principals 中),则授予访问权限并应用 Azure AD 组登录名的权限。If the Azure AD account is a member of an Azure AD group that's mapped to an Azure AD user in a database, which is present in sys.database_principals as type "X," grant access and apply permissions of the Azure AD group login.
    6. 如果某个 Azure AD 登录映射到 Azure AD 用户帐户或 Azure AD 组帐户并解析为用户身份验证,则应用此 Azure AD 登录名中的所有权限。If there's an Azure AD login mapped to either an Azure AD user account or an Azure AD group account, which resolves to the user who's authenticating, all permissions from this Azure AD login are applied.

服务密钥和服务主密钥Service key and service master key

配置Configuration

缓冲池扩展Buffer pool extension

排序规则Collation

默认实例排序规则为 SQL_Latin1_General_CP1_CI_AS 并可以被指定为创建参数。The default instance collation is SQL_Latin1_General_CP1_CI_AS and can be specified as a creation parameter. 请参阅排序规则See Collations.

兼容级别Compatibility levels

  • 支持的兼容级别:100、110、120、130、140 和 150。Supported compatibility levels are 100, 110, 120, 130, 140 and 150.
  • 不支持低于 100 的兼容级别。Compatibility levels below 100 aren't supported.
  • 新数据库的默认兼容级别为 140。The default compatibility level for new databases is 140. 对于已还原的数据库,如果其兼容级别在还原之前为 100 或更高,则还原后保持不变。For restored databases, the compatibility level remains unchanged if it was 100 and above.

请参阅 ALTER DATABASE 兼容级别See ALTER DATABASE Compatibility Level.

数据库镜像Database mirroring

不支持数据库镜像。Database mirroring isn't supported.

  • 不支持 ALTER DATABASE SET PARTNERSET WITNESS 选项。ALTER DATABASE SET PARTNER and SET WITNESS options aren't supported.
  • 不支持 CREATE ENDPOINT … FOR DATABASE_MIRRORINGCREATE ENDPOINT … FOR DATABASE_MIRRORING isn't supported.

有关详细信息,请参阅 ALTER DATABASE SET PARTNER 和 SET WITNESS 以及 CREATE ENDPOINT … FOR DATABASE_MIRRORINGFor more information, see ALTER DATABASE SET PARTNER and SET WITNESS and CREATE ENDPOINT … FOR DATABASE_MIRRORING.

数据库选项Database options

  • 不支持多个日志文件。Multiple log files aren't supported.
  • “常规用途”服务层级不支持内存中对象。In-memory objects aren't supported in the General Purpose service tier.
  • 每个“常规用途”实例限制为 280 个文件,这意味着,每个数据库最多只能有 280 个文件。There's a limit of 280 files per General Purpose instance, which implies a maximum of 280 files per database. “常规用途”层级中的数据文件和日志文件都会计入此限制。Both data and log files in the General Purpose tier are counted toward this limit. “业务关键”层级支持每个数据库 32,767 个文件The Business Critical tier supports 32,767 files per database.
  • 数据库中不能有包含文件流数据的文件组。The database can't contain filegroups that contain filestream data. 如果 .bak 包含 FILESTREAM 数据,还原将会失败。Restore fails if .bak contains FILESTREAM data.
  • 每个文件都被放置在 Azure Blob 存储中。Every file is placed in Azure Blob storage. 每个文件的 IO 和吞吐量取决于每个单独文件的大小。IO and throughput per file depend on the size of each individual file.

CREATE DATABASE 语句CREATE DATABASE statement

以下限制适用于 CREATE DATABASEThe following limitations apply to CREATE DATABASE:

  • 无法定义文件和文件组。Files and filegroups can't be defined.

  • 不支持 CONTAINMENT 选项。The CONTAINMENT option isn't supported.

  • 不支持 WITH 选项。WITH options aren't supported.

    提示

    解决方法是在 CREATE DATABASE 后面使用 ALTER DATABASE 来设置数据库选项,以添加文件或设置包含。As a workaround, use ALTER DATABASE after CREATE DATABASE to set database options to add files or to set containment.

  • 不支持 FOR ATTACH 选项。The FOR ATTACH option isn't supported.

  • 不支持 AS SNAPSHOT OF 选项。The AS SNAPSHOT OF option isn't supported.

有关详细信息,请参阅 CREATE DATABASEFor more information, see CREATE DATABASE.

ALTER DATABASE 语句ALTER DATABASE statement

无法设置或更改某些文件属性:Some file properties can't be set or changed:

  • 无法在 ALTER DATABASE ADD FILE (FILENAME='path') T-SQL 语句中指定文件路径。A file path can't be specified in the ALTER DATABASE ADD FILE (FILENAME='path') T-SQL statement. 请从脚本中删除 FILENAME,因为 SQL 托管实例自动放置文件。Remove FILENAME from the script because SQL Managed Instance automatically places the files.
  • 无法使用 ALTER DATABASE 语句更改文件名。A file name can't be changed by using the ALTER DATABASE statement.

默认会设置以下选项,无法更改这些选项:The following options are set by default and can't be changed:

  • MULTI_USER
  • ENABLE_BROKER ON
  • AUTO_CLOSE OFF

无法修改以下选项:The following options can't be modified:

  • AUTO_CLOSE
  • AUTOMATIC_TUNING(CREATE_INDEX=ON|OFF)
  • AUTOMATIC_TUNING(DROP_INDEX=ON|OFF)
  • DISABLE_BROKER
  • EMERGENCY
  • ENABLE_BROKER
  • FILESTREAM
  • HADR
  • NEW_BROKER
  • OFFLINE
  • PAGE_VERIFY
  • PARTNER
  • READ_ONLY
  • RECOVERY BULK_LOGGED
  • RECOVERY_SIMPLE
  • REMOTE_DATA_ARCHIVE
  • RESTRICTED_USER
  • SINGLE_USER
  • WITNESS

有关详细信息,请参阅 ALTER DATABASEFor more information, see ALTER DATABASE.

SQL Server 代理SQL Server Agent

  • 目前,SQL 托管实例不支持启用和禁用 SQL Server 代理。Enabling and disabling SQL Server Agent is currently not supported in SQL Managed Instance. SQL 代理始终运行。SQL Agent is always running.
  • SQL Server 代理设置为只读。SQL Server Agent settings are read only. SQL 托管实例不支持过程 sp_set_agent_propertiesThe procedure sp_set_agent_properties isn't supported in SQL Managed Instance.
  • 作业Jobs
    • 支持 T-SQL 作业步骤。T-SQL job steps are supported.
    • 支持以下复制作业:The following replication jobs are supported:
      • 事务日志读取器Transaction-log reader
      • 快照Snapshot
      • 分发服务器Distributor
    • 支持 SSIS 作业步骤。SSIS job steps are supported.
    • 目前不支持其他类型的作业步骤:Other types of job steps aren't currently supported:
      • 不支持合并复制作业步骤。The merge replication job step isn't supported.
      • 不支持队列读取器。Queue Reader isn't supported.
      • 尚不支持命令外壳。Command shell isn't yet supported.
    • SQL 托管实例无法访问外部资源(例如,通过 robocopy 访问网络共享)。SQL Managed Instance can't access external resources, for example, network shares via robocopy.
    • 不支持 SQL Server Analysis Services。SQL Server Analysis Services isn't supported.
  • 部分支持通知。Notifications are partially supported.
  • 支持电子邮件通知,不过需要配置数据库邮件配置文件。Email notification is supported, although it requires that you configure a Database Mail profile. SQL Server 代理只能使用一个数据库邮件配置文件,并且该配置文件必须命名为 AzureManagedInstance_dbmail_profileSQL Server Agent can use only one Database Mail profile, and it must be called AzureManagedInstance_dbmail_profile.
    • 不支持寻呼机。Pager isn't supported.
    • 不支持 NetSend。NetSend isn't supported.
    • 尚不支持警报。Alerts aren't yet supported.
    • 不支持代理。Proxies aren't supported.
  • 不支持 EventLog。EventLog isn't supported.

目前不支持以下 SQL 代理功能:The following SQL Agent features currently aren't supported:

  • 代理Proxies
  • 针对空闲 CPU 计划作业Scheduling jobs on an idle CPU
  • 启用或禁用代理Enabling or disabling an Agent
  • 警报Alerts

有关 SQL Server 代理的信息,请参阅 SQL Server 代理For information about SQL Server Agent, see SQL Server Agent.

Tables

不支持以下表类型:The following table types aren't supported:

若要了解如何创建和更改表,请参阅 CREATE TABLEALTER TABLEFor information about how to create and alter tables, see CREATE TABLE and ALTER TABLE.

功能Functionalities

BULK INSERT/OPENROWSETBulk insert / OPENROWSET

由于 SQL 托管实例无法访问文件共享和 Windows 文件夹,必须从 Azure Blob 存储导入文件:SQL Managed Instance can't access file shares and Windows folders, so the files must be imported from Azure Blob storage:

  • 从 Azure Blob 存储导入文件时,必须在 BULK INSERT 命令中指定 DATASOURCEDATASOURCE is required in the BULK INSERT command while you import files from Azure Blob storage. 请参阅 BULK INSERTSee BULK INSERT.
  • 从 Azure Blob 存储中读取文件内容时,必须在 OPENROWSET 函数中指定 DATASOURCEDATASOURCE is required in the OPENROWSET function when you read the content of a file from Azure Blob storage. 请参阅 OPENROWSETSee OPENROWSET.
  • OPENROWSET 可以用来从 Azure SQL 数据库、Azure SQL 托管实例或 SQL Server 实例读取数据。OPENROWSET can be used to read data from Azure SQL Database, Azure SQL Managed Instance, or SQL Server instances. 其他资源(例如 Oracle 数据库或 Excel 文件)不受支持。Other sources such as Oracle databases or Excel files are not supported.

CLRCLR

由于 SQL 托管实例无法访问文件共享和 Windows 文件夹,因此存在以下约束:A SQL Managed Instance can't access file shares and Windows folders, so the following constraints apply:

数据库邮件 (db_mail)Database Mail (db_mail)

  • sp_send_dbmail 无法使用 @file_attachments 参数发送附件。sp_send_dbmail cannot send attachments using @file_attachments parameter. 在此过程中无法访问本地文件系统和外部共享或 Azure Blob 存储。Local file system and external shares or Azure Blob Storage are not accessible from this procedure.
  • 请参阅与 @query 参数和身份验证相关的已知问题。See the known issues related to @query parameter and authentication.

DBCCDBCC

SQL 托管实例不支持 SQL Server 中启用的未记录 DBCC 语句。Undocumented DBCC statements that are enabled in SQL Server aren't supported in SQL Managed Instance.

  • 仅支持有限数量的全局跟踪标志。Only a limited number of Global Trace flags are supported. 不支持会话级 Trace flagsSession-level Trace flags aren't supported. 请参阅跟踪标志See Trace flags.
  • DBCC TRACEOFFDBCC TRACEON 使用有限数量的全局跟踪标志。DBCC TRACEOFF and DBCC TRACEON work with the limited number of global trace-flags.
  • 无法使用带有 REPAIR_ALLOW_DATA_LOSS、REPAIR_FAST 和 REPAIR_REBUILD 选项的 DBCC CHECKDB,因为无法在 SINGLE_USER 模式中设置数据库 - 请参阅 ALTER DATABASE 的差异DBCC CHECKDB with options REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, and REPAIR_REBUILD cannot be used because database cannot be set in SINGLE_USER mode - see ALTER DATABASE differences. 潜在的数据库损坏将由 Azure 支持团队负责处理。Potential database corruption is handled by the Azure support team. 如果发生数据库损坏,请联系 Azure 支持人员。Contact Azure support if there is any indication of database corruption.

分布式事务Distributed transactions

SQL 托管实例目前不支持 MSDTC 和弹性事务MSDTC and elastic transactions currently aren't supported in SQL Managed Instance.

扩展事件Extended Events

不支持对扩展事件 (XEvent) 使用某些特定于 Windows 的目标:Some Windows-specific targets for Extended Events (XEvents) aren't supported:

  • 不支持 etw_classic_sync 目标。The etw_classic_sync target isn't supported. 在 Azure Blob 存储中存储 .xel 文件。Store .xel files in Azure Blob storage. 请参阅 etw_classic_sync 目标See etw_classic_sync target.
  • 不支持 event_file 目标。The event_file target isn't supported. 在 Azure Blob 存储中存储 .xel 文件。Store .xel files in Azure Blob storage. 请参阅 event_file 目标See event_file target.

外部库External libraries

尚不支持数据库中的 R 和 Python 外部库。In-database R and Python, external libraries aren't yet supported. 请参阅 SQL Server 机器学习服务See SQL Server Machine Learning Services.

文件流和文件表Filestream and FileTable

  • 不支持文件流数据。Filestream data isn't supported.
  • 数据库中不能有包含 FILESTREAM 数据的文件组。The database can't contain filegroups with FILESTREAM data.
  • 不支持 FILETABLEFILETABLE isn't supported.
  • 表不能采用 FILESTREAM 类型。Tables can't have FILESTREAM types.
  • 不支持以下函数:The following functions aren't supported:
    • GetPathLocator()
    • GET_FILESTREAM_TRANSACTION_CONTEXT()
    • PathName()
    • GetFileNamespacePat)
    • FileTableRootPath()

有关详细信息,请参阅 FILESTREAM文件表For more information, see FILESTREAM and FileTables.

不支持语义搜索Semantic Search isn't supported.

链接的服务器Linked servers

SQL 托管实例中的链接服务器支持有限数量的目标:Linked servers in SQL Managed Instance support a limited number of targets:

  • 支持的目标有 SQL 托管实例、SQL 数据库、Azure Synapse SQL 和 SQL Server 实例。Supported targets are SQL Managed Instance, SQL Database, Azure Synapse SQL and SQL Server instances.
  • 链接服务器不支持分布式可写事务 (MS DTC)。Linked servers don't support distributed writable transactions (MS DTC).
  • 不支持的目标为文件、Analysis Services 和其他 RDBMS。Targets that aren't supported are files, Analysis Services, and other RDBMS. 请尝试使用从 Azure Blob 存储进行本机 CSV 导入(使用 BULK INSERTOPENROWSET)来代替文件导入操作。Try to use native CSV import from Azure Blob Storage using BULK INSERT or OPENROWSET as an alternative for file import.

操作:Operations:

  • 不支持跨实例写入事务。Cross-instance write transactions aren't supported.
  • 支持使用 sp_dropserver 删除链接服务器。sp_dropserver is supported for dropping a linked server. 请参阅 sp_dropserverSee sp_dropserver.
  • OPENROWSET 函数只能用于在 SQL Server 实例上执行查询。The OPENROWSET function can be used to execute queries only on SQL Server instances. 它们可以是托管的、位于本地或位于虚拟机中。They can be either managed, on-premises, or in virtual machines. 请参阅 OPENROWSETSee OPENROWSET.
  • OPENDATASOURCE 函数只能用于在 SQL Server 实例上执行查询。The OPENDATASOURCE function can be used to execute queries only on SQL Server instances. 它们可以是托管的、位于本地或位于虚拟机中。They can be either managed, on-premises, or in virtual machines. 仅支持将 SQLNCLISQLNCLI11SQLOLEDB 值用作提供程序。Only the SQLNCLI, SQLNCLI11, and SQLOLEDB values are supported as a provider. 例如 SELECT * FROM OPENDATASOURCE('SQLNCLI', '...').AdventureWorks2012.HumanResources.EmployeeAn example is SELECT * FROM OPENDATASOURCE('SQLNCLI', '...').AdventureWorks2012.HumanResources.Employee. 请参阅 OPENDATASOURCESee OPENDATASOURCE.
  • 不能使用链接服务器从网络共享读取文件(Excel、CSV)。Linked servers cannot be used to read files (Excel, CSV) from the network shares. 请尝试使用从 Azure Blob 存储读取 CSV 文件的 BULK INSERTOPENROWSETTry to use BULK INSERT or OPENROWSET that reads CSV files from Azure Blob Storage.

PolyBasePolyBase

不支持引用 HDFS 或 Azure Blob 存储中文件的外部表。External tables that reference the files in HDFS or Azure Blob storage aren't supported. 有关 PolyBase 的信息,请参阅 PolyBaseFor information about PolyBase, see PolyBase.

复制Replication

  • 支持快照和双向复制类型。Snapshot and Bi-directional replication types are supported. 不支持合并复制、对等复制和可更新订阅。Merge replication, Peer-to-peer replication, and updatable subscriptions are not supported.
  • 事务复制在 SQL 托管实例上为公共预览版,但存在一些约束:Transactional Replication is available for public preview on SQL Managed Instance with some constraints:
    • 所有类型的复制参与者(发布服务器、分发服务器、拉取订阅服务器和推送订阅服务器)都可以放置在 SQL 托管实例上,但发布服务器和分发服务器必须同时在云中或同时在本地。All types of replication participants (Publisher, Distributor, Pull Subscriber, and Push Subscriber) can be placed on SQL Managed Instance, but the publisher and the distributor must be either both in the cloud or both on-premises.
    • SQL 托管实例可以与最新版 SQL Server 通信。SQL Managed Instance can communicate with the recent versions of SQL Server. 有关详细信息,请参阅支持的版本矩阵See the supported versions matrix for more information.
    • 事务复制存在一些其他的网络要求Transactional Replication has some additional networking requirements.

有关配置事务复制的详细信息,请参阅以下教程:For more information about configuring transactional replication, see the following tutorials:

RESTORE 语句RESTORE statement

  • 支持的语法:Supported syntax:
    • RESTORE DATABASE
    • RESTORE FILELISTONLY ONLY
    • RESTORE HEADER ONLY
    • RESTORE LABELONLY ONLY
    • RESTORE VERIFYONLY ONLY
  • 不支持的语法:Unsupported syntax:
    • RESTORE LOG ONLY
    • RESTORE REWINDONLY ONLY
  • 来源:Source:
    • FROM URL(Azure Blob 存储)是唯一受支持的选项。FROM URL (Azure Blob storage) is the only supported option.
    • 不支持 FROM DISK/TAPE/备份设备。FROM DISK/TAPE/backup device isn't supported.
    • 不支持备份集。Backup sets aren't supported.
  • 不支持 WITH 选项。WITH options aren't supported. WITH(如 DIFFERENTIALSTATSREPLACE 等)等还原尝试将失败。Restore attempts including WITH like DIFFERENTIAL, STATS, REPLACE, etc., will fail.
  • ASYNC RESTORE:即使客户端连接断开,还原也会继续。ASYNC RESTORE: Restore continues even if the client connection breaks. 如果删除了连接,可以在 sys.dm_operation_status 视图中检查还原操作的状态,以及 CREATE DATABASE 和 DROP DATABASE 的状态。If your connection is dropped, you can check the sys.dm_operation_status view for the status of a restore operation, and for a CREATE and DROP database. 请参阅 sys.dm_operation_statusSee sys.dm_operation_status.

将设置或重写以下数据库选项,以后无法更改:The following database options are set or overridden and can't be changed later:

  • NEW_BROKER(如果未在 .bak 文件中启用代理)。NEW_BROKER if the broker isn't enabled in the .bak file.
  • ENABLE_BROKER(如果未在 .bak 文件中启用代理)。ENABLE_BROKER if the broker isn't enabled in the .bak file.
  • AUTO_CLOSE=OFF(如果 .bak 文件中的数据库采用 AUTO_CLOSE=ON)。AUTO_CLOSE=OFF if a database in the .bak file has AUTO_CLOSE=ON.
  • RECOVERY FULL(如果 .bak 文件中的数据库采用 SIMPLEBULK_LOGGED 恢复模式)。RECOVERY FULL if a database in the .bak file has SIMPLE or BULK_LOGGED recovery mode.
  • 添加源 .bak 文件中不包含内存优化文件组,则会添加名为 XTP 的内存优化文件组。A memory-optimized filegroup is added and called XTP if it wasn't in the source .bak file.
  • 任何现有的内存优化文件组将重命名为 XTP。Any existing memory-optimized filegroup is renamed to XTP.
  • SINGLE_USERRESTRICTED_USER 选项将转换为 MULTI_USERSINGLE_USER and RESTRICTED_USER options are converted to MULTI_USER.

的限制:Limitations:

  • 根据损坏类型,有时可以还原已损坏的数据库的备份,但在修复损坏之前,不会创建自动备份。Backups of the corrupted databases might be restored depending on the type of the corruption, but automated backups will not be taken until the corruption is fixed. 确保在源 SQL 托管实例上运行 DBCC CHECKDB,并使用备份 WITH CHECKSUM 来避免此问题。Make sure that you run DBCC CHECKDB on the source SQL Managed Instance and use backup WITH CHECKSUM in order to prevent this issue.
  • 无法在 SQL 托管实例上还原包含本文档所述的任何限制的数据库的 .BAK 文件(例如 FILESTREAMFILETABLE 对象)。Restore of .BAK file of a database that contains any limitation described in this document (for example, FILESTREAM or FILETABLE objects) cannot be restored on SQL Managed Instance.
  • 无法还原包含多个备份集的 .BAK 文件。.BAK files that contain multiple backup sets can't be restored.
  • 无法还原包含多个日志文件的 .BAK 文件。.BAK files that contain multiple log files can't be restored.
  • 在“常规用途”实例上,无法还原包含 8 TB 以上的数据库、活动的内存中 OLTP 对象或每个实例有 280 个以上的文件的备份。Backups that contain databases bigger than 8 TB, active in-memory OLTP objects, or number of files that would exceed 280 files per instance can't be restored on a General Purpose instance.
  • 在“业务关键”实例上,无法还原包含 4 TB 以上的数据库或内存中 OLTP 对象,且总大小超过资源限制中所述大小的备份。Backups that contain databases bigger than 4 TB or in-memory OLTP objects with the total size larger than the size described in resource limits cannot be restored on Business Critical instance. 有关 restore 语句的信息,请参阅 RESTORE 语句For information about restore statements, see RESTORE statements.

重要

这些限制同样适用于内置的时间点还原操作。The same limitations apply to built-in point-in-time restore operation. 例如,在“业务关键”实例上,无法还原大于 4 TB 的“常规用途”数据库。As an example, General Purpose database greater than 4 TB cannot be restored on Business Critical instance. 在“常规用途”实例上,无法还原包含内存中 OLTP 文件或 280 个以上的文件的“业务关键”数据库。Business Critical database with In-memory OLTP files or more than 280 files cannot be restored on General Purpose instance.

服务代理Service broker

不支持跨实例 Service Broker:Cross-instance service broker isn't supported:

  • sys.routes:作为先决条件,必须从 sys.routes 中选择地址。sys.routes: As a prerequisite, you must select the address from sys.routes. 该地址必须在每个路由的本地。The address must be LOCAL on every route. 请参阅 sys.routesSee sys.routes.
  • CREATE ROUTE:不能将 CREATE ROUTE 与除 LOCAL 以外的 ADDRESS 配合使用。CREATE ROUTE: You can't use CREATE ROUTE with ADDRESS other than LOCAL. 请参阅 CREATE ROUTESee CREATE ROUTE.
  • ALTER ROUTE:不能将 ALTER ROUTE 与除 LOCAL 以外的 ADDRESS 配合使用。ALTER ROUTE: You can't use ALTER ROUTE with ADDRESS other than LOCAL. 请参阅 ALTER ROUTESee ALTER ROUTE.

存储过程、函数和触发器Stored procedures, functions, and triggers

系统函数和变量System functions and variables

以下变量、函数和视图返回不同的结果:The following variables, functions, and views return different results:

  • SERVERPROPERTY('EngineEdition') 返回值 8。SERVERPROPERTY('EngineEdition') returns the value 8. 此属性唯一标识 SQL 托管实例。This property uniquely identifies a SQL Managed Instance. 请参阅 SERVERPROPERTYSee SERVERPROPERTY.
  • SERVERPROPERTY('InstanceName') 返回 NULL,因为 SQL Server 存在的实例概念并不适用于 SQL 托管实例。SERVERPROPERTY('InstanceName') returns NULL because the concept of instance as it exists for SQL Server doesn't apply to SQL Managed Instance. 请参阅 SERVERPROPERTY('InstanceName')See SERVERPROPERTY('InstanceName').
  • @@SERVERNAME 返回完整的 DNS“可连接”名称,例如 my-managed-instance.wcus17662feb9ce98.database.chinacloudapi.cn。@@SERVERNAME returns a full DNS "connectable" name, for example, my-managed-instance.wcus17662feb9ce98.database.chinacloudapi.cn. 请参阅 @@SERVERNAMESee @@SERVERNAME.
  • SYS.SERVERS 返回完整的 DNS“可连接”名称,例如,为属性“name”和“data_source”返回 myinstance.domain.database.chinacloudapi.cnSYS.SERVERS returns a full DNS "connectable" name, such as myinstance.domain.database.chinacloudapi.cn for the properties "name" and "data_source." 请参阅 SYS.SERVERSSee SYS.SERVERS.
  • @@SERVICENAME 返回 NULL,因为 SQL Server 存在的服务概念并不适用于 SQL 托管实例。@@SERVICENAME returns NULL because the concept of service as it exists for SQL Server doesn't apply to SQL Managed Instance. 请参阅 @@SERVICENAMESee @@SERVICENAME.
  • 支持 SUSER_IDSUSER_ID is supported. 如果 Azure AD 登录名不在 sys.syslogins 中,则返回 NULL。It returns NULL if the Azure AD login isn't in sys.syslogins. 请参阅 SUSER_IDSee SUSER_ID.
  • 不支持 SUSER_SIDSUSER_SID isn't supported. 将返回错误数据,这是暂时性的已知问题。The wrong data is returned, which is a temporary known issue. 请参阅 SUSER_SIDSee SUSER_SID.

环境约束Environment constraints

子网Subnet

VNETVNET

  • VNet 可以使用资源模型进行部署 - 不支持适用于 VNet 的经典模型。VNet can be deployed using Resource Model - Classic Model for VNet is not supported.
  • 创建 SQL 托管实例后,不支持将 SQL 托管实例或 VNet 移到另一个资源组或订阅。After a SQL Managed Instance is created, moving the SQL Managed Instance or VNet to another resource group or subscription is not supported.
  • 应用服务环境、逻辑应用和 SQL 托管实例之类的某些服务(用于异地复制、事务复制,或者通过链接服务器来使用)在其 VNet 是通过全局对等互连进行连接的情况下不能访问不同区域中的 SQL 托管实例。Some services such as App Service Environments, Logic apps, and SQL Managed Instance (used for Geo-replication, Transactional replication, or via linked servers) cannot access SQL Managed Instance in different regions if their VNets are connected using global peering. 可以通过 VNet 网关经由 ExpressRoute 或 VNet-to-VNet 连接到这些资源。You can connect to these resources via ExpressRoute or VNet-to-VNet through VNet Gateways.

故障转移组Failover groups

系统数据库不会复制到故障转移组中的辅助实例。System databases are not replicated to the secondary instance in a failover group. 因此,除非在辅助实例上手动创建系统数据库中的对象,否则依赖于该对象的方案将不可能在辅助实例上出现。Therefore, scenarios that depend on objects from the system databases will be impossible on the secondary instance unless the objects are manually created on the secondary.

故障转移组Failover groups

系统数据库不会复制到故障转移组中的辅助实例。System databases are not replicated to the secondary instance in a failover group. 因此,除非在辅助实例上手动创建系统数据库中的对象,否则依赖于该对象的方案将不可能在辅助实例上出现。Therefore, scenarios that depend on objects from the system databases will be impossible on the secondary instance unless the objects are manually created on the secondary.

TEMPDBTEMPDB

在“常规用途”层级上,tempdb 的最大文件大小不能超过 24 GB 每核心。The maximum file size of tempdb can't be greater than 24 GB per core on a General Purpose tier. 在“业务关键”层级上,最大 tempdb 大小根据 SQL 托管实例存储大小受到限制。The maximum tempdb size on a Business Critical tier is limited by the SQL Managed Instance storage size. 在“常规用途”层级上,Tempdb 日志文件大小限制为 120 GB。Tempdb log file size is limited to 120 GB on General Purpose tier. 如果某些查询需要在 tempdb 中为每个核心提供 24 GB 以上的空间,或者生成 120 GB 以上的日志数据,则这些查询可能会返回错误。Some queries might return an error if they need more than 24 GB per core in tempdb or if they produce more than 120 GB of log data.

MSDBMSDB

SQL 托管实例中的以下 MSDB 架构必须由其相应的预定义角色拥有:The following MSDB schemas in SQL Managed Instance must be owned by their respective predefined roles:

重要

客户更改预定义的角色名称、架构名称和架构所有者将会影响服务的正常运行。Changing the predefined role names, schema names and schema owners by customers will impact the normal operation of the service. 如果对这些属性进行任何更改,在检测到此类更改后会立即将其还原到预定义值,或者最迟在下次更新服务时还原,以确保服务正常运行。Any changes made to these will be reverted back to the predefined values as soon as detected, or at the next service update at the latest to ensure normal service operation.

错误日志Error logs

SQL 托管实例将详细信息放在错误日志中。SQL Managed Instance places verbose information in error logs. 有很多内部系统事件记录在错误日志中。There are many internal system events that are logged in the error log. 使用自定义过程读取已筛选出某些不相关条目的错误日志。Use a custom procedure to read error logs that filters out some irrelevant entries. 有关详细信息,请参阅 SQL 托管实例 - sp_readmierrorlog 或用于 Azure Data Studio 的 SQL 托管实例扩展(预览版)For more information, see SQL Managed Instance – sp_readmierrorlog or SQL Managed Instance extension(preview) for Azure Data Studio.

后续步骤Next steps