title: Deploying MSSQL change scripts
body: <p>So I am in the throes of developing our Continuous Integration practices.  We are a .Net/MSSQL shop.  We will all soon be on VS2012.  We have settled on CruiseControl.Net for CI server, using msbuild to compile our projects.  We use SVN (possibly switching to Git later, but that's another discussion) for source control.  I'm leaning towards using InstallShield to deploy code packages (usually web apps and/or batch exeutables) to our QA and production servers.  (CCNet would build these MSI's as part of our CI.)  We are also starting to include unit testing in our projects, and will use NUnit integrated with CCNet to run them automatically upon check-in.</p>
  
  <p>So far this works for our standard web app/exe development.  Where it does not fit in (yet) is with our MSSQL change management, or lack thereof.  It's been pretty cowboy how we've done this.  Some folks have used Migrator.Net.  Others just do a SQL Compare with Redgate and generate a script.  Still others have hand-written sql scripts.  It may or may not be in SVN.  "Source control" at the db level is basically "we have backups of our databases."  <em>Boo, hiss</em>.  Needless to say that if we want some consistency with our CI and with our deployments, we need to settle on something.  So far I am leaning towards using VS SQL projects to handle the change management and deployment.</p>
  
  <p>Note: we (developers) are not supposed to push changes.  Sys admins do that.  So <em>we</em> can't run anything to deploy code or sql.</p>
  
  <p>So, 2 problems to solve (I think):</p>
  
  <ol>
  <li><p>What "technique" to use so that our CI server blows away a CI version of the database so that unit tests can be tested against it.  I've settled that VS2012 SQL projects can do that.  CCNet can run msbuild against the db project, which recreates the database.  This is fairly easy.</p></li>
  <li><p>How to generate change scripts for our QA and prod environments?  This one I'm stuck on.  </p></li>
  </ol>
  
  <p>VS can do a schema compare and then generate the sql script -- but it is dependent on sqlcmd.  So our sys admins would have to run sqlcmd from the command prompt to deploy it... probably not ideal.  Right?</p>
  
  <p>I could run msbuild again to deploy... but I don't want the database re-created, I just want changes deployed.  </p>
  
  <p>So what are the options here?  I need something self-contained for the admins to run -- and check-in to SVN.  Should I make another msi for database deployments?  Can CCNet/msbuild make some other kind of "deployment package" for database changes (not re-creation) where the sys admins can double-click and go?</p>
  
  <p>How do you all handle this?</p>
  
  <p>Thanks
  Tom</p>