Press "Enter" to skip to content

Month: August 2017

Comparing Baselines Using Power BI

Published 2017-08-24 by Kevin Feasel

Melissa Connors shows how to take baseline data from SentryOne and visualize it within Power BI:

Using Power BI to connect to multiple baselines in SentryOne allows me to make fast comparisons. I previously translated baseline values into charts manually or through some Excel/SQL Server connections. See this post on data compression as an example. I wanted to compare performance between different compression levels (None, Row, and Page), and include the Average, Minimum, Maximum, and Standard Deviation values. Now, I have a standard template that looks better in Power BI. Once I create a baseline in my database, I have access to it in my charts. If you don’t have Power BI, you can use the query from this post in Excel or another reporting method for your comparisons.

Even if you don’t use SentryOne, the principles are generally applicable.

Comments closed

Thinking About Virtual Log Files

Published 2017-08-24 by Kevin Feasel

Monica Rathbun has a reminder that Virtual Log Files can be troublesome in excess:

What causes High VLFs?

As transactions force growth of the log file, inappropriate log file sizing or auto-growth settings can cause a high number of VLFs to occur.  Each growth event adds VLFs to the log file.  The more often you grow in conjunction with smaller growth segments, the more VLFs your transaction log will have.

Example

If you grow your log by the default 1 MB you may end up with thousands of VLFs as opposed to growing by 1GB increments. MSDN does a great job on explaining how a transaction logs work for a deeper dive I recommend reading it.

Read on to see how many VLFs your databases have, as well as how to reduce the number should it grow excessive.

Comments closed

Failure Modes In Event-Based Systems

Published 2017-08-23 by Kevin Feasel

Dave Copeland has an interesting article on understanding how message- and event-based systems can fail:

The system trigger (1) initiates everything. Common failures here are timeouts inside Application. This is particularly insidious because when this happens, the System Trigger may retry the operation. Think about a user on a webpage getting a 500 error. They will likely retry what they were doing until it succeeds.

This means that the entire workflow could be triggered multiple times, and it could be done in a way that is not programmatically obvious. Imagine our Merchandise buyer marking down an item’s price, and the entire operation succeeds but at the last minute their Internet connection dies and they get an error. They will repeat the markdown action and now there will be two messages about the inventory price being sent.

This is an interesting read.  Also, definitely check out Dave’s earlier post on how there is no happy path; it seems that most developers only code for a chimera, as there is so much code that assumes everything will work perfectly.

Comments closed

Building A Tally Table With U-SQL

Published 2017-08-23 by Kevin Feasel

Saveen Reddy shows how to create a tally (or numbers) table in U-SQL:

Many common scenarios for U-SQL developers require constructing a RowSet made up of a simple range of numbers or dates, for example the integers from 1 to 10. In this blog post we’ll take a look at options for doing this in U-SQL. In the process, we’ll get a chance to learn how to use some common U-SQL features:

  • Creating RowSets from constant values

  • Using CROSS JOIN

  • Using SELECT to map integers to DateTimes

  • Using CREATE TABLE to create a table directly from a RowSet. This is sometimes called “CREATE TABLE AS SELECT” and often abbreviated as “CTAS“.

Click through to learn more.

Comments closed

Handling Sensitive Data With Powershell

Published 2017-08-23 by Kevin Feasel

Adam Bertram shows off the data protection API in Powershell:

We need to automatically read this information without our intervention. This is where you sometimes see people get lazy and add passwords in plain text into the script. That’s a big no-no, and there’s a better way.

 PowerShell has native support for something called the data protection API (DPAPI). DPAPI is a built-in way Windows users can use certificates to encrypt and decrypt information on the fly which is perfect for PowerShell scripting. No username and password required. We simply need a certificate installed which can be self-signed.

This is about much more than storing and decoding passwords, so check it out.

Comments closed

Enabling SQL Server SSL Encryption With Powershell

Published 2017-08-23 by Kevin Feasel

Tracy Boggiano shows how to create and put into place SSL encryption certificates on SQL Server instances using Powershell:

Next we take the exported pfx file and copy it locally to the temp folder of each machine and import into the local certificate store.  Then we edit the registry with the thumbprint of the certificate.  After that you will have to restart SQL Server to get the changes to take effect.  We also clean up after ourselves and delete the pfx from the temp folder.

Note: To make this safe for production I commented out the restart of SQL Server.  Also, Get-CmsHosts cmdlet can found here.

If you’re dealing with sensitive information, enabling (and forcing!) SSL encryption is one of the easiest effective ways of securing an instance; in this case, it’s securing data in transit from SQL Server to and from the client.

Comments closed

SQL Server Features Forgotten But Not Gone

Published 2017-08-23 by Kevin Feasel

Robert Sheldon looks at five cobweb-ravaged parts of SQL Server:

Policy-Based Management (PBM) has a PR problem, even worse that DQS and MDS. A lot of people don’t know what it is, or if they do, are not sure if it’s still supported. Microsoft doesn’t even mention the feature in its list of Editions and supported features of SQL Server 2016. The PR problem is so bad, in fact, that I’ve come across community forum postings asking whether PBM still exists.

Despite its silent role, PBM is still a supported feature and appears to be available to all SQL Server editions, although I suspect it caters primarily to organizations with a large number of similar SQL Server implementations.

Microsoft introduced PBM in SQL Server 2008 to provide a policy-based system for managing one or more SQL Server instances. You can, for example, use policies to grant permissions on user databases, disable lightweight pooling, or choose an authentication mode. You can also target policies at specific SQL Server instances, databases, tables, or indexes.

Quick hits:  Data Quality Services and Master Data Services have been lapped by the competition and aren’t really competitive products anymore.  Policy-Based Management is still useful, and I wish it’d get some internal love to extend roles & features.  Management Data Warehouse is a tire fire that you should avoid at all costs.  Service Broker is useful in the right circumstances, but it needs a few big changes to become a great broker.  Unfortunately, I don’t see big changes happening and so there are superior alternatives for data flow (like Apache Kafka).  But read Robert’s take on these features.

Comments closed

Synchronizing Logins And Jobs

Published 2017-08-23 by Kevin Feasel

Ryan Adams shows five methods for synchronizing SQL logins and a couple ways of synchronizing SQL Agent jobs between instances of SQL Server:

Robert Davis wrote a great script back when he published his Mirroring book.  I started to write my own and was almost done when I contacted Robert and asked if he had dealt with SQL logins since the script only handled Windows logins.  His reply was something along the lines of, “What are you talking about? Of course it handles SQL logins”.  It turns out that the publisher didn’t get the right script version published with the book.  That’s when this post from Robert with the full script was born…

Transferring Logins

I also wrote about it HERE.

This script creates a stored procedure to handle the move and also uses Linked Servers.  If you can’t have linked servers in your environment this is not a good choice for you.  However, you can create the linked server in a SQL Agent job step prior to the step for transfer and then remove it in a job step after the transfer.  It breaks the rule but it does it fast enough maybe no one will notice.

Read the whole thing.

Comments closed

Custom SQL Server Docker Images

Published 2017-08-23 by Kevin Feasel

James Anderson shows an easy way of creating a custom Docker image which relies on some other image:

The FROM statement declares that we want to lay some instructions on top of the microsoft/mssql-server-windows image. The beauty of this approach is that when I pull down a new version of the microsoft/mssql-server-windows image, my image will be updated too. The microsoft/mssql-server-windows Dockerfile does the same thing with the microsoft/windowsservercore image.

The rest of the Dockerfile sets some meta data, downloads the installer and adds the Advanced Analytics feature.

SSIS, SSAS, SSRS or any other SQL Server feature could be added to a containerised SQL Server deployment in the same way.

With this approach, you do run the risk that upstream changes will break your image, but for something like this, it’s a very useful approach.

Comments closed

Building Temp Tables From Queries

Published 2017-08-23 by Kevin Feasel

David Fowler shows how to use dm_exec_describe_first_result_set to generate a temp table schema:

Have you ever needed to store the results of a complex query in a temp table?  How did you go about working out what the definition for that temp table should be, the columns and their data types?

It can be a bit of a pain, not to mention time consuming to have to go figuring out what all datatypes of the base tables are.

I got fed up with all that hunting around as well so as a quick blog I thought I’d share a little script that will take your query in a variable and print out a temp table definition for its result set.

Click through for the script, as well as an important comment by frequent curatee Shane O’Neill.

Comments closed