History DB Installation and Rotation

History DB Installation

This article describes the installation and rotation of the History DB

A new HDB should be created yearly
Naming: IAMHDB_XXXX



Create an entry for the source database.

Create an entry for the new HDB in the source database

Disable the old entry

Which Owner should be used?

Jobservice Installation
Change the Name of the Service to OneIMHDBService, One Identity Manager HDB Service

Enable Schedule “Import process information directly”

ToDo in PROD:
Assigne the correct Owner to the SQL Agent and HistoryDB
Create Jobqueue
DB is cluster? If Yes SQL Agent must be installed on both
Encryption?

Issues and Solutions of Prod HDB

When I checked the HDB of prod, I found that there are 2 HDB connecting to Prod, one is v7 and another v8. The service of v7 is disabled and it did not work. The service of v8 is running, but there is no jobqueue running.

Issue 1

I checked the scheduler configuration, everything is normal. I triggered the jobchain VI_SourceDatabase_Import in ObjectBrowser and triggered the scheduler in Designer, they showed success, but there is no jobchain running in JobQueue.

Solution 1

I checked the 1IM community for this phenomenon and luckily I got the answer
I tried to execute below SQL. After the execution, the jobchain started to run and the dbqueue is not in wait status any more.

--Stop all DBQueue Processor components
exec QBM_PWatchDogPrepare 1
exec QBM_PDBQueuePrepare 1

--Check whether other database sessions are active.
--If there are still sessions active, they must be closed first.
select * from sys.sysprocesses p where dbid = DB_ID() and spid <> @@SPID

--Create a new ServiceBroker ID and enable message delivery.
alter database IAMHistoryDB_812 set NEW_BROKER
alter database IAMHistoryDB_812 set enable_broker

--Initializing the DBQueue Processor.
exec QBM_PDBQueuePrepare 0,1
exec QBM_PWatchDogPrepare

Issue 2

When execute the command exec QBM_PDBQueuePrepare 1, it gave an error, which means that the dbo user does not exist. I can not remember the exact expression.

Solution 2

After searching on google, I realized the database did not have an owner. In version 7, there is no this request and this is newly added in version 8. After the operation, the command could be executed without errors.

Issue 3

On the second day, I find the jobchain VI_SourceDatabase_Import is in frozen status. The error message was as below. After re-enable, it still shows below error.

[810023] Error during execution of statement: exec HDB_PGetRawFromSource N'SQLIAM.cloud.munichre.com', N'iamdb', null, N'', 1  
[810143] Database error 266: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.  
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.  
OLE DB provider "SQLNCLI11" for linked server "QD1C7D64C49467DB6102936FDDF13C5F96216E57A" returned message "Query timeout expired". 

Solution 3

I found the SQLServer database parameter remote query timeout (s) is 600, which means if query time is longer than 10 minutes, the query will fail. The HDB stopped for months, so the amount of the rows must be a large number, 10 minutes was definitely not enough. Then I use below SQL to extend the limitation. After the operation, the jobchain finished without error.

--find the configuration value
select * from sys.configurations order by 2 ;
--change the value
sp_configure 'remote query timeout', 1800 
reconfigure with override 

Issue 4

Every time the jobchain VI_SourceDatabase_Import executed successfully, but I noticed that there is no records transferred to the backup table. For example, the number of rows of HistoryJob is not increasing. And the system journal shows No import of processes possible because task in the DBQueue are not yet processed.
Then I checked the table QBMDBQueueCurrent, and I found the task was generated more than 100 times.
I checked the table DialogJournal I found one message: #LDS#DBQueueProcess stopped because runtime limit {0} minutes was exceeded.|20| I realized that the HDB is a “Development” environment, not “Production”

Solution 4

Because the dbqueue task execution time is different according to the staging level of the environment. Development is 20 minutes while production is 120 minutes. I used the Designer to change the staging level to Production. After monitoring for some time, the task finally finished and the row amount of backup tables increased as expected.

As of now, the production history database seems working normally.