Wondering how to launch Microsoft SQL Server on EC2 Windows instance? We can help you.

At Bobcares, we often get requests to restore the site as a part of our Server Management Services.

Today, let’s see how our Support Engineers Launch Microsoft SQL.

 

How to launch Microsoft SQL Server on EC2 Windows instance?

Basically, New SQL environment deployments are classified under three categories:

• SQL Server stand alone
• SQL Server failover cluster instances (FCI)
• Finally, SQL Server Always On availability groups

 

Please note the following before launching SQL Server on your instance.

• The built-in availability form of clustering in Windows Server is enabled by a feature named Failover Clustering.

This feature allows you to build a Windows Server Failover Cluster (WSFC) to use with an availability group or FCI.

Always On is an umbrella term for the availability features in SQL Server and covers both availability groups and FCIs.

Always On isn’t the name of the Availability Group (AG) feature.

• The major difference between FCI and AG is that all FCIs require some sort of shared storage, even if it’s provided through networking.

The FCI’s resources can be run and owned by one node at any given time. AG doesn’t require that shared storage is also highly available.

Make sure you have replicas that are local in one data center for high availability, and remote ones in other data centers for disaster recovery, each with separate storage.

• An availability group also has another component called the listener.

The listener allows applications and end users to connect without needing to know which SQL Server instance is hosting the primary replica.

Each availability group has its own listener.

 

Today, let us see the steps followed by our Support Techs to launch Microsoft SQL Server on EC2 Windows instance.

Deploy SQL Server standalone

For SQL Server standalone deployment, you can use one of the AMIs provided by AWS with an included SQL license.

You must use the version of SQL that comes with the AMI. However, you can customize it for your needs using the setup included in the AMI.

Deploy SQL Server FCI

FCIs provide availability for the entire installation of SQL Server, known as an instance.

This means that everything inside the instance, including databases, SQL Server Agent jobs, linked servers, and so on, move to another server if the underlying server fails.

To provide a shared storage for FCI use one of the following solutions:

• Use Microsoft Storage Spaces Direct (S2D).
• Use FSx share. Deploy Amazon FSx file systems in the same Availability Zones as the SQL Server FCI cluster nodes. Additionally, deploy the file share witness into a third Availability Zone to keep the vote majority if there is a loss of one Availability Zone.

To deploy SQL Server FCI, do the following:

Before you begin, deploy Microsoft S2D or AWS FSx share using the instructions provided in the preceding section.

After deploying a two node cluster, deploy SQL Server on to it using the following steps:

• Firstly, connect to the first virtual machine by using RDP.
• In Failover Cluster Manager, make sure that all core cluster resources are on the first virtual machine. If necessary, move all resources to that virtual machine.
• Locate the installation media. If the virtual machine uses an Amazon Machine Image (AMI), then the media is located at C:\SQLServerSetup\Setup media.
• Then, select Setup. In the SQL Server Installation Center, select Installation.
• Next, select New SQL Server failover cluster installation. Follow the instructions in the wizard to install the SQL Server FCI.
• After you complete the instructions in the wizard, setup installs a SQL Server FCI on the first node. Connect to the second node using RDP.
• Open the SQL Server Installation Center, and then select Installation.
• Finally, select Add node to a SQL Server failover cluster. Follow the instructions in the wizard to install SQL Server and add the server to the FCI.

 

Deploy SQL Server Always On availability group (AG)

Configure the secondary IPs for each cluster node elastic network interface

Two secondary IPs are required for each cluster node eth0 elastic network interface.

Please note, if you don’t plan to deploy a SQL Group Listener, add only one secondary IP for each cluster node elastic network interface.

1. Firstly, open the Amazon EC2 console, and then choose the AWS Region that will host your Always On cluster.

2. Then, choose Instances from the navigation pane, and then select your EC2 cluster instance.

3. Next, choose the Networking tab.

4. Under Network interfaces, choose the Interface ID elastic network interface.

5. Then, select the network interface, and then choose Actions, Manage IP addresses.

6. Choose the arrow next to the network interface ID to expand the window, and then choose Assign new IP address. You can select a specific IP or leave the field as Auto-assign. Repeat this step to add a second new IP.

7. Choose Save, Confirm.

8. Repeat steps 1-7 for the other EC2 instance that will participate in the cluster.

 

Create a two-node Windows cluster

1. Firstly, connect to your EC2 instance using RDP with a domain account that has local Administrator permissions on both nodes.

2. On the Windows Start menu, open Control Panel, and then choose Network and Sharing Center.

3. Then, choose Change adapter settings from the navigation pane.

4. Select your network connection, and then choose Change settings of this connection.

5. Select Internet Protocol Version 4 (TCP/IPv4), and then choose Properties.

6. Next, choose Advanced.

7. On the DNS tab, choose Append primary and connection specific DNS suffixes.

8. Choose Ok, choose Ok, and then choose Close.

9. Repeat steps 1-8 for the other EC2 instance that will participate in the cluster.

10. On each instance, install the cluster feature on the nodes from the Server Manager, or run the following PowerShell command:

Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools

11. Open cmd as Administrator, and enter cluadmin.msc to open the Cluster Manager.

12. Open the context (right-click) menu for Failover Cluster Manager, and then choose Create Cluster.

13. Choose Next, and then choose Browse.

14. For Enter the object names to select, enter the cluster node hostnames, and then choose Ok.

15. Choose Next. You can now choose whether you want to validate the cluster. It’s a best practice to run a cluster validation.

If the cluster doesn’t pass validation, Microsoft might not be able to provide technical support for your SQL cluster.

Choose Yes or No, and then choose Next.

16. For Cluster Name, enter a name, and then choose Next.

17. Clear Add all eligible storage to the cluster, and then choose Next.

18. When the cluster creation is complete, choose Finish.

Please cluster logs and reports are located at the following path:

%systemroot%\cluster\reports

19. In the Cluster Core Resources section of Cluster Manager, expand the entry for your new cluster.

20. Open the context (right-click) menu for the first IP Address entry, and then choose Properties.

For IP Address, choose Static IP Address, and then enter one of the secondary IPs associated with eth0 elastic network interface. Choose Ok. Repeat this step for the second IP Address entry.

21. Open the context (right-click) menu for the cluster name, and then choose Bring Online.

 

Create Always On availability groups

1. Firstly, open SQL Server Configuration Manager.

2. Open the context (right-click) menu for the SQL instance, and then choose Properties.

3. On the AlwaysOn High Availability tab, select Enable AlwaysOn Availability Groups, and then choose Apply.

4. Open the context (right-click) menu for the SQL instance, and then choose Restart.

5. Repeat steps 1-4 on the other cluster node part of the cluster.

6. Then, open Microsoft SQL Server Management Studio (SSMS).

7. Log in to one of the SQL instances with your Windows authenticated login that has access to the SQL instance.

8. Create a test database. Open the context (right-click) menu for Databases, and then choose New Database.

9. For Database name, enter a name, and then choose Ok.

10. Open the context (right-click) menu for the new database name, choose Tasks, and then choose Back Up.
For Backup type, choose Full.

11. Choose Ok, and then choose Ok.

12. Open the context (right-click) menu for Always On High Availability, and then choose New Availability Group Wizard.

13. Choose Next.

14. For Availability group name, enter a name, and then choose Next.

15. Select your database, and then choose Next.

16. A primary replica is already present in the Availability Replicas window. Choose Add Replica to create a secondary replica.

17. For Server name, enter a name for the secondary replica, and then choose Connect.

18. For Availability Mode, decide which availability mode you want, and then choose Synchronous commit or Asynchronous commit for each replica.

19. Choose Next.

20. Choose your data synchronization preference, and then choose Next.

21. When the validation has succeeded, choose Next.

22. Choose Finish, and then choose Close.

Add a SQL Group Listener

1. Open SSMS, and then expand Always On High Availability, Availability Groups, [primary replica name].

2. Open the context (right-click) menu for Availability Group Listeners, and then choose Add Listener.

For Listener DNS Name, enter a name.
For Port, enter 1433.
Then for Network Mode, choose Static IP.

3. Choose Add.
For IPv4 Address, enter the second secondary IP address from one of the cluster node instances, and then choose Ok. Repeat this step, using the second secondary IP address from the other cluster node instance.

4. Choose Ok.

Test failover

1. Using SSMS, open the context (right-click) menu for the primary replica on the navigation menu, and then choose Failover.

2. Choose Next, and then choose Next.

3. Choose Connect, and then choose Connect.

4. Choose Next, and then choose Finish. The primary replica will become the secondary replica after failover.

 

Connect to SQL Server

After deploying SQL Server on your instance, you can connect to it using one of the following tools:

• Use SQL Server Management Studio (SSMS).
• Use SQL Server Configuration Manager.

 

[Still, have any queries on Windows? – We can help you.]

 

Conclusion

In short, today we saw how our Support Techs to launch Microsoft SQL Server on EC2 Windows instance