Paul Bradley • Solutions Architect & Software Developer


Published:

Deploying an AWS PostgreSQL Aurora RDS Cluster with Babelfish Enabled

Migrating SQL Server databases to AWS and switching to the open source PostgreSQL database with little change to your application.

midjourney ai - da vinci style technical drawing of a mechanical babel fish

Introduction to Babelfish

Migrating an SQL Server database to Amazon Web Services, while possible, can be expensive. Microsoft’s SQL Server licensing model is expensive when compared to an open source alternative like PostgreSQL. Thanks to an open-source project called Babelfish for PostgreSQL you can move your SQL Server workloads to the cloud and benefit from the cost efficiencies that PostgreSQL offer.

Babelfish for PostgreSQL adds a Microsoft SQL Server-compatible end-point to PostgreSQL. Babelfish allows PostgreSQL to understand T-SQL, SQL Server’s proprietary SQL dialect, and supports the TDS communication protocol, so applications originally written for SQL Server may work with PostgreSQL with fewer code changes and without changing database drivers.

Software suppliers will need to test their software using a Babelfish endpoint, but most applications will work with minor changes.

Provisioning an Aurora Postgres Cluster with Babelfish Enabled Using Terraform

Below is a sample Terraform script to provision an RDS Aurora Cluster with a Babelfish endpoint enabled. I hope you find this useful. The code is commented, and you can change elements to suit your requirements.

resource "aws_rds_cluster" "rds_cluster_maywoods_audit_tool" {

    cluster_identifier = "maywoods-audit-tool"
    availability_zones = ["eu-west-2a", "eu-west-2b", "eu-west-2c"]

    // database engine type, mode and version
    engine          = "aurora-postgresql"
    engine_mode     = "provisioned"
    engine_version  = "13.7"

    // database_nam must begin with a letter and 
    // contain only alphanumeric characters.
    // ensure a strong long password is used.
    database_name   = "MaywoodsAuditToolsQA"
    master_password = "**********************************"
    master_username = "MaywoodsAuditAdmin"

    backup_retention_period = "60"
    copy_tags_to_snapshot = true

    db_cluster_parameter_group_name = aws_rds_cluster_parameter_group.rds_cluster_maywoods_audit_tool_pg.name
    db_subnet_group_name = aws_db_subnet_group.rds_cluster_maywoods_audit_tool_sng.name
    deletion_protection = true
    enabled_cloudwatch_logs_exports = ["postgresql"]

    skip_final_snapshot = flase
    final_snapshot_identifier = "maywoods-audit-tool-cluster-final-snapshot"

    iam_database_authentication_enabled = false
    iam_roles = []

    preferred_backup_window = "23:00-00:00"
    preferred_maintenance_window = "sun:01:00-sun:02:00"
    
    storage_encrypted = true
    kms_key_id = "aws/rds"

    vpc_security_group_ids = var.sg_id_list

    tags = {
        // override any tags already set within the
        // default_tags block with the providers.tf
        Application = "CORE SERVICE - AUDIT TOOL DATABASE",
        Provider = "MAYWOODS"
    }
}

resource "aws_rds_cluster_instance" "rds_cluster_maywoods_audit_tool_instance" {

    cluster_identifier = aws_rds_cluster.rds_cluster_maywoods_audit_tool.id

    // change instance type/size and count
    // based on the workloads requirements
    instance_class = "db.r5.large"
    count = 1
    identifier = "maywoods-audit-tool-inst-0"

    // use the same engine/version as define in the cluster
    engine = aws_rds_cluster.rds_cluster_maywoods_audit_tool.engine
    engine_version = aws_rds_cluster.rds_cluster_maywoods_audit_tool.engine_version

    auto_minor_version_upgrade = true
    copy_tags_to_snapshot = true
    db_parameter_group_name = "maywoods-audit-tool-pg"
    db_subnet_group_name = aws_db_subnet_group.rds_cluster_maywoods_audit_tool_sng.name
    monitoring_interval = "0"
    monitoring_role_arn = ""
    performance_insights_enabled = true
    preferred_maintenance_window = "sun:01:00-sun:02:00"
    publicly_accessible = false

    tags = {
        // override any tags already set within the
        // default_tags block with the providers.tf
        Application = "CORE SERVICE - AUDIT TOOL DATABASE",
        Provider = "MAYWOODS"
    }
}

resource "aws_rds_cluster_parameter_group" "rds_cluster_maywoods_audit_tool_pg" {
    name = "maywoods-audit-tool-pg"
    family = "aurora-postgresql13"

    # enable babelfish to be active
    parameter {
        name = "rds.babelfish_status"
        value = "on"
        apply_method = "pending-reboot"
    }

    tags = {
        // override any tags already set within the
        // default_tags block with the providers.tf
        Application = "CORE SERVICE - AUDIT TOOL DATABASE",
        Provider = "MAYWOODS"
    }
}

resource "aws_db_subnet_group" "rds_cluster_maywoods_audit_tool_sng" {
    name = "${var.name_prefix}maywoods-db-subnet"
    subnet_ids = [var.private_subnet_zero, var.private_subnet_one]

    tags = {
        Application = "ScR CORE SERVICE - AUDIT TOOL DB",
        Provider = "MAYWOODS"
    }
}

resource "aws_cloudwatch_log_group" "rds_cluster_maywoods_audit_tool_lg" {
    name = "/aws/rds/cluster/${aws_rds_cluster.rds_cluster_maywoods_audit_tool.cluster_identifier}/postgresql"
    retention_in_days = 60
}

Security Groups

To allow servers to connect to the RDS Aurora PostgreSQL Cluster you’ll also need to define a security group. The security group will need to open ports 5432 for native PostgreSQL traffic, and port 1433 for Babelfish traffic.

resource "aws_security_group" "rds_maywoods_audit_tool_sg" {
    name = "${var.name_prefix}rds_maywoods_audit_tool_sg"
    description = "Maywoods Audit Tool Database Access"
    vpc_id = var.vpc_id

    tags = {
        Name = "${var.name_prefix}rds_maywoods_audit_tool_sg",
    }
}

resource "aws_security_group_rule" "rds_maywoods_audit_tool_psql_inbound" {
    security_group_id = aws_security_group.rds_maywoods_audit_tool_sg.id
    description = "Ingress PostgreSQL traffic from hospital servers"
    type = "ingress"
    from_port = 5432
    to_port   = 5432
    protocol  = "tcp"
    cidr_blocks = ["10.164.33.38/32", "10.164.33.37/32"]
}

resource "aws_security_group_rule" "rds_maywoods_audit_tool_psql_outbound" {
    security_group_id = aws_security_group.rds_maywoods_audit_tool_sg.id
    description = "Egress PostgreSQL traffic from hospital servers"
    type = "egress"
    from_port = 5432
    to_port   = 5432
    protocol  = "tcp"
    cidr_blocks = ["10.164.33.38/32", "10.164.33.37/32"]
}

resource "aws_security_group_rule" "rds_maywoods_audit_tool_babelfish_inbound" {
    security_group_id = aws_security_group.rds_maywoods_audit_tool_sg.id
    description = "Ingress Babelfish traffic from hospital servers"
    type = "ingress"
    from_port = 1433
    to_port   = 1433
    protocol  = "tcp"
    cidr_blocks = ["10.164.33.38/32", "10.164.33.37/32"]
}

resource "aws_security_group_rule" "rds_maywoods_audit_tool_babelfish_outbound" {
    security_group_id = aws_security_group.rds_maywoods_audit_tool_sg.id
    description = "Egress Babelfish traffic from hospital servers"
    type = "egress"
    from_port = 1433
    to_port   = 1433
    protocol  = "tcp"
    cidr_blocks = ["10.164.33.38/32", "10.164.33.37/32"]
}