SQL

SQL Always On Availability Groups for MIM

Image from: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview

Edited July 2 2022 after reviewing my Facebook discussion with Eugene Sergeev on Microsoft’s product team.

MIM 2016 SP2 (and 4.4.1459.0 or later supports SQL Server Always On Availability Groups (AG))! Yeah!

Ok let’s implement it!

But wait! It won’t give us all we hope for!

• Up to the moment distributed backup of the data – yes!
• Automatic instant failover – not without a huge caveat!

What do you mean it won’t give us Automatic Instant Failover?

Continue reading

How many attributes can you have in the Metaverse?

Back in 2013 I published 5 posts about the Secrets of the Metaverse:

Parts 1-5:

1. What is the Metaverse?
2. How is the Metaverse data stored?
3. Is there a limit to how many Metaverse attributes I can have?
4. Has access to the metaverse gotten faster with recent releases?
5. How do I safely query the metaverse?
6. Added (Aug 5 2015): How Many Metaverse Attributes can I have?

The third post was about how many attributes you can have in the Metaverse in which I said that the mms_metaverse_lineageguid table limits us to 502 single valued non-reference attributes in the Metaverse. This is still correct but a client told me of a scenario they encountered where the lineageguid table prevented them from getting to over 450 attributes and they encouraged me to blog about how they solved it.

Continue reading

SQL Maintenance for FIM and anything other databases

An easy way to take care for your FIM databases is to “use Ola Hallengren’s script (http://ola.hallengren.com/scripts/MaintenanceSolution.sql). Download the script, adjust the backup paths and run the script on each instance of SQL Server. It will automatically create several jobs some for maintaining the system databases and some for maintain the user databases. You will need to create schedules for each of the jobs.” – FIM Best Practices Volume 1

I love using Ola script for index maintenance because it is so much smart than the Database Maintenance wizard which wants to spend lots of time rebuilding indexes that only needed to be reorganized and messing with indexes that were just fine or too small to matter. A table with less than 1000 pages is usually too small to matter. Less than 5% fragmentation and why bother. Less than 20% and a reorg will usually solve it. Over 20% and you should usually rebuild.

Continue reading

Mailbag: Learning FIM, SQL and IIS

Recently, a reader reached out to me for advice on learning FIM, SQL and IIS. As well as guidance on setting up a lab (more advice on that part in a later post).

First think for a moment about your best learning styles for technology. Do you need to read the concepts and architecture first and then do it? Do you need to watch a video and then read, and then do it? Do you need to try it and then go back and read? Do you need an instructor? Sometimes you have to learn through experimentation. In the early days of ILM 2 Beta there wasn’t much info so we had to experiment. Brad Turner and I spent many days in a lab configuring and trying things out to see what was the best practice.

Continue reading

FIM Reporting Craig Martin style

Thanks for attending the session, glad you liked i…

Craig Martin - May 2, 2012

Thanks for attending the session, glad you liked it! I had fun talking and running with Scissors.

Continue reading

FIM Reporting Craig Martin style

Craig’s session is on how to get data out from the FIM Service and FIM Sync with PowerShell and displaying it with SSRS, which he has dubbed Scissors!

Ok Craig we get it! You have even persuaded me that PowerShell is important! I have started writing scripts. SQL Server of course is still important.

Key is to hook up a pipeline from PowerShell to pass into his custom SSRS PowerShell Data Processing Extension (DPE). Craig uses export-clixml and import-clixml to serialize data before it is expired from the FIM system.

Continue reading

FIM DB Sizing Calculator

Pretty slick, man!

Craig Martin - May 3, 2012

Pretty slick, man!

Thanks Craig. Glad you like it!

WAY too timely! Thanks Dave!

Thanks, very helpful.

Hi very helpul, do you have something like that for the Reporting data base of FIM (SCSM DW)?

Continue reading

FIM DB Sizing Calculator

FIM has two databases (well three if we count the FIM Certificate Management service):

• FIMService
• FIMSynchronizationService

Here is a calculator in excel that you can download and use to calculate how big to make your databases.

In my experience the FIMService database size depends mostly on how many request objects are in the database.

The FIM Sync Database depends mostly on how much run history details (step object details) you generate and keep.

Continue reading

Calling a stored procedure in an ADFS claims rule

After you have setup your SQL Attribute Claims Store in ADFS. If you want to use it and in fact test it you must set up a claims rule that makes use of it. To do this you must create a claim using a custom rule, which allows you to employ the claims rule language.

The following technet entry is a good start as it illustrates how to enter a SQL Query and even a stored procedure.

Continue reading

Troubleshooting SQL Attribute Stores with ADFS

Several others have showed how to define SQL attribute stores with ADFS.

Note that when entering the connection string there is no validation or feedback to the administrator. If there is a problem you usually won’t see it until you setup a claims rule that uses it and you get an error. So make certain to carefully build and test your connection string. Remember that if you use integrated authentication to connect to the SQL Server that it will run under the context of your ADFS Service account so you will need to grant your ADFS service account permissions to the SQL Server and Database.

Continue reading