Exportovat (0) Tisk
Rozbalit vše
EN
Tento obsah není k dispozici ve vašem jazyce, ale můžete využít tuto verzi v anglickém jazyce.

SQL Server Managed Backup to Windows Azure

SQL Server 2014

SQL Server Managed Backup to Windows Azure manages and automates SQL Server backups to the Windows Azure Blob storage service. The backup strategy used by SQL Server Managed Backup to Windows Azure is based on the retention period and the transaction workload on the database. SQL Server Managed Backup to Windows Azure supports point in time restore for the retention time period specified.

SQL Server Managed Backup to Windows Azure can be enabled at the database level or at the instance level to manage all the databases on the instance of SQL Server. The SQL Server can be running on-premises or in hosted environments like the Windows Azure virtual machine. SQL Server Managed Backup to Windows Azure is recommended for SQL Server running on Windows Azure Virtual Machines.

  • Currently automating backups for multiple databases requires developing a backup strategy, writing custom code, and scheduling backups. Using SQL Server Managed Backup to Windows Azure, you only are required provide the retention period settings and the storage location. SQL Server Managed Backup to Windows Azure schedules, performs and maintains the backups.

    SQL Server Managed Backup to Windows Azure can be configured at the database level or at configured with default settings for an instance of SQL Server. Automating backup using SQL Server Managed Backup to Windows Azure has the following benefits:

    • By setting the defaults at the instance level, you can apply these settings to any database created thereafter, thus removing the risk of new databases not being backed up and data loss.

    • The option of enabling SQL Server Managed Backup to Windows Azure and setting the retention period at the database level, allows you to override the default settings set at the instance level. This allows you to have more granular control on the recoverability for a specific database.

  • With SQL Server Managed Backup to Windows Azure, you do not have to specify the type or frequency of the backups for a database. You specify the retention period, and SQL Server Managed Backup to Windows Azure determines the type and frequency of backups for a database stores the backups on Windows Azure Blob storage service. For more details on the set of criteria that SQL Server Managed Backup to Windows Azure uses to create the backup strategy,, see the Components and Concepts section in this topic.

  • When configured to use encryption, you have additional security for the backup data. For more information, see Backup Encryption

For more details on the benefits of using Windows Azure Blob storage for SQL Server backups, see SQL Server Backup and Restore with Windows Azure Blob Storage Service

SQL Server Managed Backup to Windows Azure

A SQL Server feature that automates database backup and maintains the backups based on the retention period.

Retention Period

The retention period is used bySQL Server Managed Backup to Windows Azure to determine what backup files should be retained in the storage in order to recover a database to a point in time within the time frame specified. The supported values are in the range of 1-30 days.

Log Chain

A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database.

In this section:

Permissions

Transact-SQL is the main interface used to configure and monitor SQL Server Managed Backup to Windows Azure. In general, to run the configuration stored procedures, db_backupoperator database role with ALTER ANY CREDENTIAL permissions, and EXECUTE permissions on sp_delete_backuphistory stored procedure is required. Stored procedures and functions used to review information typically require Execute permissions on the stored procedure and Select on the function respectively.

Prerequisites

Prerequisites:

Windows Azure Storage service is used by SQL Server Managed Backup to Windows Azure to store the backup files. The concepts, structure, and requirements for creating a Windows Azure storage account is explained in detail in the Introduction to Key Components and Concepts section of the SQL Server Backup to URL topic.

SQL Credential is used to store the information required to authenticate to the Windows Azure storage account. The SQL Credential object stores the account name and the access key information. For more information, see the Introduction to Key Components and Concepts section in the SQL Server Backup to URL topic. For a walkthrough on how to create a SQL Credential to store Windows Azure Storage authentication information, see Lesson 2: Create a SQL Server Credential.

Concepts and Key Components

The SQL Server Managed Backup to Windows Azure is a feature that manages the backup operations. It stores the metadata in the msdb database and uses system jobs to write full database and transaction log backups.

Components

Transact-SQL is the main interface to interact with SQL Server Managed Backup to Windows Azure. System stored procedures are used for enabling, configuring, and monitoring SQL Server Managed Backup to Windows Azure. System functions are used to retrieve existing configuration settings, parameter values, and backup file information. Extended events are used to surface errors and warnings. Alert mechanisms are enabled through SQL Agent jobs and SQL Server Policy Based Management. The following is a list of the objects and a description of its functionality in relation to SQL Server Managed Backup to Windows Azure.

PowerShell cmdlets are also available to configure SQL Server Managed Backup to Windows Azure. SQL Server Management Studio supports restoring backups created by SQL Server Managed Backup to Windows Azure by using the Restore Database task

System Object

Description

MSDB

Stores the metadata, backup history for all the backups created by SQL Server Managed Backup to Windows Azure.

smart_admin.set_db_backup

System stored procedure for enabling and configuring SQL Server Managed Backup to Windows Azure for a database.

smart_admin.set_instance_backup

System stored procedure for enabling and configuring default settings SQL Server Managed Backup to Windows Azure for the SQL Server instance.

smart_admin.sp_ backup_master_switch

System stored procedure to pause and resume SQL Server Managed Backup to Windows Azure.

smart_admin.sp_set_parameter

System stored procedure to enable and configure monitoring for SQL Server Managed Backup to Windows Azure. Examples: enabling extended events, mail settings for notifications.

smart_admin.sp_backup_on_demand

System stored procedure that is used to perform an ad-hoc backup for a database that is enabled to use SQL Server Managed Backup to Windows Azure without breaking the log chain.

smart_admin.fn_backup_db_config

System function that returns the current SQL Server Managed Backup to Windows Azure status and configuration values for a database, or for all the databases on the instance.

smart_admin.fn_is_master_switch_on

System function that returns the status of the master switch.

smart_admin.sp_get_backup_diagnostics

System stored procedure used to return the events logged by Extended Events.

smart_admin.fn_get_parameter

System function that returns the current values for backup system settings such as monitoring and mail settings for alerts.

smart_admin.fn_available_backups

Stored Procedure used to retrieve available backups for a specified database or for all the databases in an instance.

smart_admin.fn_get_current_xevent_settings

System function that returns the current extended event settings.

smart_admin.fn_get_health_status

System function that returns the aggregated counts of errors logged by Extended Events for a specified period.

Monitor SQL Server Managed Backup to Windows Azure

Extended Events for monitoring, email notification of errors and warnings, SQL Server Policy Based Management for SQL Server Managed Backup to Windows Azure .

Backup Strategy

Backup Strategy used by SQL Server Managed Backup to Windows Azure:

The type of backups scheduled and the backup frequency is determined based on the workload of the database. The retention period settings are used to determine the length of time a backup file should be retained in the storage and the ability to recover the database to a point-in-time within the retention period.

Backup Container and File Naming Conventions:

SQL Server Managed Backup to Windows Azure names the Windows Azure storage container using the SQL Server Instance Name for all databases except availability databases. For availability databases, the Availability Group GUID is used to name the Windows Azure storage container.

The backup file for non availability databases are named using the following convention: The name is created using the first 40 characters of the database name, the database GUID without the ‘-‘, and the timestamp. The underscore character is inserted between segments as separators. The .bak file extension is used for full backup and .log for log backups. For Avaialbility Group databases, in addition to the file naming convention described above, the Availability Group database GUID is added after the 40 characters of the database name. The Availability Group database GUID value is the value for group_database_id in sys.databases.

Full Database Backup: SQL Server Managed Backup to Windows Azure agent schedules a full database backup if any of the following is true.

  • A database is SQL Server Managed Backup to Windows Azure enabled for the first time, or when SQL Server Managed Backup to Windows Azure is enabled with default settings at the instance level.

  • The log growth since last full database backup is equal to or larger than 1 GB.

  • The maximum time interval of one week has passed since the last full database backup.

  • The log chain is broken. SQL Server Managed Backup to Windows Azure periodically checks to see whether the log chain is intact by comparing the first and last LSNs of the backup files. If there is break in the log chain for any reason, SQL Server Managed Backup to Windows Azure schedules a full database backup. The most common reason for log chain breaks is probably a backup command issued using Transact-SQL or through the Backup task in SQL Server Management Studio. Other common scenarios include accidental deletion of the backup log files, or accidental overwrites of backups.

Transaction Log Backup: SQL Server Managed Backup to Windows Azure schedules a log backup if any of the following is true:

  • There is no log backup history that can be found. This is usually true when SQL Server Managed Backup to Windows Azure is enabled for the first time.

  • The transaction log space used is 5 MB or larger.

  • The maximum time interval of 2 hours since the last log backup is reached.

  • Any time the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.

Retention Period Settings

When enabling backup you must set the retention period in days: The minimum is 1 day, and maximum is 30 days.

SQL Server Managed Backup to Windows Azure based on the retention period settings, assesses the ability to recover to a point in time in the specified time, to determine what backup files to keep and identifying the backup files to delete. The backup_finish_date of the backup is used to determine and match the time specified by the retention period settings.

Important Considerations

There are some considerations that are important to understand their impact on SQL Server Managed Backup to Windows Azure operations. They are listed below:

  • For a database, if there is an existing full database backup job running, then waits for the current job to be completed before doing another full database backup for the same database. Similarly, only one transaction log backup can be running at a given time. However, a full database backup and a transaction log backup can run concurrently. Failures are logged as Extended Events.

  • If more than 10 concurrent full database backups are scheduled, a warning is issued through the debug channel of Extended Events. then maintains a priority queue for the remaining databases that require a backup until the all backups are scheduled and completed.

Support Limitations

The following are some limitations specific to SQL Server 2014:

  • SQL Server Managed Backup to Windows Azure agent supports database backups only: Full and Log Backups. File backup automation is not supported.

  • SQL Server Managed Backup to Windows Azure operations are currently supported using Transact-SQL. Monitoring and troubleshooting can be done by using Extended Events. PowerShell and SMO support is limited to configuring storage and retention period default settings for an instance of SQL Server, and monitoring the backup status and overall health based on SQL Server Policy Based Management policies.

  • System Databases are not supported.

  • Windows Azure Blob Storage service is the only supported backup storage option. Backups to disk or tape are not supported.

  • Currently, the maximum file size allowed for a Page Blob in Windows Azure Storage is 1 TB. Backup files larger than 1 TB will fail. In order to avoid this situation, we recommend that for large databases, use compression and test the backup file size prior to setting up SQL Server Managed Backup to Windows Azure. You can either test by backing up to a local disk or manually backing up to Windows Azure storage using BACKUP TO URL Transact-SQL statement. For more information, see SQL Server Backup to URL.

  • Recovery Models: Only databases set to Full or Bulk-logged model are supported. Databases set to simple recovery model are not supported.

  • SQL Server Managed Backup to Windows Azure may have some limitations when it is configured with other technologies supporting backup, high availability, or disaster recovery. For more information, see SQL Server Managed Backup to Windows Azure: Interoperability and Coexistence.

Task descriptions

Topic

Basic tasks like configuring SQL Server Managed Backup to Windows Azure for a database, or configuring default settings at the instance level, disabling SQL Server Managed Backup to Windows Azure at instance or database level, pausing and restarting SQL Server Managed Backup to Windows Azure.

SQL Server Managed Backup to Windows Azure - Retention and Storage Settings

Tutorial: Step by Step instructions to configuring and monitoring SQL Server Managed Backup to Windows Azure.

Setting up SQL Server Managed Backup to Windows Azure

Tutorial: Step by Step instructions to configuring and monitoring SQL Server Managed Backup to Windows Azure for databases in Availability Group.

Setting up SQL Server Managed Backup to Windows Azure for Availability Groups

Tools and Concepts and tasks related to monitoring SQL Server Managed Backup to Windows Azure .

Monitor SQL Server Managed Backup to Windows Azure

Tools and steps to troubleshooting SQL Server Managed Backup to Windows Azure.

Troubleshooting SQL Server Managed Backup to Windows Azure

Obsah vytvořený komunitou

Přidat
Zobrazit:
© 2014 Microsoft