- Hello, my name is Rob.
I am a principal database engineer at AWS.
In this video, I want to talk
about Babelfish for Aurora Postgres.
In fact, this is the first video in a series
we will be publishing about Babelfish.
(no audio)
If you are running applications
on Microsoft SQL Server today,
you should learn about Babelfish.
Why?
Well, Babelfish allows you to migrate your applications
from Microsoft SQL Server to Aurora Postgres,
but without having to change all your SQL code,
and without having to change
any client-server connectivity drivers.
With Babelfish, such a migration is much easier
than a traditional approach
for migrating a SQL-based application.
Now, moving from a proprietary on-premises database
to an open source database in the cloud is
part of your application landscape modernization
that everybody needs to do at some point.
But Babelfish also allows you to break free
from the database licenses you are currently paying,
because Aurora Postgres has
a much more attractive cost model
that does not involve traditional licensing.
Babelfish is available as an option with Aurora Postgres
at no additional cost.
If you don't want to use Aurora,
you can also use the open source version of Babelfish,
which is published separately at GitHub.
So Babelfish offers a faster way
to migrate your applications from SQL Server to Postgres.
It does so by providing two key features.
First, Babelfish supports
the SQL Server-specific TDS protocol
for client-server communication.
And second, Babelfish provides you
with the Transact-SQL dialect that SQL Server uses.
Babelfish implements these natively in Postgres
as three Postgres extensions
using building blocks that Postgres provides.
Babelfish will give you the same syntax
and semantics as SQL Server,
even though Postgres itself uses
different syntax and semantics.
So when you connect to the TDS port that Babelfish provides,
Postgres will behave as if it is SQL Server,
with the Transact-SQL dialect,
with databases like master and tempdb,
and with many of the SQL Server catalogs.
At the same time, you can still connect
through the default Postgres port 5432
using Postgres syntax and semantics.
To migrate your applications from SQL Server to Babelfish,
on a high level, you need to take these steps.
First, you need to get a copy of the schema
of the databases you want to migrate.
The best way to do this is to reverse engineer
those databases with SQL Server Management Studio.
That will produce a script file containing
the DDL statements for these databases.
You can also capture the SQL statements
generated the client application,
and that gets sent to the database server,
but these are usually less important.
What you need to do next is run the Compass assessment tool
on the DDL code for schema.
Compass is a standalone tool that is available
as part of Babelfish, and which you can download.
Compass will analyze DDL and SQL code,
and report in great detail every SQL feature found,
and it will classify them
either as supported by Babelfish, or not.
Now, if the Compass report indicates
that your application contains SQL features
not currently supported by Babelfish,
you need to figure out what to do about it.
One option is to rewrite those pieces of SQL
using SQL statements that are supported by Babelfish.
Another is to descope the migration,
in other words, remove those pieces of SQL
from the application altogether.
There are separate videos on the Compass tool,
and you should check 'em out.
Now, next, you need to create yourself a Babelfish instance,
and that's pretty simple.
Just go to the RDS service at AWS,
create a new Aurora Postgres database,
and tick the box to turn on Babelfish.
Once you have your instance,
you need to recreate the schema in Babelfish.
You do that by taking your DDL script,
and execute it against the TDS port of Babelfish.
This is best done using the command line utility sqlcmd.
When the schema has been created,
you need to migrate the data from SQL Server to Babelfish,
and perhaps the best way to do that is
to use the DMS tool which AWS provides.
So once you've done all that, you've migrated your schema,
you've migrated the data in the tables,
then the last thing to do is to change
how the applications connect to the database.
Currently, the client applications connect to SQL Server,
and you have to change their configuration,
so that they connect to Babelfish instead.
And once you've done that, you can test your application,
and basically your migration is completed.
When you compare a migration to Babelfish
with a traditional migration,
where you have to convert all your SQL code
to the dialect of the target database,
there are some big differences.
With a traditional migration,
you have to migrate all your SQL code.
There is no alternative.
You cannot do only a part.
But with Babelfish, this is different,
because most of the Transact-SQL code is already supported,
so it does not require changes.
Likewise, for migrating a .NET application,
that's going to require a pretty big rewrite
if you want to move it to a different database,
like Postgres.
But again, because Babelfish supports
most of the SQL Server dialect,
your .NET applications will just keep working.
Also, with the traditional SQL dialect conversion approach,
the really hard part is migration
of procedural SQL code, such as stored procedures,
SQL functions, triggers, or SQL batch scripts.
All that procedural logic will have to be rewritten,
and that can be really challenging,
especially when there is a lot of such code.
With Babelfish, most of the stored procedures
will keep working unchanged.
It may be needed to do some refactoring to address pieces
that are not fully supported by Babelfish,
but that will most likely be only a minor part of the code.
In addition, in a traditional migration,
you'll have to change the connectivity drivers
between the client application and the database server.
With Babelfish, you don't need to change that,
because the SQL Server connectivity is supported.
All taken together, this means that a migration
to Babelfish is much less complex
than a traditional migration.
It's lower risk, lower cost, and just much more feasible.
Now, where are we with Babelfish today?
The first release was in October, 2021,
and that was version 1.0.0
based on Postgres version 13.4.
That had all the basics, the Transact-SQL dialect,
the tables, views, stored procedures, data types,
and really all the basics of SQL Server.
However, there were also a few features
that were not yet supported in that first release.
Now, today in 2022, we are looking at release 2.2.0
based on Postgres 14.5,
and some pretty big features have been added
since that original release.
Just to quickly mention a few,
one of the challenges in the first release was
that there was no good way to migrate data to Babelfish.
Today, in release 2.2.0,
DMS works, the bcp utility works,
and the Import/Export Wizard
in SQL Server Management Studio works as well.
For SQL Server Management Studio, in the first release,
you could only connect with a Query Explorer window.
And today, you can also connect with your Object Explorer,
even though not all aspects of the Object Explorer work,
but it is a much better experience
than in that first release.
For the big Transact-SQL features that have been added,
an important one is the TIMESTAMP column,
also known as ROWVERSION column.
Also, GRANT and REVOKE are now supported
for granting object permissions.
You can now create database users, or database principals,
and you can group them together with user-defined roles.
Cross-database queries work now,
including cross-database procedure executions,
and we have support for lateral joins.
That is CROSS APPLY and OUTER APPLY.
There are many more smaller enhancements
to Transact-SQL features and functions,
but these are really the big ones.
For more information about Babelfish,
go to the documentation link shown here,
but it may be easier to just do a web search
for Aurora Babelfish documentation.
For the Compass tool,
which will be discussed in a separate video,
just search for Babelfish Compass,
and that will take you to the GitHub repository,
where you can download it.
Now, after all this,
you should really try Babelfish yourself.
It's pretty simple.
Go to the RDS service at AWS,
hit the Create database button,
and choose the Aurora Postgres Compatible Edition.
The important thing is to tick the box
that says Turn on Babelfish.
That will get you a Babelfish version of Aurora Postgres.
And once you have that, you can use it, and try it out.
I hope you will enjoy Babelfish.
Please check out our other videos on Babelfish.
And for now, thank you for watching.
(no audio)