visual studio – A Geeky Gulati

Primer on SSDT / Checking Schema into Source Control

I’ve been learning a lot of new things with this client – SSIS, SSRS, and now we’ve also implemented a workflow using SSDT. In short: This is a well supported way to check your database schema and seed data into source control. So, looking back, what are the resources that I wish I had known going in?

Installation

Near as I can tell, SSDT is installed if/when you install SQL Server Express. There’s a basic version also installed if you install VS2012 Premium and up, supposedly.

It can also be installed directly via http://msdn.microsoft.com/en-us/jj650015

Once installed, you’ll be able to do Schema Compares, Data Compares (useless to me?), and database projects (*.sqlproj). As a lot of people don’t seem to have it installed, I usually put it in a different solution than the main project solution.

Capturing an Existing Database into a Database Project

Create database project; If you choose to go blank at first then..
Right click solution, compare schema, SWITCH DIRECTIONS, Update Target
Hint: Don’t save any user objects, not till you are very comfortable with it.

As of the current version, I no longer have to have one database project, and a different server project (as I did when I looked at it in 2010 or so).

Making Changes

Can either make them in the database and then suck the changes in, or can edit them directly in visual studio – either graphically or in SQL.

A build action will verify every reference – for example, sproc referencing columns, etc. There’s a bit of a confusion around getting casing incorrect that you can turn off.

When you check the changes into source control, they are saved as the T-SQL version. Very self-documenting, diffable, etc.

Applying Changes to Local Database Interactive

Use schema compare, select/deselect what you want to update, and hit Update.

This does NOT run pre- and post- deployment scripts.

Applying Database Changes Automated / Without Visual Studio

Compile the project, get a .DACPAC file. Then use SQLPACKAGE.EXE, located usually at C:\Program Files\Microsoft SQL Server\110\dac\bin\sqlpackage.exe. Newlines added for readability.

“If I publish it, what will it do?”

/action:DeployReport
/sourceFile:Flintstone.Database.dacpac
/TargetServerName:BEDROCKCITY
/TargetDatabasename:FlintStone
/OutputPath:differences.xml
/p:DropObjectsNotInSource=false

“What is absolutely everthing that is different between the two?”

/action:DeployReport
/sourceFile:Flintstone.Database.dacpac
/TargetServerName:BEDROCKCITY
/TargetDatabasename:FlintStone
/OutputPath:differences.xml
/p:DropObjectsNotInSource=true

“Do it. (but don’t delete those users!)”

/action:Publish
/sourceFile:Flintstone.Database.dacpac
/TargetServerName:BEDROCKCITY
/TargetDatabasename:FlintStone
/p:DropObjectsNotInSource=false
/p:BlockOnPossibleDataLoss=false

It runs it with pretty colors in a console app.

With Sql Server Management Studio, caveats

In SSMS, under database, tasks, there is “Update Database-Tier Application”. This does effectively the same thing, except you don’t get to choose the options above, so it usually does too much. However, you can save off a change script and then walk the change script by hand.

Handing Data Changes

This guy wrote an excellent blog article about it: http://schottsql.blogspot.com/2012/11/ssdt-pre-and-post-deploy-scripts.html. I extended it a little bit:

Created a ScriptHistory table with a script name (PK) and when it was run, to avoid rerunning
This is for the one time refactors that move data from an old location to a new location (or going from inferred settings to explicit settings), but cannot be run again, but there is no way to detect if it’s a rerun or not.

Use MERGE statements like this article suggests: http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx

Best Practice for Security / Users / etc

I have not had a chance to do this yet, but the general idea is to create roles for permissions, have SSDT deploy the roles, but the user membership is unique per server and is handled manually. (We’re talking about situations like I am developing in COMPANY1’s domain, but the production server is in COMPANY2’s domain and Windows Auth is being used.)

In Conclusion

I watched my dba-idol Richard Svatos struggle with this in 2003.

I’ve tried to solve it in a variety of ways

I’m glad there is a usable, well supported solution now in the ecosystem which I am flourishing.

Day at the Office: Visual Studio Express 2013

Customer has requested sufficient documentation that after we leave the project, they could bring on another developer and they could get up to speed efficiently.

So, here’s my table of contents that I’m filling out (sure to change as it progresses). I’m currently working on the “Build From Scratch”, which, to avoid any future awkwardness, means “Create a blank windows 7 virtual machine, Install visual studio and Compile.”

Just to see if it was possible, I tried installing the free Visual Studio Express 2013 for Desktop instead of the paid version.

It works.

One of my resentments against Microsoft – from back when I was a Unixite and a Micro$oft Hat3r – was that the average joe can’t afford the tools. (Whereas, Perl and Python and gnu C++ were free). This is no longer true.

You can download and run VS2013 Express for Web, or Desktop. Limitation: no code analysis stuff; possibly harder to integrate 3rd party tools. Includes: NuGet package manager, Intellisense, Multiple projects per solution, etc.

You can download and run SQLExpress. Its pretty much the full blown SQL except it limits itself to 1 core and 1G of memory.

I think that pretty much covers most of the software development I’ve done in the last 14 years, and gotten paid for.

Good Job, Microsoft. I think I can be proud of you now.

Saving Wrists

My wrists have been hurting lately.. especially the right one. Wife thinks I have carpel tunnel syndrome, she might be right. I already have the ergonomic keyboard, and a trackball; I can use a mouse in either hand, with either button configuration. However, when working with code, there’s definitely a “switch hand to arrow keys” and “switch back” repetitive thing that happens. (at least for me). So, this journey to save on keystrokes and wrist movements.

Step 1: Try Not to Use the Mouse

I started by putting the mouse very far away from me. This forced me to try to find keyboard shortcuts for most of the things I was trying to do – especially switching windows. Here are some of the ones I use now; most of these are not the default keyboard combinations, but rather the secondary keyboard combinations, which I was left with after vsvim got installed.

Shft-Alt-L or Ctrl-Alt-L	Solution Explorer
F5	Build + Debug
F6 or Ctrl-Shift-B	Build
Ctrl-Alt-O	Ouptut Window
Ctrl-R Ctrl-R	Resharper Refactor
Alt-~	Navigate to related symbol
Ctrl-K C	Show Pending Changes
Ctrl-T, Ctrl-Shift-T	Resharper Navigate to Class / File
Alt-\	Go to Member
Ctrl-Alt-F	Show File Structure
Alt-E O L	Turn off auto-collapsed stuff

I also assumed a layout where I have a bunch of code windows, and all other windows are either shoved over on the right or detached and on another monitor. No more messing with split windows all over the place. By using a keyboard shortcut, wherever the window is, it becomes visible. I don’t hunt around in tabs anymore.

Step 2: VsVim

History

I first learned vi in 1983, on a Vt100 Terminal emulator connected via a 150 baud modem to the unix server provided by Iowa State University’s Computer Science department. (I was still in high school, I was visiting my brother who was a graduate student at the time). There was some kind of vi-tutor program that I went through. It was also much better than edlin and ed, which were my other options at the time.

Anti-Religious-Statement: I used it religiously till 1990, when learning LISP, I also learned to love emacs. Yes, I stayed in emacs most of the time, starting shell windows as needed.

I maintained a proficiency in both vi and emacs till 2001, when I got assimilated by .Net and left my Unix roots behind.

And Now

Having had a history with it, I decided to try vsvim and see how quickly things came back to me.

The first thing I noticed is that every other program I used, whenever I mis typ hhhhcwtyped something, I’d start throwing out gibberish involving hhhjjjjxdw vi movement commands. And pressing ESC a lot. I (am still having to) to try to train my eyes to only use vi commands when I saw the flashing block yellow cursor that I configured it to be.

I also had to un-bind a few things – for example, vi’s Ctrl-R is a lot less useful to me than Resharper’s Ctrl-R Refactorhhhhhhhhhhhhhhh I did it again. vi’s Ctrl-R “redo” I could just do :redo instead.

And where am I now? I still need to think about it a bit.. but, for example, recently I changed some code from being a static Foo.DoSomething() to a IFoo.DoSomething(), and I had to inject the class in a bunch (10+?) of constructors. The key sequences went something like this. (R# in Red, vsvim command in blue)

ALT-\ ctor ENTER	Jump to the constructor in the file (R#)
/) ENTER	Search forward for “)” and put cursor there (/ and ? go across lines, fF are in current line only)
i, IFoo foo ESC	Insert “, IFoo foo”
F,	Go back to the comma
v lllllllllll “ay	Enter visual select mode, highlight going right, to buffer A, yank (copy); cursor back at ,
/foo ENTER	jump forward to foo
Alt-ENTER ENTER	Use R# “insert field for this thingy in the constructor” thingy
Ctrl-T yafvm ENTER	Use R# Go to Class, looking for YetAnotherFooViewModel (most of the common things I work with have a very fast acronym. For example “BasePortfolioEditorViewModel” is “bpevm”. I can also use regexp stuff)
Alt-\ ctor ENTER	Jump to constructor
/) ENTER	Go to closing brace
“aP	paste from buffer A before cursor

If this sounds like complete gibberish … yes it is. But here’s the thing:

I am talking aweZUM s3krit c0dez with my computer!
My fingers are not leaving the home position on the keyboard. Not even for arrow keys.
By storing snippets of text into paste buffers (a-z, 0-9, etc), I can avoid typing those things again, which is very useful.
If I plan ahead a bit I can save a lot of keystrokes trying to get somewhere in a file.
Once I enter insert mode, Its just like normal – can still use arrow keys to move around, shift-arrow to select, etc.

It is geeky, nerdy, experimental, and it might be helping my wrists a bit. 1 week so far, still going okay.

another trick I use: variable names like “a”, “b”, “c” .. and then I Ctrl-R Ctrl-R rename them to something better later.

I would not recommend trying to learn vi without a vi-tutor type program