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!

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 ON;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ? SET MULTI_USER ;
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;
END'
EXEC sp_MSforeachdb @command


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!

If anyone discovers any further pre-requisites for MS SQL Server please let me know and I will update this post with the information.