Database migration from SQL Server to PostgreSQL

Microsoft SQL Server and PostgreSQL are both very popular RDBMS havingrich set of administration tools and programming APIs. However, MS SQL has quite restrictive licensing policyand high total cost of ownership while PostgreSQL is open source released under Open Source Initiative Approved License.

Shall we consider this economic aspect more precisely. License prices of SQL Server are ranged from $3K for the Standard Edition to more than $10K for the Enterprise edition (two cores). A free version of this database management system is only available to students and developers for internal using. The use of PostgreSQL is free even for any commercial purpose.

That reason forces many organizations migrate their databases from SQL Server to PostgreSQL. However, before beginning this complicated procedure it is reasonable to compare two DBMS to find all possible bottlenecks of the migration.

Scalability

In terms of scalability SQL Server provides enhancements to the on-disk storage for memory-optimized tables. It offers multiple concurrent threads to make memory-optimized tablespersistent, multithreaded recovery and merge operations, dynamic management views.

PostgreSQL implements scalability though indexing and partitioning options to improve data operations and query performance on tables. Table scalability can be greatly improved since partitions and Indexes can be placed in separate tablespaces on different disk file systems. Horizontal table partitioning is not supported in PostgreSQL.

Computed Columns

SQL Server supports computed columns with a number of limitations: it cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. Also, computed columns cannot be used in INSERT or UPDATE statements in MS SQL.

PostgreSQL supports the same feature since v12 declared by the term ‘generated columns’.Generated columns cannot be part of a partition key; they can only reference the current row and cannot use subqueries. Generated values cannot be included INSERT or UPDATE,however DEFAULT constraint is accepted.

Identity Columns

SQL Server uses identity column property for generating unique key values for rows. Identity declaration includes initial value for the first row (seed) and amount to increase value over the previous row (increment).

PostgreSQL provides special data type SERIAL that allows to assign a unique value to an identity column automatically. Unique constraint or primary key over SERIAL column must be specifiedjust like for other data types.

Spatial Data

SQL Server provides‘geography’ data type for storing spatial data.

PostgreSQL does not have native support for spatial data. The 3rd party open-source PostGIS resource can be used to support for spatial objects.

Views

SQL Server provides user-defined and system-defined views to restrict access to the data. Views can be automatically updated using triggers. Materialized views are called ‘Indexed Views’ in SQL Serves. Unlike materialized views in other RDBMS, indexed views are synched to the underlying data and updated automatically.

PostgreSQL supports generic and updatable views. Views created with simple queries (without recurrency, subqueries, etc.) can be updated; complex views can be updated using rules. The DBMS also supports materialized views. Toupdate data in materialized views the statement‘REFRESH MATERIALIZED VIEW’can be used.

Migration Tools

Differences between SQL Server and PostgreSQL listed above prove that migration between the twodatabase management systems requires many efforts when doing it manually. Human factor leads to high risk of data loss or corruption caused bymigration errors. This issue can be resolved using special database conversion software.

One of such conversion tools is SQL Server to PostgreSQL developed by Intelligent Converters, companyspecializing in database migration and synchronization between all popular database management systems like MS SQL, PostgreSQL, Oracle, MySQL and others. Theconverter has extremely high performance due to direct importing and exporting data without anymiddleware libraries or components. It supports all modern versions of MS SQL and PostgreSQL including cloud solutions. Command line support is a powerful option to automate the database migration.

For those cases where PostgreSQL server declines remote connection, the program can convert the source data into local SQL script file containing statements to create tables, indexes, constraints and to insert rows. Then the person responsible for database migration can load that file into PostgreSQL database via standard tools like psql or pgAdmin.

Tech

Increase productivity of small industries with SEO

Business requires lots of action with well planned schedule to be more successful which helps them to be productive to gain more profit. Hosting the business in online is the best way to increase the process with different countries customers and that helps the small business to get intro with the different dealers or distributors. […]

Read More
Tech

 How To Hire Web Developers For Your Business And Forget All Your Worries: WebCitz

Are you planning to launch your own business and think of developing a website for it? But, you have no coding skills or knowledge about how to hire developers. You might be wondering where to find such professionals, what their hourly rate will be, how much they will cost upfront, and how do you explain […]

Read More
Tech

Use The Accessibe WordPress Plugin to Double Web Traffic to Your Site

As the WordPress site owner, you are aware that more visitors imply more lead conversions. Now, if you are struggling with bringing in more web traffic to your site, there are some simple things that you can do to get better results. Embracing them ensures that you rank higher in search engines and boost web […]

Read More