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.

Situation

Now

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

Then

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?

PostgreSQL

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:

We are Microsoft partner and SQL Server is our commitment to Microsoft.
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 …

… in Numbers

Data Definition (DDL)
- >750 tables
- 16 views
- >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