SSDT Talk Notes

Here’s a summary of the talk I did at the Adelaide SQL Server User Group last week. Most of the talk was a demo (only a couple of PowerPoint slides) so hopefully this is a useful reference for those who attended.

Intro to SSDT

• Evolution of DataDude
• The new way of shipping BIDS
• Ships with SQL Server 2012 and Visual Studio 2012
• Replaces ‘Database Projects’ from previous versions

• Updates every 4-6 months (in time for SQL Azure updates)

  • Ships with VS 2012 and with SQL 2012

  • Free tool - use VS shell or integrate with existing
  • Will update frequently to keep up with SQL Azure features

Connected

• SQL Server Object Explorer

• Connect to local or Azure

• Queries

• Execution Plans
• Client stats

Disconnected

• Database projects
• Model-based
• Edition-aware targeting (project properties)
  • Can switch to azure to check if database is compatible with azure
• Table designer/code view
  • Synchronised
  • Demo deleting a column
    • Can see errors (even before building) of related objects (eg. Views) that reference column
• Build creates a ‘dacpac’ (Data-tier Application Package)
  • ‘Upgrade data-tier application’ from SMSS
• Declarative model
• ‘Create New Project’ via SQL Server Object Explorer
• F5 builds and deploys database to localdb
• Snapshots
• Import .sql files
• Code analysis

Schema Compare

• Connected, Project, dacpac or snapshot
• Update changes (defaults to not losing data)

Refactoring

• Expand wildcards to column names - SELECT *
• Semantic refactoring through model - not just find/replace
• Go to Reference, Find all references

Publish database

• Directly
• Via SQL script
• DACPAC
  • C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe

“c:\Program Files (x86)\Microsoft SQL Server\110\dac\bin\SqlPackage.exe” /action:driftreport /tsn:.\sql2012

/outputpath:c:\tmp\driftreport.xml /tdn:SsdtSample

Unit testing

• Right-click on stored proc to create unit tests

3rd Party Integration and extensions

• Red Gate SQL Compare (beta)
• Version control
• Laan SQL Formatter (soon)

Further reading

• http://msdn.microsoft.com/en-us/data/tools.aspx
• http://blogs.technet.com/b/ilikesql_by_dandyman/archive/2013/03/10/installing-sql-server-data-tools-bi-and-data-projects-in-visual-studio-2012-or-visual-studio-2012-shell.aspx

Another Birthday

I am blessed with a wonderful family and friends. It was my birthday this week, and Narelle cooked a very yummy birthday tea. Pasty slice (see below) followed by baked cheesecake if you were wondering.

My sister surprised me with a present that included jars of Spring Gully pickled onions and sweet spiced gherkin rounds. This is special because not only do I like gherkins (like pickles for American readers) and pickled onions, but the Spring Gully company has been in the news this week as they were placed under administration. I hope they can turn the business around and these jars don’t become collectors items.

My friend Andrew also has a creative streak. The Star Wars storm trooper mug was part of his present. To top the night off we had two unexpected guests drop in just in time for the birthday cake, one of whom is on a flying visit from overseas.

On a sad note, I came home the next day from work to discover that one packet of Iced Vo Vo biscuits (also a present) had already been eaten by some little “mice”. That’s the trouble with having your birthday in the middle of school holidays I guess.

Nanna Jean’s Pasty Slice

• Pastry
• 1lb mince
• 1lb potatoes
• 1 big carrot
• 2 onions
• Parsley
• Grate the veggies

Nanna Jean passed away in 2009, but a few years earlier I’d asked her for the recipe for her pasty slice that I enjoyed so much. The above notes are all that I wrote down. Not very complicated. The pastry ends up like a pie crust, with the filling in the middle. Yum.

Speaking on SSDT at Adelaide SQL User Group

Today just happens to be T-SQL Tuesday, and this month’s question is “how did you come to love presenting?”

The first time I presented in front of a group? hmm that’s a tough question. Actually now that I think about it, it might have been the first time I did the kids’ story as a young teenager teaching Sunday School. Keeping the attention of 5 year olds is a tough gig, but I must have done ok as that’s something I still enjoy doing to this day. Sunday school, youth groups, camps, church conferences – they all gave me opportunities to try out “being up the front”.

Moving more to the IT-side of things, getting opportunities to present at local user groups and events has definitely been a highlight. The last couple of years I’ve also been able to work in and present at the hands-on-labs at Microsoft’s TechEd conferences, which is great fun.

One great thing about presenting is that it can be a two-way street. Sometimes you end up learning just as much from those you’re presenting to as you hope they did from you.

Speaking of presenting, it’s nice to be able to get back to the Adelaide SQL User Group next week and present on SQL Server Data Tools. It’s something I’ve been using a bit lately and thought it would be of interest to others too. I’ve been involved with this group since it first started years ago, but for the last 12 months or so I’ve been unable to make the Wednesday timeslot in person due to some family commitments, so I’ll be looking forward to catching up with some old friends.

If you’re free next Wednesday lunchtime, feel free to register and come along. It would be great to see you!