Part 1 can be found here.
In this Part 2 we will discuss:
- Create DC on Azure and confirm VNET to VNET connectivity
- SQL VMs
- Configure WSFC and lastly configure AAG
DC and Connectivity VNET to VNET
First thing first, we need VMs for the Domain Controller (DC) and SQL Server 2012. I will use my script below to create few VMs
I created 2 DC , one on each VNET: AZSEDC001 and AZUSDC001
I registered both as DNS on Azure. The next step , allow ICMP on wf.msc as we are going to test ping on both servers.
Great. Now we have confirmed the connectivity between both DC and connectivity between SEVNET and USVNET.
SQL VMs
Created 2 SQL VMs ( AZSEDB001 and AZSEDB002) under one Cloud Service (AZSEDB) on Azure-Backend Subnet of SEVNET . Domain Joined both SQL server
Configure WSFC
For this scenario, I created three extra accounts on AD:
1. kloud\kloudinstall – for failover cluster and AG. Give permission Allow for Read all properties and Create Computer Objects via AD. Assign Local Admin permission on both SQL Servers
2. kloud\svcsql1 and kloud\svcsql2
Next part, Add Failover Clustering feature on both servers and install the HotFix for Windows 2012 cluster-based node http://support.microsoft.com/kb/2803748
1. Create the WSFC Cluster:
2. Create multi – node cluster on azsedc001 (Add all VMs using Wizard and the Wizard will smart enough to detect multi-subnet) and do not choose require support from Microsoft for this cluster.
3. Configure Quorum File Share Witness on other machines. I configure it on SEVNET DC
4. Change the cluster IP address (WSFC will use azsedc001 IP: 10.0.1.4) to unused IP. I used 10.0.1.103 for SEVNET and 192.168.1.110 for USVNET
5. Bring the cluster online:
You can test failover to USVNET by using PowerShell command below:
Click here for more details regarding multi-subnet WSFC
Configure AAG
Prep:
1. Launch wf.msc to allow firewall inbound rules (All SQL Servers). Program: %ProgramFiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
2. Enable AlwaysOn Availability Group (all SQL servers): Launch SQL Server Configuration Wizard > SQL Server Services > SQL Server (MSSQLSERVER) > Tick Enable AlwaysOn Availability Group > Restart the Services
3. Launch SQL Server Management Studio. Add new Security Login for NTAuthority\System , go to Securables, Grant: Alter any availability group, connect SQL, view server state and installer account with SysAdmin Server Role.
4. Change the SQL Service Account from NTService\MSSQLSERVER. In this case: svc_sql1 for AZSEDB001 and svc_sql2, svcsql3 for AZSEDB002 and AZUSDB001
AAG Steps:
1. Attach extra disk on AZSEDB001, Format the drive and Create a folder : backup on AZSEDB001. Share backup folder as below:
2. Go to AZSEDB001, Run SQL Management Studio and create new Database: kloud1.
3. Take a full backup of the database: Right click kloud1 database > Tasks > Back Up. Remove the default destination to \\azsedb001\backup\kloud1.bak
4. Do the Transactional Backup: Use the same destination file path
5. Restore the full and transactional log backups on azsedb002 and azusdb001. On SQL Server Management Studio (SSMS), Right click databases and select restore database. Click Device and Add Backup Media, Backup File location: \\azsedb001\backup. Choose the backup database: kloud1.bak
6. Click Options and select Restore with No Recovery in Recovery State and then click Ok to restore the database
7. Now the fun stuff, We will run AAG Wizard: Right click the AlwaysOn High Availability on SSMS and follow the wizard
8. In AAG WIzard Specify Replica session, follow instructions as follow:
What we have here: Two replicas – one (AZSEDB002) in SEVNET and one (AZUSDB001) in USVNET. The details configuration:
Note: AZUSDB001 configured with Asynchronous data replication since AZUSDB001 hosted hundreds of miles away from Southeast Asia data centre, latency will hurt the performances.
9. In the Select Initial Data Synchronization page, select Join only and click Next since we have done backup and restore operations which is recommended as best practice especially for enterprise database
10. Follow the wizard, typical click – click – next. Igone the listener configuration warning. Finish.
The AAG dashboard by now:
More details can be found here.
Configure the Listener:
Next we will create the AAG listener:
1. Create load-balanced Azure VM endpoints for each Azure VM
2. Install KB2854082 for Windows Server 2008R2 and Windows Server 2012 cluster nodes.
3. Open the firewall ports to allow inbound rules Ports: 59999 specified earlier on Step 1.
4. Create the AG listener:
Open Failover Cluster Manager > Expand the AAG cluster name > Roles >
Right click the AG name and select Add Resource > Client Access Point
Click the Resources tab right click the listener > Properties > Note the IP Address Name and Network Name
Get the Cloud Service VIP on both SEVNET and USVNET. Run the script below on AZSEDB001
Once completed:
Create a dependency on the listener name resource. Right click the Availability Group and click Properties:
Launch SSMS > Go to AlwaysOn High Availability > Availability Groups > AAG Listener Name > Properties and specify Port: 1433
And that’s it. We have Highly Available SQL 2012 AAG across Azure VNET
Follow this link for more details how to configure AlwaysOn in Azure.
Comments are closed.