Sofia Event Center 21-22 November 2013

1. Sofia Event Center 21-22 November 2013 DI7 SQL Sever 2014 Platform for hybrid cloud Margarita Naumova www.SQLMasterAcademy.com

2. Simplified cloud backup Manual or automatic At an instance level with point-in-time restore Measures database usage patterns to set backup frequency New migration wizard Easily migrate on-premises SQL Server to a Windows Azure VM Designed for users unfamiliar with Windows Azure Cloud disaster recovery Fast disaster recovery (low RTO) Easy to deploy and manage SQL Server in a Windows Azure VM Ideal for existing apps and dev/test new apps Full SQL Server functionality Full VM control Extend on-premises apps Cloud bursting Greater global reach Better isolation of internal assets Windows Azure SQL Database Service Ideal for new variable-demand apps Dynamic scale-out of database No patching of OS or DB Built-in HA with 99.9-percent SLA DB

3. Cloud VM and Deploying dB to Windows Azure

4. SQL Server VM in the Cloud • Windows Azure virtual machine gallery • Size • Disk • Memory • CPU • SW • Consider proper planning • You can scale further whenever needed

5. Windows Azure VM

6. Deploy databases to Windows Azure VM • What’s being delivered • New wizard to deploy databases to SQL Server in Windows Azure VM • Can also create a new Windows Azure VM if needed • Main benefits • Easy to use • Perfect for database administrators new to Azure and for ad hoc scenarios • Complexity hidden • Detailed Azure knowledge not needed • Almost no overhead: defining factor for time-to-transfer is database size

7. Backup to Cloud

8. Backup to Windows Azure Windows Azure VM Windows Azure storage On-premises server Box Windows Azure Blobs • On-site/off-site storage costs • Device management costs • Near “bottomless” storage • Off-site, geo-redundant • No provisioning • No device management • Media safety (decay-free) • Remote accessibility • 100TB per storage account • Max backups size supported is 1TB • Supported by UI, T-SQL, PS WA

9. Backup to cloud

10. Backup to Windows Azure Tool • What is it? • Stand-alone Tool that adds backup to Windows Azure capabilities and backup encryption to prior versions of SQL Server • Benefits • One Cloud Backup strategy across prior versions of SQL Server including 2005, 2008, and 2008 R2 • Adds backup encryption to prior versions, locally or in the cloud • Takes advantage of backup to Azure • Easy configuration

11. Managed Backup to Azure • What’s being delivered • Agent that manages and automates SQL Server backup policy • Main benefit • Large-scale management and no need to manage backup policy • Context-aware – for example, workload/throttling • Minimal knobs – control retention period • Manage whole instance or particular databases • Take advantage of backup to Azure • Inherently off-site • Geo-redundant • Minimal storage costs • Zero hardware management Example: EXEC smart_admin.sp_set_db_backup @database_name='TestDB', @storage_url=<storage url>, @retention_days=30, @credential_name='MyCredential', @enable_backup=1

12. SQL Server integration with Windows Azure Storage (SQL XI)

13. SQL Server Data & Log Files in Windows Azure Storage • What’s being delivered • Ability to move data & log files in Windows Azure Storage, while keeping the compute node of SQL Server on-premise • Transparent Data Encryption (TDE) is supported • Main benefits • No application changes required • Centralized copy of data and log files • Enjoy unlimited storage capacity in Azure Storage (built in HA, SLA, geo-DR) • Secure because TDE encryption key can be stored on-premise • Restore database is simply an attach operation

14. SQL Server Data & Log Files in Windows Azure Storage

15. SQL Server HA and DR Technologies

16. Failover Cluster • Instance level redundancy • Local or Remote Site • Presents VNN • Automatic Failover • Does not protect against data loss • Require Shared Storage, which is a single point of failure • Second server stays idle

17. Log Log Data Data Database Mirroring • Provide “a” redundant copy of database • Local or remote side • Works by sending TLog records • Connections are accepted only to the principal database • No VNN • Automatic failover with Witness Server Client 8. Commit Tran Witness 1. Begin Tran 4. Transfer to mirror 7. Acknowledgement 5. Write to Log 2. Write to Log 6. Log written 3. Log written Principal Mirror

18. Status and History Restore Job Restore Job Restore Job 3 3 3 2 2 2 Copy Job Copy Job Copy Job Log Shipping Monitor Server Instance • Provide multiple redundant copies of database • Local or remote side • Works by performing and transferring Log backups using jobs • Data loss is possible • NO Automatic failover • No role changing options i.e. one direction only Secondary Server Instances Primary Server Instance Backup Job 1

19. Failover Clustering and Database Mirroring Asynchronous Database Mirroring Secondary Data Center Primary Data Center SQL Server 2008 R2 Failover Cluster SQL Server 2008 R2 Failover Cluster Asynchronous Data Movement with Database Mirroring

20. Database Mirroring and Log Shipping Log Shipping Disaster Recovery Datacenter2 Primary Datacenter Log Shipping Witness SQL Server 2008 R2 Disaster Recovery Datacenter1 SQL Server 2008 R2 Database Mirroring SQL Server 2008 R2 Log Shipping Synchronous Data Movement with Database Mirroring

21. SQL Server 2012+ AlwaysOn: Features Availability Groups and Failover Cluster Instances rely on Windows Server failover clustering, which provides a robust and reliable high-availability platform

22. Client Failover using Virtual Name • Availability Group Virtual Name allow applications to failover seamlessly on availability group failover • Application reconnects using a virtual name after a failover to a secondary ServerA ServerC ServerB HRDB HRDB HRDB AGHR HRVNN Primary Primary Secondary Secondary Secondary Application retry during failover Connect to new primary once failover is complete and the virtual name is online -server HRVNN;-catalog HRDB

23. Readable Secondary Reports • Readable secondary allow offloading read queries to secondary • Close to real-time data, latency of log synchronization impact data freshness • BackupandDBCC CheckDB operations can be done on secondary SQLservr.exe SQLservr.exe Primary Secondary InstanceA InstanceB Database Log Synchronization DB1 DB2 DB1 DB2

24. Availability Group Scenarios Availability Group provides redundancy for databases on both standalone instances and failover cluster instances A A A A Direct Attached Storage local, regional and geo secondaries Shared Storage, regional and geo secondaries A A A Synchronize Asynchronize

25. Comparison of SQL Server High-Availability and Disaster-Recovery Solutions Microsoft Confidential

26. Cloud DR

27. Why Do We Need Cloud DR for SQL Server? • An event can cause on-premises SQL Server to become unavailable • Temporarily (e.g. gateway failure) • Permanently (e.g. flooding) • A disaster recovery site is expensive • Site rent + maintenance • Hardware • Ops

28. Typical Configuration • Deploy one or more secondary replicas for on-premises SQL Server • Replicas continuously synchronize • At best region: West US, East US, East Asia, Southeast Asia, North Europe, West Europe • Political considerations • Latency • Low TCO • VM and storage Windows Azure On-Premises Inverse Deployment Configuration Supported

29. Technologies Supported * Most customers use Async Mode (High Performance) to avoid impacting primary performance **AsyncMode only supports Force Failover to make DR (and potential data loss) a conscious decision

30. Windows Azure-Only: High Availability Solutions Database Mirroring AlwaysOn Availability Groups Backup and restore with storage blobs Microsoft Confidential

31. Windows Azure-Only: Disaster Recovery Solutions Database Mirroring Backup and Restore with Windows Azure Blob Storage Service Microsoft Confidential

32. Hybrid IT: Disaster Recovery Solutions AlwaysOnAvailability Groups Database Mirroring Log Shipping Backup and Restore with Windows Azure Blob Storage Service Microsoft Confidential

33. Considerations for SQL Server HADR in Windows Azure • High availability nodes in an availability set • WSFC cluster behavior in Windows Azure networking • Availability group listener support • Network Latency in Hybrid IT • Virtual disks with no read or write caching • Geo-replication not supported for data and log files on separate disks Microsoft Confidential

34. Complete and consistent data platform SQL Server 2014 Mission-critical performance Faster insights from any data Platform for hybrid cloud CUSTOMER SERVICE PROVIDER WINDOWS AZURE 1 CONSISTENTPLATFORM Development Management Data Identity Virtualization

35. Споделете вашата обратна връзка за тази сесия и за цялостната организация на конференцията http://aka.ms/incharge и участвайте в томболата за HTC 8S и други награди!