Press "Enter" to skip to content

Author: Kevin Feasel

Using Azure Active Directory With Azure SQL Database

Published 2017-08-31 by Kevin Feasel

Shannon Lowder shows how to integrate Azure Active Directory with Azure SQL Database:

Now that our instance is set up to use AAD, let’s connect to our instance from SSMS. If you’re running Management Studio 2016 and SQL Server Data Tools for 2015 (14.0.60311.1) or later you should have noticed there are some extra authentication methods available in SSMS now.  We’re going to cover these out of order, since some of these options take more work than others.

Active Directory Password Authentication looks similar to a SQL authentication, but it accepts AAD User names and passwords.  When you choose this method, your credentials are sent over to Azure and end at your AAD instance.  Once your username and password are validated, AAD will check to see if you have rights to connect to the instance.  If so, you will connect.  If not, you will get an error message that you’ll need to google (bing) to find out what it really means.

With the steps we took in the last section, you should be able to log in to your Azure SQL Server as an administrator by using Active Directory Password Authentication.

Click through for the process, as well as links to additional resources.

Comments closed

SQL Server In Kubernetes

Published 2017-08-31 by Kevin Feasel

Andrew Pruski shows us how to spin up SQL Server containers within Kubernetes running on Azure Container Services:

Looks pretty good to me! SQL is up and has accepted the config value within our yaml file to change the SA password. But how are we going to connect to it?

What we need to do now is define a Kubernetes service. A service is a level of abstraction over pods to allow connections to the containers within the pods regardless of where the pod is located in the cluster. So let’s setup a service.

Andrew does a good job of taking us through the process step by step.

Comments closed

Explaining Max Server Memory

Published 2017-08-31 by Kevin Feasel

Randolph West explains SQL Server memory limits in various editions:

For the sake of this post, I am talking specifically about SQL Server 2012 and higher.

Let’s break this down.

On all editions of SQL Server (Enterprise, Standard, Web, Express), there is a configuration setting called Max Server Memory. This value should not be left at the default 2.1 petabytes, because at that value SQL Server will use all the memory you allow it (plus a little bit more if it needs to), and once it has it, it doesn’t release it back to the operating system without a fight.

Read on to see what Max Server Memory actually includes, as well as additional limitations on editions other than Enterprise.

Comments closed

Tokenizing Text With R

Published 2017-08-30 by Kevin Feasel

Rachael Tatman shows how to tokenize a set of text as the first step in a natural language processing experiment:

In this tutorial you’ll learn how to:

  • Read text into R
  • Select only certain lines
  • Tokenize text using the tidytext package
  • Calculate token frequency (how often each token shows up in the dataset)
  • Write reusable functions to do all of the above and make your work reproducible

For this tutorial we’ll be using a corpus of transcribed speech from bilingual children speaking in English.  You can find more information on this dataset and download it here.

It’s a nice tutorial, especially because the data set is a bit of a mess.

Comments closed

One-Way ANOVA Testing With R

Published 2017-08-30 by Kevin Feasel

Bidyut Ghosh shows how to perform a one-way ANOVA test in R:

From the above results, it is observed that the F-statistic value is 17.94 and it is highly significant as the corresponding p-value is much less than the level of significance (1% or 0.01). Thus, it is wise to reject the null hypothesis of equal mean value of mileage run across all the tyre brands. In other words, the average mileage of the four tyre brands are not equal.
Now you have to find out the pair of brands which differ. For this you may use the Tukey’s HSD test.

ANOVA is a fairly simple test, but it can be quite useful to know.

Comments closed

The Value Of Tidyeval

Published 2017-08-30 by Kevin Feasel

Bruno Rodrigues explains why he likes tidyeval:

Last year, this column, let’s call it spam, had values 1 for good and 0 for bad. This year the column is called Spam and the values are 1 and 2. When I found out that this was the source of the problem, I just had to change the arguments of my functions from

generate_spam_plot(dataset = data2016, column = spam, value = 1)
generate_spam_plot(dataset = data2016, column = spam, value = 0)

to

generate_spam_plot(dataset = data2017, column = Spam, value = 1)
generate_spam_plot(dataset = data2017, column = Spam, value = 2)

without needing to change anything else. This is why I use tidyeval; without it, writing a function such as genereta_spam_plot would not be easy. It would be possible, but not easy.

Read the whole thing.

Comments closed

R Services Packet Captures

Published 2017-08-30 by Kevin Feasel

Niels Berglund continues his R Services internals series:

In Figure 15, I set the filter to be tcp.srcport==50755, and then I applied the filter by clicking the arrow. To start using this:

  • Clear the Process Monitor display, and make sure you are capturing events.
  • Start WireShark capturing (Ctrl+E). If you get a question whether you want to save the captured packets, just click “Continue without Saving”.
  • Execute the code in Code Snippet 3.

The Process Monitor output looks almost the same as in Figure 9, whereas the WireShark output looks like so:

Niels also includes a recap to help people who haven’t been following along get up to speed.

Comments closed

Loading Webpage Data Into Excel

Published 2017-08-30 by Kevin Feasel

Kerry Tyler has an interesting use of Excel:

Fortunately, there are some tools built right into Excel that make this a whole lot easier than scrolling through the list in your browser. Armed with nothing more than the URL to the CU’s KB article and Excel 2016 (or a few older versions) quick work can be made of generating custom filters for this data.

Here are the steps:

The same Power Query bits are available in Power BI, so check it out.

Comments closed

Using Visual Studio Code As A Powershell IDE

Published 2017-08-30 by Kevin Feasel

Mike Robbins has a new video up:

If you follow me on Twitter, then I’m sure you’re aware that I’ve been using nothing but VS Code (Visual Studio Code) as a replacement for the PowerShell ISE (Integrated Scripting Environment) for the past couple of weeks and while I had tried it in the past, I didn’t previously think it was ready for prime time. That’s now changed with all of the updates and work that has gone into it. From what I’ve found, it works fairly well flawlessly so I’ve created a short and simple video to help others get VS Code installed and configured as a replacement for the PowerShell ISE.

Click through for some interesting comments, especially one from Mike Fal.

Comments closed

Finding Compressable Indexes

Published 2017-08-30 by Kevin Feasel

Tracy Boggiano has a script to help you figure out which indexes make sense to compress:

We can write procedure check periodically rather a table will benefit from compression or not.  There are a few tricks though:

  1. If you have sparse columns you cannot compress the table, we check for that in lines 70-74.
  2. Next if is a index type of 1 (a clustered index) it cannot contain LOB data types image, ntext, or text.  Which these days shouldn’t be a problem, they have been after all deprecated, so you aren’t using them.  We check for these in lines 76-84.
  3. Finally, we perform the same test for LOB data on index type of 2 (nonclustered indexes) in lines 85-95.

If everything checks out OK we call our second procedure to calculate the percentage of space we would space and if we would save more than 25% then we create script to compression the index.  Now we have a @Debug parameter in here so it can just show us what code it would execute to determine what it will do.

Click through for the code for the two procedures Tracy has created.

Comments closed