Migrating from one DBMS to another

Does anyone have any experience migrating from one DBMS to another? If you have done this, why did you do it? Features? Cost? Corporate Directive?

At times, I've worked with DBAs who insisted that we not use features specific to a DBMS (for example, CLR Stored Procedures in SQL Server.) The DBAs point is, if we use these features, it will make it more difficult to switch to another DBMS if we have to. But so far, I've never been asked to switch.

From stackoverflow

• In my opinion its silly not to take advantage of all the features of the db your using. Changing DBMS regardless of how many features you use is going to be difficult. There are minute differences between the systems (like some record Date and some record date and time) that will cause a huge headache to change. There is no such thing of just switching to a new dbms.

  From a business perspective there is a lot of work to be done. Analysis on the new dbs to change to. Figuring out the impact of changing dbs on the new system. Having development change the existing systems, testing the changes etc. The list goes on and on. Making a switch like that on an enterprise system takes months if not years. The last place I worked had to change dbs, and it took us a solid 11 months to do it and about 2 million dollars for consultants, hardware, software, and employee salaries. It's a big deal. If someone is saying not to use features, because that "may" happen someday and it'll be easier to do, Most likely, that person is off their rocker. The extra amount of time and money it will take to convert those features is minuscule compared to everything else (most likely). IMO if it will save time and money now buy using those features, then that is the best course of action.

  We did it because the systems we had running on the old dbms were too big. There was too much data, and we needed something a lot more powerful. Plus, it wasn't supported anymore.

• Switched many times. Mostly because the "Involuntary Conversion" -- an old product is no longer supported or is no longer suitable.

  • DB2 to Oracle. Pre-UDB data was preserved and moved into Oracle.
  • MS-Access to Oracle. Continued using the Access front-end over Oracle tables.
  • Oracle to Oracle. 6 to 8, I think...

  "why did you do it?" Not features. Not cost. In all cases, something is broken.

  • Old product no longer works. Either an OS upgrade or something else has made the legacy product break.
  • Old product did not scale.

  Switching is rarely something you choose to do. It's forced on you when vendors go out of business (Ingres did this once) or stop supporting your version (Microsoft does this frequently).

  Then, of course, it's a crisis. Compounded by the technical complexity of trigger and stored procedure changes. If it was only the data, it wouldn't be much of a crisis. Dump to some standard form (CSV, for instance), reload, and you're up and running.

  More importantly, the more "stuff" (stored procedures, triggers, etc.) in the database, the more your application software becomes a confusing pile of hard-to-follow (and hard-to-maintain) kludges. There's nothing so frustrating as waiting weeks for someone to track down a stored procedure name. If it was VB code, everyone would have had access to it. But since it was in the database, it became -- paradoxically -- less visible. Code is code and should be kept away from data.

  See Where to put your code - Database vs. Application? for more on this topic.

• I've been involved in several projects to migrate data from one database to another. In every case, it was the data that was being migrated--not the RDMBS. If an application is working then there's not going to be any pressure to switch databases for the sake of switching. The impetus for the migration is usually because the old system's data is either out-of-date, incompatible, or both, and that affords an opportunity to switch the RDBMS as well.

  The most likely change is to consolidate the reference data (employees, customers, etc) into the existing master database (for consistency and ease-of-use) and then modify all the other tables so that the keys are pointing to the new reference data. This requires schema and corresponding code changes up and down the stack. It's a data migration--not a database migration. And more than likely you'll want to take advantage of the opportunity to add data, or standardize names, or (de)-normalize the tables, etc.

  The upshot is that these projects nearly always have an enormous impact on the data, the schema, and the code, and any work required to--say--translate T-SQL into PL-SQL will be a trivially small part of the project. So if you're paying for a nice RDBMS, use all of it. To do otherwise would be like not using the trunk or the glove compartment of your new car so that it'll be easier to switch cars when you buy a new one.

• I worked at a company for a number of years, whose product supported either Oracle or SQL Server. We maintained the model in Erwin, and generated schema scripts, triggers and Oracle Packages from it. The packages were used to make the Oracle triggers work similearly to SQL Server ones (with a logical 'inserted' and 'deleted' tables) We kept two sets of Stored Procedure Scripts.

  With that mess under my belt, I would suggest that you can migrate big projects, so long as you can succesfully make your data tier completely seperate from any logic code. If you can do that, then you can implement whatever database features will speed up the application in the data tier, without affecting your core app.

• One other point (in support of S.Lott). Depending on your development environment your developers may not have an easy time developing or even viewing stored procedures. Splitting your application code between two different sets of development tools and execution environments can get complicated, and it can make it harder to find skilled employees.

  I don't think that's an argument against stored procedures, but it's certainly something to consider when deciding where the code should reside for a given component of your applications.