Validating SQL Server to PostgreSQL Migration

Controlling outcomes of SQL Server to PostgreSQL migration is usually a daunting job for a lot of. Nevertheless, an unequivocal understanding of the assorted objects that have to be validated within the PostgreSQL database after migration is an successfully sensible option to start. Here’s a checklist of database objects that must be verified:

• Desk constructions
• Information
• Major keys and indexes
• International keys
• Views

Desk Buildings

SQL Server exposes desk construction as follows:

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

The usual PostgreSQL console shopper device psql discover desk definition by working the command d table_name

Desk construction is transformed correctly when every column has an equal sort, measurement, and default worth within the ensuing desk throughout SQL Server migration. Right here is the desk of secure knowledge sorts mapping:

SQL Server	PostgreSQL
BIGINT	BIGINT
BINARY(n)	BYTEA
CHAR(n), CHARACTER(n)	CHAR(n), CHARACTER(n)
DATE	DATE
DATETIME	TIMESTAMP(3)
DATETIME2(p)	TIMESTAMP(p)
DATETIMEOFFSET(p)	TIMESTAMP(p) WITH TIME ZONE
DECIMAL(p,s), DEC(p,s)	DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION	DOUBLE PRECISION
FLOAT(p)	DOUBLE PRECISION
INT, INTEGER	INT, INTEGER
MONEY	MONEY
NCHAR(n)	CHAR(n)
NTEXT	TEXT
NUMERIC(p,s)	NUMERIC(p,s)
NVARCHAR(n)	VARCHAR(n)
NVARCHAR(max)	TEXT
REAL	REAL
ROWVERSION	ROWVERSION
SMALLDATETIME	TIMESTAMP(0)
SMALLINT	SMALLINT
TEXT	TEXT
TIME(p)	TIME(p)
TIMESTAMP	BYTEA
TINYINT	SMALLINT
UNIQUEIDENTIFIER	CHAR(16)
VARBINARY(n), VARBINARY(max)	BYTEA
VARCHAR(n)	VARCHAR(n)
VARCHAR(max)	TEXT
XML	XML

Information

Transformed knowledge could 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 shopper 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 shopper device might run the next question to extract fragment of knowledge:

SELECT * FROM table_name LIMIT number_of_records

Moreover, it’s crucial to test 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 info is migrated from SQL Server to PostgreSQL correctly.

Major Keys and Indexes

Microsoft SQL permits to checklist indexes as follows:

• In a command line shopper (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.sort = ‘U’

ORDER BY o.identify, i.sort

• In Administration Studio, open ‘Design’ view of the desk (see particulars in ‘Desk Buildings’ 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 shopper device psql shows details about indexes on the backside of desk definition generated by the command: d table_name. Indexes are accurately 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 shopper (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 appropriate migration from SQL Server to PostgreSQL for overseas keys is similar as for indexes.

Views

The one option to test that every one views have been migrated from SQL Server to PostgreSQL correctly is to match code of every view with respect to variations between SQL dialects of those two DBMS. That is the way to 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, making certain a profitable SQL Server to PostgreSQL migration requires meticulous validation of assorted database objects. The method includes verifying desk constructions, validating knowledge integrity by way of 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 gives 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 make sure that their knowledge is migrated precisely and effectively from SQL Server to PostgreSQL.