- 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>
- Posted by justin on February 26, 2008
When we went to deploy our notification services (NS )instance, we ran into an issue with the subscription management web page failing with the login failed message below.
Notification Services failed to get the metadata for the specified instance. Instance Name: [YourAppName] SqlServerError: Source: .Net SqlClient Data Provider Number: 18456 State: 1 Class: 14 Server: (local) Message: Login failed for user 'ASPNET'. Procedure: Line Number: 65536
The same code worked just fine previously in SQL Server 2000 NS and it worked with no problem in our dev environments since we all have sql admin rights and are running one a single machine whereas in production we do not have admin rights and the web/database servers are seperate machines.
So it turns out that with 2005 NS when you call say new NSInstance and pass the NS Database Name, it now automatically connects to the instance versus in SQL 2000 NS it waited until you set the reference to NSApplication and passed the NSInstance into is. So now our code looks like the following:
_instance = new NSInstance(AlertsDatabaseName, AlertsDatabaseUser, AlertsDatabasePassword);
_application = new NSApplication(_instance, _applicationName);
- Posted by justin on February 12, 2008
I had a weird SSIS error this morning and couldn't find anything in books online. I actually had to google for the fix. http://msdn2.microsoft.com/en-us/library/ms137789.aspx
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2005 Books Online.
Login Timeout Expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to ssNoVersion [2]. (MsDtsSvr).
- Posted by justin on April 16, 2007
Today I started working on my cruise control setup and added my Team Edition for Database Professionals project into the build. Everything went well except that the warning messages from it were taking up a good portion of my log file.
Most of the warnings are coming from the TDS3006 error which basically means that it cannot figure out where a column comes from. To me this error is just about worthless to me since having an alias on my tables/columns seems to cause the problem even though the statement is technically valid.
Luckily enough you can suppress this message. To suppress warning messages for the Team Edition for Database Professional you need to add the error number without the TDS to the suppress warning textbox on the project properties\build.
A complete list of error messages for Team Edition for Database Professionals, http://msdn2.microsoft.com/en-us/library/bb203958(VS.80).aspx
- Posted by justin on February 20, 2007
Here is a good list of all of the updates that came with sp2.