Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Use the Babelfish Feature of Alibaba Cloud ApsaraDB for PostgreSQL to Serve as the SQL Server

Use the Babelfish Feature of Alibaba Cloud ApsaraDB for PostgreSQL to Serve as the SQL Server

This article discusses the pros and cons of performing SQL Server operations with the Babelfish feature of Alibaba Cloud ApsaraDB for PostgreSQL.

Support for Windows Server 2012 and Windows Server 2012 R2 will end on October 10, 2023. After the end of support, security updates will no longer be provided, increasing security risks. Therefore, many people consider upgrading their operating systems or migrating data to the cloud before support ends. However, due to the requirements of applications running on Windows Server 2012 and Windows Server 2012 R2, there are scenarios where Windows Server could be used in conjunction with SQL Server. In this case, the most popular choice from a cost perspective is to switch to Azure SQL Server. Therefore, we will try using the Babelfish feature of Alibaba Cloud ApsaraDB for PostgreSQL instead of SQL Server.

Contents

  1. What Is Babelfish?
  2. Verification Flow
  3. Create ECS Instances for PostgreSQL and SQL Server Clients
  4. Create an ApsaraDB for PostgreSQL Instance
  5. Connect to an ApsaraDB for PostgreSQL Instance Using PostgreSQL CLI
  6. Connect to an ApsaraDB for PostgreSQL Instance Using SQLServer CLI

1. What Is Babelfish?

Babelfish is an open-source service that converts SQL queries from applications written for Microsoft SQL Server to PostgreSQL and processes SQL queries. Babelfish allows users with a requirement that demands SQL Server to run on PostgreSQL.

1

References

Babelfish for PostgreSQL accelerates your journey to migrate SQL Server applications to PostgreSQL
https://babelfishpg.org/

An Introduction to Babelfish
https://www.alibabacloud.com/help/en/apsaradb-for-rds/latest/babelfish-for-pg#concept-2212689

Self-Built SQL Server Full Data Migration to RDS for PostgreSQL with Babelfish Single-DB Mode
https://www.youtube.com/watch?v=_YGz-Hh7FPs&t=39s

2. Verification Flow

As mentioned earlier, Babelfish supports SQL queries for PostgreSQL and SQL Server in ApsaraDB for PostgreSQL. To test this, we will prepare two Elastic Compute Service (ECS) instances, install the PostgreSQL client to one ECS instance and the SQL Server client to the other, and check their performance on SQL queries in the clients. The following figure shows the overall configuration diagram. Note:8 The port that connects the PostgreSQL client to the database is 5432, and the port that connects the SQL Server client to the database is 1433.

Configuration diagram of connections

2

3. Create ECS Instances for PostgreSQL and SQL Server Clients

(1) Create two ECS instances. The two ECS instances run on Ubuntu.

3
4

(2) Connect ECS01 to the PostgreSQL client and ECS02 to the SQL Server client using Shell.

5

4. Create an ApsaraDB for PostgreSQL Instance

Create an ApsaraDB for PostgreSQL instance in the console. When you create an ApsaraDB for PostgreSQL instance, Enable Babelfish is displayed on the page. Select the checkbox to enable the Babelfish feature. Babelfish currently only supports PostgreSQL 13 and 14.

6

When you enable Babelfish, create an ApsaraDB for PostgreSQL instance as usual. An ApsaraDB for PostgreSQL instance with Babelfish enabled will be created in any configuration (such as Single, HA configuration, Zone, or low spec).

7

If Babelfish is enabled, you can see the status of Babelfish.

8

We will configure some settings so you can connect and use the ApsaraDB for PostgreSQL instance.

The first is registering a whitelist. Go to the Data Security > Whitelist and add the IP address of the ECS instance connected to the client to the whitelist.

9

Next, enable the use of public endpoint:

10
11

5. Connect to an ApsaraDB for PostgreSQL Instance Using PostgreSQL CLI

Install PostgreSQL CLI on the ECS instance. Install the package from the repository settings required for CLI installation as a flow. Set up the repository.

# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

12

Import the public repository key:

# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

13

Update the package list:

# sudo apt-get update

14
15

Install postgresql

# sudo apt-get -y install postgresql

16
17

Now that the PostgreSQL CLI client is available, we will try to connect to an ApsaraDB for PostgreSQL instance. In this example, the account inituser is used.

18

When you connect to an ApsaraDB for PostgreSQL instance using PostgreSQL CLI, select the endpoint displayed on the page (pgm-.pgsql.japan.rds.aliyuncs.com) and port 5432 as the connection endpoint.

19

Now that everything is ready, use the PostgreSQL CLI to connect to an ApsaraDB for PostgreSQL instance.

The connection method is psql -h<instance endpoint> -U <Username> -p <port> -d <DBname>. The following connection command is run in the preceding example:

# psql -h pgm-<user endpoint>.pgsql.japan.rds.aliyuncs.com -U inituser -p 5432 -d postgres

After you connect to an ApsaraDB for PostgreSQL instance, you will be prompted to enter the password of the user account and connect to PostgreSQL in the ApsaraDB for PostgreSQL instance.

20

Check the database list and confirm that babelfish_db exists.

If Babelfish is enabled, ApsaraDB creates a database named babelfish_db. Note: The babelfish_db database will be unavailable if you connect an application to the RDS instance over the Tabular Data Stream (TDS) port.

References

An Introduction to Babelfish
https://www.alibabacloud.com/help/en/apsaradb-for-rds/latest/babelfish-for-pg#concept-2212689

postgres=> \l

21

Connect to babelfish_db.

postgres=> \c babelfish_db

22

Check the schema

babelfish_db=> \dn

23

Currently, the database is created and in its initial state. We will check whether PostgreSQL can generally be controlled based on PostgreSQL DDL operations (Data Definition Language, commands to define databases, tables, etc.) by assigning roles while creating the database. Run the following PostgreSQL commands to create a database.

CREATE DATABASE name [ [ WITH ] option [ ... ] ]

CREATE DATABASE db_test;

24

Modify the database. Run the following PostgreSQL commands:

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

ALTER DATABASE name RENAME TO new_name
ALTER DATABASE db_test RENAME TO test_db;

25

Remove the PostgreSQL database:

DROP DATABASE [ IF EXISTS ] name [ WITH ]

DROP DATABASE test_db；

26

Based on this, we can confirm that PostgreSQL-based DDL operations can generally be performed. Next, when you create the database and table, check whether the ApsaraDB for PostgreSQL instance can generally be controlled based on PostgreSQL DCL operations (Data Control Language, commands that control data with privileges, etc.). First, create a database again:

CREATE DATABASE test_db;

27

Create a new role with a password. Run the following PostgreSQL command:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

babelfish_db=> CREATE ROLE role_test WITH LOGIN  PASSWORD 'Test1234';

28

GRANT — Try to define access permissions. Run the following PostgreSQL command:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

Note: The PRIVILEGES keyword is optional for PostgreSQL but is required for strict SQL databases (such as SQLServer).

GRANT ALL PRIVILEGES ON DATABASE  test_db TO role_test ;

29

Check the role you just created. Connect to the new database with the new role.

psql -h pgm-0iw7au5dx890i090yo.pgsql.japan.rds.aliyuncs.com -U role_test -p 5432 -d test_db

30

We have confirmed that PostgreSQL-based DCL operations can be performed. Next, we will check whether the ApsaraDB for PostgreSQL instance can generally be controlled based on PostgreSQL-based DML operations (Data Manipulation Language, commands for manipulating data). First, create a table:

CREATE TABLE product (
    product_id integer,
    product_name text,
    product_price numeric
);

31

Insert data into the table you just created. Run the following PostgreSQL data insertion command:

INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
INSERT INTO product (product_id , product_name , product_price )
    VALUES (121, 'Yombook', 109.98);
INSERT INTO product (product_id , product_name , product_price )
    VALUES (111, 'Engbook', 190.12);

32

After you insert the data, try to retrieve the data by executing the SELECT statement. Run the following PostgreSQL command to execute the SELECT statement:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
SELECT * FROM product ORDER BY product_id;

33

Check the command that counts the number of lines. PostgreSQL has the COUNT function, so check this by using the COUNT function.

SELECT COUNT(*) FROM product;

34

PostgreSQL does not have the COUNT_BIG function of SQL Server. The COUNT_BIG function can return a large number of records that cannot be processed by the COUNT function. Try running the following command to check:

 SELECT COUNT_BIG(*) FROM product;

35

As a result, the COUNT_BIG function and other SQL Server commands could not be used when you connect to an ApsaraDB for PostgreSQL instance using the PostgreSQL client.

Please visit the website below for more information about PostgreSQL commands
https://www.postgresql.jp/document/13/html/sql-commands.html

Finally, we will try to check PL/SQL and other procedure operations based on PostgreSQL. PL/SQL is an alternative programming language (variables, assignments, error handling, loops, IF statements, etc.) to SQL queries. If the value of the variable whose value is increasing reaches a certain value, an exception handling and a user-defined error will be generated. You can check the movement of the variable for PostgreSQL by executing the RAISE statement.

CREATE OR REPLACE PROCEDURE test1(id INTEGER) 
AS $$
DECLARE
   i INTEGER;
BEGIN
   i := 0;
   WHILE i < 3 LOOP
       RAISE NOTICE‘ % ’ ,  i;
       i := i + 1;
   END LOOP;
END;
$$
LANGUAGE plpgsql;
call test1(2);

36

References

[PostgreSQL] Display the value of the variable on the screen (raise) [also for debugging]
https://postgresweb.com/post-2852

6. Connect to an ApsaraDB for PostgreSQL Instance Using the SQLServer CLI

Now, install the SQL Server CLI on the ECS instance with SQL Server as the client. The flow is the same as PostgreSQL CLI. Install the package from the repository settings required for CLI installation. Connect to ECS02 of the SQL Server client using Shell.

37

Import the public repository GPG key:

# curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

38

Register the Microsoft Ubuntu repository:

# curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

39

Update the package list:

# sudo apt-get update

40
41

Install mssql-tools:

# sudo apt-get install mssql-tools

42

Accept the licensing:

43
44
45

Add the environment variable:

# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
# source ~/.bashrc

46

Now that the SQL Server CLI client is available, we will try to connect to an ApsaraDB for PostgreSQL instance. In this example, the account inituser is used.

47

When you connect to an ApsaraDB for PostgreSQL instance using SQL Server CLI, use the endpoint displayed on the page (pgm-.pgsql.japan.rds.aliyuncs.com) and Babelfish TDS port 1433 as the connection endpoints.

48

Now that everything is ready, use the PostgreSQL CLI to connect to an ApsaraDB for SQL Server instance.

The connection method is sqlcmd -S <instance endpoint> ,1433 -U <Username>. The following connection command is run in the preceding example:

# sqlcmd -S pgm-<user endpoint>.pgsql.japan.rds.aliyuncs.com 1433 -U inituser

After you connect to an ApsaraDB for SQL Server instance, you will be prompted to enter the password and connect to PostgreSQL in the ApsaraDB for PostgreSQL instance.

49

We will try to check if the SQL Server SQL command is working at the earliest opportunity. First, check the version based on SQL Server:

50

SQL Server-based version confirmation command SELECT @@version; the PostgreSQL version is returned.

We will try DDL, DCL, and DML to see to what extent we can control SQL Server with the Babelfish feature of ApsaraDB for PostgreSQL. First, check whether PostgreSQL can generally be controlled based on DDL operations (Data Definition Language, commands that define databases, tables, etc.) using SQL Server. Run the following SQL Server command to create a database:

CREATE DATABASE name [ [ WITH ] option [ ... ] ]

CREATE DATABASE testdb;

51

USE testdb;

52

Modify the database. Databases can be modified in PostgreSQL by running the ALTER DATABASE command, but SQL Server does not support the ALTER DATABASE command, so we will try running the following command:

ALTER DATABASE testdb Modify Name=dbtest;

53

Remove the SQL Server database:

DROP DATABASE [ IF EXISTS ] name

DROP DATABASE testdb;

54

Based on this, we can confirm that SQL Server-based DDL operations can generally be performed. Next, when you create the database and table, check whether the ApsaraDB for PostgreSQL instance can generally be controlled based on SQL Server DCL operations (Data Control Language, commands that control data with privileges, etc.). First, create a database again:

CREATE DATABASE testdb;

Create and log on to the database with a new user and password. Run the following SQL Server commands:

CREATE LOGIN ... PASSWORD

CREATE LOGIN testuser WITH PASSWORD  = 'Test1234';

55

Create a database user for login:

CREATE USER testuser FOR LOGIN testuser ;

56

Check the user information:

SELECT name FROM sys.server_principals;

57

Log on as the new user:

58

PostgreSQL supports the CREATE ROLE command, but SQL Server does not, so we will try to run this command to check if it is not supported as expected.

59

As a result, we can confirm it is not supported.

GRANT — We will try to define access permissions.

GRANT ALL ON dbo.testdb TO testuser;

60

Connect as testuser:

# sqlcmd -S pgm-0iw7au5dx890i090yo.pgsql.japan.rds.aliyuncs.com,1433 -U testuser

Check the SELECT statement privileges of the database:

61

Make sure you do not have permissions to use the database:

use testdb;

62

Note: Babelfish does not fully support the GRANT command. Please visit the following link for more information:
https://babelfishpg.org/docs/usage/limitations-of-babelfish

We have confirmed that SQL Server-based DCL operations can be performed. Next, we will check whether the ApsaraDB for PostgreSQL instance can generally be controlled based on SQL Server-based DML operations (Data Manipulation Language, commands for manipulating data). First, create a table:

CREATE TABLE dbo.product (product_id integer, product_name text, product_price numeric);

63

Insert data into the table you just created.

INSERT INTO product (product_id , product_name , product_price )  VALUES (121, 'Yombook', 09.98);
INSERT INTO product (product_id , product_name , product_price )   VALUES (111, 'Engbook', 90.12);

64

After you insert the data, try to retrieve the data by executing the SELECT statement:

SELECT * FROM product ORDER BY product_id;

65

SQLServer has both the COUNT command and the COUNT_BIG command. The COUNT_BIG command is a function unique to SQLServer that can return a large number of rows. Use the two functions to check the command that counts the number of lines.

The COUNT command:

SELECT COUNT(*) FROM product;

66

The COUNT_BIG command:

SELECT COUNT_BIG(*) FROM product;

67

This result indicates it is possible to support SQLServer-only functions even in PostgreSQL using Babelfish.

Finally, we will try to check PL/SQL procedure operations based on SQL Server. Under the same conditions as the preceding PostgreSQL procedure operation, if the value of the variable whose value is increasing reaches a certain value, an exception handling and a user-defined error are generated. You can generate an error for SQL Server while checking the movement of the variable by using RAISERROR.

CREATE PROCEDURE dbo.pro_tr3 (@id int)
AS
BEGIN
    DECLARE @i int
    SET @i = 0
    WHILE @i < 10
    BEGIN
        SET @i = @i + 1
        PRINT @i
        IF @i = 7
            BEGIN
              RAISERROR('SERVER IS ERROR ! %d ',1,1,@i)
              BREAK
            END
    END
END;


EXEC pro_tr3 @id = 7;，

68

7. Conclusion

We have shown that the SQL Server operations can be performed with the Babelfish feature of Alibaba Cloud ApsaraDB for PostgreSQL. SQL Server is convenient as an enterprise database, but the license fee tends to be expensive. In addition, the learning costs for operating SQL Servers are high. Therefore, using the Babelfish feature to reduce running costs and operating costs is an option.

This article has been translated from SoftBank.
https://www.softbank.jp/biz/blog/cloud-technology/articles/202209/babelfish-on-apsaradb/

PostgreSQL SQL Server Database Developers SQL Server Tutorials ApsaraDB for PostgreSQL Babelfish
0 3 1
Share on

Read next post:

Start Hybrid Cloud and Multi-Cloud with Alibaba Cloud ACK One [Part One]

Hironobu Ohara

9 posts | 0 followers

You may also like

Hironobu Ohara

9 posts | 0 followers

Related Products

More Posts by Hironobu Ohara

See All