Visual Studio 2010 Database Projects

I'm having issues trying to get teamcity to deploy my database projects in vs 2010.  I have a solution that has 3 database projects in it, and they each have Deploy options set up to only create the .sql file.  Now if I "Deploy Solution" in Visual Studio 2010 I get a ProjectName.sql file created.  I've tried to setup this solution in TeamCity but I can't get it to create the .sql file.  I can get it to successfully "Build" the project, but just building doesn't create the script.  I am using the Visual Studio (sln) build runner, and I've tried setting the target to "Deploy" but I get an error about an invalid target.  The reason I tried to use the "Deploy" target was based on the fact I saw a MSBuild guide on MSDN that used /t:Deploy.  I've also tried to switch to the MSBuild runner, point the solution file to the database project file and use /t:Deploy, but I get assembly not found errors like that.  

Does anyone have any advice or a guide on how to get TeamCity to deploy a database project?  I've gotten TeamCity to work with my VS website, but I just can't get the database projects to work.  thanks

5 comments

More Information;
  I'm now trying to use the Command Line BuildRunner along with the DevEnv.exe command.  I can't figure out the correct command line syntax.  Ignore paths in the following code;
DevEnv.exe "SolutionName.sln" /Deploy Debug /out "error.log"

The command above will run but it doesn't create the Project.sql file that contains the deploy script.  It creates the dbschema, manifest, etc...  Even when I run it from a normal command line the command runs but doesn't create the final script.  If I change /Deploy to /Build it builds without any errors also.
When I run the command with the /Deploy the last line in the error.log file is "Project 'Project.dbproj' was successfully serialized to file '...Project.dbmdl'.

Any help would be greatly appreciated, also, when I open the solution and Deploy Solution, the script gets generated properly.

0

We have an issue in our issue tracker to support deven.exe base build runner at
http://youtrack.jetbrains.net/issue/TW-5385

Have you tried running the build with msbuild.exe locally? What TeamCity Solution runner doing is starting 'msbuild.exe solution.sln'. If the build succeeds locally, please try running msbuld.exe on the build agent machine from the same user account that used to run build agent service.

Do you have VS2010 installed on the build agent?

0

Currently TeamCity is installed on my development machine.  I wanted to get a proof of concept working on my machine before moving it to a build server.

I figured out how to use VSDBCMD.exe from a command prompt yesterday, I'm going to try to get it to work in TeamCity and I will post back with my results later today.  I messed up my configuration so I need to get it back to the way it was when it generated the deployment files but not the final sql file, then I think I can use VSDBCMD to use those files and generate the final sql file. I'll post back on this thread later today. thanks

0

Just wanted to post an update on this, at this time I have 5 projects setup;

1. This step is using the Visual Studio 2010 runner, it builds the solution (1 website, 8 class libraries, 3 database projects, 1 web deployment project).  After this step is completed I have my website precompiled in a folder ready for deployment.  The database

      projects also created all of the files necessary for deployment (manifest, dbschema, etc...) other than the final sql change script.

2. This step is using the Command Line runner, it deploys the first database project (for now it is only creating the sql file, not making the changes on the target database but I should be able to flip a switch and have it make the changes).

      For this step my command executable is "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy\VSDBCMD.EXE"

       my command parameters are @C:\dev\Deploy.txt, I had to use a file because I couldn't figure out how to get quotes to work properly, the file contains the following;

       /a:Deploy /dd:- /dsp:sql /manifest:DB1.deploymanifest /cs:"Data Source=dbname;User ID=user;Password=pass"

    I should be able to change /dd:- to /dd:+ and VSDBCMD should then deploy the changes to the database server.     

3. Same as step 2 just a different database.

4. Same as step 2 just a different database.

5. This step uses the command line runner, I am executing a batch file that does the following;

    set variables for the current date and time

    delete *.pdb files from bin folder,

    delete config files that were used for web.config section replacement in the web deployment project

    map a network drive to the web server

    zip up the old files and place in a backup folder

    delete the old files

    copy the new files to the web server

    zip up the files that were used in this deployment (db files and web site files) and place in a deployed folder

    delete all files used in the deployment

    unmap the network drive.

So far this is working in my test environment on my development machine, my next steps are to flip /dd:- to /dd:+ and see if it performs the database updates, then I am going to install team city on another machine (probably the web server to make it easier to shutdown and startup IIS).  After that I may look into replacing the batch file in step 5 with msbuild, also I am going to look into adding the deployed zip file back into svn.

This is going to be the process for our weekly test builds.  I am probably going to setup another project in team city that will deploy to live using the files that were in the deployed zip file (running vsdbcmd with a new target to deploy db changes).

I'll reply to this post after I get more steps done, but as of now it is looking like I have everything I need to deploy my website and databases. thanks

0

Thank you for describing your case. If you need to start/stop IIS on the server you do not need to have TeamCity server installed on that server machine. TeamCity launches builds on a build agents, it's distributed system, thus you may simply install a build agent on the server machine and make your builds require that build agent to run. This is the way how we upgrade TeamCity server in our setup. Please have a look to some description of build agent in the documentation of TeamCity at http://confluence.jetbrains.net/display/TCD5/Build+Agent

Merging all those configurations in to one MSBuild script is a good idea, I believe it will make the process more transparent.

What was set to commandline in the UI and what was actually put to process arguments? Try putting all parameters to the commandline arguments as it was shown in the example? Do you use 5.1.3?

Please have a look to parameters and references at http://confluence.jetbrains.net/display/TCD5/Using+Properties
You may define a build system property with the value for /dd, like "/dd:%system.update.real.database%". The value of that property is defined in "6. Properties and Environment variables" build configuration section.

0

Please sign in to leave a comment.