Team Foundation -- Oops, I shouldn't have checked that in!

Working in Visual Studio 2010 today, I was in the middle of creating a shelveset when I goofed and pressed Check In. Agh! Oh no!

I discovered that Team Foundation (tf) has a rollback command.  So I used 

>  tf rollback ...

 from the command line.

Then, I found the setting in Visual Studio to propmt on check-in.
Menu:

Tools > Options

Options dialog:

Source Control > Visual Studio Team Foundation Server > Prompt before check-in from Pending Changes window

I checked the option.  And now I get a confirmation before checking in.

The cool thing is that the change type in history shows 'edit, rollback'.  I also updated the initial changeset's comment to include a note about the rollback and the rollback changeset id.

VS Studio 2010 Database Projects

Initial Thought on VS Database Projects

NOTE: I wrote this up as notes on my first experience with database projects in Visual Studio 2010. It contains my notes and thoughts. As 'Noob-notes', nothing here is guaranteed to be correct, insightful, or interesting :)

Meta-model

It looks to me like VS 2010 creates a meta model of the a database from the source files and compares on that. It maps tables, columns, constraints, procedures(body, parameters...), to the source files that define them.

We ran into a situation where somebody defined a pk in the table.sql file instead of the normal key.sql file.  well. because the dif is on a against a meta model, it didn't matter that i changed the location of the pk def to correspond to the standard way in vs db projects. it still recognized that there wasn't a meaningful change and didn't insist on any update to the db.  Now that i've thought how you would do that it doesn't seem amazing to me so much. but i was very impressed with that level of intelligence in the being to handle the situation well.

Best Dev practices

you can develop in SSMS still, but first take your development database and sync it with the db project. when you are 'done', sync the project with your dev db.

only review the dev project for checkin, never the objects in the dev db.

don't have db source files and change scripts.  but do archive the change scripts that are generated and put into prod...  don't hand create them.

Some interesting possibilities

with db projects you would be able to move between versions of the database much more freely.

normally db changes are managed as change scripts in tfs and hopefully equivalent changes to source files for the creation of dbs from scratch. you could think of this as two possibly inconsistent representations of your DB.  for example, say a well-meaning developer creates a change script and doesn't update the 'source' for the db.  and, impossible though this would be, it passes code review and gets into tfs and production.  so you could create a fresh db that is wrong.  maybe this would be detected or maybe not.  But with one definition of the database, you don't have that problem.  You need disipline to generate (and archive) change scripts, not write them yourself.  and the tool will let you easily create a new one and compare it to an existing...or just compare the project to the existing db.

References

See http://vsdatabaseguide.codeplex.com for some really good (i hope, will read before posting) reference whitepapers.

Providing Metadata to SSIS dataflows

Recently I've begun to work with SQL Server Integration Services (SSIS) to extract data and load it into a data warehouse.  I discovered a seemingly absurd statement in an existing package.

IF 1=2 SELECT CAST(NULL AS...), CAST(NULL AS ...) ...

This was the very first statement in a long complicated SQL script that eventually...way down at the end of the file...after hundreds of lines...selected a result set that would be used in a SSIS dataflow.

Oh, this must be nonsense, I thought. Let me ask if I can take this out.  There is no reason to have an if that tests a contradiction (the opposite of tautology is, which is what we have here).


Well there is a good reason why the contradiction had to be there and I couldn't remove it.

SSIS must determine what columns are coming into it.  It does this by looking at the first select in the SQL script and assuming that the columns and datatypes it sees there are what will flow into it.  So, in this very complicated script, the IF 1=2 statement must be there.

If the sql script were simply a single select statement, the fake first select wouldn't be necessary since SSIS could get its result set metadata from the one-and-only select statement.

Clearing Up My SSL Noob Questions

In a conversation with co-workers recently I realized that I didn't have a clear, correct view of how HTTP over SSL (HTTPS) works.  So, having done some reading today, I want to record some things about HTTPS.

My main question was, where on the computer is the encryption/decryption happening? Does it happen somewhere in the windows networking stack (maybe somewhere in its TCP/IP implementation)? Seemed that handling the SSL work outside of the browser would be dangerous.  But, I wondered if that might be the case since you can use a tool like Fiddler to view the clear text content of secure traffic.

As a starting point, I set about learning about SSL. There's some excellent documentation on Mozilla. I started reading about Network Security Services(NSS). And that lead me to a good intro to SSL.

So, does SSL encrypt or decrypt in the Operating System's Network Stack? Thankfully, no.  The client application (i.e. web browser) actually does this using an SSL-capable security mechanism of its own. In Firefox, this is NSS.

Then, how does Fiddler allow you to see the traffic? Fiddler decrypts SSL traffic by acting as a "Man-in-the-middle".  It sits between the browser and the web server and establishes an SSL connection to each, decrypting the traffic that flows through it for inspection.

Find out what is causing CPU use in SQL Server

Here is a snippet shared by a co-worker for finding out what processes are eating cpu resources on SQL Server.


exec admin.dbo.usp_serverblocks @orderby = 'secprocessortime'