I am just a medium, SQL Server the Goal

Category Archives: Business Continuity

Business Continuity and Disaster Recovery with Azure SQL Databases

During one of the recent Azure Conference, I had talked about the Business Continuity and Disaster Recovery options available with Windows Azure SQL Databases. The presentation I used is included below.

Azure SQL Databases provides the ability to restore a database to any point in time, within the retention interval for that service tier or as configured. Additionally, in case of site failures backups can be restored from Geo redundant copies. Both the Point-In-Time and the Geo restore features are available in all the service tiers for Azure SQL Databases. The retention interval is different for different service tiers. The total storage for backups is dependent on the size of the DB provisioned (twice the size of the DB provisioned). Additional storage can be purchased if required or the retention can be reduced by contacting the Azure Support teams.

Azure SQL Databases also provides a pre-configured, transparent Local (within the same datacenter) and optional Geo (Standard Geo and Active Geo Replications) high availability option for business continuity. The Standard (available in Standard and Premium tiers) and Active (Only Premium Tiers) Geo Replication needs to be configured and will incur additionally costs for the high availability copy. In case of standard Geo replication the secondary is a non-readable secondary. While Active geo allows creation of up to 4 readable secondary’s.

Demo Script


# Pre-Work -- Create 3 databases - 1 Basic, 1 StandardS1 and 1 PrimiumP1 using portal
# **********************************************************************************#

#Add the Azure Account

# List All the DB's already under the Account
$DbServer = Get-AzureSqlDatabaseServer -ServerName "Azure SQL Database Server"
$DBName = Get-AzureSqlDatabase -ServerName $DbServer.ServerName
$DBName | Format-Table

# Demo 1 -- Changing the Servier Tier and Performance Levels for a DB.
# **********************************************************************************#

# 1. Create a Sample DB in the basic tier  (Database Name - TestBasic)
New-AzureSqlDatabase -ServerName $DbServer.ServerName -DatabaseName "TestBasic" -Edition Basic -Verbose -Confirm
#Check if the DB Got created or not..
$test = Get-AzureSqlDatabase -ServerName $DbServer.ServerName -DatabaseName "TestBasic"
If($test -ne $null)
Write-Host "Database Creation Successful"

# 2. Use the below PowerShell to change the service Tier to Premium P1
$P1= Get-AzureSqlDatabaseServiceObjective -ServerName $DbServer.ServerName -ServiceObjectiveName "P1"
Set-AzureSqlDatabase -ServerName $DbServer.ServerName -DatabaseName "TestBasic" -Edition Premium -ServiceObjective $P1 -Verbose

# 3. From the Output below, we can see that the scale operation is being done in the backend.
$test = Get-AzureSqlDatabase -ServerName $DbServer.ServerName -DatabaseName "TestBasic"
$test | Format-Table

# 4. From the Output below, we can see that the scale operation is being done in the backend.
Remove-AzureSqlDatabase -ServerName $DbServer.ServerName -DatabaseName "TestBasic"

$DBName = Get-AzureSqlDatabase -ServerName $DbServer.ServerName
$DBName | Format-Table

# Demo 2 -- Point-in-Time Restore
# **********************************************************************************#

#1. Get the last restorable time for all the Databases in the account.
$DBName = Get-AzureSqlDatabase -ServerName $DbServer.ServerName
Foreach ($db in $DBName)
if($db.name -ne "master")
{ Write-Host $db.Name  "-->" $db.RecoveryPeriodStartDate.ToUniversalTime() }

## PointInTime Paramter takes values in GMT and not local server time.
$RestoreRequest = Start-AzureSqlDatabaseRestore -SourceDatabase $DBName[0] –TargetDatabaseName “BasicDB_New” –PointInTime “2015-05-07 04:00:00”
Get-AzureSqlDatabaseOperation –ServerName $DbServer.ServerName –OperationGuid $RestoreRequest.RequestID

## Restore a Deleted Database
Get-AzureSqlDatabase -RestorableDropped -ServerName $DbServer.ServerName
$RestoreRequest = Start-AzureSqlDatabaseRestore -SourceServerName $DbServer.ServerName –SourceDatabaseName “BasicDB” -SourceDatabaseDeletionDate "5/4/2015 4:54:02 PM" –TargetDatabaseName “BasicDB_Restored” -RestorableDropped
Get-AzureSqlDatabaseOperation –ServerName $DbServer.ServerName –OperationGuid $RestoreRequest.RequestID

# Demo 3 -- Geo Restore - Using PowerShell
# **********************************************************************************#

# Get a list of all recoverable Databases, which can be Geo-Restored
Get-AzureSqlRecoverableDatabase -ServerName $DbServer.ServerName

# Perform Geo-Restore
$RecoveryDatabase = Get-AzureSqlRecoverableDatabase -ServerName $DbServer.ServerName -DatabaseName "Basic_ADWorksDW"
$RestoreRequest = Start-AzureSqlDatabaseRecovery -SourceDatabase $RecoveryDatabase -TargetServerName "Azure SQL Database Server" -TargetDatabaseName "Basic_ADWorksDW_GeoRestored"
Get-AzureSqlDatabaseOperation –ServerName "Azure SQL Database Server" –OperationGuid $RestoreRequest.ID

# Demo 4 -- Setting up Geo Replication for Database
# **********************************************************************************#

#1. Standard Geo-Replication (Offline) using Powershell

Start-AzureSqlDatabaseCopy -ServerName $DbServer.ServerName -DatabaseName "StandardS2" -PartnerServer "Azure SQL Database Server" -OfflineSecondary -ContinuousCopy
Get-AzureSqlDatabaseCopy -ServerName $DbServer.ServerName -DatabaseName "StandardS2" -PartnerServer "Azure SQL Database Server"

#2. Active Geo-Replication Using PowerShell
Start-AzureSqlDatabaseCopy -ServerName $DbServer.ServerName -DatabaseName "PremiumP2" -PartnerServer "Azure SQL Database Server" -ContinuousCopy
Get-AzureSqlDatabaseCopy -ServerName $DbServer.ServerName -DatabaseName "PremiumP2" -PartnerServer "Azure SQL Database Server"

# Demo 5 -- Perform a disaster Drill
# **********************************************************************************#

#Initiate a forced termination of the Continuous Copy on the Secondary

$SecondaryDb = Get-AzureSqlDatabaseCopy -ServerName "Azure SQL Database Server" -DatabaseName "PremiumP2"
$SecondaryDb | Stop-AzureSqlDatabaseCopy -ServerName "Azure SQL Database Server" -ForcedTermination