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:
- 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.)
- Migrate to a different vendors database (e.g. SQL Server to Oracle) - I will explore this in depth below
- 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.