VENDITE: 1-800-867-1389

Geo-DR for SQL Server on Azure Infrastructure Services using Log Shipping

Geographical disaster recovery (geo-DR) is the ability to recover from a disaster (such as a natural calamity) that affects an entire geographic region. Implementing geo-DR for a primary site that is hosted on Azure™ Infrastructure Services requires keeping redundant copies of data in one or more secondary sites in different geographic regions and establishing the mechanism of propagating changes to data from the primary site to the secondary sites.

This document first considers the limitations of available geo-DR options for Microsoft® SQL Server® software on Azure Infrastructure Services. These considerations motivate the creation of a custom geo-DR solution for SQL Server by using log shipping as the underlying technology. Next, the document describes the solution approach and the solution components of that approach. It then describes the recovery time objective (RTO) and recovery point objective (RPO) tradeoffs that are possible in the solution during a failover. The document also considers the steps that are involved in role switching (from the primary to the secondary) and reverse role switching (from the new primary to the old primary).

Advantages of the solution include the following:

  • You can use it with all supported versions of SQL Server.

  • You do not need any domain relationship or file share between servers in the primary site and the disaster recovery site.

  • It supports a number of options, such as compression, HTTP-based and HTTP Secure (HTTPS)–based transport, and restore options (for example, standby or no recovery).

  • You can use it for SQL Server geo-DR in exclusively on-premises scenarios, in exclusively Azure Infrastructure Services scenarios, or in hybrid scenarios that take advantage of an Azure subscription and Azure Blob storage.

Among options that you might consider for geo-DR for SQL Server on Azure Infrastructure Services, database mirroring using certificates is a viable choice. However, if you choose database mirroring disaster recovery across data centers, achieving local high availability within a data center is problematic. This is because database mirroring allows only one secondary database for a primary database. (Also note that database mirroring is deprecated.) At this time, you cannot use AlwaysOn Availability Groups as a geo-DR option because Azure does not currently support virtual networks across data centers. The in-built log-shipping feature provided in SQL Server depends on file shares, which also cannot work across Azure data centers for the same reason. These challenges might lead you to seek a solution for geo-DR for SQL Server on Azure Infrastructure Services that is not in-built but is simple enough to put together, operate, and customize further if needed.

The individual operations of log shipping are log backup, file copy, and log restoration. These fundamental operations are not dependent on file shares. You can create Transact-SQL scripts and stored procedures, along with SQL Server Agent jobs, to piece together the individual operations and create a working solution for geo-DR that uses these fundamental operations. This approach is called customized log shipping.

Customized log shipping involves running log backups on the primary SQL Server instance and storing the sequence-numbered log backups in a container in Azure Blob storage. On the disaster recovery site, the approach involves reading the log backups from Azure Blob storage in sequence and restoring the log backups onto the disaster recovery SQL Server instance. An example of support for this approach in Microsoft SQL Server 2012 is the Backup to URL/Restore from URL feature. In that feature, SQL Server writes full backups or transaction log backups directly to Azure Blob storage, or SQL Server restores the backups directly from Blob storage. For more information about the feature, see SQL Server Backup and Restore with Azure Blob Storage Service.

An alternative option is backing up the log files to local disk and using the popular AzCopy program to move the backup files to Azure Blob storage. The AzCopy program has useful features whereby it can use a journal file to maintain a record of the status of an interrupted copy operation and to allow a failed copy operation to resume. The resume operation can even resume the copy from the part of the file that has not been copied. For more information about the AzCopy program, see AzCopy – Uploading and Downloading files for Azure Blobs.

In the solution approach, custom stored procedures are used to automate the log-shipping operations, such as supplying Azure credentials for backup, running Transact-SQL for database or log backup, sequence numbering the files, and restoring the files in sequence. These stored procedures accept a number of input parameters and record the execution history and error information in tables. The stored procedures are called through scheduled SQL Server Agent jobs. A Microsoft .NET–based program is used to install the stored procedures, jobs, and tables. You can also manually install the components of the solution by using Transact-SQL scripts. For this purpose, you can use the Sample .NET code and Transact SQL scripts which includes the “Documentation for Tables and Stored Procedures” companion document for further understanding and customizations.

The following sections describe options for the location of the Blob storage.

The Blob storage for log files can be allocated in the same location as the primary data center, as shown in Figure 1.

With this choice, on the primary data center, the movement of log backup data to the Blob store should be fast because the destination for backup files is within the same data center. Movement of the backup file to the Blob store in the same data center does not incur any transfer charges.

In the data center for disaster recovery, the movement of log backup data from the Blob store is slower than a movement from local disk because of the movement across data centers. Additionally, the transfer of the log files across data centers incurs data transfer charges. A final consideration is that in the event of complete data-center outage on the primary site, you will lose log backups that are stored there and that have not been copied to the disaster recovery site.

Figure 1. Blob storage in the same location as the primary site.

The Blob storage for log files can be allocated in the same location as the data center for disaster recovery, as shown in Figure 2.

With this choice, the movement of log backup data to the Blob store is slower compared to the earlier option because of the movement across data centers. Additionally, the movement across data centers incurs data transfer charges.

In the data center for disaster recovery, the movement of log backup data is faster compared to the earlier option because no communication occurs across data centers. A final consideration is that in the event of complete data-center outage on the disaster recovery site, you cannot successfully store log backups of the primary site in the designated Blob storage.

Figure 2. Blob storage in the same location as the secondary site.

The customized log shipping approach is implemented primarily using TSQL SQL Scripts and a sample .NET installer program with associated files. Called the Azure Datacenter Log Shipper (ADLS), it consists of the following components:

  • ADLS Installer and ADLS Installer Configuration file

  • ADLS database that includes installation, log backup/restore stored procedures, and operation/error tracking tables

  • ADLS (SQL Server Agent) jobs

  • Optionally included AzCopy.exe program that is used to support the option of backing up logs to disk and moving them to Blob storage

  • Blob storage container in a storage account in Azure

You install the ADLS components by using the ADLS Installer. Execution of the ADLS Installer depends on parameters in a configuration file. When the ADLS Installer is run on a primary (source) or disaster recovery (destination) server that is running SQL Server, it accomplishes the following:

  • Creates source or destination components (stored procedures, tables, jobs, Blob container, and credentials) that are essential for log shipping

  • Sets execution options by reading corresponding inputs from the source ADLS Installer Configuration file

  • Creates the execution schedules for the log-shipping jobs as configured in the ADLS Installer Configuration file

  • Creates a blob storage container in Azure

On the primary SQL Server, the log-shipping activities start when the SQL Server Agent job that the installer creates is invoked according to the specified schedule. The first job step calls the sp_BackupLog stored procedure at scheduled times. The sp_BackupLog procedure performs a full backup (first time) or transaction log backup (subsequent times) of the source database, appropriately naming and sequence numbering the backup files. It stores the operation execution history in the LogBackupInfo table. If sp_BackupLog encounters any errors, it stores them in the OperationErrorInfo table.

The installer configuration determines where the sp_BackupLog procedure writes the backup file to. When you use the default installation options, sp_BackupLog writes the backup to the Blob storage directly (Transact-SQL BACKUP TO URL). An installation option enables sp_BackupLog to place the backup in a local folder on disk. A subsequent job step moves the backup file to Blob storage by invoking AzCopy as a CmdExec task.

On the disaster recovery SQL Server, the log-shipping activities start when the SQL Server Agent job that the installer creates is run at the scheduled times. When you use the default installation options, the sp_RestoreLog stored procedure reads—directly from the Azure Blob storage container—the file to be restored (Transact-SQL RESTORE FROM URL). An installation option enables the SQL Server Agent job to invoke AzCopy as a prior step to copy the backup files from the Blob storage to a local disk before the job invokes sp_RestoreLog.

The sp_RestoreLog restores the read log files in sequence and stores the operation execution history in the LogRestoreInfo table. If sp_RestoreLog encounters any errors, it stores them in the Operation Error Info table.

Figure 3 depicts the solution components and dataflow.

Figure 3. Solution components and dataflow.

The ADLS Installer Configuration (ADLSInstaller.exe.config) file is in the standard .NET App.config XML format and is in the same directory as the ADLS Installer (ADLSInstaller.exe) file.

The name/value pairs in ADLSInstaller.exe.config store the names and corresponding values of parameters. These parameters specify the following:

  • Whether the ADLS Installer installs source or destination components

  • The target of log shipping (SQL Server instance and database)

  • Mode of operation (backup/restore to/from URL or local disk)

  • Azure parameters (storage account, storage access key, Blob container)

  • Restore options (recovery mode, standby file, and so on)

  • Transport and compression options

  • Job-scheduling options (frequency, start date/time, and so on)

The Documentation for Tables and Stored Procedures lists and describes the parameters.

You must set the parameters for installation in ADLSInstaller.exe.config before you run ADLSInstaller.exe.

The ADLS Installer (ADLSInstaller.exe) reads the ADLS Installer Configuration file and installs and configures the source and destination components. The individual actions that the ADLS Installer performs are as follows:

  • Reads parameters from the ADLS Installer Configuration file

  • Creates an Azure Blob container of the specified name, if it does not exist

  • Starts the SQL Server service if it has not already started

  • Starts the SQL Server Agent service if it has not already started

  • Creates the ADLSLogShipper database

  • Creates tables (use if existing) and stored procedures (drop if existing) that are needed for the source or destination in the ADLSLogShipper database

  • Runs the installation procedure to create the log-shipping job

You run the ADLS Installer by invoking ADLSInstaller.exe with no arguments.

The ADLS Installer is intended to reduce the effort in the setup of the solution components. However, use of the ADLS Installer is not mandatory. For a manual setup, perform the following steps in each of the source and destination computers that are running SQL Server:

  1. Create an Azure Blob container in a storage account.

  2. Start SQL Server and SQL Server Agent on both the source and destination computers.

  3. Create a database for storing the ADLS stored procedures and tables on each of the source and destination computers.

  4. Create the stored procedures and tables in the ADLSLogShipper database (Refer to the .NET code and Transact SQL scripts for the same) on both the source and destination computers.

  5. Run sp_ADLS_SourceInstall by using the appropriate parameters (as described in the Documentation for Tables and Stored Procedures) on the source computer to create the SQL Server Agent job.

  6. Run sp_ADLS_DestnInstall by using the appropriate parameters (as described in the Documentation for Tables and Stored Procedures) on the destination computer to create the SQL Server Agent job.

The ADLSLogShipper database acts as a container of the tables and stored procedures that the ADLS Installer creates. If this database does not already exist, the ADLS Installer creates it.

The purpose of the sp_ADLS_SourceInstall stored procedure is to create the Azure credential and job that are needed for log shipping at the primary SQL Server (source). The individual actions that sp_ADLS_SourceInstall performs are as follows:

  • It creates a unique Azure credential by using the Azure storage account name and storage access key that the ADLS Installer provides.

  • If LogLocationType=0, it creates a single-step job that invokes sp_ADLS_BackupLog by using the provided parameters to back up the database or transaction log to Azure Blob storage. If LogLocationType=1, it creates a two-step job that runs sp_ADLS_BackupLog and then invokes AzCopy. The sp_ADLS_BackupLog stored procedure backs up the full database or transaction log to disk. AzCopy copies the backup file from the disk to the Azure Blob storage container.

  • In creating the jobs, it uses the Azure parameters, transport/compression parameters, and job schedule parameters as passed by the ADLS Installer.

The Documentation for Tables and Stored Procedures lists and describes the signature and parameters of this stored procedure.

The purpose of the sp_ADLS_DestnInstall stored procedure is to create the Azure credential and job that are needed for log shipping at the destination. The individual actions that sp_ADLS_DestnInstall performs are as follows:

  • It creates a unique Azure credential by using the Azure storage account name and storage access key that the ADLS Installer provides.

  • If LogLocationType=0, it creates a single-step job that invokes sp_ADLS_RestoreLog by using the provided parameters to restore the database backup or transaction log backups from Azure Blob storage. If LogLocationType=1, it creates a two-step job to invoke AzCopy and then run sp_ADLS_RestoreLog. AzCopy copies the backup files from the Blob storage container to the local disk. The sp_ADLS_RestoreLog stored procedure restores the database backup or transaction log backups from disk.

  • In creating the jobs, it uses the Azure parameters, transport/compression parameters, and job schedule parameters as passed by the ADLS Installer.

The Documentation for Tables and Stored Procedures lists and describes the signature and parameters of this stored procedure.

The purpose of the sp_ADLS_BackupLog stored procedure is to back up either the full database or the transaction log and to correctly name and sequence number the files. The name and sequence numbering are critical to ensuring that the files are restored in the correct database and in the correct order. The individual actions that sp_ADLS_BackupLog performs are as follows:

  • Forms a backup statement by using the supplied parameters by the following steps

    • Retrieving the last successful backup sequence number for a specified SQL Server instance and database name from the LogBackupInfo table.

    • Forming a database backup statement if the last sequence number was null. Otherwise incrementing the sequence number by 1 and forming a log backup statement.

    • Forming a backup file name syntax of the form. <DB Name> + ‘_db_’ + [Next Sequence No] + ‘.bak’ if this is a full backup.<DB Name> + ‘_log_’ + [Next Sequence No] + ‘.bak’ if this is a log backup.

    • Using HTTPS by default. (Using HTTP only if that is explicitly specified).

    • Using compression by default. (Using no compression only if that is explicitly specified).

    • Forming the Azure Blob storage URL if LogLocationType=1. Otherwise, use a local folder path.

  • Performs the backup.

  • Records the result of backup in the LogBackupInfo table (status=1 implies success), along with the server name, database name, backup statement, and execution date/time.

  • If errors occur, records the error information that it obtains through the Transact-SQL functions ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), and ERROR_MESSAGE() in the OperationErrorInfo table.

The Documentation for Tables and Stored Procedures lists and describes the signature and parameters of this stored procedure.

The purpose of the sp_ADLS_RestoreLog stored procedure is to restore either the full database or transaction log files. The individual actions that sp_ADLS_RestoreLog performs are as follows:

  • Forms a restore statement by using the supplied parameters by the following steps

    • Retrieving the last successful restore sequence number for the specified SQL Server instance and database name from the LogRestoreInfo table.

    • Forming a database restore statement if the last sequence number was null. Otherwise, incrementing the sequence number by 1 and forming a log restore statement.

    • Forming a restore file name of the form.<DB Name> + ‘_db_’ + [Next Sequence No] + ‘.bak’ if this is a full backup.<DB Name> + ‘_log_’ + [Next Sequence No] + ‘.bak’ if this is a log backup.

    • Using HTTPS as the default. (Using HTTP only if that is explicitly specified).

    • Forming the Azure Blob storage URL if LogLocationType=1. Otherwise, use a local folder path.

    • Specifying standby and a standby file name or norecovery during restore, depending on the recovery option that is specified.

    • Forming the RESTORE statement and adding the specified MOVE option.

  • Puts the destination database in SINGLE_USER mode before the restore operation.

  • Ends existing connections by using the ROLLBACK IMMEDIATE option of ALTER DATABASE.

  • Performs the restore operation.

  • Puts the database in MULTI_USER mode after the restore operation or if an error occurs.

  • Records the result of the restore operation in the LogBackupInfo table (status=1 implies success), along with the server name, database name, backup statement, and execution date/time.

  • If errors occur, records the error information that it obtains through the functions ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), and ERROR_MESSAGE() in the OperationErrorInfo table.

The Documentation for Tables and Stored Procedures lists and describes the signature and parameters of this stored procedure.

The LogBackupInfo table stores the history of the log backup operations for each SQL Server instance and database. This history includes details such as server name, database name, storage account name, full Transact-SQL statement that was run and the full path of the backup file. This table stores the sequence number of the last successful backup for each combination of server name and database name.

The Documentation for Tables and Stored Procedures provides the definition of this table and descriptions of the columns.

The LogRestoreInfo table stores the history of the log restore operations for each SQL Server instance and database. This history includes details such as server name, database name, storage account name, full Transact-SQL statement that was run and the full path of each restored file. This table stores the sequence number of the last successful restore operation for each combination of server name and database name.

The Documentation for Tables and Stored Procedures provides the definition of this table and descriptions of the columns.

The OperationErrorInfo table stores error information for backup or restore operations that failed. You can join the OperationErrorInfo table with the LogBackupInfo or LogRestoreInfo table by using the OperationErrorInfoId column to find the details of the operation that caused the error. The other columns in this table store the values that the Transact-SQL ERROR functions return when they are called immediately after an error occurs.

The Documentation for Tables and Stored Procedures provides the definition of this table and descriptions of the columns.

In-built log shipping provides a way to alert you if a backup or restore operation has not been run for a certain amount of time. Although the solution components do not provide an alert mechanism, you can build a scheduled job that checks the history tables for the last successful backup or restore operation and find information about pending backup and restore operations.

The solution does not provide a cleanup/delete mechanism for log files that have been restored. Because you cannot directly use Transact-SQL to delete files from Blob storage, you could consider creating a .NET program that uses the Representational State Transfer (REST) APIs for Blob storage to clean up older log files.

When you use log shipping, there is no automatic failover from the primary site to the disaster recovery site when a disaster affects the primary site. This failover must be either manually initiated or automated through scripts, tools, or application logic. After you notice the disaster in the primary database, all remaining transaction log backups of the primary site (that have not yet been applied at the secondary site) that are available should be restored on the SQL Server instance at the disaster recovery site for as low an RPO as possible. The time that is spent in accessing and restoring these transaction logs will affect the RTO. The following list describes the possible recovery modes of the disaster recovery database:

  • NORECOVERY. When log shipping puts the SQL Server database at the disaster recovery site in the NORECOVERY mode, the SQL Server database at the disaster recovery site cannot be read or written to. However, transaction logs can continue to be restored. After failover, the disaster recovery database must switch to the RECOVERY mode.

  • STANDBY. In this mode, the SQL Server instance at the disaster recovery site is read-only. Although this mode provides a low RTO for read queries, the database does not accept write transactions. Also, logs cannot be restored while active connections exist. All write queries will fail in this mode. Hence, in this mode, you cannot meet any RTO that is set for servicing write queries. You might consider this mode temporarily to allow read queries on the disaster recovery site while the primary site is being repaired.

  • RECOVERY. When the SQL Server database at the disaster recovery site is recovered, SQL Server rolls forward all committed transactions and rolls back uncommitted transactions by using the last transaction log backup that was applied. The database is then ready for both read and write activity. How quickly the destination database can be recovered depends on how long it takes to access and restore transaction logs that have not yet been restored. If immediate failover (low RTO) overrides the need for minimizing data loss, you can conduct an immediate recovery on the disaster recovery site without restoring any further transaction logs.

To achieve zero RPO, you must restore all log backups to the disaster recovery site before you recover the disaster recovery database. This is possible only in a limited disaster scenario wherein the primary database and Blob storage location are still available despite the disaster. This allows for the final tail-log backup to be performed on the primary site and allows access to all remaining log files yet to be restored on the disaster recovery site. The purpose of the tail-log backup is to capture all outstanding transactions that have occurred since the last complete log backup.

You can use the installed SQL Agent jobs and stored procedures to perform a planned failover.

In a limited disaster scenario where the SQL Server instance and storage in the primary site are available, you may need to stop the log-shipping automation and run one or more manual backups and a tail-log backup.

To stop log shipping temporarily for a particular database, disable the ADLS_Job_Source_<DBName> stored procedure by using SQL Server Management Studio or the Transact-SQL sp_update_job stored procedure. For example, the following Transact-SQL statement disables the ADLS copy and restore job for the database that is named "test."

exec sp_update_job @job_name = N'ADLS_Job_Source_test', @enabled = 0

You can also disable the job from SQL Server Management Studio. In the case of a permanent cutover to the secondary site, you can delete the job.

To perform manual log backups in sequence after you disable the job, run the sp_ADLS_BackupLog stored procedure. For example, the following statement performs a manual (sequence numbered) log backup of the database that is named "test" from Blob storage by using the SQL Server credential that is named "mycred", the storage account that is named "mystorageaccount," and the Blob container that is named "backup."

exec sp_ADLS_BackupLog 0, 'test', 'mycred', 'mystorageaccount', 'backup'

The ADLS_ stored procedures currently do not provide an option to take a tail-log backup. Please refer to How to Backup the tail of the transaction log to implement this.

After the tail-log backup is successful, manually copy the tail-log backup to the disaster recovery site.

Whether a limited or full-scale disaster occurs, you might, at some point, need to fail over to the disaster recovery site. The steps that you must take are to disable (and delete) the restore job, restore all remaining logs (including the tail-log backup, if applicable), and recover the database at the disaster recovery site.

To disable log shipping for a particular database, disable the ADLS_Job_Destn<DBName> stored procedure by using SQL Server Management Studio or the Transact-SQL sp_update_job stored procedure. For example, the following Transact-SQL statement disables the ADLS copy and restore job for the database that is named "test."

exec sp_update_job @job_name = N'ADLS_Job_Destn_test', @enabled = 0

You can also disable the job from SQL Server Management Studio. In the case of a permanent cutover to the secondary site, you can delete the job.

To manually restore one or more log files in sequence at the destination (after you disable the SQL Server Agent job), run the stored procedure sp_ADLS_RestoreLog. For example, you can use the following statement to manually restore the next log backup of the database that is named "test" from Blob storage by using the SQL Server credential that is named "mycred," the storage account that is named "mystorageaccount," and the Blob container that is named "backup." In this example, the restored database is in STANDBY mode, and the standby file is placed in C:\backup.

exec sp_ADLS_RestoreLog 0, 'test', 'mycred', 'mystorageaccount', 'backup', 1, 'C:\backup'

The result of the restore operation will be recorded in the LogRestoreInfo table. If any errors occur, they will be recorded in the OperationErrorInfo table.

To bring the destination database to RECOVERY mode, you can run the following Transact-SQL statement.

restore  database <DatabaseName> with recovery

Assume that a disaster has occurred at the primary site and a failover to the disaster recovery site has been performed. After some time, the original primary site has been repaired and is ready to use. In this situation, you might want to switch from the disaster recovery site (the current primary site) back to the original primary site. For this purpose, you can use the custom log-shipping components to first set up log shipping in the reverse direction and later perform a planned failover at an appropriate time. Before you reverse the direction, we recommend that you delete existing log-shipping jobs, and then back up and delete entries in the LogRestoreInfo and LogBackupInfo tables for the SQL Server instance and databases in question.

Switching back involves performing a full database backup at the disaster recovery site (the current primary site), in addition to the transfer and restoration of the backup onto the original primary site. This might be a time-consuming operation if the database is large. You may be able to avoid doing and transferring a full backup during the switch back if, during the failover from the original primary site, you took the tail-log backup of the original primary site and restored it on the disaster recovery site. In this case, it is sufficient to restore only the log backups of the disaster recovery site (the current primary site) that were created after the failover onto the original primary site. The ADLS components currently do not provide a way to automate the switch back to the original primary by transferring only log backups.

The geo-DR strategy that this document described used the idea of storing database and transaction log backups in Azure Blob storage. Recently, Microsoft has released a tool that you can use for this purpose. The Microsoft SQL Server Backup to Azure Tool enables backup to Azure Blob storage and encrypts and compresses SQL Server backups that are stored locally or in the cloud. For more information, see Microsoft SQL Server Backup to Azure Tool. This tool currently does not provide a feature to restore backups onto a disaster recovery site, so you must implement a custom approach for that.

You can realize Geo-DR for SQL Server on Azure Infrastructure as a Service using a customized log shipping approach. Refer to the Sample .NET code and Transact SQL scripts used to implement this approach and customize the same for your requirement

Il documento è risultato utile?
(1500 caratteri rimanenti)
Grazie per i commenti inviati.
Mostra:
© 2014 Microsoft