Configure Microsoft SQL Server Database for Metadata Services Schema

Here you can learn how to configure Microsoft Server Database for Metadata Services Schema (MDS).

Note the following about using SQL Server as the database for MDS, OPSS, and 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.