Database creation script - external parameters/settings

Question

Question Summary:

Is there a better method than the one posted below to implement the database-creation stage of an automated deployment?

Background/Requirements:

• I am in the process of attempting to develop a coherent database version control process using an approach similar to this one. This question is specifically about the baseline, which includes physically creating the database.

• The plan - for now - is to implement a simple command-line tool (in .NET) to apply both the baseline and change scripts. I want the tool to be generic (we have several independent transactional databases), and therefore would like all scripts to be external, including database creation.

• The test environment(s) may not be a perfect replica of production; physical file locations and sizes may need to be quite different.

Current Idea: (What I'm looking for feedback on)

Basically a template system. Use a simple string.Replace on a script like the following:

CREATE DATABASE [{DBNAME}] ON PRIMARY
(
    NAME = N'MyDB_Data',
    FILENAME = N'{PRIMARYFILENAME}',
    SIZE = {PRIMARYSIZE}KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5%
),
FILEGROUP [FG_MyDB_Index]
(
    NAME = N'MyDB_Index',
    FILENAME = N'{INDEXFILENAME}',
    SIZE = {INDEXSIZE}KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5%
),
-- more filegroups ...
LOG ON 
(
    NAME = N'MyDB_Log',
    FILENAME = N'{LOGFILENAME}',
    SIZE = {LOGSIZE}KB,
    -- etc.
)
GO

ALTER DATABASE [{DBNAME}] SET COMPATIBILITY_LEVEL = 100
GO

-- more ALTER DATABASE stuff ...

Obviously, the tokens to be replaced are the ones like {DBNAME} and {LOGFILESIZE}. The values for these would be taken from command-line parameters or perhaps from an external file.

Problems I see with this approach:

• The "script" bills itself as a T-SQL script but is in fact invalid T-SQL. It forces you to either use the deployment tool or perform a tedious manual search-and-replace.

• There is no way to run it with "default" values. Worse, it provides no indication of what a good default value would be.

• It feels flimsy. It seems like it would be possible to alter the script in such a way that it is still valid SQL (or at least, as "valid" as it presently is) but still break the deployment tool. The lack of any escaping, for one, is concerning. And template parameters within literal strings make my skin crawl.

Other possibilities that I've considered:

• Construct a dynamic SQL script to be executed with sp_executesql. This would provide the benefit of true parameters as opposed to a naive template, but would make the script incredibly cumbersome to write, test, and maintain, especially if we ever decide to take a new baseline (which we might - it's a big database).

• Use SSMS template parameters. This decouples it nicely from the deployment tool but also makes it difficult to integrate with the deployment tool - kind of ties it to SSMS.

• Embed the script in the deployment tool. That way nobody can mess around with it, and it's clear that the script is merely a template that must be processed. Default values can be embedded somewhere "close" to the script. The main, huge disadvantage is that the deployment tool becomes application-specific and might require code changes for something that really shouldn't require a code change.

• Generate the entire script dynamically off an external, human-readable file, like YAML. At first this idea seemed very elegant, but eventually it started sinking in that I might be re-inventing the wheel. Everything about the database would have to be included in the file, so does this really offer any advantage over manually modifying a plain-vanilla SQL script or even creating the database manually through SSMS?

None of these options seem quite right. What I want is something that's independent (just as code is theoretically independent of the environment you use to build it), but also readable (no "SQL within SQL"), and self-explanatory (clearly identifies what settings are needed and what good defaults might be).

Are there any methods that will achieve everything I'm trying to achieve? Or am I just being too picky or looking at the problem the wrong way?

(P.S. I am not presently looking for commercial, standalone, 3rd-party tools that will perform this task for me. "Now I have two problems.")

Answer 1

I would probably either use this script as an embedded template in a command-line tool; get the minimal information you need (to replace the placeholders - DBName and LogFileSize; the other things like filegroup names could be deduced from the DBName, e.g. FG_{DBName}_PRIMARY for the primary filegroup) from the command line parameters. Use text search/replace to fill in the blanks, and then execute this against the SQL Server you want to deploy it to.

E.g. something like:

CreateDB -server:(local) -dbname:MyTestDB -logsize:50 (as in 50 MB)

Or: use SMO (SQL Server Management Objects) to create the database. You basically need the same information (DB Name, Log File Size) and you can programmatically create a database and its layout - no T-SQL script required.

As a side note: if you're going into creating separate filegroups, I'd suggest this layout at a minimum:

• primary filegroup: left alone for just the system catalog views - nothing else
• DATA filegroup: mark as DEFAULT, use for tables
• INDEX filegroup: for indices

That way, you minimize the size of the primary filegroup, and thus you also minimize the risk of it running into a disk error and getting corrupted. The primary filegroup contains the system catalogs, so if that FG is ruined, your entire DB is toast.

Answer 2

Have you considered using SQLCMD scripts and the SQLCMD utility rather than writing your own command line interface, or perhaps writing your own and simply wrapping the SQLCMD functionality? Naturally this would assume you only want to support SQL 2005 and up. I've done this in the past a few different ways (i.e. using a simply batch file with params/options that wrap the SQLCMD, using a more extensive approach wrapped with code that happened to unfortunately be VB 6.0 at the time, etc.).

If you take the route of wrapping with your own code, may want to consider leveraging powershell as well (I'm currently thinking of this as well). Sql Server now includes custom Powershell providers and extensions for things like SMO functionality, code execution, management, etc., which can naturally and easily be extended to wrap SQLCMD and SQL functionality.

Given what you're trying to do, the benefits with SQLCMD (and SQLCMD scripts) seem to fit nicely into things:

• The scripts are still valid for use/interpretation/integration with SSMS and other IDE's
• There are a few options for default values, though nothing really integrated into the scripts themselves (environment variables, command line options, etc.). Additionally, you may want to consider pulling your default values for file/group locations from a pre-defined location on the server/instance you are running on (such as the model or master or tempdb).
• Less flimsy in the sense that current IDE's that support SQLCMD would throw exceptions in SQLCMD mode for undefined variable/value combinations

If you'd like to exchange notes/thoughts on your project, I'd definitely be up for helping out with coding and/or just talking through some challenges I ran into (different editions of the engine for example and feature usage were a few things I didn't originally think of).

Answer 3

We solved a similar problem by making a script that could use sqlcmd parameters. Something like:

:setvar filename somefile.ext
:setvar version 1
:on error exit

print 'This is version $(version) on $(filename)'
-- the rest of the actual script

This could be run via command line or in SSMS. Eventually we made a tool that could read the file and replace variables with the same syntax.

I don't think it takes care of every concern you listed, but it should help with some.