There is a new sheriff in town with a split personality – Microsoft SQL Server/Azure SQL Database

Cloud first but not cloud only – with the new upcoming 7.10 release of ICM we will introduce Microsoft SQL Server and therefore Azure SQL Database as an alternative to Oracle Database. Using Azure SQL Database gives every project the possibility to benefit from all administrative advantages of the Microsoft Azure infrastructure. This includes, among others, Azure Security, growing support for non-Microsoft technologies and scalable service plans for multiple needs and budgets.



  • Monolithic architecture with support for:
    • Oracle Database
      • ICM, ICI – based Intershop ORM Engine tightly coupled with Oracle
    • PostgreSQL
      • IOM – based on Wildfly in theory will run on “any” DB
      • ICI – used for aggregated reporting data, JDBC
  • Microservices supporting:
    • Oracle Database, PostgreSQL, Derby via JPA implementation EclipseLink


  • Short term: ICM to support Microsoft SQL Server and therefore Azure SQL Database
  • Long term: Consolidation to at least support one DB for all Intershop applications where it makes sense of course

Why Microsoft SQL Server?


  • Supported by IOM
  • Open Source, therefore no license fee, but monthly support costs
  • Streamlined portfolio

Microsoft SQL Server

  • Commercial DB
  • License costs, monthly support costs
  • Cost neutral to Oracle Database at least if we consider just production environments

So why again we decided for Microsoft SQL Server?!

Well, mainly two reasons:

  1. We are Microsoft partner and SQL Server is our commitment to Microsoft.
  2. Azure SQL Database is the only DBaaS available under Azure.

Meanwhile there is a preview available of Azure Database for PostgreSQL and Intershop got an invite for a closed beta test program. C’est la vie!

Work Almost Behind us …

From Oracle to Microsoft SQL Server

… in Numbers

  • Data Definition (DDL)
    • >750 tables
    • >2100 indexes
  • Data Manipulation (DML)
    • >150 stored procedures
    • ~100 functions
    • <10 PL/SQL packages (transformed into MS SQL functions)
  • Data Query (DQL)
    • 750+ queries (65 of them are Oracle/Microsoft specific)

… in Epics

  • Remove Oracle Client from ICM
  • Create Test Framework for SQL Statements
    • Extend SQL logs to dump input/output
    • Create test replaying queries
    • Compare results from Oracle/MS SQL runs
    • Test coverage tool in Intershop Studio
  • Adapt DBInit
    • Create dump import/export tool
    • Divide into structure and data creation
    • Transform PL/SQL packages to MS SQL functions
  • Allow ORM to work with MS SQL
    • Locking
    • Commit
  • Adapt Platform to cater for MS SQL
  • Fix Storefront Functionality
    • Separate queries from managers into query files
    • Develop Query JUnit test skeleton
    • Transform >750 query files + test cases
    • Use ANSI SQL wherever possible
  • Fix Backoffice Functionality
    • Use Microsoft’s full-text index for search
  • Enable Staging for MS SQL
    • Build solution for our staging shadow tables in Microsoft
  • Migration Support from Oracle DB to MS SQL
    • Migration Guides
    • Recipes for JUnit tests
  • Performance Tuning
    • Microsoft SQL Server
    • Azure SQL Database
  • Operations Manual

Key Takeaways

Here and there we would be better off with PostgreSQL as it is closer to Oracle Database than Microsoft SQL Server. The support we got from our partner Microsoft is excellent.  I doubt that we would have gotten better support from any other cloud provider. So thanks Microsoft!

We would have had it easier if we would have had JUnit tests for every query. The majority of time was spent for creating tests for DB artifacts and creating test data for them.

Was it the right choice not to remove our home brew ORM engine? For sure! To replace it with an JPA implementation like EclipseLink, which we use for our Microservices, would have been far more costly in money and time.

There is a new sheriff in town with a split personality – Microsoft SQL Server/Azure SQL Database
Tagged on: