Database conversion between the most popular DBMSs

Today many companies determine to publish their data on the Internet trying to expand their business and make their information more accessible. The IT industry proposes a wide range of original solutions for resolving data inconsistency problems that publishers inescapably face when exporting their data as they need to properly access, process and interchange large amounts of information mainly through the web.

Database design is considered to be the most opportune manner to arrange data on the web in an organized way so that both you and your clients can easily access them.

Usually, users start from simple desktop database tools and then increasing size of data force them to migrate information to some kind of server databases.

It doesn't matter whether this article is your first encounter with databases or you're familiar with them for a long time. The article will be equally useful both for a newbie and for an experienced publisher. The article covers some critical issues such as database conversion, synchronization, etc. important to know for database administrators, database architects, database owners, database driven website developers and for all who use database approach in their fields of activity. At the end of reading you will surely make the right choice of a database application that is the most suitable for your needs.

It's significant to know how data parts will interact with each other, how data can be made accessible online with a web site as its front-end and a database server as its backend. Even if from the beginning you do not know all the future needs, the most important thing is to forecast the initial architecture to migrate the data to server platform without difficulties afterwards.

Database classification

Talking about databases we firstly need to indicate how databases are organized and represented.

To handle a database a variety of database management systems (DBMSs) are developed to streamline the process. DBMSs were primarily designed to manage vast databases at a much faster speed than the solutions that formerly existed.

Presently the most popular structure for database design is considered to be the relational one. In this concept, tables represent the primary amount of data. Logically typed relations can be created between tables in one database. Such commercial database as Microsoft's Access is an example of a relational database. Because of the popularity of relational databases, we will clear up the most important advantages for this type of databases.

All DBMSs can be classified as desktop and server applications. Usually the desktop DBMSs are considered to be single-user while server-based DBMSs are multi-user systems.

Desktop DBMS is a system that imposes constraints on the size of a database. Along with actual advantages a desktop DBMS has storage management limitations so it is usually used as an individual or single-user database application. Because of these restrictions, many companies wish to extend their business to assist more customers and users to migrate their data to a storage warehouse with greater capabilities.

Server-based DBMS as a standalone and distributed database can support as many connections as needed capable of being used by several people at once. A multiuser server-based DBMS, as its name means, must enable multiple users to access the database simultaneously. This is necessary if data for multiple applications has to be integrated and supported in a single database. The DBMS must include multiprocessing control software to guarantee that several users trying to update the identical data do so under control so that the outcome of the updates is correct. A primary role of server-based DBMS software is to provide that multitasking transactions that must operate correctly.

The most popular desktop database applications are MS Access, MS FoxPro, and SQLite. The widely used server systems are MySQL, MS SQL, PostgreSQL, Oracle, and Firebird. More information about the most popular databases and their usage you can read here http://www.dbconvert.com/overview.php

Some strong and weak aspects of database applications

What's the difference between desktop and server databases?

• Desktop database packages are often low-priced and user-friendly for simple desktop or workteam database management but for server-built application deployment it would be more suitable to use server-based database solutions.
• Desktop database applications usually support one or a few number of synchronous users. Server database applications are usable for web applications where many simultaneous connections are necessary.
• Server database systems provide much more safe security systems for web applications unlike desktop database applications that generally have not strong security.
• Desktop database packages scalability, architecture, and overall performance are usually not optimized for web application deployment or other similar services unlike server-based applications that is ideal for serious database driven site development.
• Installation, configuration, and maintenance for server databases are more difficult and require extensive computer knowledge and advanced skills to deal with while desktop database applications can be used by a beginner.
• Most server database systems can run on Windows, Mac OS X, Linux, and UNIX while desktop database applications usually run only under a single operating system.

Problems and solutions

Generally, DBMSs provide an efficient set of tools that make accessing, organizing, and sharing information easier than ever. Most of them are reliable, fast, and easy to use, with secure and flexible password system that enables host-based verification but to select a right DBMS satisfying the specific needs the publishers should know what problems and possible solutions they can experience working with databases. We suggest four exemplary scenarios below.

Scenario 1:

Small business owners need to categorize their data. For a start it's enough to use some kind of a desktop database. As his business progresses he should handle more data and it becomes necessary to migrate data to a server database system. Generally all server databases are located in the internet thus data migration to server database can help to kill two birds with one stone: multi-user database located on server allows to store and process more data and make it possible to access more users to the same data at a time.

Scenario 2:

A server database contains bulk of data. It's necessary to sort and then process data on a personal computer. Afterwards it is possible to update data on a remote server with information changed on the local computer.

Scenario 3:

Several users input data on their PCs then transfer entered data to the distributed server database. The information entered by users will be accessible for general use after moving to the server. Many organizations have similar databases keeping them updated regularly with the help of synchronization.

Scenario 4:

In order to make the previous scenario simpler you can try the desktop application such as MS Access which contains database, forms, Visual Basic scripts, etc. In order to make it possible to work through a front-end with the data for many users at once the solution is to convert the database to the bundles such as PHP+MySQL/PostgreSQL or ASP/ASP.NET+MS SQL. Following this way the entering data will process in-place using web forms. Also this solution will be a cross-platform and will work equally perfect both for Windows and for Mac OS and Linux.

Two-way conversion

To manipulate data in the right way and eliminate possible problems a proper database tool should contain a set of required options. Two-way data conversion is one of them.

When setting your choice on an efficient application that will be responsible for your database conversion you'd better select a tool that is able to implement two-way data transfer. It will give you an additional opportunity to take more control over your conversion process and will create prosperous conditions for managing.

The chief purpose of every conversion application is to convert one database into another one. In other words data conversion is a data migration process from one DBMS to another one without a loss of data integrity.

The result of conversion is identity of the data: all data from the table in source database must be accurately copied in the table of destination database. Due to the lack of time to learn database technology the life of database publisher can be relieved by many useful tools accessible on the market this time. More information about database conversion you can find here http://www.dbconvert.com/conversion.php

Synchronization

If you need to keep your database updated make sure that a db conversion tool you'd like to purchase accomplishes synchronization option that will enable your data to be reflected and regularly synchronized between databases. It is a common practice for organizations to have similar or identical databases in many systems.

Synchronization process includes data uploading or mirroring between two or more databases. It would be better to update only the tables that have been changed since the last synchronization without converting the entire database in order to minimize the bulk of data that will have to pass through the network. More information about database synchronization you can find here http://www.dbconvert.com/synchronization.php

Preverification of possible conversion errors

Any conversion process is difficult to imagine without any possible data collisions due to the peculiarity of your database structure and its types. Sometimes, it may happen that the general rules of database design are not observed in view of particular requirements. In this case, it is essential to be notified about the program behavior beforehand. As the saying goes, to be warned is to be armed.

It's quite possible that some errors that could bring to the conflict on a destination database are easy to eliminate by a small reconfiguration of the conversion process issues in one touch.

Data mapping

In general, data mapping is the process of creating data element mappings between two distinct data models. Data mapping is usually used as an additional ability for data population. In other words, data mapping is a compatible data types assignment for a destination database that makes your conversion more flexible.

Just choose data types you need for the target fields or the most appropriate data types for the source type and a reliable tool you prefer will automatically transform types from one specific format to another. More information about data mapping you can find here http://www.dbconvert.com/field.php

Data filtering

Data filtering is the additional feature which performs the most advanced method to export definite data. Appling filters user can set complex conditions and as a result receive complete information according to specific criteria separating all the rest records from a table. The purpose of data filtering is to assist the user in conversion of only needed range of information.

It is possible to create new filters on the base of query and easily modify existing queries by adding your own conditions manually. Enabling filters in your conversion process you can reduce time required for migration of all info and retrieve only needed.

Taking into consideration the information above it would be better for you to find a tool that provides easy-to-use data filtering assignment that can be applied to all tables in databases for partial conversion. You can simultaneously determine multiple criteria for a table.

Filtering seems to be especially useful when you need to process tables with large bulk of data. Using filters, it is possible to handle only the records that meet your precise criteria in a second. More information about data filtering you can find at http://www.dbconvert.com/filtering.php

Database schemes support

In the case you want to keep your database safe and secure make sure that a conversion tool you'd like to buy supports database schemes. The application must operate with schemes which exist in a certain database.

The scheme of a database system is its structure defined in a programming language supported by the database management system. In relational database management systems, the schema defines the tables, the fields in each table, the relationships between tables and fields. For each element mentioned above, database users or group of users can be granted definite permissions and access rights specified in scheme.

Usually schemes are stored in a data dictionary. Although a schema is described in text database language, the term is often used to mean a graphic representation of the database structure.

Database schemes are one of the most popular methods used by MS SQL and PostgreSQL administrators for dividing access rights to the information stored on a server. Access rights contain privilege levels for read, write, exclusive, etc. Database scheme support allows customizing access rights with the help of access rights to schemes, tables, etc. This method is very popular among database administrators because it shifts the burden of account management to the network administration staff and it provides the ease of a single sign-on to the end user. For MS SQL there are specified schemes types while creating database schemes types in PostgreSQL is considered to be the prerogative of the administrator.

Getting round the access limitations

Database administrator can deny the access to a database being concerned in protecting the information and avoiding insecure connections. To gain access to a MySQL base you usually need to obtain the permission for connection from your Web hosting provider.

For example, in case your IP address is not static (as dialup connection) and changes each time you get on-line your MySQL server administration can reject giving you a direct access to a database. In this situation saving data of a database in a dump file comes into play. Thus the ability to store data into a dump file is the best way to present deferred conversion, gain more control over the conversion process and resolve the access problem.

The contents of the source database will be stored into a local dump file instead of sending it to the server directly. This file will be delivered to the server where the work with particular database is executed and using this dump file the server administrator will add data to your database. Thus, you will be able to create Dumps and PHP scripts for indirect data uploading on MS SQL, MySQL, and PostgreSQL thereby going a workaround way to possible server restrictions.

Secure shell (SSH) support

Working with data a trustworthy tool must establish stable connection between platforms and provide secure tunnel for safe and quick data running with optional configurations. Thus, a tool of high quality must support secure shell (SSH), a program that allows a user to log into another computer remotely across the Internet, while maintaining complete security. It's a packet-based binary protocol that provides encrypted connections to remote hosts or servers via a secure channel. The encryption used by SSH guarantees privacy and data integrity over an insecure network as the Internet.

SSH is used for port tunneling or forwarding, often as an alternative to a full-blown VPN. What SSH tunneling enables you to do is to transmit all your traffic to the server via your SSH connection.

An insecure TCP/IP connection of a front-end application is forwarded to the SSH program (server or client), which redirects it to the other SSH party (client or server), which in turn redirects the connection to the needed destination host. The redirected connection is encoded and protected on the path between the SSH client and server solely. One of the uses of SSH port forwarding is accessing database servers.

SSH tunneling feature is firstly useful for accessing PostgreSQL and MySQL servers connected remotely.

Automation of the process with scheduler

If scheduling function is available it gives you an opportunity to launch the conversion process on a user required schedule that implies that you can run a program without user interaction. Scheduler enables you to automatically synchronize or convert data regularly without supplementary settings and with the smallest effort. Comprehensive scheduling functionality (creating a scheduled task to run once only as well as daily, weekly, monthly, or at certain times e.g. when a user logs on, modifying the schedule and customizing how a task runs at a scheduled time) is supposed to bring extra flexibility to your business thus you can focus extra efforts on more important activity. More information about scheduling you can find at http://www.dbconvert.com/dc-scheduler.php

Command line

If you have a task that should be performed by scheduler and all converting parameters are known beforehand then there is no need to open GUI interface. The program should be launched in command line mode. Running the application in command line mode as a service allows a user to work with it even not being logged in.

In command line mode session parameters are passed along with executed command. Optionally a user can create file containing the exact syntax of command line, the so called 'batch file'. Batch file is considered to be as basis for scheduled task that will automatically work as service in background mode giving you an opportunity to keep them unattended at anytime after having scheduled it once.

A command line interface is used whenever a large dictionary of queries and commands, coupled with a wide variety of options, can be entered more quickly as text than with only GUI.

Command line interfaces are often used by programmers and database administrators and by technically advanced personal computer users when working with databases.

Conclusion

Now that you're more familiar with problem, what's the solution? That isn't always a simple answer. Just keep in mind that you have to cautiously consider all the factors that affect the choosing a conversion tool that will serve your business in long term period. When come to your decision, make certain of that all your urgent needs are met.

Software products market abounds with various tools providing different solutions. That's why it's very difficult to choose a suitable product that must meet the demands described in this paper. Often publishers face the challenge how to select a conversion tool which can reliably migrate data from one database format to another. Hopefully, this article will help evaluate the right tool you may need when you have a task to transfer data.

In case you consider using specialized db convert applications (http://www.dbconvert.com/) you have a good chance to combine your computer's ability to gather and sort vast amounts of data and the Internet's ability to distribute it globally.

back to top