Search results matching tags 'SSDT' and 'sql server'

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

Deployment of client-specific database code using SSDT

jamiet — Sun, 10 Mar 2013 13:24:00 GMT

Yesterday I attended SQL Saturday 194 in Exeter for which many people deserve credit but especially the organisers Jonathan Allen & Annette Allen and the sponsors Fusion-IO, Confio Software, Nexus, Big Bang Data Company, Purple Frog Systems, Redgate, idera, Pluralsight, Jurys Inn Exeter & Wrox.

I gave a talk entitled Declarative Database Development with SSDT; both during the session and then afterwards on Twitter Leo Pasta asked me

would you have any extra references on how to handle client-specific changes to the DB schema

I promised Leo an answer, hence this blog post.

If I understand Leo’s question correctly he has an application that is used by multiple clients, he has a separate instance of the application for every client, and the database requires client-specific customisations; luckily for Leo there is a new feature in SSDT that is perfect for this situation. First let’s set up our solution in SSDT:

We have three database projects:

_core - This contains all the objects that are common to all clients. For demonstration purposes it simply contains a table called [Sales]
clientFoo – All the objects that are required only by client “Foo”. In this case there is view which aggregates the data in [Sales] by Customer

clientBar – All the objects that are required only by client “Bar”. In this case there is view which aggregates the data in [Sales] by Location

In order that the reference to table [Sales] in the two views can be resolved both of the client-specific projects have a database reference to _core:

It is those database references that allows objects in clientFoo/clientBar to refer to objects in _core. Now here’s the important bit. When we set up those two database references we must specify that the objects in the referenced project are intended to be in the same database:

Having objects from one database split over multiple projects is called composite projects. The effect of this is that (by default) whenever clientFoo or clientBar are deployed the objects in _core will get deployed as well. We can see this in the output when we deploy clientFoo:

Notice how even though we chose not to deploy _core two objects have been created; table [Sales] (from the _core project) and view [vSalesPerCustomer]. This is the new feature in SSDT that I spoke of above – a deployment of a project will (by default) also deploy all the objects in referenced projects where objects in the referenced database are intended to be in the same database (incidentally this functionality replaces the “partial projects” feature from previous incarnations of SSDT).

Note that you can change this default behaviour in the advanced publish settings by unchecking the “Include composite objects” box:

Hence, with the “Include Composite Objects” setting turned on, we can deploy both projects clientFoo & clientBar and both will contain table [Sales] plus their own client-specific view:

which (I hope) is exactly what Leo was after!

That’s all there is to it. A very very nice new feature of SSDT!

@Jamiet

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

Get to Know SQL Server 2012's SQL Server Data Tools [article published]

jamiet — Wed, 09 Jan 2013 23:12:03 GMT

I recently wrote an article on SQL Server Data Tools for SQL Server Magazine and it has now been published online at Get to Know SQL Server 2012's SQL Server Data Tools. In the article I offer my thoughts and opinions on SQL Server Data Tools as it exists in SQL Server 2012.

Note that the article refers to SSDT database projects only, there is nothing in there pertaining to SSIS, SSAS or SSRS. If you find that confusing then you should probably go and at least read the first few paragraphs of the article where I try to alleviate some of that confusion.

Constructive feedback is, as always, very welcome.

@Jamiet

“Add as object” or “Add as script”? [SSDT]

jamiet — Fri, 04 Jan 2013 14:15:46 GMT

In SQL Server Data Tools (SSDT) one way to create a new object is to right click on a folder in Solution Explorer and point to Add. Doing so will display this menu:

A number of options are offered for creating a brand new object (“function”, “table”, “stored proc” etc…) and one of them is “Script”. If your preference is to handcraft the DDL for every database object without resorting to a GUI then you may choose to use the “Script” option because, after all, every file is just a DDL script – why not write the thing from scratch? There is however one fundamental difference between the Script” option and all the other options that you should be aware of.

For the screenshot below I added a script and handcrafted a simple CREATE TABLE statement:

Notice that the table is defined in the dbo schema but it is not appearing in SQL Server Object Explorer under the dbo schema node. Why is that? The answer is actually pretty simple. If I select Script1.sql in Solution Explorer and hit F4 the properties window appears and we see this:

The Build Action property of the file is set to “None” which means that the file does not get included in a build and hence any objects defined within that file will not appear in our database. This is the default behaviour when you Add as script whereas the default behaviour when you explicitly add an object is to set Build Action=Build. That is the fundamental difference between adding a script and adding a specific object.

This is one of those nuances of SSDT that is an easy problem to spot when you know about it but can cause you to spend hours hunting around for a solution if you do not (believe me, I’ve been there). Be aware of it!

@Jamiet

Warning! Publish may fail on creation of a FOREIGN KEY constraint and then immediately succeed [SSDT]

jamiet — Thu, 03 Jan 2013 12:15:06 GMT

I have stumbled across a nuance of SSDT that other users should be cognizant of. I published an SSDT database project onto an existing database that already had data in it but that Publish operation failed:

The error message was:

(10036,1): SQL72014: .Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 1 
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 
"ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo". 
The conflict occurred in database "PM_EDW", table "ent.CustomerHierarchyLvl03", column 
'CstHierLvl03_EK'.

The command that caused the error was:

ALTER TABLE [fct].[ActualsDay] WITH CHECK CHECK CONSTRAINT [ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo];

In other words SSDT created a FOREIGN KEY constraint in my database and then attempted to check that existing data satisfied that constraint (hence the WITH CHECK of the command above). There was some data that violated that constraint and the Publish operation failed.
This is good, I definitely want a Publish operation to fail under these circumstances.

If you want to understand more about WITH CHECK take a read of How to load related tables.

A problem occurred though when I next published because that Publish operation actually succeeded:

even though I hadn’t changed anything in the project! If I check out the metadata of that FOREIGN KEY:

SELECT	name,is_not_trusted
FROM	sys.foreign_keys  WHERE	name = 'ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo'

I see that [is_not_trusted] equals 1.

What does that mean, exactly? Well, SSMS’s table designer provides more useful information:

[is_not_trusted] means that my new FOREIGN KEY constraint has been created but the existing data has not been checked for compliance. Ouch! Not good, not good at all!

What’s going on here? If we break down the individual steps it becomes evident:

First Publish operation starts
FOREIGN KEY constraint gets created but without the WITH CHECK option
FOREIGN KEY constraint is ALTERed to have the WITH CHECK option. This fails due to existing data violating the constraint and hence the entire Publish operation fails
Second Publish operation starts
Publish engine only checks to see that the FOREIGN KEY constraint exists, which it does. Hence, no action is taken
Second Publish operation succeeds and I’m left with data that violates a newly created FOREIGN KEY constraint

This sequence of events can leave your database in a dangerous state and hence is something that SSDT users should be aware of. In my opinion this is a bug and I have raised it as such on Microsoft Connect: [SSDT bug] Publish will fail due to FK constraint and then succeed immediately after.

Be aware, be very aware!

@Jamiet

New version of SQL Server Data Tools is now available

jamiet — Sun, 16 Sep 2012 22:03:45 GMT

If you don’t follow the SQL Server Data Tools (SSDT) blog then you may not know that two days ago an updated version of SSDT was released (and by SSDT I mean the database projects, not the SSIS/SSRS/SSAS stuff) along with a new version of the SSDT Power Tools. This release incorporates a an updated version of the SQL Server Data Tier Application Framework (aka DAC Framework, aka DacFX) which you can read about on Adam Mahood’s blog post SQL Server Data-Tier Application Framework (September 2012) Available. DacFX is essentially all the gubbins that you need to extract and publish .dacpacs and according to Adam’s post it incorporates a new feature that I think is very interesting indeed:

Extract DACPAC with data – Creates a database snapshot file (.dacpac) from a live SQL Server or Windows Azure SQL Database that contains data from user tables in addition to the database schema. These packages can be published to a new or existing SQL Server or Windows Azure SQL Database using the SqlPackage.exe Publish action. Data contained in package replaces the existing data in the target database.

In short, .dacpacs can now include data as well as schema. I’m very excited about this because one of my long-standing complaints about SSDT (and its many forebears) is that whilst it has great support for declarative development of schema it does not provide anything similar for data – if you want to deploy data from your SSDT projects then you have to write Post-Deployment MERGE scripts. This new feature for .dacpacs does not change that situation yet however it is a very important pre-requisite so I am hoping that a feature to provide declaration of data (in addition to declaration of schema which we have today) is going to light up in SSDT in the not too distant future.

Read more about the latest SSDT, Power Tools & DacFX releases at:

@Jamiet

CASTs become CONVERTs in computed columns with implications for SSDT

jamiet — Thu, 19 Jul 2012 10:03:00 GMT

I discovered a peculiar little nuance in SQL Server yesterday that I think is worth sharing. Execute the following CREATE TABLE statement:

CREATE TABLE _t(c INT,
	[S_Profit] AS (CAST (1 AS DECIMAL (19, 6)))
);

If you then go to Object Explorer and script that table out:

you see this:

Notice how the definition of the [S_Profit] computed column is not the same as it was what we created the table, it is now a CONVERT rather than a CAST.

OK, so that behaviour might be considered a little strange but its hard to see why it might be a problem. Well, it actually did create a problem for me yesterday as I shall now explain.

On my current project we are using Publish in SQL Server Data Tools (SSDT) [and by “SSDT” I mean the database projects, not the Visual Studio shell around the SSIS/SSAS/SSRS stuff] to deploy our database schema. That schema included computed columns using CAST and hence every time we attempted a publish SSDT would compare the table definition in the project with the existing table in the target and notice that they were different. This can be better better illustrated using SSDT’s Schema Compare tool (which does essentially the same comparison as is done at publish time):

Notice where I have underlined in red that the table definition in my source uses a CAST yet in the target it was changed to a CONVERT the last time we published. Notice also, underlined in green, that another expression has been rewritten to take out parentheses. Here’s a similar case where DEFAULT constraints also get rewritten:

Another one, keywords in CHECK constraints get uncapitalised* and parentheses get added in:

At publish time SSDT will generate a script that will attempt to make the target look like the source hence it is going to be making the same changes to these computed columns, default constraints and check constraints every single time you publish. Also note that any dependent objects will get affected too; e.g. dependent views may get dropped and recreated – if those views have indexes on them and there is a lot of data in those indexes then your publish operations are going to take a longggggg time.

The point is, your SSDT publishes might be working fine yet doing a lot more work than they actually need to so I would encourage you to change the default constraints, check constraints and computed column definitions in your source code to whatever SQL Server rewrites it to. The Schema Compare tool is your friend when trying to do this.

UPDATE: There was a little confusion from someone on Twitter as to where the problem lies here. Its is not something that SSDT is doing incorrectly, the re-writing is being done by the database engine.

@Jamiet

*Is “uncapitalised” a word? What is the opposite of “capitalised”?

Bitmask data insertions in SSDT Post-Deployment scripts

jamiet — Mon, 11 Jun 2012 10:11:23 GMT

On my current project we are using SQL Server Data Tools (SSDT) to manage our database schema and one of the tasks we need to do often is insert data into that schema once deployed; the typical method employed to do this is to leverage Post-Deployment scripts and that is exactly what we are doing.

Our requirement is a little different though, our data is split up into various buckets that we need to selectively deploy on a case-by-case basis. I was going to use a SQLCMD variable for each bucket (defaulted to some value other than “Yes”) to define whether it should be deployed or not so we could use something like this in our Post-Deployment script:

IF ($(DeployBucket1Flag) = 'Yes')
BEGIN
   :r .\Bucket1.data.sql
END
IF ($(DeployBucket2Flag) = 'Yes')
BEGIN
   :r .\Bucket2.data.sql
END
IF ($(DeployBucket3Flag) = 'Yes')
BEGIN
   :r .\Bucket3.data.sql
END

That works fine and is, I’m sure, a very common technique for doing this. It is however slightly ugly because we have to litter our deployment with various SQLCMD variables. My colleague James Rowland-Jones (whom I’m sure many of you know) suggested another technique – bitmasks. I won’t go into detail about how this works (James has already done that at Using a Bitmask - a practical example) but I’ll summarise by saying that you can deploy different combinations of the buckets simply by supplying a different numerical value for a single SQLCMD variable. Each bit of that value’s binary representation signifies whether a particular bucket should be deployed or not. This is better demonstrated using the following simple script (which can be easily leveraged inside your Post-Deployment scripts):

/* $(DeployData) is a SQLCMD variable that would, if you were using this in SSDT, be declared in the SQLCMD variables section of your 
project file. It should contain a numerical value, defaulted to 0.
 
In this example I have declared it using a :setvar statement. Test the affect of different values
by changing the :setvar statement accordingly.
 
Examples:
:setvar DeployData 1   will deploy bucket 1
:setvar DeployData 2   will deploy bucket 2 
:setvar DeployData 3   will deploy buckets 1 & 2
:setvar DeployData 6   will deploy buckets 2 & 3
:setvar DeployData 31  will deploy buckets 1, 2, 3, 4 & 5
*/
:setvar DeployData 0
DECLARE  @bitmask VARBINARY(MAX) = CONVERT(VARBINARY,$(DeployData));
IF (@bitmask & 1 = 1)
BEGIN     PRINT 'Bucket 1 insertions';
END
IF (@bitmask & 2 = 2)
BEGIN     PRINT 'Bucket 2 insertions';
END
IF (@bitmask & 4 = 4)
BEGIN     PRINT 'Bucket 3 insertions';
END
IF (@bitmask & 8 = 8)
BEGIN     PRINT 'Bucket 4 insertions';
END
IF (@bitmask & 16 = 16)
BEGIN     PRINT 'Bucket 5 insertions';
END

An example of running this using DeployData=6

The binary representation of 6 is 110. The second and third significant bits of that binary number are set to 1 and hence buckets 2 and 3 are “activated”.

Hope that makes sense and is useful to some of you!

@Jamiet

P.S. I used the awesome HTML Copy feature of Visual Studio’s Productivity Power Tools in order to format the T-SQL code above for this blog post.