What email address or phone number would you like to use to sign in to Docs.com?
If you already have an account that you use with Office or other Microsoft services, enter it here.
Or sign in with:
Signing in allows you to download and like content, and it provides the authors analytical data about your interactions with their content.
Embed code for: Azure SQL Database - 12 Things to Know
Select a size
The PowerPoint slide deck used for the SQL Saturday talk by the same name.
Azure SQL Database: 12 Things To Know
Director of IT, Sorrell College of Business @ Troy University (#IGoToTroy)
Software Architect, Sorrell Solutions, L.L.C.
1. Stay Up-To-Date on Feature Set
Recent service changes
January 2017: Perf upgrades
Max of 4 TB DB size
New scaling UI (ask Tim Radney)
New RS tier
Microsoft Azure Newsletter
2. Have a PS Create Script Handy Abbreviated. Full version in resources.
# Define name for Azure resources
$ResourceName = "SqlSatBhm"
$ServerName = $ResourceName.ToLower()
# Create new resource group
New-AzureRmResourceGroup –Name $ResourceName -Location eastus
# Create new SQL Server endpoint
New-AzureRmSqlServer -ResourceGroupName $ResourceName -ServerName $ServerName -Location eastus `
-ServerVersion "12.0" -SqlAdministratorCredentials `
(Get-Credential -UserName dbadmin -Message "Pwd for server admin")
# Create firewall rules
New-AzureRmSqlServerFirewallRule -ResourceGroupName $ResourceName -ServerName $ServerName `
# Set Azure AD admin
Set-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName $ResourceName -ServerName $ServerName `
# Create the database
# Or -Edition Basic without RequestedServiceObjectiveName
New-AzureRmSqlDatabase -ResourceGroupName $ResourceName -ServerName $ServerName `
-DatabaseName MyDb -Edition Standard -RequestedServiceObjectiveName "S0" `
3. Restores Always Create a New DB
4. Restores Take a Long Time
While RPO is outstanding, RTO is not so great…
“In most cases, the recovery time is less than 12 hours.”
Can you be offline for 24 hours or more?
Can you lose up to 1 hour of data?
Do you need to restore data older than 35 days?
5. Geo-Replication April 2017: Active Only
Active = Readable
Available in all tiers
Replica must be same tier, but not same perf objective
Active: 1x cost
(Standard is 0.75x)
Take advantage for read operations!
6a. BACPAC for long-term archiving
DANGER, WILL ROBINSON! DANGER!
6b. Azure Recovery Services VaultPREVIEW
NEW: Effective March 2017
Up to 10 years
Retention managed by Vault
Set up on active secondaries also
Why do you still need 6a?
7. Deleting a Server is Irreversible… Deleting a database is not
Remember, you don’t pay for a “server,” only for databases
8. Use Azure AD Authentication
Assign Azure AD group as DB admin
Add AD users or groups as DB logins
CREATE USER [firstname.lastname@example.org] FROM EXTERNAL PROVIDER;
CREATE USER [Azure AD Group Display Name] FROM EXTERNAL PROVIDER;
Add contained users to roles
9. Regularly Check Firewall Rules
Use a PS script with the “fixed” IPs
10. Configure Access Permissions Using RBAC
11. Turn On Auditing and Threat Detection
Inherit settings from server
Restores to same server will have correct settings
12. Automate Management with Azure Fx
Check the Automated Runbook Gallery:
Please, complete a session eval form!
As always, “be honest, but gentle” (Johan Arwidmark, ca. 2013)
Quick show of hands:
I learned at least 1 new bits about SQL Azure from this talk.
I learned at least 2 new bits about SQL Azure from this talk.
I learned at least 4 new bits about SQL Azure from this talk.
I learned at least 8 new bits about SQL Azure from this talk.
Up and running with Azure SQL Database already? This session will provide a grab bag of 12 how-to items you'll need to know to successfully keep your database running. Among those, we'll cover PowerShell for Azure SQL DB, geo-replica and other disaster recovery features and monitoring.
March 2017: 4 TB, scaling UI, new RS tier
January 2017: Perf
per-database read and log-write resources across all Premium performance levels for Azure SQL Database are now increased by 100 percent. Log-write resources in the Basic and Standard performance levels are also increased by 100 percent.
~ 2016-07-05: 35-day backup retention for Standard tier
2016-04-13: User SQL CLR removed
On active secondaries: when becomes primary, immediate full backup to vault
Need to know about 6a because you can actually download it, manage retention at your leisure, etc.e $ResourceName -Location eastus
Up and running with