Introduction

ICM 7.10 now supports MS SQL Server as additional database type besides Oracle. 

General Approach

DBMigrate for Microsoft SQL Server is supported starting with 7.10.2 GA only. This implies that customers who want to migrate need to migrate to 7.10 on Oracle first before they can start to move over to Microsoft SQL Server.

Configuration

Microsoft SQL Server Setup

Development

For more information on how to configure MS SQL Edition and Intershop 7 to be used as a development database, see Guide - Setup Microsoft SQL Server as Intershop Development Database.

Test / Production

For more information on how to configure SQL Server and Intershop 7 for a production environment, see Guide - Setup Microsoft SQL Server as Intershop Production Database.

Deployment

For more details see Guide - 7.10 Database Deployment Configuration.

Code Migration

DBInit

For development purposes it is recommended to use data created via DBInit. Maybe DDL Preparers in DBMigrate need to be transformed to DBInit to create the initial table structures and indexes if only DBMigrate has been used.

Starting with 7.10 GA, the DBInit will not clear the database content by default. To do that, an additional parameter needs to be added to the DBInit call:

cd %IS_HOME%\bin
dbinit.bat --clean-db=<FLAG>

Available values for the cleanup are only / yes / no.

Furthermore, it is possible to perform only the DDL database preparation steps in order to prepare table, indexes, stored procedures and functions:

dbinit.bat --ddl=true

More details can be found in Cookbook - DBInit.

Database-Dependent Preparer

The preparer is only executed if the database system fits the sqldialect. Currently, the dialects Oracle and Microsoft are available, e.g., in this example the script will only be executed if the system is running on an Oracle database system.

pre.Class1 = com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer \
         resources/bc_rating_orm/dbmigrate/scripts/RecreateRatingCommentIndexes.sql \
         sqldialect=Oracle

DBMigrate

There is no difference in comparison to run dbmigrate on Oracle.

Adapting Queries for SQL Server

The SQL syntax for Oracle Database and Microsoft SQL Server are not completely identical. They are slightly different. Now there are possibilities to provide both SQL dialects in the same file. For more detailed information on how to achieve this, see Reference - Query File Syntax and Cookbook - Mastering Database Independence.

Migrate Stored Procedures, Functions, PL/SQL

The syntax for Stored Procedures under Microsoft SQL Server also differs from Oracle Database. See SQL Server Create Procedure for further details.

For Microsoft SQL Server the Stored Procedures are stored in: /staticfiles/cartridge/lib/resources/{cartridge}/dbinit/scripts/microsoft. The Stored Procedures need to be converted.

Java Code Changes

Running Database-Specific Code

Sometimes it is necessary to run specific code for different databases. To distinguish which database is currently used, the enum com.intershop.beehive.core.capi.cartridge.SQLDialect can be used:

...
import com.intershop.beehive.core.capi.cartridge.SQLDialect;
...
    if (SQLDialect.ORACLE.isActive())
    {
    	// Oracle specifics
    }
    else if (SQLDialect.MSSQL.isActive())
    {
    	// Microsoft SQL Server specifics
    }
    else
    {
    	throw new IllegalStateException("Unknown SQL dialect " + SQLDialect.getCurrentSQLDialect());
    }
... 

Data Migration via Microsoft SQL Server Migration Assistant for Oracle

To migrate the data from Oracle to Microsoft SQL Server or Azure SQL Managed Instance, the tool Microsoft SQL Server Migration Assistant (SSMA) for Oracle is used.

Preconditions

• Fully prepared Oracle schema (7.10): If the version is lower than 7.10, run a DBMigrate on Oracle to 7.10 first.
• DDL-only-prepared MS-SQL schema (dbinit -classic --ddl=true): This is needed because the Assistant does not create the necessary table structure and indexes.
• The script normalizeTableStructure.sql is executed in the SQL Server database (for normalizing the table structure).

Data Migration Steps

1. Run the Microsoft SQL Server Migration Assistant for Oracle. Perform the following steps (tested with SSMA v8.1.0):
1. Go to File | New Project and create a new project.
   
   1. Specify the project name, the project file location and the target database version.
   2. Finish by clicking OK.
      
      
      
2. Go to Tools | Project Settings.
   1. Go to General | Migration | Misc | Batch Size.
   2. Change the value to 10001.
      
      
      
3. Connect to Oracle (admin rights are required here, use SYSTEM login).
   1. From the list of providers select the "Oracle Data Provider for .NET":
      
      (avoid the usage of the OLEDB provider)
   2. The required connection String has the following format:
      Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;
      Example:
      Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.ad.intershop.net)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = ORCL1)));User Id=system;Password=intershop;
   3. Deselect Load all user objects.
   4. Select Oracle Schema to migrate.
   5. Click OK.
      
      
      
4. Connect to the SQL Server.
   
   
   
5. In the Oracle Metadata Explorer, expand Schemas and select the database to convert.
6. In the Oracle Schema Mapping Window:
   1. Select Source Schema to export.
      
      
      
   2. Click Modify to modify the Target Schema (e.g., intershop.dbo).
      
2. In the Oracle Metadata Explorer:
   1. Expand the Source Schema (e.g., INTERSHOP).
   2. Select Sequences, Synonyms and Tables, deselect others.
   3. Right-click on Tables and and select Advanced Object Selection.
      1. Deselect all tables with the prefix DR$ (Oracle Text index tables).
      2. Deselect all tables with the prefix MY_ (will be created if required).
      3. Deselect the table NOT_USABLE_INDEXES (not needed under MS SQL).
      4. Deselect the table PLSQLLOG (not needed under MS SQL).
      5. Deselect tables with the prefix DBMONITOR_.
      6. Click Close.
         
         
         
   4. Change Type Mapping:
      1. Select the table PRODUCTBINDINGATTRIBUTE.
      2. Switch to Type Mapping View.
         
         
         
      3. Check if "Columns type mapping" is selected.
      4. Change Type mapping number to int.
         
         
         
      5. Click OK.
      6. Apply Type Mapping changes.
   5. Right-click on your Oracle database and select Convert Schema.
      The metadata will be analyzed.
   6. If the message "The target 'synonym: XYZ' already exists. Do you want to overwrite it?" is displayed, click Overwrite All.
3. In the Oracle Metadata Explorer right-click on Source Schema and select Migrate Data.

4. Provide the login parameters for databases again.

   Note

   After analyzing both databases, a summary with failed prerequisites is displayed. Please note that warnings for tables which already contain data can be ignored.

5. Click Continue.
   The Oracle database will then be migrated to MS SQL.
   When the migration is finished, the Data Migration Report is displayed.
6. Click Close.
7. In the SQL Server Metadata Explorer, go to Databases | <Database Name> | Schemas | dbo, right-click on Synonyms and select Synchronize with Database.
8. In the Synchronize with the Database popup, click OK.
9. In the SQL Server Metadata Explorer, go to Databases | <Database Name> | Schemas | dbo, right-click on Sequences and select Synchronize with Database.
10. In the Synchronize with the Database popup, click OK.
11. Execute script on the target database to check if synonyms are set correctly.
    1. Check how synonyms have been migrated. execute the sql
       select name, base_object_name from sys.synonyms;
    2. If the base_object_name values are like [icmdbXYZ].[dbo].[CLASSIFICATIONCOUNT$1] i.e. have the DB and schema qualifiers, then you have to remove them.
       We need a pure name like [CLASSIFICATIONCOUNT$1] :
       DROP SYNONYM CLASSIFICATIONCOUNT; CREATE SYNONYM [CLASSIFICATIONCOUNT] FOR [CLASSIFICATIONCOUNT$1];
       Or execute this script: Synonyms_RenameTargets.sql

Execution Durations

When following the steps described in the previous chapter, it must be taken into account that some of these steps can take a considerable amount of time. 

The actual required execution duration depends on the following parameters:

• Performance of the Oracle database server
• Performance of the MS SQL database server
• Performance of the machine that executes the SSMA tool
• Network (throughput and latency) performance between the above mentioned machines
• Amount of data to be transferred

The following times have been measured as an example to know about the durations to be expected (data used: inSPIRED-b2x demo data):

Additional Hints

Microsoft suggests to adapt one to two connections for each core.

Some customers want to reduce the downtime of their system. A two step migration can reduce the downtime significantly. At the first step all "non-transactional" data will be transferred (like products, content, configurations, ...), in the second step all "transactional" data will be transferred (e.g., users, baskets, orders, ...).

We prepared a list of tables that is suitable for most customers.

In addition, some businesses require a short hand modification of non-transactional data. Therefore it is important to inform the business users about the transfer period. Furthermore it may require additional effort to do the modification a second time on the new platform.

See the attachment for each step (step_1_nontransactional.txt, step_2_transactional.txt).

Disable and Rebuild Indexes

A lot of time is required for updating the XML- and text-indexes. To be more effective, they can be disabled before and rebuilt after the migration.

This is an example script that helps to disable all of the indexes that are shown in a table that has at least one XML-index.

DECLARE @indexes TABLE (rowid INT IDENTITY(1,1), indexname nvarchar(128), tablename nvarchar(128));
DECLARE @i INT = 1,
		@count INT,
		@indexname nvarchar(128),
		@tablename nvarchar(128),
		@sql NVARCHAR(MAX);

INSERT INTO @indexes
SELECT name as indexname, OBJECT_NAME(object_id) as tablename 
FROM sys.indexes
WHERE is_primary_key = 0
  and object_id IN (select object_id from sys.indexes where type_desc = 'XML')
ORDER BY OBJECT_NAME(object_id), name

select @count = COUNT(1) from @indexes

WHILE @i <= @count
BEGIN
	SELECT @indexname = indexname, @tablename = tablename from @indexes where rowid = @i
	SET @sql = CONCAT('ALTER INDEX ', @indexname, ' ON ', @tablename, ' DISABLE')

	PRINT @sql
	EXEC sp_executesql @sql

	SET @i = @i+1
END

The following script shows how to rebuild the indexes.

DECLARE @indexname nvarchar(128),
		@tablename nvarchar(128),
		@sql NVARCHAR(MAX);

SELECT TOP 1 @indexname = name, @tablename = OBJECT_NAME(object_ID) FROM sys.indexes where is_disabled = 1 AND OBJECT_SCHEMA_NAME(object_id) = SCHEMA_NAME()
WHILE @indexname IS NOT NULL
BEGIN
	SET @sql = CONCAT('ALTER INDEX ', @indexname, ' ON ', @tablename, ' REBUILD')

	PRINT @sql

	EXEC sp_executesql @sql

	SET @indexname = NULL
	SET @tablename = NULL
	
	SELECT TOP 1 @indexname = name, @tablename = OBJECT_NAME(object_ID) FROM sys.indexes where is_disabled = 1 AND OBJECT_SCHEMA_NAME(object_id) = SCHEMA_NAME()
END

Replication

Check the following document for replication with SQL Server: Guide - Replication with Microsoft SQL Server/Azure SQL Managed Instance.

Development Tool dev_query

This tool contains some tools for database-related content to help the developer.

For a comprehensive guide, see Cookbook - Developer Tools dev_query.

Testing

JUnit Query Tests

To check the correctness of the queries, a new test has been added.

See Recipe: Create Test Case for a Particular Query for its usage.

Syntax Test (Prototype)

All queries located in the server can be tested if their syntax is correct. To do so, execute the tests.unit.com.intershop.dev.query.tests.QuerySyntaxCheckTest. This test can be found in the cartridge dev_query.

It also scans for suspicious elements in queries which will not run in SQL Server.

There is a prepared launch configuration to start the test. The following attributes are required:

// Microsoft SQL Server configuration
-DCONNECTION_URL=jdbc:sqlserver://<host>:<port>;databaseName=<database name>
// Oracle configuration
#   using sid           : jdbc:oracle:thin:@<HOST>:<PORT>:<SID>
#   using service Name  : jdbc:oracle:thin:@//<HOST>:<PORT>/<SERVICE_NAME>
-DDB_USER_NAME=<db user name>
-DDB_USER_PASSWORD=<db password>

SQL Logging

Several tools have been added to help the developer to analyze the sent queries to the database system.

These tools are located in the cartridge dev_query.

Record Queries

It is possible to record all queries sent to a database system. See Recipe: Record Query Call Log Files.

Replay Queries

The recorded queries can be replayed against a different database system to check if the syntax and the semantic remain the same or to measure the database system performance.

The tool makes it possible to check differences between databases.

See Recipe: Replay Query Log Files.