How to develop a web application compatible with multiple database management systems

Question

How do you design and manage the development of a web application that should be compatible with multiple database management system such as Oracle and MS SQL Server?

If you can't use ORM like NHibernate or EF, how do you maintain database schemas during the development?

My approach now is to have a development database on SQL Server and to port it to Oracle (with a tool) just before releasing a test patch, to test the software on both rdbms. (The tool also generates a file used by the application to upgrade the database)

Is it a good approach? What about a database project of Visual Studio, could it be a better way to keep my db schema?

EDIT: This question is not about designing the architecture of the application (I have already an abstract data access layer), but how to maintain database schemas for different kinds of rdbms during the development.

Answer 1

I think the key to this is to make sure that you stick to standard SQL syntax. MS SQL Server ships with Transact SQL (T-SQL) which is a super-set of ISO standard SQL, meaning that it has extra syntax which is not officially part of standard SQL.

SQLZoo is a good site that allows you to compare the syntax support of different databases. You will find that most of the syntax that you use from day to day will be the same between most DBs, but there are a few quirks out there. The best way to find them is to check each of your queries in each environment and then check them into source control.

Your idea to use a database project is a good one. It will allow you to deploy your changes to multiple databases quickly and test them automatically.