Single-Mode Vs Multiple-Mode in Babelfish for Aurora PotgreSQL - which one should you use

16 March,2022 by Rambler

- Babelfish for Aurora PotgreSQL : Single -Mode versus Multiple - mode ?   This will impact how many user database can co-exist on a single Babelfish Instance . 

To illustrate the point , If you're accessing a Babelfish for Aurora PostgreSQL in  single-mode , you will be able to have a single user database , if you attempt to create more than 1 database , the following message will appear 

Only one user database allowed under single-db mode. User database "xxxxxxx" already exists

 

 

Single-Mode considerations

            - can only create 1 t-sql database in Babelfish 

             - schemas are created as regular PostgreSQL schemas in Babelfish database

             -  The other impact is that the  SQL Server schema names remain the same in the babelfish db. The schema names remain the same as in SQL Server.  From a migration perspective this makes it straightforward. If you were using    dbo and myschename1 in SQL Server , the same references will apply in Babelfish 

Multiple-Mode considerations

      -  can create multiple t-sql databases (with a unique set of schemas per database)

      - a t-sql schema is created as PostgreSQL schema for name conflict 

        -  In comparison to single mode , if you were to configure Babelfish as  multiple mode , although you will see the schema names through SQL Server as the same , they will be referenced as dbname_schemaname  when viewed through PostgreSQL.   So if you  have a database called   - myDB1  with   myschema1 and myschema2   , when it's migrated to Babelfish , from a SQL Server perspective the references remain the same.      But through PostgreSQL    the schema reference  become    myDB1_myschema1 and myDB1_myschema2

 

 

The migration-mode  (single or multiple) is stored in the migration-mode parameter. Once you've set it and built the Babelfish instance it isn't possible to change it 

By default - it's set at single mode

How can you check the current migration mode on an AWS Babelfish for Aurora PostgreSQL?

Using the Amazon RDS interface  => Go to the Parameter groups and find the relevant Parameter Group for the Babelfish instance. Click on the Parameter Group and search for the "migration mode" parameter  . The current value will be in the Values column 

 

Read more on Babelfish for Aurora PotgreSQL

    Spotlight on Babelfish for Aurora PostgreSQL

   


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Single-Mode Vs Multiple-Mode in Babelfish for Aurora PotgreSQL - which one should you use


dba-ninja.com