Return to Birmingham for SQLSaturday Bham 2017!

It's been 2.5 years since I last spoke at SQLSaturday Birmingham, and I'm eager to make a return, this time with two more Sparkhound colleagues in tow and an amazing secret raffle prize to giveaway at the end!

We'll see you there on Saturday!

Sparkhound speakers in the lineup:

Robert Bishop, "Hostile Takeover" at 830AM
Myself, "SQL Admin Best Practices with DMVs", at 10:50AM
Kevin Grohoske and myself, "SQL + SharePoint: Best Friends Forever", at 3:20PM

UPDATE: The slides and demos for all three of the above presentations is available at the schedule site here: http://www.sqlsaturday.com/593/Sessions/Schedule.aspx

Other highlights on the schedule you should check out:

Patrick LeBlanc: Introduction to the Power BI Desktop
Bill Pearson: Seeking the Perfect Hybrid: On-Prem Data with Reports and Dashboards in Power BI
Mike Robbins: PowerShell 101: The No-Nonsense Beginner’s Guide to PowerShell
Ed Watson: Introduction to SQL Server Integration Services

It's going to be great event, and a thanks-in-advance to Samir Behara and John Baldwin and all the volunteers who have been working hard to make this event happen!

Actual Chats: RIGHT OUTER joins in the wild

Colleague:
I think I just encountered my first Right join
*shocked* 

Me:
huh. weird.
a rare Right Join has appeared!

Colleague:
catch it!

Me:
You use ON. It's super effective! 

Colleague:
haha
how often do you see them? 

Me:
I've used RIGHT join maybe twice in 10 years. 

Colleague:
lol

Me:
just makes things difficult to read.

Colleague:
yea it took me a while
it was at the bottom of the query
i kept wondering what was bringing in the extra rows till I found it

And that's why, whenever possible, avoid using RIGHT OUTER. It breaks the nice top-to-bottom flow of your complicated FROM clauses, forces you to page-split your brain around data sets, and generally makes it more difficult for the next DBA to understand.

For this overly-simplistic example, these two queries on the WideWorldImporters database are identical and have the same execution plans:

SELECT  s.CustomerName
, pp.FullName
, ap.FullName 
FROM Sales.Customers AS s
LEFT OUTER JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
INNER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID 

SELECT  s.CustomerName
, pp.FullName
, ap.FullName
FROM [Application].People AS pp
RIGHT OUTER JOIN Sales.Customers AS s
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
INNER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID

The first query allows you to do the easily relational math as you read. The second has you flipping the dataset before moving your eye back up to the top.

There are certainly legitimate albeit complicated cases for using RIGHT OUTER, but from a readability and maintainability, I'd prefer to stick with LEFT OUTER.

SQL SSRS Express Edition Cannot Connect to Azure SQL Databases

This has bit a couple of clients recently, figured I'd put out a PSA.

Express editions of SQL Server Reporting Service, from SQL 2016 on down, cannot connect to Azure SQL Databases. Turns out, getting something for free does have some significant limitations.

For example, you'll see an error message "The Report Server has encountered a configuration error" on a data source page, when creating a new SSRS data source in the Report Manager website. What you may have not noticed on this page was the possible values in the Data Source Type drop down list.

In express edition, "Microsoft SQL Server" is the only option. In Standard and higher editions, there are many data types to choose from, including "Microsoft SQL Azure". Remember that Azure SQL Databases can't accept OLEDB connections.

But why? On this page (and see caption right), Microsoft lists a large number of "Built-in data extensions," Azure SQL Database included. The explanation lies here, where under possible "Supported data source", Express edition only supports Express edition, while Standard and above supports "All SQL Server editions."

This is what you're looking at in SSRS Express:

This is what you could be doing, in SSRS Standard or higher:

So what's the solution?

Upgrading the edition of a SSRS Express edition instance in-place is possible. 

Remember you can install various SQL Server features (Database, RS, IS, AS) on different Windows servers, each only once, as part of your license. So if you already own a Standard+ edition SQL Server somewhere but aren't using the SSRS feature, you now have a good use for it. 

Myth BUSTED: The above is incorrect! In the Licensing Guide for each recent version of SQL Server you'll find this: "The software components of a single SQL Server 2016 license cannot be separated. Any OSE running any of the licensed components of SQL Server 2016, requires a license. For example, if the SQL Server DB is deployed in one OSE and SQL Server RS is deployed in another, both OSEs must be fully licensed for SQL Server 2016 accordingly." The language was different and less specific for SQL 2005.

There are also a large number of fantastic (and also fantasy) ideas that developers will have, including SSRS alternatives. Listen to them, sure. But keep in mind that if your platform is SSRS and all the features that come with it, you will need to pay for it at some point. SQL Express edition and never been and never will be intended or suitable for standalone production usage.