Transcript Document

ALWAYSON AVAILABILITY GROUPS ON
AZURE – LESSONS FROM THE FIELD
101 quick overview
• Azure
• Storage account
• IOPs – number of I\O operations per sec
• Latency the time to get first response from the call
•
•
•
•
•
Affinity group – Location proximity
Availability group – disaster protection
Cloud service – external IP address allowing accesses to a group of servers
Endpoint – opening in the cloud service for a specific port
ACL –Accesses List ( security replacment for firewalls at network level )
• Always-On
• Windows Cluster based
• Replication\log-shipping like system:
• Two modes synchronized and asynchronized
• One primary server up to 3 secondary
• One listener resource used for load balancing connection between one
master and the slaves creating a scalability affect connection string property
“ApplicationIntent=READWRITE”
Azure Consideration
Storage
• Shared with everyone
• Virtual drives connected to a storage account
• Maximum drive size 1T
• Redundancy
• Geo vs. local
• Good latency 25ms
• Based on SSD but slower then your hard drive
• There is a 500iops limits but not guaranteed
• Monitoring
• Read Write priority
• Drive D
Storage affect
Scale 0-24 On Azure
Scale 0-2.2 On NetApp
Storage solution
DO :
•
•
•
•
Create Storage account per SQL server installation
Remove geo redundancy
More memory less iops
Maximum number of drives ( 2XCPU )
• Build the file groups on as many drives as possible
• Rebuild indexes to spread drives load equally
Don’t DO :
• Do not prioritize disk performance
• Do not try to increase iops by creating Logical raid arrays in OS
level
Storage
• Create a storage account per server
• Place the sql servers in the same affinity group of as the storage account
• Set is to local redundancy
Azure Consideration
Network
• One ip address per server
• Can not add your own hardware to azure .
• No load balancer as software allowed , can not add load balancing software.
• Cloud service as an accesses point as a load balancer I.E. Listener is on an
external IP address . All communication between the app servers and the DB is
on a public network
• By default new server get the DHCP service from Azure DHCP
• Every restart might result with a new ip address, so the sql server should be
registered at a your internal DNS\DHCP server ( in MS environment the Dc
servers )
• The clients must reside on a different cloud service ( to support direct server
return)
• Security
• No firewall, just ACLs
• Latency
• To utilize the always on connection is done on the public network
• Change all communication that does not require the LB set to the
internal IP address
• Heartbeat failures
Network Set up
• Create network structure before you start
• Set a specific subnet for the DB
• Create a specific subnet for your application server groups. Don’t
mix the groups !!!
End Point
• Create a new load balanced end point
• For security select a different port then 1433 , disabling RDS
• Notice the prob port
End Point
• Add a additional nodes to the end point
• Do not add your primary selected node
Azure Consideration
Always on
• Servers
• A7 A8 A9 the bigger the better
• Place server and storage at the same location
• Availability set
• Must setup a witness servers for two node cluster
• Security must – set ACL roles on listener cloud service
• Regular azure update every two weeks might result in server reboot
• Select your failover scenario
• Change the limit on the number of recovery attempts
• Sync method
• Sync -> high storage demand
• Async -> data integrity in failover
Azure Consideration
Always on
• Only 2 node cluster is automatically supports
• To support 3+ nodes requires special configuration
• Facilitate your recovery in case of disaster
• End point test port XXXX it is not aware of the DB status
• Set the routing to the two primary server
• Remove server from cloud service
Servers
• Select a memory enhanced server and the sql version from an image
Server Network and location
•
•
•
•
Create a dedicated cloud service for your always-on cluster
Select the subnet otherwise you will get a random ip address
Select the specific storage account
Create\join an availability set
Create using power-shell
# set the storage acount to use
Set-AzureSubscription -SubscriptionName 'Fixya Azure Main' -CurrentStorageAccount
dbao1
Get-AzureSubscription -Current
#Deploy a new VM and join it to the domain
#------------------------------------------#Specify Fixya DNS IP (10.10.11.4)
$dns1 = New-AzureDNS -name 'Fixya-dns' -IPAddress '10.11.0.13'
$dns2 = New-AzureDNS -name 'Fixya-dns2' -IPAddress '10.11.0.12'
#Get Latest Image
#-----------------# Family
$imageFamily = "SQL Server 2012 SP1 Enterprise on Windows Server 2012"
# Location
$location = "East US"
# Get Latast Image
$images = Get-AzureVMImage `
| where { $_.ImageFamily -eq $imageFamily } `
| where { $_.Location.Split(";") -contains $location} `
| Sort-Object -Descending -Property PublishedDate
Create using power-shell
# Network Config
$AG = 'FixyaAffinity'
$vnet = 'Fixya-Azure-10.11.X.X'
$subnet = 'DB'
$image = $images[0].ImageName
# OS Config
$LocalAdminUsername = 'GuyAdmin'
$localPassword = 'GuyFixY@'
$DomainAdminUsername = 'guyAdmin'
$DomainPassword = 'GuyFixY@2'
$size = 'A7' # <-- Size
$domain = 'FixyaProd'
$fulldomain = 'FixyaProd.local'
#VM Configuration
$vmName1 = 'WAZ-guy3' # <---VM Name
$ServiceName = 'WAZ-DB' #<-- Service Name
#Create VM Config Files
$vmConfig1 = New-AzureVMConfig -Name $vmName1 -InstanceSize $size -ImageName
$image | Add-AzureProvisioningConfig -WindowsDomain -AdminUsername
$LocalAdminUsername -Password $localPassword -Domain $domain -DomainUserName
$DomainAdminUsername -DomainPassword $DomainPassword -JoinDomain $fulldomain |
Set-AzureSubnet -SubnetNames $subnet
# Create Server
New-AzureVM -ServiceName $ServiceName -AffinityGroup $AG -VNetName $vnet -VMs
$vmconfig1
–DnsSettings $dns1, $dns2
Always-On setup scripts
• Routing and end point settings
ALTER AVAILABILITY GROUP [<name>]
MODIFY REPLICA ON N’ WAZ-DB10' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [<name>]
MODIFY REPLICA ON N‘WAZ-DB10' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WAZ-DB10.fixyaprod.local:1433'));
ALTER AVAILABILITY GROUP [<name>]
MODIFY REPLICA ON N‘WAZ-DB10'
WITH (ENDPOINT_URL = 'TCP://WAZ-DB10.fixyaprod.local:5022')
Change routing options make the two primary servers first on
the list
ALTER AVAILABILITY GROUP [<name>]
MODIFY REPLICA ON N'WAZ-DB10‘
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WAZ-DB11','WAZ-DB12')));
ALTER AVAILABILITY GROUP [<name>]
MODIFY REPLICA ON N'WAZ-DB11‘
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WAZ-DB10','WAZ-DB12')));
ALTER AVAILABILITY GROUP [<name>]
MODIFY REPLICA ON N'WAZ-DB12‘
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WAZ-DB10','WAZ-DB11')));
Check primary location
If exists ( SELECT 1 FROM sys.dm_hadr_availability_replica_cluster_states AS RCS
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id
WHERE ARS.role_desc = 'PRIMARY' and replica_server_name = @@SERVERNAME)
begin
• Close the secondary cloud service
• Try to failover
• ….
End
ACL
•
•
•
•
•
ACL on Load Balanced set can not be set by GUI
ACL is per VM
Has to be set on all VMS
When joining a load balanced set the ACL setting are copied to the machine
ACL setting are in overwrite mode
#Remove-Variable acl1
$ServiceName = "waz-db"
$LBSetName = "AlwaysonLB"
$vmname = "WAZ-DB11"
$aclendpointname = "Alwayson"
$ApplicationCloudServiceIPsubnet = "23.96.10.175/32"
$ordername = 200
$description = "Remote website SubNet ACL config"
$acl1 = Get-AzureVM -ServiceName $ServiceName -name $vmname | GetAzureAclConfig -EndpointName $aclendpointname
#Print ACL
$acl1
#add aditional
Set-AzureAclConfig –AddRule –ACL $acl1 –Order $ordername –Action Permit –
RemoteSubnet $ApplicationCloudServiceIPsubnet –Description $description Verbose
Set-AzureLoadBalancedEndpoint –ServiceName $ServiceName –LBSetName $LBSetName Protocol tcp –LocalPort 1423 –PublicPort 1111 –ProbePort 1423 -ProbeProtocolTCP
-DirectServerReturn $false –ACL $acl1 -verbose
ACL PrintOut
RuleId
:0
Order
:0
Action
: permit
RemoteSubnet : 23.96.10.175/32
Description : Remote IIS SubNet ACL config
RuleId
:1
Order
:1
Action
: permit
RemoteSubnet : 23.96.2.87/32
Description : Remote API SubNet ACL config
RuleId
:2
Order
: 300
Action
: permit
RemoteSubnet : 23.96.10.183/32
Description : Remote Search SubNet ACL config
…
The solution
• Read the installation articles follow the
installation to the letter
•
•
•
•
•
•
•
•
Set affinity group and subnet for the sql servers
Set storage account per SQL server instance
Set all internal communication to use full server names .
Change the routing two 2 primary servers
Set the correct sync settings
Create an additional Cloud service (LB) for 3+ node solution
Set security Acls on all cloud services
Add monitoring for primary server
Important links
• Cluster and always-on set up
http://msdn.microsoft.com/en-us/library/azure/dn249504.aspx
• Listener setup
http://msdn.microsoft.com/en-US/library/azure/dn425027.aspx
• Configuring always on secondary servers
http://blogs.msdn.com/b/igorpag/archive/2013/09/02/sql-server-2012-alwaysonavailability-group-and-listener-in-azure-vms-notes-details-and-recommendations.aspx
• Sql server performance optimization
http://blogs.msdn.com/b/sqlcat/archive/2013/06/17/performance-guidance-for-sqlserver-in-windows-azure-virtual-machines.aspx