Configure Microsoft SQL Server as Repository Database for Essbase Schemas

To use Microsoft SQL Server as the database for Essbase repository (RCU) schemas, you need to use case sensitive collation, and set READ_COMMITTED_SNAPSHOT to ON.

The following database configuration notes are applicable when you use Microsoft SQL Server as the database Essbase schemas, which include Metadata Services Schema (MDS), OPSS, and WebLogic (WLS) schemas.

• To create a metadata repository in SQL Server, set READ_COMMITTED_SNAPSHOT to ON for the hosting database. This enables the needed row versioning support. Use the following SQL command ALTER DATABASE, as in the following example:

  ALTER DATABASE <DB NAME> SET READ_COMMITTED_SNAPSHOT ON 

• Use case-sensitive collation to support the case-sensitive semantics in the metadata repository. For example, if Latin1_General is used, select the SQL_Latin1_General_CP1_CS_AS collation using the following SQL command:

  DECLARE @collate sysname
  SELECT @collate = convert(sysname, 
  serverproperty('COLLATION'))
  IF ( charindex(N'_CI', @collate) > 0 ) 
  BEGIN
  select @collate = replace(@collate, N'_CI', N'_CS') 
  exec ('ALTER database <DB NAME> COLLATE ' + @collate) 
  END
  GO

  Note:

  For both code sets above, you need to replace <DB NAME> with the actual name of your Essbase database.

  In many cases, this command will run successfully. However, the command might fail and generate error messages concerning functions, primary keys, constraints, or indexes. This can be caused if the database already has collation aware objects. In this case, SQL Server does not allow you to change the collation at the database level. In this case, the alternative is to create a new database with the expected collation for MDS to use.

• There are some minor differences between an Oracle schema and a SQL Server schema. The length of the certain text fields are shorter for a SQL Server schema. For example, the full path name of the metadata in SQL Server is limited to 400 characters.

• Some WebCenter domain configurations do not require MDS schema, but all WebCenter domains require OPSS and WLS schemas.

See Configuring a Microsoft SQL Server Database for the Metadata Services (MDS) Schema.