Press "Enter" to skip to content

Author: Kevin Feasel

Putting TempDB Files On Azure IaaS D Drive

Published 2019-03-26 by Kevin Feasel

John McCormack tries out using the temporary drive on Azure VMs for tempdb:

Azure warn you not to to store data on the D drive in Azure VMs, but following this advice could mean you are missing out on some very fast local storage. It’s good general advice because this local storage is not permanently attached to your instance, meaning you could lose data or log files if your VM is stopped and restarted but what if you could afford to lose certain files? Say files that are recreated during startup anyway.

TempDB is the ideal candidate for this. No other database is suitable! Putting the tempdb data and log files onto D drive can be achieved quite easily with a little bit of effort. And you will most likely see a big improvement in tempdb read/write latency.

John ended up seeing much bigger gains than I did when I tried this, but with a difference that big, it’s definitely worth using the temporary drive for tempdb.

Comments closed

Desired State Configuration’s Local Configuration Manager

Published 2019-03-26 by Kevin Feasel

Jess Pomfret continues a series on Desired State Configuration in Powershell:

Once we have crafted the perfect configuration and shipped it out to our target nodes, it’s time for the magic to happen. The MOF file that we created by executing our configuration is translated and enacted by the Local Configuration Manager (LCM) on each target node. The LCM is the engine of DSC and plays a vital role in managing our target nodes.
The LCM on each target node has many settings that can be configured using a meta configuration. This document is written very similarly to our regular DSC configurations and then pushed out to the target node.

I’m going to cover a few of the important LCM settings for use in push mode. This is where the LCM passively waits for a MOF file to arrive. The other option is pull mode- this is a little more complicated to set up and in this scenario the LCM is set to actively check in with a pull server for new configurations.

Click through to see some of those important settings.

Comments closed

CTP 2.3 Scalar Function Improvements

Published 2019-03-26 by Kevin Feasel

Erik Darling see a marginal improvement between CTPs in SQL Server 2019’s ability to optimize scalar functions:

To test FROID, which is the codename for Microsoft’s initiative to inline those awful scalar valued function things that people have been griping about for like 20 years, I like to take functions I’ve seen used in real life and adapt them a bit to work in the Stack Overflow database.

Read on for a case where Erik saw major improvements.

Comments closed

Flink and Stateful Streaming

Published 2019-03-25 by Kevin Feasel

Himanshu Gupta explains some of the benefits Apache Flink offers for stateful streaming applicatons:

The 2 main types of stream processing done are:
1. Stateless: Where every event is handled completely independent from the preceding events.
2. Stateful: Where a “state” is shared between events and therefore past events can influence the way current events are processed.

Stateless stream processing is easy to scale up because events are processed independently. But Stateful stream processing is difficult to scale up because the “state” needs to be shared across the events.

Himanshu does point out alternatives, but this isn’t a comparison exercise.

Comments closed

Performance Testing Aiven Kafka

Published 2019-03-25 by Kevin Feasel

Heikki Nousiainen tests the Aiven platform’s Kafka implementation on different cloud providers at different service levels:

We used a single topic for our write operations with a partition count set to either 3 or 6, depending on the number of brokers in each test cluster. As the test clusters were regular Aiven services, the partitions and replicas were spread out across availability zones.

Messages were produced via the librdkafka_performance tool with a message size of 512 bytes, a default batch size of 10,000 and no compression. Continuing our quest to simulate real-world use, client connections were made over TLS.

We used Kafka version 2.1 running with Java 8; as a side note, it’ll be interesting to benchmark Aiven Kafka running with Java 11 in future tests because we expect Java improvements to positively impact its performance.

During the test, we kept increasing the number of producing clients until we reached the maximum throughput rate each plan tier’s cluster could accept. To verify our readings, we left the load running for some time.

There are some interesting results here.

Comments closed

Building a Power BI Dashboard on Streaming Data

Published 2019-03-25 by Kevin Feasel

Annie Xu shows us how to build a Power BI dashboard on a streaming data source in Azure:

This post is about something new I have tried last week. The goal was to create simulated streaming data source, feed it into Power BI as a streaming dataset, create a report out of the streaming dataset, and then embed it to an web application. With proper directions provided by my teammates, I finished the implementation from end to end within 1.5 hours. I was super impressed by how awesome it is and how easy it is to implement so that I want to share those directions to you.

The source data is simulated but the process is the same with real data sets.

Comments closed

Testing Data Pages in Linux

Published 2019-03-25 by Kevin Feasel

John Morehouse shows that SQL Server data pages are the same in Windows as they are in Linux:

One of the new phrases coming out of Microsoft is that “SQL is just SQL” regardless of what operating system it resides on.   This was echoed during the keynote at SQL Bits 2019 by the Microsoft team, which you can watch here.   Later that weekend, I gave a session about database internals.  My presentation is about how data is structured within a row and why that matters.  Understanding the internals of table structures, even in today’s age of technology, include SQL Server 2019 (which will be released in Q3/Q4 of 2019) is important.   During my session, a question came up about how a data page is structured if SQL Server is sitting on top of a Linux server, such as Ubuntu.  Does the data page have the same size and shape in Linux as it does in Windows?

They do. Click through to see John prove it.

Comments closed

Backing Up SSRS Encryption Keys

Published 2019-03-25 by Kevin Feasel

Jonathan Kehayias reminds us to back up those SQL Server Reporting Services encryption keys:

If you run SQL Server Reporting Services, part of your DR plan needs to include a backup of the encryption key for SSRS. This sadly is an all to often overlooked part of the solution, even though it is incredibly easy to do. If you don’t have a backup of the encryption key during a restore, the report server will never be able to decrypt the encrypted content (connection strings, passwords, etc) stored in the database, and your only recourse would be to delete the encrypted content and recreate it manually or through a redeployment of datasources.

Jonathan includes a couple of links to good resources. Your backups are only good if they include all of the keys and certificates you used. But keep those certificates stored someplace other than where the backups are stored.

Comments closed

Running Totals in Tableau and Power BI

Published 2019-03-25 by Kevin Feasel

David Eldersveld shows how to create running totals in both Tableau and Power BI:

What about a separate Power BI Date table?
This setup is built for consistency of comparison. As people go deeper into Power BI, they typically add a separate Date table as part of a more robust data model and add relationships between tables. At the same time, they disable the default Auto Date/Time built-in hierarchies. This more advanced setup with a separate Date table allows several conveniences as well as performance and storage benefits. It’s especially true with larger models that include many facttables that each join to Date and other possible dimension tables. Tableau doesn’t currently have a comparable data model. We’ll stay conveniently away from that setup in Power BI because we only have one simple sample table.

I think both of them make this an easy operation, though Tableau is probably easier here.

Comments closed

Persisting Databases with Docker Named Volumes

Published 2019-03-25 by Kevin Feasel

Andrew Pruski shows us how to use named volumes to keep containerized SQL Server databases hanging around:

The mssqluser named volume is going to be mounted as /var/opt/sqlserver and the mssqlsystem volume is going to be mounted as /var/opt/mssql. This is the key to the databases automatically being attached in the new container, /var/opt/mssql is the location of the system databases.

If we didn’t mount a named volume for the system databases any changes to those databases (particularly for the master database) would not be persisted so the new container would have no record of any user databases created.

Read the whole thing.

Comments closed