Search results matching tags 'SQL Server', 'SQL Server Data Tools', and 'Datadude'

Which version of SSDT Database Projects do I have installed?

jamiet — Fri, 08 Feb 2013 21:15:52 GMT

SQL Server Data Tools (SSDT) is released on a very regular cadence (note that I’m talking about the database projects part of SSDT, this is nothing to do with SSIS/SSAS/SSRS). Since the first release in March 2012 there have been releases in September 2012, November 2012, & December 2012. I personally have found it difficult to keep track of not only which particular version I am using but also that which are my colleagues are using – quite simply I didn’t know how to discover the current version number. I assumed that the answer would lie within Visual Studio 2012 in the Help->About Microsoft Visual Studio menu option, when I checked there I saw this:

A version number – 11.1.21208.0. What does that mean though? To understand more we have to go to Add/Remove programs:

There are many different items listed there, let’s look at them in turn:

Microsoft SQL Server 2012 Data-Tier App Framework. This is fairly self-explanatory if you know what the Data-Tier App Framework (aka DACFx) is. If you don’t, see the notes at the end of this blog post*. The reason that you see two entries in the list above is due to my having the 32bit & 64bit versions installed – the fact that there is no differentiation between the two in the title is a bug.
Microsoft SQL Server 2012 Express LocalDB. LocalDB is the small server-less version of SQL Server that now gets deployed with SQL Server proper. LocalDB also gets installed with SSDT if you do not already have it, but you should consider it part of SQL Server itself.
Microsoft SQL Server Data Tools 2010. This was described to me as the “installation chainer” – it is the installation component that manages SSDT and its dependencies. I think of it as the SSDT shell within Visual Studio 2010.
Microsoft SQL Server Data Tools 2012. This was described to me as the “installation chainer” – it is the installation component that manages SSDT and its dependencies. I think of it as the SSDT shell within Visual Studio 2012. Note that the version number is the same as in the Help->About screenshot at the top of this article – that is because they are one and the same.
Microsoft SQL Server Data Tools – Database Projects – Web installer entry point. A stub project that gets installed when you install SQL Server. Its the SSDT database project entry point.
Microsoft SQL Server Data Tools – enu (10.3.21208.0) & Microsoft SQL Server Data Tools – enu (11.1.21208.0). These are the things we’re interested in. These *are* SSDT database projects, for Visual Studio 2010 & Visual Studio 2012 respectively. The version numbers are those for the December 2012 release; note that the build numbers are the same (X.X.21208.X), only the major and minor numbers are different.
Microsoft SQL Server Data Tools Build Utilities – enu (10.3.21208.0) & Microsoft SQL Server Data Tools Build Utilities – enu (11.1.21208.0). Everything you need to do a headless (i.e. without having Visual Studio installed) build/deploy of an SSDT database project.

So at the end of all that we have determined the following version numbers for SSDT database projects are:

Release	Visual Studio 2010 version number	Visual Studio 2012 version number
September 2012	10.3.20905.0	11.1.20905.0
November 2012	10.3.21101.1	11.1.21101.1
December 2012	10.3.21208.0	11.1.21208.0

I do not know the version numbers for the March 2012, September 2012 & November 2012 releases (if anyone out there does know please let me know in the comments) however I shall update this blog post for future releases. September 2012 and November 2012 releases now added!

I have requested that any blog post to announce future releases should contain the version numbers. Let’s hope that happens.

@Jamiet

* DACFx is described here as:

The Microsoft® SQL Server® 2012 Data-Tier Application Framework (DACFx) is a component which provides application lifecycle services for database development and management for Microsoft® SQL Server® and Windows Azure SQL Databases.
DacFX supports various database deployment and management scenarios for SQL Server and Windows Azure SQL Databases including extracting/exporting a live database to a DAC package, deploying a DAC package to a new or existing database, and migrating from on-premise SQL Server to Windows Azure. This functionality is exposed via the DACFx managed API. DACFx can target SQL Server 2005 SP4, 2008 SP1, 2008R2, 2012, and Windows Azure SQL Databases. DACFx also provides the command-line utility SqlPackage.exe for creating and deploying .dacpac and .bacpac packages. DACFx supports scenarios provided by SQL Server client tooling including SQL Server Data Tools and SQL Server Management Studio 2012.

Responses to SSDT questions

jamiet — Fri, 11 Jan 2013 22:22:24 GMT

My recent article Get to Know SQL Server 2012's SQL Server Data Tools prompted two questions to come to me via email and in the interests of sharing knowledge via search engines I thought I would answer them here rather than by simply replying by email (I hate that so much useful information gets trapped inside closed inboxes).

Question: How would you handle cases below, our main database has many objects already, we are using redgate source control to keep things under sourcecontrol but ssdt would be awesome.
Our database has some invalid stored procs etc, unfortunately we do not want to drop them, underlying objects either don't exists or has changed etc
There are linked servers or code referencing other objects stored in the database
There are staging table named automatically in the database and we do not want to include them in the project and not drop them or touch them in a deploy

JT: The overall question here seems to be “how do we prevent SSDT from DROPping sprocs and staging tables that are in the target database but are not in the SSDT project?” Fortunately SSDT has an option within it to allow exactly what the questioner requires. When an SSDT project is published one has the option to set some properties relating to that publish. One of the settings is “DROP objects in target but not in project”:

If that option is left unchecked (which is the default) then no objects will get removed from the target database.

Question: At my previous shop, I done a proof of concept with one of our databases, but never got it implemented as VS2012 (with SSDT) had not been released yet. Well, fast forward 9 or so months, and at my new shop (which is extremely agile) I've been tasked with setting up our db projects with SSDT so for the immediate short term goals/objectives we can;

Allow new or existing developers to be able to easily get the latest databases from source control downloaded from TFS, built and deployed onto our local sandbox db environments. This ideally will be done via a powershell script or something of the sort.
Be able to employ C.I. so that every night a database deployment is done to our system test servers.

3 days into it, and I've wrestled with 3 of our databases, reverse engineering them into TFS and getting them built etc. My question is, what are the best practices or guidelines for;

Allowing only Windows authentication for the local deploys to the sandbox environment. i.e. is it best to have a script with all our team user logins in a script? How would the project recognise a team member which will create a login for them so once the project is deployed they can login to their local db?
Using a 1-touch/click action (preferably outside of VS2010) to get latest, build project & deploy to local sandbox
Same as above but to a system test server

JT: There are quite a few questions there. Firstly I’ll say that its great to hear of sandboxed environments and Continuous Integration (CI) being used – I openly advocate these techniques in my blog post Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences.

Now, taking each question in turn:

Windows Auth for local deploys. I’m not sure if there’s any recognised best practise here but if developers are working on local sandboxes I would assume they would have sysadmin rights so I’m not sure there’s a need to have a script of team logins. Perhaps I have misunderstood the question in which case please clarify in the comments below. I will say this though – I think its better to have a Windows group that you can move all of your devs in and out of rather than having to modify a script each time a user joins your team.
1-click get latest, build & deploy. Scripting your build & deploy is something else I advocate in the aforementioned blog post:

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.

I’ll go further here and say that the script with which you use to build and deploy to your sandbox should be the same script as which you use for your production environment and everything in between. The script should take a parameter value to name the environment and then deploy appropriately.
See 2.

Hope that is useful. Any questions? Please put them in the comments.

@Jamiet

SQL Server Data Tools does support required variables

jamiet — Sun, 12 Feb 2012 11:58:24 GMT

Over the past few years of using datadude (aka DBPro aka Visual Studio Database Projects) I have fallen prey to a peculiar little nuance – if you forget to supply a value for a sqlcmd variable then it will simply use a default and often that is not the desired behaviour. Hence why yesterday I submitted the following suggestion to http://connect.microsoft.com/sqlserver/feedback :

Specify sqlcmdvars properties as "required to be overridden”

In my current place of work I am responsible for maintaining our datadude projects and we have another team that is in charge of deployments. Hence, when we place new properties into the sqlcmdvars file I need to tell the deployment team what values to supply for that property per environment (dev, systest, uat, prod).
Unfortunately lack of communication/human error occasionally creeps in and, for whatever reason, no value gets supplied for some property at deployment time. If this is the case the default value as specified in the sqlcmdvars file gets used instead - invariably this will be the wrong value. I would like a mechanism within SSDT of preventing this from ever happening.

One simple way to prevent this would be to specify that a sqlcmdvars property is *required* to be overridden during the deployment. In other words, never use the default. if an override is not supplied at deployment time then the deployment should fail.
Note that this stipulation should only be in place when deployment occurs from the command-line - if deploying from Visual Studio the default should be allowed (simply because Visual Studio doesn't provide a way to specify anything other than the default value supplied in the sqlcmdvars file).

https://connect.microsoft.com/sqlserver/feedback/details/724366/ssdt-specify-sqlcmdvars-properties-as-required-to-be-overridden#details

It transpires that this requested feature is already available in the forthcoming SQL Server Data Tools (SSDT) as I shall now demonstrate. This screenshot shows the project properties of a SSDT project where we define the sqlcmd variables, I have defined a variable called $(Id_value):

In SSDT the nomenclature for deploying a project is “Publish”, a function that can be found by right-clicking on a project in Solution Explorer:

Selecting that brings up the Publish dialog.

Notice how the “Publish” button is greyed out – that it because I have not supplied a value for $(Id_value); supplying a value enables the Publish button and I can go ahead and publish my project. In other words, SSDT insists that I supply a value for that variable – exactly as I requested in my Connect submission.

The same is true if I use the command-line publishing tool sqlpackage.exe. The following command does essentially the same thing as the above depicted Publish dialog:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac"

Executing that gives me an error:

Publishing to database 'MyDB' on server '.\RC0'.
*** Missing values for the following SqlCmd variables:
'Id_value'.

Here’s a screenshot showing the same:

In order to supply a value for the variable from the command-line you need to use the /v: switch like so:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac" /v:Id_value=1

As you can see, publish was successful!

So there you go, using SSDT you’ll no longer be able to fall prey to the problem I highlighted at the top of this blog post. In a later blog post I’ll show how you CAN supply a default value if you want to and also how you can override for your local environment.

@jamiet