Protect Your Data With Microsoft Azure Backup

Speakers:

  • Vijay Tandra Sistla, Principal PM Manager
  • Aruna Somendra, Senior Program Manager

Aruna is first to speak. It’s a demo-packed session. There was another session on AB during the week – that’s probably worth watching as well.

All the attendees are from diverse backgrounds, and we have one common denominator: data. We need to protect that data.

Impact of Data Loss

  • The impact can be direct, e.g. WannaCry hammering the UK’s NHS and patients.
  • It can impact a brand
  • It can impact your career

Azure Backup was built to:

  • Make backups simple
  • Keep data safe
  • Reduce costs

Single Solution

Azure Backup covers on-premises and Azure. It is one solution, with 1 pricing system no matter what you protect: instance size + storage consumed.

Protecting Azure Resources

A demo will show this in action, plus new features coming this year. They’ve built a website with some content on Azure Web Apps – images in Azure FIles and data in SQL in an IaaS VM. Vijay refreshes the site and the icons are ransomwared.

Azure Backup can support:

  • Azure IaaS VMs – the entire VM, disks, or file level recovery
  • Azure Files via Storage account snapshots (NEW)
  • SQL in an Azure IaaS VM (NEW)

Discovery of databases is easy. An agent in the guest OS is queried, and all SQL VMs are discovered. Then all databases are shown, and you back them up based on full / incremental / transaction log backups, using typical AB retention.

For Azure File Share, pick the storage account, select the file share, and then choose the backup/retention policy. It keeps up to 120 days in the preview, but longer term retention will be possible at GA.

When you create a new VM, the Enable Backup option is in the Settings blade. So you can enable backup during VM creation instead of trying to remember to do it later – no longer an afterthought.

Conventional Backup Approaches

What happens behind the scenes in AB. Instead of using on-prem SQL, file servers, you’re starting to use Azure Files and SQL in VMs. Instead of hacking backups into Azure storage (doesn’t scale, and messy) you enable Azure Backup which offers centralized management, In Azure, it is infrastructure-free. SQL is backed up using a backup extension, VM’s are backed up using a backup extension.

28-09-2017 14-34 Office Lens

Azure File Sync is supported too:

In preview, there is short-term retention using snpashots in the source storage account. After GA they will increase retention and enable backups to be storage in the RSV.

28-09-2017 14-38 Office Lens

Linux

When you backup a Linux VM, you can run a pre-script, do the backup, and then run a post-script. This can enable application-consistent backups in Linux VMs in Azure. Aruna logs into a Linux VM via SSH. There are Linux CLI commands in the guest OS, e.g. az backup. There is a JSON file that describes the pre-and post scripts. There’s some scripts by a company by a company called capside for MySQL. The pre-script creates database dumps and stops the databases.

28-09-2017 14-49 Office Lens

az backup recoverypoint list and some flags can be used to list the recovery points for the currently logged in VM. The results show if they are app or file consistent.

az backup restore files and some parameters can be used to mount the recovery point – you then copy files from the recovery point, and unmount the recovery point when done.

28-09-2017 14-45 Office Lens

Restore as a Service

28-09-2017 14-50 Office Lens

On-Premises

2/3 of customers keeping on-premises data.

Two solutions in AB for hybrid backup:

  • Microsoft Azure Backup Server (MABS) / DPM: Backup Hyper-V, VMware, SQL, SharePoint, Exchange, File Server & System State to local storage (short-term retention)  and to the cloud (long term retention)
  • MARS Agent: Files & Folders, and System State backed up directly to the cloud.

System State

Protects Active Directory, IIS metadata, file server metadata. registry, COM+ Cert Services, Cluster services info, AD, IIS metabase.

Went live in MARS agent last month.

In a demo, Vijay deletes users from AD. He restores system state files using MARS. Then you reboot the DC in AD restore mode. And then use the wbadmin tool to restore the system state. wbadmin start systemstaterecovery. You reboot again, and the users are restored.

Vijay shows MARS deployment, and shows the Project Honolulu implementation.

Next he talks about the ability to do an offline backup instead of an online full backup. This leverages the Azure storage import service, which can leverage the new Azure Data Box – a tamper proof storage solution of up to 100 TB.

Security

Using cloud isolates backup data from the production data. AB includes free multi-approval process to protect destructive operations to hybrid backups. All backup data is encrypted. RBAC offers governance and control over Azure Backup.

There are email alerts (if enabled) for destructive operations.

If data is deleted, it is retained for 14 days so you can still restore your data, just in case.

Hybrid Backup Encryption

Data is encrypted before it leaves the customer site.

Customers want:

  • To be able to change keys
  • Keep the key secret from MS

A passphrase is used to create they key. This is a key encryption key process. And MS never has your KEK.

Azure VM Disk Encryption

You still need to be able to backup your VMs. If a disk is encrypted using a KEK/BEK combination in the Key Vault, then Azure Backup includes the keys in the backup so you can restore from any point in time in your retention policy.

Isolation and Access Control

Two levels of authorization:

  • You can control access/roles to individual vaults for users.
  • There are permissions or roles within a vault that you can assign to users.

Monitoring & Reporting

Typical questions:

  • How much storage am I using?
  • Are my backups healthy?
  • Can I see the trends in my system?

Vijay does a tour of information in the RSV. Next he shows the new integration with OMS Log Analytics. This shows information from many RSVs in a single tenant. You can create alerts from events in Log Analytics – emails, webhooks, runbooks, or trigger an ITSM action. The OMS data model, for queries, is shared on docs.microsoft.com.

For longer term reporting, you can export your tenant’s data to an AB Content Pack in PowerBI – note that this is 1 tenant per content pack import, so a CSP reseller will need 100 imports of the content pack for 100 customers. Vijay shows a custom graphical report showing the trends of data sources over 3 months – it shows growth for all sources, except one which has gone down.

Power BI is free up to 1 GB of data, and then it’s a per-user monthly fee after that.

Roadmap

  • Backup of SQL in IaaS – preview
  • Backup of Azure file – preview
  • Azure CLI
  • Backup of encrypted VMs without KEK
  • Backup of VMs with storage ACLs
  • Backup of large disk VMs
  • Upgrade of classic Backup Vault to ARM RSV
  • Resource move across RG and subscription
  • Removal of vault limits
  • System State Backup

Running Tier 1 Worklaods on SQL Server on Microsoft Azure Virtual Machines

Speaker: Ajay Jagannathan, Principal PM Manager, Microsoft Data Platform Group. He leads the @mssqltiger team.

I think that this is the first every SQL Server that I’ve attended in person at a TechEd/Ignite. I was going to a PaaS session instead, but I’ve got so many customers running SQL Server on Azure VMs, that I thought that this was important for me to see. I also thought it might be useful for a lot of readers.

Microsoft Data Platform

Starting with SQL 2016, the goal was to make the platform consistent on-premises, with Azure VMs, or in Azure SQL. With Azure, scaling is possible using VM features such as scale sets. You can offload database loads, so analytics can be on a different tier:

  • On-premises: SQL Server and SQL Server (DW) Reference architecture
  • IaaS: SQL Server in Azure VM with SQL Server (DW) in Azure VM.
  • PaaS: Azure SQL database with Azure SQL data warehouse

Common T-SQL surface area. Simple cloud migration. Single vendor for support. Develop once and deploy anywhere.

Azure VM

  • Azure load balancer routes traffic to the VM NIC.
  • The compute and storage are separate from the storage.
  • The virtual machine issues operations to the storage.

SQL Server in Azure VM – Deployment Options

  • Microsoft gallery images: SQL Server 2008 R2 – 2017, SQL Web, Std, Ent, Dev, Express. Windows Server 2008 R2 – WS2016. RHEL and Ubuntu.
  • SQL Licensing: PAYG based on number of cores and SQL edition. Pay per minute.
  • Bring your own license: Software Assurance required to move/license SQL to the cloud if not doing PAYG.
  • Creates in ~10 miuntes.
  • Connect via RDP, ADO, .NET, OLEDB, JBDC, PHO …
  • Manage via Portal, SSMS, owerShell, CLI, System Center …

It’s a VM so nothing really changes from on-premises VM in terms of management.

Everytime there’s a critical update or service pack, they update the gallery images.

VM Sizes

The recommend DS__V2- or FS-Series with Premium Storage. For larger loads, they recommend the GS- and LS-Series.

For other options, there’s the ES_v2 series (memory optimized DS_v3), and the M-Series for huge RAM amounts.

VM Availability

Availability sets distribute VMs across fault and update domains in a single cluster/data centre. You get a 99.95% SLA on the service for valid configurations. Use this for SQL clusters.

Managed disks offer easier IOPS management, particularly with Premium Disks (storage account has a limit of 20,000 IOPS). Disks are distributed to different storage stamps when the VM is in an availability set – better isolation for SQL HA or AlwaysOn.

High Availability

Provision a domain controller replica in a different availability set to your SQL VMs. This can be in the same domain as your on-prem domain (ExpressRoute or site-to-site VPN).

Use (Get-Cluster).SameSubnetThreshold = 20 to relax Windows Cluster failure detection for transient network failure.

Configure the cluster to ignore storage. They recommend AlwaysOn. There is no shared storage in Azure. New-Cluster –Name $ClusterName –NoStorage –Node $LocalMachineName

Configure Azure load balancer and backend pool. Register the IP address of listener.

There are step-by-step instructions on MS documentation.

SQL Server Disaster Recovery

Store database backups in geo-replicated readable storage. Restore backups in a remote region (~30 min).

Availability group options:

  • Configure Azure as remote region for on-premise
  • Configure On-prem as DR for Azure
  • Replicate in Azure Remote region – failover to remove in ~30s. Offload remote reads.

Automated Configuration

Some of these are provided by MS in the portal wizard:

  • Optimization to a target workload: OLTP/DW
  • Automated patching and shutdown – latter is very new, and to reduce costs for new dev/test workloads to reduce costs at the end of the workday.
  • Automated backup to a storage account, including user and system databases. Useful for a few databases, but there’s another option coming for larger collections.

Storage Options

The recommend LRS only to keep write performance to a maximum. GRS storage is slower, and could lead to database file being written/replicated before log storage.

Premium Storage: high IOPS and low latency. Use Storage Spaces to increase capacity and performance. Enable host-based read caching in data disks for better IOPS/latency.

Backup to Premium Storage is 6x faster. Restore is 30x faster.

Azure VM Connectivity

  • Over the Internet.
  • Over site-site tunnel: VPN or ExpressRoute
  • Apps can connect transparently via a listener, e.g. Load Balancer.

Demo: Deployment

The speaker shows a PowerShell script. Not much point in blogging this. I refer JSON anyway.

http://aka.ms/tigertoolbox is the script/tools/demos repository.

Security

  • Physical security of the datacenter
  • Infrastructure security: virtual network isolation, and storage encryption including bring-your-own-key self-service encryption with Key Vault. Best practices and monitoring by Security Center.
  • Many certifications
  • SQL Security: auto-patching, database/backup encryption, and more.

VM Configuration for SQL Server

  • Use D-Series or higher.
  • Use Storage Spaces for performance of disks. Use Simple disks: the number of columns should equal the number of disks. For OLTP use 64KB interleave and use 256KB for data warehouse.
  • Do not use the system drive.
  • Put TempDB, logs, and databases on different volumes because of their different write patterns.
  • 64K allocation unit size.
  • Enable read caching on disks for data files and TempDB.
  • Do not use GRS storage.

SQL Configuration

  • Enable instant file initialization
  • Enabled locked ages
  • Enable data page compression
  • Disable auto-shrink for your databases
  • Backup to URL with compressed backups – useful for a few VMs/databases. SQL 2016 does this very quickly.
  • Move all databases to data disks, including system databases (separate data and log). Use read caching.
  • Move SQL Server error log and trace file directories to data disks

Demo: Workload Performance of Standard Versus Premium Storage

A scripted demo. 2 scripts doing the same thing – one targeting a DB on Standard disk (up to 500 IOPS) and the second targets a DB on a Premium P30 (4,500 IOPS) disk. There’s table creation, 10,000 rows, inserts, more tables, etc. The scripts track the time required.

It takes a while – he has some stats from previous runs. There’s only a 25% difference in the test. Honestly – that’s no indicative of the differences. He needs a better demo.

An IFI test shows that the bigger the database file is, the bigger the difference is in terms of performance – this makes sense considering the performance nature of flash storage.

Seamless Database Migration

There is a migration guide, and tools/services. http://datamigration.microsoft.com. One-stop shop for database migrations. Guidance to get from source to target. Recommended partners and case studies.

Tools:

  • Data Migration Assistant: An analysis tool to produce a report.
  • Azure Database Migration Service (free service that runs in a VM): Works with Oracle, MySQL, and SQL Server to SQL Server, Azure SQL, Azure SQL Managed Instance. It works by backing up the DB on the source, moving the backup to the cloud, and restoring the backup.

Azure Backup

Today, SQL Server can backup from the SQL VM (Azure or on-prem) to a storage account in Azure. It’s all managed from SQL Server. Very distributed, no centralized reporting, difficult/no long-term retention.  Very cheap.

Azure Backup will offer centralized management of SQL Backup in an Azure VM. In preview today. Managed from the Recovery Services Vault. You select the type of backup, and a discovery will detect all SQL instances in Azure VMs, and their databases. A service account is required for this and is included in the gallery images. You must add this service for custom VMs. You then configure a backup policy for selected DBs. You can define a full backup policy, incremental, and transactional backup policy with SQL backup compression option. The retention options are the familiar ones from Azure Backup (up to 99 years by the looks of it). The backup is scheduled and you can do ad-hoc/manual backups as usual with Azure Backup.

You can restore databases too – there’s a nice GUI for selecting a restore date/time. It looks like quite a bit of work went into this. This will be the recommended solution for centralized backup of lots of databases, and for those wanting long term retention.

Backup Verification is not in this solution yet.

Global Azure BootCamp 2016 – Dublin

Microsoft and “the community” are partnering once again to run The Azure Global BootCamp. ICYMI, the boot camp is a one-day event in locations around the world, where Azure veterans share their knowledge with attendees at this free event.

This event is running in Dublin at 09:30 on Saturday April 16th at Microsoft Atrium Building B, at Carmanhall Road, in Sandyford Industrial Estate, Dublin 18.

The agenda is:

  • What’s new in Azure – Niall Moran (Microsoft)
  • Building and Deploying Azure App Services – Aidan Casey (MVP)
  • Migrating SQL to Azure, an Architectural Perspective – Bob Duffy (MVP)
  • Building Real World applications – Vikas Sahni
  • When disaster strikes – Aidan Finn (MVP)

My session will be focusing on the hybrid cloud solution where Azure acts as a DR site for your on-premises servers (physical, VMware, or Hyper-V).

The event page, with agenda and registration can be found here.

Microsoft News – 23 April 2015

I’ve been really busy either preparing training, delivering training, on customer sites, or prepping my two sessions for Ignite. Here’s the roundup of recent Microsoft news for infrastructure IT pros:

Hyper-V

Windows Server

Windows 10

Azure

Office 365

Intune

Miscellaneous

Microsoft News – 3 March 2015

Here’s the latest in the Microsoft world!

Hyper-V

System Center

Azure

Office 365

Miscellaneous

Microsoft News – 24 February 2015

Here is the latest news in the world of Microsoft infrastructure:

Hyper-V

Windows Server

System Center

Azure

Office 365

Miscellaneous

Microsoft News Summary – 22 August 2014

Here’s the latest news from the Microsoft wires. More new services have popped up on Azure, mostly for devs, but the SQL AlwaysOn template should be a massive time saver.

Hyper-V

Azure

Security

OS Deployment

Office 365

Microsoft News Summary – 19 August 2014

Does “fail fast” = “fail predictably often”? Automated testing of software for cloud services needs to be investigated and questioned. First we had the clusterfrak August updates for Windows. Then a significant chunk of Azure went offline.

image

Microsoft News Summary – 23 July 2014

Overnight Microsoft news is dominated by their Q4 2014 (MSFT  financial year is July-June and just started FY 2015) returns.

SQL Server Now Requires SA For A Cold Replica

When you replicate a virtual machine from site A to site B then typically the replica VM in site B is powered down. Note that I haven’t specified a hypervisor or replication method, so this article applies to Hyper-V and vSphere, and not just to Hyper-V Replica.

In the past, if you ran SQL Server in a VM in a production site, you could replicate that VM to a secondary site. If the replica VM was powered down, i.e. cold, then you were granted a free license for that cold VM. This has changed with the release of SQL Server 2014, as covered by this post. Now you must have Software Assurance (SA) to cover the cold VM’s license for SQL Server.

This brings SQL Server in line with Windows Server’s SA offsite cold replica benefit.

There are restrictions on failover in the secondary site:

  • You can perform a brief test failover (lasting 1 week) once every 90 days.
  • The production system in the primary site must be powered off to legally perform a failover.
  • You can power up the secondary site VM for a “brief time” during the disaster while the production system is running in the primary site.