[ad_1]
Many companies follow the long-term modern trend to migrate databases from commercial DBMS to free open-source equivalent in order to reduce total cost of ownership. For this purpose, PostgreSQL is the ideal choice among all free database management systems due to the following advantages:
– it is 100% compliant with SQL standard
– it supports point-in-time recovery
– it supports sophisticated locking mechanisms
– it provides advanced data types such as multi-dimensional arrays and spatial
The most straight forward way to migrate from SQL Server to PostgreSQL manually or semi-manually is known as extract-transfer-load or ETL model. It consists of the following steps:
– All SQL Server database entries (table definitions, indexes and constraints) are extracted in form of CREATE-statements
– Those items are transformed to comply with PostgreSQL syntax of CREATE-statements (with respect to types mapping and naming rules) and loaded to the target database
– SQL Server data is extracted from the source database into CSV files used as external intermediate storage
– The CSV files must be transformed to comply with PostgreSQL format when it is necessary (special attention to dates and binary data)
– The final step is to load the resulting data into the PostgreSQL database
Now, let’s explore each of those steps in details. To extract definitions of SQL Server tables, open Microsoft SQL Server Management Studio, right-click on the database name, then click on ‘Tasks > Generate Scripts’ menu item. Navigate to “Set scripting options” tab in the appeared window, click on Advanced link and select “data and schema” in the ‘General’ section.
The resulting script must be modified according to PostgreSQL format as follows:
– remove square brackets around types
– replace all square brackets around names of database entries by double quotes
– replace all occurrences of the default SQL Server schema “dbo” by PostgreSQL equivalent “public”
– remove SQL Server keywords that are optional and not supported by PostgreSQL (i.e. “WITH NOCHECK”, “CLUSTERED”)
– remove any specifications of filegroup, for example “ON PRIMARY”
– change SQL Server data type “INT IDENTITY(…)” to PostgreSQL “SERIAL”
– convert types that are not supported by PostgreSQL into equivalents (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
– all statements “GO” used to terminate SQL Server queries must be replaced by semicolons “;”
Next step to migrate SQL Server to PostgreSQL is processing the data, which can be accomplished with the use of the Microsoft SQL Server Management Studio:
– Right-click on the database to migrate, then navigate to the Tasks > Export Data menu
– Using intuitive interface of the wizard specify “Microsoft OLE DB Provider for SQL Server” as data source and “Flat File Destination” as destination.
After the export is completed, the resulting data will appear in the specified destination file according to the comma-separated values (CSV) format.
Now it is time to load data from CSV files to PostgreSQL tables through the “COPY” command like this:
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
If you receive a “Permission denied” error, try the “\COPY” command instead.
The steps above illustrate that manual conversion is a time-consuming procedure with high risk of data loss or corruption. Fortunately, there are some special tools which can migrate SQL Server to PostgreSQL within just a couple of clicks making the entire procedure smooth and safe. One of such solutions is MS SQL to PostgreSQL converter, a program having all necessary features to handle migration of large and complicated databases between the two DBMS. It has been developed by Intelligent Converters, software company focusing on database conversion and synchronization techniques since 2001.
Basic features of the converter:
– Schemas, data, sequences, indexes, constraints and views are migrated from SQL Server to PostgreSQL
– All modern versions of on-premises and could variations of DBMS are supported
– Merging and synchronizing PostgreSQL databases with SQL Server data
– Option to script and schedule the database migration via command line version of the tool
– Store configuration of the completed migration into a profile to simplify next launch
Besides those basic features the converter offers a few powerful capabilities to make the migration even more customizable. First is filtering data to migrate via SELECT queries. SQL Server to PostgreSQL converter allows users to compose SELECT query over the source database and then migrate resulting rowset as if it would be a regular table. This option may be uses to filter data, rename columns or tables, merge data from multiple into a single one and many other tasks.
Next capability is called ‘Edit Table’. It allows to completely customize resulting table: change name, type and attributes of any column and exclude some columns from migration. It is implemented via dialog window having easy-to-use intuitive interface, so any technical skills are not required to entirely customize the database migration.
Learn more about how to migrate SQL Server to PostgreSQL and the related software solutions at the homepage of Intelligent Converters.
[ad_2]
Source link