THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Day 1 – Online and offline (10 days of SSDT)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

Its probably only fair to point out that this was intended to be “30 days of SSDT” but I laboured over writing the first 10 and then my second daughter turned up and since then time has been somewhat at a premium. Hence you’re getting the first 10 Smile. Hopefully one day I’ll find time to write the other 20!

SSDT is a tool to aid with developing databases. It is not intended for administering databases and hence is not intended as a replacement for SQL Server Management Studio (SSMS). Within that developer remit it supports two distinct ways of working, online and offline. Those two distinct usage scenarios are most obvious with SQL Server Object Explorer (or SSOX for short).  SSOX is a pane within Visual Studio that is made visible from from the View menu or by using the keyboard shortcut CTRL+\, CTRL+S and it looks something like this:

image

There are two top-level nodes:

  • SQL Server – For the online usage scenario. This node is a collection of SQL Server instances that can be expanded so you can examine what’s in them. I think of this node as a hybrid of the Object Explorer and Registered Servers panes in (SSMS).
  • Projects – offline projects

Online

The online usage scenario is quite simply for working with SQL Server databases that already exist. In the screenshot above notice that I have two SQL Server instances listed, (localdb)\Projects (more about that later in the series) and mhknbn2kdz.database.windows.net which just happens to be the server that hosts AdventureWorks on Azure. From here I can browse through the objects on those servers and interrogate them just as I would in SSMS. Within SSDT/SSOX however there is one important difference, when I make changes to the database I am not affecting the database directly, I am affecting an in-memory model of the database. This approach has two important advantages,

  • I can view the impact of a change (e.g. if I change a column name will it break any views that reference it) without actually making the change
  • I can make many changes to the model and then push them into my actual database en masse and SSDT will work out the best way of doing that. I don’t have to write the code that deploys my code.

Working online within Visual Studio is often referred to as connected development.

Offline

The offline usage scenario is all about writing new database “stuff” that you want to deploy. Ostensibly that doesn’t sound too different from the online scenario but the key here is that there is no requirement for a full-blown SQL Server instance to exist. In fact, you can download and install SSDT today (its free) and start building SQL Server databases without having to install SQL Server. Working offline within Visual Studio is often referred to as disconnected development.

When working offline you interact with SSOX as I previously mentioned but you will also find yourself using a pane inside Visual Studio called “Solution Explorer”. As anyone who has used Visual Studio will tell you Solution Explorer is where your code lives and that’s no different for SSDT. A solution is a collection of projects and each SSDT project pertains to a SQL Server database.

image

Any questions so far? Please put them in the comments section below.

@Jamiet

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Published Wednesday, September 17, 2014 9:41 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter Schott said:

I haven't played with the "online" portion of Object Explorer too much. I may have to look at that a little more closely. Most of my work has been in the offline world, making sure the project builds successfully, getting post-deploy scripts ready, etc. Sounds like I might want to use the online mode a little more if it just previews changes.

September 17, 2014 9:35 AM
 

jamiet said:

Hi Peter,

Yeah, I love the ability to make changes against a model, see errors pop up in the error window, fixerrors and then push the changes them to your database. Its a really nice flow.

JT

September 18, 2014 2:36 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 18, 2014 2:36 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 19, 2014 3:39 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 22, 2014 2:55 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 23, 2014 2:44 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 24, 2014 3:06 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 25, 2014 3:36 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 26, 2014 2:40 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 1, 2014 2:37 AM
 

Brett Gerhardi said:

Jamie, thought you might find this interesting - annoyingly, it seems if you pick the db from the "SQL Server" root that happens to be your debug database (as configured in the project), when you try to edit the object and get the script, it takes you to the offline script that is in your model.

So the distinction between offline and online mode is not quite as clear-cut as we might hope!

Sigh..

October 7, 2014 11:18 AM
 

jamiet said:

Hi Brett,

Yeah, I think that's "by design". The justification being that they're trying to lessen the gap between your code and your development sandbox. I suspect its one of those things that is going to divide opinion.

JT

October 7, 2014 11:24 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 13, 2014 2:57 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement