Migrating from MS SQL to PostgreSQL or from MySQL to PostgreSQL

PostgreSQL  is another strong widely used companion in addition to MySQL in the field of free and open source softwares (FOSS). PostgreSQL also has extension names PostGIS which is specially designed to handle Map data efficiently. If you have your existing data in MS SQL or MySQL and want to migrate it to PostgreSQL then follow the following steps-:

  • Firstly, if you are migrating from MS SQL , then migrate your database to MySQL . You can follow my previous post for details. Here is the link of the post Migrating from MS SQL to MySQL.
  • After migrating to MySQL download and install PostgreSQL if not already in your pc from the PostgreSQL website. Ready to install (.bin) files for Linux and .exe files for Windows are available to download.
  • When installation of PostgreSQL is completed it will ask you for adding additional components. Continue on this window by clicking on Next button. If you already have PostgreSQL installed on your pc then start PostgreSQL Application Stack Builder from Start Menu in case of Windows or from Applications Menu in case of Linux. You can also use following command under Linux

#{PostgreSQL installation path}/scripts/launchstackbuilder.sh

Select your server on first Screen and click on Next.

  • Now select EnterpriseDB MySQL migration wizard under Registration required add-ons as shown in following screenshot-:
  • You have to register on PostgreSQL website for installing the above add-on . Then follow on screen options to complete installation.
  • Now run the Migration Wizard from  Linux (Applications–>PostgreSQL Plus Add-ons –>Migration Wizard) or from Windows (Start Menu –>PostgreSQL Plus Add-ons –> Migration Wizard). Below is Screenshot of Migration Wizard-:
  • Now fill in the various fields according to your configuration and then follow on screen instructions to complete the migration process
  • After migrating data you can use phpPgAdmin or pgAdmin graphical tools to log in to your database and verify the transfer process.
Advertisements

Migrating from MS SQL to MySQL Server

I am here discussing about the ways, reasons, problems that can occur while migrating from MS SQL Server to MySQL.

First of all for what purpose these servers are used???

I know this is a stupid question…….but it may be possible that you don’t know about these servers (I also don’t know about MS SQL Server some days back). So don’t worry !!

Ok…These two are database management systems (DBMS) available in client or server packages  used to manage large data in better and organised way than filesystem..

Reasons for migration

As today is a world of open source and everyone is rushing towards free and open source softwares then why not I and you??

Here I am giving some of common reasons –

  • MySQL is free
  • MySQL is fast.
  • MySQL is Cross-platform
  • MySQL supports large Applications Without Database Abstraction
  • MySQL is easy to use

You can find details regarding these facts by searching internet.

Lets Move towards Migration process. I am here explaining the migration process taking example of MS SQL 2005 Express Edition. This is free version of MS SQL but has limited features as it is obvious that Microsoft doesn’t provide you full features without money.

Migration tools -:

  • MySQL Migration Toolkit

This is the most powerful and easy to use tool for migrating your database. This tool is provided by MySQL community and is free but this tool is designed currently for windows (it may be available for Linux in near future)..but as we are migrating from MS SQL which is present on windows so be happy with this fact….

Below is screenshot of this tool

Here is the link of tutorial about using this tool on MySQL website -:

http://dev.mysql.com/doc/migration-toolkit/en/index.html

Note:

If you are experiencing problems while connecting to MS SQL check that whether tcp service of MS SQL is enables on correct port and also check that MS SQL Server authentication should be enabled in addition to Windows authentication.

Steps to enable tcp service and set port number –

Go to

SQL Server Configuration Manager >
SQL Server 2005 Network configuration >
Protocols for SQLEXPRESS >
TCP/IP >
Properties >
IP Addresses>
TCP Port

If this is blank or anything else  change it to 1433 (default port) and restarted the service.

Steps to enable Server authentication login:

To change security authentication mode

  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
  3. In the SQL Server Management Studio dialog box, click OK, to acknowledge the need to restart SQL Server.

To restart SQL Server from SQL Server  Management     Studio

  1. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login by using Transact-SQL

  1. Execute the following statements to enable the sa password and assign a password.

Copy

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = ‘<password>’ ;
GO

To enable the sa login by using Management Studio

  1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  2. On the General page, you may have to create and confirm a password for the sa login.
  3. On the Status page, in the Login section, click Enabled, and then click OK.

Tell me if you are experiencing other problems while migrating using this tool.

  • Migration using Microsoft DTS service

Microsoft DTS ( Data Transformation Services ) is a tool available in MS SQL which provides a lot of features including Import and Export Wizard which I am using here. This tool is mainly used to backup MS SQL data in some other form.

Using DTS is fairly straightforward, you choose a ODBC data source to read data from, and then select a ODBC data source to convert the data to. You are then given a list of tables to convert, with an option of renaming the destination table and even performing basic transformations on the data before it is inserted into the target database. These transformations are performed using Visual Basic scripting. In addition, you are given control over the table creation statements to be used, allowing you to fine-tune the MySQL table definitions to add parameters such as table handler (InnoDB, BDB, etc) to the script that will be executed.

Installing DTS in MS SQL 2005 Express

DTS is available under  Microsoft SQL Server 2005 Express Edition Toolkit. You can download it from here

http://go.microsoft.com/fwlink/?linkid=65111

Using Import Export Wizard -:

Run  “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe” and follow instructions given on-screen.

  • Migration with the help of third-party softwares

Here are names of some third-party softwares which are shareware and are available for windows or Linux (or both). You can use Import/Export features or Data Transformation/Migration Wizard present in these tools.

  • SQLyog
  • Navicat
  • MySQL Front End
  • MS Access import export
  • DB Tools Manager