timabell/README.md

## README.md

      
    Raw
  

              README.md
            
          
    We've created a set of good old fashioned windows batch scripts which tie together the process of completely rebuilding test systems and their databases. This gives us confidence that when we eventually upgrade a live web system that all the pieces of the upgrade have been well tested.  The scripts will take an ASP.NET website offline, upgrade it (code and database) and put it back online all in one script. They can be run with or without a continuous integration server.
You can find the files here: https://gist.github.com/3164291
To rebuild a local development system run rebuild.bat, to rebuild a QA system run rebuild-QA.bat which overrides some of the values in the main rebuild file.
Some more details:

The scripts must be run in a "Visual Studio Command Prompt" for the code build & deployment to work.
The program relies on SqlHawk to do the database upgrade which must be available on the path.
You can set environment variables at the command prompt to alter settings without altering the script files.
Build and deploy of the code depends on visual studio due to the publish & web config transform parts.

The scripts will reset to a snapshot or backup of live before applying changes, meaning you can alter any part of your scripted update and retest all the changes.

  
## app_offline.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
    <title>Application Offline</title>
    <style  type="text/css">

    div {
        background-color:#ffffcc;
        padding-top:10px;
        padding-bottom:10px;
        padding-left:10px;
        padding-right:10px;
        border-style:solid;
        border-color:Black;
        border-width:1px;
    }

    </style>
</head>
<body>
    <div>
        This application is currently offline.  Site update in progress.
    </div>
</body>
</html>

## config-transform.xml
<!-- http://stackoverflow.com/questions/2905151/msbuild-script-and-vs2010-publish-apply-web-config-transform -->
<!-- this is an msbuild configuration for applying web config transforms -->
<Project ToolsVersion="4.0"
         DefaultTargets="Transform"
         xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

    <UsingTask TaskName="TransformXml"
               AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\Web\Microsoft.Web.Publishing.Tasks.dll"/>

    <PropertyGroup>
        <ProjectPath>..\Shell.DocumentGenerator.Mvc</ProjectPath>
        <TransformInputFile>$(ProjectPath)\Web.config</TransformInputFile>
        <TransformFile>$(ProjectPath)\Web.$(Configuration).config</TransformFile>
        <TransformOutputFile>$(DeployPath)\Web.config</TransformOutputFile>
        <StackTraceEnabled>False</StackTraceEnabled>
    </PropertyGroup>

    <Target Name="Transform">
        <TransformXml Source="$(TransformInputFile)"
                      Transform="$(TransformFile)"
                      Destination="$(TransformOutputFile)"
                      StackTrace="$(StackTraceEnabled)" />
    </Target>
</Project>

## create-snapshot.sql
/* script for taking a snapshot of a test db for testing scripts */
-- This is manually run for setting a point for the reset scripts to go back to before re-applying all outstanding changes. Replace ##database## with the name of the database you are creating

-- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

-- create snapshot of CI db for testing scripts
create database ##database##_snapshot
	on (name = 'DevDb', -- name of source file
		filename = 'C:\sqlserver_databases\snapshots\##database##_snapshot.ss1')
	as snapshot of ##database##

-- Tim Abell
-- 3 May 2012

## global-config.sql
-- Set up config stored in database for local testing.

/*
 Note that $(mailbox) is a variable set for sqlcmd.
 ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
 As the exchange server is part of our domain the logged in user will
 have permissions to send as the matching email address.
*/

update globalConfig
	set value = '$(mailbox)'
	where name like '%EMAIL_ADDRESS';

update globalConfig
	set value = REPLACE(value, 'http://www.example.com', '$(siteUrl)')
	where value like 'http://www.example.com%';

## rebuild-QA.bat
@echo off
Setlocal EnableDelayedExpansion

REM set variables for building QA
set server=staging
set database=QA
if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run. Changeset Cxxxx
set publishPath=\\staging\web\QA
set mailbox=shared@mycompany.co.uk
set siteUrl=http://staging/qa
set resetType=snapshot
set config=Debug (QA)

call rebuild.bat

## rebuild.bat
@echo off
REM =============================================================================================
REM                           !! DESTRUCTIVE !!
REM  Resets a test database, runs upgrades, adds test data, adds test files.

REM Must be run in visual studio command prompt to allow build.
REM Requires SqlHawk, msbuild, and msdeploy (part of webdeploy) to be on the path.
REM Environment variables can be overridden by caller / calling scripts.

REM It is recommended that you set a buffer of 3000 lines on your command window to avoid losing output.
REM =============================================================================================

REM allow use of !var! everywhere, the normal !var! version doesn't work with if / for etc.
REM and avoid messing with variables in the command window scope:
Setlocal EnableDelayedExpansion

set localPublishPath=..\WebProject

REM set variable defaults if not already set by caller
if "!server!"=="" set server=localhost
if "!database!"=="" set database=DevDb
if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run
if "!publishPath!"=="" set publishPath=!localPublishPath!
if "!mailbox!"=="" set mailbox=!USERNAME!@mycompany.co.uk
if "!siteUrl!"=="" set siteUrl=http://localhost:7031
if "!resetType!"=="" set resetType=backup
if "!config!"=="" set config=Debug

echo   Server      : !server!
echo   Database    : !database!
echo   Batch       : !batch!
echo   Publish path: !publishPath!
echo   Mailbox     : !mailbox!
echo   Site url    : !siteUrl!
echo   Config      : !config!

echo.
echo !date! !time! Starting rebuild...

if not "!publishPath!"=="!localPublishPath!" (
	echo.
	@echo Taking app offline...
	copy app_offline.htm "!publishPath!"
	if !errorlevel! neq 0 exit /b !errorlevel!
)

if not "!skipDb!"=="" (
	echo.
	@echo Skipping db rebuild.
) else (
	echo.
	@echo Reverting to clean backup of live db...
	@echo Do not interrupt this step as a broken connection can wedge the database. Ref http://stackoverflow.com/questions/520967
	sqlcmd -b -S !server! -E -d master -i reset-to-!resetType!.sql -v database=!database!
	if !errorlevel! neq 0 exit /b !errorlevel!
)

REM this is the bit that will be done on the final deployment to live:
echo.
@echo Running upgrade...
call upgrade.bat
if !errorlevel! neq 0 exit /b !errorlevel!


if "!skipDb!"=="" (
	echo.
	@echo Applying test data...
	pushd .
	cd test-data\sql\
	call test-data.bat
	if !errorlevel! neq 0 exit /b !errorlevel!
	popd

	echo.
	@echo Configuring global config...
	sqlcmd -b -S !server! -E -d !database! -i global-config.sql -v mailbox="!mailbox!" siteUrl="!siteUrl!"
)

echo.
@echo Copying test files into publish directory...
xcopy /S /Y test-data\upload !publishPath!\data\upload\
if !errorlevel! neq 0 exit /b !errorlevel!

if not "!publishPath!"=="!localPublishPath!" (
	echo.
	@echo Putting app back online...
	del !publishPath!\app_offline.htm
)

echo.
@echo !date! !time! Done.

## reset-to-backup.sql
--       !! DESTRUCTIVE !!
-- Restore a local database from backup.
-- (sqlexpress doesn't support snapshots)
-- To be used in preparation for re-testing scripted changes with sqlhawk
-- This script is not intended to be run manually. See ../README.txt for the (re)build process.

-- kill active connections
-- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
ALTER DATABASE [DevDb]
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DevDb]
SET ONLINE

RESTORE DATABASE [DevDb]
FROM  DISK =
N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\DevDb.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
use [DevDb]
GO
-- fix login mapping after databse restore
alter user WebsiteUser with login = WebsiteUser, default_schema = dbo

-- Tim Abell
-- 23 May 2012

## reset-to-snapshot.sql
--       !! DESTRUCTIVE !!
-- Restore a local database back to the last snapshot.
-- To be used in preparation for re-testing scripted changes with sqlhawk
-- This script is not intended to be run manually. See ../README.txt for the (re)build process.

-- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

/*
 Note that $(database) is a variable set for sqlcmd.
 ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
*/

-- kill active connections
-- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
ALTER DATABASE $(database)
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE $(database)
SET ONLINE

-- reset the snapshot
restore database $(database)
	from database_snapshot = '$(database)_snapshot';


-- fix login mapping after databse restore
use $(database)
alter user WebsiteUser with login = WebsiteUser, default_schema = dbo

## test-data.bat
@echo off
REM this is a generic script for running all the files matching pattern test-data_*.sql in a folder
rem set DelayedExpansion to be able to catch errorlevel inside a loop
setlocal EnableDelayedExpansion
for /R %%I IN (test-data_*.sql) DO (
	@echo %%I
	sqlcmd -b -S !server! -E -d !database! -i "%%I"
	if !errorlevel! neq 0 exit /b !errorlevel!
)

## upgrade-live.bat
@echo off
Setlocal EnableDelayedExpansion

REM TODO: Fill this in with the correct values for live

set server=liveSql
set database=liveDb
if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!;
set publishPath=\\web01\web\live
set config=Release (Production)

echo   Server      : !server!
echo   Database    : !database!
echo   Batch       : !batch!
echo   Publish path: !publishPath!
echo   Config      : !config!
echo.

REM saftey buffer, remove this if we move to a proper build server based deployment
@echo Are you sure you want to upgrade the live db?
@echo * Have you made a backup first?
@echo * Have you filled in the parameters of this script?
@echo.
@echo Press Ctrl+C to cancel, any other key to continue.
pause >nul

@echo Taking app offline
copy app_offline.htm !publishPath!

echo.
@echo Running upgrade...
call upgrade.bat
if !errorlevel! neq 0 exit /b !errorlevel!

@echo Putting app back online...
del !publishPath!\app_offline.htm

@echo Done.

## upgrade.bat
@echo off
Setlocal EnableDelayedExpansion
REM This script takes the live database or a clone of it, and upgrades it to the latest version based on the stored procedures and scripts in source control.

REM The current live database (and therefore the backups and snapshots) have never had SqlHawk run, thus the tracking table needs to be created. Once live has the tracking table the initialize-tracking step must be discarded.

echo.
echo !date! !time! Starting upgrade...

if "!skipDb!"=="" (
	echo.
	@echo Initializing upgrade log table...
	java -jar "C:\Program Files\SqlHawk\sqlhawk.jar"                                 ^
		--initialize-tracking -t mssql05-jtds -h !server! --sso -d !database! -s dbo ^
		--driver-path "C:\Program Files\jtds-1.2.5\jtds-1.2.5.jar"
	if !errorlevel! neq 0 exit /b !errorlevel!


	echo.
	@echo Upgrading database...
	java -jar "C:\Program Files\SqlHawk\sqlhawk.jar"                             ^
		--scm-input --target-path ..\database\                                   ^
		--database-output -t mssql05-jtds -h !server! --sso -d !database! -s dbo ^
		--driver-path "C:\Program Files\jtds-1.2.5\jtds-1.2.5.jar"               ^
		--upgrade-batch "!batch!"
	if !errorlevel! neq 0 exit /b !errorlevel!

	REM "sleep" for windows. hack. http://stackoverflow.com/questions/7958265/sleep-command-in-batch-file
	REM this is because on a local SQLExpress the next sql command fails with spurious login failure if it is run too soon.
	@echo Waiting for database to settle...
	ping -n 10 127.0.0.1 > NUL
	if !errorlevel! neq 0 exit /b !errorlevel!
)

REM Only publish if not working locally.
if not "!publishPath!"=="!localPublishPath!" (
	REM move to sln directory ready for build and publish
	pushd .
	cd ..\

	set packageTemp=obj\PackageTemp

	echo.
	@echo Building and packaging the code...
	REM delete old copy
	rd /S /Q "WebProject\!packageTemp!"
	msbuild /p:DeployOnBuild=true;DeployTarget=PipelinePreDeployCopyAllFilesToOneFolder;PackageTempRootDir="!packageTemp!";AutoParameterizationWebConfigConnectionStrings=false;Configuration="!config!"
	if !errorlevel! neq 0 exit /b !errorlevel!

	echo.
	@echo Publishing code ...
	REM Cleanup
	REM Delete outdated folders
	for /d %%a in ("!publishPath!\*") do (
		rd /S /Q %%~Fa
		if !errorlevel! neq 0 exit /b !errorlevel!
	)
	REM delete outdated files except the offline file (ignore case on comparison as windows corrects the path)
	for %%a in ("!publishPath!\*") do (
		if /i not "%%~fa" == "!publishPath!\app_offline.htm" (
			del /Q "%%~fa"
			if !errorlevel! neq 0 exit /b !errorlevel!
		)
	)
	REM deploy updated copy
	xcopy /S /Y "WebProject\!packageTemp!\PackageTmp\*" "!publishPath!"
	if !errorlevel! neq 0 exit /b !errorlevel!

	REM delete temp files
	rd /S /Q "WebProject\!packageTemp!"

	popd

	echo.
	@echo Publishing Web.config for configuration...
	msbuild config-transform.xml /p:Configuration="!config!";DeployPath="!publishPath!"
	if !errorlevel! neq 0 exit /b !errorlevel!
)

echo.
echo !date! !time! Upgrade complete.
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml" >
	<head>
	<title>Application Offline</title>
	<style type="text/css">

	div {
	background-color:#ffffcc;
	padding-top:10px;
	padding-bottom:10px;
	padding-left:10px;
	padding-right:10px;
	border-style:solid;
	border-color:Black;
	border-width:1px;
	}

	</style>
	</head>
	<body>
	<div>
	This application is currently offline. Site update in progress.
	</div>
	</body>
	</html>
	<!-- http://stackoverflow.com/questions/2905151/msbuild-script-and-vs2010-publish-apply-web-config-transform -->
	<!-- this is an msbuild configuration for applying web config transforms -->
	<Project ToolsVersion="4.0"
	DefaultTargets="Transform"
	xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

	<UsingTask TaskName="TransformXml"
	AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\Web\Microsoft.Web.Publishing.Tasks.dll"/>

	<PropertyGroup>
	<ProjectPath>..\Shell.DocumentGenerator.Mvc</ProjectPath>
	<TransformInputFile>$(ProjectPath)\Web.config</TransformInputFile>
	<TransformFile>$(ProjectPath)\Web.$(Configuration).config</TransformFile>
	<TransformOutputFile>$(DeployPath)\Web.config</TransformOutputFile>
	<StackTraceEnabled>False</StackTraceEnabled>
	</PropertyGroup>

	<Target Name="Transform">
	<TransformXml Source="$(TransformInputFile)"
	Transform="$(TransformFile)"
	Destination="$(TransformOutputFile)"
	StackTrace="$(StackTraceEnabled)" />
	</Target>
	</Project>
	/* script for taking a snapshot of a test db for testing scripts */
	-- This is manually run for setting a point for the reset scripts to go back to before re-applying all outstanding changes. Replace ##database## with the name of the database you are creating

	-- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

	-- create snapshot of CI db for testing scripts
	create database ##database##_snapshot
	on (name = 'DevDb', -- name of source file
	filename = 'C:\sqlserver_databases\snapshots\##database##_snapshot.ss1')
	as snapshot of ##database##

	-- Tim Abell
	-- 3 May 2012
	-- Set up config stored in database for local testing.

	/*
	Note that $(mailbox) is a variable set for sqlcmd.
	ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
	As the exchange server is part of our domain the logged in user will
	have permissions to send as the matching email address.
	*/

	update globalConfig
	set value = '$(mailbox)'
	where name like '%EMAIL_ADDRESS';

	update globalConfig
	set value = REPLACE(value, 'http://www.example.com', '$(siteUrl)')
	where value like 'http://www.example.com%';
	@echo off
	Setlocal EnableDelayedExpansion

	REM set variables for building QA
	set server=staging
	set database=QA
	if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run. Changeset Cxxxx
	set publishPath=\\staging\web\QA
	set mailbox=shared@mycompany.co.uk
	set siteUrl=http://staging/qa
	set resetType=snapshot
	set config=Debug (QA)

	call rebuild.bat
	@echo off
	REM =============================================================================================
	REM !! DESTRUCTIVE !!
	REM Resets a test database, runs upgrades, adds test data, adds test files.

	REM Must be run in visual studio command prompt to allow build.
	REM Requires SqlHawk, msbuild, and msdeploy (part of webdeploy) to be on the path.
	REM Environment variables can be overridden by caller / calling scripts.

	REM It is recommended that you set a buffer of 3000 lines on your command window to avoid losing output.
	REM =============================================================================================

	REM allow use of !var! everywhere, the normal !var! version doesn't work with if / for etc.
	REM and avoid messing with variables in the command window scope:
	Setlocal EnableDelayedExpansion

	set localPublishPath=..\WebProject

	REM set variable defaults if not already set by caller
	if "!server!"=="" set server=localhost
	if "!database!"=="" set database=DevDb
	if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run
	if "!publishPath!"=="" set publishPath=!localPublishPath!
	if "!mailbox!"=="" set mailbox=!USERNAME!@mycompany.co.uk
	if "!siteUrl!"=="" set siteUrl=http://localhost:7031
	if "!resetType!"=="" set resetType=backup
	if "!config!"=="" set config=Debug

	echo Server : !server!
	echo Database : !database!
	echo Batch : !batch!
	echo Publish path: !publishPath!
	echo Mailbox : !mailbox!
	echo Site url : !siteUrl!
	echo Config : !config!

	echo.
	echo !date! !time! Starting rebuild...

	if not "!publishPath!"=="!localPublishPath!" (
	echo.
	@echo Taking app offline...
	copy app_offline.htm "!publishPath!"
	if !errorlevel! neq 0 exit /b !errorlevel!
	)

	if not "!skipDb!"=="" (
	echo.
	@echo Skipping db rebuild.
	) else (
	echo.
	@echo Reverting to clean backup of live db...
	@echo Do not interrupt this step as a broken connection can wedge the database. Ref http://stackoverflow.com/questions/520967
	sqlcmd -b -S !server! -E -d master -i reset-to-!resetType!.sql -v database=!database!
	if !errorlevel! neq 0 exit /b !errorlevel!
	)

	REM this is the bit that will be done on the final deployment to live:
	echo.
	@echo Running upgrade...
	call upgrade.bat
	if !errorlevel! neq 0 exit /b !errorlevel!


	if "!skipDb!"=="" (
	echo.
	@echo Applying test data...
	pushd .
	cd test-data\sql\
	call test-data.bat
	if !errorlevel! neq 0 exit /b !errorlevel!
	popd

	echo.
	@echo Configuring global config...
	sqlcmd -b -S !server! -E -d !database! -i global-config.sql -v mailbox="!mailbox!" siteUrl="!siteUrl!"
	)

	echo.
	@echo Copying test files into publish directory...
	xcopy /S /Y test-data\upload !publishPath!\data\upload\
	if !errorlevel! neq 0 exit /b !errorlevel!

	if not "!publishPath!"=="!localPublishPath!" (
	echo.
	@echo Putting app back online...
	del !publishPath!\app_offline.htm
	)

	echo.
	@echo !date! !time! Done.
	-- !! DESTRUCTIVE !!
	-- Restore a local database from backup.
	-- (sqlexpress doesn't support snapshots)
	-- To be used in preparation for re-testing scripted changes with sqlhawk
	-- This script is not intended to be run manually. See ../README.txt for the (re)build process.

	-- kill active connections
	-- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
	ALTER DATABASE [DevDb]
	SET OFFLINE WITH ROLLBACK IMMEDIATE
	ALTER DATABASE [DevDb]
	SET ONLINE

	RESTORE DATABASE [DevDb]
	FROM DISK =
	N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\DevDb.bak'
	WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
	GO
	use [DevDb]
	GO
	-- fix login mapping after databse restore
	alter user WebsiteUser with login = WebsiteUser, default_schema = dbo

	-- Tim Abell
	-- 23 May 2012
	-- !! DESTRUCTIVE !!
	-- Restore a local database back to the last snapshot.
	-- To be used in preparation for re-testing scripted changes with sqlhawk
	-- This script is not intended to be run manually. See ../README.txt for the (re)build process.

	-- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

	/*
	Note that $(database) is a variable set for sqlcmd.
	ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
	*/

	-- kill active connections
	-- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
	ALTER DATABASE $(database)
	SET OFFLINE WITH ROLLBACK IMMEDIATE
	ALTER DATABASE $(database)
	SET ONLINE

	-- reset the snapshot
	restore database $(database)
	from database_snapshot = '$(database)_snapshot';


	-- fix login mapping after databse restore
	use $(database)
	alter user WebsiteUser with login = WebsiteUser, default_schema = dbo
	@echo off
	REM this is a generic script for running all the files matching pattern test-data_*.sql in a folder
	rem set DelayedExpansion to be able to catch errorlevel inside a loop
	setlocal EnableDelayedExpansion
	for /R %%I IN (test-data_*.sql) DO (
	@echo %%I
	sqlcmd -b -S !server! -E -d !database! -i "%%I"
	if !errorlevel! neq 0 exit /b !errorlevel!
	)
	@echo off
	Setlocal EnableDelayedExpansion

	REM TODO: Fill this in with the correct values for live

	set server=liveSql
	set database=liveDb
	if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!;
	set publishPath=\\web01\web\live
	set config=Release (Production)

	echo Server : !server!
	echo Database : !database!
	echo Batch : !batch!
	echo Publish path: !publishPath!
	echo Config : !config!
	echo.

	REM saftey buffer, remove this if we move to a proper build server based deployment
	@echo Are you sure you want to upgrade the live db?
	@echo * Have you made a backup first?
	@echo * Have you filled in the parameters of this script?
	@echo.
	@echo Press Ctrl+C to cancel, any other key to continue.
	pause >nul

	@echo Taking app offline
	copy app_offline.htm !publishPath!

	echo.
	@echo Running upgrade...
	call upgrade.bat
	if !errorlevel! neq 0 exit /b !errorlevel!

	@echo Putting app back online...
	del !publishPath!\app_offline.htm

	@echo Done.