Jamie Thomson : Datadude, Visual Studio 2010http://sqlblog.com/blogs/jamie_thomson/archive/tags/Datadude/Visual+Studio+2010/default.aspxTags: Datadude, Visual Studio 2010enCommunityServer 2.1 SP2 (Build: 61129.1)Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experienceshttp://sqlblog.com/blogs/jamie_thomson/archive/2012/01/01/implementing-sql-server-solutions-using-visual-studio-2010-database-projects-a-compendium-of-project-experiences.aspxSun, 01 Jan 2012 18:13:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:40711jamiet29http://sqlblog.com/blogs/jamie_thomson/comments/40711.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=40711http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=40711<p>Over the past eighteen months I have worked on four separate projects for customers that wanted to make use of Visual Studio 2010 Database projects to manage their database schema.* All the while I have been trying to take lots of notes in order that I can write the blog post that you are reading right now – a compendium of experiences and tips from those eighteen months. I should note that this blog post should not necessarily be taken as a recommendation to actually <i>use</i> database projects in Visual Studio 2010 – it is intended to be useful for those of you that have already made the decision to use them; having said that, I do make recommendations as to actions I think you should take if you have made that decision.</p> <p>First let’s be clear what we’re talking about here. Visual Studio Database Projects have been known by a few different names down the years, some of which you may be familiar with. If you have ever heard the terms datadude, DBPro, teamdata, TSData or Visual Studio Team System for Database Professionals then just know that all of these terms refer to the same thing, namely the project type highlighted below when starting a new project in Visual Studio:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_4302F9CA.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_4079C80C.png" width="748" height="411"></a></p> </blockquote> <p>From here onwards I am going to refer to Visual Studio Database projects and all the features therein simply as datadude because that’s a popular colloquial name (and is also much quicker to type). Know also that at the time of writing the features that I am talking about here are currently undergoing some changes ahead of the next release of SQL Server (i.e. SQL Server 2012) in which these features are mooted to be delivered under a new moniker - SQL Server Developer Tools (SSDT).</p> <p>OK, with all those preliminaries out of the way let’s dig in.</p> <h1>Continuous Integration</h1> <p><a href="http://www.martinfowler.com/articles/continuousIntegration.html" target="_blank">Continuous Integration</a> (CI) is a development practise that has existed for many years but in my experience has not been wholly embraced by the database community. The idea behind CI for databases is that every time a developer checks-in a piece of code be it a stored procedure, a table definition or whatever, the entire database project is built and then deployed to a database instance. Microsoft provide a useful article <a href="http://msdn.microsoft.com/en-us/library/aa833165.aspx" target="_blank">An Overview of Database Build and Deployment</a> that goes some way to explaining how to setup your CI deployment.</p> <p>CI is one of the fundamental tenets that underpins a lot of the things I talk about later in this blog post and hence gives rise to my first recommendation when using datadude:</p> <blockquote> <p><font size="4">Recommendation #1: Use Source Control and implement a Continuous Integration deployment</font></p> </blockquote> <p><i>In an earlier draft of this blog post I outlined in detail the CI configuration from one of the aforementioned projects. Its not suitable for inclusion at this point in the current draft but I still think there is some useful information to be gleaned so I have included it below in “Appendix – An example CI configuration”.</i></p> <h1>Composite Projects</h1> <p>Each of the four aforementioned projects were brownfield projects meaning that each already encompassed some established, deployed, databases and they wanted to bring those databases under the control of datadude. Each project had thousands of objects across multiple databases and in this situation it is very likely that some of the stored procedures, views or functions will refer to objects in one of the other databases. The way to resolve those references is to use <a href="http://blogs.msdn.com/b/gertd/archive/2007/07/26/database-references.aspx" target="_blank">database references</a> however once you have applied all of your database references it is still very possible that you will run into a situation where code in database A refers to an object in database B while at the same time database B refers to an object in database A. This is depicted in the following figure:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_05B21F36.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2BA7CF8C.png" width="435" height="228"></a></p> </blockquote> <p>Here we have a view [DB1]..[View2] that selects data from [DB2]..[Table1] and a view [DB2]..[View1] that selects data from [DB1]..[Table1]. Datadude does not allow a database reference from [DB2] to [DB1] if there is already a database reference from [DB2] to [DB1] and hence will return an error akin to:</p> <ul> <li>SQL03006: View: [dbo].[View1] has an unresolved reference to object [DB1].[dbo].[Table1]. </li> </ul> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0395206D.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_096FC406.png" width="574" height="108"></a></p> </blockquote> <p>We have the proverbial chicken-and-egg problem, [DB1] can’t be created before [DB2] and vice versa. This problem is solved by using <i><b>Composite Projects</b></i> (not to be confused with Partial Projects) which allow you to split objects that are intended to be in the same database over multiple datadude projects. I could go over how you set one of these things up but there’s really no need because there is a rather excellent walkthrough on MSDN at <a href="http://msdn.microsoft.com/en-us/library/dd193415.aspx" target="_blank">Walkthrough: Partition a Database Project by Using Composite Projects</a>; the reason for me mentioning it here is to make you aware that composite projects exist and of the problem that they solve. If you are introducing datadude into a brownfield project then it is highly likely that you are going to require composite projects so learn them and learn them good.</p> <p>One important last note about composite projects is to answer the question “How does the tool know that the multiple projects refer to the same database?” The answer is given at the walkthrough that I linked to above; namely, it says:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5A3DD86E.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="Composite projects - referring to the same database" border="0" alt="Do not specify server variables and values or database variables and values when you define references in a composite project. Because no variables are defined, the referenced project is assumed to share the target server and database of the current project." src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_7509FE7A.png" width="631" height="116"></a></p> </blockquote> <blockquote> <p><i>“Do not specify server variables and values or database variables and values when you define references in a composite project. Because no variables are defined, the referenced project is assumed to share the target server and database of the current project.”</i></p> </blockquote> <p>So now you know! To put it another way, if you reference one project from another and don’t tell datadude that the two projects refer to different databases then it assumes they refer to the <i>same </i>database.</p> <h1>Code Analysis</h1> <p>Datadude provides the ability to analyse your code projects for code in stored procedures and functions that it considers to be inferior and highlight it – this feature is called <b><i>Code Analysis</i></b>. Note that Code Analysis will not highlight code that is syntactically incorrect (datadude does that already, which may well be considered its core feature), it highlights code that is syntactically correct but may be considered defective when executed. Specifically Code Analysis will highlight the following perceived code defects (click through on the links for explanations of why these are considered code defects):</p> <ul> <li><a href="http://msdn.microsoft.com/en-us/library/dd193296.aspx" target="_blank">Use of SELECT *</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd172121.aspx" target="_blank">Use of @@IDENTITY rather than SCOPE_IDENTITY()</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193263.aspx" target="_blank">NCHAR &amp; NVARCHAR fields whose maximum length is 1 or 2</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd172122.aspx" target="_blank">Deprecated join syntax</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd172136.aspx" target="_blank">An output parameter that might not be assigned to</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193269.aspx" target="_blank">Casts that could result in data loss</a> (in my experience this is the one that shows up most often – particularly in inherited code) </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd172134.aspx" target="_blank">Use of special characters in object names</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193421.aspx" target="_blank">Use of reserved words</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd172115.aspx" target="_blank">Use of sp_ prefix for stored procedures</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193249.aspx" target="_blank">Use of unindexed columns in IN predicates</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193273.aspx" target="_blank">Use of patterns beginning with a wildcard in a LIKE predicate</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193264.aspx" target="_blank">Code that would not use an existing index that might actually be beneficial</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193267.aspx" target="_blank">Use of ISNULL() on nullable columns</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193285.aspx" target="_blank">Deterministic function calls in a WHERE predicate</a> </li> </ul> <p>In my opinion the best aspect of Code Analysis is that it can be run as part of your Continuous Integration deployment meaning that if anyone checks in some deficient code, BOOM, your CI deployment fails and the developer is left red-faced. Nothing else has increased the code quality on my projects quite like running Code Analysis as part of a CI deployment.</p> <p>Hopefully I have convinced you that turning on Code Analysis is a good idea. If you agree then head to the project properties and check the box labelled <b>Enable Code Analysis on Build</b>. I also recommend checking the <b>Treat warnings as errors</b> boxes otherwise you’ll find that <a href="http://www.codinghorror.com/blog/2007/08/discipline-makes-strong-developers.html" target="_blank">undisciplined developers</a> will simply ignore the warnings.</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0CC135E1.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="Enable datadude code analysis" border="0" alt="Enable datadude code analysis" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_129BD97A.png" width="583" height="204"></a></p> </blockquote> <blockquote> <p><i>N.B. Incidentally if you have time I highly recommend that you go and read the blog post I linked to there – </i><a href="http://www.codinghorror.com/blog/2007/08/discipline-makes-strong-developers.html" target="_blank"><i>Discipline Makes Strong Developers</i></a><i> by Jeff Atwood. I’ve read many thousands of blog posts in my time and that is the one that has influenced me more than any other.</i></p> </blockquote> <p>Turning on Code Analysis on a greenfield project is a no-brainer. On a brownfield project its not quite so easy – on a recent engagement I moved a single database into datadude and turned on Code Analysis which immediately found over two thousand perceived code defects. I generally abhor the use of that famous maxim <a href="http://en.wikipedia.org/wiki/If_it_ain%27t_broke,_don%27t_fix_it#.22If_it_ain.27t_broke.2C_don.27t_fix_it..22" target="_blank">if it aint broke, don’t fix it</a><i></i> in our industry but on occasions like this you may be well advised to heed that advice and leave well alone for fear of breaking code that does what it is supposed to (no matter how inefficiently it does it). Instead you do have the option to suppress Code Analysis warnings/errors:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_434EE125.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="Suppress datadude code analysis" border="0" alt="Suppress datadude code analysis" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_492984BE.png" width="585" height="195"></a></p> </blockquote> <p>I advise using Code Analysis suppression sparingly. Recently I discovered that one of the developers on my team had decided it was OK to simply suppress every error that was thrown by Code Analysis without first investigating the cause. I was not amused!</p> <blockquote> <p><font size="4">Recommendation #2: Turn on Code Analysis</font></p> </blockquote> <h1>Realising the value of idempotency</h1> <p>An operation is considered idempotent if it produces the same result no matter how many times that operation is applied; for example, multiplication by a factor of one is an idempotent operation – no matter how many times you multiple a number by one the result will always be the same.</p> <p>Idempotency is a vital facet of database deployment using datadude. Datadude tries to ensure that no matter how many times you deploy the same project the state of your database should be the same after each deployment. The implication here is that during a deployment datadude will examine the target database to see what changes (if any) need to be made rather than simply attempting to create lots of objects; if all the objects already exist nothing will be done. In my opinion this is the single biggest benefit of using datadude – you don’t have to determine what needs to be done to change your database schema to the desired state, datadude does it for you.</p> <p>If I have convinced you about the value of idempotency within datadude then you should also realise that the same rigour should be applied to data as well. Datadude provides Post-Deployment scripts that allow you to deploy data to your schema however there is no inbuilt magic here – datadude will simply go and run those scripts as-is, it will not try and comprehend the contents of those scripts. What this means is that you, the developer, are responsible for making your Post-Deployment scripts idempotent and the easiest way to do that is to employ the T-SQL MERGE statement.</p> <p>T-SQL’s INSERT is not sufficient as it will work once and thereafter fail as it will be attempting to insert already inserted data; this gives rise to my third recommendation:</p> <blockquote> <p><font size="4">Recommendation #3: When running your deployment in a test environment, run it more than once.</font></p> </blockquote> <h1>No-brainer Recommendations</h1> <p>I consider Code Analysis and Idempotency to be so important that I called them out as dedicated headlines. In this section I’ll outline some additional simple measures that you can undertake and which will, if employed correctly, have a profound effect on the success of your datadude projects.</p> <h2>Putting a build number into the DB</h2> <p>I find it is very useful to maintain a log of deployments that have been made to a database and my chosen method is to use a Post-Deployment script to insert a value into some table. Here’s the definition of the table I use for this:</p> <blockquote> <p><code style="font-size:12px;"><span style="color:blue;">CREATE TABLE </span><span style="color:black;">[dbo].[DeployLog] <br></span><span style="color:gray;">( <br>&nbsp;&nbsp; </span><span style="color:black;">[BuildId]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:blue;">NVARCHAR</span><span style="color:gray;">(</span><span style="color:black;">50</span><span style="color:gray;">) <br>,&nbsp; </span><span style="color:black;">[DeployDatetime]&nbsp;&nbsp;&nbsp; SMALLDATETIME <br></span><span style="color:gray;">,&nbsp; </span><span style="color:blue;">CONSTRAINT&nbsp; </span><span style="color:black;">PK_dboDeployLog </span><span style="color:blue;">PRIMARY KEY </span><span style="color:gray;">(</span><span style="color:black;">[DeployDatetime]</span><span style="color:gray;">) <br>)</span></code> </p> </blockquote> <p>In my Post-Deployment script I will use:</p> <blockquote> <p><code style="font-size:12px;"><span style="color:blue;">INSERT </span><span style="color:black;">[dbo].[DeployLog]</span><span style="color:gray;">(</span><span style="color:black;">[BuildId]</span><span style="color:gray;">,</span><span style="color:black;">[DeployDatetime]</span><span style="color:gray;">) <br></span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'$(BuildId)'</span><span style="color:gray;">,</span><span style="color:magenta;">GETDATE</span><span style="color:gray;">());</span></code> </p> </blockquote> <p>to insert a row into that table during every deployment. <font color="#ff0000">$(BuildId)</font> is a variable defined in the .sqlcmdvars file of my project:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0ECE0EDD.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_0D8975FE.png" width="293" height="216"></a></p> </blockquote> <p>Here is what we see inside that file:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5E578A66.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_64322DFF.png" width="526" height="219"></a></p> </blockquote> <p>The <font color="#ff0000">$(BuildId)</font> variable has been defined with a default value of UNKNOWN and hence subsequent deployments from Visual Studio will result in the following:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5BCE58A8.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_0F964EFA.png" width="513" height="284"></a></p> </blockquote> <p>On first glance that might not seem particularly useful however it comes into its own if you are doing CI deployments (see recommendation #1) because each build in a CI environment will result in a new build identifier. The following command-line call to vsdbcmd.exe is how deployments are generally done using datadude, note the presence of the <b>/p:BuildId</b> switch:</p> <blockquote> <p>..\Tools\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:"Data Source=.;Integrated Security=True;Pooling=False" <b>/p:BuildId="some-value"</b> /DeployToDatabase:+ /ManifestFile:.\FinanceDB\sql\release\FinanceDB.deploymanifest</p> </blockquote> <p>Your CI tool should be able to replace “some-value” with an identifier for the current build (that’s outside the scope of this blog post but any CI tool worth its salt will be able to do this) – when the deployment executes that value will then make its way into your [dbo].[DeployLog] table and you will have a self-maintaining history of all the deployments (datetime &amp; build identifier) that have been made to your database.</p> <blockquote> <p><font size="4">Recommendation #4: Maintain an automated history of your deployments</font></p> </blockquote> <h2>Use Schema View</h2> <p>It is natural to navigate through all of the objects in your database project using Solution Explorer however datadude provides a better mechanism for doing just that – the Schema View window.</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_6E36A95D.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_781B9AC8.png" width="250" height="304"></a></p> </blockquote> <p>Schema View provides a logical view of all the objects defined in your database project regardless of which file they may be defined in. That is very useful for many reasons, not least because it makes it easy to locate whichever object you are after – that’s advantageous if multiple objects are defined in the same file. Moreover if some files have property BuildAction=”Not In Build” (see later) they won’t show up in Schema View (this is a good thing by the way). Schema View is also the place that operations such as refactoring and dependency analysis are launched from.</p> <p>Some people think that it is important that the name of each file in a datadude project should accurately reflect the object defined within. I disagree; object renames mean that maintaining the filenames becomes laborious and having the Schema View means you never have to use the filenames to navigate your project anyway.</p> <p>One final reason to use Schema View is the External Elements button:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_04A947E5.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6349A248.png" width="356" height="152"></a></p> </blockquote> <p>Toggling this button on means that objects in referenced projects show up in the project that they are referenced from (this is particularly useful if you are using Composite Projects). Note in the following screenshot how the object [dbo].[t1] in project Database2 appears in the [dbo] schema of Database3 – that’s because Database3 has a reference to Database2.</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_62050969.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_47586D50.png" width="243" height="439"></a></p> </blockquote> <p>For those reasons my fifth recommendation is:</p> <blockquote> <p><font size="4">Recommendation #5: Use Schema View in preference to Solution Explorer</font></p> </blockquote> <p>You will still need Solution Explorer to navigate files that do not contain database objects (e.g. Post-Deployment scripts) but ordinarily you should spend most of your time interacting with Schema View.</p> <h2>Make liberal use of PRINT statements in Pre/Post-Deployment Scripts</h2> <p>When you deploy a datadude project datadude will take care of telling you what it is up to. For example, the following screenshot shows the output from deploying the already discussed [dbo].[DeplogLog]:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_11074541.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_68F49621.png" width="479" height="117"></a></p> </blockquote> <p>Of course it only does this for objects that it knows about and that doesn’t include anything in your Pre or Post \deployment scripts so you need to take responsibility for outputting pertinent information from those scripts. Here I have amended the script that inserts into [dbo].[DeployLog]:</p> <blockquote> <p><code style="font-size:12px;"><span style="color:blue;">SET NOCOUNT ON</span><span style="color:gray;">; <br></span><span style="color:blue;">INSERT </span><span style="color:black;">[dbo].[DeployLog]</span><span style="color:gray;">(</span><span style="color:black;">[BuildId]</span><span style="color:gray;">,</span><span style="color:black;">[DeployDatetime]</span><span style="color:gray;">) <br></span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'$(BuildId)'</span><span style="color:gray;">,</span><span style="color:magenta;">GETDATE</span><span style="color:gray;">()); <br></span><span style="color:blue;">PRINT </span><span style="color:magenta;">CAST</span><span style="color:gray;">(</span><span>@@ROWCOUNT </span><span style="color:blue;">as NVARCHAR</span><span style="color:gray;">(</span><span style="color:black;">5</span><span style="color:gray;">)) + </span><span style="color:red;">N'rows inserted into [dbo].[DeployLog], BuildId=$(BuildId)'</span><span style="color:gray;">;</span></code></p> </blockquote> <p>This gives us much more useful output:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_159D4FFB.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_46BC8A9B.png" width="475" height="122"></a></p> </blockquote> <p>Adding PRINT statements to your Pre &amp; Post Deployment scripts is so easy it really is a no-brainer and hence my next recommendation is:</p> <blockquote> <p><font size="4">Recommendation #6: Any action in a Pre or Post-Deployment Script should use PRINT to state what has been done</font></p> </blockquote> <h2>Output variable values in your Pre-Deployment script</h2> <p>This is in the same vein as the previous bullet-point – output as much information as is possible. In this case we’re talking about outputting the values of all variables that are stored in the .sqlcmdvars file; first, a reminder of what’s in that file:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_73654474.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5924DB50.png" width="526" height="219"></a></p> </blockquote> <p>Here is the contents of my amended Pre-Deployment Script:</p> <blockquote> <p><code style="font-size:12px;"><span style="color:blue;">PRINT </span><span style="color:red;">'DefaultDataPath=$(DefaultDataPath)'</span><span style="color:gray;">; <br></span><span style="color:blue;">PRINT </span><span style="color:red;">'DatabaseName=$(DatabaseName)'</span><span style="color:gray;">; <br></span><span style="color:blue;">PRINT </span><span style="color:red;">'DefaultLogPath=$(DefaultLogPath)'</span><span style="color:gray;">; <br></span><span style="color:blue;">PRINT </span><span style="color:red;">'BuildId=$(BuildId)'</span><span style="color:gray;">;</span></code> </p> </blockquote> <p>And the resultant output:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_22D3B341.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_3D9FD94D.png" width="568" height="119"></a></p> </blockquote> <p>This is the sort of simple amendment that will pay off in spades later in your project (especially if you are supplying many values from the command-line) and again, its so easy to do it there really is no reason not to. Just remember to update your Pre-Deployment script whenever you add new variables to .sqlcmdvars.</p> <blockquote> <p><font size="4">Recommendation #7: Output the value of all variables in your Pre-Deployment script</font></p> </blockquote> <h2>One Object Per File</h2> <p>Datadude doesn’t restrict what can go in a file, for example the following file, “t.table.sql”, defines three objects; a table, a primary key and a view:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_75721D70.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_1B67CDC7.png" width="352" height="248"></a></p> </blockquote> <p>Even though they’re all defined in the same file they show up in Schema View separately (one of the aforementioned benefits of using Schema View):</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_6C35E22F.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_3D03F698.png" width="314" height="355"></a></p> </blockquote> <p>That said, just because you can doesn’t mean that you should. I prefer to go for one object per file for the simple reason that its easier to track the history of an object via Source Control. Moreover, if an object is no longer required then it is a simple change to just remove the file containing that object from the build (see “Don’t delete anything from your project” later) as opposed to editing a file to remove all traces of an object.</p> <blockquote> <p><font size="4">Recommendation #8: Each database object should be defined in a dedicated file</font></p> </blockquote> <h2>Time your Pre and Post Deployment Scripts</h2> <p>Its always useful to know where time is spent when doing deployments, in my experience the majority of time spent is in the Post-Deployment script (your mileage may vary of course). An easy win is to output the time taken to run your Pre and Post Deployment scripts. Adapt your Pre-Deployment script so that it looks something like this:</p> <blockquote> <p><code style="font-size:12px;"><span style="color:blue;">DECLARE </span><span>@vPreDeploymentStartTime </span><span style="color:blue;">DATETIME = </span><span style="color:magenta;">GETDATE</span><span style="color:gray;">(); <br></span><span style="color:blue;">PRINT </span><span style="color:red;">'****************Begin Pre-Deployment script at ' </span><span style="color:gray;">+</span><span style="color:magenta;">CONVERT</span><span style="color:gray;">(</span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span><span style="color:black;">30</span><span style="color:gray;">),</span><span style="color:magenta;">GETDATE</span><span style="color:gray;">(),</span><span style="color:black;">120</span><span style="color:gray;">) + </span><span style="color:red;">'***********************'</span><span style="color:gray;">; <br></span><span style="color:gray;"> <br></span><span style="color:green;">/*Call other scripts from here using SQLCMD's :r syntax <br>Example:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :r .\myfile.sql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>*/ <br> <br></span><span style="color:blue;">PRINT </span><span style="color:red;">'Pre-Deployment duration = ' </span><span style="color:gray;">+ </span><span style="color:magenta;">CONVERT</span><span style="color:gray;">(</span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span><span style="color:black;">5</span><span style="color:gray;">),</span><span style="color:magenta;">DATEDIFF</span><span style="color:gray;">(</span><span style="color:black;">ss</span><span style="color:gray;">,</span><span>@vPreDeploymentStartTime</span><span style="color:gray;">,</span><span style="color:magenta;">GETDATE</span><span style="color:gray;">())) + </span><span style="color:red;">' seconds'</span><span style="color:gray;">; <br></span><span style="color:blue;">PRINT </span><span style="color:red;">'****************End Pre-Deployment script at ' </span><span style="color:gray;">+</span><span style="color:magenta;">CONVERT</span><span style="color:gray;">(</span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span><span style="color:black;">30</span><span style="color:gray;">),</span><span style="color:magenta;">GETDATE</span><span style="color:gray;">(),</span><span style="color:black;">120</span><span style="color:gray;">) + </span><span style="color:red;">'***********************'</span><span style="color:gray;">;</span></code></p> </blockquote> <p>then do similar for your Post-Deployment script. When you deploy your output will include the following:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_3BBF5DB9.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_217EF495.png" width="593" height="201"></a></p> </blockquote> <p>Note the lines:</p> <blockquote> <p><font face="Consolas">&nbsp;&nbsp;&nbsp; ****************Begin Pre-Deployment script at 2011-12-31 20:00:34*********************** <br>&nbsp;&nbsp;&nbsp; Pre-Deployment duration = 0 seconds <br>&nbsp;&nbsp;&nbsp; ****************End Pre-Deployment script at 2011-12-31 20:00:34*********************** <br>&nbsp;&nbsp;&nbsp; </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp; ****************Begin Post-Deployment script at 2011-12-31 20:00:34*********************** <br>&nbsp;&nbsp;&nbsp; Post-Deployment duration = 0 seconds <br>&nbsp;&nbsp;&nbsp; ****************End Post-Deployment script at 2011-12-31 20:00:34***********************</font></p> </blockquote> <p>In this particular case its not all that useful to know that the deployment took 0 seconds but if and when your deployments snowball to many minutes it will be useful to know how long your scripts are taking at which point you can investigate further by timing each individual step in your Pre and Post Deployment scripts.</p> <blockquote> <p><font size="4">Recommendation #9: Time your deployments and output the timings</font></p> </blockquote> <h2>Use sqlcmdvars and the command-line as much as possible</h2> <p>Hardcoding any value into a piece of code is a fraught practise; you should assume that values previously thought to be constant may not be so in the future. You can protect yourself from future changes by storing all literal values as variables in your .sqlcmdvars file. Sure, you can supply default values for those variables but you have the added advantage that they can be overridden from the command-line when deploying using <a href="http://msdn.microsoft.com/en-us/library/dd193283.aspx" target="_blank">vsdbcmd.exe</a>. Moreover, if you have values that are hardcoded in multiple places in your code then specifying those values in .sqlcmdvars ensures that your code adheres to the principle of <a href="http://en.wikipedia.org/wiki/Don%27t_repeat_yourself" target="_blank">DRY</a>. Lastly, if values are stored in the .sqlcmdvars file then you can output them at deploy time (see recommendation #7).</p> <blockquote> <p><font size="4">Recommendation #10: All literal values should be stored in your .sqlcmdvars file</font></p> </blockquote> <h2>Every developer gets their own development database</h2> <p>In most SQL Server development shops that I’ve been on all developers work against a single centralised development database. To me this is an antiquated way of working because its possible that work one person is doing can conflict with that of someone else, I find it much better for every developer to work in isolation and then use the CI deployment to check that one’s code is not in conflict with anyone else’s. Datadude supports (nay encourages) this way of working with the notion of an Isolated Development Environment:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_79001280.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_0CACFC15.png" width="541" height="172"></a></p> </blockquote> <p>Every developer should configure their isolated development environment which, typically, would be their local instance. And so to my next recommendation:</p> <blockquote> <p><font size="4">Recommendation #11: Every developer should use the Isolated Dev Environment settings in order to author their code</font></p> </blockquote> <p>Incidentally, if every developer has their own development database and you are following my earlier recommendation to use a [DeployLog] table then you can track how often a developer is bothering to deploy and test their code. On a recent project we used this evidence in a (ahem) <i>discussion</i> with a developer who tried to convince us that he was testing his code sufficiently even though he was repeatedly causing the CI deployment to fail.</p> <h2>Don’t delete anything from your project</h2> <p>When projects are no longer required in your database then intuitively it makes sense to remove the file containing that object from the datadude project, I would however like to suggest a different approach. Rather than removing a file just change the Build property to “Not in Build”:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0B686336.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_114306CF.png" width="330" height="293"></a></p> </blockquote> <p>This has the advantage that your project maintains some semblance of history of what objects have been removed from your database – that can be useful to anyone inheriting your code in the future.</p> <blockquote> <p><font size="4">Recommendation #12: Use “Not in Build” to remove an object from your database</font></p> </blockquote> <h2>Build and Deploy your datadude projects outside of Visual Studio</h2> <p>Building and deploying your datadude projects within Visual Studio can become a real time hog; in my experience its not unusual for deployments to take many minutes and your Visual Studio environment will be unavailable for further development work during that time. For that reason I recommend investing some time in writing some msbuild scripts that will build and deploy your project(s) from the command-line. Here are some examples that you can adapt for your own use, firstly a script to build a solution:</p> <div style="font-size:10pt;"> <div style="padding:2px 5px;overflow:auto;white-space:nowrap;"><span>&lt;?</span><span>xml</span><span> </span><span>version</span><span>=</span>"<span>1.0</span>"<span> </span><span>encoding</span><span>=</span>"<span>utf-8</span>"<span>?&gt;</span> <br><span>&lt;!--</span> <span>Execute using:</span> <br><span>msbuild SolutionBuild.proj</span> <br><span>--&gt;</span> <br><span>&lt;</span><span>Project</span><span>&nbsp; </span><span>xmlns</span><span>=</span>"<span>http://schemas.microsoft.com/developer/msbuild/2003</span>" <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>DefaultTargets</span><span>=</span>"<span>Build</span>"<span>&gt;</span> <br><span>&nbsp; &lt;!--</span> <span>Notes:</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; When doing .net development Visual Studio Configurations are particularly useful because they can affect<br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>how the code is executed (i.e. under the Debug configuration</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; debug symbols can be used to step through the code (something like that anyway - I don't know too much about that <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>stuff).</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; In DBPro, Configurations are less relevant because there is no such thing as debugging symbols. Nonetheless, they <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>can still be useful</span> <span>for times when you want to do different things (e.g. you might want to run Code Analysis in <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>a debug situation but not in a release</span> <span>situation. There is a useful thread on this here: <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>"Debug vs Release" http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/a0ec0dc0-a907-45ba-a2ea-d2f0175261a7</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp; </span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Note that Visual Studio Configurations should not be used to maintain different settings per environment. <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>The correct way to do that is to maintain</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; seperate .sqlcmdvars files per environment and then choose which one to use at deployment time when using</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vsdbcmd.exe (use syntax "/p:SqlCommandVariablesFile=$(ProjectName)_$(Environment).sqlcmdvars")</span> <br><span>&nbsp; </span><span>--&gt;</span> <br><span>&nbsp; &lt;</span> <span>ItemGroup</span> <span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;!--</span> <span>List all the configurations here that you want to build</span> <span>--&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>Config</span><span> </span><span>Include</span><span>=</span>"<span>Debug</span>"<span> /&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>Config</span><span> </span><span>Include</span><span>=</span>"<span>Release</span>"<span> /&gt;</span> <br><span>&nbsp; &lt;/</span> <span>ItemGroup</span> <span>&gt;</span> <br><span>&nbsp; &lt;</span><span>Target</span><span> </span><span>Name</span><span>=</span>"<span>Build</span>"<span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>Message</span><span> </span><span>Text</span><span>=</span>"<span>Building %(Config.Identity) configuration...</span>"<span>/&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>MSBuild</span><span> </span><span>Projects</span><span>=</span>"<span>.\Lloyds.UKTax.DB.sln</span>"<span> </span><span>Properties</span><span>=</span>"<span>Configuration=%(Config.Identity)</span>"<span> /&gt;</span> <br><span>&nbsp; &lt;/</span> <span>Target</span> <span>&gt;</span> <br><span>&lt;/</span> <span>Project</span> <span>&gt;</span> </div> </div> <p>and secondly a script that will deploy a datadude project:</p> <div style="font-size:10pt;"> <div style="padding:2px 5px;overflow:auto;white-space:nowrap;"><span>&lt;?</span><span>xml</span><span> </span><span>version</span><span>=</span>"<span>1.0</span>"<span> </span><span>encoding</span><span>=</span>"<span>utf-8</span>"<span>?&gt;</span> <br><span>&lt;!--</span> <span>Execute using:</span> <br><span>msbuild SolutionDeploy.proj /Target:Deploy</span> <br><span>--&gt;</span> <br><span>&lt;</span><span>Project</span><span>&nbsp; </span><span>xmlns</span><span>=</span>"<span>http://schemas.microsoft.com/developer/msbuild/2003</span>" <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>DefaultTargets</span><span>=</span>"<span>Build;Deploy</span>"<span>&gt;</span> <br><span>&nbsp; &lt;</span> <span>PropertyGroup</span> <span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;!--</span> <span>At time of writing I don't see a reason for anything else to be used but that may change in the future hence why this <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>is a property and hence can be overriden.</span> <span>--&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>Configuration</span><span>&gt;</span>Debug<span>&lt;/</span><span>Configuration</span><span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>DevServer</span><span>&gt;</span>Data Source=GBS0039182\GLDDEV01;Integrated Security=True;Pooling=False<span>&lt;/</span><span>DevServer</span><span>&gt;</span> <br><span>&nbsp; &lt;/</span> <span>PropertyGroup</span> <span>&gt;</span> <br><span>&nbsp; </span> <br><span>&nbsp; &lt;</span> <span>ItemGroup</span> <span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>ProjectToBuild</span><span> </span><span>Include</span><span>=</span>"<span>SolutionBuild.proj</span>"<span> /&gt;</span> <br><span>&nbsp; &lt;/</span> <span>ItemGroup</span> <span>&gt;</span> <br> <br><span>&nbsp; &lt;!--</span> <span>Notes:</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Add a &lt;DbProj&gt; item for every database project (.dbproj) that needs to be deployed. They will get deployed in the <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>order that they are listed</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; thus it is your responsibility to make sure they are listed in the correct order (respecting dependency order).</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; %Identity is a metadata reference. It refers to the name of the item (i.e. Include="The bit that goes here is the <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>identity")</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Note also that whatever you put for Include is important. Include="dev_thomsonj" means the project will only get <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>deployed if the</span> <span>deployment is being executed by username=dev_thomsonj</span> <span>--&gt;</span> <br><span>&nbsp; &lt;</span> <span>ItemGroup</span> <span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>DbProj</span><span> </span><span>Include</span><span>=</span>"<span>username</span>"<span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span>DbName</span><span>&gt;</span>MyDB<span>&lt;/</span><span>DbName</span><span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span>ProjectName</span><span>&gt;</span>MySoln.MyDB<span>&lt;/</span><span>ProjectName</span><span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span>OutputPath</span><span>&gt;\</span>%(ProjectName)\sql\$(Configuration)\\cf1 &lt;/<span>OutputPath</span><span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span>DeployConnStr</span><span>&gt;</span>Data Source=localhost;Integrated Security=True;Pooling=False<span>&lt;/</span><span>DeployConnStr</span><span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;/</span> <span>DbProj</span> <span>&gt;</span> <br><span>&nbsp; &lt;/</span> <span>ItemGroup</span> <span>&gt;</span> <br> <br><span>&nbsp; &lt;</span><span>Target</span><span> </span><span>Name</span><span>=</span>"<span>Build</span>"<span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>MSBuild</span><span> </span><span>Projects</span><span>=</span>"<span>@(ProjectToBuild)</span>"<span> /&gt;</span> <br><span>&nbsp; &lt;/</span> <span>Target</span> <span>&gt;</span> <br><span>&nbsp; </span> <br><span>&nbsp; &lt;</span><span>Target</span><span> </span><span>Name</span><span>=</span>"<span>Deploy</span>"<span>&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;!--</span> <span>Notes:</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 09 is the hex code for TAB, hence all of the %09 references that you can see. See http://asciitable.com/ <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>for more details.</span> <br><span>&nbsp;&nbsp;&nbsp; </span> <span>--&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>Message</span><span> </span><span>Text</span><span>=</span>"<span>USERNAME=$(USERNAME)</span>"<span> /&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>Message</span><span> </span><span>Condition</span><span>=</span>"<span>'%(DbProj.Identity)'==$(USERNAME)</span>"<span> </span><span>Text</span><span>=</span>"<span>Deploying:</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp; Project%09%09%09:&nbsp; %(DbProj.ProjectName)&nbsp;&nbsp; </span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp; DbName%09%09%09:&nbsp; %(DbProj.DbName)</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp; From OutputPath%09%09:&nbsp; %(DbProj.OutputPath)</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp; To ConnStr%09%09:&nbsp; %(DbProj.DeployConnStr)</span> <br><span>&nbsp;&nbsp;&nbsp;&nbsp; By%09%09%09:&nbsp; %(DbProj.Identity)</span>" <br><span>&nbsp;&nbsp;&nbsp;&nbsp; /&gt;</span> <br><span>&nbsp;&nbsp;&nbsp; &lt;</span><span>Exec</span><span> </span><span>Condition</span><span>=</span>"<span>'%(DbProj.Identity)'==$(USERNAME)</span>"<span> </span><span>Command</span><span>=</span>"<span>&amp;quot;</span><span>$(VSINSTALLDIR)\vstsdb\deploy\vsdbcmd.exe</span><span>&amp;quot;</span><span> <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>/Action:Deploy /ConnectionString:</span><span>&amp;quot;</span><span>%(DbProj.DeployConnStr)</span><span>&amp;quot;</span><span> /DeployToDatabase+ <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>/manifest:</span><span>&amp;quot;</span><span>.%(DbProj.OutputPath)%(DbProj.ProjectName).deploymanifest</span><span>&amp;quot;</span><span> /p:TargetDatabase=%(DbProj.DbName) <br></span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span>/p:Build=</span><span>&amp;quot;</span><span>from cmd line</span><span>&amp;quot;</span>"<span> /&gt;</span> <br><span>&nbsp; &lt;/</span> <span>Target</span> <span>&gt;</span> <br><span>&lt;/</span> <span>Project</span> <span>&gt;</span> </div> </div> <p>Writing these scripts may appear to be laborious but they’ll save you heaps of time in the long run.</p> <blockquote> <p><font size="4">Recommendation #13: Build and deploy to your development sandbox using scripts</font></p> </blockquote> <p><i>UPDATE: Upon reading this blog post Mordechai Danielov wrote a follow-up in which he published a useful script that builds a series of projects using Powershell. Its at </i><a href="http://bitwisemnm.com/2012/01/03/building-your-database-solutions-outside-of-visual-studio/" target="_blank"><i>building your database solutions outside of Visual Studio</i></a><i>.</i></p> <h1>Useful links</h1> <p>Over the years I’ve collected some links to MSDN articles that have proved invaluable:</p> <ul> <li><a href="http://msdn.microsoft.com/en-us/library/dd193409.aspx" target="_blank">Build and Deploy Databases to an Isolated Development Environment</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/aa833165.aspx#CommunityContent" target="_blank">An Overview of Database Build and Deployment</a> </li> <li><a href="http://msdn.microsoft.com/en-us/magazine/dvdarchive/cc164243.aspx" target="_blank">Apply Test-Driven Development to your Database Projects</a> </li> <li><a href="http://msdn.microsoft.com/en-us/library/dd193415.aspx" target="_blank">Walkthrough: Partition a Database Project by Using Composite Projects</a> </li> <li><a href="http://blogs.msdn.com/b/bahill/archive/2009/03/23/right-sizing-the-master-dbschema-file-for-better-design-time-performance.aspx" target="_blank">Right sizing the master.dbschema file for better design time performance</a> </li> <li><a href="http://msdn.microsoft.com/en-us/magazine/dd483214.aspx#id0100036" target="_blank">Referencing for Shared Server-Level Objects</a> </li><li><span style="text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;float:none;white-space:normal;orphans:2;widows:2;font-size-adjust:none;font-stretch:normal;-webkit-text-size-adjust:auto;-webkit-text-stroke-width:0px;"><a href="http://msdn.microsoft.com/en-us/library/dd193258.aspx" target="_blank">How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE</a></span></li> </ul> <h1>Datadude bugs</h1> <p>Like any substantial piece of software datadude is not without bugs. Many of the issues I have found are concerned with the datadude interpreter not correctly parsing T-SQL code, here’s a list of some bugs that I have found down the years:</p> <ul> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/688259/refactor-rename-in-schema-view-crashes-visual-studio" target="_blank">Under certain circumstances a Refactor-&gt;Rename operation can crash Visual Studio</a> (followed up <a href="https://connect.microsoft.com/VisualStudio/feedback/details/705676/would-like-feedback-on-a-bug-that-has-been-closed-as-wont-fix" target="_blank">here</a>) </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/674391/datadude-valid-merge-syntax-causes-warnings#details" target="_blank">Valid T-SQL MERGE syntax can cause false warnings</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/668651/datadude-refactoring-applies-the-wrong-edits-to-unit-tests" target="_blank">Refactor-&gt;Rename doesn’t work properly for database unit tests</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/657580/datadude-deploy-failure-something-to-do-with-triggers#details" target="_blank">Removal of triggers from your datadude project may, under certain circumstances, cause a failed deployment</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/635058/datadude-derived-table-with-the-same-alias-as-one-of-the-columns-in-that-derived-table-incorrectly-causes-a-warning#details" target="_blank">Rare naming circumstance can cause false warnings</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/624876/datadude-t-sql-parser-throws-warnings-for-valid-t-sql#details" target="_blank">Valid function definition containing a CTE can cause false warnings</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/606656/datadude-datadude-attempts-to-deploy-a-default-constraint-before-deploying-the-function-referenced-by-that-constraint" target="_blank">A default constraint is attempted to be deployed before a function referenced by that constraint – hence an error occurs</a> (confirmed as fixed in VS2010 SP1) </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/605544/datadude-failure-to-recognise-operand-type-clash-when-using-table-types#details" target="_blank">Datadude doesn’t catch a type mismatch between a table-valued-parameter and a parameter passed to it</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/594309/datadude-datetime2-data-generator-doesnt-generate-unique-values-when-the-column-is-part-of-a-composite-key" target="_blank">DateTime2 Data generator doesn't generate unique values when the column is part of a composite key</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/592932/datadude-certain-db-unit-test-class-names-can-throw-errors-when-the-class-is-created" target="_blank">Unit test class files cannot contain periods</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/586040/datadude-valid-create-view-syntax-throws-errors#details" target="_blank">Valid CREATE VIEW definition throws errors</a> </li> <li><a href="https://connect.microsoft.com/VisualStudio/feedback/details/573824/datadude-unexplained-warnings-from-dbproj-project" target="_blank">MERGE syntax in a stored procedure creates false warnings</a> </li> </ul> <p>Some of these bugs were reported a long time ago and may well have been fixed in later service packs.</p> <h1>Previous datadude blog posts</h1> <p>I have <a href="http://sqlblog.com/blogs/jamie_thomson/archive/tags/Datadude/default.aspx" target="_blank">blogged on datadude</a> quite a bit in the past:</p> <ul> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2011/11/21/workaround-for-datadude-deployment-bug.aspx">Workaround for datadude deployment bug – NullReferenceException</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2011/02/10/building-a-database-installer-with-wix-datadude-and-visual-studio-2010.aspx">Building a database installer with WiX, datadude and Visual Studio 2010</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2011/01/19/generate-multiple-sqlcmdvars-vars-in-your-database-projects.aspx">Generate multiple SqlCmdVars files in your database projects</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2011/01/17/querying-visual-studio-project-files.aspx">Querying Visual Studio project files using T-SQL and Powershell</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/10/19/maintaining-version-history-in-your-database-using-visual-studio-2010.aspx">Maintaining version history in your database using Visual Studio 2010</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/10/06/enforcing-naming-conventions-using-database-unit-testing.aspx">Enforcing naming conventions using database unit testing</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/09/18/experiences-from-writing-sp-cascadingdataviewer-db-unit-testing-and-code-distribution.aspx">Experiences from writing sp_CascadingDataViewer – DB unit testing and code distribution</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/09/14/some-thoughts-on-visual-studio-database-references-and-how-they-should-be-used-for-sql-server-bi.aspx">Some thoughts on Visual Studio database references and how they should be used for SQL Server BI</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/08/23/microsoft-publish-visual-studio-2010-database-project-guidance.aspx">Microsoft publish Visual Studio 2010 Database Project Guidance</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/08/20/setting-up-database-unit-testing-as-part-of-a-continuous-integration-build-process-vs2010-db-tools-datadude.aspx">Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude]</a> </li> <li><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/21/a-strategy-for-managing-security-for-different-environments-using-the-database-development-tools-in-visual-studio-2010.aspx">A strategy for managing security for different environments using the Database Development Tools in Visual Studio 2010</a> </li> </ul> <h1>Summing up</h1> <p>This has been an inordinately large blog post so if you’ve read this far – well done. For easy reference, here are all the recommendations that I have made:</p> <ol> <li>Use Source Control and implement a Continuous Integration deployment </li> <li>Turn on Code Analysis </li> <li>When running your deployment in a test environment, run it more than once </li> <li>Maintain an automated history of your deployments </li> <li>Use Schema View in preference to Solution Explorer </li> <li>Any action in a Pre or Post-Deployment Script should use PRINT to state what has been done </li> <li>Output the value of all variables in your Pre-Deployment script </li> <li>Each database object should be defined in a dedicated file </li> <li>Time your deployments and output the timings </li> <li>All literal values should be stored in your .sqlcmdvars file </li> <li>Every developer should use the Isolated Dev Environment settings in order to author their code </li> <li>Use “Not in Build” to remove an object from your database </li> <li>Build and deploy to your development sandbox using scripts </li> </ol> <p>I really hope this proves useful because its taken a good long while to get it published <img class="wlEmoticon wlEmoticon-smile" alt="Smile" src="http://sqlblog.com/blogs/jamie_thomson/wlEmoticon-smile_68C424BA.png"> If you have any feedback then please let me know in the comments.</p> <p>Thanks for reading!</p> <p><a href="http://twitter.com/jamiet">@jamiet</a></p> <p><i>* When I started writing this blog post the first sentence was “Over the past six months I have worked on two separate projects for customers that wanted to make use of Visual Studio 2010 Database projects to manage their database schema.” as opposed to what it is now: “Over the past eighteen months I have worked on four separate projects…” Yes, that’s how long its taken to write it! <img class="wlEmoticon wlEmoticon-smile" alt="Smile" src="http://sqlblog.com/blogs/jamie_thomson/wlEmoticon-smile_68C424BA.png"></i></p> <p><i></i></p> <h1>Appendix – An example CI configuration</h1> <p><i>As stated above, an earlier draft of this blog post included full details of the CI configuration from one of the projects that I have worked on. Although it may repeat some of what has already been said I have included that text below.</i></p> <p><i>==============================================================</i></p> <h2>Introduction</h2> <p>This project has invested heavily in using a Continuous Integration (CI) approach to development. What that means, succinctly, is that whenever someone checks-in some code to our source control system an automated build process is kicked off that constructs our entire system from scratch on a dedicated server. CI is not a new concept but it is fairly rare that anyone applies the same rigour to their database objects as they do to so-called “application code” (e.g. the stuff written in .Net code) and on this project we have made a conscious decision to properly build our databases as part of the CI build.</p> <p>Datadude employs a <i>declarative</i> approach to database development. In other words you define what you want database schema to look like and datadude will work out what it needs to do to your target in order to turn it into what you have defined. What this means in practice is that you only ever write CREATE … DDL statements rather than IF &lt;object-exists&gt; THEN ALTER …ELSE CREATE … statements which is what you may have done in the past.</p> <p>Here’s our CI environment setup:</p> <ul> <li>SubVersion (SVN) is being used for source control </li> <li><a href="http://hudson-ci.org/">Hudson</a>, an open source CI server, is being used to orchestrate our whole CI environment. It basically monitors our SVN repository and when it spots a checked-in file, kicks off the CI build </li> <li>Our CI scripts (the stuff that actually does the work) are written using <a href="http://msdn.microsoft.com/en-us/library/0k6kkbsd.aspx">msbuild</a> </li> <li>We have 2 msbuild scripts: <ul> <li>Build.proj which is responsible for: <ul> <li>Compiling all our .Net website code </li> <li>Building/Compiling our datadude projects&nbsp; (every datadude project file is a msbuild-compliant script) </li> </ul> </li> <li>Deploy.proj which is responsible for: <ul> <li>Restoring latest database backups from our production environment into our CI environment </li> <li>Deploying our built datadude projects on top of those restored backups </li> <li>Build a folder structure to hold all the artefacts that get deployed </li> <li>Creating folder shares </li> <li>Moving SSIS packages into folder structure </li> <li>Deploying SSRS reports to our SSRS server </li> <li>Deploy our Analysis Services cube definitions to our Analysis Server </li> </ul> </li> </ul> </li> <li>Both Build.proj and Deploy.proj get executed by our CI build </li> </ul> <h2>Building datadude projects</h2> <p>Datadude makes it very easy to build datadude projects in a CI environment because they are already msbuild-compliant; its simply a call to the MSBuild task, passing in the location of the solution file as an argument. We use the Release configuration (although there is no particular reason for you to do the same – purely your choice):</p> <div style="font-size:10pt;"> <div style="padding:2px 5px;overflow:auto;"><span>&lt;</span><span>Target</span><span> </span><span>Name</span><span>=</span>"<span>Database</span>"<span>&gt;</span> <br><span>&nbsp; &lt;!--</span><span>Build database projects and copy output to staging </span><span>--&gt;</span> <br><span>&nbsp; &lt;</span><span>Message</span><span> </span><span>Text</span><span>=</span>"<span>*****Building database solution</span>"<span> /&gt;</span> <br><span>&nbsp; &lt;</span><span>MSBuild</span><span> </span><span>Projects</span><span>=</span>"<span>..\src\SQL\DatabaseSolution.sln</span>"<span> </span><span>Properties</span><span>=</span>"<span>Configuration=Release</span>"<span> /&gt;</span> <br><span>&lt;/</span><span>Target</span><span>&gt;</span></div> </div> <p>That’s it! The output from a datadude build includes a number of files but the most important one is a .dbschema file which is an XML representation of all the objects in your database.</p> <h2>Deploying the output from a built datadude project</h2> <p>This is a little more difficult. We *<b>could</b>* simply use the MSBuild task to call our deployment script as we do for build script (see above) but the problem with that is that there are many pre-requisites (including datadude itself) and we don’t want to install Visual Studio and all the assorted paraphernalia onto our various environments. Instead we chose to make use of a command-line tool called <a href="http://msdn.microsoft.com/en-us/library/dd193283.aspx">VSDBCMD.exe</a> to deploy datadude projects. VSDBCMD does basically the same job as what happens if you were to right-click on a datadude project in Visual Studio and select “Deploy” i.e. It compares the output of a build (A) to the target database (B) and works out what it needs to do to make B look like A. It then produces a .sql script that will actually make those requisite changes, then goes and executes it.</p> <p>The difficulty comes in VSDBCMD.exe having its own list of file dependencies that are listed at MSDN article <a href="http://msdn.microsoft.com/en-us/library/dd193258.aspx">How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE</a>, thankfully a much smaller list then if we were using the MSBuild task.</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_334B6295.png"><img style="border-width:0px;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_11EBBCF9.png" width="581" height="389"></a></p> </blockquote> <p>Some of those files, namely:</p> <ul> <li>Sqlceer35en.dll </li> <li>Sqlceme35.dll </li> <li>Sqlceqp35.dll </li> <li>Sqlcese35.dll </li> <li>System.Data.SqlServerCe.dll </li> </ul> <p>get installed with SQL Server CE. We bundle along the x86 &amp; x64 installers for SQL Server CE along with all the rest of our deployment artefacts and then, as part of Deploy.proj, install them like so:</p> <div style="font-size:10pt;"> <div style="padding:2px 5px;overflow:auto;"><span>&lt;</span><span>Exec</span><span> </span><span>Command</span><span>=</span>'<span>msiexec /passive /l* "$(SetupLogDirectory)\SSCERuntime_x86-ENU.log" /i "$(BuildDir)\Vendor\SSCERuntime_x86-ENU.msi"</span>'<span> /&gt;</span> <br><span>&lt;</span><span>Exec</span><span> </span><span>Condition</span><span>=</span>"<span>$(X64)</span>"<span> </span><span>Command</span><span>=</span>'<span>msiexec /passive /l* "$(SetupLogDirectory)\SSCERuntime_x64-ENU.log" /i "$(BuildDir)\Vendor\SSCERuntime_x64-ENU.msi"</span>'<span> /&gt;</span></div> </div> <p>That takes care of some of the dependencies but we still have to take care of:</p> <ul> <li>DatabaseSchemaProviders.Extensions.xml </li> <li>Microsoft.Data.Schema.dll </li> <li>Microsoft.Data.Schema.ScriptDom.dll </li> <li>Microsoft.Data.Schema.ScriptDom.Sql.dll </li> <li>Microsoft.Data.Schema.Sql.dll </li> <li>Microsoft.SqlServer.BatchParser.dll </li> </ul> <p>as well as the actual VSDBCMD.exe file itself. Quite simply we keep those files in SVN and then bundle them along with all our deployment artefacts (I won’t show you how we do that because its out of the scope of this post and besides if you’re at all proficient with msbuild then you’ll know how to do that and if you’re not, well, why are you reading this?)</p> <p><i>==============================================================</i></p><img src="http://sqlblog.com/aggbug.aspx?PostID=40711" width="1" height="1">Continuous IntegrationDatadudeDBProsql serverSSDTVisual Studio 2010Workaround for datadude deployment bug - NullReferenceExceptionhttp://sqlblog.com/blogs/jamie_thomson/archive/2011/11/21/workaround-for-datadude-deployment-bug.aspxMon, 21 Nov 2011 08:56:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:39903jamiet3http://sqlblog.com/blogs/jamie_thomson/comments/39903.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=39903http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=39903
<p>I have come across a bug in Visual Studio 2010 Database Projects (<a href="http://www.sqlservercentral.com/questions/tags/visual+studio/" target="_blank">aka datadude aka DPro aka Visual Studio Database Development Tools aka Visual Studio Team Edition for Database Professionals aka Juneau aka SQL Server Data Tools</a>) that other people may encounter so, for the purposes of googling, I'm writing this blog post about it. Through my own googling I discovered that a Connect bug had already been raised about it (<a href="https://connect.microsoft.com/VisualStudio/feedback/details/693158/vs2010-database-project-deploy-sqldeploytask-task-failed-unexpectedly-nullreferenceexception#details" target="_blank">VS2010 Database project deploy - “SqlDeployTask” task failed unexpectedly, NullReferenceException</a>), and coincidentally enough it was raised by my former colleague Tom Hunter (whom I have <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/25/sql-server-2008-sp1-cu-6-includes-small-changes-to-dtsx-files.aspx" target="_blank">mentioned here before as the superhuman Tom Hunter</a>) although it has not (at this time) received a reply from Microsoft. Tom provided a repro, namely that this syntactically valid function definition:</p>
<code style="font-size:12px;"><span style="color:blue;">CREATE FUNCTION </span><span style="color:black;">[dbo].[Function1]<br></span><span style="color:gray;">()<br></span><span style="color:blue;">RETURNS TABLE<br>AS<br>RETURN </span><span style="color:gray;">(<br>&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color:blue;">WITH </span><span style="color:black;">cte </span><span style="color:blue;">AS </span><span style="color:gray;">(<br>&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color:blue;">SELECT </span><span style="color:black;">1 </span><span style="color:blue;">AS </span><span style="color:black;">[c1]<br>&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color:blue;">FROM </span><span style="color:black;">[$(Database3)].[dbo].[Table1]<br>&nbsp;&nbsp; </span><span style="color:gray;">)<br>&nbsp;&nbsp; </span><span style="color:blue;">SELECT </span><span style="color:black;">1 </span><span style="color:blue;">AS </span><span style="color:black;">[c1]<br>&nbsp;&nbsp; </span><span style="color:blue;">FROM </span><span style="color:black;">cte<br></span><span style="color:gray;">) </span></code>
<p>would produce this nasty unhelpful error upon deployment:<br></p>
<font color="red">
<p>C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets(120,5): Error MSB4018: The "SqlDeployTask" task failed unexpectedly.<br><span style="font-weight:bold;">System.NullReferenceException: Object reference not set to an instance of an object</span>.<br>&nbsp;&nbsp; at Microsoft.Data.Schema.Sql.SchemaModel.SqlModelComparerBase.VariableSubstitution(SqlScriptProperty propertyValue, IDictionary`2 variables, Boolean&amp; isChanged)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.Sql.SchemaModel.SqlModelComparerBase.ArePropertiesEqual(IModelElement source, IModelElement target, ModelPropertyClass propertyClass, ModelComparerConfiguration configuration)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareProperties(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonChangeDefinition changes)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition&amp; changes)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareChildren(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareParentElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes, Boolean isComposing)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition&amp; changes)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareChildren(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareParentElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes, Boolean isComposing)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition&amp; changes)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareAllElementsForOneType(ModelElementClass type, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean compareOrphanedElements)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareStore(ModelStore source, ModelStore target, ModelComparerConfiguration configuration)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.Build.SchemaDeployment.CompareModels()<br>&nbsp;&nbsp; at Microsoft.Data.Schema.Build.SchemaDeployment.PrepareBuildPlan()<br>&nbsp;&nbsp; at Microsoft.Data.Schema.Build.SchemaDeployment.Execute(Boolean executeDeployment)<br>&nbsp;&nbsp; at Microsoft.Data.Schema.Build.SchemaDeployment.Execute()<br>&nbsp;&nbsp; at Microsoft.Data.Schema.Tasks.DBDeployTask.Execute()<br>&nbsp;&nbsp; at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()<br>&nbsp;&nbsp; at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggingContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean&amp; taskResult)<br>&nbsp;&nbsp; Done executing task "SqlDeployTask" -- FAILED.<br>&nbsp; Done building target "DspDeploy" in project "Lloyds.UKTax.DB.UKtax.dbproj" -- FAILED.<br>&nbsp;Done executing task "CallTarget" -- FAILED.<br>Done building target "DBDeploy" in project <br></p>
</font>
<p>It turns out there are a certain set of circumstances that need to be met for this error to occur:</p>
<ul>
<li>The object being deployed is an inline function&nbsp; (may also exist for multistatement and scalar functions - I haven't tested that)<br></li>
<li>That object includes SQLCMD variable references</li>
<li>The object has already been deployed successfully</li>
</ul>
<p>Just to reiterate that last bullet point, the error does not occur when you deploy the function for the first time, only on the subsequent deployment.</p>
<p>&nbsp;</p>
<p>Luckily I have a direct line into a guy on the development team so I fired off an email on Friday evening and today (Monday) I received a reply back telling me that there is a simple fix, one simply has to remove the parentheses that wrap the SQL statement. So, in the case of Tom's repro, the function definition simply needs to be changed to:</p>
<p>
<code style="font-size:12px;"><span style="color:blue;">CREATE FUNCTION </span><span style="color:black;">[dbo].[Function1]<br></span><span style="color:gray;">()<br></span><span style="color:blue;">RETURNS TABLE<br>AS<br>RETURN </span><span style="color:green;">--(<br>&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color:blue;">WITH </span><span style="color:black;">cte </span><span style="color:blue;">AS </span><span style="color:gray;">(<br>&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color:blue;">SELECT </span><span style="color:black;">1 </span><span style="color:blue;">AS </span><span style="color:black;">[c1]<br>&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color:blue;">FROM </span><span style="color:black;">[$(Database3)].[dbo].[Table1]<br>&nbsp;&nbsp; </span><span style="color:gray;">)<br>&nbsp;&nbsp; </span><span style="color:blue;">SELECT </span><span style="color:black;">1 </span><span style="color:blue;">AS </span><span style="color:black;">[c1]<br>&nbsp;&nbsp; </span><span style="color:blue;">FROM </span><span style="color:black;">cte<br></span><span style="color:green;">--) </span></code></p>
<p><i>I have commented out the offending parentheses rather than removing them just to emphasize the point.
</i></p>
<p>Thereafter the function will deploy fine. I tested this out on my own project this morning and can confirm that this fix does indeed work.</p>
<p>&nbsp;</p>
<p>I have been told that the bug CAN be reproduced in the Release Candidate (RC) 0 build of SQL Server Data Tools in SQL Server 2012 so am hoping that a fix makes it in for the Release-To-Manufacturing (RTM) build.</p>
<p>Hope this helps</p>
<p><a href="http://twitter.com/jamiet" target="_blank">@jamiet</a> </p><img src="http://sqlblog.com/aggbug.aspx?PostID=39903" width="1" height="1">DatadudeDBProjuneauSQL Server Data ToolsVisual Studio 2010Building a database installer with WiX, datadude and Visual Studio 2010http://sqlblog.com/blogs/jamie_thomson/archive/2011/02/10/building-a-database-installer-with-wix-datadude-and-visual-studio-2010.aspxThu, 10 Feb 2011 21:45:17 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:33387jamiet20http://sqlblog.com/blogs/jamie_thomson/comments/33387.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=33387http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=33387<p><font size="3">Today I have been using Windows Installer XML (WiX) to build an installer (.msi file) that would install a SQL Server database on a server of my choosing; the source code for that database lives in datadude (a tool </font><a href="http://www.sqlservercentral.com/questions/visual+studio/72236/" target="_blank"><font size="3">which you may know by one of quite a few other names</font></a><font size="3">). The basis for this work was a most excellent blog post by Duke Kamstra entitled </font><a href="http://blogs.msdn.com/b/dukek/archive/2009/10/19/implementing-a-wix-installer-that-calls-the-gdr-version-of-vsdbcmd-exe.aspx" target="_blank"><font size="3">Implementing a WIX installer that calls the GDR version of VSDBCMD.EXE</font></a><font size="3"> which coves the delicate intricacies of doing this – particularly how to call </font><a href="http://msdn.microsoft.com/en-us/library/dd193283.aspx" target="_blank"><font size="3">Vsdbcmd.exe</font></a><font size="3"> in a </font><a href="http://wix.sourceforge.net/manual-wix3/wix_xsd_customaction.htm" target="_blank"><font size="3">CustomAction</font></a><font size="3">. Unfortunately there are a couple of things wrong with Duke’s post:</font></p> <ol> <li><font size="3">Searching for “datadude wix” didn’t turn it up in the first page of search results and hence it took me a long time to find it. And I knew that it existed. If someone else were after a post on using WiX with datadude its likely that they would never have come across Duke’s post and that would be a great shame because its the definitive post on the matter.</font> </li> <li><font size="3">It was written in October 2009 and had not been updated for Visual Studio 2010.</font> </li> </ol> <p><font size="3">Well, this blog post is an attempt to solve those problems. Hopefully I’ve solved the first one just by following a few of </font><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/12/21/blogging-tips-for-sql-server-professionals.aspx" target="_blank"><font size="3">my blogging SEO tips</font></a><font size="3"> while writing this blog post, in the rest of it I will explain how I took Duke’s code and updated it to work in Visual Studio 2010.</font></p> <p><font size="3"></font></p> <p><font size="3">If you need to build a database installer using WiX, datadude and Visual Studio 2010 then you still need to follow Duke’s blog post so go and do that now. Below are the amendments that I made that enabled the project to get built in Visual Studio 2010:</font></p> <p><font size="3"></font></p> <ol> <li><font size="3" face="Calibri">In VS2010 datadude’s output files have changed from being called Database.&lt;suffix&gt; to &lt;ProjectName&gt;_Database.&lt;suffix&gt;. Duke’s code was referencing the old file name formats.</font> </li> <li><font face="Calibri"><font size="3">Duke used $(var.SolutionDir) and relative paths to point to datadude artefacts I have replaced these with Votive Project References </font><a href="http://wix.sourceforge.net/manual-wix3/votive_project_references.htm"><font size="3">http://wix.sourceforge.net/manual-wix3/votive_project_references.htm</font></a></font> </li> <li><font size="3" face="Calibri">I commented out all references to MicrosoftSqlTypesDbschema in DatabaseArtifacts.wxi. I don't think this is produced in VS2010 (I may be wrong about that but it wasn't in the output from my project)</font> </li> <li><font size="3" face="Calibri">Similarly I commented out component MicrosoftSqlTypesDbschema in VsdbcmdArtifacts.wxi. It wasn't where Duke's code said it should have been so am assuming/hoping it isn't needed.</font> </li> <li><font size="3" face="Calibri">Duke's <font face="Consolas">?define</font> block to work out appropriate SrcArchPath actually wasn't working for me (i.e. <font face="Consolas">&lt;?if $(var.Platform)=x64 ?</font>&gt; was evaluating to false)&#160; so I just took out the conditional stuff and declared the path explicitly to the “Program Files (x86)” path. The old code is still there though if you need to put it back.</font> </li> <li><font size="3" face="Calibri">None of the <font face="Consolas">&lt;RegistrySearch&gt;</font> stuff is needed for VS2010 - so I commented it all out!</font> </li> <li><font size="3" face="Calibri">Changed to use <font face="Consolas">/manifest </font>option rather than <font face="Consolas">/model</font> option on vsdbcmd.exe command-line. Personal preference is all!</font> </li> <li><font size="3" face="Calibri">Added a new component in order to bundle along the vsdbcmd.exe.config file</font> </li> <li><font size="3" face="Calibri">Made the install of the Custom Action dependent on the relevant feature being selected for install. This one is actually really important – deselecting the database feature for installation does not, by default, stop the CustomAction from executing and so would cause an error - so that scenario needs to be catered for</font> </li> </ol> <p><font size="3">I have made my amended solution available for download at: </font><a title="http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.zip" href="http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.zip"><font size="3">http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.zip</font></a><font size="3"> It contains two projects: the WiX project and the datadude project that is the source to be deployed (for demo purposes it only contains one table).</font></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_640907F6.png"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_4817D2FE.png" width="407" height="335" /></a></p> <p><font size="3">I have also made the .msi available although in order that it gets through file blockers I changed the name from InstallMyDatabase.msi to InstallMyDatabase.ms_ – simply rename the file back once you have downloaded it from: </font><a title="http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.ms%5E_" href="http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.ms%5E_"><font size="3">http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.ms%5E_</font></a><font size="3"> .You can try it out for yourself – the only thing it does is dump the files into %Program Files%\MyDatabase and uses them to install a database onto a server of your choosing with a name of your choosing - no damaging side-affects. I will caveat this by saying “it works on my machine” and, not having access to a plethora of different machines, I haven’t tested it anywhere else. One potential issue that I know of is that Vsdbcmd.exe has a <a href="http://msdn.microsoft.com/en-us/library/dd193258.aspx" target="_blank">dependency on SQL Server CE</a> although if you have SQL Server tools or Visual Studio installed you should be fine. Unfortunately its not possible to bundle along the SQL Server CE installer in the .msi because Windows will not allow you to call one installer from inside another – the recommended way to get around this problem is to build a bootstrapper to bundle the whole lot together but doing that is outside the scope of this blog post. If you discover any other issues then please let me know.</font></p> <p><font size="3">Here are the screenshots from the installer:</font></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_1879B472.png"><font size="3"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_7DCD1858.png" width="522" height="408" /></font></a></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_14ABE9D5.png"><font size="3"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2BF6EE46.png" width="522" height="408" /></font></a></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_029FA648.png"><font size="3"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_15E05CE7.png" width="524" height="409" /></font></a></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_74148455.png"><font size="3"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_322D9F07.png" width="524" height="410" /></font></a></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_7765F630.png"><font size="3"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_0F1D2D97.png" width="524" height="409" /></font></a></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_141F6B46.png"><font size="3"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_64814CB9.png" width="526" height="411" /></font></a></p> <p><font size="3"></font></p> <p><font size="3">And once installed….</font></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_7CEF886A.png"><font size="3"></font><a href="http://sqlblog.com/blogs/jamie_thomson/image_109C71FF.png"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2808D80F.png" width="322" height="191" /></a></a></a></p> <p><font size="3"></font></p> <p><font size="3"></font></p> <p><font size="3"></font></p> <p><font size="3"></font></p> <p><font size="3"></font></p> <p><font size="3"></font></p> <p><font size="3"></font></p> <p><font size="3"></font></p> <p><font size="3">Hope this is useful!</font></p> <p><a href="http://twitter.com/jamiet"><font size="3">@jamiet</font></a><font size="3">&#160;</font></p><img src="http://sqlblog.com/aggbug.aspx?PostID=33387" width="1" height="1">DatadudemsisqlserverVisual Studio 2010vsdbcmd.exeVSTS Database EditionwixMaintaining version history in your database using Visual Studio 2010http://sqlblog.com/blogs/jamie_thomson/archive/2010/10/19/maintaining-version-history-in-your-database-using-visual-studio-2010.aspxTue, 19 Oct 2010 19:26:14 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:29503jamiet4http://sqlblog.com/blogs/jamie_thomson/comments/29503.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=29503http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=29503<p>I have written a few blogs lately explaining how my current project is employing the use of datadude (aka the database development tools in Visual Studio 2010) in order to manage our database code, deployment of that code and also testing of it. In this blog post I’m going to share a little technique that we use in order to store a version history of our deployments. Note that this assumes a working knowledge of datadude so if you don’t know what the terms Post-Deployment script, SQLCMD variables, Continuous Integration &amp; msbuild refer to then maybe this blog post is not for you!</p> <p>&#160;</p> <h3><b>Need a table</b></h3> <p>Firstly, we need a table to store our version history, in our case we have called it <font face="Consolas">[BuildVersion]</font> and it looks like this:</p> <code style="font-size:12px;"><span style="color:black;"> <blockquote> <p> <br /></p> <span style="color:blue;">CREATE TABLE </span><span style="color:black;">[dbo].[BuildVersion] <br /></span><span style="color:gray;">( <br />&#160;&#160;&#160; </span><span style="color:black;">[BuildVersion] </span><span style="color:blue;">NVARCHAR</span><span style="color:gray;">(</span><span style="color:black;">20</span><span style="color:gray;">) NOT NULL, <br />&#160;&#160;&#160; </span><span style="color:black;">[Deployed]&#160;&#160;&#160;&#160; </span><span style="color:blue;">DATETIME </span><span style="color:gray;">NOT NULL <br />) <br /></span></blockquote> </span></code> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/20101019schema_view_722CAE33.png"><img style="background-image:none;border-bottom:0px;border-left:0px;margin:;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="20101019schema_view" border="0" alt="20101019schema_view" src="http://sqlblog.com/blogs/jamie_thomson/20101019schema_view_thumb_0FBE8933.png" width="311" height="277" /></a></p> <p>&#160;</p> </blockquote> <h3><b>Need a project variable</b></h3> <p>We have a SQLCMD variable declared as part of our project that is intended to hold a version number. In datadude SQLCMD variables are (by default) declared in a file called <strong>Database.sqlcmdvars</strong>:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/20101019slcmdvars_solnexp_2DBC9727.png"><img style="background-image:none;border-bottom:0px;border-left:0px;margin:;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="20101019slcmdvars_solnexp" border="0" alt="20101019slcmdvars_solnexp" src="http://sqlblog.com/blogs/jamie_thomson/20101019slcmdvars_solnexp_thumb_53461488.png" width="307" height="256" /></a></p> </blockquote> <p>In our case we have a variable called ‘BuildVersion’ that we default to the value of “Unknown”</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/20101019sqlcmdvars_vars_55AED07D.jpg"><img style="background-image:none;border-bottom:0px;border-left:0px;margin:;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="20101019sqlcmdvars_vars" border="0" alt="20101019sqlcmdvars_vars" src="http://sqlblog.com/blogs/jamie_thomson/20101019sqlcmdvars_vars_thumb_5391D1B4.jpg" width="476" height="134" /></a></p> <p>&#160;</p> </blockquote> <h3><b>Need to populate the table</b></h3> <p>We use the value in our ‘BuildVersion’ variable in order to populate our [BuildVersion] table and we do that within a Post-Deployment script using the following simple code:</p> <blockquote> <p><code style="font-size:12px;"><span style="color:blue;">INSERT&#160;&#160; </span><span style="color:black;">[dbo].[BuildVersion]</span><span style="color:gray;">(</span><span style="color:black;">[BuildVersion]</span><span style="color:gray;">, </span><span style="color:black;">[Deployed]</span><span style="color:gray;">) <br /></span><span style="color:blue;">VALUES&#160;&#160; </span><span style="color:gray;">(</span><span style="color:red;">'$(BuildVersion)'</span><span style="color:gray;">, </span><span style="color:magenta;">GETDATE</span><span style="color:gray;">())&#160; </span><span style="color:green;">--$(BuildVersion is a SQLCMD variable declared within this project)</span></code> </p> <p>&#160;</p> </blockquote> <h3><b>And of course we need to tell our mechanism what the build number is</b></h3> <p>We need to make sure that $(BuildVersion) has a value in it. As we are deploying our database as part of a continuous integration build (leveraging msbuild) we can pass in a value from the msbuild script. Here’s the code that we use to do that:</p> <blockquote> <p><font face="Consolas"><font color="#0000ff">&lt;</font><font color="#c0504d">Exec </font><font color="#ff0000">Command</font><font color="#0000ff">=</font>&quot;<font color="#0000ff">..\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:</font><font color="#ff0000">&amp;quot</font><font color="#ff0000">;</font><font color="#0000ff">Data Source=$(Server);Integrated Security=True</font><font color="#ff0000">&amp;quot;</font> <font color="#0000ff">/DeployToDatabase:+ /ManifestFile:</font><font color="#ff0000">&amp;quot;</font><font color="#0000ff">..\MyDB.deploymanifest</font><font color="#ff0000">&amp;quot;</font> <u><font color="#0000ff">/p:BuildVersion=</font></u><u><font color="#ff0000">&amp;quot;</font><font color="#0000ff">$(BuildLabel)</font><font color="#ff0000">&amp;quot;</font></u>&quot; <font color="#0000ff">/&gt;</font></font></p> </blockquote> <pre><font face="Calibri">The important bit for what we are discussing herein is that last underlined part:</font></pre>
<blockquote>
<p><font face="Consolas"><u><font color="#0000ff">/p:BuildVersion=</font></u><u><font color="#ff0000">&amp;quot;</font><font color="#0000ff">$(BuildLabel)</font><font color="#ff0000">&amp;quot;</font></u></font></p>
</blockquote>
<p>The /p directive is used to populate a named variable (in our case <font color="#0000ff" face="Consolas">BuildVersion</font>) with a value. In our case than value is taken from an msbuild property which, in our case, is referred to by <font color="#0000ff">$(BuildLabel)</font>. [Note that managing msbuild properties is outside the scope of this blog post.]</p>
<p><b></b></p>
<h3>Wrap-up</h3>
<p>That’s pretty much all you need. If it all hangs together correctly then <font face="Consolas">[BuildVersion]</font> will contain a nice history of all your deployments like so:</p>
<blockquote>
<p><a href="http://sqlblog.com/blogs/jamie_thomson/20101019results_074A8E0F.png"><img style="background-image:none;border-bottom:0px;border-left:0px;margin:;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="20101019results" border="0" alt="20101019results" src="http://sqlblog.com/blogs/jamie_thomson/20101019results_thumb_184EBBF2.png" width="461" height="215" /></a></p>
</blockquote>
<p>Hope this is useful! I suspect the same technique will work in earlier versions of datadude but I don’t have any to hand so can’t find out.</p>
<p><a href="http://twitter.com/jamiet">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=29503" width="1" height="1">Datadudesql servert-sqlVisual Studio 2010Experiences from writing sp_CascadingDataViewer – DB unit testing and code distributionhttp://sqlblog.com/blogs/jamie_thomson/archive/2010/09/18/experiences-from-writing-sp-cascadingdataviewer-db-unit-testing-and-code-distribution.aspxSat, 18 Sep 2010 16:41:55 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:28849jamiet3http://sqlblog.com/blogs/jamie_thomson/comments/28849.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=28849http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=28849<p>Its now been a month since I <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/08/11/introducing-sp-cascadingdataviewer.aspx" target="_blank">introduced sp_CascadingDataViewer</a> and I wanted to take the opportunity to talk about a couple of my experiences while writing it.</p> <p>&#160;</p> <h2>SQL Server needs packages</h2> <p>Version 1 of sp_CascadingDataViewer is an 899 line stored procedure (view the code at <a href="http://cascadingdataviewer.codeplex.com/SourceControl/changeset/view/58999#1568990" target="_blank">changeset 58999</a>), that’s a lot of code and much of it is repeated. I would have loved to have hived off different parts into dedicated functions but I couldn’t do that because my aim was to keep everything in a single stored procedure thus making it as easy as possible for someone to add it to their SQL Server instance. That is an unfortunate trade-off that we have to make with SQL Server.</p> <p>Oracle has a solution to this problem called packages. A package is ostensibly a collection of stored procedures and functions that can be distributed and installed as a single unit. That makes them extremely portable and would be an ideal mechanism for me to distribute a collection of stored procedures and functions that make up Cascading Data Viewer.</p> <p>SQL Server needs something equivalent to Oracle packages.</p> <p>&#160;</p> <h2>You need to be doing automated database unit testing</h2> <p>sp_CascadingDataViewer wouldn’t exist without automated database unit testing in Visual Studio. Period.</p> <p>With something as complicated as sp_CascadingDataViewer there were many many combinations of code, schema and data that could cause is to break. I had to deal with all those combinations and have a way of ensuring that any changes that I made did not break something else and automated testing was the means with which I did that.</p> <p>I wrote the code for sp_CascadingDataViewer in Visual Studio rather than SQL Server Management Studio (as I do for all my T-SQL code these days) which meant that my code and the tests lived in the same solution. At the time of writing that solution includes 30 tests and all are available in <a href="http://cascadingdataviewer.codeplex.com/SourceControl/list/changesets" target="_blank">the source code on Codeplex</a>:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5F31D6E2.png"><img style="background-image:none;border-right-width:0px;margin:;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_79CE4D2E.png" width="529" height="628" /></a></p> </blockquote> <p>With a simple key chord (CTRL+R, CTRL+A) I could deploy my code and run all my tests against it – that’s an incredibly powerful mechanism and I actually find it to be very productive method of development even if you have many tests that need to be run.</p> <p>Writing sp_CascadingDataViewer switched me on to the value of automated database unit testing and I now advocate its use wherever I go.</p> <p><a href="http://twitter.com/jamiet">@jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=28849" width="1" height="1">Datadudesql serverunit testingVisual Studio 2010Microsoft publish Visual Studio 2010 Database Project Guidancehttp://sqlblog.com/blogs/jamie_thomson/archive/2010/08/23/microsoft-publish-visual-studio-2010-database-project-guidance.aspxMon, 23 Aug 2010 11:33:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:28174jamiet1http://sqlblog.com/blogs/jamie_thomson/comments/28174.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=28174http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=28174<SPAN class=Apple-style-span style="WORD-SPACING:0px;FONT:medium 'Times New Roman';TEXT-TRANSFORM:none;TEXT-INDENT:0px;WHITE-SPACE:normal;LETTER-SPACING:normal;BORDER-COLLAPSE:separate;orphans:2;widows:2;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-size-adjust:auto;-webkit-text-stroke-width:0px;">
<DIV style="PADDING-RIGHT:8px;PADDING-LEFT:8px;FONT-SIZE:10pt;PADDING-BOTTOM:8px;PADDING-TOP:8px;FONT-FAMILY:Arial, Helvetica, sans-serif;">Three days ago I wrote a blog post entitled&nbsp;<A class="" href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/08/20/setting-up-database-unit-testing-as-part-of-a-continuous-integration-build-process-vs2010-db-tools-datadude.aspx">Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude]</A>&nbsp;which goes through some of the intracasies of using Visual Studio 2010 Database projects (aka datadude projects) and database unit testing as part of a continuous integration build. Well it turns out I needn't have bothered because today Microsoft have (or, more accurately, Jens Suessmeyer has) published a 91-page guidance document on using datadude projects and on page 57&nbsp;it covers exactly what I covered in my blog post (plus a lot more besides). Ah well...</DIV>
<DIV style="PADDING-RIGHT:8px;PADDING-LEFT:8px;FONT-SIZE:10pt;PADDING-BOTTOM:8px;PADDING-TOP:8px;FONT-FAMILY:Arial, Helvetica, sans-serif;">Anyway, if you want to take a read of this tome head to <A href="http://vsdatabaseguide.codeplex.com/">http://vsdatabaseguide.codeplex.com/</A>&nbsp;and/or go and read Jens' blog post on the matter <A class="" href="http://blogs.msdn.com/b/jenss/archive/2010/08/21/alm-visual-studio-database-projects-guidance-is-out-in-the-wild.aspx">ALM Visual Studio Database Projects Guidance is out in the wild !</A></DIV>
<DIV style="PADDING-RIGHT:8px;PADDING-LEFT:8px;FONT-SIZE:10pt;PADDING-BOTTOM:8px;PADDING-TOP:8px;FONT-FAMILY:Arial, Helvetica, sans-serif;"><A class="" href="http://twitter.com/jamiet">@Jamiet</A></DIV></SPAN><img src="http://sqlblog.com/aggbug.aspx?PostID=28174" width="1" height="1">Datadudesql serverunit testingVisual Studio 2010Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude]http://sqlblog.com/blogs/jamie_thomson/archive/2010/08/20/setting-up-database-unit-testing-as-part-of-a-continuous-integration-build-process-vs2010-db-tools-datadude.aspxFri, 20 Aug 2010 18:41:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:28103jamiet19http://sqlblog.com/blogs/jamie_thomson/comments/28103.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=28103http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=28103<P><I><FONT face=Consolas>[This blog post assumes a familiarity with the terms Continuous Integration (CI), MSBuild &amp; MSTest. If you don’t have a good appreciation of those terms then this blog post probably isn’t for you anyway so don’t worry about it!]</FONT></I></P>
<P><FONT face=Consolas>Over the past few days I have been working to get database unit tests executing as part of our Continuous Integration (CI) build and in this blog post I’ll explain how I went about it because it is decidedly <I>not</I> straightforward.</FONT></P>
<P><FONT face=Consolas>We are using the <STRONG>DB Tools in Visual Studio 2010</STRONG> (aka <STRONG>DBPro</STRONG> or <STRONG>Datadude</STRONG> - I will refer to it as Datadude from here on in) which includes unit testing functionality. The rest of this blog post also assumes a familiarity with database unit testing in Datadude although if you want to do some background reading an excellent place to start is Jamie Laflen’s whitepaper </FONT><A href="http://msdn.microsoft.com/en-us/magazine/cc164243.aspx"><FONT face=Consolas>Apply Test-Driven Development to your Database Projects</FONT></A><FONT face=Consolas> on MSDN.</FONT></P><FONT face=Consolas>
<HR>
</FONT>
<P><FONT face=Consolas>We got to the point where we had a C# test project containing database unit tests that executed successfully inside Visual Studio. For demonstration purposes I have put together a solution that contains a simple database project and a test project containing a database unit test:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image001_png01CB4075_1B0AD4EE.png"><FONT face=Consolas><IMG title="Solution Explorer screenshot" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=433 alt="solution explorer" src="http://sqlblog.com/blogs/jamie_thomson/cid_image001_png01CB4075_thumb_7DC4E149.png" width=506 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>And just to prove that the test executes successfully in Visual Studio:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image002_png01CB4075_10D5E829.png"><FONT face=Consolas><IMG title="Visual Studio Test Results screenshot" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=249 alt="Visual Studio Test Results screenshot" src="http://sqlblog.com/blogs/jamie_thomson/cid_image002_png01CB4075_thumb_1DCFC83A.png" width=856 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>N.B. The code for the database object(s) and the unit test itself are not important, that is outside the scope of this blog post.</FONT></P>
<P><FONT face=Consolas>At this point we have some tests that run in our development sandbox, the configuration for which is done using the Database Test Configuration dialog:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image003_png01CB4076_14938CF9.png"><FONT face=Consolas><IMG title="database test configuration choice" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=382 alt="database test configuration choice" src="http://sqlblog.com/blogs/jamie_thomson/cid_image003_png01CB4076_thumb_057CAE1F.png" width=786 border=0></FONT></A></P></BLOCKQUOTE>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image004_png01CB4076_70AAB59E.png"><FONT face=Consolas><IMG title="database test configuration dialog" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=638 alt="database test configuration dialog" src="http://sqlblog.com/blogs/jamie_thomson/cid_image004_png01CB4076_thumb_4FB742F7.png" width=508 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>Those config settings are stored in the app.config file which exists as part of our test project (screenshot of which is at the top of this blog post). If we take a look inside that file we can see the</FONT> <FONT face="Courier New">ConnectionString</FONT> <FONT face=Consolas>that we defined in the Database Test Configuration dialog:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image005_png01CB4076_5F76D7FB.png"><IMG title="app config connectionString DatabaseUnitTesting" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=271 alt="app config connectionString DatabaseUnitTesting" src="http://sqlblog.com/blogs/jamie_thomson/cid_image005_png01CB4076_thumb_17491C1F.png" width=959 border=0></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>Note the</FONT> <FONT face="Courier New"><FONT color=#0000ff>&lt;</FONT><FONT color=#c0504d>DatabaseUnitTesting</FONT><FONT color=#0000ff>&gt;</FONT> </FONT><FONT face=Consolas>element, we’re going to be coming back to that a little later!!</FONT></P><FONT face=Consolas>
<HR>
</FONT>
<P><FONT face=Consolas>Once we have the tests running in Visual Studio the next step is to get them running inside a CI build and for that we call out to MSTest.exe from inside an MSBuild script:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image006_png01CB407D_54F603DB.png"><FONT face=Consolas><IMG title="msbuild script mstest" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=155 alt="msbuild script mstest" src="http://sqlblog.com/blogs/jamie_thomson/cid_image006_png01CB407D_thumb_733FF92A.png" width=1069 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>When we execute that script we get some positive results and all looks peachy:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image007_png01CB407D_380C1D5F.png"><FONT face=Consolas><IMG title="msbuild mstest output" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=534 alt="msbuild mstest output" src="http://sqlblog.com/blogs/jamie_thomson/cid_image007_png01CB407D_thumb_4837E558.png" width=650 border=0></FONT></A></P></BLOCKQUOTE><FONT face=Consolas>
<HR>
</FONT>
<P><FONT face=Consolas>OK, we now have a test that runs successfully both in Visual Studio and by calling MSTest.exe directly from MSBuild. The problem I have to solve now though (and this is the real crux of this blog post) is that the test is still running against the server I specified in app.config; I don’t want this, I want the test to run against a server of my choosing, namely my build server. For this I had to call on the help of the aforementioned Jamie Laflen (who, luckily, I have met in the past) and he gave me guidance on how to achieve it. Below are the steps that you need to take.</FONT></P>
<P><FONT face=Consolas>Firstly you need to tell Datadude that you want to specify some different credentials and that is done by adding an attribute</FONT> <FONT face="Courier New"><FONT color=#ff0000>AllowConfigurationOverride</FONT><FONT color=#0000ff>=</FONT>"<FONT color=#0000ff>true</FONT>"</FONT><FONT face=Consolas> to the</FONT> <FONT face="Courier New"><FONT color=#0000ff>&lt;</FONT><FONT color=#c0504d>DatabaseUnitTesting</FONT><FONT color=#0000ff>&gt;</FONT> </FONT><FONT face=Consolas>element of app.config like so:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image008_png01CB407E_32F9B9E3.png"><FONT face=Consolas><IMG title="app config DatabaseUnitTesting AllowConfigurationOverride" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=345 alt="app config DatabaseUnitTesting AllowConfigurationOverride" src="http://sqlblog.com/blogs/jamie_thomson/cid_image008_png01CB407E_thumb_23E2DB09.png" width=643 border=0></FONT></A></P></BLOCKQUOTE>
<P><I><FONT face=Consolas>Don’t forget to build your test project after changing the app.config file!!!</FONT></I></P>
<P><FONT face=Consolas>This will cause datadude unit testing framework to go and search for a file called</FONT> <FONT face="Courier New">*.dbunittest.config</FONT> <FONT face=Consolas>where the * indicates either:</FONT></P>
<UL>
<LI><FONT face=Consolas>The name of machine upon which the tests are being run or</FONT> </LI>
<LI><FONT face=Consolas>The name of the user running the tests</FONT> </LI></UL>
<P><FONT face=Consolas>According to Jamie L datadude runs the following precedence checks:</FONT></P>
<OL>
<LI><FONT face=Consolas>Is an override specified in app.config? If not, use app.config</FONT> </LI>
<LI><FONT face=Consolas>Does an override file exist called &lt;machinename&gt;.dbunittest.config? If so, use it, if not…</FONT> </LI>
<LI><FONT face=Consolas>Does an override file exist called &lt;username&gt;.dbunittest.config? If so, use it, if not…</FONT> </LI>
<LI><FONT face=Consolas>Fail!</FONT> </LI></OL>
<P><FONT face=Consolas>“So”, you may be asking, “what goes in this </FONT><FONT face="Courier New">*.dbunittest.config</FONT> file then?”. <FONT face=Consolas>Fair question, that’s where the</FONT> <FONT face="Courier New"><FONT color=#0000ff>&lt;</FONT><FONT color=#c0504d>DatabaseUnitTesting</FONT><FONT color=#0000ff>&gt;</FONT> </FONT><FONT face=Consolas>element that I mentioned earlier comes in. Copy that element from the app.config file into your <FONT face="Courier New">*.dbunittest.config</FONT> file, remove the</FONT>&nbsp;<FONT face="Courier New"><FONT color=#ff0000>AllowConfigurationOverride</FONT><FONT color=#0000ff>=</FONT>"<FONT color=#0000ff>true</FONT>"</FONT><FONT face=Consolas> attribute and change the</FONT> <FONT face="Courier New">ConnectionString</FONT> <FONT face=Consolas>property accordingly:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image009_png01CB4081_20ED7656.png"><FONT face=Consolas><IMG title="dbunittest.config ConnectionString" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=236 alt="dbunittest.config ConnectionString" src="http://sqlblog.com/blogs/jamie_thomson/cid_image009_png01CB4081_thumb_7464466F.png" width=847 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>The next step is to tell MSTest.exe that there is another file that it needs to be aware of when it runs the tests and to do that we need to create a custom testsettings file that will be used instead of the default Local.testsettings. To create a new testsettings file use the Add New Item dialog:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image010_png01CB4083_7D049EFB.png"><FONT face=Consolas><IMG title="add new test settings" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=466 alt="add new test settings" src="http://sqlblog.com/blogs/jamie_thomson/cid_image010_png01CB4083_thumb_1BDB12DA.png" width=825 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>In the Test Settings dialog go to ‘Deployment’, click ‘Enable deployment’, then ‘Add File…’. Browse to your</FONT><FONT face="Courier New"> *.dbunittest.config </FONT><FONT face=Consolas>file and add it to the project:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image011_png01CB4083_39D920CE.png"><FONT face=Consolas><IMG title="test settings dialog" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=655 alt="test settings dialog" src="http://sqlblog.com/blogs/jamie_thomson/cid_image011_png01CB4083_thumb_3FB3C467.png" width=892 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>At this point you’re good to go and all you need to do edit your call to MSTest.exe and tell it to use your new testsettings file:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image012_png01CB4084_5CD96C71.png"><FONT face=Consolas><IMG title="msbuild script mstest testsettings" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=163 alt="msbuild script mstest testsettings" src="http://sqlblog.com/blogs/jamie_thomson/cid_image012_png01CB4084_thumb_70865605.png" width=1011 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>Notice the extra information in the output:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image013_png01CB4084_2F0BA3AC.png"><FONT face=Consolas><IMG title="msbuild script output" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=577 alt="msbuild script output" src="http://sqlblog.com/blogs/jamie_thomson/cid_image013_png01CB4084_thumb_4DE2178A.png" width=702 border=0></FONT></A></P></BLOCKQUOTE>
<P><FONT face=Consolas>You’ll have to trust me that it used the new config file and therefore the new </FONT><FONT face="Courier New">ConnectionString</FONT><FONT face=Consolas>, although having said that you do get some useful information in the test results file (*.trx) that MSTest.exe creates:</FONT></P>
<BLOCKQUOTE>
<P><A href="http://sqlblog.com/blogs/jamie_thomson/cid_image014_png01CB4085_248ACF8C.png"><FONT face=Consolas><IMG title="mstest test results trx output" style="BORDER-TOP-WIDTH:0px;PADDING-RIGHT:0px;DISPLAY:inline;PADDING-LEFT:0px;BORDER-LEFT-WIDTH:0px;BACKGROUND-IMAGE:none;BORDER-BOTTOM-WIDTH:0px;PADDING-TOP:0px;BORDER-RIGHT-WIDTH:0px;" height=255 alt="mstest test results trx output" src="http://sqlblog.com/blogs/jamie_thomson/cid_image014_png01CB4085_thumb_2A657325.png" width=991 border=0></FONT></A></P></BLOCKQUOTE><FONT face=Consolas>
<HR>
</FONT>
<P><FONT face=Consolas>I think its fair to say that this process is a little fiddly to say the least so I’m hoping that Microsoft come up with a better solution in the future, one that integrates better into the whole testing infrastructure. Meanwhile you can download the demo that I put together for this blog post from my </FONT><A href="http://bit.ly/cYYIdq" target=_blank><FONT face=Consolas>SkyDrive</FONT></A><FONT face=Consolas>.</FONT></P>
<P><FONT face=Consolas>Hope that helps! Comments are welcome.</FONT></P>
<P><A href="http://twitter.com/jamiet" target=_blank><FONT face=Consolas>Jamiet</FONT></A></P><SPAN class=Apple-style-span style="WORD-SPACING:0px;FONT:medium 'Times New Roman';TEXT-TRANSFORM:none;TEXT-INDENT:0px;WHITE-SPACE:normal;LETTER-SPACING:normal;BORDER-COLLAPSE:separate;orphans:2;widows:2;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-size-adjust:auto;-webkit-text-stroke-width:0px;"><SPAN class=Apple-style-span style="FONT-SIZE:13px;FONT-FAMILY:Arial, Helvetica, sans-serif;">
<P>UPDATE: Atul Verma has written a 3-part blog series on how to write your database unit tests so if Jamie Laflen's whitepaper (linked to above) doesn't tickle your fancy try reading Atul's series instead:</P>
<UL>
<LI><A class="" href="http://blogs.msdn.com/b/atverma/archive/2010/07/28/how-to-unit-test-sql-server-2008-database-using-visual-studio-2010.aspx">How to unit test SQL Server 2008 database using Visual Studio 2010</A></LI>
<LI><A class="" href="http://blogs.msdn.com/b/atverma/archive/2010/08/22/how-to-unit-test-sql-server-2008-database-using-visual-studio-2010-part-2.aspx">How to unit test SQL Server 2008 database using Visual Studio 2010 – Part 2</A></LI>
<LI><A class="" href="http://blogs.msdn.com/b/atverma/archive/2010/08/22/how-to-unit-test-sql-server-2008-database-using-visual-studio-2010-part-3.aspx">How to unit test SQL Server 2008 database using Visual Studio 2010 – Part 3</A></LI></UL></SPAN></SPAN><img src="http://sqlblog.com/aggbug.aspx?PostID=28103" width="1" height="1">Continuous IntegrationDatadudemsbuildmstestsql serverunit testingVisual Studio 2010A strategy for managing security for different environments using the Database Development Tools in Visual Studio 2010http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/21/a-strategy-for-managing-security-for-different-environments-using-the-database-development-tools-in-visual-studio-2010.aspxWed, 21 Jul 2010 20:59:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:27217jamiet13http://sqlblog.com/blogs/jamie_thomson/comments/27217.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=27217http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=27217<h3>Intro</h3>
<p>Of late I have been getting down and dirty with the Database Development tools in Visual Studio 2010. You may know this feature set by one of the plethora of other names it has had over recent years such as:</p>
<ul>
<li>Visual Studio Team System for Database Professionals </li>
<li>DBPro </li>
<li>Datadude </li></ul>
<p>For the rest of this post I’ll stick with the colloquial name that most people seem to recognise – datadude.</p>
<p>Regardless of which moniker you prefer I’m hoping you recognise the feature set that I am referring to here which includes:</p>
<ul>
<li>Schema Compare </li>
<li>Data Compare </li>
<li>Offline database development </li>
<li>T-SQL refactoring </li>
<li>Database unit testing/Data generation </li>
<li>MSBuild integration </li>
<li>various other things… </li></ul>
<p>I plan to write a detailed blog post in the coming weeks talking about my overall experiences with datadude but in the meantime I’m writing this blog post to focus on how I have chosen to manage security; by which I mean:</p>
<ul>
<li>logins </li>
<li>users </li>
<li>role membership </li>
<li>user and role permissions </li></ul>
<hr>
<h3>Security in Post-Deployment scripts</h3>
<p>Datadude has built-in support for managing all of this stuff as we can see from this screenshot:</p>
<p><a href="http://sqlblog.com/blogs/jamie_thomson/image_4F758EED.png" mce_href="http://sqlblog.com/blogs/jamie_thomson/image_4F758EED.png"><img width="676" height="433" title="users roles logins in datadude" class="wlDisabledImage" style="border-width:0px;display:inline;" alt="users roles logins in datadude" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_0D8EA99F.png" border="0"></a></p>
<p>Notice that we have created a <b>user</b>, a <b>login </b>for that user and a <b>role </b>into which we later want to add the user. They exist as explicit objects within our project which means that datadude “knows” about them and that has a number of advantages including:</p>
<ul>
<li>Datadude can include these objects in a schema compare </li>
<li>Datadude can do dependency tracking thus enabling offline development (e.g. If the login that is referenced in the CREATE USER statement does not exist then a design-time error will be raised) </li></ul>
<p>That should be enough to justify using datadude to manage users and roles however I have come across a limitation that prohibits us from doing so; we do not deploy the same security model to each of our environments. The reasons for this are very familiar to you I’m sure, we have people that require different permissions on different environments (e.g. our developers will want to be able to issue DDL and UPDATE/INSERT/DELETE on our development environment but we definitely don’t want them to have the same on production).</p>
<p>As far as I can discern datadude does not have a good solution for dealing with this situation. I have not found a way of conditionally deploying objects depending on which environment we are deploying to. Actually that is not altogether true, we could have a Visual Studio configuration for each environment and include/exclude objects as we choose but that doesn’t play well with the build and deploy model of msbuild which requires that you build each one of those configurations; each of our builds takes about 10 minutes which, when multiplied by the number of environments, results in a total build time that takes too long for our continuous integration (CI) usage.</p>
<p>Hence we needed a better way to deploy our security model – we chose to use PostDeployment scripts. PostDeployment scripts do exactly what they say on the tin – they are scripts that get run after datadude has deployed the objects that it knows about. They get called (using SQLCMD syntax) from the <font face="Courier New">Script.PostDeployment.sql </font>file that exists in every datadude project:</p>
<p><a href="http://sqlblog.com/blogs/jamie_thomson/cid_image002_png01CB274D_4ACF5E66.png" mce_href="http://sqlblog.com/blogs/jamie_thomson/cid_image002_png01CB274D_4ACF5E66.png"><img width="303" height="221" title="post deployment script solution explorer" class="wlDisabledImage" style="border-width:0px;display:inline;" alt="post deployment script solution explorer" src="http://sqlblog.com/blogs/jamie_thomson/cid_image002_png01CB274D_thumb_6A120539.png" border="0"></a></p>
<p>My chosen approach is to have a script called <font face="Courier New">SecurityAdditionsWrapper.sql</font> that gets called from <font face="Courier New">Script.PostDeployment.sql</font>. <font face="Courier New">SecurityAdditionsWrapper.sql </font>checks a SQLCMD variable to determine which environment is being deployed to and then calls the appropriate <font face="Courier New">SecurityAdditionsXXX.sql </font>script that is responsible for deploying the security model for that environment (where XXX is the environment).</p>
<p>I then have 3 subfolders that contain all the individual CREATE USER, role membership and permissions scripts that get called from <font face="Courier New">SecurityAdditionsXXX.sql</font> as and when required.</p>
<p>The screenshot below shows the contents of <font face="Courier New">Script.PostDeployment.sql </font>&amp; <font face="Courier New">SecurityAdditionsWrapper.sql</font> and also the files as they exist in Solution Explorer:</p>
<p><a href="http://sqlblog.com/blogs/jamie_thomson/cid_image003_png01CB274F_7DBEEECD.png" mce_href="http://sqlblog.com/blogs/jamie_thomson/cid_image003_png01CB274F_7DBEEECD.png"><img width="778" height="614" title="!cid_image003_png@01CB274F" class="wlDisabledImage" style="border-width:0px;display:inline;" alt="!cid_image003_png@01CB274F" src="http://sqlblog.com/blogs/jamie_thomson/cid_image003_png01CB274F_thumb_5969E47E.png" border="0"></a></p>
<p>Some things to notice:</p>
<ol>
<li>I have a SQLCMD variable called $(DeployType) that will contain the value “DEV”, “UAT” or “PRD” </li>
<li>All of my security scripts exist in Post-Deployment&nbsp; -&gt; SecurityAdditions </li>
<li>In the commented-out code you can see that I was trying to be clever and dynamically build the name of the file to be executed using the $(DeployType) variable – the thinking being that if we later added a new environment type I wouldn’t need to change this script. Unfortunately even though this is valid SQLCMD syntax it does not work in datadude; I have reported that issue on Connect as a bug (with repro) at <a href="https://connect.microsoft.com/VisualStudio/feedback/details/575383/datadude-valid-sqlcmd-syntax-throws-error-at-build-time" mce_href="https://connect.microsoft.com/VisualStudio/feedback/details/575383/datadude-valid-sqlcmd-syntax-throws-error-at-build-time">[Datadude] Valid SQLCMD syntax throws error at build time</a>. </li></ol>
<p>You may be wondering how we get a value into that $(DeployType) variable when using msbuild. Well, its pretty simple when you know how. First you need to include the variable in the sqlcmdvars file for your datadude project:</p>
<p><a href="http://sqlblog.com/blogs/jamie_thomson/cid_image004_png01CB2755_5A129AA8.png" mce_href="http://sqlblog.com/blogs/jamie_thomson/cid_image004_png01CB2755_5A129AA8.png"><img width="430" height="272" title="!cid_image004_png@01CB2755" class="wlDisabledImage" style="border-width:0px;display:inline;" alt="!cid_image004_png@01CB2755" src="http://sqlblog.com/blogs/jamie_thomson/cid_image004_png01CB2755_thumb_0E46C3EF.png" border="0"></a></p>
<p>You then need to pass in the appropriate value to that variable at deploy-time. We are using <a href="http://msdn.microsoft.com/en-us/library/dd193258.aspx" mce_href="http://msdn.microsoft.com/en-us/library/dd193258.aspx">VSDBCMD.exe</a> to run our deployments and the <a href="http://msdn.microsoft.com/en-us/library/dd193283.aspx" mce_href="http://msdn.microsoft.com/en-us/library/dd193283.aspx">syntax to pass in a value for a variable</a> is /p:PropertyName=PropertyValue. We are calling VSDBCMD.exe from inside an msbuild script which therefore looks like this:</p>
<p><font face="Courier New"><font color="#0000ff">&lt;Exec</font> <font color="#ff0000">Command</font><font color="#9b00d3">="..\Tools\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:'Data Source=$(DatabaseServer);Integrated Security=True;Pooling=False' /DeployToDatabase:+ /ManifestFile:.\MyDB.deploymanifest <b>/p:DeployType=&amp;quot;$(DeployType)&amp;quot</b>; "</font> <font color="#0000ff">/&gt;</font></font></p>
<p>$(DeployType) is of course also a property within our msbuild file, which I am drawing attention to in the bold section above.</p>
<h3>Wrap-Up</h3>
<p>In conclusion, I am not saying that this is the <i>correct</i> way to deploy a SQL Server security model using datadude, it just happens to be the method that I have chosen. I would really like to how people have solved this using datadude so if you have any anecdotes to share please put them in the comments.</p>
<hr>
<h3>One more thing…</h3>
<p>Cranking all of those files out by hand is a laborious undertaking, especially if you have lots of users with lots of permissions. In our case we introduced datadude to be used with an existing system hence we already had all our security setup on existing servers but we had no good way of getting it into the structure that I explained above. Powershell and SQLCMD to the rescue!</p>
<p>I have produced a series of scripts that interrogate a named instance and produce all of the files that I explain above. Namely:</p>
<ul>
<li>A file for each user which gets dropped into %SolutionRoot%\%ProjectName%\Scripts\Post-Deployment\Users\ </li>
<li>A file for each principle that issues all the GRANT/DENY EXECUTE/SELECT/INSERT/UPDATE/DELETE for that principle, it gets dropped into %SolutionRoot%\%ProjectName%\Scripts\Post-Deployment\PermissionsSets\ </li>
<li>A file that specifies all of the role memberships, for all database principles. </li></ul>
<p>Creating the CREATE USER file and the file containing the permissions for each principle is&nbsp;quite involved. For that we need some nested loops that loop over (a) all the databases for which you want to scripts users and (b) all the principles in each database. It then dumps the appropriate files into the appropriate folders and then you have the simple task of adding them to your project and calling them from your Post Deployment script.</p>
<p>The files are stored in a zip folder on my OneDrive at <a href="http://1drv.ms/11tdPML">http://1drv.ms/11tdPML</a></p>
<p>To run the Powershell script simply unzip all the files into the root folder of your solution and issue:</p>
<blockquote>
<p><font face="Courier New">&gt;GeneratePermissions.ps1 -SQLInstance instanceName -Environment XXX</font></p></blockquote>
<p>changing SQLInstance &amp; XXX accordingly (the <font face="Courier New">$Environment</font> variable is simply a suffix that is added to the names of some of the generated files). The Powershell script contains more detailed notes within it in the comments section at the top.</p>
<p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0A78F952.png" mce_href="http://sqlblog.com/blogs/jamie_thomson/image_0A78F952.png"><img width="739" height="273" title="powershell script comments" class="wlDisabledImage" style="border-width:0px;display:inline;" alt="powershell script comments" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_62664A32.png" border="0"></a></p>
<p>Hope this helps!</p>
<p>Disclaimer: I’m giving this away for free and I won’t be supporting it so use at your own peril (in other words, don’t come here complaining that it doesn’t work on your environment). On the other hand if you find a problem and are able to fix it – please let me know <img class="wlEmoticon wlEmoticon-smile" alt="Smile" src="http://sqlblog.com/blogs/jamie_thomson/wlEmoticonsmile_19CC5B61.png"></p>
<p>UPDATE: I did actually find some problems with the scripts and have now uploaded a new set. I'm confident that they'll work this time.</p>
<p>UPDATE 2: I've updated the scripts again. They now create the SecurityAdditionsXXX.sql file as well as the individual CREATE USER &amp; permissions files;&nbsp;SecurityAdditionsXXX.sql contains all of the calls to the relevant scripts for environment XXX.</p>
<p>SecurityAdditionsXXX.sql also issues all of the role membership statements as well. So just to clarify - you now no longer have a need to run the script that generates the role memberships - GeneratePermissions.ps1 takes care of everything.</p><p>UPDATE 3: Peter Schott has taken this, adapted it for hiw own needs, and sent it back to me so that I can share it here. His modified version of the scripts is at <a href="http://1drv.ms/1uimCKV">http://1drv.ms/1uimCKV</a>&nbsp;and includes these changes (mainly to deal with roles):</p><p>1. Changed the Powershell script:<br>&nbsp; * Added a section to handle role permissions. (really don't care for the way VS does it)<br>&nbsp; * Tweaked the Database Principle section - for some reason, the variable in the PS script I extracted wasn't set properly so I just changed the names around<br>&nbsp; * Added a bunch of lines of code to add to the Array. My Powershell's not too strong so wasn't sure about the best way to add new DBs/Projects to the DB Array. I ended up doing a bunch of sets. I know it's not ideal, but I just wanted to get it done one time.<br>&nbsp; * Tweaked the section that creates the Users. If one of those logins doesn't exist, the grant permissions script would stop. Now I check to make sure that the login exists before the script runs.&nbsp;I sometimes get empty (Login = [])&nbsp; sections. No ideas on that yet. I fix or delete that file as appropriate.<br><br>2. Added a "GetDatabaseRoleList.sql" file.&nbsp; Gets the roles for the DB so we can run it through the Permissions file<br><br>3. Permissions SQL file. I tweaked this with a query that UNIONs the object permissions with the Schema permissions. I didn't break it down further <br>like you did, but our permissions are so messed up right now that it doesn't matter much to me. (we have <i>far</i> too many permissions granted to <br>individual users instead of roles because the prior DBA didn't understand the point of DB Roles for permissions and even then far too many permissions because we had a job that nightly granted all permissions on all objects to certain usernames :-P&nbsp; )<br></p><p>And one more change that needs to be made which Peter informed me of later: </p><p>Ended up changing the GeneratePermissions.ps1 file to change all of the Out-File <br>to "Out-File -width 500 ".<br><br>After doing that, the permissions weren't truncated at 80 characters.&nbsp; Also tweaked the Invoke-SQLCmd functions to use -MaxCharLength 500 so the output line would be long enough. That may have been overkill or there may be a better way to handle, but some of my permission lines were definitely longer than the allowed line lengths.</p>
<p><a href="http://twitter.com/jamiet" mce_href="http://twitter.com/jamiet">@Jamiet</a></p>P.S. I have used Powershell in the past but this is the first time I have really got into the inner workings of it and I’ve got to tell you – its just fantastic. The productivity you get is just incredible – if you don’t believe me have a look at the script herein and see how easy it is to generate a dataset from SQL Server and then loop over it inside your script. Its two lines of code - really fantastic stuff – I recommend any SQL Server pro go out and spend some time getting to know Powershell. <img src="http://sqlblog.com/aggbug.aspx?PostID=27217" width="1" height="1">DatadudemsbuildPowershellSecuritysql serverVisual Studio 2010