Database considerations for Microsoft SQL

Database recovery mode

For optimal performance during data migration, set the Microsoft SQL database recovery mode to SIMPLE in the source and target systems. Other values for this setting cause significant performance overheads and use large volumes of REDO logs.

A full data export in an environment upgrade scenario without SIMPLE recovery mode in place will have a significant impact on REDO logs and might fail if insufficient space is available.

Throttling

Throttling limits the number of requests being processed in the database within a specified period. Throttling is used to prevent the backend from being flooded (for example, in a denial-of-service attack). Because BMC Helix Data Manager is request intensive, you should disable throttling, which reduces the throughput of queries to the database. 

Tablespace requirements—export

When exporting dependent data, BMC Helix Data Manager creates tables that hold key values that identify child form records to include in the data export. During the export process, the tables are dropped as soon as possible (that is, as soon as all child forms are processed). BMC Helix Data Manager stores only key information in these temporary tables. For example, for a 20-million-row table that has child records with a qualification on the Incident Number field, the storage requirement can be calculated as follows:

The temporary table creation will have some impact on REDO logs if database recovery mode is not set to SIMPLE.

Tablespace requirements—import

During an import operation, BMC Helix Data Manager creates temporary tables and indexes. These temporary tables are created for each table being imported and are removed as soon as the import of that table is complete.
Incremental imports also create temporary tables that hold request IDs from the source system. You should allocate approximately 20% of the total database size for these temporary tables.

Tablespace requirements—threading

Multithreading can improve the overall performance of migration, but multiple tables are exported in parallel. This increases the impact on the database tablespace because more than one set of temporary tables is created when performing multithreaded export and import. If you use multithreading, make sure that you have sufficient database resources to handle multithreaded migration.