What database to use as temporary database?

[Former Member]

Looking for reason, pro and cons what database to use as the temporary data base.

In examples I see the Identity Store itself is allmost used for this.

My first reflex was to create a dedicated database for all temporary tables to separate it from the Identity Store.

Are there best practices or maybe does it depend on user count (10.000, 100.000, 1.000.000) and target system count?

Other considerations? Maybe staging aspects, etc.

[former_member2987 · Accepted solution]

Thomas,

I don't know how much this will help you, but I always try to use the identity store database.  Usually for the following reasons:

1.It's definition is held in ddm.identitycenter, so it's always easy to define.

2.Fewer rights issues about trying to access another database

3.Can be more efficient, less latency

Both IDM and PI can be used to move data from other systems and enterprise apps and it makes sense to have the data in one safe spot, particularly if you will be doing transformations to the data.  This ensures that you're only touching and changing your copy of the data and not the original.  This helps in building your authoritative store of data.

[Former Member · Accepted solution]

4. If temporary tables are in the mxmc database you can do SQL joins to the identity store (carefully and using with(nolock) on SQL Server 🙂 ) when loading the data into the IdStore later.

Br,

Chris

[former_member2987 · Accepted solution]

Yes, it's important to watch for locking issues.  Only use Enterprise databases.  Do not do something silly like using MS Access

Right, Chris?

[terovirta]

Thomas Liebeck wrote:

My first reflex was to create a dedicated database for all temporary tables to separate it from the Identity Store.

What Matt replied + if you're loading data with the usual "ETL-passes" in IdM/MMC the data is loaded to runtime user's schema, so it is isolated from the Id Store.