Loading ...
Sorry, an error occurred while loading the content.

Re: [agileDatabases] Migrating data and code from legacy system to a new one

Expand Messages
  • Amanda Varella
    Pramod, very interesting the approach to incremental migration. Could be a very good strategy. In my context, I see I still will have the following challenges:
    Message 1 of 16 , Jul 20, 2011
    • 0 Attachment
      Pramod,

      very interesting the approach to incremental migration. Could be a very good
      strategy. In my context, I see I still will have the following challenges:
      - The new application will access the new database and the old application
      will access the old one database.
      - There will be some tables that will be so used that both systems (new and
      old) will have to use it. So, I still will have a synchronization effort (I
      think I can�t get away from this)


      David,

      you have a good point. Of course not all the schema is so bad. But we are
      sure that some refactorings must be made.
      Other points:
      - The old system is well known and understood
      - You�ve warned me correctly. The team is formed by VB programmers and will
      have the learning curve for .NET. But at this moment, the time to migrate
      it�s not such a big thing. The current system is live and working
      - One of the reasons I would like to maintain both systems live is that I
      don�t want to wait, maybe three years to have the new system been used. We
      never know how much time it takes, and if there is no real use, in
      production enviroment, many bugs, integration problems can occur.
      - Besides this, new features are required, and I want to minimize the
      problem of one system always behind another, and have two efforts of
      implementations (in the old one and the new one)

      Vasco,

      1. Yes, the new system will use the new schema
      2. Yes, I think I can tolerate some downtime
      3. We plan to have a test suite
      4. Once the whole migration is completed, the old one will be turned off.

      It is a complex schema.
      The only way to have the two applications running on the same schema is to
      have the new application runnning on the old database, because we don�t want
      to alter the VB6 code. But we also want to change some structures of the
      database for the new version.

      Thank you all
      I apprecciate new ideas based on that restrictions

      PS.: Just bought Refactoring Databases

      On Wed, Jul 20, 2011 at 5:43 AM, w-p <w-p@...> wrote:

      > **
      >
      >
      > > One of the strategies that we are thinking is to have is the new
      > application
      > > pointing to the old database
      > > and build a transformation layer
      >
      > Often, you can have a transformation layer in the database as well as in
      > the code. So you can define a set of stored routines that you develop
      > against, or two implementations in the client. I have worked for a
      > company where we were only allowed to communicate to the database
      > through stored routines. That was because of security considerations,
      > but you could use this for structural freedom as well.
      >
      > Good luck,
      > --
      > Willem Bogaerts
      >
      > Applicatiesmid
      > Kratz B.V.
      > http://www.kratz.nl/
      >
      >
      >



      --
      Amanda Varella


      [Non-text portions of this message have been removed]
    • deddy205ar
      Amanda - Will consideration be given to the existing data in the database? There s an activity called data profiling that looks at the actual data in every
      Message 2 of 16 , Aug 30, 2011
      • 0 Attachment
        Amanda -

        Will consideration be given to the existing data in the database? There's an activity called
        "data profiling" that looks at the actual data in every single column & row. Typically
        surprises will be found.

        I remember being burned when doing a mutual fund share split... and I *** ASSUMED *** that shares could only be positive. Oh, wrongo!

        So look deep, look close.

        - David


        --- In agileDatabases@yahoogroups.com, "amandavarell" <amandavarella@...> wrote:
        >
        > Hi,
        >
        > I´m new to this group. Now I´m involved in a decision of a migration of a legacy system. Here is the scenarium:
        > 1 - The system is written in VB6
        > 2 - It uses an oracle database
        > 3 - It will be migrated from VB6 to .NET
        > 4 - The schema will be moved to another database, where naming conventions will be applied, tables can be a little bit different... in other words, the new schema will be a little bit different than the old one
        >
        > Talking to the team, they said that would be necessary 1 year to migrate the whole system. But, usually we tend to give very optimistics estimates, so I imagine that this time can be twice or three times higher, so, a big bang approach is not a option.
        >
        > I would like to know your opinions. Had anybody here had similar experience? I would like to test approachs like:
        > - New system, new database. As I update the new database the old one is also updated. What should I use? Triggers? Views?
        >
        > I apprecciate any ideas.
        >
        > Thank you
        >
      • Paul
        Amanda, We ve been in the process of a legacy system conversion such as yours now for the last 3 years. Coordinating the rewrite of over a dozen interconnected
        Message 3 of 16 , Sep 11 6:18 AM
        • 0 Attachment
          Amanda,

          We've been in the process of a legacy system conversion such as yours now for the last 3 years. Coordinating the rewrite of over a dozen interconnected systems. Like yourself, reorganizing schemas, tables, columns to bring the new system online in the most efficient way possible.

          We've had to write both SSIS (MSSQL to MSSQL) and custom conversions (C# .Net) ETL's to migrate the data from the old to the new and custom SQL validations to prove that the data all made it over.

          All I can say is make data your highest priority in the conversion.

          New code can't be fully tested until you have the migrated data as well as any new data you add on top of it. We run a weekly ETL to prove that the existing data can come into the new system correctly.

          After 2 years we had to spend the last month catching up on the data conversion part, as we had written code/data stubs to keep the code moving while the data conversion parts got further and further behind. Not Fun.

          As the other poster said, look close look deep, make sure every bit is accounted for.

          Paul


          --- In agileDatabases@yahoogroups.com, "deddy205ar" <deddy@...> wrote:
          >
          > Amanda -
          >
          > Will consideration be given to the existing data in the database? There's an activity called
          > "data profiling" that looks at the actual data in every single column & row. Typically
          > surprises will be found.
          >
          > I remember being burned when doing a mutual fund share split... and I *** ASSUMED *** that shares could only be positive. Oh, wrongo!
          >
          > So look deep, look close.
          >
          > - David
          >
          >
          > --- In agileDatabases@yahoogroups.com, "amandavarell" <amandavarella@> wrote:
          > >
          > > Hi,
          > >
          > > I´m new to this group. Now I´m involved in a decision of a migration of a legacy system. Here is the scenarium:
          > > 1 - The system is written in VB6
          > > 2 - It uses an oracle database
          > > 3 - It will be migrated from VB6 to .NET
          > > 4 - The schema will be moved to another database, where naming conventions will be applied, tables can be a little bit different... in other words, the new schema will be a little bit different than the old one
          > >
          > > Talking to the team, they said that would be necessary 1 year to migrate the whole system. But, usually we tend to give very optimistics estimates, so I imagine that this time can be twice or three times higher, so, a big bang approach is not a option.
          > >
          > > I would like to know your opinions. Had anybody here had similar experience? I would like to test approachs like:
          > > - New system, new database. As I update the new database the old one is also updated. What should I use? Triggers? Views?
          > >
          > > I apprecciate any ideas.
          > >
          > > Thank you
          > >
          >
        • wbrianwhite
          Here is how we solved a similar project at my company. We changed from a single database store to a sharded system, while also changing the data model and the
          Message 4 of 16 , Feb 23, 2012
          • 0 Attachment
            Here is how we solved a similar project at my company. We changed from a single database store to a sharded system, while also changing the data model and the way we saved the data to the database.

            Based on a thorough analysis of the domain, a new database system was created. You do have to get this pretty close to right up front. Not perfect, but spend the time thinking it out. In our main application we added a company by company setting to say whether they use the old system or new system. It was actually quad-state: "read/write old", "write both/read old", "write both/read new", "read/write new". Got the system basically working so you could do the same actions in the new system as you could in the old. You have to add a bunch of plumbing for this to work.

            Next is the migration of data part. In our case, the new system was web service based with no direct database access, so when our app was talking to the new system, it was doing it all through web service posts/gets. There is a layer of XSL transforms here too, to smooth over the data model mismatches. So we created a batch job that could look at our old data, and format it so that the web services for the new system could accept that document post. Had the normal batch system type stuff: keep track of where you were, do one company at a time, do all companies in mode "write both", etc. Don't expect to get this right up front. Expect to have to wipe out all the exported data a few time, and be able to re-export it. It's an iterative process. Particularly we hit problems with our oldest data, because our product just wasn't mature way back when.

            Next is the transfer from one system to the other. We would do things like flip all companies to "write both/read old" to get performance metrics on the new database system. We would also set up a system where we could gather up the thousands and thousands of posts happening in prod while this was going on, and get them into dev and test them out there and find anything that was unexpected. So to start transfering, you pick a client (pick yourselves, or a test company) and set to write both/read old for a while. Then kick off the batch export and see how it goes. Then flip them to write both/read new and see how it goes. Start moving different types of companies and monitoring, knowing that you can always fail them back to the old system if they have a critical problem. We finally got everyone read/write new system only. It was a very complicated process. But based on the types of issues we encountered, if we had tried to just shut down for a weekend, and move everyone to the new system, we would have only realized we had unsolved problems when we were irretrievably committed to the new system with no rollback system, and we would have lost major face and major customers.

            Having regression suites in place, which we didn't when we started, are a big help for something like this. This project was analogous to redesigning and rebuilding an airplane while in flight. Dangerous, complicated, and if you do everything exactly right, nobody ever knows you did anything at all. It took us about 18 months from start to finish.

            --- In agileDatabases@yahoogroups.com, Amanda Varella <amandavarella@...> wrote:
            >
            > Thank you for your answers. Completing my scenarium:
            >
            > 1 - It´s a information system, whose purpose is to have a strategic project
            > portfolio and helps executives to make decisions based on simulations
            > 2 - It has been developed for 10 years
            > 3 - Its written in VB6 and the new version will use .NET WPF (desktop)
            > 4 - The changes in the database will not be restricted to naming convetions,
            > but also to new semantics and new organization of tables (these could
            > profound changes, for a better database organization)
            > 5 - There will be a migration from a local database to a enterprise database
            > (integration of some concepts)
            > 6 - We would like to have an improvement of the OO model (actually, creating
            > one from scratch), to have a more maintenable application
            >
            > One of the strategies that we are thinking is to have is the new application
            > pointing to the old database
            > and build a transformation layer, that receives the data in a good design
            > (to the new database and model) and transforms to the old tables. After we
            > change from VB6 to .NET, we could point the application to the new database
            > without the transformation step.
            >
            > Caveats: We are afraid of migrate to the new database only at the end of the
            > proccess and have an integration problem, ex: The OO model could not fit
            > with the new database (impedance mismatch)
            >
            > Thank you
            > Amanda
            >
            >
          • ROBERT
            This is a great post. I hope Amanda is still watching! Bob Corrick.
            Message 5 of 16 , Feb 24, 2012
            • 0 Attachment
              This is a great post.
              I hope Amanda is still watching!
              Bob Corrick.

              --- In agileDatabases@yahoogroups.com, "wbrianwhite" <wbrianwhite@...> wrote:
              >
              >
              >
              > Here is how we solved a similar project at my company. We changed from a single database store to a sharded system, while also changing the data model and the way we saved the data to the database.
              >
              > Based on a thorough analysis of the domain, a new database system was created. You do have to get this pretty close to right up front. Not perfect, but spend the time thinking it out. In our main application we added a company by company setting to say whether they use the old system or new system. It was actually quad-state: "read/write old", "write both/read old", "write both/read new", "read/write new". Got the system basically working so you could do the same actions in the new system as you could in the old. You have to add a bunch of plumbing for this to work.
              >
              > Next is the migration of data part. In our case, the new system was web service based with no direct database access, so when our app was talking to the new system, it was doing it all through web service posts/gets. There is a layer of XSL transforms here too, to smooth over the data model mismatches. So we created a batch job that could look at our old data, and format it so that the web services for the new system could accept that document post. Had the normal batch system type stuff: keep track of where you were, do one company at a time, do all companies in mode "write both", etc. Don't expect to get this right up front. Expect to have to wipe out all the exported data a few time, and be able to re-export it. It's an iterative process. Particularly we hit problems with our oldest data, because our product just wasn't mature way back when.
              >
              > Next is the transfer from one system to the other. We would do things like flip all companies to "write both/read old" to get performance metrics on the new database system. We would also set up a system where we could gather up the thousands and thousands of posts happening in prod while this was going on, and get them into dev and test them out there and find anything that was unexpected. So to start transfering, you pick a client (pick yourselves, or a test company) and set to write both/read old for a while. Then kick off the batch export and see how it goes. Then flip them to write both/read new and see how it goes. Start moving different types of companies and monitoring, knowing that you can always fail them back to the old system if they have a critical problem. We finally got everyone read/write new system only. It was a very complicated process. But based on the types of issues we encountered, if we had tried to just shut down for a weekend, and move everyone to the new system, we would have only realized we had unsolved problems when we were irretrievably committed to the new system with no rollback system, and we would have lost major face and major customers.
              >
              > Having regression suites in place, which we didn't when we started, are a big help for something like this. This project was analogous to redesigning and rebuilding an airplane while in flight. Dangerous, complicated, and if you do everything exactly right, nobody ever knows you did anything at all. It took us about 18 months from start to finish.
              >
              > --- In agileDatabases@yahoogroups.com, Amanda Varella <amandavarella@> wrote:
              > >
              > > Thank you for your answers. Completing my scenarium:
              > >
              > > 1 - It´s a information system, whose purpose is to have a strategic project
              > > portfolio and helps executives to make decisions based on simulations
              > > 2 - It has been developed for 10 years
              > > 3 - Its written in VB6 and the new version will use .NET WPF (desktop)
              > > 4 - The changes in the database will not be restricted to naming convetions,
              > > but also to new semantics and new organization of tables (these could
              > > profound changes, for a better database organization)
              > > 5 - There will be a migration from a local database to a enterprise database
              > > (integration of some concepts)
              > > 6 - We would like to have an improvement of the OO model (actually, creating
              > > one from scratch), to have a more maintenable application
              > >
              > > One of the strategies that we are thinking is to have is the new application
              > > pointing to the old database
              > > and build a transformation layer, that receives the data in a good design
              > > (to the new database and model) and transforms to the old tables. After we
              > > change from VB6 to .NET, we could point the application to the new database
              > > without the transformation step.
              > >
              > > Caveats: We are afraid of migrate to the new database only at the end of the
              > > proccess and have an integration problem, ex: The OO model could not fit
              > > with the new database (impedance mismatch)
              > >
              > > Thank you
              > > Amanda
              > >
              > >
              >
            • Amanda Varella
              Yes I am, Thank you for the ideas! Amanda Varella Enviado via iPad ... [Non-text portions of this message have been removed]
              Message 6 of 16 , Feb 24, 2012
              • 0 Attachment
                Yes I am,

                Thank you for the ideas!

                Amanda Varella

                Enviado via iPad

                Em 24/02/2012, às 07:15, "ROBERT" <bobcorrick@...> escreveu:

                > This is a great post.
                > I hope Amanda is still watching!
                > Bob Corrick.
                >
                > --- In agileDatabases@yahoogroups.com, "wbrianwhite" <wbrianwhite@...> wrote:
                > >
                > >
                > >
                > > Here is how we solved a similar project at my company. We changed from a single database store to a sharded system, while also changing the data model and the way we saved the data to the database.
                > >
                > > Based on a thorough analysis of the domain, a new database system was created. You do have to get this pretty close to right up front. Not perfect, but spend the time thinking it out. In our main application we added a company by company setting to say whether they use the old system or new system. It was actually quad-state: "read/write old", "write both/read old", "write both/read new", "read/write new". Got the system basically working so you could do the same actions in the new system as you could in the old. You have to add a bunch of plumbing for this to work.
                > >
                > > Next is the migration of data part. In our case, the new system was web service based with no direct database access, so when our app was talking to the new system, it was doing it all through web service posts/gets. There is a layer of XSL transforms here too, to smooth over the data model mismatches. So we created a batch job that could look at our old data, and format it so that the web services for the new system could accept that document post. Had the normal batch system type stuff: keep track of where you were, do one company at a time, do all companies in mode "write both", etc. Don't expect to get this right up front. Expect to have to wipe out all the exported data a few time, and be able to re-export it. It's an iterative process. Particularly we hit problems with our oldest data, because our product just wasn't mature way back when.
                > >
                > > Next is the transfer from one system to the other. We would do things like flip all companies to "write both/read old" to get performance metrics on the new database system. We would also set up a system where we could gather up the thousands and thousands of posts happening in prod while this was going on, and get them into dev and test them out there and find anything that was unexpected. So to start transfering, you pick a client (pick yourselves, or a test company) and set to write both/read old for a while. Then kick off the batch export and see how it goes. Then flip them to write both/read new and see how it goes. Start moving different types of companies and monitoring, knowing that you can always fail them back to the old system if they have a critical problem. We finally got everyone read/write new system only. It was a very complicated process. But based on the types of issues we encountered, if we had tried to just shut down for a weekend, and move everyone to the new system, we would have only realized we had unsolved problems when we were irretrievably committed to the new system with no rollback system, and we would have lost major face and major customers.
                > >
                > > Having regression suites in place, which we didn't when we started, are a big help for something like this. This project was analogous to redesigning and rebuilding an airplane while in flight. Dangerous, complicated, and if you do everything exactly right, nobody ever knows you did anything at all. It took us about 18 months from start to finish.
                > >
                > > --- In agileDatabases@yahoogroups.com, Amanda Varella <amandavarella@> wrote:
                > > >
                > > > Thank you for your answers. Completing my scenarium:
                > > >
                > > > 1 - It´s a information system, whose purpose is to have a strategic project
                > > > portfolio and helps executives to make decisions based on simulations
                > > > 2 - It has been developed for 10 years
                > > > 3 - Its written in VB6 and the new version will use .NET WPF (desktop)
                > > > 4 - The changes in the database will not be restricted to naming convetions,
                > > > but also to new semantics and new organization of tables (these could
                > > > profound changes, for a better database organization)
                > > > 5 - There will be a migration from a local database to a enterprise database
                > > > (integration of some concepts)
                > > > 6 - We would like to have an improvement of the OO model (actually, creating
                > > > one from scratch), to have a more maintenable application
                > > >
                > > > One of the strategies that we are thinking is to have is the new application
                > > > pointing to the old database
                > > > and build a transformation layer, that receives the data in a good design
                > > > (to the new database and model) and transforms to the old tables. After we
                > > > change from VB6 to .NET, we could point the application to the new database
                > > > without the transformation step.
                > > >
                > > > Caveats: We are afraid of migrate to the new database only at the end of the
                > > > proccess and have an integration problem, ex: The OO model could not fit
                > > > with the new database (impedance mismatch)
                > > >
                > > > Thank you
                > > > Amanda
                > > >
                > > >
                > >
                >
                >


                [Non-text portions of this message have been removed]
              Your message has been successfully submitted and would be delivered to recipients shortly.