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.

mydc01

 

mydco2

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:

wsfc1

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.

wsfc2

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:
wsfc3
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

sql1

 

3.  Launch SQL Server Management Studio.  Add new Security Login for NTAuthority\System , go to SecurablesGrant: Alter any availability group, connect SQL, view server state and installer account with SysAdmin Server Role.

sql2

4. Change the SQL Service Account from NTService\MSSQLSERVER. In this case: svc_sql1 for AZSEDB001 and svc_sql2, svcsql3 for AZSEDB002 and AZUSDB001

sql3

 

AAG Steps:

1. Attach extra disk on AZSEDB001, Format the drive and Create a folder : backup on AZSEDB001. Share backup folder as below:

sql4

 

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

sql5

 

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

sql7

6. Click Options and select Restore with No Recovery in Recovery State and then click Ok to restore the database

sql8

7. Now the fun stuff, We will run AAG Wizard: Right click the AlwaysOn High Availability on SSMS and follow the wizard

sql9

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:

sql10

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:

sql11

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

sql12

 

 

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:

sql15

Create a dependency on the listener name resource. Right click the Availability Group and click Properties:

sql16

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.

 

 

Category:
Architecture, Azure Infrastructure, Cloud Infrastructure, Technology
Tags:
, , ,

Join the conversation! 1 Comment

  1. […] Part 2 we will deep dive on how to configure the SQL AAG on across both VNET […]

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: