SQL Server High Availability using Amazon EC2

SQL Server Always Availability Configuration

SQL Server Always Availability Configuration are the high avaliability and disaster recovery solution solution introduced in SQL Server 2012.It provides more granular control to create highly avaliable database across muliple server replicas & maintainence tasks can be taken to other replicas.In addition secondary replicas can be used as view only or for reporting purpose.

SQL Server Always Availability uses similar database mirror technologies.but in SQL Server Always on avaliability includes some more features,like group of database can be failed over to a secondry replica of database and all the transactions are captured into the transaction log before they are send through the send/receive queue via an encrypted network.

Here i am creating 5 storage volumes though AWS management console.By using AWS we can add extra space to the volume easily and we have control to allocate the amount of space and assigning the avaliability zone,we need to create the storage volume to the same avaliability zone as Elastic Compute Cloud( EC2 ) server instance.we can create or allocate volume from 1GB to 16TB depend on our requirements.

Enable SQL Server Always on avaliability groups feature:-

Once the Windows failover cluster has been created, we can enable the Always on avaliability groups feature in the SQL Server, this need to be done on all the sql server instances where we are configuring the SQL Server Always on avaliability groups feature in the replica.

Below step need to be follwed to configure the feature :-

Step 1

Open SQL Server Configuration Manager. Right click the SQL Server Instance (AGREPLICA01 in this case, but we need to do all the servers instances) service to open the Properties.

Step 2

On the SQL Server Properties, go to the AlwaysOn High Availability tab and check the Enable AlwaysOn Availability Groups checkbox to enable the AlwaysOn Availability Groups feature and press OK.

Step 3 

On the SQL Server Properties, when we click ok then a confirm box will prompt you to restart the SQL Server service, then press click OK to confirm and restart the SQL Server service.

Step 4

To create and configure the SQL Server 2016 Always On Availability Group feature, open SQL Server Management Studio and connect to any of SQL Server replica instances.

Step 5
On the Object Explorer, expand the Always On High Availability folder and right click on the Availability Groups folder, then select the New Availability Group Wizard option. This will open a New Availability Group Wizard.

Step 6 
On the Specify Availability Group Options dialog box, provide the Availability group name and then click Next.

Step 7
On the Select Databases dialog, click on check boxes for each database you want to include in your Availability Group.  
Note : The databases must have to use the Full Recovery Model and at least one backup needs to be completed before joining the Availability Group and then Select Next.

Step 8
On the Specify Replicas dialog,Select Add Replica, to connect to the other SQL Server instances which are part of the Windows Failover Cluster.
Note : We can add nodes now that are part of Windows cluster.

In SQL Server 2016, up to 3 replicas can be added into Automatic Failover, 

Step 9

On the Endpoints tab, verify all the information.

Thanks