« Scale out your identity management | Main | The cost of High Availability (HA) with Oracle »

February 23, 2010

When to migrate your database?

Why migrate your database? Efficiency and availability problems are harming your business - reports are out of date, your batch processing window is nearing its limits, outages (unplanned/planned) frequently halt work. Database consolidation removes the costs that result from a heterogeneous database environment (DBAs time, database vendor pricing, database versions, hardware, OSs, patches, upgrades etc.). OK, so the driving forces for migration are clear,  what now?

What's the cost of my database?

Costs cannot be simply measured in terms of licenses, hardware, DBAs etc. It is more complex and requires thorough analysis on both a business and technical level.

1) A utilities company used Oracle. During the downturn a cost cutting strategy was undertaken to benefit from free database licensees and they migrated to the open source MySQL. The migration process took a couple of months and was initially a success. Problems surfaced with a sudden growth in customer demand – frequent outages occurred and because there was no clear plan for migration they could not address the underlying cause. The cost to business was so great that it was decided to migrate back to Oracle.

Main cost to business? During outages, the resulting backlogs had to be manually processed.  Production standards could not be met in the desired time frame. Short-sighted planning had cost the company dearly.

2) In the early 90s, a manufacturing company buys the latest hardware and databases to support their production processes. As the years advance and production increases, efficiency problems appear as a result of the greater load. Managers are progressively viewing older and older data. Eventually they reach the point where they can only view today's data, tomorrow.

Main cost to business? Malfunctioning devices or defective materials, if not spotted immediately, can result in erroneous assembly of an entire batch, causing a loss of thousands in one shot. Ideally the system would operate in real-time, ensuing production quality with proactive decision making.

How to proceed?

The first step is analysis. Determine where the needs of your business lie and evaluate whether your existing IT is meeting them. It is paramount to clearly define where you start and where you want to go.

Example starting points:

  • Efficiency problems delaying decision making – product batch is failing the same test, why?
  • HA unsupported leading to frequent outages (unplanned/planned)
  • Heterogeneous database infrastructure costing time and money in licenses, DBAs maintenance etc.
  • 15 year old code base – unmanageable due to developer comings and goings with no formalized documentation

Example end goals:

  • Real-time data decision making – we know why products are failing the same test. We can act.
  • HA supported, users are no longer prevented from working
  • Homogeneous database infrastructure – license cost reduction reduced, DBAs freed from maintaining an incoherent infrastructure.
  • Standardized code base adhering to modern best practices (versioned, documented, tested etc.)

After you have identified where you want to go, the next step is to prioritize the goals based on their ROI.

Types of migration

Based on the goals, we need to evaluate the migration type that will fulfill them:

  1. Migrate to your vendors latest database version - offers the least rewards, but with minimum effort. Enables you to benefit from increased functionality, better performance, availability and security. Even if the vendor says that migration will be flawless, experience shows that manual tweaking will always be required to achieve the same functionality of your database schema (tables, procedures, functions, views etc.)
  2. Migrate to a different vendors database (e.g. SQL Server to Oracle) - I will explore this in depth below
  3. Migrate to a non-relational storage store - allows for Web 2.0 Petabyte scalability. We blog about the pros and cons here.

Example: Migrate from vendor A to vendor B's database

Business logic - Most vendors provide tools that enable DBAs to translate from one vendors schema (tables, procedures, triggers etc.) to another. Of course, the functionality varies and specialist knowledge and manual tweaking will always be required. In essence, the more functionality you use that is specific to your database, the greater the amount of manual refactoring.

Data - Again, vendor tools can be used to migrate data from one vendors database to another. In the worst case scenario a DBA can simply create their own script to import.

Interfacing applications - The final step is to configure the applications that interface with the database inserting, updating and reading the data. A simple task, but one that requires careful analysis to identify and reconfigure the applications without causing costly downtime.

Testing - As with any project, a substantial amount of testing is required ensure the maintenance of existing functionality. This must be performed incrementally to identify problems before they become blurred by others. Best practice is to run the existing production database with the migrated database in parallel over a bedding in period (1-3 months depending on complexity). Data differences of the production data are recorded, reported as bugs, prioritized and fixed accordingly.

What can be achieved?

A financial services firm continued to use the same database over a number of years without upgrading. Batch report generation times were getting longer due to the ever increasing data. IT management took the decision to act and migrated to a different vendors database in addition to investing in more horsepower.

The initial results were disappointing with only 2X speedup.  After a thorough audit of the migrated database, it was realized that the SQL logic (indexes, triggers, jobs etc.) needed to be optimized for the new database. After this was performed,  report generation was 20X faster. The reduction in report generation time opened the door to new revenue channels.

To migrate or not?

There is no straight forward answer, because it strongly depends on a case by case basis. My message is this – spend time and money during analysis to clearly define where the benefits and challenges lie. Ultimately, ROI determines if and what migration is required to realize the goals.

Bookmark and Share

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a01156f69dc6b970c0120a88d52af970b

Listed below are links to weblogs that reference When to migrate your database?:

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Yes, indeed. "There is no straight forward answer .. depends on a case by case basis."
And, there is no "one size fits all" database solution.

For instance, we have found with many of the financial institutions that are dealing with exponentially growing data rates, with extremely "bursty" loads, to be drawn to a main-memory SQL database solution that scales horizontally to be a better replacement strategy. The main driver here is scalability that is just in time and on demand. Traditional clustered DB architectures with a shared disk storage strategy is limiting or too expensive (requiring expensive disk technology). Instead, a truly shared-nothing architecture where commodity hardware can be added or removed just in time is what they care about.
Here is a presentation (http://prezi.com/gkuneyar15kv/) that attempts to provide an quick overview to GemFire SQLFabric from GemStone (horizontally partitioned memory oriented DB).
You can also read more about the solution @ http://community.gemstone.com/display/sqlfabric/SQLFabric


-- Cheers!

The above presentation link should be http://prezi.com/gkuneyar15kv/

Yes, indeed. "There is no straight forward answer .. depends on a case by case basis."

Each case is unique, it is a pity, I went to see the presentation and the link is no longer available.
Nuvenus Chovendus
By: rominho_vip

Ohh finally… this is what I was looking for. I start blogging but got stuck as I do not have enough skills and knowledge but this blog has saved me… I wish you never stop posting… I would suggest you if you can do a little bit more to improve the user interaction. Thanks for such an informative blog.

Techniques and technologies for building scalable systems, scaling out databases, and handling large volumes of data. GridwiseTech company blog.

I think there comes a point when impromtu database repair during working hours, and the amount of down time that this brings, really leads to migration and db development

Techniques and technologies for building scalable systems, scaling out databases, and handling large volumes of data.

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment