Friday, November 13, 2009

SQL Server 2008 R2 MDS - Microsoft MDM Solution

Played two hours with this on the just released SQL 2008 R2 CTP nov.

Two hours effective work was enough to
- install the product (including reading how to do it),
- installing IIS on Windows 7 (you need a modern advanced OS)
- implementing security
- adding a model (PoC) with a hierarchy
- reading most of the documentation

Books on-line is sparse, so I haven't really done a real walk-through yet. I hope a tutorial is coming soon, or I might post one here (after getting to know the thing better).

Wonder if I can run a hybrid model where I can have some entities work as in the "registry"-model, and some as in the "Central Master Data Repository"-model.

I would like to put all my meta data into this thing, hope it is mature enough.

I went to a couple of sessions on SQL PASS, but they were introductory, can't be many that have experience with this thing.

Anyhow, have a nice day.

Tuesday, November 3, 2009

Performance hit for SELECT because of fragmentation?

SQL PASS - I got help from an expert on SQL PASS, Seattle

Q: Is there a performance hit on the "select"-side because of some fragmentation of the data base files caused by auto growth?

A: No. But you might want to enable "Perform volume maintenance tasks"

Question details
We have a generic script-based deployment of many data bases, and therefore have auto-growth on them, so they are created in exactly the same way. There are a total of ten data bases with twelve files each, so 120 files. The files all start at 100MB and grow by 25%. To give each data base special treatment makes changes more expensive (We charge by the hour).

Solution details
1) Keep scripts as is.
2) Enable "Instant Initialization"
a) open a command window and run "sc qc ", for me it is "sc qc sqlserver$datawarehouse"
b) note the user name from "SERVICE_START_NAME" attribute.
c) Fire up ocal policy editor, start -> run -> gpedit.msc
d) navigate to "Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignments", open "Perform volume maintenance tasks"
e) add the user from step b)
f) restart your sql server instance (not sure if this is needed, but seems a good idea)

and thats all,have a nice day

btw:I will not mention the name of the expert that helped me, because the person can not make public advice because of legal reasons. And things might have been distorted by me translating the advice into this post.