Search results matching tags 'SQL Server Data Tools', 'SQL Server 2012', 'sql server', and 'SSDT'

Connected development in SSDT versus SSMS

jamiet — Tue, 19 Mar 2013 16:28:00 GMT

When you install the database projects template of SSDT you get SQL Server Object Explorer (SSOX) installed as well. SSOX is a pane within Visual Studio and is the main enabler of the Connected Development experience that the SSDT team have attempted to provide.

SSOX provides some really cool capabilities that are not in SQL Server Management Studio (I hope to blog about them in the near future). In theory these capabilities make it possible for a database developer to spend all their time in SSDT (i.e. Visual Studio) thus making SSMS a pureplay DBA tool (this does of course depend on your definition of both a database developer and a DBA, but I’m not getting into that debate here).

With that in mind I have spent a few days trying to work without SSMS, preferring to live wholly inside Visual Studio instead. By and large I was able to do everything I needed to do from within Visual Studio however there were a few nuances about the experience that kept pushing me back to SSMS, I detail those nuances below.

Server groups

SSOX combines the functions of SSMS’s Object Explorer and Registered Servers pane. I don’t mind either way of working but it does mean that there is no ability to group servers in SSOX like you can in the Registered Servers pane

F6

In SSMS I regularly use the F6 keyboard shortcut to jump between the query, results & messages panes of a query window. No such keyboard shortcut exists in SSDT and they’ve already canned my request on Connect to get this fixed (even though it laughably has status “closed as fixed”).

UPDATE: See the comments below where Brett Gerhardi informed me of a different keyboard shortcut that does the same thing as F6. Actually its not quite the same, if you have multiple resultsets in your results pane then the behaviour is slightly different to F6 in SSMS - but that's not an issue you'll hot frequently.

Change Connection

The context menu in SSMS provides the ability to change a connection as well as connect and disconnect:

SSDT doesn’t have change connection and believe me, you don’t know how much you use a feature until its not there:

There’s also no hotkey to jump to “Connection” on the context menu like there is in SSMS (“C”) and I find that annoying too.

Those were the main annoyances that forced me back to SSMS. The lack of F6 was a major bugbear for me as I am a big keyboard shortcut junkie. If such things don’t bother you then you may be able to live in Visual Studio quite happily. If you have any similar experiences to share I’d be keen to read them.

@Jamiet

Publish Profile Files in SQL Server Data Tools (SSDT)

jamiet — Tue, 08 May 2012 22:07:00 GMT

I have been using SQL Server Data Tools (SSDT) both at work and on some hobby projects for quite a few weeks now and of all the new features I have to say the one that I am appreciating the most is Publish Profile files. I have been searching around on MSDN for an article that explains Publish Profile files but it seems no such article exists so I’ll attempt to surmise here.

Publish Profile files are, essentially, a collection of all the property key-value pairs that are needed to deploy a database model (i.e. a .dacpac) to some target database. Those properties include (but are not limited to):

database name
connection string
whether or not to publish SSDT projects on which the current project has a dependency
Recreate the database from scratch (or not)
Backup before deploy
Drop unknown objects in target
SQLCMD Variables

Those that have used the predecessor to SSDT, Visual Studio Database Projects, will be familiar with a lot of those properties however in that case the properties had to be specified on a case-by-case basis; typically in an msbuild script or similar. Often those scripts are not maintained by a developer – rather they are maintained by a DevOps team that are not familiar with the code being deployed and as a developer myself that’s not a situation that I’m at all comfortable with. On my most recent project where Visual Studio Database Projects were being used I faced the maddening situation where every new SQLCMD variable added to a project required an email to be sent to the DevOps team to ask them to update their scripts accordingly. As you can imagine human errors crept in (on our side more than the DevOps side) and we ended up deploying projects with the wrong SQLCMD values. Moreover, we had to deal with different DevOps folks and often they would store the values in different places; totally infuriating, believe me.

Publish Profile files make this process much easier because we can define all those properties on a per-environment basis and keep them in a dedicated Publish Profile file. The obvious benefit then is that a Publish Profile file abstracts all of the environment-specific information into a single file so deploying an SSDT project now requires only two things; the build output (i.e. a .dacpac file) and a Publish Profile file:

>sqlpackage.exe /sf:MyDB.dacpac /pr:DEV.publish.xml

The implied benefit (and this is what I really like about them) is that Publish Profile files are a source code artefact that a developer maintains the same as they would any other source code artefact, all that the DevOps people need are the build output (i.e. a .dacpac file) and an appropriately named Publish Profile file. Developers are now wholly in charge of how their code gets deployed which keeps them happy and the DevOps people have less work to do – so they’re a happy bunch too! I’m not saying that the wrong values won’t ever be supplied but at least now we know exactly where to go to fix those errors.

SSDT also provides a friendly UI for maintaining these Publish Profile files. Double-click on one and this UI appears:

Its fairly self-explanatory to fill these things out. A connection string, a database name and values for each SQLCMD variable defined within the project and you’re pretty much there. (I have written previously about one other benefit of Publish Profile files - that they can be used to make SQLCMD variables mandatory. Read more at SQL Server Data Tools does support required variables.)

One minor downside of Publish Profile files is that they get created in the root of your SSDT project (I have griped about this previously at Folders in SQL Server Data Tools) so the convention that I have been using is to create a folder called Publish in each SSDT project and move all Publish Profile files into there.

Hopefully this has given you a small taster of what Publish Profile files are all about. I’ll probably share some msbuild scripts that we’re using to deploy these things in a later blog post.

@Jamiet

P.S. As an aside, I have requested that the SSIS/SSAS/SSRS teams adopt the Publish Profile file approach in future iterations of their products. If you think that that is something you would like to see happen then click through, vote and leave a comment.

UPDATE: If you want a bit more info on Publish Profile files and sqlpackage.exe check out Ben Day's blog post Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe

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