Download as PDF, PPTX
Uploaded byDatavail
Backup and Restore SQL Server Databases in Microsoft Azure
The document provides an overview of backup and restore options for SQL Server databases in Microsoft Azure, detailing concepts such as Azure virtual machines, storage accounts, and backup strategies. It also highlights the benefits and limitations of using Azure for database backups, including different SQL Server versions and techniques for managing backup files. Key presenters include Pinal Dave, a technology consultant, and Andrew McDermid, a certified SQL DBA, who share insights and resources for attendees.
Backup and Restore SQL Server Databases in Microsoft Azure
- 1. Backup and Restore SQLServer Databases in Microsoft Azure Andrew McDermid and Pinal Dave
- 2.
- 3. Explore Everything PASSHas to Offer FREE ONLINE WEBINAR EVENTS FREE 1-DAY LOCAL TRAINING EVENTS LOCAL USER GROUPS AROUND THE WORLD ONLINE SPECIAL INTEREST USER GROUPS BUSINESS ANALYTICS TRAINING VOLUNTEERING OPPORTUNITIES PASS COMMUNITY NEWSLETTER BA INSIGHTS NEWSLETTERFREE ONLINE RESOURCES
- 4. Session Evaluations ways toaccess Go to passSummit.com Download the GuideBook App and search: PASS Summit 2016 Follow the QR code link displayed on session signage throughout the conference venue and in the program guide Submit by 5pm Friday November 6th to WIN prizes Your feedback is important and valuable. 3
- 5. Agenda Is this whereyou want to be? In here we are talking about... • SQL Server in Azure Virtual Machines • SQL Server Database Backups in Azure Virtual Machines • Azure Storage Accounts
- 6. SQL Server AzureVMs 6
- 7. SQL Server AzureVMs 7
- 8. Pinal Dave SQL Authority PinalDave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. /pinaldave @sqlauthority
- 9. Andrew McDermid Microsoft SQLDBA, Datavail Corporation Andy is a MCITP certified MS SQL DBA who delivers and manages delivery of DBA services to many diverse clients. He enjoys helping his clients by finding and deploying pragmatic and practical solutions for their database issues. /andrewmcdermid @oldskipole
- 10. SQL Server AzureVMs 10 Log 512GB Data 512GB • 2 DISKS • 1 SQL data • 1 SQL logs
- 11. SQL Server AzureVMs 11 Log 512GB Data 512GB Backups • 3 DISKS • 1 SQL data • 1 SQL logs • 1 Backups
- 12. SQL Server AzureVMs 12 Log 512GB Data 512GB Backups • 4 DISKS • 1 SQL data • 1 SQL logs • 1 Backups • 1 TempDB TempDB
- 13. SQL Server AzureVMs 13 • Server Stats • 4 CPU • 14GB RAM • 28GB D:
- 14. SQL Server AzureVMs 14 https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sizes/ SizesforvirtualmachinesinAzure
- 15. SQL Server AzureVMs 15 https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sizes/
- 16. SQL Server AzureVMs https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sizes/ DS3_V2 Standard • 192 MB/sec
- 17. SQL Server AzureVMs DS3_V2 Standard • 192 MB/sec • 8 Data Disk https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sizes/
- 18. SQL Server AzureVMs 18 Log 512GB Data 512GB • 2 DISKS • 1 SQL data • 1 SQL logs
- 19. SQL Server AzureVMs 19 Log 512GB Data 512GB Backups • 3 DISKS • 1 SQL data • 1 SQL logs • 1 Backups
- 20.
- 21. Backups to URL 21 •Azure Storage Accounts • Storage Account Name • Access Key https://storageaccountname>.blob.core.windows.net Azure Storage Account
- 22. Backups to URL 22 Edwina Container sql_serv SQLSrvr01- InstncA SQLDEV •Azure Storage Accounts • Storage Account Name • Access Key • Container Name https://storageaccountname>.blob.core.windows.net/<container name>
- 23. Backups to URL 23 •SQL CREDENTIAL • Credential Name • Identity = Storage Account Name • Password = Storage Account Key TO URL = ‘https://storageaccountname>.blob.core.windows.net/<container name>/<backupfilename>.bak’ WITH CREDENTIAL = ‘<sql credential name>’
- 24. SQL Server AzureVMs 24 Log 512GB Data 512GB Backups • 2 DISKS • 1 SQL data • 1 SQL logs
- 25. SQL Server AzureVMs 25 Log 512GB Data 512GB • 4 DISKS • 1 SQL data • 1 SQL logs • 6 unused
- 26. SQL Server AzureVMs 26
- 27. SQL Server AzureVMs 27 • PREMIUM DISK • SSD • Pay-for-capacity • Dedicated I|O • 500-5000 IOPS • 100-200 MB/sec P30 1TB 5000 IOPS 200 MB/s P20 512GB 2300 IOPS 150 MB/s P10 128GB 500 IOPS 100 MB/s Premium
- 28. SQL Server AzureVMs 28 300MB/s 1TB 300MB/s 1TB Log 46K IOPS Data 46K IOPS • 4 DISKS • 2 SQL data • 2 SQL logs • 4 unused
- 29. P20 SQL Server AzureVMs 29 P20 P20 P20 P20 P20 50 100 150 200 250 300 350 400 MB/sec 192 MB/s DS3_v2
- 30. Backups to URL– What You Need 30 • Azure Storage Account • Storage Account Name+ Container Name • Access Key • SQL Server • SQL Server 2014 SP1 CU2 (min) • SQL Credential • TSQL Backup and Restore TO | FROM URL WITH CREDENTIAL
- 31. • Free-up adisk(s) for whatever… IOPS, MB/s, TempDB; utility/trace/log files; etc • Works with maintenance plans, scripts, SQL Agent jobs, etc. Pros Cons • Not local • Thru-put limits per VM size • No striped backups • 1TB backup file limit Backups to URL – Pros and Cons
- 32. Backup to LocalDrive 32 DATABASE STRIPED BACKUP SET TO DISK Database.1.bak Database.2.bak Database.3.bak Database.4.bak
- 33. Backup to LocalDrive Demo
- 34. Backup to LocalDrive – What You Need 34 • Azure Storage Account • Storage Account Name + Container Name • Access Key • SQL Server • Any version • TSQL Backup and Restore striped set TO | FROM URL • AzCopy • /Dest: /Source: /Pattern: • Automation script
- 35. • 1TB +DB backups • Fast(-ish) backups via striping • Fast copy to azure storage via AZCopy Pro Con • Local disk storage capacity limits • Local MBsec limits • Striped backup and offsite copy management complexity Backup to Local Drive – Pros and Cons
- 36. • Storage Account& Access Key • Access Policy • Shared Access Signature Azure Storage Access and Security 36
- 37.
- 38. Guess What WillHappen Next? 38 IfYear was 2006 - BEFORE IfYear was 2016 - NOW
- 39. Access and Security- BEFORE 39
- 40. Access and Security- BEFORE 40
- 41. Access and Security- NOW 41
- 42. Access and Security- NOW 42
- 43. Shared Access Signature– What You Need 43 • CREATE SHARED ACCESS SIGNATURE WITH POWERSHELL • Login to Azure Subscription • Returns ‘CREATE CREDENTIAL‘ TSQL https://msdn.microsoft.com/en-us/library/dn466430.aspx
- 44. Shared Access Signature– What You Get 44 • SQL CREDENTIAL • Credential Name = URL • Identity = “Shared Access Signature” • Password = SAS URL TO URL = ‘https://<storageaccountname>.blob.core.windows.net/<container name>/<backupfilename>1.bak’ URL = ‘https://<storageaccountname>.blob.core.windows.net/<container name>/<backupfilename>.2.bak’ URL = ‘https://<storageaccountname>.blob.core.windows.net/<container name>/<backupfilename>.3.bak’ URL = ‘https://<storageaccountname>.blob.core.windows.net/<container name>/<backupfilename>.4.bak’
- 45. Container Shared Access Signature– What You Get 45 DATABASE 2TB STRIPED BACKUP TO URL Database.1.bak Database.2.bak Database.3.bak Database.4.bak
- 46. Striped Backups toURL Demo
- 47. Container Shared Access Signature– What You Get 47 DATABASE BACKUP TO URL Database.bak 1TB Max Backup File Size
- 48. Container Shared Access Signature– What You Get 48 DATABASE STRIPED BACKUP TO URL Database.1.bak Database.2.bak Database.3.bak Database.4.bak 12.8TB Max Backup File Size
- 49. Striped Backups toURL- What You Need 49 • Azure Storage Account • Storage Account Name+ Container Name • Access Key • Shared Access Signature • SQL Server • SQL Server 2016 • SQL “SAS” Credential • TSQL Backup and Restore striped set TO | FROM URL
- 50. • Speed • VLDB(12.8TB - 64 stripes of up to 200GB each) Pro Con • SQL 2016 + only • VLDB (12.8TB - 64 stripes of up to 200GB each) Striped Backups to URL – Pros and Cons
- 51. Session Evaluations ways toaccess Go to passSummit.com Download the GuideBook App and search: PASS Summit 2016 Follow the QR code link displayed on session signage throughout the conference venue and in the program guide Submit by 5pm Friday November 6th to WIN prizes Your feedback is important and valuable. 3
- 52. Thank You Learn morefrom Andy and Pinal andy.mcdermid@datavail.com or follow @oldskipole pinal.dave@sqlauthority.com or follow @pinaldave