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.

Comments

[Jags Ramnarayan]

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!

[Jags Ramnarayan]

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

[Choosing Bedspreads]

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

[rominho_vip]

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

[counter rotating saw]

I appreciate that this article remind me to migrate my database for sometime ,thanks for your kind sharing ...

[ring die]

This is a smart blog. I mean it. Your blog certainly has help me. Its nice to find a blog thats written by a human, rather than autoposting garbage.

[Shef Joles]

I have a question, is there any plugin (gem) that after rendering page can clean and reformat it? By cleaning I mean removing unnecessary new lines and white spaces. presentation folder printing

[Customized Folders]

i like your post Plagiarism can be simple to detect if students turn to fraud essay writing service. I advice not to purchase term papers from papers writing companies ,which cannot show any guarantees about quality.

[crushed ice machines commercial]

To the extent that these arguments are independent of the Magliano disk, they constitute a logical pattern that connects the two disks aside from their obvious resemblance

[tool steel]

Great thanks for sharing this article post.