I’m Speaking! SQL Saturday Nashville and PowerShell Saturday Atlanta

Just wanted to let folks know I’ll be doing presentations at two upcoming events.

The first is SQL Saturday #145 in Nashville. That’s this weekend, October 13th. I’ll be giving my “Introduction to Data Warehousing / Business Intelligence” presentation. Here is the slide deck I’ll be using: introtodatawarehousing.pdf

My second presentation will be October 27 in Atlanta at PowerShell Saturday #003. Yep, the PowerShell guys are taking the Saturday concept and kicking off a series of PowerShell Saturdays. This is only the third, but I see many more coming in the future.

At PowerShell Saturday I’ll be presenting “Make SQL Server Pop with PowerShell”. I’ll cover both the SMO and SQL Provider during this session.

Looks like it’ll be a busy October, but I’d hurry as both events are filling up so don’t wait and get registered now!

SQL Server Data Tools in Visual Studio 2012–Snapshots

A new feature of SSDT, one not found in VS Database Projects, is the ability to do snapshots. A snapshot captures a copy of your database project as it exists at the time you take the snapshot. Once you have a snapshot there are several useful things you can do with  them.

Taking a snapshot is simplicity itself. Using the same project we’ve been using over the last few posts, right click on the project name, and pick “Snapshot Project” on the menu.





Once you do, you’ll see a new file appear in the solution. You’ll be given the chance to rename it, but for this demo I’ll just take the default name.


So now that you have a snapshot, how do you use it? Well let’s start with a database comparison. First, let’s make a change of some type to the database. I’m going to use the safe refactor (see my blog post on this). I’m going to open the Employee table in the HumanResources schema and safe refactor the JobTitle column to become JobName.

With the file saved, let’s now decide we want to compare our current database project to what we’ve done in the past, namely our snapshot. The same tools we used for schema compare can also be used with a snapshot.

Right click on the snapshot and pick Schema Compare (for more info see my previous post). When the schema compare window appears the snapshot will be in the source side. Over on the right, use the pick target to pick the current project, then click compare. You will now see the differences between your snapshot and your current project.



So looking at differences is nice, but what if you want to dig in and see the entire snapshot? If you’ll notice, the snapshot is created as a dacpac file. If you happened to notice in the earlier post on creating an SSDT project, in addition to importing from an existing database, we also have the option to import from a dacpac file.

Right click on the solution, and pick Add, New Project.


Now pick a new SSDT project and give it a name. I’m going to name mine after the snapshot. Now right click on the project, and pick Import. then pick “Data-tier Application (*.dacpac).


Now navigate to the snapshot dacpac file you created and import it. (Hint, to find out where it’s at, before you start the import right click on the snapshot file, and pick properties. One of the properties is “FullPath”, it will hold the full path / file name of the snapshot.)




Once you click start, your new project will have an exact copy of your project at the time the snapshot was created.

Move down the AdvWorks_20120821_07-19-43 project tree and open up the Human Resources schema, then the Employee table in both projects. Assuming you were following along you will now see the snapshot as the Job field as JobTitle, the name prior to the change.

Within our AdvWorks project, also navigate down to the Employees table, and you’ll see it has the new name of JobName for the job column.












While snapshots will capture versions of your database over time, be aware they are not a substitute for good source code control. Snapshots are manually created, and are part of the project. Source control will capture each version upon check in, but more importantly serves as a good back up.

Snapshots can also be useful when asking for help. Simply take a snapshot and e-mail it to your friend. They can simply import it and create a copy of your project. Much easier than trying to zip up the entire project and mail it around.

SQL Server Data Tools in Visual Studio 2012–Schema Comparison

A feature carried over, but improved upon, from Visual Studio Database Projects is the Schema Comparison tool. The tool allows you to compare one database to another, a database to your project (or vice versa). It will also allow you to do comparisons between dacpacs and projects or databases (or them to dacpacs).

Doing one is pretty simple. We’ll keep using the project we’ve been using in previous lessons. For today’s example I’ve done a safe refactor on the JobTitle column in the HumanResources.Employee table, renaming it to JobName. I have also added a table and view to the dbo schema. For your example simply rename something in your project. After you make the changes, don’t publish them! We need something different for this example.

To kick off the schema compare, go to the SQL menu, then pick Schema Compare, New Schema Comparison. The dialog will be mostly empty, we’ll start with the upper portion.


As you see, on the left we hit the drop down and will pick Select Source.


Here you have three choices. The top is to pick a project as your source. The second will let you pick an existing database as a source. The final choice will allow you to pick a dacpac file. For this example, we’ll pick the database that we created from our AdvWorks project, one prior to the changes you just made.

On the right, hit the drop down and for the target pick your AdvWorks project. Now click the Compare button in the upper left. SSDT will do the comparison and populate a dialog with the results.


In the upper half you will see a list of all the changes found. If you click on a change, the Object Definition area in the lower half populates with the code that creates the objects, and highlights the differences. In this example you’ll see that our source system has the JobTitle column, whereas the project on the right has our change to JobName.

The check boxes allow you to select or deselect individual changes. For example, you could go to the upper half which is designated to delete the ArcaneCode table and view and uncheck them. Then when you apply the changes these would be left untouched.

If you have a lot of changes you wish to omit, you can buik apply the exclusion (and likewise the inclusion) of files. Right click on a grouping (here the groups are changes and deletes), in the popup menu you can choose to Include All or Exclude All.

To apply the changes, simply click the Update button.

SNAGHTML5b39106You can choose how to group the comparisons findings. In the schema comparisons toolbar, you can choose to group by Action (the default), by the Schemas, or by Type (types being tables, views, etc).




You could have a situation that could result in data loss, for example the deletion of a table. By default SSDT will block any changes that will result in data loss in the target. Your target, however, might be a test database in which you don’t care if you lose data. There may also be other options you wish to override.

To see the options for applying updates, click on the gear immediately to the left of the grouping toolbar button.


Here you can see a vast list of options available to you, that will affect the way in which SSDT applies updates to the target. You can see the “Block on possible data loss” option under the mouse, and could uncheck it to force your changes. There’s a lot of options here, so scroll through the list to see what other options you might be interested in.

Between the dropdowns for source and target is a little double arrow symbol. Click it will swap the source and target. Do it now, so the database now becomes the target and the project becomes the source. Now run the Compare again.

SNAGHTML5bcadd9You should now see the button between the Update and Options buttons become enabled. This is the Generate Script button, and becomes active when the target is a database.


Click it an a new window will appear with all the T-SQL that will change the target database to make it in sync with the project.

Let me stress something. This is not the way you should apply changes to your databases! The publish feature is the proper way to do that, in it are options to generate incremental updates.

This option is more for times when you have a test database you want to quickly get in sync, but for whatever reasons don’t want to create a full publish profile.

The Schema Comparison tool is surprisingly useful. A true story, I was working on a project one time that had considerable changes to an existing database. It was a short term project, so not a lot of time. In theory the source database was supposed to be left unchanged. Note I say “in theory”.

Bright and early Monday morning the very new to the job DBA comes to us and says “Oh by the way we had some issues over the weekend so I had to apply a bunch of changes.” When we asked for his scripts he just shrugged and said “I didn’t keep any of that junk. The changes are in the database just go get ‘em.” And with that wandered away, presumably to provide more sunshine to the lives of other dev teams.

At one time this would have been a major set-back. Fortunately the Schema Comparison tool quickly brought our project up to date. We were able to see the database changes before we applied them to our project, and in a few cases exclude the automatic changes and instead make them manually in our project.

Play around with the Schema Comparison tool, you can run it without actually applying changes. Knowing how to use it will help you on that fateful day when a new dba spreads a little sunshine into your own life.

SQL Server Data Tools in Visual Studio 2012–Customizing the Table Designer Layout

With this post I want to show you a few of the nice shortcuts provided to you in Visual Studio SSDT for quickly customizing the layout of your designers. A few of the items only apply to the table designer, but many apply to other windows within Visual Studio, no matter what project type is being hosted.


Number 1 points to the pane swap button. Clicking it will simply swap the positions of the grid and T-SQL windows, like so:


The double bar pointed to by number 2 is the resizing handle. Click and drag to adjust the amount of space used by either pane.


Note the change of the cursor shape when it’s hovered over the double bars.

There are three buttons pointed to by number 3. The middle one is the default, and indicates you want to split the panes horizontally. If you click the left most of these 3, it will split the panes vertically.


Vertical mode is really nice when you have a super wide screen monitor. As you can see, the three buttons have now shifted to the bottom center of the screen, next to the mouse in the above image.

What if you are working on a really small screen, and don’t even have enough real estate to work comfortably with any size split? Well that’s where the right (or bottom if vertically split) button comes in. Click it to shift to tabbed mode. (Note, I suggest you shift back to the default horizontal split first, otherwise the tabs will be on the right instead of the bottom and not quite as easy to use).


The last button, number 4, is for the T-SQL pane. It’s also found in almost all code editor windows in Visual Studio. Using it you can split the code view so you can see two different sections of you code at the same time.


Great for working with especially large code bases. And this split should exist in any text editor, not just the designer. Whether it’s straight T-SQL, VB.Net, C++, F#, or C# it should work for you.

For a typical desktop user, you’ll probably set these once and forget. But for folks like me who travel a lot, these are a real blessing. When I’m at home, with my laptop hooked up to my 25inch wide screen monitor, I can quickly shift to split screen vertical mode to take advantage of all that width.

When I’m on the road though, working on my laptops small screen (12 inches), I can shift back to horizontal mode, or more often (for me) tabbed mode, for doing my work.

Experiment with different layouts, and find out what works best for you!

SQL Server Data Tools in Visual Studio 2012–Safe Refactoring

With Visual Studio Database Projects (VSDB), you entered into Safe Refactoring mode through the Schema View window. But in SSDT, the Schema View window no longer exists. So how the heck do you do it??

Well first off, let’s define safe refactoring for those who may not have been familiar with the feature in VSDB. It allowed you to right click on a column name, and pick rename. You could then enter a new name, and hit the preview button. Visual Studio would comb through your source code and find every occurrence of that column name for that table and show it to you. If you clicked OK, Visual Studio would then go through and make the change everywhere for you, ensuring you didn’t miss anything.

Before we can do a safe refactor, we need to have something to refactor, and to test with. For this demo, we’ll create a simple view to read the dbo.ArcaneCode table we created in the previous post. Since there is no folder in the dbo schema for views, we’ll first have to create it.

Still working with our AdvWorks sample, open the dbo folder. Right click on it, and pick Add, Folder. Name the folder Views. Let me interject that strictly speaking, this isn’t a requirement. We could have placed the new view source file we’re about to create anywhere. But keeping a file/folder structure consistent with this and other SSDT projects will make maintenance far easier.

OK, once the folder is there, right click on it, again pick add, only this time pick a new View. Name the view dbo.vwArcaneCode.

CREATE VIEW [dbo].[vwArcaneCode] AS 
SELECT [BlogUrl], [BlogAuthor]
FROM dbo.ArcaneCode

And save it. Now return to the table designer for the new ArcaneCode table.

Let’s decide we don’t like the name BlogUrl, and we’d rather call it BlogSite. Well, first we could try putting our cursor into the T-SQL area, and just typing over BlogUrl with BlogSite. What happens?


Well, as you can see above, the designer on the upper half changes, but the column name in Create Index did not update. Not nearly what we wanted.

Let’s start over. Restore the name in the T-SQL area back to BlogUrl. Now go into the grid at the top, and change it there.


Hey, that’s better! Once we tabbed out of the Name column (or clicked elsewhere) it changed the name in all the locations in the T-SQL area. Just what we wanted!

Or is it?

Remember, we also used this column in the vwArcaneCode. Go take a look at its code.


As you can see, it remained unchanged. Even worse, there’s now a big red squiggly under the BlogUrl name, indicating we have now created an error in our project. Sigh. OK, one more time.

Go back to the table designer, and reset the name back to BlogUrl. Now we’re finally ready to do this the right way.

Put your cursor somewhere in the column name in the T-SQL area, then right click on the column name. Note, this will not work if you try it in the grid area at the top!

From the menu, pick Refactor, Rename.


You’ll now see a dialog appear, giving you a place to enter a new name. Change the name to BlogSite, and make sure the Preview Changes is checked on, then click OK.


Once you do, a new window will appear. It will show you everywhere the change would be made.


Looking at the window,  the upper part shows you all the files and the line of code in the file for which a change would be made. If you click on a line, the new version of the code will appear in the lower window. Ahh, there’s the view, and you can see it is being shown with the new change that will be made.

Note that no change has occurred as of yet. You must click the Apply button for anything to actually change. You can also click Cancel to abandon the change.

If you need to know everywhere a column name is used, but don’t want to change it, there’s an easy way to do that too. When we right clicked on the column name and picked Refactor, well a few menu options down was another option called “Find All References”. Picking that will populate a window in your Visual Studio environment.


When you click on a line in the above window, your central display in Visual Studio changes to now show the file you clicked on in this window.

There you go, you can now not only safely refactor column names in Visual Studio 2012 SSDT, but you can also find all references to that column within your project.

SQL Server Data Tools in Visual Studio 2012–Table Designer–Other Objects


Above is the screen shot of where things were left at the end of the previous post. We had just added a new table and used the designer to create four columns, along with their data types. We also saw how we can edit the table in T-SQL and see updates in the designer’s grid above.

But what’s all that stuff to the right of the grid? It would seem to list the various objects that would be associated with a table. In this case there’s only one, the unnamed Primary Key. But through this are we can also add new objects.

Let’s add a simple index. Right click on the Indexes (0) area. from the menu pick Add New, then for this example we’ll pick a standard Index.



When you do, a new index name appears below the Indexes (0) area, with a default name.


Since this is going to be for the BlogUrl, I’m going to change the name to IX_ArcaneCode_BlogUrl. You’ll see the Indexes collection area has updated to reflect the new name, and the number in parenthesis has been updated to (1), to reflect the number of Indexes.

In the T-SQL area at the bottom, you’ll see some new T-SQL setup and ready for you to update. Here, all you have to do is change the [Column] to be the actual field name you wish to use in the index. In this case, [BlogUrl].


Think of the new designer as a combination of easy to use UI with a code generator. Creating other objects works just like you’ve seen here. You right click, pick what you want to add, and Visual Studio SSDT inserts the code template read for you to update.

Simulating SQL Server Work with PowerShell

No, I’m not talking about simulating your job so you can sit home in your PJs and play Call of Duty. (Although if you work out a way to do so, call me!) What I’m speaking of is emulating a workload on your server.

There’s many reasons for wanting to do this. For folks like me, who do a lot of demos, it can be a good way to get your server busy so you can demo things like DMVs or the SQL Provider. Another use would be stress or performance testing your applications. Ensuring you get good retrieval times when the server is loaded.

I have to give a little shout out here to my friend and fellow MVP Allen White (Blog | Twitter). I had attended one of his sessions back at a SQL Saturday, and when I downloaded his samples found a “SimulateWork” PowerShell script among the sample code. In Allen’s script he used the .Net SQLClient library to do his work. I decided to emulate the concept but to rewrite the entire thing using the SQL Provider. I borrowed a few of his queries so I could be sure my results were similar.

The script is pretty simple. I have a function that will load up the SQL Provider, if it’s not already loaded. Next is the SimulateWork function. It has one parameter, how many times you want to execute the code in the loop.

Within the function I load up a series of T-SQL queries against the AdventureWorks2012 database using “here” strings. I then execute them using the Invoke-SqlCmd cmdlet. Finally I have a little code which calls the functions.

My goal with this script was to get the server ram loaded up and simulate some I/O so I could demo a few simple things. Astute observers will notice that, because these are just executing the same T-SQL commands over and over, for the most part SQL Server will just hit its plan cache and memory each time. If those are important to you, I’d suggest altering the T-SQL commands to include a where clause of some sort then each time through the loop add your new where condition to the variable holding the T-SQL.

So without further ado, here’s the full script.

# Simulate Work
# This routine will simulate work being done against a SQL Server. Ideal
# for demoing things like the SQL Profiler or DMV Stats. 
# The T-SQL queries were tested against the AdventureWorks2012 database. You
# may have to alter some queries if you are on previous versions of
# AdventureWorks.
# Author......: Robert C. Cain
# Blog........: http://arcanecode.com
# Twitter.....: http://twitter.com/arcanecode
# Last Revised: July 17, 2012
# Credit...: This script is based on one by SQL MVP Allen White.
#   Blog...: http://sqlblog.com/blogs/allen_white/default.aspx
#   Twitter: http://twitter.com/sqlrunr
# In his original script, he used the System.Data.SqlClient .Net library to 
# simulate work being done on a SQL Server. I liked the idea, but rewrote 
# using the SQL Provider. A couple of the SQL queries I borrowed from 
# his routine. 
# Other uses: The techniques below might also be a good way to stress test
# a system. Alter the t-sql (and probably variable names) and away you go. 

# Loads the SQL Provider into memory so we can use it. If the provider is 
# already loaded, the function does nothing. This makes it safe to call
# multiple times. 
function Load-Provider
  # Get folder where SQL Server PS files should be
  $SqlPsRegistryPath = "HKLM:SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
  $RegValue = Get-ItemProperty $SqlPsRegistryPath
  $SqlPsPath = [System.IO.Path]::GetDirectoryName($RegValue.Path) + "\"
  # Check to see if the SQL provider is loaded. If not, load it. 
  [String] $IsLoaded = Get-PSProvider | Select Name | Where { $_ -match "Sql*" }

  if ($IsLoaded.Length -eq 0)
    # In this case we're only using the SQL Provider, so the code to load the
    # SMO has been commented out. Leaving it though in case you copy and paste it from somewhere
    # and need it. 
  # ----------------------------------------------------------------------------------------
    # Load the assemblies so we can use the SMO objects if we want. 
    # Note if all you need is the basic SMO functionality like was in 2005, you can get away
    # with loading only the first three assemblies. 
  # ----------------------------------------------------------------------------------------
    $assemblylist = 
    "Microsoft.SqlServer.ConnectionInfo ", 
    "Microsoft.SqlServer.SmoExtended ", 
    "Microsoft.SqlServer.Dmf ", 
    "Microsoft.SqlServer.SqlWmiManagement ", 
    "Microsoft.SqlServer.Management.RegisteredServers ", 
    "Microsoft.SqlServer.Management.Sdk.Sfc ", 
    "Microsoft.SqlServer.SqlEnum ", 
    "Microsoft.SqlServer.RegSvrEnum ", 
    "Microsoft.SqlServer.WmiEnum ", 
    "Microsoft.SqlServer.ServiceBrokerEnum ", 
    "Microsoft.SqlServer.ConnectionInfoExtended ", 
    "Microsoft.SqlServer.Management.Collector ", 
    foreach ($asm in $assemblylist) {[void][Reflection.Assembly]::LoadWithPartialName($asm)}

    # Set the global variables required by the SQL Provider    
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    # Load the actual providers  
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    # The Types file lets the SQL Provider recognize SQL specific type data.
    # The Format file tells the SQL Provider how to format output for the 
    # Format-* cmdlets.
    # First, set a path to the folder where the Type and format data should be
    $sqlpTypes = $SqlPsPath + "SQLProvider.Types.ps1xml"
    $sqlpFormat = $sqlpsPath + "SQLProvider.Format.ps1xml"
    # Now update the type and format data. 
    # Updating if its already loaded won't do any harm. 
    Update-TypeData -PrependPath $sqlpTypes
    Update-FormatData -prependpath $sqlpFormat
  # Normally I wouldn't print out a message, but since this is a demo
  # it will give us a nice 'warm fuzzy' the provider is ready
  Write-Host "SQL Server Libraries are Loaded"


# This function simply loads a series of SQL Commands into variables, then
# executes them. The idea is to simulate work being done on the server, so
# we can demo things like SQL Profiler. 
# Parameters: $iterations - The number of times to repeat the loop
function SimulateWork ($iterations) 

  $sqlSalesOrder = @"  
  SELECT d.SalesOrderID
       , d.OrderQty
       , h.OrderDate
       , o.Description
       , o.StartDate
       , o.EndDate
    FROM Sales.SalesOrderDetail d
   INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
   INNER JOIN Sales.SpecialOffer o ON d.SpecialOfferID = o.SpecialOfferID
   WHERE d.SpecialOfferID <> 1

  $sqlSalesTaxRate = @"  
       , st.TaxRate
    FROM Sales.SalesTaxRate st
    JOIN Person.StateProvince sp 
         ON st.StateProvinceID = sp.StateProvinceID
   WHERE sp.CountryRegionCode = 'US'
   ORDER BY st.TaxRate desc ;
  $sqlGetEmployeeManagers = @"
  EXECUTE dbo.uspGetEmployeeManagers 1

  $sqlSalesPeople = @"
  SELECT h.SalesOrderID
       , h.OrderDate
       , h.SubTotal
       , p.SalesQuota
    FROM Sales.SalesPerson p
   INNER JOIN Sales.SalesOrderHeader h 
         ON p.BusinessEntityID = h.SalesPersonID ;

  $sqlProductLine = @"
       , ProductNumber
       , ListPrice AS Price
    FROM Production.Product
   WHERE ProductLine = 'R'
     AND DaysToManufacture < 4
   ORDER BY Name ASC ;

  $sqlHiringTrend = @"
  WITH HiringTrendCTE(TheYear, TotalHired)
    (SELECT YEAR(e.HireDate), COUNT(e.BusinessEntityID) 
     FROM HumanResources.Employee AS e
     GROUP BY YEAR(e.HireDate)
   SELECT thisYear.*, prevYear.TotalHired AS HiredPrevYear, 
    (thisYear.TotalHired - prevYear.TotalHired) AS Diff,
    ((thisYear.TotalHired - prevYear.TotalHired) * 100) / 
                 prevYear.TotalHired AS DiffPerc
   FROM HiringTrendCTE AS thisYear 
        HiringTrendCTE AS prevYear
   ON thisYear.TheYear =  prevYear.TheYear + 1;

  $mi = $env:COMPUTERNAME + "\SQL2012"  
  Set-Location SQLSERVER:\sql\$mi\databases\AdventureWorks2012

  for ($i=1; $i -lt $iterations; $i++) 
    Write-Host "Loop $i"
    $outSalesOrder = Invoke-Sqlcmd -Query $sqlSalesOrder -ServerInstance $mi -SuppressProviderContextWarning
    $outSalesTaxRate = Invoke-Sqlcmd -Query $sqlSalesTaxRate -ServerInstance $mi -SuppressProviderContextWarning
    $outGetEmployeeManagers = Invoke-Sqlcmd -Query $sqlGetEmployeeManagers -ServerInstance $mi -SuppressProviderContextWarning
    $outSalesPeople = Invoke-Sqlcmd -Query $sqlSalesPeople -ServerInstance $mi -SuppressProviderContextWarning
    $outProductLine = Invoke-Sqlcmd -Query $sqlProductLine -ServerInstance $mi -SuppressProviderContextWarning
    $outHiringTrend = Invoke-Sqlcmd -Query $sqlHiringTrend -ServerInstance $mi -SuppressProviderContextWarning


# This is the code that executes the above functions. To change the workload
# simply change the number of iterations. 

# Number of times to repeat the work simulation. 
$iterations = 100 

Write-Host "Starting simulated work"
SimulateWork $iterations
Write-Host "Done Working"

SQL Server Learning Resources for Beginners

Over the last couple of months I’ve been putting up some of my favorite learning resources. Recently my friend Robin Hunt (twitter | linkedin) at ThinkData Solutions asked me for some recommendations for some SQL Server learning resources for beginners. I thought I’d share that list here as well.


Below are some of my favorite books on the subject. The links are to Kindle format, mostly because I’m a Kindle junkie, but you can also get the paperback version from a link on each page.

Microsoft SQL Server 2008 Step by Step – Any of the step by step books are good quality. There’s no 2012 version of this that I know of, but I’m sure one will be coming.

Microsoft SQL Server 2012 A Beginners Guide – This is a good resource if you are totally new to SQL Server. It’s very complete, covering all aspects of SQL Server Administration.

Introducing Microsoft SQL Server 2012 – This book is an easy read, so even beginners shouldn’t have any issues understanding it. It focuses mostly on new features in 2012, so it’s not quite a comprehensive book. But the Kindle version is FREE, so it makes a good resource for this list.

Professional Microsoft SQL Server 2012 Administration – If you already have some good technical background you might want something one step above a beginner book, but is still easy for people new to SQL Server to understand, then this is a really good book to get. (Disclaimer, I’m a co-author of this book.)


SQL Share – This is a really unique site. It’s designed for the busy professional. All the videos here are very short, generally in the 10 minute range, and focus on one very specific topic. As I write this, the featured video is on working with foreign keys. It doesn’t have anything in the way of an overall course though, so I’d suggest it as a great resource to learn more about a topic you are interested in, or perhaps read about but don’t quite understand. (Disclaimer,  I have some videos on this site.)



Another good resource is Twitter. Yes, Twitter. Do a search on the #sqlhelp hash tag and you’ll see all sorts of great questions being asked and answered. The SQL community does a great job of monitoring this hash tag and providing assistance.

SharePoint BI (Business Intelligence) Training Resources

SharePoint is a huge topic unto itself, so I wanted to provide some links that focus on learning how to use SharePoint in the context of Business Intelligence. 

A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That’s because I’m lucky enough to work with some of the best people in the field. Also, in the case of the books I’ve linked to the Kindle version where possible, mostly because I’m a Kindle junkie. There are paper versions of the books, and you are free to buy from your favorite retailer.


SharePoint 2010 Business Intelligence 24 hour Trainer – This is a really cool book, in that it’s not just a book. It comes with a DVD loaded with video lessons on how to use the various tools in SharePoint for doing BI. This is the first book to buy if you are new to doing BI in SharePoint.

Business Intelligence in Microsoft SharePoint 2010 – This is a great book which provides an introduction to all of the BI Services available within SharePoint 2010.

Microsoft SharePoint 2010 Business Intelligence Unleashed – Like most of the books in the “Unleashed” series, this takes a much deeper dive into the tools than the previous two books. A good choice once you are ready to move beyond the beginner stage.


There aren’t many blogs that focus solely on SharePoint for BI, so I’ve picked out two sites that have a lot of SharePoint for BI content on them.

Data Inspirations -  This blog focuses on all aspects of BI, one of the lead bloggers is Stacia Misner, co-author of the BI in Microsoft SharePoint 2010 book listed above.

BIDN – Many experts in the BI field, myself included, contribute to the Business Intelligence Developer Network. Here you’ll find a wide variety of topics in the BI field, including many on SharePoint BI.


Pragmatic Works Webinars – On our website we have a big catalog of past webinars (all of which are free to watch), many of which focus on PowerPivot.

Pluralsight – Pluralsight has an extensive catalog of other courses you can pick from. It’s subscription bases so there is a modest fee (starts at $29 US per month last I checked) but well worth it for the training you can get. There’s also a free trial.

For a quick link direct to this post, you can use http://bit.ly/arcanespbi

PowerPivot Training Resources

I’ve been asked to provide links to some useful resources for learning about PowerPivot. Below are a list of my favorite blogs, books, and other sites to learn from.

A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That’s because I’m lucky enough to work with some of the best people in the field. Also, in the case of the books I’ve linked to the Kindle version where possible, mostly because I’m a Kindle junkie. There are paper versions of the books, and you are free to buy from your favorite retailer.


Professional Microsoft PowerPivot for Excel and SharePoint – This book covers all aspects of PowerPivot, from using it to installing it to configuration. Everything you want to know in one volume. I wouldn’t particularly call this a beginners book however, it assumes you are competent in BI, Excel, and SharePoint.

Microsoft PowerPivot for Excel 2010: Give Your Data Meaning – This is a good first book to get, it goes deeply into the use of PowerPivot within Excel.

Practical PowerPivot and DAX Formulas for Excel 2010 – Once you are comfortable with PowerPivot, you’ll want to learn more about DAX, Data Analysis eXpressions, the set of functions used to do advanced calculations and aggregations in PowerPivot. I’m a big fan of the way the author, Art Tennick does his books. It’s the Problem – Solution approach, where he demonstrates a common problem then shows one or more ways to solve it. Art also has books on MDX and DMX you should check out.


PowerPivot Pro – Probably one of the best blogs around, Rob and Kasper provide excellent content.

Denny Lee’s Blog – Denny works for Microsoft and is part of the PowerPivot team. He provides some really great insights, and is co-author of the Professional Microsoft PowerPivot for Excel and SharePoint book above.

PowerPivot Info – Not so much a blog but a blog aggregator, this site brings the best PowerPivot content on the web to the forefront.


Pragmatic Works Webinars – On our website we have a big catalog of past webinars (all of which are free to watch), many of which focus on PowerPivot.

Pluralsight – A little shameless self promotion here. I did a complete course on PowerPivot for Pluralsight. This includes both using PowerPivot from Excel and managing PowerPivot within SharePoint. In addition, Pluralsight has an extensive catalog of other courses you can pick from. It’s subscription bases so there is a modest fee (starts at $29 US per month last I checked) but well worth it for the training you can get. There’s also a free trial.

For a quick link direct to this post, you can use http://bit.ly/arcanepivot

Using TFS2010 with Visual Studio / BIDS 2008 and SQL Server Management Studio

When I come to a customer site, I often have to help them get setup with TFS (Team Foundation Server) 2010, Microsoft’s source code control / ALM (application lifecycle management) system. This is so they can work with their BIDS (Business Intelligence Developer Studio) projects as a team, giving the added benefit of source code control. I’ve had to do this often enough I wanted to record the steps for my own use, and hopefully others too.

Installing the TFS 2010 tools for Visual Studio / BIDS 2008

First off, thanks to Derek Miller for covering most of the steps involved in his blog post http://derekjmiller62.wordpress.com/2010/10/19/using-tfs-2010-with-bids-2008/. I won’t go into the detail he did, but will summarize into these basic steps.

1. If you haven’t installed Visual Studio 2008 Service Pack 1, do so by downloading it and installing.

2. Next, you will need to install the Visual Studio 2008 Team Explorer.

3. After installing Team Explorer, you will have to go back and reinstall VS SP1 (from step 1). Don’t skip this step! Team explorer has some older components that overwrite the SP1 components, and you will have reinstall them.

Now this next part I really haven’t seen anywhere else and was a real pain to find, and thus is the main reason for this post. During the SP1 install, we often see “Visual Studio SP1 Installation Failed”. Checking the error log, buried deep you will find “Returning IDOK. INSTALLMESSAGE_ERROR [Error 2902. An internal error has occurred. …”

When you see this, go to your Control Panel, and then to Add Remove Programs. Look for a program called “Microsoft Visual Studio Web Authoring Component” and uninstall it. This is actually installed as part of the Office suite, and you don’t really need it since you likely have much more powerful web authoring tools, or since you are doing BI development won’t be doing an web development in Microsoft Office.

After uninstalling it, SP1 should then install, and you are ready for step 4.

4. Install the Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010. That probably took you longer to read than it actually will to install. After installing, it may prompt you to reboot. Even if it doesn’t ask you should reboot anyway, we’ve seen a few times when we weren’t able to connect until we rebooted.

After that you should be able to go into Visual Studio and go to Tools, Connect to Team Foundation Server. If you still have problems connecting, I will refer you to Derek’s post where he describes some registry entries you can try. So far we haven’t found them necessary, but you may.

Installing the TFS 2010 Tools

Note that there is one big limitation to using TFS 2010 with VS2008. You can connect to a TFS site and upload your solutions and projects, but you can’t create a new team site with VS2008. To do so, you will need the VS2010 shell with the TFS components, a free download.

Installing TFS 2010 for SQL Server Management Studio (SSMS)

Now that you have BIDS all setup to work with TFS, it only makes sense to make your SQL Server Management Studio (SSMS) also work with TFS. Joseph Jun has a great blog post that goes into all the nitty gritty of how to do this. The short version though, is after you install the TFS 2010 tools in the step above (and they are a prerequisite) you need to install the Team Foundation Server MSSCCI Provider 2010.

After the install, you should see a new Source Control menu option under the File menu in SSMS. From here you can launch the TFS 2010 management shell or open an existing SSMS project / solution. If you have a solution you need to add, simply right click on the solution in the Solution Explorer window and pick Add to Source Control.

Visual Studio Database Projects

Note that if you are using Visual Studio Database Projects, any SQL Server 2008R2 development must be done in Visual Studio 2010. VS2010 is already setup to talk to TFS 2010. If you are using VS 2008 database projects to build a SQL Server 2008 (non-R2) database, then with the steps above you should be good to go for checking in your database project into TFS.

And away we go!

And with that you should be setup to manage your BI Development in Team Foundation Server 2010. It’s a lot of work, but well worth the effort. Using TFS will let your BI staff work as a team to develop projects. Additionally you have the benefit of source code control, something invaluable in the case of package corruptions or needing to track history.

Task Factory–File Properties Task

My last video on the Task Factory Compression Task was well received, so I thought I’d build on it with a video on the File Properties Task. It’s a cool little tool that will grab any of the various attributes associated with a file. If you want to see more about Task Factory, you can head on over to my employers website, Pragmatic Works.com

Data Dude Webinar for Pragmatic Works

Just wanted to let everyone know I’ll be doing my first webinar for Pragmatic Works this Thursday, June 9th 2011. It will be at 11 am Eastern time.

I’ll be doing an introduction to “Data Dude”, Visual Studio Database Projects. We’ll cover such things as generating a project from an existing database, using the safe refactoring tools, and generating sample data. There’s no cost, and I hope you can join us live so I can answer any of your questions. If you can’t, then we’ll be recording the session for later viewing. To register, jump on over to our website and register:


If Data Dude isn’t your thing don’t worry, there are lots of upcoming webinars. Next week Brian Knight is doing an intro to MDX session, and Ben Evans will be doing one on Data Driven Website Design. There’s also a nice selection of already recorded past webinars available for instant viewing. And like I said, it’s all free. How cool is that!