Friday 1 May 2015

The Real SQL Server Pre-Requisites for EPM

Oracle have published some pre-requisites for SQL Server databases to be used by EPM. You will typically see these mentioned:

ALTER DATABASE DB_XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE DB_XXX SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE DB_XXX SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE DB_XXX SET MULTI_USER ;


However that really doesn't tell the whole story! I had to go through an Oracle Support Request to find this out but there are actually much more than that. 

The script below will run the full pre-requisites on all databases prefixed with "EPM_". I like to use prefixes on the databases as it keeps things neat, and it also means we can perform operations on more than 1 database at a time without worrying about screwing up anyone else's databases! We also assume the EPMA database ends with "EPMA" and the FDMEE database ends with "FDMEE". The SQL user is assumed to be called "hyperion".

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''EPM_%''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT;
ALTER DATABASE ? SET ANSI_NULL_DEFAULT OFF;
ALTER DATABASE ? SET ANSI_NULLS OFF;
ALTER DATABASE ? SET ANSI_PADDING OFF;
ALTER DATABASE ? SET ANSI_WARNINGS OFF;
ALTER DATABASE ? SET ARITHABORT OFF;
ALTER DATABASE ? SET CONCAT_NULL_YIELDS_NULL OFF;
ALTER DATABASE ? SET QUOTED_IDENTIFIER OFF;
ALTER DATABASE ? SET RECURSIVE_TRIGGERS OFF;
ALTER DATABASE ? COLLATE SQL_Latin1_General_CP1_CI_AS;
END'
EXEC sp_MSforeachdb @command

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''%EPMA''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT ;
END'
EXEC sp_MSforeachdb @command

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''%FDMEE''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT ;
END'
EXEC sp_MSforeachdb @command

ALTER LOGIN hyperion WITH DEFAULT_LANGUAGE = us_english

It is also worth mentioning that EPM requires the languages on all users and databases to be "us_english". Again, had to go through an SR to find that one out as well!

Based on feedback in the comments and some tuning guides for FDMEE and HFM the default is now for READ_COMMITTED_SNAPSHOT to be off for all databases except EPMA and FDMEE.

This does contradict the recommendations here, though, which say to turn on READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION for all databases except HFM:
https://docs.oracle.com/cd/E57185_01/EPMIS/ch03s04s02s01.html

Edit: the collation previously mentioned was case sensitive. This is against Oracle recommendations and has been amended.

8 comments:

  1. Is there any Oracle documentation supporting these database settings?

    ReplyDelete
  2. I had to raise an SR in order to find these - that was after the configuration tool failed to configure some databases. The same with using "us_english", I don't think it is explicitly mentioned but after a failure we learned that requirement through an SR.

    ReplyDelete
  3. Thanks for the reply! I'm working on setting up a new 11.1.2.3 environment, this post came at a great time!

    ReplyDelete
  4. READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are only required for the EPMA database. I wouldn't be surprised if most of the others are also product-specific, because to be honest I've never seen them -- can you tell us which products you were setting up?

    You also don't mention the collation, which is critical if you have SOA in the mix.

    Apart from that, very useful post, thanks! :)

    ReplyDelete
    Replies
    1. At the time I wrote this I was setting up HSS, EPMA, Planning, Calc, Essbase Studio, EAS, HPCM, RAF and FDM Classic. We found an issue when the SQL user doesn't have the default language as us_english - fixed that. Then we had another issue where the configtool was failing on our databases. After comparing the databases against a few other environments we applied the settings that were common to the SQL instances where it was working. The configtool ran fine after that.

      I've found no ill effects to having these settings on all the databases and I would prefer to keep all of our database options the same for consistency sake - please correct me if it is actually not a good idea!

      Good shout on the collation, I will amend the blog post with that info at some point. Thanks for the comments.

      Delete
  5. Something you should note when setting up HFM... Oracle has released an article stating that you should NOT enable the snapshot isolation settings to HFM database because they will cause performance issues.

    See:
    Hyperion Financial Management (HFM) Application Performance Degrades Using MS SQL Database (Doc ID 2158174.1)
    https://support.oracle.com/epmos/faces/DocContentDisplay?id=2158174.1

    ReplyDelete
  6. Hi Henri

    According to the article it applies to 11.1.2.4.000

    In your opinion, is this also applicable to 11.1.2.2.500?

    ReplyDelete
  7. I've updated the post to reflect the new guidelines.

    ReplyDelete