Microsoft Azure 中的 SQL Server 資料檔案SQL Server data files in Microsoft Azure

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Azure 上的資料檔案Data files on Azure

Microsoft Azure 中的 SQL Server 資料檔案可提供將 SQL Server 資料庫檔案儲存為 Microsoft Azure Blob 的原生支援。SQL Server Data Files in Microsoft Azure enables native support for SQL Server database files stored as Microsoft Azure Blobs. 它可讓您在內部部署執行的 SQL Server 或 Microsoft Azure 虛擬機器執行的 SQL Server 中建立資料庫,以將您的 Microsoft Azure Blob 儲存體資料儲存在專用儲存位置。It allows you to create a database in SQL Server running in on-premises or in a virtual machine in Microsoft Azure with a dedicated storage location for your data in Microsoft Azure Blob Storage. 此增強功能特別簡化了使用卸離和附加作業,在電腦之間移動資料庫的工作。This enhancement especially simplifies to move databases between machines by using detach and attach operations. 此外,它可讓您還原至 Microsoft Azure 儲存體或從中還原,為您的資料庫備份檔案提供替代儲存位置。In addition, it provides an alternative storage location for your database backup files by allowing you to restore from or to Microsoft Azure Storage. 因此,它會針對資料虛擬化、資料移動、安全性和可用性提供許多優點,進而實現許多混合式方案,而且成本低廉、維護簡單,即可達到高可用性和彈性調整的效果。Therefore, it enables several hybrid solutions by providing several benefits for data virtualization, data movement, security and availability, and any easy low costs and maintenance for high-availability and elastic scaling.

重要

不建議且不支援將系統資料庫儲存在 Azure Blob 儲存體中。Storing system databases in Azure blob storage is not recommended and is not supported.

本主題介紹將 SQL Server 資料檔案儲存在 Microsoft Azure 儲存體服務中的核心概念與考量。This topic introduces concepts and considerations that are central to storing SQL Server data files in Microsoft Azure Storage Service.

如需如何使用此新功能的實際操作體驗,請參閱教學課程:搭配 SQL Server 2016 資料庫使用 Microsoft Azure Blob 儲存體服務For a practical hands-on experience on how to use this new feature, see Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases.

為何要在 Microsoft Azure 中使用 SQL Server 資料檔案?Why use SQL Server data files in Microsoft Azure?

  • 簡單快速移轉優點: 這項功能會在內部部署的電腦之間以及內部部署與雲端環境之間一次移動一個資料庫來藉以簡化移轉程序,且不需要變更任何應用程式。Easy and fast migration benefits: This feature simplifies the migration process by moving one database at a time between machines in on-premises as well as between on-premises and cloud environments without any application changes. 因此,它支援累加式移轉,同時就地維護您現有的內部部署基礎結構。Therefore, it supports an incremental migration while maintaining your existing on-premises infrastructure in place. 此外,當應用程式需要在內部部署環境中的多個位置執行時,存取集中式資料儲存體可簡化應用程式邏輯。In addition, having access to a centralized data storage simplifies the application logic when an application needs to run in multiple locations in an on-premises environment. 在某些情況下,您可能需要快速地設定散佈於不同地理位置的電腦中心,以便蒐集許多不同來源的資料。In some cases, you may need to rapidly setup computer centers in geographically dispersed locations, which gather data from many different sources. 只要使用這項新的增強功能,您就可以將許多資料庫儲存成 Microsoft Azure Blob,然後執行 Transact-SQL 指令碼,在本機電腦或虛擬機器上建立資料庫,而不需要在不同的位置之間移動資料。By using this new enhancement, instead of moving data from one location to another, you can store many databases as Microsoft Azure blobs, and then run Transact-SQL scripts to create databases on the local machines or virtual machines.

  • 成本和無限制儲存體優點: 這項功能可讓您在 Microsoft Azure 中擁有無限制的異地儲存體,同時運用內部部署計算資源。Cost and limitless storage benefits: This feature enables you to have limitless off-site storage in Microsoft Azure while leveraging on-premises compute resources. 當您將 Microsoft Azure 當做儲存位置使用時,可以輕鬆地將重點放在應用程式邏輯上,而不會產生硬體管理的負擔。When you use Microsoft Azure as a storage location, you can easily focus on the application logic without the overhead of hardware management. 如果您遺失了某個內部部署的計算節點,不需要移動任何資料,就可以設定新的節點。If you lose a computation node on-premises, you can set up a new one without any data movement.

  • 高可用性和災害復原優點: 使用 Microsoft Azure 功能中的 SQL Server 資料檔案可簡化高可用性和災害復原解決方案。High availability and disaster recovery benefits: Using SQL Server Data Files in Microsoft Azure feature might simplify the high availability and disaster recovery solutions. 例如,假設某個 Microsoft Azure 虛擬機器或 SQL Server 執行個體當機,您只要重新建立 Microsoft Azure Blob 的連結,就可以在新的 SQL Server 執行個體中重建資料庫。For example, if a virtual machine in Microsoft Azure or an instance of SQL Server crashes, you can re-create your databases in a new SQL Server instance by just re-establishing links to Microsoft Azure Blobs.

  • 安全性優點: 這項新的增強功能可讓您分隔計算執行個體與儲存執行個體。Security benefits: This new enhancement allows you to separate a compute instance from a storage instance. 您可以擁有完整加密的資料庫,而且只針對計算執行個體進行解密,但不在儲存執行個體中進行解密。You can have a fully encrypted database with decryption only occurring on compute instance but not in a storage instance. 換言之,使用這項新的增強功能時,您可以使用透明資料加密 (TDE) 憑證 (與資料實體分隔) 來加密公用雲端中的所有資料。In other words, using this new enhancement, you can encrypt all data in public cloud using Transparent Data Encryption (TDE) certificates, which are physically separated from the data. TDE 金鑰可以儲存在 master 資料庫中,而這個資料庫會儲存在實體安全的內部部署電腦本機並且進行本機備份。The TDE keys can be stored in the master database, which is stored locally in your physically secure on-premises computer and backed up locally. 您可以使用這些本機金鑰來加密位於 Microsoft Azure 儲存體中的資料。You can use these local keys to encrypt the data, which resides in Microsoft Azure Storage. 如果您的雲端儲存體帳戶認證遭竊,您的資料仍然保持安全,因為 TDE 憑證永遠位於內部部署。If your cloud storage account credentials are stolen, your data still stays secure as the TDE certificates always reside in on-premises.

  • 快照集備份: 這項功能可讓您使用 Azure 快照集,針對使用 Azure Blob 儲存體服務儲存的資料庫檔案,提供近乎即時備份及更快速的還原。Snapshot backup: This feature enables you to use Azure snapshots to provide nearly instantaneous backups and quicker restores for database files stored using the Azure Blob storage service. 這個功能可讓您簡化備份和還原原則。This capability enables you to simplify your backup and restore policies. 如需詳細資訊,請參閱 Azure 中資料庫檔案的檔案快照集備份For more information, see File-Snapshot Backups for Database Files in Azure.

概念和需求Concepts and Requirements

Azure 儲存體概念Azure Storage Concepts

使用 Azure 功能中的 SQL Server 資料檔案時,您必須在 Azure 中建立儲存體帳戶和容器。When using SQL Server Data Files in Azure feature, you need to create a storage account and a container in Azure. 然後,您必須建立 SQL Server 認證,其中包括容器原則的相關資訊以及存取容器所需的共用存取簽章。Then, you need to create a SQL Server credential, which includes information on the policy of the container as well as a shared access signature that is necessary to access the container.

Microsoft Azure中, Azure 儲存體 帳戶代表存取 Blob 之命名空間的最高層級。In Microsoft Azure, an Azure storage account represents the highest level of the namespace for accessing Blobs. 儲存體帳戶可以包含不限制數目的容器,只要其總大小低於儲存體限制即可。A storage account can contain an unlimited number of containers, as long as their total size is below the storage limits. 如需有關儲存體限制的最新資訊,請參閱 Azure 訂閱與服務限制、配額及條件約束For the latest information on storage limits, see Azure Subscription and Service Limits, Quotas, and Constraints. 容器會提供一組 Blob的群組。A container provides a grouping of a set of Blobs. 所有 Blob 都必須位於容器中。All Blobs must be in a container. 帳戶可以包含不限制數目的容器。An account can contain an unlimited number of containers. 同樣地,容器也可以儲存不限制數目的 Blob。Similarly, a container can store an unlimited number of Blobs as well. Azure 儲存體可以儲存的 Blob 類型有兩種:區塊和分頁 Blob。There are two types of blobs that can be stored in Azure Storage: block and page blobs. 這項新功能使用的是分頁 Blob,而且當檔案中的位元組範圍經常修改時,更有效率。This new feature uses Page blobs, which are more efficient when ranges of bytes in a file are modified frequently. 您可以使用以下 URL 格式存取 Blob: https://storageaccount.blob.core.windows.net/<container>/<blob>You can access Blobs using the following URL format: https://storageaccount.blob.core.windows.net/<container>/<blob>.

Azure 計費考量Azure billing considerations

在決策和規劃程序中,估計使用 Azure 服務的成本是很重要的事項。Estimating the cost of using Azure Services is an important matter in the decision making and planning process. 將 SQL Server 資料檔案儲存在 Azure 儲存體中時,需要支付與儲存體和交易相關聯的成本。When storing SQL Server data files in Azure Storage, you need to pay costs associated with storage and transactions. 此外,實作 Azure 儲存體功能中的 SQL Server 資料檔案時,還需要每隔 45 至 60 秒,隱含地更新 Blob 租用一次。In addition, the implementation of SQL Server Data Files in Azure Storage feature requires a renewal of Blob lease every 45 to 60 seconds implicitly. 這也會產生每個資料庫檔案 (例如 .mdf 或 .ldf) 的交易成本。This also results in transaction costs per database file, such as .mdf or .ldf. 使用 Azure 價格頁面上的資訊來協助估計與使用 Azure 儲存體和 Azure 虛擬機器的每月相關成本。Use the information on the Azure Pricing page to help estimate the monthly costs associated with the use of Azure Storage and Azure Virtual Machines.

SQL 伺服器概念SQL Server concepts

使用這項新的增強功能時,您必須執行下列動作:When using this new enhancement, you are required to do the followings:

  • 您必須在容器上建立原則,同時產生共用存取簽章 (SAS) 金鑰。You must create a policy on a container and also generate a shared access signature (SAS) key.

  • 對於資料或記錄檔所使用的每一個容器,您都必須建立名稱符合容器路徑的 SQL Server 認證。For each container used by a data or a log file, you must create a SQL Server Credential whose name matches the container path.

  • 您必須將 Azure 儲存體容器的相關資訊、其相關聯的原則名稱以及 SAS 金鑰,儲存在 SQL Server 認證存放區中。You must store the information regarding Azure Storage container, its associated policy name, and SAS key in the SQL Server credential store.

下列範例會假設已經建立 Azure 儲存體容器,而且已經建立具有讀取、寫入和列出權限的原則。The following example assumes that an Azure storage container has been created, and a policy has been created with read, write, list, rights. 在容器上建立原則會產生 SAS 金鑰,這個金鑰會以未加密狀態安全地保存在記憶體中,而且 SQL Server 需要此金鑰才能存取容器中的 Blob 檔案。Creating a policy on a container generates a SAS key which is safe to keep unencrypted in memory and needed by SQL Server to access the blob files in the container. 在下列程式碼片段中,使用與下列類似的項目取代 '<your SAS key>''sr=c&si=<MYPOLICYNAME>&sig=<THESHAREDACCESSSIGNATURE>'In the following code snippet, replace '<your SAS key>' with an entry similar to the following: 'sr=c&si=<MYPOLICYNAME>&sig=<THESHAREDACCESSSIGNATURE>'. 如需詳細資訊,請參閱 管理 Azure 儲存體資源的存取For more information, see Manage Access to Azure Storage Resources

CREATE CREDENTIAL [https://testdb.blob.core.windows.net/data]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = '<your SAS key>'  
  
CREATE DATABASE testdb   
ON  
( NAME = testdb_dat,  
    FILENAME = 'https://testdb.blob.core.windows.net/data/TestData.mdf' )  
 LOG ON  
( NAME = testdb_log,  
    FILENAME =  'https://testdb.blob.core.windows.net/data/TestLog.ldf')  

重要事項: 如果有任何作用中參考指向容器中的資料檔案,則嘗試刪除相對應的 SQL Server 認證會失敗。Important note: If there are any active references to data files in a container, attempts to delete the corresponding SQL Server credential fails.

SecuritySecurity

以下是將 SQL Server 資料檔案儲存在 Azure 儲存體中的安全性考量和需求。The following are security considerations and requirements when storing SQL Server Data Files in Azure Storage.

  • 建立 Azure Blob 儲存體服務的容器時,建議您將存取設為私用。When creating a container for the Azure Blob storage service, we recommend that you set the access to private. 當您將存取設為私用時,只有 Azure 帳戶擁有者才能讀取容器和 Blob 資料。When you set the access to private, container and blob data can be read by the Azure account owner only.

  • 在 Azure 儲存體中儲存 SQL Server 資料庫檔案時,您必須使用共用存取簽章,這是針對容器、Blob、佇列和資料表授與有限存取權限的 URI。When storing SQL Server database files in Azure Storage, you need to use a shared access signature, a URI that grants restricted access rights to containers, blobs, queues, and tables. 您可以使用共用存取簽章,讓 SQL Server 存取儲存體帳戶中的資源,而不需要共用您的 Azure 儲存體帳戶金鑰。By using a shared access signature, you can enable SQL Server to access resources in your storage account without sharing your Azure storage account key.

  • 此外,我們建議您繼續針對資料庫實作傳統的內部部署安全性作法。In addition, we recommend that you continue implementing the traditional on-premises security practices for your databases.

安裝必要條件Installation prerequisites

以下是將 SQL Server 資料檔案儲存在 Azure 中的安裝必要條件。The followings are installation prerequisites when storing SQL Server Data Files in Azure.

  • SQL Server 內部部署: SQL Server 2016 及更新版本包含這項功能。SQL Server on-premises: SQL Server 2016 and later include this feature. 若要了解如何下載最新版的 SQL Server,請參閱 SQL ServerTo learn how to download the latest version of SQL Server, see SQL Server.

  • 在 Azure 虛擬機器中執行的 SQL Server:如果您要將 SQL Server 安裝在 Azure 虛擬機器上,請安裝 SQL Server 2016,或更新現有的執行個體。SQL Server running in an Azure virtual machine: If you are installing SQL Server on an Azure Virtual Machine, install SQL Server 2016, or update your existing instance. 同樣地,您也可以使用 SQL Server 2016 平台映像,在 Azure 中建立新的虛擬機器。Similarly, you can also create a new virtual machine in Azure using SQL Server 2016 platform image.

限制Limitations

  • 在目前版本的功能中,不支援將 FileStream 資料儲存在 Azure 儲存體中。In the current release of this feature, storing FileStream data in Azure Storage is not supported. 您可以在同時包含 Azure 儲存體所儲存資料檔案的資料庫中儲存 FileStream 資料,但所有的 FileStream 資料檔案都必須儲存在本機儲存體上。You can store FileStream data in a database that also contains data files stored in Azure Storage, but all FileStream data files must be stored on local storage. 由於 FileStream 資料必須位於本機儲存體上,所以它無法在使用 Azure 儲存體的機器之間移動,因此建議您繼續使用傳統技術,在不同電腦之間移動與 FileStream 建立關聯的資料。Since the FileStream data must reside on local storage, it cannot be moved between machines using Azure Storage, therefore we recommend that you continue using the traditional techniques to move the data associated with FileStream between different machines.

  • 目前,這項新的增強功能不支援多個 SQL Server 執行個體同時存取 Azure 儲存體中的相同資料庫檔案。Currently, this new enhancement does not support more than one SQL Server instance accessing the same database files in Azure Storage at the same time. 如果 ServerA 在線上且具有作用中的資料庫檔案,而 ServerB 意外啟動,而且也有指向相同資料檔案的資料庫,則第二部伺服器將無法啟動資料庫,錯誤碼為 5120 無法開啟實體檔案 "%.*ls"。作業系統錯誤 %d: "%ls"If ServerA is online with an active database file and if ServerB is accidently started, and it also has a database which points to the same data file, the second server will fail to start the database with an error code 5120 Unable to open the physical file "%.*ls". Operating system error %d: "%ls".

  • 透過使用 Azure 功能中的 SQL Server 資料檔案,僅能將 .mdf、.ldf 和 .ndf 檔案儲存在 Azure 儲存體中。Only .mdf, .ldf, and .ndf files can be stored in Azure Storage by using the SQL Server Data Files in Azure feature.

  • 使用 Azure 功能中的 SQL Server 資料檔案時,您的儲存體帳戶不支援異地備援。When using the SQL Server Data Files in Azure feature, geo-replication for your storage account is not supported. 如果儲存體帳戶進行異地備援,然後進行異地容錯移轉,可能會發生資料庫損毀。If a storage account is geo-replicated and a geo-failover happened, database corruption could occur.

  • 如需容量限制,請參閱 Blob 儲存體簡介For capacity limitations, see Introduction to Blob storage.

  • 您無法使用 Azure 儲存體功能中的 SQL Server 資料檔案,將記憶體內部 OLTP 資料儲存在 Azure Blob 中。It is not possible to store In-Memory OLTP data in Azure Blob using the SQL Server Data Files in Azure Storage feature. 這是因為記憶體內部 OLTP 相依於 FileStream ,而在目前版本的功能中,不支援將 FileStream 資料儲存在 Azure 儲存體中。This is because In-Memory OLTP has a dependency on FileStream and, in the current release of this feature, storing FileStream data in Azure Storage is not supported.

  • 使用 Azure 功能中的 SQL Server 資料檔案時,SQL Server 會使用 master 資料庫中設定的定序執行所有 URL 或檔案路徑比較。When using SQL Server Data Files in Azure feature, SQL Server performs all URL or file path comparisons using the Collation set in the master database.

  • 只要您不要將新的資料庫檔案新增至主要資料庫,就支援 AlwaysOn 可用性群組Always On availability groups are supported as long as you do not add new database files to the primary database. 如果資料庫作業需要在主要資料庫中建立新的檔案,請先在次要節點中停用 AlwaysOn 可用性群組。If a database operation requires a new file to be created in the primary database, first disable Always On availability groups in the secondary node. 然後,在主要資料庫上執行資料庫作業,並且備份主要節點中的資料庫。Then, perform the database operation on the primary database and backup the database in the primary node. 接著,將資料庫還原到次要節點,並且在次要節點中啟用 AlwaysOn 可用性群組。Next, restore the database to the secondary node, and enable Always On availability groups in the secondary node. 請注意,使用 Azure 功能中的 SQL Server 資料檔案時,不支援 AlwaysOn 容錯移轉叢集執行個體。Note that Always On failover cluster instances is not supported when using the SQL Server data files in Azure feature.

  • 一般作業期間,SQL Server 會使用暫時租用來保留 Blob 進行儲存,而且每隔 45 至 60 秒就會更新每個 Blob 租用。During normal operation, SQL Server uses temporary leases to reserve Blobs for storage with a renewal of each Blob lease every 45 to 60 seconds. 如果伺服器當機,而且設定為使用相同 Blob 的另一個 SQL Server 執行個體啟動,新的執行個體最多會等候 60 秒,讓現有的 Blob 租用過期。If a server crashes and another instance of SQL Server configured to use the same blobs is started, the new instance will wait up to 60 seconds for the existing lease on the Blob to expire. 如果您想要將資料庫附加至另一個執行個體,而且您無法等候租用在 60 秒內過期,可以明確中斷 Blob 租用,避免附加作業發生任何失敗。If you want to attach the database to another instance and you cannot wait for the lease to expire within 60 seconds, you can explicitly break the lease on the Blob to avoid any failures in attach operations.

工具和程式設計參考支援Tools and programming reference support

本節說明將 SQL Server 資料檔案儲存在 Azure 儲存體中時,可以使用哪些工具和程式設計參考程式庫。This section describes which tools and programming reference libraries can be used when storing SQL Server data files in Azure Storage.

PowerShell 支援PowerShell support

您可以使用 PowerShell Cmdlet,藉由參考 Blob 儲存體 URL 路徑而非檔案路徑,將 SQL Server 資料檔案儲存在 Azure Blob 儲存體服務中。Use PowerShell cmdlets to store SQL Server data files in Azure Blob Storage service by referencing a Blob Storage URL path instead of a file path. 請使用下列 URL 格式來存取 Blob:https://storageaccount.blob.core.windows.net/<container>/<blob>Access Blobs using the following URL format: https://storageaccount.blob.core.windows.net/<container>/<blob> .

SQL Server 物件和效能計數器支援SQL Server object and performance counters support

從 SQL Server 2014 開始,已加入新的 SQL Server 物件,以用於 Azure 儲存體功能中的 SQL Server 資料檔案。Starting with SQL Server 2014, a new SQL Server object has been added to be used with SQL Server Data Files in Azure Storage feature. 這個新的 SQL Server 物件稱為 SQL Server, HTTP_STORAGE_OBJECT,而且系統監視器可以在使用 Azure 儲存體執行 SQL Server 時,使用此物件來監視活動。The new SQL Server object is called as SQL Server, HTTP_STORAGE_OBJECT and it can be used by System Monitor to monitor activity when running SQL Server with Azure Storage.

SQL Server Management Studio 支援SQL Server Management Studio support

SQL Server Management Studio 可讓您經由許多對話方塊視窗使用此功能。SQL Server Management Studio allows you to use this feature via several dialog windows. 例如,您可以鍵入儲存體容器的 URL 路徑,例如 https://teststorageaccnt.blob.core.windows.net/testcontainer/For example, you can type the URL path of the storage container, such as > https://teststorageaccnt.blob.core.windows.net/testcontainer/ :

作為路徑,例如 [新增資料庫] 、[附加資料庫] 和 [還原資料庫] 。as a Path in several dialog windows, such as New Database, Attach Database, and Restore Database. 如需詳細資訊,請參閱教學課程:搭配 SQL Server 2016 資料庫使用 Microsoft Azure Blob 儲存體服務For more information, see Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases.

SQL Server 管理物件 (SMO) 支援SQL Server Management Objects (SMO) support

使用 Azure 功能中的 SQL Server 資料檔案時,可支援所有 SQL Server 管理物件 (SMO)。When using the SQL Server Data Files in Azure feature, all SQL Server Management Objects (SMO) are supported. 如果 SMO 物件需要檔案路徑,請使用 BLOB URL 格式而非本機檔案路徑,例如 https://teststorageaccnt.blob.core.windows.net/testcontainer/If an SMO object requires a file path, use the BLOB URL format instead of a local file path, such as https://teststorageaccnt.blob.core.windows.net/testcontainer/. 如需 SQL Server 管理物件 (SMO) 的詳細資訊,請參閱《SQL Server 線上叢書》中的 SQL Server 管理物件 (SMO) 程式設計指南For more information about SQL Server Management Objects (SMO), see SQL Server Management Objects (SMO) Programming Guide in SQL Server Books Online.

Transact-SQL 支援Transact-SQL support

這項新功能已經在 Transact-SQL 介面區中導入下列變更:This new feature has introduced the following change in the Transact-SQL surface area:

  • sys.master_files 系統檢視中的新 int資料行: credential_idA new int column, credential_id, in the sys.master_files system view. credential_id 資料行是用來讓啟用 Azure 儲存體的資料檔案能夠交互參考 sys.credentials,以便取得針對它們建立的認證。The credential_id column is used to enable Azure Storage enabled data files to be cross-referenced back to sys.credentials for the credentials created for them. 您可以使用此資料行進行疑難排解,例如某個資料庫檔案正在使用認證,導致無法刪除認證時。You can use it for troubleshooting, such as a credential cannot be deleted when there is a database file which uses it.

Microsoft Azure 中的 SQL Server 資料檔案疑難排解Troubleshooting for SQL Server Data Files in Microsoft Azure

為了避免因為功能不支援或有限制而發生錯誤,請先檢閱< Limitations>。To avoid errors due to unsupported features or limitations, first review Limitations.

使用 Azure 儲存體功能中的 SQL Server 資料檔案時,可能會收到的錯誤清單如下。The list of errors that you might get when using the SQL Server Data Files in Azure Storage feature are as follows.

驗證錯誤Authentication errors

  • 無法卸除認證 '%.*ls',因為作用中的資料庫檔案正在使用它。 Cannot drop the credential '%.*ls' because it is used by an active database file.
    解決方案:當您嘗試卸除的認證仍然由 Azure 儲存體作用中資料庫檔案使用時,就可能會看見此錯誤。Resolution: You may see this error when you try to drop a credential that is still being used by an active database file in Azure Storage. 若要卸除認證,您必須先刪除具有此資料庫檔案的相關聯 Blob。To drop the credential, first you must delete the associated blob that has this database file. 若要刪除擁有使用中租用的 Blob,您必須先中斷租用。To delete a blob that has an active lease, you must first break the lease.

  • 尚未在容器上正確建立共用存取簽章。 Shared Access Signature has not been created on the container correctly.
    解決方案:請確定您已經在容器上正確建立共用存取簽章。Resolution: Make sure that you have created a Shared Access Signature on the container correctly. 請檢閱以下連結中第 2 課所提供的指示:教學課程:搭配 SQL Server 2016 資料庫使用 Microsoft Azure Blob 儲存體服務Review the instructions given in Lesson 2 in Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases.

  • 尚未正確建立 SQL Server 認證。 SQL Server credential has not been not created correctly.
    解決方案:請確定您已經針對 [識別] 欄位使用「共用存取簽章」,並正確建立密碼。Resolution: Make sure that you have used 'Shared Access Signature' for the Identity field and created a secret correctly. 請檢閱以下連結中第 3 課所提供的指示:教學課程:搭配 SQL Server 2016 資料庫使用 Microsoft Azure Blob 儲存體服務Review the instructions given in Lesson 3 in Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases.

租用 Blob 錯誤:Lease blob errors:

  • 在另一個使用相同 Blob 檔案的執行個體已經當機之後,嘗試啟動 SQL Server 時發生錯誤。Error when trying to start SQL Server after another instance using the same blob files has crashed. 解決方案:一般作業期間,SQL Server 會使用暫時租用來保留 Blob 進行儲存,而且每隔 45 至 60 秒就會更新每個 Blob 租用。Resolution: During normal operation, SQL Server uses temporary leases to reserve Blobs for storage with a renewal of each Blob lease every 45 to 60 seconds. 如果伺服器當機,而且設定為使用相同 Blob 的另一個 SQL Server 執行個體啟動,新的執行個體最多會等候 60 秒,讓現有的 Blob 租用過期。If a server crashes and another instance of SQL Server configured to use the same blobs is started, the new instance will wait up to 60 seconds for the existing lease on the Blob to expire. 如果您想要將資料庫附加至另一個執行個體,而且您無法等候租用在 60 秒內過期,可以明確中斷 Blob 租用,避免附加作業發生任何失敗。If you want to attach the database to another instance and you cannot wait for the lease to expire within 60 seconds, you can explicitly break the lease on the Blob to avoid any failures in attach operations.

資料庫錯誤Database errors

  1. 建立資料庫時發生錯誤 Errors when creating a database
    解決方案:請檢閱以下連結中第 4 課所提供的指示:教學課程:搭配 SQL Server 2016 資料庫使用 Microsoft Azure Blob 儲存體服務Resolution: Review the instructions given in Lesson 4 in Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases.

  2. 執行 Alter 陳述式時發生錯誤 Errors when running the Alter statement
    解決方案:請務必在資料庫上線時執行 Alter Database 陳述式。Resolution: Make sure to execute the Alter Database statement when the database is online. 將資料檔案複製到 Azure 儲存體時,一定要建立分頁 Blob 而非區塊 Blob。When copying the data files to Azure Storage, always create a page blob not a block blob. 否則,ALTER Database 將會失敗。Otherwise, ALTER Database will fail. 請檢閱以下連結中第 7 課所提供的指示:教學課程:搭配 SQL Server 2016 資料庫使用 Microsoft Azure Blob 儲存體服務Review the instructions given in Lesson 7 in Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases.

  3. 錯誤碼 5120 無法開啟實體檔案 "%.*ls"。作業系統錯誤 %d: "%ls"Error code 5120 Unable to open the physical file "%.*ls". Operating system error %d: "%ls"

    解決方案:目前,這項新增強功能不支援多個 SQL Server 執行個體同時存取 Azure 儲存體中的相同資料庫檔案。Resolution: Currently, this new enhancement does not support more than one SQL Server instance accessing the same database files in Azure Storage at the same time. 如果 ServerA 在線上且具有作用中的資料庫檔案,而 ServerB 意外啟動,而且也有指向相同資料檔案的資料庫,則第二部伺服器將無法啟動資料庫,錯誤碼為 5120 無法開啟實體檔案 "%.*ls"。作業系統錯誤 %d: "%ls"If ServerA is online with an active database file and if ServerB is accidently started, and it also has a database which points to the same data file, the second server will fail to start the database with an error code 5120 Unable to open the physical file "%.*ls". Operating system error %d: "%ls".

    若要解決此問題,請先判斷您是否需要讓 ServerA 存取 Azure 儲存體中的資料庫檔案。To resolve this issue, first determine if you need ServerA to access the database file in Azure Storage or not. 如果不需要,只要移除 ServerA 與 Azure 儲存體中資料庫檔案之間的任何連接即可。If not, simply remove any connection between ServerA and the database files in Azure Storage. 若要這樣做,請遵循下列步驟:To do this, follow these steps:

    1. 使用 ALTER Database 陳述式,將 Server A 的檔案路徑設定為本機資料夾。Set the file path of Server A to a local folder by using the ALTER Database statement.

    2. 將 Server A 中的資料庫設為離線。Set the database offline in Server A.

    3. 然後,將資料庫檔案從 Azure 儲存體複製到 ServerA 中的本機資料夾。這樣可確保 ServerA 在本機仍然具有資料庫的複本。Then, copy database files from Azure Storage to the local folder in Server A. This ensures that ServerA still has a copy of the database locally.

    4. 將資料庫設為上線。Set the database online.

後續步驟Next steps

建立資料庫Create a database