Admin Scripts

Microsoft's new database development tool, Visual Studio Team Edition for Database Professionals (VSDB Pro), will provide not only database development life cycle tools but also scripting support for DBAs. VSDB Pro's Architect and Development Manager Gert Drapers and Program Manager Architect Richard Waymire told me about its scripting management capabilities.

In addition to development projects, do you support administrators?

Gert: We recently added the idea that you can use a project for script management only, with full folder support, with full version-control support. It's not limited in any way.

Karen: Richard, you were a DBA before you came to Microsoft. Did you have anything to do with adding scripting support?

Richard: The scripts-only project was me being a reformed DBA, saying I'd always want to keep maintenance scripts affiliated with the database. For example, I might have SQL Agent tasks, things that I could script out of SQL Server using [SQL Server Management Studio] SSMS. I'd want to preserve those scripts for the same reason I want to preserve the schema. And I might not necessarily have them associated with any particular schema.

When I was a DBA, I had lots of magic scripts lying around. One thing you get by having source control integration is a great backup and recovery strategy. Now I can safely check in the SQL scripts I use to maintain my production and test systems, and I get the automatic backup and recovery and change tracking that I get for my schema.

As a DBA, I want my specific backup scripts and I want to have my SQL maintenance plans all scripted out—start thinking of the mental picture that you've got of this checked-in repository of the truth: I'm handling databases, but what about my linked servers, my custom error messages, my SQL Agent jobs? I really want to check all those in. Well, using SQL Management Objects (SMO) you could script all that out of SQL Server, turn it into .sql files, and then create a scripts-only project and check it in.

Karen: A scripts-only project is also a way to reuse scripts for different purposes and let other people adapt your scripts without losing the originals.

Richard: Exactly. Anyone on your team can take a copy from source control, modify it, give it a new name, and check it in or add it to a different project. Or now as a DBA, you can write the maintenance plans you want run with the database. At deployment time, you could not only roll out all the schema changes, but you could roll out a new maintenance plan as a post-deployment script at the same time.

Gert: We support parameterized scripts. If we say Create Database, where does that database live physically? Well, we can parameterize that and pass in arguments at deployment time, saying, On this box it lives on these disks, in these directories. Another goal is to enable the decoupling of build and deploy. You can deploy to a target even if you don't know the physical layout per se.