TFS - Team Foundation Server

I always see TFS being used for application code.  Rarely do I see TFS being used for databases, SSIS, or SSRS.  Many believe there is no need or TFS is unable to do so.  In the next few posts I am going to give examples of each.

Personally, I like to create a separate build for each databases, SSIS and SSRS.

Depending on how tightly coupled your databases are, you may want to build them together or keep them in separate builds.  Below I am building 2 database projects together.  I then copy the files to a location I will use for the release.

Releases are my favorite part of  having databases in TFS.  Here you can set up each environment, and appoint people to be gatekeepers for each environment.  I let my QAs and BAs push the releases to the QA or UAT themselves when they see fit, taking the developers out of that role.

To release the database, we will need to find and call the SqlPackage.exe

Here is where my utility was installed on the build machine.

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe

You can read more here:

https://msdn.microsoft.com/en-US/library/hh550080(v=vs.103).aspx

using the following parameters we can deploy our changes to the instance of our choice.  Please read through the parameters and pick the ones best for your situation

• /a:Publish
  
  
  
  • incremental update of the database


• /tcs:"Data Source=server\instance;Integrated Security=true;Initial Catalog=Database;Pooling=false"
  
  
  
  • connection to database


• /sf:"D:\ArtifactStaging\Database\$(Build.BuildNumber)\Database\Database\Database\bin\Release\Database.dacpac"
  
  
  
  • the location of the dacpac file used as source


• /p:VerifyDeployment=True
  
  
  
  • verify deploment


• /p:IgnorePermissions=True
  
  
  
  • do not use permissions in the project.  this lets DBAs manage permissions independently.


• /p:BackupDatabaseBeforeChanges=False
  
  
  
  • Create a backup before changes are applied.  this is a MUST for Production


• /p:DropObjectsNotInSource=True
  
  
  
  • sometimes objects are added adhoc, this will remove them.


• /p:BlockOnPossibleDataLoss=False
  
  
  
  • if set to True, any action which removes data will cause a failure


• /p:IgnoreRoleMembership=True
  
  
  
  • igrone membership of roles in the source


• /p:ExcludeObjectTypes=RoleMembership;users;Logins;
  
  
  
  • Exclude objects.  here Roles, users, and logins are excluded for DBAs to managed independently