(upbeat music)
- Hello, I'm Noureddine,
a cloud support engineer here
at the AWS office in Dallas.
Sometimes customers ask me
how do I create a SQL server
always-on availability group cluster on EC2?
So let me walk you through the process.
Prerequisites to configure a Windows cluster
and always-on availability group
with SQL group listener.
Deploy two EC2 Window server instances
across availability zones
inside a virtual private cloud VPC.
As part of our EC2 to best practice,
we want our customers to deploy their critical applications
across AZs to improve fault tolerance.
Configure secondary EBS volumes
to host SQL server master data file, log data file,
and SQL backup files.
AWS recommends provision IOPS SSD (io1) EBS volumes
for large SQL server database workloads.
Active directory domain
for our SQL server always-on cluster.
Make sure that you have permissions
to create active directory objects in your domain.
I also recommend that you deploy
your cluster nodes in private subnets
so that you can use remote desktop protocol
or RDP to connect from a jump server
to your cluster node instances.
In this demonstration,
I already launched two EC2 Windows server instances
with SQL server 2016 enterprise edition
in two different subnets and availability zones.
I also changed host names
and joined my EC2 Windows server instances
to my active directory domain.
Keep in mind
that AWS does not support Windows server failover clustering
on the same subnet.
I also deployed as secondary EBS volume
to host my SQL server master data file, log data file,
and SQL backup files.
The storage configuration might be different
in your environment.
Before we can create a Windows cluster
and prepare each SQL server instances
for always-on high availability,
I recommend that you configure the networking
for each cluster node instance ENI
or elastic network interface.
We need two additional secondary IPs
for each cluster node eth0 ENI.
The primary IP will be assigned to the Windows OS
and the secondary IP will be assigned
to Windows cluster core network name resource
and the treasury IP will be assigned
to the SQL group listener network name resource.
SQL server always-on
does not require a SQL group listener to function.
If you don't plan to deploy SQL group listener,
there's no need to add two secondary IPS
for each cluster node ENI.
Instead, you can add just one secondary IP
for each cluster node ENI.
Always-on and Windows clustering requires some ports
to be opened between the cluster nodes
and active directory domain controllers.
I already configured the security groups
and network ACL for my cluster nodes
and the domain controllers.
To configure the secondary IPs for each cluster node ENI,
I'll show you the following.
After logging in into your AWS management console,
be sure to select the applicable AWS region
that will host you always-on cluster.
Then navigate to the EC2 console,
then select instances.
Locate your EC2 cluster instances
and select eth0 ENI.
Select the ENI
and it will take you straight to the network interface.
Select actions manage IP addresses,
assign new.
You can select the specific IP or leave auto-assign.
Make sure to add two secondary IPs.
Ensure that you apply the same steps
on the other Windows instance
that will participate in the cluster.
After configuring the secondary IPS
for each cluster node ENI,
we'll use RDP to connect to our cluster node instances.
We'll follow the following steps
to create the Windows cluster
and SQL server always-on availability groups.
Part one will cover the windows cluster portion,
part two will cover
SQL server 2016 availability group set up portion.
Part one create a two-node Windows cluster.
Now I'm in my RDP connection.
First, open the network adapter advanced TCP IP settings
for IPV4
and then make sure that you check append primary
and connection specific DNS suffix.
Install the cluster feature on the nodes
from the server manager
or run the following PowerShell command.
Make sure that you install the cluster feature
on the other Windows server instance
that will participate in the cluster.
Note that windows server 2012 R2 won't ask you
to reboot after you install the cluster feature.
Now open CMD as administrator
then type clueadmin.msc to open the cluster manager.
From the cluster manager,
right click to create a cluster.
Click next,
select browse,
enter the cluster nodes host names
and select next.
You can validate the cluster or choose not to validate it.
I advise that you run a cluster validation
as Microsoft support me
and I support your SQL cluster
if it doesn't pass validation.
Give the cluster a name and select next.
Make sure to uncheck add all eligible storage to the cluster
and click next.
Wait for the cluster to complete and click finish.
Note that the cluster logs
and reports are located at the following path.
Percent sign system root
percent sign backslash cluster backslash reports.
Then configure a static IP for each subnet
for the cluster core network.
We'll use one of the secondary IPs associated with eth0 ENI.
We'll also bring the cluster record network name online.
Now let's create always-on availability groups.
Make sure that you run the SQL DB instances
with an active directory service account.
Configure AD integrated SQL login
that has admin permissions on both instances.
For example,
I granted myself Windows authenticated domain user
a SQL login
with sysadmin role on my test instances.
Please consult your database administrator.
Make sure the SQL browser is configured properly.
This is needed only for SQL server named instances.
First, open SQL server configuration manager
on the cluster nodes.
Select the properties of the SQL instance.
Always-on high availability.
Enable always-on high availability on the cluster nodes.
The always-on availability feature
will also detect if the node is part of a Windows cluster.
Make sure to do the same steps
on the other cluster node part of the cluster.
SQL server will ask you to restart the SQL instance
after you enabled the always-on
high availability groups option.
You can also restart the SQL service
from the configuration manager.
Make sure you restart SQL service
on all of the cluster nodes part of the cluster.
Then open SSMS SQL server management studio.
Log in to one of the SQL instances
with your Windows authenticated log in
that has access to the SQL instance.
Before creating the databases,
I recommend that you use the same MDF
and LDF directory file paths across the SQL instances.
Now create a test database
with full recovery model
and then take a full backup.
Always-on requires you to take a full backup
before creating the availability group.
After the full backup,
right click always-on high availability
and select new high availability group wizard.
Select next
and give a name for the availability group
then select next.
Select the database to add part of the availability group
and click next.
Pick your availability mode whether is synchronous
or asynchronous commit modes
and then add your primary and secondary replicas
then click next.
Skip the listener configuration for now.
Select your data synchronization preference and click next.
To use the automatic seeding
note that you need SSMS version 17 or higher.
If the always-on group validation succeed,
you'll see the option to select next
to complete the availability group wizard.
Click finish to complete the wizard.
Close.
In the cluster manager,
navigate to roles
where we see our availability group resource.
In SSMS,
you should see the availability group primary replica
and they're always on high availability.
To configure the sequel availability group listener
within SSMS under the primary replica
availability group listener,
right click to add a listener.
Give a name and for the port
you can select the default SQL server TCP port 1433.
For the network mode,
we will assign the second secondary IP address
as static IP addresses for the SQL listener.
Select okay to complete the listener configuration.
Please note that the static IP addresses need to match
the secondary IPS for each cluster node instance in the VPC.
The listener will show under the clustered resource.
Now test failover then make sure
that the failover completes without any issues.
When failing over do so from SSMS
because the FCM failover cluster manager MMC snap-in
is not aware of the synchronization status
of the availability replicas.
The primary will become the secondary replica
after failover.
Thanks for watching
and happy cloud computing
from all of us here AWS.
(upbeat music)