2

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.

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." Boo, hiss. 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.

Note: we (developers) are not supposed to push changes. Sys admins do that. So we can't run anything to deploy code or sql.

So, 2 problems to solve (I think):

  1. 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.

  2. How to generate change scripts for our QA and prod environments? This one I'm stuck on.

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?

I could run msbuild again to deploy... but I don't want the database re-created, I just want changes deployed.

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?

How do you all handle this?

Thanks Tom

ironfist
  • 797
  • 7
  • 14
  • Just one tip. You should write your DDL scripts in an "idempotent manner". Basically, that means you can run the script once or 100 times and the results are the same. Simple example. Jr developers hate idempotent scripts because they have to "care". http://stackoverflow.com/questions/1077412/what-is-an-idempotent-operation – granadaCoder Jun 19 '13 at 14:11
  • I won't say that I'm consistent about that, because it's always a manual process to make sure running it twice doesn't break anything. How would one go about automating SQL deploy scripts while ensuring they are idempotent? – ironfist Jun 21 '13 at 14:59

1 Answers1

3

Check out the SQL Server Data Tools package from the Microsoft site.

This will register a new SQL Server 2012 Database type project to contain the definition for all of your database structures. Upon build, this will generate a create script that you can use to deploy your database.

Then for upgrading your database, use the SQLPACKAGE.EXE tool using the create script and target database server name to generate an Update.sql script.

Update: Also on the issue of how you're running unit tests, you could create supplemental methodologies that invoke the create scripts by launching a process and and passing the path to the output create.sql script, then have your tests 'tear down' the database using the same method but with a drop database statement.

Nicodemeus
  • 4,005
  • 20
  • 23
  • Should have mentioned our databases our MSSQL 2008, no plans to upgrade. Does that matter? – ironfist Jun 17 '13 at 20:43
  • There is a 2008 version of SSDT. The 2008 version implements .dbproj projects while the 2012 version implements .sqlproj projects. When we were on 2008 we used the 2008 database projects exclusively as the 2012 ssdt versions did not work. The SSDT package is something you'd install on your development machines and on the build server. ... also the sqlpackage.exe is not included in the 2012 version. – Nicodemeus Jun 17 '13 at 21:13