Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
SQL Query Migration Dll in .net?
Posted Date:
30 Aug 2008
Total Responses:
1
|
Posted By: suman space Member Level: Bronze Points: 1
|
I Have a problem to Convert SQL Server Query to MySQL Query. So I
need dll file in .net for to solve this.
|
Responses
|
| Author: Vidhya 30 Aug 2008 | Member Level: Gold | Rating:  Points: 6 | hi,
follow these steps:
Migration Tools
There are a wide variety of tools available to help you migrate a SQL Server or Access database to MySQL. We'll look at several different tools so you can choose the one that best suits your needs. The tools we will look at will include the following:
MSSQL2MYSQL
Microsoft DTS
SQLyog
Access Export
Text Import/Export
SQLYog and the Microsoft DTS wizard offer graphical interfaces that can be used with both MSSQL and Microsoft Access to import tables into MySQL. MSSQL2MYSQL is a script by Michael Kofler that can convert not only the table structure and data, but converts the index information as well. If you use Microsoft Access you may not have access to the above tools, but you can use the data export features of Access.
MSSQL2MYSQL
MSSQL2MYSQL is a creation of Michael Kofler, author of The Definitive Guide to MySQL by Apress. MSSQL2MYSQL is a Visual Basic script that can be executed using either a Microsoft Visual Basic 6 installation or an application that supports VBA such as Microsoft Word or Excel.
Details on usage can be found at the author's web site, which also includes a listing of GUI front-ends that can be used to make MSSQL2MYSQL a bit more user-friendly for non-programmers.
To use MSSQL2MYSQL with VB6, simply copy the text located at http://www.kofler.cc/mysql/mssql2mysql.txt and paste it into the code section of a VB form. You will need to change the constants at the beginning of the code to match your SQL Server and MySQL installations, and you can then proceed to run the VB6 application and your conversion will take place. MSSQL2MYSQL does not provide any visual feedback on the progress of your conversion, and provides a simple messagebox upon completion.
A nice feature of MSSQL2MYSQL is the ability to dump all statements into a text file, which you can then review and edit before executing on the MySQL server.
Microsoft Data Transformation Services
Microsoft DTS is a data manipulation tool that is included with Microsoft SQL Server. DTS is excellent for moving data between various formats and systems such as databases, spreadsheets, and even HTML. The Microsoft Data Transformation Service can be very complex, but most of us will only ever need to use the Import/Export Wizard that is included with DTS.
Using DTS is fairly straightforward, you choose an ODBC data source to read data from, and then select an 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.
DTS also has the ability to perform scheduled data transformations, something that can be very useful when you are using MySQL for analysis of SQL Server data or when you just want the latest data available as you work on your application migration.
SQLyog
SQLyog is a third-party commercial tool available to help administrators manage MySQL in a GUI environment. SQLyog is provided by by webyog, a MySQL partner, and a thirty day trial of the tool is provided. SQLyog provides an ODBC import tool that is similar to DTS, offering a straightforward interface that is perhaps even simpler to use than DTS.
SQLyog is capable of scheduled imports of data, and can also be used to synchronize both data and schema between multiple MySQL servers.
Access Export
If you are a Microsoft Access user but do not have access to Microsoft DTS or SQLyog, you may want to use the export capability of Microsoft Access. Access can export its tables to a variety of formats, including ODBC. This allows you to export an Access table to MySQL by way of the Connector/ODBC ODBC driver provided by MySQL AB.
To export an Access table to MySQL, right-click on the table in question and choose the 'Export' option. After several steps your data will be exported to MySQL. The column-type choices made by Access may need to be modified, and you should be aware that Access will not export index information with the data, meaning that you will need to implement indexes on your tables after exporting them.
Text Import/Export
One final way to import data is to export the data from MSSQL/Access in a text format and import it directly into MySQL. When exporting, common formats such as tab-delimited or comma-delimited will work fine for later import into MySQL.
When taking this approach, you will need to manually create the MySQL tables, then import the data with the LOAD DATA command in the mysql command-line client. Additional information on the LOAD DATA command can be found in the "LOAD DATA INFILE syntax" section of the MySQL Reference Manual.
While perhaps the most labor-intensive and time-consuming, this approach gives you the highest level of control over table schema as you manually create the tables before importing data.
Application Modifications
Every database application is different, and as such there are no hard and fast rules that will apply to every application migration. Below we will discuss some of the areas that most developers will need to consider when migrating an Access or SQL Server database to MySQL.
Access Control
Many Windows applications use integrated Windows NT security to provide access control to their databases (also known as SSPI). This functionality is not currently available in the MySQL server and such authentication will have to be moved to the client application. Additionally, MySQL offers a high level of granularity when specifying database privileges, which can help increase application security when properly implemented.
Cursors
Although server-side cursors are a pending feature for MySQL, true server-side cursors are not currently implemented. If your application currently uses server-side cursors you may need to evaluate your application and determine whether the simulated server-side cursors provided by Connector/ODBC are adequate, or whether similar functionality can be achieved with client-side cursors in your application.
Stored Procedures And Views
Stored procedures and views are newly implemented features in MySQL 5. If your application relies heavily on either of these features it is recommended that you base your migration on the MySQL 5 server or find a way to move your stored procedures into the client application. MySQL is basing stored procedure syntax on the ANSI SQL standard, which will result in some incompatibilities between T-SQL syntax and MySQL stored procedure syntax. You should plan to perform rewrites on all but the most trivial of T-SQL stored procedures to bring then into conformance with the ANSI SQL standard.
General SQL Syntax
Almost all relational database systems deviate from the SQL standard in one way or another, often to add enhancements and other special features that were not addressed in the original standards. One key to a successful application migration will be to identify SQL queries and statements used in your application that will be incompatible with MySQL.
One area of concern is quoting of table names; While Access uses square brackets (i.e. SELECT myfield FROM [my table]), MySQL instead uses back-ticks (i.e. SELECT myfield FROM `my table`). When possible it is best to avoid using table names that require quoting. When this is not possible you will need to change your queries accordingly.
MySQL Optimizations
When migrating an application, it is important to note that MySQL has certain advantages that can be exploited in your application. MySQL is often faster at creating and destroying connections within an application than its counterparts, which can affect how you go about creating and destroying connections when developing. In addition, there are specialized functions available from within MySQL that can cut down on the amount of client-side programming needed. Finding and utilizing these advantages can help improve application performance and simplify client-side development.
Deployment Considerations
The migration of a database and client application is not a trivial undertaking. Not only does such a procedure take time to complete, but it often has to be performed on production systems with a minimum tolerance for downtime. The following are a few recommendations to take into consideration when migrating and deploying a database application.
Give Yourself Enough Time
Even the best planned migration can take longer than expected. When budgeting your time be sure to factor in unexpected delays and external interruptions. It is better to over-estimate on a migration project and be done early than to overshoot your schedule.
Perform Trial Migrations
Be certain to perform trial runs of your data migrations before doing any final work. I would recommend giving yourself at least a week leeway before any deadline to allow time to fix any problems that crop up during trial runs. Be careful about making changes between your last successful trial run and your production migration as even the most trivial of changes to your migration tool/script or client application can spell disaster during a production conversion run.
Perform A Limited Rollout Where Possible
If it is feasible in your situation, consider performing a limited deployment of your new system. Perhaps you have one or two customers/branches who are willing to try your new MySQL powered version in a beta program before you roll the update to all your customers. If this is not possible, you may be able to run one or two terminals in a test environment with live data pulled from your existing system using the scheduling capabilities of the migration tools mentioned earlier. In either case this would allow you to test the system with real data and real users before pushing your changes to all users as a whole.
Have Maintenance Plans In Place
Before beginning a production migration you need to ensure that you have an effective disaster recovery plan in place. Ensure that your backup hardware will be compatible with your new MySQL database, and that you have scheduled backups in place with a tested plan to recover your data. Because of the nature of data migrations, you may wish to perform backups with increased regularity in the initial weeks after conversion.
|
| Post Reply |
|
|
|
You must Sign In to post a response.
|
|
|
|