Architecture Introduction

Recent Pages

Architecture Introduction

Last updated: 2024-04-09 10:31:59

Based on Babelfish for PostgreSQL, TencentDB for PostgreSQL has been adapted to support the data types, syntax, and functions of Microsoft SQL Server, the SQL Server wire-level protocol (TDS), and the communication between SQL Server applications and PostgreSQL. This helps the migration of objects, storage procedures, and application codes from TencentDB for SQL Server to TencentDB for PostgreSQL with minimal changes.
TencentDB for PostgreSQL does not fully support T-SQL, but you can run PostgreSQL commands to perform most tasks that are typically handled by these commands. For example, if you frequently use specific T-SQL commands that are not supported by TencentDB for PostgreSQL, you can connect to the PostgreSQL port and use PostgreSQL commands instead. For more information, see the SQL commands in the PostgreSQL documentation.
Note:
All the subsequent SQL Server versions compatible with TencentDB for PostgreSQL are collectively referred to as the MSSQL Compatible Edition.
Architecture Description
The MSSQL Compatible Edition currently supports TencentDB for PostgreSQL version 14.
The MSSQL Compatible Edition has a new database access port, allowing it to support SQL Server T-SQL and commonly used SQL Server statements, and enable TDS-based client applications to access the TDS listener port of the MSSQL Compatible Edition. Currently, TDS 7.1 and later versions are supported. For more information about the SQL Server wire-level protocol, see [MS-TDS]: Tabular data stream protocol.
You can access data simultaneously by using the TDS connection from an application and the native PostgreSQL connection.
By default, to use different database-specific syntax, please select the following ports:
For SQL Server, clients connect to port 1433.
For PostgreSQL, clients connect to port 5432.
When building a TecentDB for PostgreSQL database instance for the MSSQL Compatible Edition, the system will create a TecentDB for PostgreSQL database named babelfish_db for the instance. The database is where all SQL Server objects and structures are migrated to.
Note:
A database named babelfish_db will be reserved for this instance.
If you connect to the TDS port, the session will automatically be switched to the babelfish_db database. From the perspective of T-SQL, the architecture is similar to that for connecting a SQL Server instance. You will see the master and tempdb databases along with the sys.databases directory. You can create additional user databases and switch between them through the USE statement.
When you create a SQL Server user database, the database is mapped as a schema in the babelfish_db database. The retained cross-database syntax and semantics are equal to or similar to those assigned by SQL Server.
Difference Between Single-Database and Multi-Databases
When you create a MSSQL Compatible Edition instance, you can choose one or more SQL Server databases. You choice will affect how the names of SQL Server schemas within the babelfish_db database are displayed in PostgreSQL. The migration mode is specified in the migration_mode parameter, which is an initialization parameter and cannot be modified later.
In single-database mode, the schema name of the user database within the babelfish_db database is the same as that in the SQL Server database. If you choose to migrate a single database, its schemas will be rebuilt in the database, and you can name them the same as those used in SQL Server. For example, if the dbo and sche A schemas are in the db1 database, after migration to PostgreSQL, the schemas are as follows.
﻿
When connecting via TDS, you can run USE db1 to check the dbo and sche A schemas from T-SQL, just like in SQL Server. You can also check the same schema names from PostgreSQL.
In multi-database mode, the user database schema name becomes dbname_schemaname in PostgreSQL, while the schema name remains unchanged in T-SQL.
﻿
When connecting via TDS, you can run USE db1 or db2 to check the Sche A and Sche B schames from T-SQL, just like in TencentDB for SQL Server. You can also check the names of their mapped schemas, such as  db1_dbo  and dbA_Sche A, in TencentDB for stgreSQL.
Each database still contains your schemas. Each database's name is prefixed to the SQL Server schema name with an underscore as a separator, for example:
db1 contains db1_dbo and db1_sche A.
db2 contains db2_dbo and db2_Sche B.
In the babelfish_db database, T-SQL users still need to run the USE dbname command to change databases, similar to the operations in TencentDB for SQL Server.
How to Choose a Migration Mode
You can select a proper migration mode based on the number of user databases you have and how you plan to migrate them. Once you create an instance, you will not be able to change the migration mode. Therefore, when choosing a migration mode, please take into account of your user databases and client requirements.
If you create an MSSQL Compatible Edition instance, the master and tempdb system databases are also built. If you already created or modified any objects in the system databases (master or tempdb), please be sure to re-create these objects in the new cluster. Unlike TencentDB for SQL Server, TencentDB for PostgreSQL does not re-initialize tempdb after cluster restart.
Single-database migration mode is recommended in the following scenario:
You intend to migrate a single TecentDB for SQL Server database. In single-database mode, the migrated schema names are the same as the original schema names in the SQL Server, and migrating applications requires minimal changes to SQL codes.
Your ultimate goal is to fully migrate databases to TecentDB for PostgreSQL, and the Compatible Edition is only for transition.
Multi-database migration mode is recommended in the following scenarios:
You are trying out the MSSQL Compatible Edition and are not sure about what you will need.
You need to migrate multiple user databases together, and your ultimate goal is not for a completely native migration to TencentDB for PostgreSQL.
You have a potential need to migrate multiple databases in the future.