Planning for Database Migrations

Many project managers, database administrators, system architects or software developers experience at least once in their lifetime the perspective of a database migration. Your company acquired Oracle and you want to see if your SQL Server based application cannot be easily switched to Oracle. Or to an open-source client-server database such as MySQL or PostgreSQL. Or, for a small application with no justification for such large resources, you want to check the cost of migrating all your queries to SQLite or Firebird.

Traditional Approach

Let’s consider here just one simple perspective and assume many of your SQL Server queries have datetime calculations, such as adding a number of years, months and weeks to existing dates. You do this using the DATEADD Transact-SQL function in SQL Server, but have no idea what other similar datetime functions other platforms have.

The typical approach to discover this is to look at the documentation of each platform, and eventual install one similar database of that type as a sample, and write queries for it. Most cases involve a considerable learning curve here, because few people are familiar with so many databases. There is also additional time to invest in the setup and installation of other platforms, especially considering that just one of them will be eventually chosen.

The Power of Simulated Queries

Consider now the simple wonderful approach we offer with our productivity tool, and how much time and money you will save. Start by connecting to ANY database (yes, any, we don’t care!) and build a simple query with your datetime calculations, on a constant value:

…and this is it, done with your full setup and installation! “Wait”, you will ask, “where is this query in Oracle, MySQL, PostgreSQL, SQLite or Firebird? Are you kidding? We don’t even know what’s the generated SQL for SQL Server! Because we installed none of these databases yet!”

Well, the magic button is in the left corner of your bottom toolbar, and it’s called “Simulate for a different platform“. Select SQL Server for a start and check your generated SQL: the query uses indeed DATEADD for all three expressions: SELECT DATEADD(WEEK, 3, DATEADD(MONTH, 3, DATEADD(YEAR, 3, ‘2016-02-15 11:22:33’))) AS [value].

Switch now to …Oracle. You instantly get something like SELECT (((TIMESTAMP ‘2016-02-15 11:22:33’ + INTERVAL ‘3’ YEAR) + INTERVAL ‘3’ MONTH) + INTERVAL ’21’ DAY) AS “value” FROM dual. Our SQL generator and simulator automatically took care of the empty but mandatory FROM clause. And adapted DATEADD to Oracle-specific datetime functions, with additional cast to TIMESTAMP and proper syntax with INTERVAL. Quite a difference, you may say.

Simulate it for MySQL now, by another extremely easy single-click: SELECT TIMESTAMPADD(WEEK, 3, TIMESTAMPADD(MONTH, 3, TIMESTAMPADD(YEAR, 3, ‘2016-02-15 11:22:33’))) AS `value` FROM dual. Remark the backticks and the different similar function, which is now TIMESTAMPADD.

And in PostgreSQL: SELECT (((‘2016-02-15 11:22:33’::TIMESTAMP + (3 * INTERVAL ‘1 YEAR’)) + (3 * INTERVAL ‘1 MONTH’)) + (3 * INTERVAL ‘1 WEEK’)) AS “value”. Another different way to cast datetime values, with the ::TIMESTAMP suffix. And another very different way to calculate our expressions.

Check also SQLite: SELECT DATETIME(DATETIME(DATETIME(‘2016-02-15 11:22:33’, ‘+3 YEAR’), ‘+3 MONTH’), ‘+21 DAY’) AS “value”. A different DATETIME function this time!

And finally, in Firebird: SELECT DATEADD(3 WEEK TO DATEADD(3 MONTH TO DATEADD(3 YEAR TO TIMESTAMP ‘2016-02-15 11:22:33’))) AS “value” FROM rdb$database. Yes, another empty but mandatory FROM clause we took care of. And a DATEADD pretty different from SQL Server as well.

No, your translation to any of these platform would not be easy, if you have such datetime calculations. By curiosity, check the simulated query for all other supported platforms, and you may find Sybase/SQP ASE the closest to SQLL Server: SELECT DATEADD(WEEK, 3, DATEADD(MONTH, 3, DATEADD(YEAR, 3, ‘2016-02-15 11:22:33’))) AS [value]. This is of course because both databases have Sybase as common ancestor.

Conclusion

We could imagine plenty of similar scenarios and query types, fact is our productivity tool can save you a lot of time and money, when you plan or study database migrations. With Simulated Queries you do not even need to install a database platform of a specific type! It is simply enough to design a query for one platform, and have it instantly translated, with one single mouse click, for other 15 supported databases.

Your options don’t stop here. You can see queries using nested embedded subqueries or a CTE syntax. You can also hide subquery definitions and focus just on your outer query.

Whatever you try, remember chances are your designed query will look the same way, in our builder, in any of the supported platforms. Even when the generated SQL could be very different from one platform to another. You have generic vendor-independent queries in our visual query builder, automatically translated into vendor-specific SQL queries.