Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What might be the best approach when converting or migrating a database containing over 2,000 tables? Where would you begin to tackle such an issue? Steps to take in the first weeks of designing? Risks? Assuming there is hardly any information about the old (current) database?

share|improve this question
4  
I may not be able to answer it but other might need more details in order to answer. Which RDBMS? SQL Server/Oracle/MySQL? Which platform are you migrating to? –  yogirk Apr 15 '13 at 5:53
2  
Do you also have any high availability constraints? How big is the data? 2000 tables might be a a lot of space... or maybe not so much. –  KookieMonster Apr 15 '13 at 6:09
    
Personally I think that having no information about the current database is a blessing in disguise: the current system is what is, not the documentation thereof. –  Colin 't Hart Apr 15 '13 at 8:23
    
How many of these 2000+ tables are generated, or duplicate structures but for different purposes? Is it possible there's a lot of redundancy that can be cleaned up, or the ability to re-generate these tables without actually converting them? To me, it feels like with this many tables, many of them were most likely generated by a tool rather than crafted by hand (but I could be wrong). –  FrustratedWithFormsDesigner Apr 15 '13 at 14:13
add comment

1 Answer

up vote 3 down vote accepted

The approach I take is broadly as follows (and this is by no means complete):

  1. Requirements You need to know the goals of the to be situation.

    • specifics of platforms (hardware, software), versions of these. There may be dependencies in terms of feature availability
    • features used in "from" situation and needed in "to" situation. Think of
      • queueing
      • clustering
      • high availability
      • backup and recovery
  2. Analyse current situation for

    • requirements missing from above
    • DBMS or platform specific features being used
  3. Design for an entirely automated and repeatable approach. This makes it

    • testable
    • able to run overnight/weekend in case of a long process
    • allows for an iterative solution where the database becomes more and more complete during the life of the project
  4. Determine dependencies between systems both on the "from" side and on the "to" side. This will affect the "flow" of the migration.

  5. Decide how the data will be migrated. Amongst others, choices are:

    • dump and reload, probably via some database agnostic format
      • if reloading, it may be easier to go via "temporary" tables and then populate the real target tables from the temporary ones. I've had good experience with this approach.
      • this also makes it easier to allow for "fallout" which can be manually corrected after cutover (but you need to keep this under control too).
    • "suck" the data from the "from" database into the "to" database using some database connection technology (eg Oracle Gateway, PostgreSQL foreign data wrappers). This approach is usually also best when wanting to perform a migration where the target DBMS is the same as the source.
    • decide whether to use scripting (SQL scripts mainly, but possibly also Perl/awk etc) or a tool (eg Oracle used to have a product called Migration Workbench or an ETL tool can be used for this purpose) or even "programmed" solutions in Java/C# etc. This decision will largely be driven by the skillset at hand. My preference is to use SQL wherever possible.
  6. Build it.

    • If the target data model is different, determine mappings. An ETL tool can be handy in these cases, but well written SQL can be just as clear. Like all code, try to keep the documentation on the mapping with the code rather than in a separate document.
share|improve this answer
1  
If the structures will be different, it is worth the time to specifically figure out the mappings of every table/column to the new version. That takes a while in 2000 table migration but having amapping document will help you tremendously. Especially when you have differnt datatypes and you must convert them and what to do if the data cannot convert. Your current system may have dates stored in varchar and has some records where the date isn't a real date, if the new system uses date datatypes for dates, then you have to figure out what to do with the data that won't fit into it. –  HLGEM Apr 15 '13 at 13:56
1  
I'd also add to this that it's probably a good idea to build a reconciliation tool to examine how much data got successfully converted, and how much failed, and also to report on any other problems that occurred during conversion. This can be useful during test runs to help refine the process, until the reconciliation tool reports very few errors (or none, ideally). –  FrustratedWithFormsDesigner Apr 15 '13 at 14:10
    
Editted original to include comment on mapping from old to new. Inadvertently enabled community wiki, but that's OK too :-) –  Colin 't Hart Apr 15 '13 at 15:25
    
Auto CW isn't the best feature of SE! Now I've removed it, it shouldn't come back no matter how many time you edit :) –  Jack Douglas Apr 15 '13 at 15:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.