Press "Enter" to skip to content

Month: January 2017

Upgrading SSRS From 2008R2 To 2014

Published 2017-01-13 by Kevin Feasel

Dave Turpin needed to perform an upgrade of a Reporting Services installation:

So let me share with you my biggest take away from this project:  EVERYTHING ABOUT USER CREATED REPORTS IS STORED IN THE SQL Server Reporting databases.  So if you are reading this post you probably are about to move an instance of SSRS, and may be concerned about the many, many reports involved.

Based on my one experience with this, there is need to move individual reports.  If you follow the process carefully, all of the existing reports will be re-created on the new machine.  It’s not quite magic, but it sure feels like it when everything shows up on the new system.

Read on for the solution Dave came up with.

Comments closed

Basics Of Azure Analysis Services Management

Published 2017-01-13 by Kevin Feasel

Bill Anton walks through some of the basics of managing an Azure Analysis Services cube:

The quickest win – from an ROI perspective – for Azure AS is the ability to pause the instance during extended periods of inactivity – for example, at night, when there aren’t any users running reports.

This can be achieved via the Suspend-AzureRmAnalysisServicesServer cmdlet we saw in the previous post.

Read on for a few tips of this ilk, including resizing the server.

Comments closed

Joins Galore

Published 2017-01-13 by Kevin Feasel

Lukas Eder has a comprehensive guide to joining data using SQL:

Alternative syntaxes: NATURAL JOIN

An more extreme and much less useful form of "EQUI" JOIN is the NATURAL JOIN clause. The previous example could be further “improved” by replacing USING by NATURAL JOIN like this:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

Notice how we no longer need to specify any JOIN criteria, because a NATURAL JOIN will automatically take all the columns that share the same name from both tables that it joins and place them in a “hidden” USING clause. As we’ve seen before, as primary keys and foreign keys have the same column name, this appears quite useful.

There is a high likelihood that you will learn at least one new thing here; for example, check out lateral joins (which SQL Server practitioners know as something else).

Comments closed

Turning On SQL Authentication

Published 2017-01-13 by Kevin Feasel

Kenneth Fisher hits a frequent cause of login failure:

No go. Next thing to check is the password. The error I’m getting does indicate the incorrect password so it’s a distinct possibility. Now when I moved the server principal I made sure to copy the password hash from the old server so the password should be the same. However, I’ve made mistakes before, and odd things happen, so I decided to change the password just in case. Before I did I backed up the original password just in case.

The solution is something that I’ve seen a lot of sysadmins forget to do in their setup processes.

Comments closed

DDL And The Optimizer

Published 2017-01-12 by Kevin Feasel

Grant Fritchey notes that data definition language (DDL) queries do not appear to go through the query optimizer:

We can check whether or not you’re going to get an execution plan for this in two easy ways. You can capture the estimated plan, or run the query and capture the actual plan. Since most of the time, it’s preferable to work with the added runtime information that an actual plan provides, let’s start there. However, if you execute the query above and try to capture an actual plan, you will not get one. So, let’s capture the estimated plan instead. Here’s what we see:

Not much of a plan really.

Read on for that, and start holding your breath for a new execution plans book.

Comments closed

Thinking Post-DRAM

Published 2017-01-12 by Kevin Feasel

Joe Chang argues that we may benefit more from a hardware architecture which uses lower-latency, lower-capacity RAM:

There are different types of SRAM. High-performance SRAM has 6 transistors, 6T. Intel may use 8T Intel Labs at ISSCC 2012 or even 10T for low power? (see real world tech NTV). It would seem that SRAM should be about six times less dense than DRAM, depending on the number of transistors in SRAM, and the size of the capacitor in DRAM.

There is a Micron slide in Micro 48 Keynote III that says SRAM does not scale on manufacturing process as well as DRAM. Instead of 6:1, or 0.67Gbit SRAM at the same die size as 4Gbit DRAM, it might be 40:1, implying 100Mbit in equal area? Another source says 100:1 might be appropriate.

Eye-balling the Intel Broadwell 10-core (LCC) die, the L3 cache is 50mm2, listed as 25MB. It includes tags and ECC on both data and tags? There could be 240Mb or more in the 25MB L3? Then 1G could fit in a 250mm2 die, plus area for the signals going off-die.

There is a lot of depth in this blog post.

Comments closed

Quickstats Craziness

Published 2017-01-12 by Kevin Feasel

Lonny Niederstadt takes an in-depth look at the intersection of several features and discusses an issue:

Here’s the Connect Item involved – written by Michael Swart.

Trace flag 2390 can cause large compile time when dealing with filtered indexes. (Active)
https://connect.microsoft.com/SQLServer/feedback/details/2528743/

Huh.  “What does that have to do with clustered columnstore indexes?” you might ask.  No, really, it’d be a personal favor if you *did* ask…

Ask and then read on.  This is the kind of post I’d send to someone wanting to learn how to troubleshoot issues.

Comments closed

Sankey Bar Charts

Published 2017-01-12 by Kevin Feasel

Devin Knight continues his custom visuals series:

In this module you will learn how to use the Sankey Bar Chart Power BI Custom Visual.  The Sankey Bar Chart is used to show a flow of data between different stages of a process.

It’s an interesting mix of sankey, bar chart, and funnel.  In other words, you may only have one thing you can use it for, but it’ll be a really good use.

Comments closed

TempDB DDL Triggers

Published 2017-01-12 by Kevin Feasel

Erik Darling wants to create a DDL trigger in tempdb which survives after restart:

One workaround is to create it in model. Then it will be in tempdb on restart, but it will also be in all the databases you create.

That may or may not have the intended effect! If you don’t want people doing a certain thing in just tempdb, you have to add an additional check using the event data.

Click through for more details, including a couple of sample scripts.

Comments closed