Despite the fact that PostgreSQL is effective and open source database administration framework, here and there its extensive variety of elements and capacities seem overpowering for specific tasks. For this situation, associations may consider exchanging their database to another DBMS that is all the more simple to utilize. The primary reason for such relocation is to lessen the cost of database upkeep.
Here is the arrangement of ventures to relocate database from PostgreSQL to SQL Server:
– export table definitions from the source SQL database
– convert them into SQL Server organize
– load the subsequent proclamations to the objective SQL Server
– export PostgreSQL information into INSERT-proclamations
– convert these announcements into MS SQL organize
– load it into the objective database
PostgreSQL table definitions can be sent out into DDL script by means of the accompanying summon:
pg_dump –schema-only –no-owner –no-privileges the_db_name > table_definitions.sql
The subsequent script must be rectified before stacking to SQL Server as takes after:
– replace twofold quotes around database question (table, segment, list, imperative) names by square sections
– remove square sections around sorts
– replace default PostgreSQL pattern “open” by SQL Server default outline “dbo”
– replace all PostgreSQL “SERIAL” sorts by SQL Server proportional “INT IDENTITY(…)”
– convert all information sorts that are not bolstered by SQL Server into content shape
– replace the PostgreSQL question eliminator “;” with the MS SQL one “GO”
PostgreSQL information can be traded into INSERT-statements by means of the accompanying command line:
pg_dump –data-only –column-inserts the_db_name > load_data.sql
Another alternative to relocate database from PostgreSQL to SQL Server is to utilize the Integration Services. Here is the succession of vital strides:
– Open the SQL Server Business Intelligence Development Studio and open another Integration Services Project
– In the Toolbox intuitive the Data Flow undertaking to the plan sheet. Double tap in the Data stream Task with a specific end goal to make a Data Flow
– In the toolbar simplified the OLE DB Source and OLE DB Destination and join both errands with the green bolt.
– double tap on the OLE DB Source errand and press the New catch to make another OLE DB association director. In the Configure OLE DB Connection Manager, press “New” catch.
– In the dialog window of Connection Manager specify the Native OLE DB\PostgresSQL Native Provider. Take note of that this choice will be accessible just on the off chance that you introduced the essentials determined toward the start of this tip
– In the Server or File name, determine the name of the Postgres Server
– Select the alternative ‘Utilize a particular client name and watchword’. Check the permit sparing watchword choice. Determine the client to interface with PostgreSQL and the client secret key.
– In the underlying list, select the database used to make the table. Squeeze OK twice.
– In the plan sheet, double tap the OLE DB Destination errand, select the Native OLE DB\SQL Server Native Client and give vital data to arrange SQL Server target database
– In the OLE BD Destination Editor, tap the Mapping page and determine mapping between PostgreSQL to SQL Server tables
As you may see both methodologies require a considerable measure of manual work and get to be distinctly dull process for huge and complex databases. In addition, there is danger of information misfortune or debasement associated with the human variable. To stay away from it and make the database movement from PostgreSQL to SQL Server a completely computerized handle, unique database change instrument might be utilized.
PostgreSQL to SQL Server converter is one of such apparatuses gave by Intelligent Converters, a product organization gaining practical experience in database relocation and synchronization for all prominent DBMS since 2001. The database change instrument has every single fundamental element:
- All renditions of PostgreSQL and Microsoft SQL (counting Azure SQL) are upheld
- Indexes are changed over with every single fundamental trait
- Option to consolidate PostgreSQL information into a current MS SQL tables
- Command line support
- Stores change settings into profile
- Unicode support
PostgreSQL to SQL Server converter keeps running on Windows XP(SP2 or SP3)/2003/Vista/Server 2008/7/8/10. It requires PostgreSQL and MS SQL environment (server or customer parts must be introduced on a similar machine where program is running).
