ISV targeting ORACLE and thinking of moving to SQL Server 2005? Read on...

  • Article

If you are an ISV which today runs against Oracle and so far has resisted producing a version for Microsoft SQL Server then I have some good news. One of the challenges you would face to make your application run well on SQL Server 2000 would be the different approach to concurrency of database transactions between the two RDBMs. Oracle uses data versioning to solve this where as SQL Server 2000 (and many other RDBMs for that matter) use locking.

There are pros and cons to each approach - but the significant difference in behavior is that on SQL Server 2000 a reader will block a writer where as on Oracle a reader does not block a writer. I have seen many Oracle applications which have been quickly ported to SQL Server 7 or 2000 behave very poorly as a direct result of this. Application code for the first time ends up waiting on read locks to be released, with read locks being held sometimes for very long times as under Oracle there was never an issue. To solve under SQL Server 2000 would typically require many changes across the application and its interaction with the database.

The good new is SQL Server 2005 solves this. We add an additional Isolation Level which uses data versioning in a similar form to Oracle. The new Isolation level is called Snapshot and has two flavours:

  • Statement-Level Snapshot which maps to Repeatable Read for Oracle. This is the most common Isolation level for Oracle applications. (ALTER DATABASE <db> SET READ_COMMITTED_SNAPSHOT ON)
  • Transaction-Level Snapshot which maps to Serializeable for Oracle. (ALTER DATABASE <db> SET ALLOW_SNAPSHOT_ISOLATION ON)

You can find a detailed discussion of this at https://www.microsoft.com/technet/prodtechnol/sql/2005/SQL05B.mspx 

The Snapshot Isolation Level may also be of interest to you even if you not coming from Oracle. It may be the best choice if you have an application:

  • that is predominantly reading data.
  • that has a high number of deadlocks using locking-based isolation levels.
  • where read-only reports and ad hoc queries run in parallel with an application that is updating the data.