Access ApsaraDB RDS for SQL Server
In Function Compute, states are not shared between different execution environment instances. You can use a database to persist structured data and share states. You can use Function Compute to access a cloud database to perform operations such as querying and inserting data. This topic uses Python 3 as an example to describe how to access ApsaraDB RDS for SQL Server from within the same VPC or across different VPCs and regions.
Prerequisites
-
The sample code in this topic queries all data from a table named
users. You can modify the table name as needed, but make sure the table exists and contains at least one row of data.
Procedure
Step 1: Configure the database whitelist
Same VPC
-
Make sure your function and the database instance are in the same region.
-
Create the database instance in an availability zone supported by Function Compute. For more information, see Supported availability zones.
-
If your database instance is not in an availability zone supported by Function Compute, you can create a vSwitch in your VPC that is in a supported availability zone. Then, configure the service for your function to use this vSwitch ID. Because vSwitches within the same VPC can communicate over the private network, your function can access resources in other availability zones within that VPC. For detailed steps, see What do I do if I receive the "vSwitch is in unsupported zone" error?.
-
Log on to the ApsaraDB RDS console. In the top navigation bar, select a region. Then, click the target instance ID and go to the Basic Information page. Click View Connection Details next to VPC to view the network information of the SQL Server instance.
On the Database Connection panel, you can find the network type VPC, the Internal Address (for example,
rm-bp***.sqlserver.rds.aliyuncs.com), and the Internal Port (for example,3433). The server name format for connecting with SQL Server Management Studio isInternal Address,Port. Next to the internal address, click Configure whitelist to configure the whitelist. -
Log on to the Function Compute console and create a Python web function. On the function details page, go to the Configuration tab. Find Advanced Settings and click Modify on the right. In the Advanced Settings panel, find the Network section. Enable VPC access for the function and configure the target VPC resources.
NoteMake sure that the VPC configured for the function is the same as the one bound to the database instance.
In the network configuration section, VPC, vSwitch, and security group are required. For high availability, we recommend deploying vSwitches in two or more availability zones.
-
On the function details page, go to the Configuration tab. In the Network section, get the CIDR block of the vSwitch configured for the function.
-
Add the CIDR block of the vSwitch to the database whitelist.
ImportantYou must use an IP address whitelist to authorize the function to access the database. Do not use a security group. Using a security group may cause intermittent connection failures that can affect your services.
-
Log on to the ApsaraDB RDS console.
-
In the left-side navigation pane, choose Whitelists and security groups. On the Whitelist settings tab, find the target whitelist name and click Modify.
-
In the Modify whitelist group panel, enter the CIDR block of the vSwitch in the IPs in group field and click OK.
-
After the configuration is complete, the function can access the SQL Server database using the internal address of the database.
Across VPCs or regions
Different VPCs and regions are logically isolated. By default, you cannot access a database across VPCs or regions. To do this, you can assign an elastic IP address to your function. The system creates a NAT Gateway in the function's VPC, which allows the function to access the database over the internet using the elastic IP address.
-
Log on to the ApsaraDB RDS console. In the top navigation bar, select a region. Then, click the target instance ID and go to the Basic Information page. Click View Connection Details next to VPC to view the network information of the SQL Server instance.
-
Log on to the Function Compute console and create a Python web function. On the function details page, go to the Configuration tab. Find Advanced Settings and click Modify on the right. In the Advanced Settings panel, find the Network section. Turn on the Static Public IP Address switch and turn off the Allow Default NIC to Access Internet switch to activate the elastic IP address. Then, click Deploy.
NoteSetting Allow Default NIC to Access Internet to No activates the elastic IP address. Function Compute disables the default network interface to force internet traffic through the VPC-bound network interface.
-
On the function details page, go to the Configuration tab. In the Network section, get the elastic IP address configured for the function.
-
Add the elastic IP address to the database whitelist.
ImportantYou must use an IP address whitelist to authorize the function to access the database. Do not use a security group. Using a security group may cause intermittent connection failures that can affect your services.
-
Log on to the ApsaraDB RDS console.
-
In the left navigation bar, select Whitelists and Security Groups, and on the Whitelist Settings tab, find the target whitelist template name and click Modify on the right.
-
In the Modify whitelist group panel, enter the elastic IP address in the IPs in group field and click OK.
-
After the configuration is complete, the function can access the SQL Server database using the public address of the database.
Step 2: Access the database
-
Log on to the Function Compute console. In the function list, find the target function and go to its details page. Click the Code tab and enter the following sample code in the code editor.
from flask import Flask, jsonify import os import pymssql # You need to install the pymssql library first. from datetime import datetime app = Flask(__name__) # Global variable to store the singleton database connection. _mssql_connection = None # Create a database connection (singleton pattern). def getConnection(): global _mssql_connection try: # If the connection exists and is active, return it directly. if _mssql_connection is not None: try: # Test if the connection is valid (simple query test). with _mssql_connection.cursor() as cursor: cursor.execute("SELECT 1") # Simple query to test the connection status. result = cursor.fetchone() if result and result[0] == 1: return _mssql_connection except pymssql.OperationalError: # If the connection is broken, reset it. _mssql_connection = None # If the connection does not exist or is broken, create a new one. _mssql_connection = pymssql.connect( host=os.environ['MSSQL_SERVER'], port=int(os.environ['MSSQL_PORT']), user=os.environ['MSSQL_USER'], password=os.environ['MSSQL_PASSWORD'], database=os.environ['MSSQL_DATABASE'], charset='utf8' ) return _mssql_connection except Exception as e: print(f"ERROR: Unexpected error: Could not connect to SQL Server instance: {e}") raise @app.route('/', defaults={'path': ''}) @app.route('/<path:path>', methods=['GET', 'POST', 'PUT', 'DELETE']) def hello_world(path): conn = getConnection() try: with conn.cursor() as cursor: # Query all records from the users table. Change 'users' to your actual table name. cursor.execute("SELECT * FROM users") result = cursor.fetchall() columns = [desc[0] for desc in cursor.description] # Get the list of column names. # Convert the query result to a list of dictionaries. users = [] for row in result: user = {} for idx, column_name in enumerate(columns): value = row[idx] if isinstance(value, datetime): # Handle datetime fields. user[column_name] = value.strftime('%Y-%m-%d %H:%M:%S') else: user[column_name] = value users.append(user) if users: # Return a JSON response with all users. return jsonify(users), 200 else: # If no users are found, return a 404 error. return jsonify({'error': 'No users found'}), 404 finally: # No need to close the connection because it uses a singleton pattern. pass if __name__ == '__main__': app.run(host='0.0.0.0', port=9000) -
Install the required dependencies for the function by running
pip3 install -t . pymssql. Click Deploy to apply the changes. For detailed steps, see Install dependencies by using the Web IDE terminal in the console. -
On the Function Details page, go to the Advanced Settings section and click Modify. In the Advanced Configurations panel, find the Environment Variables section, configure the following environment variables, and then click Deploy.
Parameter
Value
Description
MSSQL_SERVER
rm-bp*****wn.sqlserver.rds.aliyuncs.com
The endpoint of the database instance. If you chose Same VPC, set this to the internal address of the database. If you chose Across VPCs or regions, set this to the public address of the database.
To find the endpoint, go to the Instance List in the ApsaraDB RDS console, select the region, and click the target instance ID. In the left-side navigation pane, click Database Connection to view the connection information in the Database Connection section.
MSSQL_PORT
1433
The port of the database instance.
MSSQL_USER
*****
The username for the database account.
MSSQL_PASSWORD
*****
The password for the database account.
MSSQL_DATABASE
*****
The name of the database.
-
On the function details page, go to the Code tab and click Test Function. After the function executes successfully, view the returned result.
The result shows that the
SELECT * FROM users;query was successful. Theuserstable contains four columns (id,name,age, andgender) and multiple records. The sample output shows one record where the values are1,Zhang San,20, andMrespectively. This indicates that the function has successfully accessed the SQL Server database and read the data.{ "age": 20, "gender": "M", "id": 1, "name": "Zhang San" }
More information
-
For more examples of accessing an ApsaraDB RDS for SQL Server database, see Connect to a SQL Server database from Function Compute by using Python.
-
For more information about how to view vSwitch information in Function Compute and how to add the CIDR block of a vSwitch to the whitelist of an ApsaraDB RDS for SQL Server database, see Configure network settings and Configure a whitelist for an SQL Server instance.
-
If the database connection fails, troubleshoot the issue based on the symptoms. For more information, see Common causes of database connection failures.
-
To use the Serverless Devs command-line tool to create a function and access an ApsaraDB RDS for SQL Server database, follow these steps.