UNIFYBroker SQL Server Database Recommendations

Keeping in mind organisational policies surrounding the creation and maintenance of SQL Server databases, it is recommended that the latest released version of SQL Server is used, in addition to the following recommendations relating to the performance and security of the UNIFYBroker database.

Performance

For maximum database performance, it is recommended that the UNIFYBroker database files are located on a high speed disk without contention from other systems. Standard SQL Server best practices should be followed, including Optimizing tempdb Performance.

In addition, there are some settings that can be changed during the creation of the database that have the potential to greatly improve the performance of UNIFYBroker.

File Settings

The Initial Size setting for the Rows Data should be set to the size that the database is expected to grow to. This can be approximated at 20MB hard drive space per 1000 identities.

The Initial Size setting for the Log should be set to roughly 1/3 of the size of the Rows Data setting.

The Autogrowth setting should be changed from the default to avoid database fragmentation and reduce the chance of SQL Server from having to create space during the middle of an operation. A setting of 100MB generally avoids these problems. This setting should be seen as a contingency for unexpected growth, with monitoring and alerts allowing the database administrator to manually set the desired file size (see Considerations for the "autogrow" and "autoshrink" settings in SQL Server for best practices).

The Maximum File Size should be set to a reasonable limit to avoid the database filling the disk, whilst at the same time not restricting legitimate size increases.

Options

• Recovery model - For an Identity Management solution that does not require point-in-time recoverability (connected systems can successfully resolve references to new objects by performing a full import), this setting should be set to Simple for maximum performance.

UNIFYBroker

For systems with high activity, it may be necessary to schedule recurring periods of inactivity, to allow database indexes to return to near 0% fragmentation. Please see Connector Overview for details on configuring Connector Schedules. Additional SQL Server Agent jobs may have to be created to force index rebuild or reorganise, as well as update statistics.

Data Integrity

Through the use of Configuring Microsoft Distributed Transaction Coordinator UNIFYBroker is designed to be resilient to data loss or failed database transactions. In the unlikely situation where data does become inconsistent/incorrect (for example MS DTC being disabled or misconfigured), UNIFYBroker will recover its data through the regular full import cycles - identity management platforms should be configured to allow for this to process to occur preventing import cycles from deleting missing data. Larger deployments may wish to employ other strategies for recoverability, such as backups (regular, incremental, and/or transactional).

Security

There are three options when securing the UNIFYBroker database:

• Create a new windows account to have the UNIFYBroker service run as. Provide this account with permissions to the Unify.IdentityBroker database; or
• Provide permissions to the Unify.IdentityBroker database for the account under which UNIFYBroker is already running; or
• Create a new Microsoft SQL Server account, that has permissions to the Unify.IdentityBroker database. See Data Configuration for details on how to configure the connection.

SQL Server required permissions:

• db_datareader
• db_datawriter

The above settings are generally the minimum required, however, some instances of SQL Server are set up in such a way that further permissions are required. Should UNIFYBroker log an exception pertaining to incorrect privileges whilst creating a temporary table, please grant db_owner to the appropriate account.

Maintenance Tasks

Statistics

The UNIFYBroker database should be maintained by a regular Update Statistics task, using the full scan option to avoid miscalculations. The task should be run as often as necessary to avoid performance degradation, or in cases where certain Connectors or Adapters are performing noticeably worse than others. A good starting point is to schedule the task to run nightly. Do not schedule after an index rebuild as it will result in duplicate work (after a reorganize is fine). An example script that updates statistics for all tables:

EXEC sp_MSforeachtable @command1="print '?'", @command2="UPDATE STATISTICS ? WITH FULLSCAN"

Indexes

As with ensuring that statistics are up to date, the indexes play an important role in the correct functioning of UNIFYBroker. At the very minimum a weekly Rebuild Index Task (Maintenance Plan) should be set up. This can be further optimized by first determining the level of fragmentation and then running the appropriate command, taking into consideration the SQL Server license restrictions (for example online rebuild only being available in the Enterprise edition). Performance should be monitored and the frequency of the task changed as appropriate. An example script that updates indexes on all tables:

EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"

Microsoft Azure SQL Database

As SQL in Azure does not support all features available in the on-premises version, the above queries will not work as-is. A starting point to replace the index rebuild query is as follows:

DECLARE @Table varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT '[' + table_schema + '].[' + table_name + ']' FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC('ALTER INDEX ALL ON ' + @Table + ' REBUILD')
  FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor