Transcript 530x - SQLSaturday

Stretch Database &
Managed Backups
Cloud Services for your SQL Servers
Andrew Loree – Solution Architect
Goals
• Learn about Stretch Database & Managed Backups
• Two Azure service supported features for on-premises SQL
Servers
• Understand related Azure services, how to setup and
capacity/cost
• Demo configuration and examples of using feature
• Cover practical use-cases that make sense today and
limitations for future versions to possibly resolve
Got a question?
Andrew Loree
andyloree.com
[email protected]
@LowOnDiskSpace
Agenda
• Managed Backups
• Azure Storage
• Overview & Benefits
• Demo
• Use-cases & Limitations
• Stretch Database
• Azure SQL Server & Database
• Overview & Benefits
• Demo
• Use-cases & Limitations
Help!!!
Patience!
• Azure and SSMS have fast paced release cycles
Fast releases == unclear & out-of-date documentation
• Where to look
• Slack SQL Community
https://sqlcommunity.slack.com/
• Twitter
#sqlhelp
• Search Connect
https://connect.microsoft.com/
Azure Storage
Terminology
• Azure Storage Account – single point of billing and rollup of
invoicing for all associated storage containers
• Two “kinds” of accounts:
• Classic - 2014
• Resource Manager - 2016
• Azure Storage Container – security and access control point,
within an associated storage account
• Shared Access Signature – delegated access (key) for an
application to have limited access to a container (like a SQL
Server credential)
• Resource Groups – grouping of associated services (not just
storage accounts or SQL Databases)
Managed Backups
Overview & Benefits
• Manages and automates SQL Server backups to Azuze Blob Storage
Why? Simplified backup process, so one less thing to manage & maintain
When? Fixed or custom schedules, transaction log size awareness
How? Database or instance-wide configuration, just set a retention duration
Where? SQL Server 2014+, standard or enterprise Edition, on-premises or IaaS
What? Any recovery model, Availability Group-aware, encryption of
backup
Managed Backups
Requirements
• Azure Storage Account
• SQL Server Agent required & must be running - Poor SQL Express
Implementation Details
• New schema(s) in msdb – managed_backup, smart_admin
• Internal “jobs” – Not visible thru SQL Agent
• PowerShell CMD’s:
Get-SqlSmartAdmin,Set-SqlSmartAdmin,Test-SqlSmartAdmin
Managed Backups
Demo!
Managed Backups
Email Notifications
• Sent via SQL Agent job, with default frequency of every 15 minutes
• Adjust the job schedule
Managed Backups
Limitations
• 1 to 12 TB size backup size - based upon Storage Account
• Maximum 30 days retention, no separate log retention
• No differentials or complex backup strategies
• After database drop/deletion, backups retained according
to the retention period
• Ad-hoc backups and any other tooling MUST use
COPY_ONLY option – don’t break the log chain
• No option to keep local copy – longer recovery time
Managed Backups
Use-Cases
• Limited infrastructure locations
• Remote offices and closet data centers
• No offsite solution for disaster recovery
• Azure hosted virtual machines running SQL Server
• IaaS database migrations
• Others?
Azure SQL DB/Server
Terminology
• Database Transaction Unit (DTU) – performance scaling unit
• Azure SQL Database Tiers – Service-level groupings, with multiple
performance levels within each increasing in DTU’s
• Basic – think SQL Express
• Standard – Go-to option for most cloud applications
• Premium – High transactional volume databases
• Azure SQL Server – Hosts one or more Azure SQL Database
Azure SQL DB/Server
Database Transaction Unit (DTU)
• Blended measure of CPU, memory, reads/writes
• Different “tiers” and levels within
• DTU “Calculator” using perf counter data
http://dtucalculator.azurewebsites.net/
Azure SQL DB/Server
Database Transfer Unit
Updated 7/5/2016 - https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/
But wait…
Azure SQL Stretch Database != Azure SQL Database
• With RTM release, new resource type:
SQL Stretch Database
• Database Stretch Units (DSU)
• Roughly like DTU, but there is no tier levels
• Storage and snapshot pricing separate
Azure SQL Stretch Database
Database Stretch Unit
Updated 7/5/2016 - https://azure.microsoft.com/en-us/pricing/details/sql-server-stretch-database/
Stretch Database
Overview & Benefits
• Lower cost “cold” storage
• Transparent to queries/applications*
• Streamlines on-premises data maintenance
• Faster backups
• Divide and conquer indexes
* Assumes your tables are candidates for the limitations imposed
Stretch Database
Demo
Stretch Database
Limitations
• You cannot UPDATE or DELETE rows already migrated*
• Default and check constraints
• Blob data types – text, ntext, image, xml,…
• Foreign key constraints as the parent
Orders -> Order_Detail
• Full text or indexed views
* Only on Azure side
Updated 6/14/2016 - https://msdn.microsoft.com/en-us/library/mt605114.aspx
Stretch Database
Limitations – part 2
• Unique constraints not enforced
• Cannot INSERT rows in Azure-side
• No indexed views or filtered indexes
• No memory optimized and replicated tables
• No CDC or Change Tracking
Updated 6/14/2016 - https://msdn.microsoft.com/en-us/library/mt605114.aspx
Stretch Database
Use-Cases
• Offloading log or event type data from more
expensive storage
• Long-term compliance retention scenarios
• TDE is supported on the Azure Stretch Database
• Others?
Got a question?
Andrew Loree
andyloree.com
[email protected]
@LowOnDiskSpace