mike.renwick-894639 (5/7/2010)

---

Thanks for +ve comments,

Anyone interested in sample code of what we did, take a look here

http://www.mediafire.com/?eim2mydnjti

Disclaimered as you'd expect.

Regarding the ongoing debate about "proper" source control usage, I think a lot of people are indeed missing something. I haven't seen anyone explain adequately how they prevent and detect accidents when rolling out changes to their production servers (e.g. authorized changes by dbo level users), nor how they can prove that what is in their source repository is exactly and only what is in their production environment.

Another example of where this sort of automation is useful is that by scripting to a folder and running a compare against a baseline, you can evaluate the performance and accuracy of a rollout by dba staff, and also have an exact list of changes made in a rollout that you can append to your change control schedule to show that what was meant to be changed is exactly what was changed. This captures those last minute "saves" or "tweaks" that would otherwise be invisible.

Finally, this also allows you to capture changes to more esoteric areas that in some environments are considered beyond the scope of a sql developers source control and falling into the reactive dba world, e.g. tweaked fill factors on indexes, statistics, etc, and can capture objects and settings that might not normally be created via a script (for example, mail profiles).

I wouldn't call the process here particularly onerous- I wrote the attached scripts in a day without extensive powershell experience.