- Posted by Justin on September 30, 2008
I am using the VSTS Database Editon to manage my schema in development as well. For deployments I am using a combination of VSTS Database Edition generate scripts, manually created data migration scripts and nant.
Steps for generating scripts for deployment:
- Create a new database with the schema from the last production release.
- Do a Schema Compare in VSTS DB Edition with the project as the source and the new database as the target. Export to the Editor so that you can get the sql script to run but don’t execute the script yet. This will create you the upgrade script.
- Do a Schema Compare in VSTS DB Edition with the new database as the source and project as the target. Export to the Editor so that you can get the sql script to run but don’t execute the script yet. This will create you the back out script.
- Scan thru the generate scripts and make any additions/changes to handle the data migration. Unfortunately it is hard to auto generate a script to manage the data side of the upgrade.
- To test the scripts, restore a copy of the production database and run the upgrade/back out scripts against it.
Nant Script used for Upgrade Deployment:
<!—List of upgrade scripts -->
<property name="releases" value="3.0.1,3.0.2,3.0.3,3.0.4,3.0.5,3.0.6,3.0.7" />
<!—Reverse list of upgrade scripts used for back out -->
<property name="releases.reverse" value="3.0.7,3.0.6,3.0.5,3.0.4,3.0.3,3.0.2,3.0.1" />
<!—Current production release. Could get from DB. Used to determine how many back out scripts to run -->
<property name="current.version" value="3.0.7" />
<!— Release package number. Used to determine when to start running the upgrade scripts.
<property name="current.release" value="3.0.8" />
<!—Loop thru the releases, run all once you either hit the current release or what is in prod original. -->
<!-- Files are name v.X.X.X.AlterScript.sql and vX.X.X.DataAlterScript.sql -->
<property name="runmorepappscripts" value="false" />
<foreach item="String" in="${releases}" delim="," property="count">
<if test="${property::get-value('count') == property::get-value(current.release')}">
<property name="runmorepappscripts" value="true" />
</if>
<if test="${property::get-value('runmorepappscripts') == 'true'}">
<property name="filename" value="${path::combine(release.database.dir, 'v' + count + '.AlterScript.sql')}" />
<if test="${file::exists(path::combine(release.database.dir, filename))}">
<echo message="Running ${filename} script against ${database.name} database on ${database.server}" />
<sql
connstring=""
transaction="true"
delimiter="GO"
delimstyle="Line"
batch="false"
print="true"
source="${path::combine(release.database.dir, filename)}"
verbose="${verbose}" />
<echo message="Completed ${filename} script against ${database.name} database on ${database.server}" />
</if>
<property name="filename" value="${path::combine(release.database.dir, 'v' + count + '.DataAlterScript.sql')}" />
<if test="${file::exists(path::combine(release.database.dir, filename))}">
<echo message="Running ${filename} script against ${database.name} database on ${database.server}" />
<sql
connstring=""
transaction="true"
delimiter="GO"
delimstyle="Line"
batch="false"
print="true"
source="${path::combine(release.database.dir, filename)}" verbose="${verbose}" />
<echo message="Completed ${filename} script against ${database.name} database on ${database.server}" />
</if>
</if>
<if test="${property::get-value('count') == property::get-value(current.version')}">
<property name="runmorepappscripts" value="true" />
</if>
</foreach>
Nant Script for backout
<!—Loop thru the releases reverse, run all until you hit what was original in prod. Files are name v.X.X.X.AlterScript.sql and vX.X.X.DataAlterScript.sql -->
<property name="runmorepappscripts" value="true" />
<foreach item="String" in="${releases.reverse}" delim="," property="count">
<if test="${property::get-value('count') == property::get-value(‘current.version')}">
<property name="runmorepappscripts" value="false" />
</if>
<if test="${property::get-value('runmorepappscripts') == 'true'}">
<property name="filename" value="${path::combine(release.database.dir, 'v' + count + '.BackoutAlterScript.sql')}" />
<if test="${file::exists(path::combine(release.database.dir, filename))}">
<echo message="Running ${filename} script against ${database.name} database on ${database.server}" />
<sql connstring="" transaction="false" delimiter="GO" delimstyle="Line" batch="false" print="true" source="${path::combine(release.database.dir, filename)}" verbose="${verbose}" />
<echo message="Completed ${filename} script against ${fuzion.database.name} database on ${fuzion.database.server}" />
</if>
<property name="filename" value="${path::combine(release.database.dir, 'v' + count + '.BackoutDataAlterScript.sql')}" />
<if test="${file::exists(path::combine(release.database.dir, filename))}">
<echo message="Running ${filename} script against ${database.name} database on ${database.server}" />
<sql connstring="" transaction="false" delimiter="GO" delimstyle="Line" batch="false" print="true" source="${path::combine(release.database.dir, filename)}" verbose="${verbose}" />
<echo message="Completed ${filename} script against ${database.name} database on ${database.server}" />
</if>
</if>
</foreach>