![]() It is especially helpful to identify stored procedures that are not in use anymore - translating and testing complex stored procedures is probably the most time consuming part of a schema translation process. It is essential to do that homework ahead of time so you don't waste effort moving things that aren't needed. We use a few ad-hoc SQL scripts to determine how often database objects are actually used - stored procedures, tables, etc. For example, a recent client of ours claimed they needed to migrate 25 databases, but upon further investigation, less than 20 were actually used in production. There are almost always schemas, tables, and sometimes even entire databases that are no longer in use. Thankfully, PostgreSQL's open-source nature solves many of these problems, as there is usually an extension for any common use case. For example, if your application requires low latency OLAP queries on large amounts of data, you will miss columnstore indexes. We have not yet encountered a case where PostgreSQL couldn't replace MSSQL, but there are definitely use cases that require some of these features. SSIS - Memory optimized tables - Custom file groups - Columnstore indexes - Clustered indexes - PostgreSQL has 6 decimal points of datetime precision, while MSSQL has 7Īlso, watch out for table and column names that conflict with PostgreSQL's reserved keywords! Common examples of these features include: Most non-trivial applications make use of some SQL Server feature that isn't available in PostgreSQL. The point of this exercise is to make a yes/no decision on whether this project is worth your time, energy, and money. You should identify MSSQL features that are not supported by PostgreSQL and find parts of your application that don't need to be migrated. The first part of the process is high level research. We also also have a SQL Server to Babelfish Migration Guide. This guide assumes you have decided to use plain PostgreSQL, without Babelfish. In the end, the choice is really up to you. ![]() However, if your team has lots of PostgreSQL experience and you only have a few SQL Server databases, then it may be worth it to homogenize your data stack on PostgreSQL. If your organization is on AWS and has historically used SQL Server as your main relational database, then it may make sense to investigate Babelfish. Con: As far as we know, Amazon Aurora is the only managed database that offers the Babelfish feature, so you will have to use Aurora, or run Babelfish yourself. Pro: Babelfish, like PostgreSQL, is open-source. Con: Babelfish does not support 100% of SQL Server's features or syntax, so you must find workarounds. Pro: Drastically reduces the amount of work that needs to be done to translate stored procedures, client queries, column types, etc. In our opinion, the most important pros and cons of Babelfish are: Using Babelfish may seem like an easy choice, but there are many factors to consider. In case you're unaware, Babelfish is a PostgreSQL extension from Amazon that allows it to understand queries written in SQL Server syntax. ![]() The first step is deciding whether or not to use Babelfish. Babelfish - Preparation / decision making - Schema translation - Data movement - Final changeover Babelfish This guide explains how to migrate from SQL Server to PostgreSQL. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |