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 -:
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 –
SQL Server Configuration Manager >
SQL Server 2005 Network configuration >
Protocols for SQLEXPRESS >
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
- In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
- On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
- 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
- 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
- Execute the following statements to enable the sa password and assign a password.
ALTER LOGIN sa ENABLE ;
ALTER LOGIN sa WITH PASSWORD = ‘<password>’ ;
To enable the sa login by using Management Studio
- In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
- On the General page, you may have to create and confirm a password for the sa login.
- 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
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.
- MySQL Front End
- MS Access import export
- DB Tools Manager