Welcome!

Linux Containers Authors: Yeshim Deniz, Kong Yang, Derek Weeks, Nishanth Kadiyala, William Schmarzo

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
Amazon started as an online bookseller 20 years ago. Since then, it has evolved into a technology juggernaut that has disrupted multiple markets and industries and touches many aspects of our lives. It is a relentless technology and business model innovator driving disruption throughout numerous ecosystems. Amazon’s AWS revenues alone are approaching $16B a year making it one of the largest IT companies in the world. With dominant offerings in Cloud, IoT, eCommerce, Big Data, AI, Digital Assista...
We build IoT infrastructure products - when you have to integrate different devices, different systems and cloud you have to build an application to do that but we eliminate the need to build an application. Our products can integrate any device, any system, any cloud regardless of protocol," explained Peter Jung, Chief Product Officer at Pulzze Systems, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
SYS-CON Events announced today that Conference Guru has been named “Media Sponsor” of SYS-CON's 21st International Cloud Expo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. A valuable conference experience generates new contacts, sales leads, potential strategic partners and potential investors; helps gather competitive intelligence and even provides inspiration for new products and services. Conference Guru works with conference organi...
Multiple data types are pouring into IoT deployments. Data is coming in small packages as well as enormous files and data streams of many sizes. Widespread use of mobile devices adds to the total. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists looked at the tools and environments that are being put to use in IoT deployments, as well as the team skills a modern enterprise IT shop needs to keep things running, get a handle on all this data, and deliver...
SYS-CON Events announced today that Enzu will exhibit at SYS-CON's 21st Int\ernational Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive advantage. By offering a suite of proven hosting and management services, Enzu wants companies to focus on the core of their ...
In his session at @ThingsExpo, Eric Lachapelle, CEO of the Professional Evaluation and Certification Board (PECB), provided an overview of various initiatives to certify the security of connected devices and future trends in ensuring public trust of IoT. Eric Lachapelle is the Chief Executive Officer of the Professional Evaluation and Certification Board (PECB), an international certification body. His role is to help companies and individuals to achieve professional, accredited and worldwide re...
IoT solutions exploit operational data generated by Internet-connected smart “things” for the purpose of gaining operational insight and producing “better outcomes” (for example, create new business models, eliminate unscheduled maintenance, etc.). The explosive proliferation of IoT solutions will result in an exponential growth in the volume of IoT data, precipitating significant Information Governance issues: who owns the IoT data, what are the rights/duties of IoT solutions adopters towards t...
With the introduction of IoT and Smart Living in every aspect of our lives, one question has become relevant: What are the security implications? To answer this, first we have to look and explore the security models of the technologies that IoT is founded upon. In his session at @ThingsExpo, Nevi Kaja, a Research Engineer at Ford Motor Company, discussed some of the security challenges of the IoT infrastructure and related how these aspects impact Smart Living. The material was delivered interac...
The current age of digital transformation means that IT organizations must adapt their toolset to cover all digital experiences, beyond just the end users’. Today’s businesses can no longer focus solely on the digital interactions they manage with employees or customers; they must now contend with non-traditional factors. Whether it's the power of brand to make or break a company, the need to monitor across all locations 24/7, or the ability to proactively resolve issues, companies must adapt to...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
No hype cycles or predictions of zillions of things here. IoT is big. You get it. You know your business and have great ideas for a business transformation strategy. What comes next? Time to make it happen. In his session at @ThingsExpo, Jay Mason, Associate Partner at M&S Consulting, presented a step-by-step plan to develop your technology implementation strategy. He discussed the evaluation of communication standards and IoT messaging protocols, data analytics considerations, edge-to-cloud tec...
When growing capacity and power in the data center, the architectural trade-offs between server scale-up vs. scale-out continue to be debated. Both approaches are valid: scale-out adds multiple, smaller servers running in a distributed computing model, while scale-up adds fewer, more powerful servers that are capable of running larger workloads. It’s worth noting that there are additional, unique advantages that scale-up architectures offer. One big advantage is large memory and compute capacity...
New competitors, disruptive technologies, and growing expectations are pushing every business to both adopt and deliver new digital services. This ‘Digital Transformation’ demands rapid delivery and continuous iteration of new competitive services via multiple channels, which in turn demands new service delivery techniques – including DevOps. In this power panel at @DevOpsSummit 20th Cloud Expo, moderated by DevOps Conference Co-Chair Andi Mann, panelists examined how DevOps helps to meet the de...
"When we talk about cloud without compromise what we're talking about is that when people think about 'I need the flexibility of the cloud' - it's the ability to create applications and run them in a cloud environment that's far more flexible,” explained Matthew Finnie, CTO of Interoute, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
The Internet giants are fully embracing AI. All the services they offer to their customers are aimed at drawing a map of the world with the data they get. The AIs from these companies are used to build disruptive approaches that cannot be used by established enterprises, which are threatened by these disruptions. However, most leaders underestimate the effect this will have on their businesses. In his session at 21st Cloud Expo, Rene Buest, Director Market Research & Technology Evangelism at Ara...
The current age of digital transformation means that IT organizations must adapt their toolset to cover all digital experiences, beyond just the end users’. Today’s businesses can no longer focus solely on the digital interactions they manage with employees or customers; they must now contend with non-traditional factors. Whether it's the power of brand to make or break a company, the need to monitor across all locations 24/7, or the ability to proactively resolve issues, companies must adapt to...
Artificial intelligence, machine learning, neural networks. We’re in the midst of a wave of excitement around AI such as hasn’t been seen for a few decades. But those previous periods of inflated expectations led to troughs of disappointment. Will this time be different? Most likely. Applications of AI such as predictive analytics are already decreasing costs and improving reliability of industrial machinery. Furthermore, the funding and research going into AI now comes from a wide range of com...
In this presentation, Striim CTO and founder Steve Wilkes will discuss practical strategies for counteracting fraud and cyberattacks by leveraging real-time streaming analytics. In his session at @ThingsExpo, Steve Wilkes, Founder and Chief Technology Officer at Striim, will provide a detailed look into leveraging streaming data management to correlate events in real time, and identify potential breaches across IoT and non-IoT systems throughout the enterprise. Strategies for processing massive ...
SYS-CON Events announced today that GrapeUp, the leading provider of rapid product development at the speed of business, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company, specialized in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market acr...