Verifying SQL Server to Postgres Migration

The most important phase of database migration is validating results at the end of the process. Whether you migrate manually or via special database migration tool, it is extremely important to understand what and how to check after the procedure is completed. This whitepaper explains how to verify the primary database entries:

• Schemas
• Data
• Indexes
• Views

Schemas

SQL Server allows to explore schemas in two ways.

• In T-SQL console client use the query EXEC sp_columns @table_name=’table_name’
• In Management Studio click on database name in the left pane, expand ‘Tables’ item, right-click on the appropriate name and select ‘Design’ item

PostgreSQL displays table structure through the query: \d table_name

Schema is considered as properly converted from SQL Server to Postgres when each column has the same type, size and default value in the resulting table. This is the list of safe type mapping:

Data

To verify the data is migrated from SQL Server to Postgres properly, it is required to do visual comparison of random fragment(s) in the source and target tables. SQL Server provides two options to extract data fragment:

• In T-SQL console client use the query SELECT TOP number_of_records * FROM table_name

or

• Click on the appropriate table name in the left pane of Microsoft Management Studio and select ‘Select Top 1000 Rows’ menu item

Postgres extracts fragment of data through the query:

SELECT * FROM table_name LIMIT number_of_records

It is also reasonable to check that source and destination tables have the same number of records. Both SQL Server and PostgreSQL display number of rows as follows:

SELECT COUNT(*) FROM table_name

Indexes

Index is correctly converted from SQL Server to Postgres when it is built on the same columns going in the same order in both source and target tables. Number of indexes and uniqueness also must be the same.

SQL Server provides two options to explore indexes:

• In T-SQL console client run the query

SELECT o.name AS Tbl_Name,

i.name AS Idx_Name,

i.type_desc AS Idx_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

• In Management Studio, open ‘Design’ view of the table (see ‘Schemas’ section above) and click ‘Manage Indexes and Keys’ icon on the toolbar (it is marked red on the screenshot)

PostgreSQL describes indexes at the bottom of table definition generated by ‘\d table_name’ statement.

Views

In order to check that the view is converted from SQL Server to Postgres properly, it is required to compare SQL code of this view in the source and target databases with respect to differences between SQL dialects of these two database management systems.

SQL Server allows explore views list using the query:

select TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS where TABLE_NAME not in (‘sysalternates’,’sysconstraints’, ‘syssegments’)

PostgreSQL uses this query for the same purpose:

select viewname, definition from pg_catalog.pg_views where schemaname NOT IN (‘pg_catalog’, ‘information_schema’)

Here are the most common steps to make SQL Server view or query compatible with PostgreSQL format:

• All square brackets acting as delimiters for object names must be replaces by double quotes
• Default schema ‘dbo’ must be replaced by ‘public’ in all references to database objects
• Query modifier ‘TOP (100) PERCENT’ means to return all rows of the resulting rowset. It must be removed in PostgreSQL equivalent. Other variations of ‘SELECT TOP N’ must be converted into SELECT … LIMIT N
• SQL Server offers ‘FOR XML PATH’ pattern used to merge multiple values into a comma separate string. For example:

SELECT DISTINCT r.category, STUFF(

(SELECT distinct ‘,’+ Cast(a.itemcode as varchar) FROM tbl_price a

WHERE r.category = a.category FOR XML PATH(”), TYPE).value(‘.’,’VARCHAR(max)’), 1, 1, ”

) FROM tbl_price r

It must be migrated from SQL Server to Postgres using STRING_AGG function:

SELECT category, string_agg(DISTINCT itemcode::varchar,’,’) code

FROM tbl_price GROUP BY price

• All system or embedded functions involved in views must be migrated from SQL Server to Postgres according to the following table:

Find more details on database migration from SQL Server to Postgres at the official site of Intelligent Converters, one of the leading software companies in the field of database migration and synchronization.