AWS Database Blog

Scale modern serverless applications with Amazon RDS Proxy for SQL Server

Many applications, including those built on modern serverless architectures, can have a large number of open connections to the database server and may open and close database connections at a high rate, exhausting database memory and compute resources. Databases can also suffer from transient failures, impacting application availability. Finally, applications need to maintain database credentials to connect to databases, increasing risk of exposure and reducing security.

Today, we are pleased to announce that RDS Proxy now supports RDS for SQLServer. In this post, we talk about how Amazon RDS Proxy with RDS for SQL Server can help mitigate these issues to make your application more scalable, more resilient to database failures, and more secure.

SQL Server is a relational database management system developed by Microsoft. Amazon RDS for SQL Server makes it easy to set up, operate, and scale SQL Server deployments in the cloud. With Amazon RDS, you can deploy multiple editions of SQL Server (2014, 2016, 2017 and 2019) including Express, Web, Standard and Enterprise, in minutes with cost-efficient and re-sizable compute capacity. We have a decade of experience in operating and scaling database workloads for customers in the cloud. Some of benefits of running relational databases in Amazon RDS is that it removes the significant effort of managing and scaling databases, allowing you to focus on making your customers successful. We do that by making databases easier to administer, scalable, available and durable, and more importantly secure and compliant.

Benefits of RDS Proxy with RDS for SQLServer

RDS Proxy is a fully managed, highly available database proxy service. So, you don’t need to provision or manage any additional infrastructure to start using RDS Proxy. Moreover, RDS takes care of maintenance activities such as patching, and upgrading RDS Proxy on your behalf. In this section, we will cover how RDS Proxy with SQL Server can help make you application more scalable, more resilient to database failures, and more secure.

As modern-day applications move towards micro-services, serverless architectures have allowed them to scale rapidly. For example, Applications built on technologies, such as Serverless, PHP, or Ruby on Rails, may open and close database connections frequently to serve application requests. Establishing thousands of new connections frequently, consumes database resources, which otherwise could have been used to process your queries. In such cases, RDS Proxy maintains a pool of database connections between your application and the database to avoid unnecessary stress on database compute and memory from establishing new connections. RDS Proxy also shares database connections with multiple application connections when it is safe to do so. This especially helps application that maintain idle connections or have sufficient think time between different transactions in a session. By sharing the same database connections with multiple application connections, RDS Proxy can help reduce the number of connections to the backend database, which in turn helps reduce the CPU and memory consumption of your database.

To ensure business continuity, customer want to protect against transient database failures and improve application availability. Amazon RDS automatically detects transient database errors and performs failovers without intervention.

RDS Proxy further helps improve application availability by making failovers transparent and reducing database failover time. RDS Proxy makes failover transparent by automatically routing traffic to a new database instance while preserving application connections, reducing the need to write complex failure handling code. During database failovers, the application may experience increased latencies and ongoing transactions may have to be retried. RDS Proxy can help reduce failover times by bypassing DNS (Domain Name System) caches for Multi-AZ deployments. RDS for SQL Server supports Multi-AZ deployments for high availability either through SQL Server database mirroring or Always On availability groups.

When a failover occurs, the client has to detect the connection failure, discover a new primary, and reconnect to it as quickly as possible. When a client connects to a database using a DNS name, however, it first must resolve it to an IP address by querying a DNS server. The client then caches the responses. Per protocol, DNS responses specify the time to live (TTL), which governs how long the client should cache the record. With optimized TTL settings and a socket timeout of 10 seconds, RDS Proxy improves failover time by up to 36% over connecting to the Always On listener endpoint, 59% over connecting to the RDS endpoint directly, and 83% over connecting to a SQL Server database mirroring endpoint.

The following test is done on an m5.xlarge RDS for SQL Server instance, which demonstrates improvements on timeouts with Amazon RDS Proxy for SQL Server. The X-Axis represents attempt number of failovers and the Y-Axis represents time in milliseconds.

Using RDS Proxy you can make your application more secure by using AWS Identity and Access Management (IAM) authentication for connecting to the database, thereby removing the need to store database credentials in the application code or static configuration files.

Solution overview

Your application can be running in PHP, Ruby, or .NET, or running in a serverless application like on AWS Lambda or on containers. Your applications connect to the RDS Proxy, which in turn connects to your backend RDS SQL Server database. RDS Proxy isn’t a single instance; it’s a highly available multi-instance proxy that is spread across multiple Availability Zones to give you the high availability and scale that you would expect from a serverless service. The following diagram illustrates how RDS Proxy works.

Set up network prerequisites

Using RDS Proxy requires you to have a common Amazon Virtual Private Cloud between your RDS DB instance and RDS Proxy. This VPC should have a minimum of two subnets that are in different Availability Zones. Your account can either own these subnets or share them with other accounts. For more information about VPC sharing, see Share your VPC with other accounts. Your client application resources such as Amazon Elastic Compute Cloud (Amazon EC2), Lambda, or Amazon Elastic Container Service (Amazon ECS) can be in the same VPC or in a separate VPC from the proxy. Note that if you’ve successfully connected to any RDS DB instances from within the same VPC or in a separate VPC, you already have the required network resources.

If you’re just getting started with Amazon RDS for SQL Server, you can learn the basics of connecting to a database by following the instructions to set up an environment in Create and Connect to a Microsoft SQL Server Database with Amazon RDS.

You can also create multiple RDS Proxy endpoints, each with its own VPC settings, and enable connectivity across multiple shared VPCs. For example, you can create a proxy endpoint in a shared VPC to selectively grant access only to your proxy instead of allowing access to all the resources within the proxy’s VPC. To learn more about proxy endpoints, refer to Using Amazon RDS Proxy Endpoints.

Security

You must store your database secrets in AWS Secrets Manager, which is an encrypted secret store. With Secrets Manager, you can manage and retrieve database credentials. You just make an API call from your application to retrieve the secrets and start the connection. Another added layer of control is that you can use IAM authentication with RDS Proxy. It’s much more secure than connectivity with a user name and password. You can manage the credentials your application is able to use on the proxy with Secrets Manager. You can also remove the hard-coded credentials in the code by replacing them with get secrets calls or using the IAM auth token.

You can use the following commands to create the proxy using AWS Command Line Interface (AWS CLI):

// create a secret for user credential that you use on RDS instance
// One per each credential you intend to use with proxy
aws secretsmanager create-secret \
  --name sqlserver-creds --description "db admin user" \
  --secret-string '{"username":"master","password":"**********"}'
   --region us-west-2
  
// create proxy role
aws iam create-role --role-name rds-proxy-role \
  --assume-role-policy-document '{"Version":"2012-10-17","Statement":
  [{"Effect":"Allow","Principal":{"Service":["rds.amazonaws.com"]},
  "Action":"sts:AssumeRole"}]}'
// add secrets reader policy to the role
aws iam put-role-policy --role-name rds-proxy-role \
  --policy-name rds-proxy-secret-reader-policy --policy-document
   '{"Version":"2012-10-17","Statement":[{"Sid":"getsecretvalue","Effect"
   :"Allow","Action":["secretsmanager:GetSecretValue","kms:Decrypt"],
   "Resource":"*"}]}'
  
// create proxy instance
aws rds create-db-proxy --db-proxy-name test-proxy --engine-family SQLSERVER \
--auth Description=master,AuthScheme=SECRETS,SecretArn=arn:<arn details>:<acc>:secret:sqlserver-creds-ConshD,IAMAuth=DISABLED \
--role-arn arn:aws:iam::<acc>:role/rds-proxy-role --vpc-subnet-ids subnet-XXXXX subnet-XXXXX subnet-XXXXX subnet-XXXXX --region us-west-2

// register rds instance
aws rds register-db-proxy-targets --db-proxy-name test-proxy
 --db-instance-identifiers test-instance

Create an proxy with RDS for SQLServer

To manage connections for a specified set of DB instances, you can create a proxy. The Amazon RDS Proxy is available for Amazon RDS for SQL Server, Amazon Aurora with MySQL compatibility, Amazon Aurora with PostgreSQL compatibility, Amazon RDS for MySQL, Amazon RDS for MariaDB and Amazon RDS for PostgreSQL. Here we focus on RDS Proxy support for RDS SQL Server.

  1. On the Amazon RDS console, choose Proxies in the navigation pane.
  2. Choose Create proxy.
  3. For Proxy configuration, provide information for the following settings:
    1. Proxy identifier – Specify a name of your choosing, unique within your AWS account ID and current AWS Region.
    2. Engine compatibility – Choose SQL Server.
    3. Require Transport Layer Security – Choose this setting if you want the proxy to enforce TLS/SSL for all client connections. When you use an encrypted or unencrypted connection to a proxy, the proxy uses the same encryption setting when it makes a connection to the underlying database.
    4. Idle client connection timeout – Choose a time period that a client connection can be idle before the proxy can close it. The default is 1,800 seconds (30 minutes). A client connection is considered idle when the application doesn’t submit a new request within the specified time after the previous request completed. The underlying database connection stays open and is returned to the connection pool. Therefore, it’s available to be reused for new client connections. Consider lowering the idle client connection timeout if you want the proxy to proactively remove stale connections. If your workload is spiking, consider raising the idle client connection timeout to save the cost of establishing connections.
  4. For Connectivity, provide information for the following:
    1. Secrets Manager secrets – Choose at least one Secrets Manager secret that contains DB user credentials for the RDS DB instance that you intend to access with this proxy.
    2. IAM role – Choose an IAM role that has permission to access the Secrets Manager secrets that you chose earlier. You can also use the AWS Management Console to create a new IAM role.
    3. IAM authentication – Choose whether to allow or disallow IAM authentication for connections to your proxy. The choice of IAM authentication or native database authentication applies to all DB users that access this proxy.
    4. Subnets – This field is prepopulated with all the subnets associated with your VPC. You can remove any subnets that you don’t need for this proxy. You must leave at least two subnets.
  5. Provide additional connectivity configuration:
    1. VPC security group – Choose an existing VPC security group. You can also use the console to create a new security group. This security group must allow access to the database the proxy connects to. The same security group is used for ingress from your applications to the proxy, and for egress from the proxy to the database. For example, suppose that you use the same security group for your database and your proxy. In this case, make sure that you specify that resources in that security group can communicate with other resources in the same security group. When using a shared VPC, you can’t use the default security group for the VPC, or one that belongs to another account. Choose a security group that belongs to your account. If one doesn’t exist, create one. For more information about this limitation, see Limitations.
  6. Optionally, provide advanced configuration:
    1. Enable enhanced logging – You can enable this setting to troubleshoot proxy compatibility or performance issues. When this setting is enabled, RDS Proxy includes detailed information about SQL statements in its logs. This information helps you debug issues involving SQL behavior or the performance and scalability of the proxy connections. The debug information includes the text of SQL statements that you submit through the proxy. Therefore, only enable this setting when needed for debugging, and only when you have security measures in place to safeguard any sensitive information that appears in the logs. To minimize overhead associated with your proxy, RDS Proxy automatically turns this setting off 24 hours after you enable it. Enable it temporarily to troubleshoot a specific issue.
  7. Choose Create Proxy.

Connect to RDS Proxy

After you create a proxy, you have access to its DNS endpoint to connect your application. RDS Proxy uses the SQL Server default port 1433. You can retrieve the proxy endpoint detail either on the Amazon RDS console on the details page for the corresponding proxy, or with the AWS CLI via the describe-db-proxies command. See the following example code:

# Add —output text to get output as a simple tab-separated list.
$ aws rds describe-db-proxies —query '*[*].{DBProxyName:DBProxyName,Endpoint:Endpoint}'
[
[
{
"Endpoint": "the-proxy.proxy-demo.us-east-1.rds.amazonaws.com",
"DBProxyName": "the-proxy"
},
{
"Endpoint": "the-proxy-other-secret.proxy-demo.us-east-1.rds.amazonaws.com",
"DBProxyName": "the-proxy-other-secret"
},
{
"Endpoint": "the-proxy-rds-secret.proxy-demo.us-east-1.rds.amazonaws.com",
"DBProxyName": "the-proxy-rds-secret"
},
{
"Endpoint": "the-proxy-t3.proxy-demo.us-east-1.rds.amazonaws.com",
"DBProxyName": "the-proxy-t3"
}
]
]

You can set up Secrets Manager secrets containing the user names and passwords, and authorize RDS Proxy to retrieve the credentials from Secrets Manager. The IAM authentication applies to the connection between your client program and the proxy. The proxy then authenticates to the database using the user name and password credentials retrieved from Secrets Manager. You can configure multiple secrets in RDS Proxy; for more information, refer to create-db-proxy.

For SQL Server, we need to use the appropriate token property for each driver: accessToken on JDBC, sql_copt_ss_access_token for ODBC, or AccessToken for .NET SqlClient. Other tools that don’t support such properties won’t be able to use the IAM authentication method. As of this writing, we don’t support AD authentication.

Monitoring RDS Proxy for RDS SQL Server

You can monitor RDS Proxy using Amazon CloudWatch. CloudWatch collects and processes raw data from the proxies into readable, near-real-time metrics. To find these metrics on the CloudWatch console, choose Metrics in the navigation pane, then choose RDS, and choose Per-Proxy Metrics. For more information, see Using Amazon CloudWatch metrics.

Clean up the environment

In the following steps, you clean up the resources you created in this tutorial.

  1. On the Amazon RDS console, choose Proxies in the navigation pane.
  2. Select your proxy and on the Actions menu, choose Delete.
  3. In the dialog box, enter delete me and choose Delete.
    The status of the proxy changes to Deleting. When complete, the proxy is removed from the list.To delete a DB proxy with the AWS CLI, use the command delete-db-proxy. To remove related associations, use the deregister-db-proxy-targets command. See the following code:

    aws rds delete-db-proxy --name proxy_name
aws rds deregister-db-proxy-targets
    --db-proxy-name proxy_name
    [--target-group-name target_group_name]
    [--target-ids comma_separated_list]       # or
    [--db-instance-identifiers instance_id]       # or
    [--db-cluster-identifiers cluster_id]
  4. On the Secrets Manager console, select your secret.
  5. On the Actions menu, choose Delete secret.
  6. If you created a new EC2 instance, RDS instance, and corresponding security groups for this tutorial, delete those resources as well.

Summary

RDS Proxy allows applications to pool and share connections established with your database, improving database efficiency and application scalability. RDS Proxy with RDS for SQLServer improves failover time by up to 36% over connecting to the Always On listener endpoint, and by up to 59% improvement over connecting to the RDS endpoint directly. RDS Proxy improves failover time by up to 83% when connecting to a SQL Server database mirroring endpoint. And, RDS Proxy allows you to use IAM Authentication so that you don’t need to store credentials in your application, improving application security. In this post, we showed how to set up an RDS for SQL Server proxy and discussed these potential benefits in detail.

Try out this solution in your RDS for SQL Server instance. If you have any comments or questions, leave them in the comments section.

About the Authors

Sudarshan Roy is a Senior RDS Specialist Cloud Solution Architect with the AWS Worldwide Database Services Organization (DBSO). His core area of expertise is in designing, building, and implementing Database Modernization Platform for enterprise customers with AWS RDS Services. In his spare time, he loves to play cricket and spend time with his family.

Lakshman Thatisetty is a Database Specialist Cloud Solutions Architect with Amazon Web Services. He works with AWS customers designing customer solutions on database projects, helping them migrate and modernize their existing databases to the AWS Cloud as well as orchestrate large-scale migrations in AWS.