Press "Enter" to skip to content

Author: Kevin Feasel

Firewall Configuration With Powershell

Published 2016-09-15 by Kevin Feasel

Slava Murygin gives an introduction to firewall configuration using Powershell:

The Script has list of adjustable filters:
$Direction – Direction of firewall rule: Inbound or Outbound;
$Action – Action rule performs: Allow or Block;
$Enabled – Status of a rule: Enabled – True or False;
$RuleGroup – Group rule has been assigned. By default script uses “$Null” variable, which filters all rules without assigned group. However you can specify a group a name if necessary;
$DisplayName – Name of a rule. By default I use an expression “*SQL*” to search for rules which have word “SQL” in their name. To retrieve all rules us “*”. To retrieve any particular rule use rule name.

He looks at viewing rules as well as creating, modifying, and deleting them.

Comments closed

SARGable Predicates

Published 2016-09-15 by Kevin Feasel

Gail Shaw discusses what makes a particular predicate SARGable:

Any1 function on a column will prevent an index seek from happening, even if the function would not change the column’s value or the way the operator is applied, as seen in the above case. Zero added to an integer doesn’t change the value of the column, but is still sufficient to prevent an index seek operation from happening.

While I haven’t yet found any production code where the predicate is of the form ‘Column + 0’ = @Value’, I have seen many cases where there are less obvious cases of functions on columns that do nothing other than to prevent index seeks.

UPPER(Column) = UPPER(@Variable) in a case-insensitive database is one of them, RTRIM(COLUMN) = @Variable is another. SQL ignores trailing spaces when comparing strings.

This is a straightforward concept with significant performance implications.

Comments closed

Create An Azure SQL Database Instance From Powershell

Published 2016-09-15 by Kevin Feasel

Arun Sirpal walks through the steps of setting up an Azure SQL Database instance and database using Powershell:

What I have done here is hard-code three parameters ( database edition, start IP address and end IP address) which for my situation won’t change but I have given the ability to pass in the environment name, SQL Server name and database name.

So a prompt will be presented to the user – here you should enter the relevant details and click enter.

It’s not that difficult to do, and the scripts themselves are probably faster than fumbling around the UI.

Comments closed

Diagnosing And Solving A Performance Problem

Published 2016-09-15 by Kevin Feasel

Monica Rathbun had a major performance problem; this is how she solved it:

Symptoms:

  • Very High Disk Latency as high as 300,000 milliseconds (ms) is not unusual
  • Average: 900 – 15,000ms
  • Memory Pressure
  • Slow User Experience

Problem:

  • Bad hardware
  • Over-provisioned VM Hosts (what happens on one VM effects the other)
  • Old NetApp SAN
  • No infrastructure budget for new hardware

Challenge: Make the system viable with no hardware changes or tweaks

Those disk latencies are scary.  I like the systematic approach Monica takes, and the end result was very positive.

Comments closed

Observations On Azure SQL Data Warehouse

Published 2016-09-15 by Kevin Feasel

Jeffrey Verheul is running this month’s T-SQL Tuesday.  Here is his contribution:

A thing that can make migrations to the cloud a bit more difficult, is that Azure SQL databases are basically a contained datastore (you would call it a “contained database” when you run it on-premise). This means that you (by default) can’t connect from one database to the other. This could mean that you need to rewrite your applications or stored procedures, or maybe even redesign your entire database/application/domain model.

This also means that running a stored procedure from the Ola Hallengren’s maintenance solution can only be done on the specific database, and not from the master database like the on-premise version does. These small challenges can be overcome, but it does mean code-duplication in your databases because the maintenance procedures need to be deployed to every single database.

Read on for more observations regarding Azure SQL Data Warehouse.

Comments closed

Evaluating Monitoring Tools

Published 2016-09-15 by Kevin Feasel

Richard Douglas has a great post on things to think about when evaluating a monitoring tool:

A question that often comes up in meetings is, “What would success look like?” To me, it’s my favourite football team Spurs winning the English Premier League! This is never a popular answer to the person asking the question in the meeting, but generally raises a few smiles and lightens the mood. However, you’re more likely interested in monitoring software and what success means in that scenario. As I see it, success means finding an outcome that is beyond doubt. Now success could mean that the software you are evaluating is not as good as the current incumbent. That is a successful outcome. You have decided that you already own the best solution for you. Congratulations! It can also mean that a particular solution meets all of the criteria needed by your business in order for it to solve technical issues and to grow.

The advice is vendor-agnostic and is worth reading if you plan to evaluate monitoring tools anytime soon.

Comments closed

SSRS Mobile Reports And Data Types

Published 2016-09-15 by Kevin Feasel

Koen Verbeek runs into a problem with SSRS Mobile Reports:

The error message “The JSON SharedDataSet Table renderer cannot parse the supplied report” doesn’t exactly tell you what’s going on. Apparently it is having issues with the Location column, which is of the geography data type. If you remove it, the dataset will be imported in the mobile report editor. There’s no documentation of which data types are supported or not in the mobile reports. I included the column in the first place to find out if the Mobile Report Publisher could handle it and plot the data on a map. It seems not.

Example number 798 in the “Microsoft errors tend to tell you what caused the failure, but not what actually caused the failure” ongoing series.  Sure, the JSON SharedDataSet Table renderer blew up…but what does that have to do with me and how do I fix it?  I realize that good error messages can be difficult in complex software, but this one isn’t very helpful at all.

Comments closed

Error Handling Extended Event

Published 2016-09-15 by Kevin Feasel

Dave Mason shows how to use an Extended Event to capture error data:

Here’s an example for DBCC CHECKDB on a corrupt database. Remember from the last post that in this scenario, control never passes to the CATCH block. So we’ll need to check the Event Session data after END CATCH. You can also run this as a single batch in SSMS, but you’ll need a corrupt database to get similar results. As before, replace “2016” with your SPID.

There are a lot of working parts to this, so read the scripts carefully if you’re interested in implementing something similar yourself.

Comments closed

Using Spark For Investigation

Published 2016-09-14 by Kevin Feasel

Sean Owen tries to unravel the Tamam Shud mystery:

Several people have approached these letters as a cryptographic cipher. The odd circumstances of death do sound like something out of a John Le Carré spy novel. Some of the best attempts, however, fail to produce anything but truly convoluted parsings.

Another possibility may already have occurred to you: Are they the first letters of words in a sentence (aninitialism)? Some suspect this death was a suicide, and that the message is merely some form of final note. With this morbid scenario in mind, it’s easy to imagine many phrases, like “My Life Is All But Over,” that fit the letters because indeed their frequency seems to match that of English text.

This lead has been picked up a few times. These writeups (example) present indications that the message is indeed an initialism. However, they don’t apply what is arguably the clear statistical tool for this job. And they don’t take advantage of big data. So, let’s do both.

Read on for Chi Square testing and book parsing examples using Spark.  Spoiler alert:  Sean doesn’t solve the mystery, but it’s still a fun read.

Comments closed

Predictive Maintenance Solution Template

Published 2016-09-14 by Kevin Feasel

Jaya Mathew has a SQL Server R Services template for predictive maintenance:

To illustrate the scenario, we will focus on companies who operate machines which encounter mechanical failures. These failures lead to downtime which has cost implications on any business, hence most companies are interested in predicting the failures ahead of time so that they can proactively prevent them. This scenario is aligned with an existing R Notebook published in the Cortana Intelligence Gallery but works with a larger dataset where we will focus on predicting component failures of a machine using raw telemetry, maintenance logs, previous errors/failures and additional information about the make/model of the machine. This scenario is widely applicable for almost any industry which uses machines that need maintenance. A quick overview of typical feature engineering techniques as well as how to build a model will be discussed below.

Understanding when machines are likely to break down is a very interesting statistical problem.  Check out the template.

Comments closed