Press "Enter" to skip to content

Month: December 2018

Looping Over Files In SSIS

Published 2018-12-18 by Kevin Feasel

Tim Mitchell shows us how to use the foreach loop component to iterate over a set of files:

The SSIS foreach loop is configured to allow you to easily ingest multiple data files in a single data flow task. For this to work, all of the files would need to reside in the same directory structure (either locally or on the network), and they must all have the same structure and metadata.
In this design, the data flow is contained within the foreach container, which will execute the contents of that data flow task once for each file found in the specified directory.

This gives us a good pattern for loading a bunch of text files, such as monthly extracts from a different system.

Comments closed

Always Encrypted With Secure Enclaves

Published 2018-12-18 by Kevin Feasel

Jakub Szymaszek announces secure enclaves support with Always Encrypted in SQL Server 2019:

The only operation SQL Server 2016 and 2017 support on encrypted database columns is equality comparison, providing you use deterministic encryption. For anything else, your apps need to download the data to perform the computations outside of the database. Similarly, if you need to encrypt your data for the first time or re-encrypt it later (e.g. to rotate your keys), you need to use special tools that move the data and perform crypto operations on a different machine than your SQL Server computer. These restrictions are not an issue if equality comparison is all your applications need and if the tables containing your sensitive data are small. However, many types of sensitive information, e.g. a person’s name or phone number, often require richer operations, including pattern matching and sorting, and it’s not uncommon for sensitive data to be too large to move outside of the database for processing.
To address the above challenges, Always Encrypted in SQL Server 2019 is enhanced with secure enclaves. A secure enclave is a protected region of memory that appears as a black box to the containing process and to other processes running on the machine, including the operating system. There is no way to view the data or code inside the enclave from the outside, which makes enclaves ideal for processing sensitive data. There are several enclave technologies that differ in how enclave isolation is accomplished. SQL Server 2019 preview uses a Windows Server technology called Virtualization Based Security (VBS), which relies on Hypervisor to protect and isolate enclaves.

They’re going further with Always Encrypted than I thought would be possible.  The first release of Always Encrypted had me asking “Why would I use this over running an encryption or decryption function in my app code?”  I think secure enclaves starts to answer that question.

Comments closed

Multithreading In Powershell

Published 2018-12-18 by Kevin Feasel

Andy Levy shows how to use the PoshRSJob module to implement multithreaded Powershell solutions:

Let’s say you’ve got a lot of RSJobs. Or maybe long-running RSJobs. Wouldn’t a nice, user-friendly view of the progress be handy? You bet it would!

This is both dense and verbose, but I wrote it really quickly and it works (but not just for this post, I’ve used it in production), so I’m not tinkering too much with it right now. Once the jobs are created (and a few have started), this script runs a loop watching the output of Get-RSJob and updates a progress bar to reflect where we are. It also takes care of receiving and removing the completed jobs.

This is pretty easy to work with—or at least Andy makes it look that way.

Comments closed

Database Backups With dbatools

Published 2018-12-18 by Kevin Feasel

Garry Bargsley continues the 12 Days of dbatools series, this time taking a look at taking backups:

We are on the home stretch and I have saved the last third of the series to cover some pretty cool/amazing commands.  Not that all 500+ commands are not cool/amazing but these last few are used continuously in my environment and bring automation closer to your finger tips with PowerShell and SQL Server then ever before.
Have you ever been asked to take a database backup before a developer does a deployment?  Have you ever been asked to backup a database to restore it to a development or other environment?  How about, hey can you take a quick transaction log backup?  These can all be accomplished using Backup-DbaDatabase and only changing a couple of parameters each time.

This post is strictly about taking backups, but dbatools is also great about testing backups.

Comments closed

Executing Python Code In Power BI

Published 2018-12-18 by Kevin Feasel

Brad Llewellyn shows how to build a visual based on a Python script using Power BI:

Now that we’ve seen our data, it’s a relatively simple task to convert the R script to a Python script. There are a few major differences. First, Python is a general purpose programming language, whereas R is a statistical programming language. This means that some of the functionality provided in Base R requires additional libraries in Python. Pandas is a good library for data manipulation, but is already included by default in Power BI. Scikit-learn (also known as sklearn) is a good library for build predictive models. Finally, Seaborn and Matplotlib are good libraries for creating data visualizations.

In addition, there are some scenarios where Python is a bit more verbose than R, resulting in additional coding to achieve the same result. For instance, fitting a regression line to our data using the sklearn.linear_model.LinearRegression().fit() function required much more coding than the corresponding lm() function in R. Of course, there are plenty of situations where the opposite is true and R becomes the more verbose language.

Click through for the full example.

Comments closed

Service Broker External Activator And .NET Framework

Published 2018-12-18 by Kevin Feasel

Allen White walks us through a problem he experienced recently:

My test environment is running SQL Server 2017 on Windows Server 2016, a pretty vanilla environment. After downloading the appropriate installer for the server where the service was to run, I installed it, made the necessary changes to the config file per the documentation provided after installation, assigned the service account with the necessary privileges, and attempted to start the service.

In the Windows System error log, I got three messages.

Read on for the solution.

Comments closed

Summarizing Improvements In Spark 2.4

Published 2018-12-17 by Kevin Feasel

Anmol Sarna summarizes Apache Spark 2.4 and pushes his meme game at the same time:

The next major enhancement was the addition of a lot of new built-in functions, including higher-order functions, to deal with complex data types easier.
Spark 2.4 introduced 24 new built-in functions, such as  array_union, array_max/min, etc., and 5 higher-order functions, such as transform, filter, etc.
The entire list can be found here.
Earlier, for manipulating the complex types (e.g. array type) directly, there are two typical solutions:
1) exploding the nested structure into individual rows, and applying some functions, and then creating the structure again.
2) building a User Defined Function (UDF).
In contrast, the new built-in functions can directly manipulate complex types, and the higher-order functions can manipulate complex values with an anonymous lambda function similar to UDFs but with much better performance.

2.4 was a big release, so check this out for a great summary of the improvements it brings.

Comments closed

Building A Full-Stack App With F#

Published 2018-12-17 by Kevin Feasel

Shanglun Wang has a tutorial on building a full application using F#:

In the United States, there is a popular saying: “It’s five o’clock somewhere”.
In some parts of the world, 5:00 pm is the earliest time when it is socially acceptable to have a drink, or a traditional cup of tea.
Today, we will build an application based on this concept. We will build an application that, at any given time, searches through the various time zones, find out where it is five o’clock, and provide that information to the user.

It’s a neat app.  

Comments closed

Using Powershell As Your Default Prompt On Windows Server Core

Published 2018-12-17 by Kevin Feasel

Patrick Gruenauer shows us how to make Powershell the shell of choice when running on Windows Server Core:

Well, if you’re running a Server Core (I hope so, for domain controllers, dns, file services and more there’s no good reason to do not so), then it’s an ease to change that. The corresponding setting has to be configured in the registry. Regedit can be opened on Server Core (there are more graphical user interfaces that are shipped with core, for example notepad.exe and timedate.cpl). I’m talking about this key:

Click through for the instructions.

Comments closed

Service Account Names And SQL Agent Not Starting Up After Reboots

Published 2018-12-17 by Kevin Feasel

Hamish Watson walks us through a weird scenario:

Looking at the event logs I (eventually) found this:
Event ID 7000:
The MSSQLSERVER service failed to start due to the following error: 
The account name is invalid or does not exist, or the password is invalid for the account name specified.
Which is bizarre – as the service account had been used for months – but after each reboot the services had to be manually started. GPO and other things had been blamed but no one could actually find out why.

I’ve never used @ naming for service accounts, so until I read this I didn’t even know it was possible.  Now I’m going to forget again because apparently it’s not a good idea.

Comments closed