Press "Enter" to skip to content

Curated SQL Posts

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

Mandatory And Default Powershell Parameters

Published 2018-12-17 by Kevin Feasel

Jess Pomfret shows us an example of creating mandatory parameters but also having a default set:

I came across a situation this week where I wanted to add the option of running an existing script,  for a specific server/database combination.  The script currently has no parameters and runs against all servers in the environment from a scheduled task.  I wanted to make sure that behavior didn’t change. The other requirement was that if I specified Server, Database should be a mandatory parameter and vice versa.
The final solution was to add the two parameters to a parameter set and make them both mandatory.  I also had to add a different DefaultParameterSet (thanks to Andrew for this idea), otherwise it defaulted to the defined parameter set, meaning the script always required both Server and Database parameters.

Click through for a demo.

Comments closed

Bulk Insertion With An Identity Column

Published 2018-12-17 by Kevin Feasel

Kenneth Fisher gives us a quick post on bulk insertion against tables with identity columns:

TL;DR; BULK INSERT doesn’t have an easy way to specify a column list so the columns in the insert file must match the columns in the table unless you use a format file or a staging table.
As simple as they appear on the surface identity columns are fairly complicated things. BULK INSERT, on the other hand, is pretty complicated even on the surface (at least as far as I’m concerned). Because of this, the combination can get even worse. When you do an insert into a table that has an identity column you usually just ignore that column and it’s automatically filled in. However, that’s not true with BULK INSERT. Unless you use a format file, the number of columns (and their order) in the file has to match the table.

Read the whole thing.

Comments closed

Availability Groups And Read-Only Filegroups

Published 2018-12-17 by Kevin Feasel

Allan Hirt walks us through a couple of scenarios involving databases with read-only filegroups using Always On Availability Groups:

A question came across my inbox this week which I decided to investigate: what happens if you have read only filegroups as part of your database, and you want to use Always On Availability Groups? Let’s find out.
First, I have a database with two filegroups: one read write (PRIMARY) and one read only (ROFG).

Click through for a demonstration of this, as well as a different scenario in which you might want only the read-write data on the secondary.

Comments closed

Using DISTINCT With XML Data

Published 2018-12-17 by Kevin Feasel

Dave Bland has a workaround for a limitation with processing XML in SQL Server:

Since I was working on an example for my next performance class, I decided to use an example from that session.  In the query below the qp.* part of the column list will return a column named query_plan which is an XML data type.
SELECT DISTINCT pa.,cp.,qp.* 
FROM
(SELECT plan_handle
FROM sys.dm_exec_cached_plans) cp 
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle ) qp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) As pa
WHERE is_cache_key = 1 AND pa.attribute = ‘set_options’
However, when I add the DISTINCT keyword I get the error below.

Read on for a workaround for this.

Comments closed

What’s New In Ambari 2.7

Published 2018-12-14 by Kevin Feasel

Paul Codding and Kat Petre share some of the new features in Ambari 2.7:

With this release, we wanted to make Ambari more enjoyable to use every day, simplify finding and using our API, and unblock teams managing very large clusters.  Here is a preview of a few features we’re excited to share with you:
Revamped UI – Our Hortonworks Product Design Team has created a great new UI design system, and we’ve taken the time to update Ambari to use the new styles, colors, and navigation concepts from it.

I’ll have to rebuild my home Hadoop cluster someday, so I’ll have to check this out when I do.

Comments closed

Deploying An R Service To Azure Kubernetes Service

Published 2018-12-14 by Kevin Feasel

Hong Ooi shows us how we can use Azure Container Registry and Azure Kubernetes Service to deploy an R model via Plumber:

If you run this code, you should see a lot of output indicating that R is downloading, compiling and installing randomForest, and finally that the image is being pushed to Azure. (You will see this output even if your machine already has the randomForest package installed. This is because the package is being installed to the R session inside the container, which is distinct from the one running the code shown here.)
All docker calls in AzureContainers, like the one to build the image, return the actual docker commandline as the cmdline attribute of the (invisible) returned value. In this case, the commandline is docker build -t bos_rf . Similarly, the push() method actually involves two Docker calls, one to retag the image, and the second to do the actual pushing; the returned value in this case will be a 2-component list with the command lines being docker tag bos_rf deployreg.azurecr.io/bos_rf and docker push deployreg.azurecr.io/bos_rf.

I love this confluence of technologies and at the same time get a “descent into madness” feeling from the sheer number of worlds colliding.

Comments closed

The Power BI Visual Vocabulary

Published 2018-12-14 by Kevin Feasel

Jason Thomas has put together a great Power BI report:

Note that there are some R/Python visuals and currently, R/Python visuals are not available on “Publish to Web”. Hence, I have just used a checkbox on the top of the report to show the images wherever R visuals are used (can be identified by the colorful border around the image). However, you can download the source file and then publish it to your tenant, and see the actual R visuals there in a browser by unselecting the checkbox. You can also look at the pbix file and see the source code behind the visuals.

Definitely check this out.  Jason did a great job.

Comments closed

UI Versus UX

Published 2018-12-14 by Kevin Feasel

Rajeev Thakur explains the differences between UI and UX and how they fit together:

Most of the people swap UI with UX and that’s the most common mistake. Let’s understand their difference.
UI mainly focuses on the look of your application. It is the process of improving the interactivity and presentation of your web or mobile app. Being a UI designer you need to have creative and convergent thinking, so you can improve its look and contribute to better user interaction with the application. With the unique visualization of UI designer, we can have every screen page, buttons and other visual elements of the app look intuitive. UI Designer must also have basic knowledge of the tools in order to create a better app UI plus keeping in mind the user’s requirements. Tools that designer basically use are Adobe XD, Adobe Photoshop, Illustrator and sketch.
Whereas UX is all about creating the basic skeleton of any application. It works on wireframing of an application and structuring all its components appropriately to create the user flow. The thought process of a UX Designer must be both a mix of critical and creative thinking. UX design is more of a human-centric design an enhancement of user’s experience is the main goal here. User’s needs and research play a significant role here. Usability testing must also be done frequently after the basic skeleton of the app has been prepared because that helps in cross-checking all the components.

Read the whole thing.

Comments closed

Fill Factor And The Performance Tradeoff

Published 2018-12-14 by Kevin Feasel

Tara Kizer explains the performance tradeoff when setting fill factor for an index:

There are workloads where frequent page splits are a problem. I thought I had a system like this many years ago, so I tested various fill factor settings for the culprit table’s clustered index. While insert performance improved by lowering the fill factor, read performance drastically got worse. Read performance was deemed much more critical than write performance on this system. I abandoned that change and instead recommended a table design change since it made sense for that particular table.

Click through for a demo.

Comments closed