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.
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.
1resource "aws_rds_cluster" "rds_cluster_maywoods_audit_tool" {
2
3 cluster_identifier = "maywoods-audit-tool"
4 availability_zones = ["eu-west-2a", "eu-west-2b", "eu-west-2c"]
5
6 // database engine type, mode and version
7 engine = "aurora-postgresql"
8 engine_mode = "provisioned"
9 engine_version = "13.7"
10
11 // database_name must begin with a letter
12 // and can contain alphanumeric characters.
13 // ensure a strong long password is used.
14 database_name = "MaywoodsAuditToolsQA"
15 master_password = "**********************************"
16 master_username = "MaywoodsAuditAdmin"
17
18 backup_retention_period = "60"
19 copy_tags_to_snapshot = true
20
21 db_cluster_parameter_group_name = aws_rds_cluster_parameter_group.rds_cluster_maywoods_audit_tool_pg.name
22 db_subnet_group_name = aws_db_subnet_group.rds_cluster_maywoods_audit_tool_sng.name
23 deletion_protection = true
24 enabled_cloudwatch_logs_exports = ["postgresql"]
25
26 skip_final_snapshot = flase
27 final_snapshot_identifier = "maywoods-audit-tool-cluster-final-snapshot"
28
29 iam_database_authentication_enabled = false
30 iam_roles = []
31
32 preferred_backup_window = "23:00-00:00"
33 preferred_maintenance_window = "sun:01:00-sun:02:00"
34
35 storage_encrypted = true
36 kms_key_id = "aws/rds"
37
38 vpc_security_group_ids = var.sg_id_list
39
40 tags = {
41 // override any tags already set within the
42 // default_tags block with the providers.tf
43 Application = "CORE SERVICE - AUDIT TOOL DATABASE",
44 Provider = "MAYWOODS"
45 }
46}
47
48resource "aws_rds_cluster_instance" "rds_cluster_maywoods_audit_tool_instance" {
49
50 cluster_identifier = aws_rds_cluster.rds_cluster_maywoods_audit_tool.id
51
52 // change instance type/size and count
53 // based on the workloads requirements
54 instance_class = "db.r5.large"
55 count = 1
56 identifier = "maywoods-audit-tool-inst-0"
57
58 // use the same engine/version as define in the cluster
59 engine = aws_rds_cluster.rds_cluster_maywoods_audit_tool.engine
60 engine_version = aws_rds_cluster.rds_cluster_maywoods_audit_tool.engine_version
61
62 auto_minor_version_upgrade = true
63 copy_tags_to_snapshot = true
64 db_parameter_group_name = "maywoods-audit-tool-pg"
65 db_subnet_group_name = aws_db_subnet_group.rds_cluster_maywoods_audit_tool_sng.name
66 monitoring_interval = "0"
67 monitoring_role_arn = ""
68 performance_insights_enabled = true
69 preferred_maintenance_window = "sun:01:00-sun:02:00"
70 publicly_accessible = false
71
72 tags = {
73 // override any tags already set within the
74 // default_tags block with the providers.tf
75 Application = "CORE SERVICE - AUDIT TOOL DATABASE",
76 Provider = "MAYWOODS"
77 }
78}
79
80resource "aws_rds_cluster_parameter_group" "rds_cluster_maywoods_audit_tool_pg" {
81 name = "maywoods-audit-tool-pg"
82 family = "aurora-postgresql13"
83
84 # enable babelfish to be active
85 parameter {
86 name = "rds.babelfish_status"
87 value = "on"
88 apply_method = "pending-reboot"
89 }
90
91 # allow multiple database mode
92 parameter {
93 name = "babelfishpg_tsql.migration_mode"
94 value = "multi-db"
95 apply_method = "pending-reboot"
96 }
97
98 # raise the minimum TLS protocol from v1 to v1.2
99 parameter {
100 name = "babelfishpg_tds.tds_ssl_min_protocol_version"
101 value = "TLSv1.2"
102 apply_method = "pending-reboot"
103 }
104
105 tags = {
106 // override any tags already set within the
107 // default_tags block with the providers.tf
108 Application = "CORE SERVICE - AUDIT TOOL DATABASE",
109 Provider = "MAYWOODS"
110 }
111}
112
113resource "aws_db_subnet_group" "rds_cluster_maywoods_audit_tool_sng" {
114 name = "${var.name_prefix}maywoods-db-subnet"
115 subnet_ids = [var.private_subnet_zero, var.private_subnet_one]
116
117 tags = {
118 Application = "ScR CORE SERVICE - AUDIT TOOL DB",
119 Provider = "MAYWOODS"
120 }
121}
122
123resource "aws_cloudwatch_log_group" "rds_cluster_maywoods_audit_tool_lg" {
124 name = "/aws/rds/cluster/${aws_rds_cluster.rds_cluster_maywoods_audit_tool.cluster_identifier}/postgresql"
125 retention_in_days = 60
126}
Single Database or Multiple Databases
By default, the RDS cluster will be created with a parameter group that sets the migration mode to single database mode. If your cluster needs to support more than one database then set the babelfishpg_tsql.migration_mode value to multi-db
1parameter {
2 name = "babelfishpg_tsql.migration_mode"
3 value = "multi-db"
4 apply_method = "pending-reboot"
5}
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.
1resource "aws_security_group" "rds_maywoods_audit_tool_sg" {
2 name = "${var.name_prefix}rds_maywoods_audit_tool_sg"
3 description = "Maywoods Audit Tool Database Access"
4 vpc_id = var.vpc_id
5
6 tags = {
7 Name = "${var.name_prefix}rds_maywoods_audit_tool_sg",
8 }
9}
10
11resource "aws_security_group_rule" "rds_maywoods_audit_tool_psql_inbound" {
12 security_group_id = aws_security_group.rds_maywoods_audit_tool_sg.id
13 description = "Ingress PostgreSQL traffic from hospital servers"
14 type = "ingress"
15 from_port = 5432
16 to_port = 5432
17 protocol = "tcp"
18 cidr_blocks = ["10.164.33.38/32", "10.164.33.37/32"]
19}
20
21resource "aws_security_group_rule" "rds_maywoods_audit_tool_psql_outbound" {
22 security_group_id = aws_security_group.rds_maywoods_audit_tool_sg.id
23 description = "Egress PostgreSQL traffic from hospital servers"
24 type = "egress"
25 from_port = 5432
26 to_port = 5432
27 protocol = "tcp"
28 cidr_blocks = ["10.164.33.38/32", "10.164.33.37/32"]
29}
30
31resource "aws_security_group_rule" "rds_maywoods_audit_tool_babelfish_inbound" {
32 security_group_id = aws_security_group.rds_maywoods_audit_tool_sg.id
33 description = "Ingress Babelfish traffic from hospital servers"
34 type = "ingress"
35 from_port = 1433
36 to_port = 1433
37 protocol = "tcp"
38 cidr_blocks = ["10.164.33.38/32", "10.164.33.37/32"]
39}
40
41resource "aws_security_group_rule" "rds_maywoods_audit_tool_babelfish_outbound" {
42 security_group_id = aws_security_group.rds_maywoods_audit_tool_sg.id
43 description = "Egress Babelfish traffic from hospital servers"
44 type = "egress"
45 from_port = 1433
46 to_port = 1433
47 protocol = "tcp"
48 cidr_blocks = ["10.164.33.38/32", "10.164.33.37/32"]
49}
History
- 19.01.2023 - Added options to the RDS parameter group to enable the cluster to support multiple databases. Also raised the minimum TLS protocol version to TLS v1.2