I have been three times in position of database architect responsible for design, implementation and support of multiple databases simultaneously.

The database independent approach never worked, because:

·         Some functionality should be implemented inside database using stored procedures unless you agree to suffer from really bad performance

·         If you want to right an efficient code almost immediately you need to use vendor’s proprietary SQL extensions

Unfortunately, writing vendor specific database backend has also some cons. If you use two different database vendors your development efforts even not doubled, but tripled.  The pain of supporting synchronization of application development, database development itself and testing are equally unbearable.

Let me introduce an approach that could help:

·         As early as possible identify vendors that you going to support

·         Try to find at least one DBA that is familiar with all of them

·         Research the main differences between vendors functionality

·         Identify the subset that you will never use because there is no adequate support from other vendors

·         Select your primary database development environment. This choice depends on availability of automatic database migration tools from primary vendor to secondary ones.

Try to avoid:

·         Hardcoding SQL statements inside application

·         Using string builders and/or concatenations

Create database access layer and concentrate all vendor specific database access classes over there.

Develop or use database unit testing system that should allow you to test vendor specific code simultaneously.

Suggested workflow:

·         Main database development is performed for primary database vendor

·         Automatic database migration tools are used for schema and business logic migration Note: Support of incremental migration by tool is a big plus.

·         Required manual tuning is performed after automatic migration

·         Parallel database unit testing is a must

Igor Bekelman

Principal Architect

SQL Server Migration Assistant

Disclaimer: The post reflects my personal opinion