Thursday, November 05, 2009 3:27 PM
The purpose of this article is to provide information to assist in making a strategic plan to maintain the backend MicrosoftIdentityIntegrationServer SQL database.
The articles focuses on providing key factors to review when making a database maintenance plan, as well as providing some good practices to follow in your environment.
As you know, the backend is a Microsoft SQL Server database.
Since each ILM configuration is different within each company, there is no documentation pertaining to
- How often to re-index the tables
- When to re-index the tables
- When to backup the MicrosoftIdentityIntegrationServer database
These business rules need to be set from within the company based on their business needs.
Here are the key factors that you can utilize when attempting to make these decisions:
- Execution of Run Profiles: How often are you doing imports, synchronizations, and exports? This is important, because for each run you are adding a record to the run history.
- Size of Run History: You can determine this by looking in the lower right while viewing the operations tab. A good recommendation is to keep this as small as possible. Something in the hundreds.
- Number of Management Agents: This matters because it can increase the number of records going into the run history for each execution. For example, if you have three management agents, then for each import you will have three records, for each synchronization you will have three records, for each export you will have three records, for each confirming import you will have three records. So for each cycle with three management agents, you could be looking at twelve records in the run history. Then if you run it twice a day, it will become 24 records a day.
- Backing Up: How much data are you willing to lose?
In this section, you find a list of good practices for maintaining a healthy MIIS database.
Affected areas are:
- Clear Run History
- Backing up back-end data
- Re-Building Table Indexes
- Updating Statistics
- Shrink Database
- Database Files
Clear Run History
You will see a performance decrease the larger the run history table is.
Therefore, it is a good practice to implement a process that clears the run history on a nightly basis.
You can automate this task by using the command-line tool MIISClearRunHistory provided in the MIIS 2003 Resource Kit.
In addition to this, you can also find a related script in the ILM ScriptBox.
Backing up back-end data
A good practice for the backe-end data is to do a nightly backup of the MicrosoftIdentityIntegrationServer database.
This will assist in recovering data in case of a data disaster.
You can find a good overview about the different type of SQL Server backups and the process of building a good backup – restore strategy in Backing Up and Restoring Databases.
If the database is in “Full Recovery” model, one backup strategy is to do a Full backup once a week, and do daily transaction log backups.
Depending on the size of the transaction logs and available disk space more than one transaction log should be done each day.
Another option is to do a Full backup, Differential backup and transaction log backups to allow even quicker data recovery.
Here are some good resources for Backing up and Restoring Databases in SQL Server:
- SQL 2000 - SQL Server 2000 Backup and Restore
- SQL 2005 - Backing Up and Restoring Databases in SQL Server
- SQL 2008 - Backing Up and Restoring Databases in SQL Server
Re-Building Table Indexes
A good practice from a SQL Server stand-point, is to do weekly re-build of indexes or if the indexes are more than 30% fragmented.
You can utilize SQL Server commands like DBCC ShowContig or DBCC Show_Statistics to see how the indexes look.
I would recommend reviewing the information in the MSDN articles to understand what you are seeing when you run these commands.
In a new Query window in Query Analyzer, you would execute the DBCC DBREINDEX.
You will need to do that for each table in the MicrosoftIdentityIntegrationServer database.
DBCC SHOWCONTIG and DBCC SHOW_STATISTICS are two different commands and report different information:
- DBCC SHOWCONTIG is to look at fragmentation of indexes and data (also being removed from the product in the future and sys.dm_db_index_physical_stats should be used instead in SQL 2005 and higher).
- DBCC SHOW_STATISTICS is great for showing current statistics for an index.
You can find more details on this in the Microsoft SQL Server 2000 Index Defragmentation Best Practices.
Statistics are the most important component for the Query optimizer to make the best execution plan.
Without good statistics data will likely not be returned in the quickest duration.
Here is a link on statistics and the optimizer:
- SQL 2000 - Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
- SQL 2005 - Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
- SQL 2008 - Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
- Statistical Information
- Index Statistics (2005)
Updating Statistics is a good practice to get into on a nightly basis.
You can find more details about this topic in Update Statistics.
It is a good practice to set the autogrow to MB rather than 10%. An example would be something like 100MB.
You really only need to do this in the case of emergencies.
In a SQL Server database there are three files related database files:
- The MDF file
- The LDF file
- The TempDB file
It is a good practice to place these files on separate drive partitions.
Hypothetical Database Maintenance Plan
Building a database maintenance plan is something that needs to be done within the company’s business rules.
I have outlined below a hypothetical database maintenance plan for the MicrosoftIdentityIntegrationServer database.
The outline provides a snapshot of what a maintenance plan looks like, and gives you a guide to base your database maintenance plan.
- Daily after your runs for the day:
- Clear Run History
- Update Statistics
- Differential Database Backup
- DBCC ShowContig
- Transaction Log backups need to be run multiple times a day
- Rebuild Indexes
- Full Database Backup
Distaster Recovery Scenario
A few months ago I as working with a customer who had not done any type of maintenance on the backend MicrosoftIdentityIntegrationServer SQL database.
In light of this, we found ourselves in a crisis and had to come up with a recovery process.
We were able to put together valuable information that assisted in resolving the customer’s issue.
As a result, I have compiled a list of steps we used to help get through the crisis.
- Delete the connector space
- Backup the Management Agent first
- Click Management Agents
- Select the Management Agent in question
- From the Actions menu select Export Management Agent
- Save it to an easy to remember location and name it accordingly
- From the Actions menu select Delete
- You will receive a dialog with two radio buttons
- Choose the top Radio Button “Delete Connector Space Only”
- Click OK
- Backup the Management Agent first
- Re-Index the MicrosoftIdentityIntegrationServer Tables
- In a new Query window in Query Analyzer, you would execute the DBCC DBREINDEX
- DBCC DBREINDEX
- You will need to do that for each table in the MicrosoftIdentityIntegrationServer database
- Full Import
- Full Synchronization
You can find a collection of tips, tricks, and advices from the SQL Server Query Optimization Team in the related blog.
About the Author
I am a Microsoft Senior Support Engineer and have been at Microsoft for the last 9+ years.
For the last 1.5 years. I have been working with the Developer Support Identity & Access team.
As part of this support team, I have had the privilege of working with several customers in very unique customizations of the ILM product.
Previously I spent time on the Developer Support ADSI team allowing me the privilege of working with the ADSI and WMI technologies.
Timothy P Macaulay, MCSD, MCSD.NET, MCAD, MCP
Wednesday, February 27, 2013 5:08 PM
thank you for your post really useful.
I could adapt and apply your recommendations on my FIM platforms.