Migrating data to Windows Azure SQL Database from PostgreSQL

Good afternoon!

In this article I will show how easy and simple it is to migrate (transfer data) from PostgreSQL to the Windows Azure SQL Database cloud database.

The main steps:
  1. Create a new database in the Windows Azure Control Panel
  2. Install ODBC driver for PostgreSQL
  3. Creating a project in the SQL Server Data Tool
  4. Data import


Create a new database in the Windows Azure Control Panel


To create a new database, go to the Windows Azure Control Panel ( https://manage.windowsazure.com/ ). Even if you do not have a subscription, you can use the trial period - $ 200 for one month, this is more than enough for various experiments.
Click the + New button in the lower left and select Data Services> SQL Database> Quick Create . Fill in the required fields - database name, data center region and login / password for accessing the database:



After a moment, a message should appear that the creation of the database was successful:



By default, a database is created with a maximum size of 1 GB - after reaching the upper limit, the database goes into read-only mode. However, if necessary, the size can be increased up to 150GB.

Install ODBC driver for PostgreSQL


Now you need to install the ODBC driver for postgresql. Download the latest version from the official site (download the 32-bit version, there are less problems with it). Attention! The driver must be installed on the computer on which data will be copied in the future, and not on the database server.



Installation is elementary and does not cause any problems - Next-Next-Next-Finish.

After that, you need to add ODBC Source for both PostgreSQL and SQL Database- just enter odbc on the start screen and select ODBC Data Sources (32-bit) from the list.





In the window that appears, click Add ... , select PostgreSQL Unicode in the list and click Finish



After enter all the necessary data:



Now one of the points for which this article was written - on this window, click the Datasource button and be sure to check the Use Declare / Fetch checkbox .



Without this, everything will work as long as there is little data, but it will crash with the Out of memory error on large volumes - so, I had this error on a table with approximately 60 million records.

Now also create a Data Source for the destination server in the cloud - only select SQL Server Native Client from the list, and the connection data can be viewed in the server control panel by clicking on “View SQL Database connection strings for ADO .Net, ODBC, PHP, and JDBC ". And, as this window reminds us, we must not forget to allow connections to the database from this IP address - by default, connection is possible only from the IP addresses of Windows Azure services (and if this option is enabled in the server settings) the



setup process is also the same complexity, and after that you will have approximately the following picture:



Creating a project in the SQL Server Data Tool


If the migration occurred with MySQL, then everything would be simpler - there is a wonderful tool SQL Server Migration Assistant (SSMA) , which supports, among other things, MySQL, and allows you to migrate very simply and quickly. But for PostgreSQL this is not, so you have to use the SQL Server Data Tool (which, however, is also not difficult). Launch the SQL Server Data Tool, click New Project and create a project based on the Integration Service Project template.



Drag the Data Flow Task from the Toolbox, double-click it - you will go to the Data Flow tab.



Drag and drop the ODBC Data Source and ODBC Destination into the workspace and connect them with the blue arrow coming from ODBC Source:



Double-click on ODBC Source, in the window that appears, click New, then New again and select our data source:



Double-click Ok to close these windows and select the table from which the data will be imported:



Now you need to configure the data receiver in the same way - ODBC Destination





In the same place you can adjust the correspondence of the columns, if necessary:



Now one more important point - you should switch the project start to 32-bit mode. To do this, right-click on the project in Solution Explorer, select Properties , and in the Configuration Properties -> Debugging section set Run64BitRuntime to False .



Data import


Now you are ready to transfer data! Press F5 or the “Start Debugging” button on the toolbar. Indicators will appear in the workspace that indicate that data is being transferred:



And in the Progress panel, you can watch a text report:



After making sure that everything is going as planned, you can go drink coffee / tea - this is a fairly long process (in my opinion, SSMA worked faster). After completing the process, the data source and receiver will have green ticks:



And in the Progress window, a text report:



As you can see, 570K lines were copied in 50 minutes. You can connect to the cloud database and make sure that the data is really there:



This is how you can easily and quickly transfer a large amount of data from PostgreSQL to the Windows Azure SQL Database cloud database.

References

  1. Windows Azure Trial
  2. Windows Azure SQL Database Help Center

Also popular now: