Access ApsaraDB RDS for SQL Server

Updated at:

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

  • Create an ApsaraDB RDS for SQL Server instance.

  • 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

Important
  • 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?.

  1. 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 is Internal Address,Port. Next to the internal address, click Configure whitelist to configure the whitelist.

  2. 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.

    Note

    Make 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.

  3. 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.

  4. Add the CIDR block of the vSwitch to the database whitelist.

    Important

    You 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.

    1. Log on to the ApsaraDB RDS console.

    2. In the left-side navigation pane, choose Whitelists and security groups. On the Whitelist settings tab, find the target whitelist name and click Modify.

    3. 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.

  1. 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.

  2. 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.

    Note

    Setting 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.

  3. On the function details page, go to the Configuration tab. In the Network section, get the elastic IP address configured for the function.

  4. Add the elastic IP address to the database whitelist.

    Important

    You 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.

    1. Log on to the ApsaraDB RDS console.

    2. 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.

    3. 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

  1. 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)
    
  2. 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.

  3. 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.

  4. 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. The users table contains four columns (id, name, age, and gender) and multiple records. The sample output shows one record where the values are 1, Zhang San, 20, and M respectively. 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.

    Serverless Devs steps

    1. Install Serverless Devs and Docker, and configure your AccessKey pair. For more information, see Quick start.

    2. Create a code directory named mycode, and prepare the s.yaml file and the app.py code file. A sample s.yaml file is as follows. For the sample code of app.py, see the sample code provided in Step 2: Access an SQL Server database from a function.

      The following s.yaml example is for accessing an SQL Server database within the same VPC. To access a database across VPCs or regions, see Scenario 2: Accessing an SQL Server database across VPCs or regions.

      # ------------------------------------
      #   Official documentation: https://manual.serverless-devs.com/user-guide/aliyun/#fc3
      #   Tips: https://manual.serverless-devs.com/user-guide/tips/
      #   If you have questions, join our DingTalk group: 33947367
      # ------------------------------------
      edition: 3.0.0
      name: hello-world-app
      access: "default"
      vars: # Global variables
        region: "cn-hangzhou"  # If you access the RDS database within the same VPC, make sure the function is deployed in the same region as the database.
      resources:
        hello_world:
          component: fc3 
          actions:       
            pre-${regex('deploy|local')}: 
              - component: fc3 build 
          props:
            region: ${vars.region}              
            functionName: "start-python-sqlServer"
            runtime: custom.debian10
            description: 'hello world by serverless devs'
            timeout: 10
            memorySize: 512
            cpu: 0.5
            diskSize: 512
            code: ./code
            customRuntimeConfig:
              port: 9000
              command:
                - python3
                - app.py
            internetAccess: true
            vpcConfig:
              vpcId: vpc-bp1dxqii29fpkc8pw**** # The ID of the VPC where the database instance resides.
              securityGroupId: sg-bp12ly2ie92ixrfc**** # The ID of the security group.
              vSwitchIds: 
                 - vsw-bp1ty76ijntee9z83**** # Ensure the CIDR block of this vSwitch is added to the database instance's whitelist.
            environmentVariables:
              PYTHONPATH: /code/python
              PATH: /code/python/bin:/var/fc/lang/python3.10/bin:/usr/local/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/opt/bin
              MSSQL_SERVER: rm-*****.sqlserver.rds.aliyuncs.com  # Database endpoint.
              MSSQL_USER: ******   # The name of the account created in the SQL Server instance.
              MSSQL_PASSWORD: *****     # The password for the database account.
              MSSQL_DATABASE: *****     # The name of the database created in the instance.
              MSSQL_PORT: "1433"        # The database port.
                    

      The directory structure is as follows:

      ├── code
      │ ├── app.py
      │ └── requirements.txt
      └── s.yaml

      The dependencies specified in the requirements.txt file are as follows:

      flask==2.2.5
      pymssql==2.3.2
    3. Run the following command to build the project:

      sudo s build --use-docker
    4. Run the following command to deploy the project:

      sudo s deploy -y
    5. Run the following command to invoke the function:

      Note

      Ensure the CIDR block of the vSwitch for your function is added to the database instance's whitelist. For instructions, see Step 1.

      sudo s invoke -e "{}"