Using SQL Server RDS as a SQL Server PI repository (4229175)

Using SQL Server RDS as a SQL Server PI repository

Can SQL Server RDS be used as a SQL Server PI repository?

RDS does not allow for any user (other than the master) to have dbcreator rights. Even attempts to use the “master” user to configure the PI repository fail, because it appears that the process checks specifically for dbcreator rights (which does not exist in RDS), so the user cannot proceed through the wizard.  With these limitations it appears that the only option is to manually create the database. 

WORKAROUND

** Using SQL Server RDS as a PI Repository has not been certified as by Quest R&D as a supported environment ***

Steps to follow:

1). As RDS does not give you direct access to the file system. For the “Raw Data Path” check the configuration of an existing database in SSMS. In our example it showed the data path as D:\rdsdbdata\DATA.

2). Run this script attached to initialize the database schema for SQL Server agents, or run this script to initialize the database schema for Oracle agents.

3). Create a file group for 1m data

USE [master]
GO
ALTER DATABASE [Database Name] ADD FILEGROUP [REPO_1M_FG]
GO
ALTER DATABASE [Database Name] ADD FILE ( NAME = N'1mData', FILENAME = N'[Driver 1]:\[1 minute Filegroup directory]\1mData.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [REPO_1M_FG]
GO

4). If monitoring SQL Server agents with the database, initialize the wait event categories by running this script for SQL Server.

After these steps the user should be able to select the existing database to use as a PI repository.

STATUS

An Enhancement Request PIFOG-696 has been logged to certify and support DBaaS as the SQL Server PI repository (SQL Server on RDS and Azure SQL). This will be considered by Product Management for a future release of the SQL Server and Oracle cartridges. 

PIFOG-696

pass_wait_event_dim
build_oracle_schema_mssql
install_schema_mssql

The steps provided in the workaround detailed above are based on an installation done by Jason Hall from Quest's Pre-Sales team.