Press "Enter" to skip to content

Author: Kevin Feasel

Restoring Databases With dbatools

Published 2017-03-20 by Kevin Feasel

Rob Sewell shows how to restore a slew of databases using one dbatools command:

In my lab I had installed SQL 2016 on a server running Server 2016 TP5 which expired so I needed to re-install Windows and therefore needed to restore all of my user databases again. This was so easy using the dbatools module that I thought it was worth sharing to show how easy your disaster recovery process could be.

Having re-installed Windows and SQL and copied the backup files back to the server (although I could have used a network location), I then had to restore all of the user databases.

This is how I restored all of my user databases using the dbatools module command Restore-SQLBackupFromDirectory

Read on for the answer, as well as a warning that the upcoming dbatools 1.0 release may change some things.

Comments closed

Power BI Matrix Preview

Published 2017-03-20 by Kevin Feasel

Matt Allington reports that Power BI Desktop supports a new type of matrix (in preview):

Expand, Collapse, Drill and Filter

Expand and collapse behaves just like a pivot table however with a slightly different UI. The new matrix experience is however entirely consistent with the chart drill experience so it is very intuitive.

The new cross filter behaviour is of course not possible in a regular pivot table in Excel (without VBA). You can select any column, row or cell in the matrix and it will cross drill the other visuals on the canvas as can be seen above.

This looks like an interesting change, and Matt shows how to enable the preview.

Comments closed

SSRS: SendGrid Without Encryption

Published 2017-03-20 by Kevin Feasel

Denny Cherry shows how to configure Reporting Services to use SendGrid (sans encryption) to send e-mails:

We setup our nice new SQL 2016 SSRS servers to authenticate using the new SQL 2016 GUI which just supports it now. One problem with the GUI is that it requires that you use encryption. No big deal, SendGrid supports encrypted SMTP.

The problem with SendGrids encrypted SMTP is that it has a funky certificate chain which isn’t trusted by default. And it’s a convoluted process to get the correct certificate chain installed on a few servers (we have an SSRS farm as there’s thousands of reports being delivered every morning).

Click through for the solution.

Comments closed

Data Type Mismatches

Published 2017-03-20 by Kevin Feasel

Kendra Little gets into why certain data type mismatches force scans of tables while others can still allow seeks:

Sometimes we get lucky comparing a literal value to a column of a different type.

But this is very complicated, and joining on two columns of different types in the same family without explicitly converting the type of one of the columns resulted in worse performance in Paul White’s tests, when the columns allowed NULLs! (Note: I haven’t rerun those tests on 2016, but I think the general advice below still applies.)

General advice: don’t rely on being lucky. Pay attention to your data types, and compare values of the same data type wherever possible.

That’s great advice.

Comments closed

PBM Schedule Failures

Published 2017-03-20 by Kevin Feasel

Dave Turpin diagnoses an issue where scheduled Policy-Based Management policy checks were failing:

While it is easy to build and test policies by executing them on demand (especially powerful when run through Central Management Server) I had some issues getting my policies to run in “on schedule” mode.

To be more specific, my policies that use the ExecuteSQL function have been an issue.  What I was finding was:

  • The policy would run fine “on demand” but…
  • When I run the policy through the PBM scheduler, the policy would fail.

Dealing with false positives is not a good start for any monitoring service, so getting to the root of the issue was critical.

Read on for the solution.

Comments closed

Polybase Use Cases

Published 2017-03-17 by Kevin Feasel

James Serra talks about Polybase use cases:

For federated queries: “N” requires all data from the source to be copied into SQL Server 2016 and then filtered.  For “Y”, the query is pushed down into the data source and only the results are returned back, which can be much faster for large amounts of data.

I mention “Maybe” for age out data in SQL DW as you can use PolyBase to access the aged-out data in blob or Azure Data Lake Storage (ADLS), but it will have to import all the data so may have slower performance (which is usually ok for accessing data that is aged-out).  For SQL Server 2016, it will have to import the data unless you use HDP/Cloudera, in which case the creation of the MapReduce job will add overhead.

The thing that I like about this chart is that the new Polybase sources (SQL Server, Oracle, Teradata, Mongo, and generic ODBC) do support predicate pushdown.  For large data sets, that’s huge:  it lets the database engine on the opposite end do as much filtering as possible before sending results back to your SQL Server head node.

Comments closed

Graphing R Package Dependencies

Published 2017-03-17 by Kevin Feasel

Tomaz Kastrun uses the igraph package to graph package dependencies in R:

With importing package tools, we get many useful functions to find additional information on packages.

Function package.dependencies() parses and check dependencies of a package in current environment. Function package_dependencies()  (with underscore and not dot) will find all dependent and reverse dependent packages.

This probably tilts more toward “fun” than “practical,” but this will let you see the full set of dependencies for a package if, for example, you need to grab all of these packages for upgrading an offline instance.

Comments closed

Tail Log Backups

Published 2017-03-17 by Kevin Feasel

Kendra Little explains the importance of tail log backups in the course of answering a reader question:

When you restore a full backup, does it restore to when you started the backup job— or when it completed?

In this episode, I give you the super-short answer. (Spoiler: a point near the end of when the backup was running.) For the full answer, complete with a detailed timeline to help you understand the nitty gritty, read “Understanding SQL Server Backups” by Paul Randal

Click through for the video as well as a bit more information on tail log backups.

Comments closed

Disappearing Availability Groups

Published 2017-03-17 by Kevin Feasel

Cody Konior has a not-so-great magic trick:

I logged onto that node and the AG Dashboard looked okay at first glance. But the test was still failing when I re-ran it manually, so, I looked deeper.

I logged onto the second node and noticed the AG was completely gone. All the databases were in recovery but there was no sign of the AG at all. Nothing. Nada. Zip. (I don’t have any other words). It’s like it was never there.

At first I thought someone must have done something awful. I quickly poured a coffee while checking the default trace which usually records system-level configuration changes like dropping an entire replica but in this case nothing relevant showed up.

Read on for the answer, as well as action items to take if you’re actively using Availability Groups.

Comments closed

Graph Database Basics

Published 2017-03-17 by Kevin Feasel

Victoria Holt has some good resources on learning more about graph databases:

There is graph support in the next version of SQL Server. The private preview page states

SQL Graph adds graph processing capabilities to SQL Server, which will help you link different pieces of connected data to help gather powerful insights and increase operational agility. Graphs are well suited for applications where relationships are important, such as fraud detection, risk management, social networks, recommendation engines, predictive analysis, dependence analysis, IoT suites, etc.
Initially, SQL Server will support CRUD graph operations and multi-hop graph navigation, and the following functionality will be available in the private preview:

  • Create graph objects, that is, nodes to represent entities and edges to represent relationships between any 2 given nodes. Both Nodes and Edges can have properties associated to them.
  • SQL language extensions to support join free, pattern matching queries for multi-hop navigation

Kennie Pontoppipidan wrote a great blog post on where to find out more information.

Click through for more links to interesting resources.

Comments closed