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 PostgreSQL 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"]
}
Top comments (0)