1 / 41

David Lundell, ILM MVP

Ride the Chaos through proper Care and Feeding of your Identity Metasystem Databases (ILM, CLM and RMS). David Lundell, ILM MVP. Who am I? What is Ensynch?. IDA Practice Director at Ensynch ILM MVP Ensynch Award Winning Identity Management Practice SharePoint Biztalk

shen
Download Presentation

David Lundell, ILM MVP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Ride the Chaos through proper Care and Feeding of your Identity Metasystem Databases (ILM, CLM and RMS) David Lundell, ILM MVP

  2. Who am I? What is Ensynch? • IDA Practice Director at Ensynch • ILM MVP • Ensynch • Award Winning Identity Management Practice • SharePoint • Biztalk • Unified Communications

  3. Agenda • Why do you care? Why should you care? • Problems with ILM, CLM, and RMS databases • DBA 101 • Solutions to Problems • Recommendations • QA

  4. Why do you care? • Faster Performance • Reliability • Job Security • May not have a DBA assigned • DBA may not understand specialized needs

  5. Generalized Problems

  6. Problems w/ ILM Sync Engine DB

  7. Problems with the MSILM DB

  8. Problems with CLM DB

  9. Problems with RMS Config DB

  10. Problems with RMS Logging DB

  11. Problems with RMS Directory DB

  12. SQL Background (Problems and Solutions) • Tempdb • Table Growth Consequences • Transaction Log • Recovery Models • Backup Types • High Availability

  13. Tempdb • Ideally separate spindle for TempDB data and TempDB log • But at least isolate from all else • Used by triggers, sorting, index rebuilds, big queries, temp tables, row versioning • # of tempdb data files = 1/4 to 1/2 # of cpu's for tempdb (upto max of 8) • 1 tempdb log file • KB 307487 -- don't shrink tempdb • Kb 224071 -- moving tempdb

  14. Consequences of Table/Index Growth • Page Splits • Fragmentation • More Scans than Seeks • Data File Growth • Log File Growth

  15. Page Splits • Inserts cause page splits during your run • Both tables and indexes stored in 8K pages • Use SQL Server Access Methods:Page Splits/Sec to track how often this happens Which causes …

  16. Fragmentation • Searching and inserting take longer • Because Index becomes deeper • Index can become fragmented • Pages are out of order (because of page splits) • Pages are partly empty (because of updates, deletes and pages splits) • DBCC SHOWCONTIG(‘mms_step_object_details’)

  17. Scan vs. Seek • Query Optimizer may decide to avoid beaten up indexes • So it may choose to do a Scan • Scan = Read every page in table • Access Methods: Full Scans/Sec • Ensure that Auto Create/Update Stats is enabled

  18. So rebuild indexes Tables and indexes (more than 1000 pages) with greater than 30% fragmentation should Rebuild and those with less than that but more than 5% should Defrag(Reorg)

  19. IndexOptimize from Ola Hallengren • http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html • IndexOptimize

  20. Don’t overdo it • If you rebuild indexes – don’t usually have to update statistics • Consider Bulk-Logged Recovery Model for index rebuilds • MSILM DB turn off auto update stats • May not need it for others if doing it frequently in a batch fashion

  21. File Growth • Only if Auto Grow is enabled and there is not enough file space – make it a large enough chunk • Expensive operation to take place during peak • Preferable grow rather than halt operations due to out of space • Best is to trim (clear run history) database and then grow in advance of need • DBCC SHRINKDB(‘MicrosoftIdentityIntegrationServer’,NO_Truncate) • Alter Database Modify File (NAME=MIISData1, SIZE = 1000MB)

  22. Transaction Log • Give SQL Server Reliability, recoverability and speed • Like court stenographer, serially noting down everything that took place without sorting/cataloging • Data changes happen in RAM and T-Log • Upon checkpoint (approx 1/min) changed pages are written to data files • Only need 1 Log file • Should have own spindle(s) – RAID 1

  23. Log File Growth • Log file growth depends on Recovery Model, size and number of Transactions • SQL Server: Databases: Log Growths • Start off at a decent size (1/4 estimated size for your total data files) • Virtual Log files

  24. Virtual Log files • Dbccloginfo • Too many and too few hurts performance • Too many and log is highly fragmented • Too few and log can’t clear itself often enough • Want VLF’s about 500 MB • Suggest start at 8 GB and 8 GB of growth • Log Growths • chunks less than 64MB = 4 VLFs • chunks of 64MB and less than 1GB = 8 VLFs • chunks of 1GB and larger = 16 VLFs • http://www.sqlskills.com/blogs/Kimberly/category/SQL-Server-2008.aspx

  25. http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspxhttp://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

  26. Recovery Models • Full (Best recovery) • Log everything and keep it until we Backup or Truncate the Log • Simple (Only as good as last full or diff) • Don’t log bulk logged operations and get rid of inactive transactions at every checkpoint • Bulk-Logged • Don’t log bulk logged operations, keep everything else until we Backup or Truncate the Log • Only use during a bulk logged operation

  27. Backup Types • Full Backup • Copies all data pages and only grabs T-Log items that happened during backup • Differential • Copies all data pages that have changed since last full and only grabs T-Log items that happened during backup • Log • Copies the entire log and truncates all inactive transactions (only one that does this)

  28. Full, Log or Diff? • Recommend Nightly Full Backups of • Master, MSDB, Model and User databases • If in full or Bulk Logged Recovery Models you will need to backup your log • How often? • What kind of data loss can you live with? • What about Diffs? • Speed up recovery process • How quickly must we be able to recover

  29. Which Recovery Model fits?

  30. Speedy Backups • Stripe your backupset across multiple disks … or use Compression • Red Gate SQL Backup and Quest LiteSpeed (2 examples) • Compress to 1/10 – 1/5 size of native backups • Can encrypt using AES upto 256 bit • Backups take ¼ - ½ the time • SQL 2008 has backup compression

  31. High Availability • Clustering • Protects against server hardware and server software failure • Failover 30-60 seconds • Mirroring • Protects against • Failover can be under 5 seconds • Requires Smart client • Log Shipping

  32. Which Database HA fits? MIIS service can be clustered but is not supported by ILM product group ILM 2 Services – Multi instance and NLB or other Load balancer CLM Service -- Multi instance and NLB or other Load balancer RMS – Is designed with its own application cluster

  33. ILM Sync Engine Clustering on Windows Server 2008 (not sup) • Use the Generic Script Resource per Alex T. http://blogs.msdn.com/alextch/archive/2005/12/17/clusteredmiis.aspx • But make some tweaks for windows 2008 clustering model if Node = activeNode Then Set objWMIService = objSWbemLocator.ConnectServer(Node, _     "root\CIMV2") Else Set objWMIService = objSWbemLocator.ConnectServer(Node, _     "root\CIMV2", _ strUser, _ strPassword, _     "MS_409", _     "ntlmdomain:" + strDomain) End If • http://www.ilmbestpractices.com/blog/2009/03/ilmmiis-sync-engine-clustering-windows.html

  34. Still have Database performance issues • After following the preceding advice • Defrag the disk • Try more spindles • Try solid state drives – fusionio.com

  35. References • http://blogs.technet.com/rmssupp/archive/2008/11/19/de-humidifying-the-rms-logging-database-a-k-a-de-mystifying.aspx • http://technet.microsoft.com/en-us/library/cc747680.aspx • http://technet.microsoft.com/en-us/library/cc747691.aspx • http://blogs.technet.com/rmssupp/

  36. References cont. • http://technet.microsoft.com/en-us/library/cc747731.aspx • http://www.sqlskills.com/blogs/Kimberly/category/SQL-Server-2008.aspx • http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx • http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html#1227266

  37. Thanks • Thanks to Andy Schan from Titus Labs for double checking some of my RMS info • Thanks to Jason Tyler for his RMS blogs

  38. Other Ensynch Sessions • Taming the Chaos – Building a Practical Lifecycle Mgt. Application in the ILM “2” PortalDavid Lundell and Brad Turner • Sunday 1PM - 5 PM Pre conference Workshop 2 • Designing an Object Expiration & Reconciliation process in ILM 2 Brad Turner • Monday 1 PM - 2:15 PM • Proper Care & Feeding of ILM, CLM and RMS Databases David Lundell • Monday 1 PM - 2:15 PM • Rescue Your Identity Metasystem from Chaos Through Reporting against ILM 2 with SSRS David LundellBrad Turner • Mon 4 PM - 5:15 PM • ADFS ExtensibilityChris Calderon • Tue 2:45 PM - 4 PM

  39. Visit our blogs and Identity Page • David Lundell www.ilmbestpractices.com • Brad Turner www.identitychaos.com • Chris Calderon Blog.identityjunkie.com • Joe Zamora http://c--shark.blogspot.com/ • Rebecca Croft http://www.apollojack.com/ • www.ensynch.com/ida/ • www.ensynch.com/events/

More Related