feedburner
Enter your email address:

Delivered by FeedBurner


SQL Server Managed Backup to Windows Azure

Labels:

You can use SQL Server Managed Backup to Windows Azure and have SQL Server manage and automate SQL Server backups to Azure blob storage. With this option, you specify the blob storage location and the retention period and optionally customize backup at the database level. The maximum backup size is 12.8 TB. Managed backup supports point-in-time restore during the retention period. By default, managed backups are enabled at the instance level, with the same retention period for all databases (including newly added databases). You can change the retention period on an individual database, create a custom backup schedule, or disable managed backup for an individual database using Transact-SQL. You also have the option to encrypt the database backups for additional security.

Managed backup was introduced with SQL Server 2014, and the functionality increased substantially with SQL Server 2016. For the purposes of this ebook, I will focus on SQL Server 2016.

With SQL Server 2016, if you do not specify a custom schedule, the type of backups scheduled and the backup frequency is determined based on the workload of the database.
 A full database for a database is taken in the following instances:

  •  When managed backup is enabled for the first time
  •  Log growth since the last full backup is 1 GB or more
  •  One week has passed since the last full backup
  •  The log chain is broken, such as through a manual backup

 A transaction log backup is taken in the following instances:
  •  No log backup history can be found. This usually is 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 two hours since the last log backup is reached.
  •  Anytime the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.
To use SQL Server managed backup, you create the following prerequisites:
 Create an Azure storage account.
 Create a container within the storage account.
 Generate a Shared Access policy and apply the Shared Access Signature (SAS) token to the Azure blob container.
 Create a SQL Server credential based on the SAS token.
CREATE CREDENTIAL WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '' ;

After satisfying these prerequisites, you use Transact-SQL to interact with SQL Server Managed Backup to Windows Azure. There are 13 system stored procedures and functions 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. Each of these system stored procedures and functions provides code examples for you.
Extended events are used to surface errors and warnings. Alert mechanisms are enabled through SQL Agent jobs and SQL Server Policy-Based Management.

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.

Source of Information : Migrating SQL Server Databases to Azure

1 comments:
gravatar
britishessaywriters discount code said...
April 26, 2017 at 9:27 PM  

Good informative post! Running an SQL server queries has been explained in an easy and simple way making it easy to understand.

Post a Comment

Alltop, all the top stories
BlogMalaysia.com
All Malaysian Bloggers Project
Computer Blogs - BlogCatalog Blog Directory Add to Technorati Favorites
Technorati Profile
Top Computers blogs