How to manage environment-specific values in build process?

I want to automate the build process of server instance that I maintain. In version control I have a script containing every single command and configuration I used to build the instance in production.

Now I want to write a master build script that applies all these these scripts to a target instance.

  • Including a TOTAL row in a SQL query
  • Merging 2 rows and and adding values of matching reference
  • IS NULL in WHERE clause causing query to run slow
  • Change Schema Name Of Table In SQL
  • Not Select Bottom Row
  • Call trigger explicitly
  • While I try to keep my development environment as production-like as possible, there are some values that will always be different. To handle this, the build script should accept environment-specific values and pass the values to the relevant build steps.

    The server instance has one user database. In production, the user database files are created on a drive that does not exist in my development environment, and files are larger than I have free space for in development.

    When I set up the instance in production, I used this script. This is what I currently have in version control:

    USE [master]
    GO
    
    CREATE DATABASE [QuoteProcessor] ON  PRIMARY (
      NAME = N'System_Data',
      FILENAME = N'G:\SQLData\QuoteProcessor\System_Data.mdf',
      SIZE = 500 MB,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10%
    ),
    FILEGROUP [DATA]  DEFAULT (
      NAME = N'QuoteProcessor_Data',
      FILENAME = N'G:\SQLData\QuoteProcessor\QuoteProcessor_Data.ndf',
      SIZE = 600 GB,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10%
    )
    LOG ON (
      NAME = N'QuoteProcessor_Log',
      FILENAME = N'G:\SQLLogs\QuoteProcessor\QuoteProcessor_Log.ldf',
      SIZE = 100 GB,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10%
    );
    
    ALTER DATABASE [QuoteProcessor] SET COMPATIBILITY_LEVEL = 100
    GO
    
    ALTER DATABASE [QuoteProcessor] SET ANSI_NULL_DEFAULT OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET ANSI_NULLS OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET ANSI_PADDING OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET ANSI_WARNINGS OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET ARITHABORT OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET AUTO_CLOSE OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET AUTO_CREATE_STATISTICS ON 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET AUTO_SHRINK OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET AUTO_UPDATE_STATISTICS ON 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET CURSOR_CLOSE_ON_COMMIT OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET CURSOR_DEFAULT  GLOBAL 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET CONCAT_NULL_YIELDS_NULL OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET NUMERIC_ROUNDABORT OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET QUOTED_IDENTIFIER OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET RECURSIVE_TRIGGERS OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET  DISABLE_BROKER 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET DATE_CORRELATION_OPTIMIZATION OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET TRUSTWORTHY OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET PARAMETERIZATION SIMPLE 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET READ_COMMITTED_SNAPSHOT ON 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET HONOR_BROKER_PRIORITY OFF 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET RECOVERY SIMPLE 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET  MULTI_USER 
    GO
    
    ALTER DATABASE [QuoteProcessor] SET PAGE_VERIFY CHECKSUM  
    GO
    
    ALTER DATABASE [QuoteProcessor] SET DB_CHAINING OFF 
    GO
    
    USE [master]
    GO
    
    ALTER DATABASE [QuoteProcessor] SET  READ_WRITE 
    GO
    

    In the development environment, I can use the same filegroups, but I have to use different paths and different sizes for the database files.

    I see several solutions:

    1. Edit the script by hand for every environment. I can’t really automate this, or use it to track changes to environment-specific values.
    2. Make one copy of the script for each environment. I could automate the selection of script depending on environment. This would duplicate the specification of things that should never change independently, like all the ALTER DATABASE statements.
    3. Abstract away environment-specific values using scripting variables and define those values in another place, like an environment configuration file.

    I think option 3 is the cleanest solution. It’s the one I explore here.

    For example, I could use sqlcmd scripting variables to replace the CREATE DATABASE statement with this:

    CREATE DATABASE [QuoteProcessor] ON  PRIMARY (
      NAME = N'System_Data',
      FILENAME = N'$(PrimaryDataFileFullPath)',
      SIZE = $(PrimaryDataFileSize),
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10%
    ),
    FILEGROUP [DATA]  DEFAULT (
      NAME = N'QuoteProcessor_Data',
      FILENAME = N'$(UserDataFileFullPath)',
      SIZE = $(UserDataFileSize),
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10%
    )
    LOG ON (
      NAME = N'QuoteProcessor_Log',
      FILENAME = N'$(LogFileFullPath)',
      SIZE = $(LogFileSize),
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10%
    );
    

    And to create the database in production, I could invoke the script like this:

    sqlcmd -i QuoteProcessor.sql -v PrimaryDataFileFullPath="G:\SQLData\QuoteProcessor\System_Data.mdf" -v PrimaryDataFileSize="500 MB" -v UserDataFileFullPath="G:\SQLData\QuoteProcessor\QuoteProcessor_Data.ndf" -v UserDataFileSize="600 GB" -v LogFileFullPath="G:\SQLLogs\QuoteProcessor\QuoteProcessor_Log.ldf" -v LogFileSize="100 GB"
    

    The master build script would read the values from a configuration file and pass them to sqlcmd.

    There would be one configuration file for production, one for development; one for every distinct environment in my organization.

    I haven’t decided how to stored the environment-specific values yet, but I was thinking that an INI file or an XML file would make it easy.

    Can anyone else offer advice on solving a similar problem? I’m not sure if this is the best way to do what I want. Is there an easier or better-supported way of managing environment-specific values for this problem? Should I be using some tool that manages this kind of thing for me?

  • Compare tools to generate update script for SQL server
  • One Solution collect form web for “How to manage environment-specific values in build process?”

    This is just my take on these

    1. Edit the script by hand for every environment. I can’t really
    automate this, or use it to track changes to environment-specific
    values.

    I would recommend against this. This allows people to accidently make changes to code that you didn’t intend for them to touch. Not that the others prevent it, but this welcomes the most risk.

    2. Make one copy of the script for each environment. I could automate the selection of script depending on environment. This would duplicate the specification of things that should never change independently, like all the ALTER DATABASE statements.

    This works, but you run into the problem when servers change, and based on your criteria on how you are determining what is a dev server or a prod server, the script maybe out dated.

    3. Abstract away environment-specific values using scripting variables and define those values in another place, like an environment configuration file.

    This is how SSDT, microsoft sql server data tools projects do it.

    There’s also a hybrid approach where you can abstract away the environmental specific values but not have an environement configuration file, by using template parameters (again in sql server at least)

    http://msdn.microsoft.com/en-us/library/hh230912.aspx

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.