Welcome!

Linux Containers Authors: XebiaLabs Blog, Elizabeth White, Dana Gardner, Yeshim Deniz, Carmen Gonzalez

Related Topics: Linux Containers

Linux Containers: Article

Migrating to Open Source Databases Running on Linux

Databases Like MySQL, Ingres r3, PostgreSQL, and Firebird Have Aroused a Lot of Interest

Open source databases running on Linux like MySQL, Ingres r3, PostgreSQL, and Firebird have aroused a lot of interest.

Database developers and corporate users are heralding the anticipated the release of MySQL 5.0, which includes enterprise-level features such as stored procedures, triggers, and views.

Last August Computer Associates made Ingres r3 available under the CA Trusted Open Source License and followed up with a Million-Dollar Challenge, an unprecedented offer to the open source community to develop migration toolkits for the system.

Tony Gaughan, senior VP at Computer Associates, says, "The relational database world is evolving. Enterprise customers are demanding rich, functional products that scale, while lowering the total cost of ownership. Ingres has the pedigree of one of the most seasoned and functional products in the market that can be flexible enough to meet the demands of even the largest organizations."

The publicly traded data infrastructure software company Pervasive Software now services and supports PostgreSQL. Marten Mickos, CEO of MySQL AB, as quoted in SearchEnterpriseLinux.com, is optimistic about open source databases this year betting that "We will see increased growth, faster growth than before, in the adoption of open source in the enterprise, not just for MySQL, but across the board."

Though surely not breaking news, there are many compelling business and technical reasons for migrating to an open source database running on Linux. IT organizations are becoming more focused on business value and are asking questions like "Are we overspending on software? Are there more cost-effective alternatives that meet our specific needs and don't lock us into a long-term relationship with a vendor?"

Besides, unlike the desktop, a database isn't a user-facing technology. A change or modification to a database is less likely to "stir a hornet's nest" among a company's transactional or knowledge workers.

From a technical perspective, security and flexibility are being given serious consideration. For example, since Microsoft SQL Server is tightly integrated into the Windows platform, it's exposed to Windows virus attacks and, as a result, is vulnerable. There have been numerous documented virus attacks on Microsoft Windows, the worst attacks targeted at SQL Server. The Sapphire/Slammer worm (www.cs.berkeley.edu/~nweaver/sapphire/) exploited a buffer overflow vulnerability in Microsoft SQL Server. It infected at least 75,000 hosts and caused network outages and unforeseen consequences such as canceled airline flights, interference with elections, and ATM failures.

Companies are valuing more and more the need to assume greater control of their development processes. With open source, not only can a company view and modify the source code to fix bugs and add needed features, they can control the code's future development. After a consultant or vendor has developed a specific open source application for a customer, that customer is free to use a different consultant or vendor for future development, maintenance, and enhancements if it likes.

Since it's unlikely that Microsoft will open source SQL Server anytime soon, I thought LinuxWorld readers might find an overview of how to migrate the data structure and data from SQL Server to open source databases running on Linux valuable. A word of caution - tread carefully! A manual migration is extremely tedious. Each step takes many man-hours to complete. And, due to space limitations, I won't address the manual migration of stored procedures, views, and triggers that, admittedly, are important components of the enterprise-level database.

That being said, if considering a migration from Microsoft SQL Sever to MySQL, I would recommend waiting for MySQL 5.0 since the current versions don't have stored procedures, views, and triggers. PostreSQL, Ingres r3, and Firebird support stored procedures, views, and triggers so those databases are ready for migration and implementation today. For additional instructions on migrating stored procedures, views, and triggers please see (www.versora.com/__files/documentation/database_migrationsec.pdf)

Migrating Data Structure
First, you'll need to export the table structure using SQL Server Enterprise Manager:

  • At the SQL server, launch Enterprise Manager and connect to the database you intend to migrate.
  • Select all the tables that are being migrated, right-click, and choose Generate SQL Scripts.
  • In the dialog that appears, switch to the ‘Formatting' tab. Uncheck Generate the DROP <object > Command for each Object box. Check Generate Scripts for All Dependent Objects.
  • To make things more manageable, you'll probably want to choose Create One File Per Object. Click OK and indicate where to save the script files. This procedure will create a data structure that works only with SQL Server.
Tweaking will be required for the new database. Consider removing the brackets around names and types. Change types to corollary types. Remove the permissions and index statements from the end of each of these files and store them in a temporary "holding" file that will be applied after data is applied (for speed reasons). If statements aren't revised as indicated, migrating the data will be significantly slower.

When completed, copy these files to the new machine (via file sharing, by burning a CD, or any other way you want), and apply them to the new database. Each database has its own way of running SQL script files though most will let you execute scripts via command-line redirection. For example, PostgreSQL has a command-line tool called psql used to import SQL script files. An example command line for PostgreSQL might look something like this:

psql <dbname> -U <username> < sqlscript.sql

The total time needed to move the data structure manually varies depending on which database you're migrating to, how complicated existing tables are, and how many tables there are. Though this isn't a difficult phase, it can be tedious.

More Stories By Jon Walker

Jon Walker serves as CTO of Versora, an ISV providing Microsoft to Linux migration software. Mr. Walker recently has co-authored 2 whitepapers with Novell titled Migrating from IS Web Servers to Apache SUSE LINUX Enterprise Server 9.0 and Migrating File and Print Servers from Windows to SUSE LINUX Enterprise Server 9. Prior to Versora, Mr. Walker was CTO/VP of Engineering for Miramar Systems. Software developed under his direction at Miramar has been deployed to over 20 million computers worldwide. Mr. Walker has also served as senior technologist for Nortel and Xing Technology (now Real Networks).

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
SYS-CON Events announced today that CA Technologies has been named "Platinum Sponsor" of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, New York, and 21st International Cloud Expo, which will take place in November in Silicon Valley, California.
SYS-CON Events announced today that delaPlex will exhibit at SYS-CON's @CloudExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. delaPlex pioneered Software Development as a Service (SDaaS), which provides scalable resources to build, test, and deploy software. It’s a fast and more reliable way to develop a new product or expand your in-house team.
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place June 6-8, 2017, at the Javits Center in New York City, New York, is co-located with 20th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry p...
The explosion of new web/cloud/IoT-based applications and the data they generate are transforming our world right before our eyes. In this rush to adopt these new technologies, organizations are often ignoring fundamental questions concerning who owns the data and failing to ask for permission to conduct invasive surveillance of their customers. Organizations that are not transparent about how their systems gather data telemetry without offering shared data ownership risk product rejection, regu...
WebRTC sits at the intersection between VoIP and the Web. As such, it poses some interesting challenges for those developing services on top of it, but also for those who need to test and monitor these services. In his session at WebRTC Summit, Tsahi Levent-Levi, co-founder of testRTC, reviewed the various challenges posed by WebRTC when it comes to testing and monitoring and on ways to overcome them.
Every successful software product evolves from an idea to an enterprise system. Notably, the same way is passed by the product owner's company. In his session at 20th Cloud Expo, Oleg Lola, CEO of MobiDev, will provide a generalized overview of the evolution of a software product, the product owner, the needs that arise at various stages of this process, and the value brought by a software development partner to the product owner as a response to these needs.
The Internet of Things can drive efficiency for airlines and airports. In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect with GE, and Sudip Majumder, senior director of development at Oracle, discussed the technical details of the connected airline baggage and related social media solutions. These IoT applications will enhance travelers' journey experience and drive efficiency for the airlines and the airports.
In his keynote at @ThingsExpo, Chris Matthieu, Director of IoT Engineering at Citrix and co-founder and CTO of Octoblu, focused on building an IoT platform and company. He provided a behind-the-scenes look at Octoblu’s platform, business, and pivots along the way (including the Citrix acquisition of Octoblu).
In his session at @ThingsExpo, Sudarshan Krishnamurthi, a Senior Manager, Business Strategy, at Cisco Systems, will discuss how IT and operational technology (OT) work together, as opposed to being in separate siloes as once was traditional. Attendees will learn how to fully leverage the power of IoT in their organization by bringing the two sides together and bridging the communication gap. He will also look at what good leadership must entail in order to accomplish this, and how IT managers ca...
In his keynote at @ThingsExpo, Chris Matthieu, Director of IoT Engineering at Citrix and co-founder and CTO of Octoblu, focused on building an IoT platform and company. He provided a behind-the-scenes look at Octoblu’s platform, business, and pivots along the way (including the Citrix acquisition of Octoblu).
SYS-CON Events announced today that CA Technologies has been named “Platinum Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business – from apparel to energy – is being rewritten by software. From ...
SYS-CON Events announced today that Outlyer, a monitoring service for DevOps and operations teams, has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Outlyer is a monitoring service for DevOps and Operations teams running Cloud, SaaS, Microservices and IoT deployments. Designed for today's dynamic environments that need beyond cloud-scale monitoring, we make monitoring effortless so you...
DevOps is being widely accepted (if not fully adopted) as essential in enterprise IT. But as Enterprise DevOps gains maturity, expands scope, and increases velocity, the need for data-driven decisions across teams becomes more acute. DevOps teams in any modern business must wrangle the ‘digital exhaust’ from the delivery toolchain, "pervasive" and "cognitive" computing, APIs and services, mobile devices and applications, the Internet of Things, and now even blockchain. In this power panel at @...
In his session at @ThingsExpo, Steve Wilkes, CTO and founder of Striim, will delve into four enterprise-scale, business-critical case studies where streaming analytics serves as the key to enabling real-time data integration and right-time insights in hybrid cloud, IoT, and fog computing environments. As part of this discussion, he will also present a demo based on its partnership with Fujitsu, highlighting their technologies in a healthcare IoT use-case. The demo showcases the tracking of pati...
SYS-CON Events announced today that Cloud Academy will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Cloud Academy is the industry’s most innovative, vendor-neutral cloud technology training platform. Cloud Academy provides continuous learning solutions for individuals and enterprise teams for Amazon Web Services, Microsoft Azure, Google Cloud Platform, and the most popular cloud computing technologies. Ge...
With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, Cloud Expo and @ThingsExpo are two of the most important technology events of the year. Since its launch over eight years ago, Cloud Expo and @ThingsExpo have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, I provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading the...
In his keynote at 18th Cloud Expo, Andrew Keys, Co-Founder of ConsenSys Enterprise, provided an overview of the evolution of the Internet and the Database and the future of their combination – the Blockchain. Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life settle...
A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, whic...
The best way to leverage your Cloud Expo presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering Cloud Expo and @ThingsExpo will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at Cloud Expo. Product announcements during our show provide your company with the most reach through our targeted audiences.
TechTarget storage websites are the best online information resource for news, tips and expert advice for the storage, backup and disaster recovery markets. By creating abundant, high-quality editorial content across more than 140 highly targeted technology-specific websites, TechTarget attracts and nurtures communities of technology buyers researching their companies' information technology needs. By understanding these buyers' content consumption behaviors, TechTarget creates the purchase inte...