Before migrating from an open source RDBMS, do your homework

Migrating from an open source RDBMS to free versions of database systems like SQL Server or Oracle might be tempting. Here are a few things you need to first consider.

This Content Component encountered an error

You saw the allure of the open source RDBMS, or relational database management system. Some time ago you implemented a MySQL database to support, say, a Web application that provides registration, subscription and e-commerce services. The database is reliable and more than adequate for most of your business needs. The best part is you have access to the source code and the product is free.

Now you’re evaluating your future growth and projected business requirements and you're wondering whether you should migrate your data to a more robust commercial system like SQL Server or Oracle.

But commercial products don’t come cheap, unless you go for the free, paired-down version that many of the major players now offer. For instance, you can download SQL Server Express, Oracle Database XE or DB2 Express-C without paying a cent. They might not be open source, but you can’t beat the price. The free versions of the commercial products are not nearly as robust or feature-rich as the ones that cost money, but they have features not available in the open source products. At the same time, the free commercial products can’t compete with their open source counterparts in some areas. Before considering a switch, here are a few important questions.

What environmental logistics should you take into account? Often the circumstances that define your database’s environment dictate what changes you can make to your system. For instance, SQL Server runs only in a Windows environment; however, you might be restricted to Linux computers. But let’s say you want to integrate your database security with Windows integrated security. Given that MySQL doesn’t support the Windows model, a switch to SQL Server might be your best solution.

There are many such factors to think about. The free commercial products place limitations on memory, database size and CPU usage. For instance, Oracle Database XE and SQL Server Express limit the database size to 4 GB. There are other factors as well, like support for an application programming interface, scalability requirements or availability needs.

What features should your RDBMS support? When determining whether to abandon your open source RDBMS for a commercial one, first get familiar with the features that differentiate the various database systems. At their core, most RDBMSs have a lot in common, whether they’re open source products or free versions of commercial ones. They adhere to the American National Standards Institute’s SQL standards, support ACID (atomicity, consistency, isolation, durability) transactions, provide security, include stored procedures and triggers, support replication and partitioning, provide indexes and do a whole lot more.

Not all products are created equal, however. SQL Server Express, for example, includes a lot of functionality not available in MySQL, like SQL Server Management Studio, Configuration Manager, Reporting Services, online backup and auto-tuning. MySQL, on the other hand, supports different types of storage engines, offers more partitioning options and is easier to install. And certainly with a product like MySQL, you can’t ignore that it runs on any operating system, the source code is open and the database size is limited only by the file-size limits of the host operating system. The key is to figure out which products support which feature and determine which of those features you can’t live without.

What do you want to implement down the road? This might be the most important question in determining whether to migrate to a commercial product. What the free commercial products offer that the open source systems do not is a smooth migration path to the more robust versions of those products. For example, your organization might want to start performing extensive business intelligence analysis in the next year. For that you want to have a system in place that handles the necessary extract, transform and load (ETL) operations, supports an ever-growing data warehouse, provides online analytical processing  and offers extensive reporting capabilities.

No doubt you could piece together many of these capabilities with various open source products. For instance, ETL products such as Pentaho Data Integration and Talend Open Studio are powerful open source tools you can use to move data. However, a product like SQL Server, which includes not only the database engine, but also its own Analysis Services, Reporting Services and Integration Services, provides a scalable solution that could make long-range planning and subsequent implementations a seamless process. So SQL Server Express might be the way to go.

Where do you go from here? There are no simple answers when considering a migration from an open source RDBMS. Each system has its benefits and drawbacks, and a free product doesn’t necessarily mean it’s cheap. You must take into account the hours necessary to plan and implement various components, learn about and find support for those components and integrate different strategies and systems. In some cases, the solution might be clear-cut. If you’re a Microsoft shop using all Microsoft tools and running .NET applications, SQL Server might be the optimum solution. But if you’re running Linux servers and supporting PHP applications, you might find that an open source product such as PostgreSQL is more than adequate for your current requirements. Yet even in these scenarios, it’s not that easy. You must take into account product limitations and features as well as your future needs. Only then can you make a qualified decision.

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Find more information at
http://rhsheldon.com.

 

Next Steps

Find out how entrepreneurs are migrating to open source RDBMS

Learn what Forrester says about open source RDBMS systems

Get an expert opinion on the open source RDBMS trend

This was first published in October 2011

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close