Transcript Deploying Highly Available SQL Server in Windows Azure

Deploying Highly Available SQL
Server in Windows Azure
A Presentation and Demonstration by Microsoft
Cluster MVP David Bermingham
What is Azure?
 “Windows Azure is an open and flexible cloud platform that enables you to
quickly build, deploy and manage applications across a global network of
Microsoft-managed datacenters. You can build applications using any
language, tool or framework. And you can integrate your public cloud
applications with your existing IT environment.”
Azure IaaS
 “Scale on demand, only pay for what you use”
 “With Windows Azure, you can spin up new Windows Server and Linux virtual machines in
minutes and adjust your usage as your needs change. With our pay-as-you-go approach,
you only pay for what you use and there are never any penalties for changing your virtual
machine configurations.”
SQL on PaaS or IaaS?

Windows Azure SQL Database – Platform as a Service (PaaS)
Fully managed relational database service that delivers flexible
manageability, includes built-in high availability, offers predictable
performance, and supports massive scale-out.
 SQL Server in Windows Azure Virtual Machine – Infrastructure as a Service
(IaaS)
Standard Windows OS and any version of SQL Server, running on a VM in
Azure IaaS. Supports “scale-up”
SQL on PaaS or IaaS?
 Decision Tree
 New or existing application/service?
 Application/Service requirements
Domain joined?
SSIS, SSAS, SSRS?
 Database Size? 150 GB or larger?
 ScaleUp or ScaleOut?
Configurations
Configurations
Configurations
Availability Groups vs. Clusters
AlwaysOn
Availability
Groups
AlwaysOn
Failover
Cluster
Support SQL Standard Edition
0
4
Distributed Transactions
0
4
Unlimited Databases
0
4
Automated Client Redirection
2
4
Protects System Databases (Master, MSDB, etc.)
0
4
Eases Database Administration
0
4
Support more than 2-nodes in failover configuration
2
4
Eliminates SAN as a single point of failure
4
0
Supports Multisite Clusters
4
0
Readable Secondary
4
0
Azure Virtual Network
 “Windows Azure Virtual Network enables you to create a logically isolated
section in Windows Azure and securely connect it to your on-premises
datacenter or a single client machine using an IPsec connection. Virtual
Network makes it easy for you to take advantage of Windows Azure’s
scalable, on-demand infrastructure while providing connectivity to data and
applications on-premises, including systems running on Windows Server,
mainframes and UNIX.
When using Virtual Network you are backed by a 99.9% monthly SLA and
have flexibility to enable a range of hybrid IT scenarios.”
Create Virtual Network
Create Virtual Network
Create Virtual Network
Example of a Network without
a VPN connection
Editing the Virtual Network
Configuring Site-to-Site
VPN connectivity
Add new DNS Servers
As you add new DNS Servers,
update your Virtual Network
Creating a Site-to-Site VPN
Download the VPN Script
Configuration
Scripts for Cisco,
Juniper and
Microsoft RRAS
VPN devices are
downloadable
Create Virtual Network
Demonstration
Create Your First VM
Create Your First VM
Create Your First VM
Create Your First VM
 Cloud Service/Cloud Services Name
 Region/Affinity Group/Virtual Network
 Storage Account
 Availability Set
 Each server in different “rack”
 Power supply
 Switch
 Servers
 99.95% SLA
Connect to your VM
Provision First VM
Demonstration
Configure Cluster
 Add to VMs to Domain
 Enable Failover Cluster Feature
 Validate Cluster
 Configure Cluster
 Add File Share Witness to Quorum
 Configure Replicated Storage
 Install SQL Into Cluster
Add to Domain
Enable Failover Cluster Feature
Create The Cluster - Validation
Start by creating a single node cluster only
Create The Cluster – Virtual Cluster Object (VCO)
The IP address handed out by DHCP is the same
as the physical server, it must be changed before
the IP address can come online
Create The Cluster – Virtual Cluster Object (VCO)
Edit the IP Address
Choose an IP address at
the end of the DHCP
range
Create The Cluster – Virtual Cluster Object (VCO)
Bring the cluster name
resource online after it
you change the IP
address
Create The Cluster – Add The Second Node
Validation will warn you about having no
shared storage for the quorum disk. Usa
a File Share Witness instead
Create The Cluster – Add a File Share WItness
Quorums in Windows 2012 R2
 Dynamic Quorum (introduced in Windows 2012)
 Dynamic Witness
 Take Away – In Windows 2012 R2 it is ALWAYS recommended to configure a
witness, regardless of the number of nodes in the cluster.
Create The Cluster
Demonstration
Create Replicated Storage with DataKeeper
Using 3rd party cluster replication
solution, DataKeeper, replicate the data
volume(s) between the cluster nodes
Install SQL on the First Cluster Node
To minimize potential cluster creation problems, use the Advanced Cluster
Preparation and Completion options
Attach Extra Storage
Choose the DataKeeper Volume Resource
Specify the Virtual IP Address
Add the Second Node to the Cluster
Completed Cluster
Install SQL into the Cluster
Demonstration
Configuring AlwaysOn Availability Groups

SQL 2012/2014 Enterprise only

Steps


Install Windows Cluster

Install a Standalone SQL Server 2012

Use the domain account for the SQL service account

Open Port 1433 and 5022 or disable the Windows Firewall
Create a sample database

Set recovery model to Full

Back up this sample database

Enable the AlwaysOn feature on both instances using SQL Server Configuration Manager

Create an Availability Group using the Availability Group Wizard

Create an Availability Group Listener - http://support.microsoft.com/kb/2854082
Install Windows Cluster
 Same as described in the previous section on Failover Cluster Instance
 Install Node1
 Fix IP Rsource
 Add Node2
 Add File Share Witness
Change the Service Account
The MSSQLServer service account
needs to be a domain account
Windows Firewall
 Open Port 1433 and 5022 or disable the Windows Firewall
 1433 – SQL Server
 5022 - Replication
Enable AlwaysOn High Availability
Create AlwaysOn Availability Group
Demonstration
AG Listener in Azure
 The availability group listener is currently only supported on Windows
Server 2012 VMs.
 The client application must reside on a different cloud service than the one
that contains your availability group VMs. Windows Azure does not support
direct server return with client and server in the same cloud service.
 Only one availability group listener is supported per cloud service because
the listener is configured to use the cloud service IP address.
AG Listener in Azure
 Only configurable in PowerShell
 http://www.windowsazure.com/en-us/documentation/articles/install-configurepowershell/
 Tutorial
 http://msdn.microsoft.com/en-us/library/dn376546.aspx
 Automated Script
 http://gallery.technet.microsoft.com/scriptcenter/Create-Availability-Group-fff94cd5/
Create the AG Listener
Demonstration
What About Amazon EC2?
Windows Azure
Amazon EC2
Virtual Network
Virtual Private Cloud
Fault Domains
Availability Zones
Cloud Service
Elastic IP
Most Important Thing To Remember
 All Cluster IP addresses must be added to the VM as secondary addresses
through the EC interface
 Each cluster node must reside in a different Availability Zone, which means
different subnet as well
 Failover across subnets REQUIRES Enterprise Edition of SQL 2012/2014
 You must bring your own Enterprise license of SQL Server to the Amazon
Cloud
A Look at Amazon EC
Demonstration