DTT

How to Set up SQL Server 2019 on AWS

Published: June 14, 2020 (Updated: Jun 14, 2020)
#SQL Server #AWS EC2 #Windows Server

Enjoying this content? Subscribe to the Channel!

Bypass RDS! Installing SQL Server 2019 Standard Edition on AWS EC2 (Beginner Tutorial)

Welcome to Darren’s Tech Tutorials!

Hey there, tech fans! Darren here, and today we are tackling a critical and very common challenge in the AWS world: deploying the latest database technology when AWS doesn’t quite have it ready in their managed services yet.

AWS RDS is fantastic for simplicity, but as of right now, if you want full control and access to features in SQL Server 2019 Standard Edition, you might find it’s not yet available via the easy RDS route.

The solution? We go direct!

In this comprehensive guide, we are going to walk step-by-step through installing SQL Server 2019 directly onto an AWS EC2 instance. By the end of this tutorial, you will have a fully operational, customized SQL Server instance ready for your most demanding applications. Let’s dive in!

Why We Are Choosing EC2 for SQL Server 2019

While managed services like RDS handle patching, backups, and high availability automatically, they also introduce a slight delay in offering the very latest versions or specific editions.

Since we are focused on deploying the cutting-edge SQL Server 2019 Standard Edition, leveraging EC2 gives us the full, unrestricted administrative control needed for this installation.

The key benefit of using EC2: Total control over the operating system, firewall settings, and the specific database configuration—perfect for advanced users or those who need a very particular setup.

Prerequisites and Preparation

Before we start launching servers, make sure you have the following ready to go:

  1. An Active AWS Account: Ensure you have the necessary IAM permissions to launch EC2 instances and modify Security Groups.
  2. Basic EC2 Knowledge: Familiarity with the AWS Management Console and the concept of Security Groups is helpful.
  3. SQL Server 2019 Installation Files: You will need access to the SQL Server 2019 Standard Edition setup files (either an installer download or ISO).

Step-by-Step Guide: Deploying SQL Server 2019 on EC2

The installation process can be broken down into five core phases, ensuring both the cloud infrastructure and the database software are configured correctly.

Phase 1: Launching the EC2 Windows Instance

First, we need the foundation—a Windows server instance capable of running SQL Server 2019.

  1. Navigate to the EC2 Dashboard: In the AWS console, click “Launch Instance.”
  2. Choose the AMI: Select a suitable Windows Server AMI. We recommend a recent version like Microsoft Windows Server 2019 Base or later. Note: You can choose an AMI that already has SQL Server installed, but for the purpose of a clean, manual 2019 Standard installation, a base Windows image is best.
  3. Select Instance Type: SQL Server requires significant resources. We recommend a minimum of a t3.large or m5.large instance type to ensure smooth installation and operational performance.
  4. Configure Network Settings: Ensure your instance is launched into the correct VPC and subnet. Crucially, pay attention to the Security Group (which we will configure later).
  5. Launch: Review your settings and launch the instance, generating or selecting a key pair.

Phase 2: Connecting via Remote Desktop Protocol (RDP)

Once the instance status checks pass (this usually takes 5-10 minutes):

  1. Retrieve the Password: In the EC2 console, select your instance, right-click, and choose Connect > RDP client > Get Password. Use your key pair file to decrypt the Windows administrator password.
  2. Connect: Use the RDP client on your local machine and the public IP address/DNS name of the EC2 instance, along with the decrypted password, to log in. You are now inside your remote server!

Phase 3: Executing the SQL Server 2019 Installation

With RDP access established, it’s time to install the database software.

  1. Transfer the Installer: Copy the SQL Server 2019 Standard Edition installation files (or ISO) onto the EC2 instance.
  2. Run Setup: Execute the setup.exe file to start the installation wizard.
  3. Select Installation Type: Choose “New SQL Server standalone installation.”
  4. Feature Selection: This is critical. Ensure you select the Database Engine Services. You can add other features like tools, replication, and R Services if needed.
  5. Instance Configuration: Choose either the Default Instance (MSSQLSERVER) or a Named Instance, depending on your architectural needs.

Phase 4: Server Configuration and Authentication

This is arguably the most important step for security and remote access.

  1. Server Configuration: Leave the service accounts at their default unless you are implementing a complex domain setup.
  2. Database Engine Configuration:
    • Under “Authentication Mode,” select Mixed Mode. This allows users to connect using both Windows Authentication (local/domain) and SQL Server Authentication (username/password).
    • Crucially, set a strong password for the sa (system administrator) account. This is how you will initially manage the server.
    • Add your current Windows user as a SQL Server administrator.
  3. Complete Installation: Follow the remaining prompts to finalize the installation. Once the setup reports success, SQL Server 2019 is running on your EC2 instance!

Phase 5: Configuring the AWS Security Group Firewall

By default, AWS Security Groups block all inbound traffic. We must explicitly open the standard port for SQL Server (TCP port 1433) so applications outside of the EC2 instance can connect.

  1. Return to the AWS Console: Go back to the EC2 Dashboard and find the Security Group attached to your running instance.
  2. Edit Inbound Rules:
    • Click on the Inbound Rules tab and select “Edit inbound rules.”
    • Add a Rule:
      • Type: Custom TCP
      • Port Range: 1433
      • Source: For initial testing, you might use “My IP” or 0.0.0.0/0 (for public access, though generally not recommended for production).
  3. Save Rules: Once saved, applications, SQL Server Management Studio (SSMS), or other clients can now connect to your EC2 instance’s public IP address using port 1433.

Conclusion: You Are Now Running SQL Server 2019!

That’s it! You successfully navigated the complexities of manual deployment and installed SQL Server 2019 Standard Edition directly onto an AWS EC2 instance. You now have total control over your database environment, giving you access to all the latest features, even when RDS hasn’t caught up.

This approach gives you maximum flexibility, power, and customization—the perfect combination for serious database work.

Ready to try this out yourself? Give this guide a shot and let me know how your deployment goes in the comments below!

If this tutorial helped you bypass the RDS bottleneck, please hit that Like button, Subscribe to Darren’s Tech Tutorials for more practical guides, and ring that notification bell so you don’t miss our next deep dive!

Happy deploying!