Clarity

How to import the following two OOTB supplied sample files on SQL Server 2014?
dwh_mssql_base.db   and mssql_base.db

Thank you.

Hi Mayank,

There is a feature provided by Microsoft to import the database file and here is the reference documentation from them

Import and Export Data with the SQL Server Import and Export Wizard | Microsoft Docs 

Regards

Suman Pramanik 

If you want to do it the old fashioned hard way launch MS SQL Server  2014 Management Studio and log in as sa

Create the database for CA PPM.

Select the database, right click and select Tasks - Restore - Database

Select Device and click the button with three dots to open the dialog for selecting the device.

Click Add

Locate mssql_base.db and select it, click OK

Click OK

The details of the backup will be displayed eg db name and user name

If you are happy with them click OK to start the restore.

From here on continue as detailed in the CA PPM documentation:

Execute in a Management Studio query window eg. one statement at a time

Login as sa

USE niku
GO

sp_addlogin @loginame = 'niku'
     , @passwd = 'Clarity15'
     , @defdb = 'niku'
-------------
USE niku

ALTER USER niku WITH LOGIN=niku

----------------------------

USE master
GRANT VIEW SERVER STATE to niku

ALTER DATABASE niku
SET ARITHABORT ON
ALTER DATABASE niku
SET ANSI_NULLS ON
ALTER DATABASE niku
SET QUOTED_IDENTIFIER ON

EXEC SP_DBCMPTLEVEL niku, 120

ALTER DATABASE niku
SET READ_COMMITTED_SNAPSHOT ON

sp_configure 'remote query timeout',0

reconfigure with override

----------------------

Restore the dwh backup in the same manner and

USE ppm_dwh
GO

sp_addlogin @loginame = 'dwh'
     , @passwd = 'Clarity15'
     , @defdb = 'ppm_dwh'

--USE ppm_dwh
ALTER USER ppm_dwh WITH LOGIN=dwh

 USE master
 GRANT VIEW SERVER STATE to dwh

ALTER DATABASE ppm_dwh
SET ARITHABORT ON
ALTER DATABASE ppm_dwh
SET ANSI_NULLS ON
ALTER DATABASE ppm_dwh
SET QUOTED_IDENTIFIER ON

EXEC SP_DBCMPTLEVEL ppm_dwh, 120

GRANT ALTER ANY LOGIN TO dwh

GRANT ALTER ANY LINKED SERVER TO dwh

ALTER DATABASE ppm_dwh
SET READ_COMMITTED_SNAPSHOT ON

 USE master
 GRANT VIEW SERVER STATE to dwh

sp_configure 'remote query timeout',0

reconfigure with override

USE  ppm_dwh

ppm_dwh.CMN_DBLINK_SP 'PPMDBLINK','W2012','niku','niku','Clarity15'
-- CMN_DBLINK_SP 'PPMDBLINK','W2012','niku','niku','Clarity15'

--Testing

SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources

gives
(No column name)
7

---------------------------------------

USE clarity_dwh

-- Schema owner is referenced in code when executing functions in MSSQL,

-- so we intentionally hard-code it here to niku. Login name can differ.

exec sp_grantdbaccess 'ppm_dwh','niku'

exec sp_defaultdb 'ppm_dwh', 'clarity_dwh'

exec sp_addrolemember 'db_owner', 'niku'

-- Setup the access to the MSDB for the user

use msdb

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'ppm_dwh')

BEGIN

create user ppm_dwh for login ppm_dwh

exec sp_addrolemember 'SQLAgentUserRole', 'ppm_dwh'

END

-----------------------------------------

Set up the client protocols to use TP/IP and port 1433 in SQL Server configuration manager.

Create one ODBC connection to the CA PPM db and PPM DWH to verify that the connection details will allow access to those databases.

Once the install script has successfully completed log in to CSA and set the db connection details for both database the same which you used in the ODBC connection.