Hi at all,
I write this post to talk about DB, it maintenance and some usage tips for Sailpoint.
(In this post I saw SQL Server but it is valid for oracle, MySQL…)
Generally, SailPoint doesn’t work on DB directly, it uses hibernate engine that “convert” the data from DB in object (I’m simplifying). *
But this doesn’t mean the DB is not important, all the data are stored into DB and every operation (more or less) in Sailpoint reading/writing from/into DB.
So, it is especially important that the db works well.
There are 3 concept that you follow:
- Maintenance, maintenance, maintenance
- If don’t use, delete it (or not create it)
- Use only the necessary
Maintenance
The most crucial point. Sailpoint installation doesn’t create a maintenance plan, you MUST create it.
A full maintenance plan includes a lot of steps like backups or query cost analysis, but I want to focus on index, statistics, and check integrity because those are the most impact on Sailpoint.
Shortly, indexing and statistics could improving the search and the check integrity maintain the integrity of DB.
It must be specified, the index and statistics could a negative impact on performance, if managed badly.
First of all, I want to explain how to make those control.
Index
It is important to rebuild or reorganize indexes.
Usually, if fragmentation is less of 30%, you can reorganize, if it is more, rebuild it
With this query(for MSSQL) we can see the index fragmentation:
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
The commands are those:
ALTER INDEX Index_Name ON Table_Name REBUILD
ALTER INDEX Index_Name ON Table_Name REORGANIZE
Otherwise, creating a lot of index it’s deleterious. Create an index if it is necessary, if you know that column will be search in many cases and if it is fundamental for your environment.
Statistics
Some DB already have the statistics active, otherwise it is better to activate them.
Statistics are used (with index) for improve the search. With this option active, the db creates an history and with it creates plans for the future queries.
You can check if it active with the carefully select of DB(each DB have the own).
For example for MSSQL:
SELECT name, is_auto_create_stats_on, is_auto_update_stats_on
FROM sys.databases
WHERE name = 'dbname';
or for Oracle:
SELECT table_name, num_rows, last_analyzed
FROM dba_tables
WHERE owner = 'schemaname';
For create a statistics this is the command:
CREATE STATISTICS statname
ON table_name (column_name);
It’s better to create statistics on index column.
Check Integrity
To verify and repair corruption blocks, is necessary make a check integrity.
The command like DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS parameter for MSSQL (DBVERIFY and DBMS_REPAIR for oracle )verifies/repairs the DB(if it is possible).
This control is particularly important, because in most case we don’t realize there is a problem until we go to update on the corrupted block.
If don’t use, delete it(or not create it) & Use only the necessary
Those concept are united.
In most cases who makes changes on Sailpoint don’t know how work a DB or the impacts of own action. This also applies to code.
Today, our machine have a lot of memory space and ram, so we don’t care to optimize the use of them.
I found a lot of environment without a maintenance plan, full of index or with unnecessary column or table.
So, if you don’t use or it is not necessary delete it or use only that you need.
A practical example on Extended Attribute.
Most persons, when needs to create a new Extended Attribute, copies an older one and change the number, like this:
and use a varchar column of 450 characters for a boolean or for a date. Change it.
For a boolean you need 1 character(or 5 if you want use false/true), for a name you can set 50-60(the longest name in the world is long 41) ecc…
Also, from first installation Sailpoint create some extended attribute, on identity for example:
In some environments those are not used or uses the namedColumn. Delete it(maybe you can keep the first). Also, if you don’t need search an identity attribute with filter/queryoption or for some specific use-cases, create it no searchable.
If it are not used are setting null, but a column with all null values consume space and decrease the performance of queries and indexes.
Last one: shrink dB, this operation permit to save space and it “get closer” physically the data of DB, deleting the blank spaces derived from delete operations.
A medium environment of Sailpoint has 50k-60k of identities, 100k-200k account, 250k-500k of entitlements and thousands of operations every day.
Over time, performance can decrease significantly.
All these things improve the performance of the entire environment(from 5% to 50% in some cases) and prevent future problems.
I hope I have been useful to you
*See those pages for more info
https://community.sailpoint.com/t5/IdentityIQ-Articles/Database-connections/ta-p/78543