Search results matching tag 'msbuild'

Editing sqlcmdvariable nodes in SSDT Publish Profile files using msbuild

jamiet — Mon, 10 Dec 2012 13:42:51 GMT

Publish profile files are a new feature of SSDT database projects that enable you to package up all environment-specific properties into a single file for use at publish time; I have written about them before at Publish Profile Files in SQL Server Data Tools (SSDT) and if it wasn’t obvious from that blog post, I’m a big fan!

As I have used Publish Profile files more and more I have realised that there may be times when you need to edit those Publish profile files during your build process, you may think of such an operation as a kind of pre-processor step. In my case I have a sqlcmd variable called DeployTag, it holds a value representing the current build number that later gets inserted into a table using a Post-Deployment script (that’s a technique that I wrote about in Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences – search for “Putting a build number into the DB”).

Here are the contents of my Publish Profile file (simplified for demo purposes) :

Notice that DeployTag defaults to “UNKNOWN”.

On my current project we are using msbuild scripts to control what gets built and what I want to do is take the build number from our build engine and edit the Publish profile files accordingly. Here is the pertinent portion of the the msbuild script I came up with to do that:

  <ItemGroup>
    <Namespaces Include="myns">
      <Prefix>myns</Prefix>
      <Uri>http://schemas.microsoft.com/developer/msbuild/2003</Uri>
    </Namespaces>
  </ItemGroup>
 
  <Target Name="UpdateBuildNumber">
    <ItemGroup>
      <SSDTPublishFiles Include="$(DESTINATION)\**\$(CONFIGURATION)\**\*.publish.xml" />
    </ItemGroup>
    <MSBuild.ExtensionPack.Xml.XmlFile Condition="%(SSDTPublishFiles.Identity) != ''"
                                       TaskAction="UpdateElement"
                                       File="%(SSDTPublishFiles.Identity)"
                                       Namespaces="@(Namespaces)" 
                                       XPath="//myns:SqlCmdVariable[@Include='DeployTag']/myns:Value" 
                                       InnerText="$(BuildNumber)"/>
  </Target>

The important bits here are the definition of the namespace http://schemas.microsoft.com/developer/msbuild/2003:

and the XPath expression //myns:SqlCmdVariable[@Include='DeployTag']/myns:Value:

Some extra info:

I use a fantastic tool called XMLPad to discover/test XPath expressions, read more at XMLPad – a new tool in my developer utility belt
MSBuild.ExtensionPack.Xml.XmlFile is a msbuild task used to edit XML files and is available from Mike Fourie’s MSBuild Extension Pack
I’m using a property called $(BuildNumber) to hold the value to substitute into the file and also $(DESTINATION)\**\$(CONFIGURATION)\**\*.publish.xml to define an ItemGroup all of my Publish Profile files. Populating those properties is basic msbuild stuff and is therefore outside the scope of this blog post however if you want to learn more check out MSBuild properties & How To: Use Wildcards to Build All Files in a Directory.

Hope this is useful!

@Jamiet

SSIS MSBuild task now included in MSBuild Extension Pack

jamiet — Tue, 14 Sep 2010 21:11:55 GMT

The information in this blog post may be of interest to anyone out there that is using Continuous Integration with msbuild in order to build/deploy a project that includes SSIS packages.

The MSBuild Extension Pack at http://msbuildextensionpack.codeplex.com/ now includes an MSBuild task that will build an SSIS project, the output from which is a .deploymentmanifest file that can be used in concert with the SSIS deployment utility.

Building a SSIS project using the SSIS MSBuild Task essentially has the same effect as right-clicking on a SSIS project and selecting Build with CreateDeploymentUtility=True (i.e. produces a .deploymentmanifest file):

The advantage of using the MSBuild task is of course that you can now do it in an MSBuild script, none of this manual mouse-clicking required thank you very much!

The SSIS MSBuild task was previously available in the SSIS community samples project at http://sqlsrvintegrationsrv.codeplex.com but having it as part of the MSBuild Extension Pack is definitely advantageous as this is a very widely used set of utilities. The source code has been available in the MSBuild Extension pack since changeset 54481 which was checked-in on 26th August 2010 and was included in the August 2010 Release.

Note that in order to deploy your packages after using the SSIS MSBuild task you will need to call the SSIS deployment utility and pass in the outputted .deploymentmanifest file. Information on doing that is decidedly thin on the ground so I’ll try and elucidate. The SSIS Deployment Utility is an executable that gets installed with SQL Server Integration Services workstation tools and is called dtsinstall.exe. You can call it on the command-line and pass in the path to a .deploymentmanifest file but as far as I can determine there is no way to do an unattended install (i.e. there is no way to tell dtsinstall.exe where to install the packages to, it simply opens up a GUI and prompts you for this information). This is a horribly antiquated method of deployment and I’m hunting around to see if there’s anything I’ve missed here – if you know any better please let me know.

Hope this helps!

@Jamiet

Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude]

jamiet — Fri, 20 Aug 2010 18:41:00 GMT

[This blog post assumes a familiarity with the terms Continuous Integration (CI), MSBuild & 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!]

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 not straightforward.

We are using the DB Tools in Visual Studio 2010 (aka DBPro or Datadude - 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 Apply Test-Driven Development to your Database Projects on MSDN.

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:

And just to prove that the test executes successfully in Visual Studio:

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.

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:

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 ConnectionString that we defined in the Database Test Configuration dialog:

Note the <DatabaseUnitTesting> element, we’re going to be coming back to that a little later!!

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:

When we execute that script we get some positive results and all looks peachy:

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.

Firstly you need to tell Datadude that you want to specify some different credentials and that is done by adding an attribute AllowConfigurationOverride="true" to the <DatabaseUnitTesting> element of app.config like so:

Don’t forget to build your test project after changing the app.config file!!!

This will cause datadude unit testing framework to go and search for a file called *.dbunittest.config where the * indicates either:

The name of machine upon which the tests are being run or
The name of the user running the tests

According to Jamie L datadude runs the following precedence checks:

Is an override specified in app.config? If not, use app.config
Does an override file exist called <machinename>.dbunittest.config? If so, use it, if not…
Does an override file exist called <username>.dbunittest.config? If so, use it, if not…
Fail!

“So”, you may be asking, “what goes in this *.dbunittest.config file then?”. Fair question, that’s where the <DatabaseUnitTesting> element that I mentioned earlier comes in. Copy that element from the app.config file into your *.dbunittest.config file, remove the AllowConfigurationOverride="true" attribute and change the ConnectionString property accordingly:

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:

In the Test Settings dialog go to ‘Deployment’, click ‘Enable deployment’, then ‘Add File…’. Browse to your *.dbunittest.config file and add it to the project:

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:

Notice the extra information in the output:

You’ll have to trust me that it used the new config file and therefore the new ConnectionString, although having said that you do get some useful information in the test results file (*.trx) that MSTest.exe creates:

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

Hope that helps! Comments are welcome.

Jamiet

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:

A strategy for managing security for different environments using the Database Development Tools in Visual Studio 2010

jamiet — Wed, 21 Jul 2010 20:59:00 GMT

Intro

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:

Visual Studio Team System for Database Professionals
DBPro
Datadude

For the rest of this post I’ll stick with the colloquial name that most people seem to recognise – datadude.

Regardless of which moniker you prefer I’m hoping you recognise the feature set that I am referring to here which includes:

Schema Compare
Data Compare
Offline database development
T-SQL refactoring
Database unit testing/Data generation
MSBuild integration
various other things…

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:

logins
users
role membership
user and role permissions

Security in Post-Deployment scripts

Datadude has built-in support for managing all of this stuff as we can see from this screenshot:

Notice that we have created a user, a login for that user and a role 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:

Datadude can include these objects in a schema compare
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)

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

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.

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 Script.PostDeployment.sql file that exists in every datadude project:

My chosen approach is to have a script called SecurityAdditionsWrapper.sql that gets called from Script.PostDeployment.sql. SecurityAdditionsWrapper.sql checks a SQLCMD variable to determine which environment is being deployed to and then calls the appropriate SecurityAdditionsXXX.sql script that is responsible for deploying the security model for that environment (where XXX is the environment).

I then have 3 subfolders that contain all the individual CREATE USER, role membership and permissions scripts that get called from SecurityAdditionsXXX.sql as and when required.

The screenshot below shows the contents of Script.PostDeployment.sql & SecurityAdditionsWrapper.sql and also the files as they exist in Solution Explorer:

Some things to notice:

I have a SQLCMD variable called $(DeployType) that will contain the value “DEV”, “UAT” or “PRD”
All of my security scripts exist in Post-Deployment -> SecurityAdditions
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 [Datadude] Valid SQLCMD syntax throws error at build time.

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:

You then need to pass in the appropriate value to that variable at deploy-time. We are using VSDBCMD.exe to run our deployments and the syntax to pass in a value for a variable is /p:PropertyName=PropertyValue. We are calling VSDBCMD.exe from inside an msbuild script which therefore looks like this:

$(DeployType) is of course also a property within our msbuild file, which I am drawing attention to in the bold section above.

Wrap-Up

In conclusion, I am not saying that this is the correct 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.

One more thing…

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!

I have produced a series of scripts that interrogate a named instance and produce all of the files that I explain above. Namely:

A file for each user which gets dropped into %SolutionRoot%\%ProjectName%\Scripts\Post-Deployment\Users\
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\
A file that specifies all of the role memberships, for all database principles.

Creating the CREATE USER file and the file containing the permissions for each principle is 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.

The files are stored in a zip folder on my SkyDrive at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20100721/GeneratePermissions.zip

To run the Powershell script simply unzip all the files into the root folder of your solution and issue:

>GeneratePermissions.ps1 -SQLInstance instanceName -Environment XXX

changing SQLInstance & XXX accordingly (the $Environment 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.

Hope this helps!

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

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.

UPDATE 2: I've updated the scripts again. They now create the SecurityAdditionsXXX.sql file as well as the individual CREATE USER & permissions files; SecurityAdditionsXXX.sql contains all of the calls to the relevant scripts for environment XXX.

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.

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 http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20100721/GeneratePermissions%5E_PeterSchott.zip and includes these changes (mainly to deal with roles):

1. Changed the Powershell script:
* Added a section to handle role permissions. (really don't care for the way VS does it)
* 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
* 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.
* 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. I sometimes get empty (Login = []) sections. No ideas on that yet. I fix or delete that file as appropriate.

2. Added a "GetDatabaseRoleList.sql" file. Gets the roles for the DB so we can run it through the Permissions file

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
like you did, but our permissions are so messed up right now that it doesn't matter much to me. (we have far too many permissions granted to
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 )

And one more change that needs to be made which Peter informed me of later:

Ended up changing the GeneratePermissions.ps1 file to change all of the Out-File
to "Out-File -width 500 ".

After doing that, the permissions weren't truncated at 80 characters. 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.

@Jamiet

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.