Validating SQL Server to PostgreSQL Migration

Controlling outcomes of SQL Server to PostgreSQL migration could be a daunting activity for a lot of. Nevertheless, an unequivocal understanding of the varied objects that should be validated within the PostgreSQL database after migration is an successfully sensible approach to start. Here’s a checklist of database objects that should be verified:

• Desk buildings
• Information
• Main keys and indexes
• International keys
• Views

Desk Constructions

SQL Server exposes desk construction as follows:

• In console consumer run the question EXEC sp_columns @table_name=(desk identify)
• In Administration Studio broaden the database within the left pane, then broaden ‘Tables’ node, right-click on the desk identify and choose ‘Design’ merchandise

The usual PostgreSQL console consumer instrument psql discover desk definition by working the command d table_name

Desk construction is transformed correctly when every column has an equal kind, dimension, and default worth within the ensuing desk throughout SQL Server migration. Right here is the desk of protected information sorts mapping:

Information

Transformed information might be validated by visible comparability of sure fragment(s) from MS SQL and Postgres tables. SQL Server permits for exploration of knowledge fragment as follows:

• In T-SQL consumer run the assertion SELECT TOP number_of_records * FROM table_name
• Within the Administration Studio right-click on the desk and choose ‘Choose Prime 1000 Rows’ merchandise

Any PostgreSQL consumer instrument could run the next question to extract fragment of knowledge:

SELECT * FROM table_name LIMIT number_of_records

Moreover, it’s crucial to examine that SQL Server and PostgreSQL tables have the identical variety of information. Each DBMS permits for the variety of desk information as follows:

SELECT COUNT(*) FROM table_name

If each of those validations have succeeded, the information is migrated from SQL Server to PostgreSQL correctly.

Main Keys and Indexes

Microsoft SQL permits to checklist indexes as follows:

• In a command line consumer (e.g. sqlcmd.exe) run SQL assertion

SELECT o.identify AS Table_Name,

i.identify 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.identify IS NOT NULL AND o.kind = ‘U’

ORDER BY o.identify, i.kind

• In Administration Studio, open ‘Design’ view of the desk (see particulars in ‘Desk Constructions’ part of this text) and click on ‘Handle Indexes and Keys’ button on the toolbar (marked purple on the screenshot above)

The usual PostgreSQL console consumer instrument psql shows details about indexes on the backside of desk definition generated by the command: d table_name. Indexes are appropriately migrated from SQL Server to PostgreSQL if:

• Numbers of indexes are the identical in each SQL Server and PostgreSQL desk
• Every index has the identical variety of listed fields
• Every index has the identical properties in SQL Server and PostgreSQL

International Keys

The details about overseas keys could also be obtained in SQL Server by of the next choices:

• In a command line consumer (e.g. sqlcmd.exe) run SQL assertion

SELECT obj.identify AS fk_name,

tab1.identify AS desk,

col1.identify AS column,

tab2.identify AS referenced_table,

col2.identify AS referenced_column

FROM sys.foreign_key_columns fkc

INNER JOIN sys.objects obj

ON obj.object_id = fkc.constraint_object_id

INNER JOIN sys.tables tab1

ON tab1.object_id = fkc.parent_object_id

INNER JOIN sys.columns col1

ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id

INNER JOIN sys.tables tab2

ON tab2.object_id = fkc.referenced_object_id

INNER JOIN sys.columns col2

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

• Within the Administration Studio, open ‘Design’ view of the desk and click on ‘Relationships’ button on the toolbar

PostgreSQL shops details about overseas keys within the service desk “information_schema”, it may be extracted as follows:

SELECT

tc.constraint_name, tc.table_name, kcu.column_name,

ccu.table_name AS foreign_table_name,

ccu.column_name AS foreign_column_name

FROM

information_schema.table_constraints AS tc

JOIN information_schema.key_column_usage AS kcu

ON tc.constraint_name = kcu.constraint_name

JOIN information_schema.constraint_column_usage AS ccu

ON ccu.constraint_name = tc.constraint_name

WHERE constraint_type = ‘FOREIGN KEY’ AND tc.table_name=’table_name’;

Standards of right migration from SQL Server to PostgreSQL for overseas keys is similar as for indexes.

Views

The one approach to examine that every one views have been migrated from SQL Server to PostgreSQL correctly is to check code of every view with respect to variations between SQL dialects of those two DBMS. That is how you can checklist all of the views in each SQL Server and PostgreSQL databases.

SQL Server: SELECT * FROM sys.views

PostgreSQL: SELECT table_name FROM INFORMATION_SCHEMA.views;

Conclusion on SQL Server to PostgreSQL Migration

In conclusion, guaranteeing a profitable SQL Server to PostgreSQL migration requires meticulous validation of assorted database objects. The method includes verifying desk buildings, validating information integrity via visible comparisons, confirming the equivalence of main keys and indexes, inspecting overseas keys, and validating views by evaluating the code for variations in SQL dialects. The article offers detailed steps for every validation course of, emphasizing the significance of consideration to element for a seamless transition. By following these complete validation procedures, customers can confidently be sure that their information is migrated precisely and effectively from SQL Server to PostgreSQL.