How to Validate Results of MS SQL to PostgreSQL Database Transfer

As a start, it would be wise to acknowledge the basics and what specifically should be confirmed in PostgreSQL database following the fulfillment of the migration from Microsoft SQL Server. The main objects that should be verified are:

• Table definitions
• Data
• Primary and Foreign Keys and Indexes
• Views

Table Definitions

The following table structure applies to Microsoft SQL Server:

Option 1: Open the T-SQL client and run SQL command EXEC sp_columns @table_name=(table’s name)

Option 2: Open Management Studio and browse the database on the left panel, right-click on table’s name and choose ‘Design’ object

In PostgreSQL table’s properties and definition can be exposed by the command:

d table_name

The conversion of table definition takes place properly, when these conditions are truth:

• source and destination tables have equal number of columns
• the corresponding columns have the same type
• null-properties and default values are the same for corresponds columns in MS SQL and PostgreSQL tables

Here is a table of appropriate SQL Server to PostgreSQL data types mapping.

Data

Converted data can be confirmed by comparing the similarity of specific parts from Microsoft SQL and Postgres derived tables. MS SQL enables data fragment  research of specific parts with the following queries:

• Open T-SQL client and execute the query SELECT TOP number_of_records * FROM table_name
• In Microsoft Management Studio view, right-click on the table and press ‘Select Top 1000 Rows’ object

PostgreSQL enables the following type of query to extract a part of data:

SELECT * FROM table_name LIMIT number_of_records

It would be wise at this point to also make sure that MS SQL and PostgreSQL have records of the same number. You can obtain the count of table records with this query:

SELECT COUNT(*) FROM table_name

Primary Keys and Indexes

Ms SQL enables the listing of indexes following this query path:

• In T-SQL client, run the SQL query

SELECT o.name AS Table_Name,

i.name AS Index_Name,

i.type_desc AS Index_Type

FROM sys.indexes i

INNER JOIN sys.objects o ON i.object_id = o.object_id

WHERE i.name IS NOT NULL AND o.type = ‘U’

ORDER BY o.name, i.type

• Open Management Studio and click on the ‘Design’ view of the table (for more information on this, check ‘Table Structures’ part)  and click on ‘Manage Indexes and Keys’ option on the toolbar (indicated in red in the above screenshot)

PostgreSQL will show the index data at the bottom fragment of table definition/properties with the following execution: d table_name

Foreign Keys

Microsoft SQL enables foreign key data extraction with one of these options:

• In T-SQL client, run the following query

SELECT obj.name AS fk_name,

tab_1.name AS table,

col_1.name AS column,

tab2.name AS referenced_table,

col2.name AS referenced_column

FROM sys.foreign_key_columns f_key_col

INNER JOIN sys.objects obj

ON obj.object_id = f_key_col.constraint_object_id

INNER JOIN sys.tables tab_1

ON tab_1.object_id = f_key_col.parent_object_id

INNER JOIN sys.columns col_1

ON col_1.column_id = parent_column_id AND col_1.object_id = tab_1.object_id

INNER JOIN sys.tables tab2

ON tab2.object_id = f_key_col.referenced_object_id

INNER JOIN sys.columns col2

ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

• Open Management Studio and click on the ‘Design’ display of the table then press the ‘Relationships’ button on the left panel of the toolbar, as illustrated in the above screenshot.

Views

There is only one way to ensure that all views have been converted right and that is by comparing the code visually in MS SQL as opposed to PostgreSQL, considering also a few differences between SQL language and both of these DBMS. This way, you can obtain a list of all source and destination database views.

In MS SQL you can display a list of views by executing the following query:

SELECT * FROM sys.views

You can also do the same thing in PostgreSQL with the following query:

SELECT table_name FROM INFORMATION_SCHEMA.views;

Obviously, these steps to control the results of migration take essential time and efforts when doing manually. In order to automate it and avoid risk of data loss or corruption, system administrator or another person responsible for database migration may consider special software tools designed to convert SQL Server to PostgreSQL. One of such tools is MSSQL-to-PostgreSQL by Intelligent Converters.