Deploy a database project with TFS Build

When you develop web application you often have this scenario. Some people are testers, they are testing the application in dev all day, and they want always to test latest version. Moreover testers usually fills data into web application, so they want their data to be preserved between various deploy. If you simply clear all data in test database you will end with a lot of furious tester so do not even think to do this.
 
If a developer needs to change database schema, we need to automate not only the deploy of the web application, but we need also to sync test database with the latest changes. This is necessary so the test site can work with the new version, but old data is preserved for the happiness of the testers.
 
This is quite simple using TFS and Database project. The situation is this one

 

When a developer does a check-in, the build server grabs the tip from TFS, then it runs the builds script and deploy the new version of the site in the test web server. At the same time test database gets updated with the latest database schema.

Next we need the ability to automatically deploy changes to a specific database, this task is quite simple and you can find some details here in msdn, but we need to insert this command into the msbuild script. Here is the code:

<Target Name="AfterDropBuild"><Message Text="Deploy of project database" />
<MSBuild Projects="$(SolutionRoot)srcDbEditionNorthwindTestNorthwindTestNorthwindTest.dbproj"
Properties="OutDir=$(DropLocation)$(BuildNumber)debug;
TargetDatabase=NorthwindCiTest;
DefaultDataPath=C:Program FilesMicrosoft SQL ServerMSSQL.4MSSQLData;
TargetConnectionString=Data Source=VM2003R2_1SQLTEST%3Buser=sa%3Bpwd=Pa$$w0rd;
DeployToDatabase=true;" 
Targets="Deploy"/> 
</Target>

 

Original post from Ricci Gian Maria

http://geekswithblogs.net/jakob/archive/2012/04/25/deploying-ssdt-projects-with-tfs-build/

Who is locking my SQL database?

Need to know who is logged on to your SQL database ,here is a nice script that I got from Gary Dunne

DECLARE @spid1 int = NULL      /* server process id to check for locks */ 
DECLARE @spid2 int = NULL       /* other process id to check for locks */ 

set nocount on
/*
** Show the locks for both parameters.
*/ 
declare @objid int,
   @dbid int,
   @string Nvarchar(255)

CREATE TABLE #locktable
   (
   spid       smallint
   ,loginname nvarchar(20)
   ,hostname  nvarchar(30)
   ,dbid      int
   ,dbname    nvarchar(20)
   ,objId     int
   ,ObjName   nvarchar(128)
   ,IndId     int
   ,Type      nvarchar(4)
   ,Resource  nvarchar(16)
   ,Mode      nvarchar(8)
   ,Status    nvarchar(5)
   )
   
if @spid1 is not NULL
begin
   INSERT #locktable
      (
      spid
      ,loginname
      ,hostname
      ,dbid
      ,dbname
      ,objId
      ,ObjName
      ,IndId
      ,Type
      ,Resource
      ,Mode
      ,Status
      )
   select convert (smallint, l.req_spid) 
      --,coalesce(substring (user_name(req_spid), 1, 20),'')
      ,coalesce(substring (s.loginame, 1, 20),'')
      ,coalesce(substring (s.hostname, 1, 30),'')
      ,l.rsc_dbid
      ,substring (db_name(l.rsc_dbid), 1, 20)
      ,l.rsc_objid
      ,''
      ,l.rsc_indid
      ,substring (v.name, 1, 4)
      ,substring (l.rsc_text, 1, 16)
      ,substring (u.name, 1, 8)
      ,substring (x.name, 1, 5)
   from master.dbo.syslockinfo l,
      master.dbo.spt_values v,
      master.dbo.spt_values x,
      master.dbo.spt_values u,
      master.dbo.sysprocesses s
   where l.rsc_type = v.number
   and   v.type = 'LR'
   and   l.req_status = x.number
   and   x.type = 'LS'
   and   l.req_mode + 1 = u.number
   and   u.type = 'L'
   and   req_spid in (@spid1, @spid2)
   and   req_spid = s.spid
end
/*
** No parameters, so show all the locks.
*/ 
else
begin
   INSERT #locktable
      (
      spid
      ,loginname
      ,hostname
      ,dbid
      ,dbname
      ,objId
      ,ObjName
      ,IndId
      ,Type
      ,Resource
      ,Mode
      ,Status
      )
   select convert (smallint, l.req_spid) 
      --,coalesce(substring (user_name(req_spid), 1, 20),'')
      ,coalesce(substring (s.loginame, 1, 20),'')
      ,coalesce(substring (s.hostname, 1, 30),'')
      ,l.rsc_dbid
      ,substring (db_name(l.rsc_dbid), 1, 20)
      ,l.rsc_objid
      ,''
      ,l.rsc_indid
      ,substring (v.name, 1, 4)
      ,substring (l.rsc_text, 1, 16)
      ,substring (u.name, 1, 8)
      ,substring (x.name, 1, 5)
   from master.dbo.syslockinfo l,
      master.dbo.spt_values v,
      master.dbo.spt_values x,
      master.dbo.spt_values u,
      master.dbo.sysprocesses s
   where l.rsc_type = v.number
   and   v.type = 'LR'
   and   l.req_status = x.number
   and   x.type = 'LS'
   and   l.req_mode + 1 = u.number
   and   u.type = 'L'
   and   req_spid = s.spid
   order by spID
END
DECLARE lock_cursor CURSOR
FOR SELECT dbid, ObjId FROM #locktable WHERE Type ='TAB'

OPEN lock_cursor
FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId 
WHILE @@FETCH_STATUS = 0
   BEGIN
   SELECT @string = 
      'USE ' + db_name(@dbid) + char(13)  
      + 'UPDATE #locktable SET ObjName =  object_name(' 
      + convert(varchar(32),@objId) + ') WHERE dbid = ' + convert(varchar(32),@dbId) 
      + ' AND objid = ' + convert(varchar(32),@objId) 
   
   EXECUTE (@string) 
   FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId    
   END
CLOSE lock_cursor
DEALLOCATE lock_cursor
 
SELECT * FROM #locktable
DROP TABLE #locktable

sp_lock2.sql (3.44 kb)

SQL71566 when upgrading to VS12

In VS 2012 I receive the following error message (one for each table) when trying to build a database project.

SQL71566: Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlFilegroup cannot not be set on both the Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable and the clustered Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPrimaryKeyConstraint.

For reference here is script associated with this error.

CREATE TABLE [dbo].[OperationsMaster](
[OperationID] [numeric](18, 0) NOT NULL,
[OperationName] [nvarchar](150) NOT NULL,
[OperationTypeID] [numeric](18, 0) NOT NULL,
CONSTRAINT [PK_OperationsMaster] PRIMARY KEY CLUSTERED
(
[OperationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In above script in last line on word [PRIMARY] red line appears. When I run the same script in SSMS it executed successfully.

You can get your project to build by enabling the project setting “Enable extended Transact-SQL verification for common objects.”.