Sql-server – Best practices to maximize portability in SQL Server 2016

migrationsql serversql-server-2016sql-standard

When it comes to developing the prototype of a solution, often the technologies has not been decided yet and might not be the same that will be used in the finished product.

In this scenarios I tend to use Microsoft SQL Server writing the queries as standard as possible to simplify the eventual migration to another server.

Is there a way or some known practice to enforce the use of standard SQL over T-SQL dialect directly in SQL Server or via SQL Server Management Studio (SSMS)?

Best Answer

User Aaron Bertrand made some comments that align well with my thoughts on your question. This is more of a frame challenge than an answer to your specific question, but I think it's valuable to consider in this context.

Portability is a nice textbook goal, but it rarely happens in practice.

If you have to change platforms at some point, there will be changes needed to the application, the database, and probably many other things. If you can be somewhat "platform agnostic" without too much effort, that's fine. But it's really a bad business decision to use that as a design goal.

There are many places online where people discuss the downsides or programming this way, here's one of them that I find pretty compelling:

Database Abstraction Layers Must Die!

The Portability Fallacy

The author uses an argument I hear all the time: If you use a good abstraction layer, it'll be easy to move from $this_database to $other_database down the road.

That's bullshit. It's never easy.

In any non-trivial database backed application, nobody thinks of switching databases as an easy matter. Thinking that "the conversion will be painless" is a fantasy.

Good engineers try to select the best tools for the job and then do everything they can to take advantage of their tool's unique and most powerful features. In the database world, that means specific hints, indexing, data types, and even table structure decisions. If you truly limit yourself to the subset of features that is common across all major RDBMSes, you're doing yourself and your clients a huge disservice.

That's no different from saying "I'm doing to limit myself to the subset of PHP that's the same in Perl and C, because I might want to switch languages one day and 'painlessly' port my code."

That just doesn't happen.

The cost of switching databases after an application is developed and deployed is quite high. You have possible schema and index changes, syntax changes, optimization and tuning work to re-do, hints to adjust or remove, and so on. Changing mysql_foo() to oracle_foo() is really the least of your problems. You're gonna touch most, if not all, of your SQL--or you'll at least need to verify it.

That doesn't sound "painless" to me.

Related Question