Press "Enter" to skip to content

Category: Configuration

SQL Server Configuration Settings Requiring Restarts

Published 2021-03-04 by Kevin Feasel

Randolph West enumerates configuration settings which do (or do not) require a restart of the SQL Server database engine:

SQL Server is a complex beast, with many configuration options that can range from recommended to completely avoided.

Since the release of SQL Server 2016, several options that were recommended post-install have been rolled into the default installation options and no longer need to be done, and similar changes were made with SQL Server 2017. Even so, there are configuration changes we data professionals need to make after installation, during maintenance windows, and sometimes even during operating hours, so here’s a handy list of changes that do and don’t require a restart of your operating system or SQL Server instance.

As Randolph mentions, this set is not conclusive. For example, enabling PolyBase requires a restart of the database engine. I believe enabling ML Services technically does not, though I do out of caution because the back of my mind remembers something weird about the service’s behavior if you don’t restart the database engine, but p > 0 my brain made up the whole thing.

Comments closed

Working with SQL Server Configuration Files

Published 2020-07-29 by Kevin Feasel

Jamie Wick takes us through an underrated part of the SQL Server installer:

The ability to use a parameter file (configurationfile.ini), for automating the installation of SQL Server, has been around for many years. However, each release of SQL Server has had different parameters that could be included in the file. Here are some directions on how to find or create a parameter file, along with the parameter values that are supported by each version of SQL Server.

I appreciate the fact that every installation of SQL Server generates one of these and even points it out to you as you go through the installer wizard. And Jamie has gone a step further by giving us an Excel spreadsheet with all of the available settings and their defaults.

Comments closed

Configuring Kubernetes Pod Eviction Time

Published 2020-04-08 by Kevin Feasel

Andrew Pruski is a Kubernetes slumlord:

The default time that it takes from a node being reported as not-ready to the pods being moved is 5 minutes.

This really isn’t a problem if you have multiple pods running under a single deployment. The pods on the healthy nodes will handle any requests made whilst the pod(s) on the downed node are waiting to be moved.

But what happens when you only have one pod in a deployment? Say, when you’re running SQL Server in Kubernetes? Five minutes really isn’t an acceptable time for your SQL instance to be offline.

Click through to see how to handle this scenario.

Comments closed

Checking Spark Config on Windows

Published 2019-08-29 by Kevin Feasel

Ed Elliott has a Powershell script to tell you if your Spark configuration on Windows is incorrect:

There are some pretty common mistakes people make (myself included!), most common I have seen recently have been having a semi-colon in JAVA_HOME/SPARK_HOME/HADOOP_HOME or having HADOOP_HOME not point to a directory with a bin folder which contains winutils.

To help, I have written a small powershell script that a) validates that the setup is correct and then b) runs one of the spark examples to prove that everything is setup correctly.

Click through for the script.

Comments closed

Configuring dbatools

Published 2019-08-22 by Kevin Feasel

Claudio Silva shares some tips on configuring dbatools:

Set a new configuration value
To update a value you need to use the Set-DbatoolsConfig command. Unfortunately, you will not find documentation for this command on our docs page. This is a known issue and it happens because that command is a cmdlet so the help is in the dbatools library itself.

For this particular case, you can and should rely on the Get-Help command.

Get-Help -Name Set-DbatoolsConfig -Full

Claudio has a half-dozen or so settings and there are more available to you.

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

Storing An Encrypted Password In The Solr Configuration File

Published 2018-05-18 by Kevin Feasel

Jon Morisi shows us how to store an encrypted password in Solr’s configuration file, rather than storing the password in plaintext:

The config file has a lot of options, in short this is where you configure a database connection string and reference your jdbc jar file. Full details are here.  By default any of the examples that come with the Solr distribution use a plain text username and password.  This can be potentially viewed from the front end:
http://hostname:8983/solr/ > Select Collection from the drop-down > Click data Import > expand configuration
Obviously we do not want to store our username and password in plain text.  The config file includes an option to encrypt the password and then store the key in a separate file.

Storing passwords in plaintext is a classic mistake that I see far too often.  And then when someone checks in that config file to a public GitHub repo…

Comments closed

Enabling Optimizer Fixes In SQL Server

Published 2018-02-23 by Kevin Feasel

Monica Rathbun explains that just upgrading a SQL Server database doesn’t enable optimizer changes:

When applying a new SQL Server cumulative update, hot fix, or upgrade SQL Server doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you had already stay at their pre-upgrade compatibility level, which means they run under the older set of optimizer rules. Additionally, many optimizer fixes are not turned on. The reason for this is that while they may improve overall query performance, they may have negative impact to some queries. Microsoft actively avoids making breaking changes to its software.

To avoid any negative performance impacts, Microsoft has hidden optimizer fixes behind a trace flag, giving admins the option to enable or disable the updated fixes. To take advantage of optimizer fixes or improvements you would have enable trace flag 4199 after applying each hot fix or update or set it up as a startup parameter. Did you know this? This was something I learned while working with an existing system, years into my career. I honestly assumed it would just apply any applicable changes that were in the patch to my system. Trace flag 4199 was introduced in the SQL Server 2005-era. In SQL Server 2014, when Microsoft made changes to the cardinality estimator they protected the changes with trace flags as well, giving you the option to run under compatibility level 120 and not have the cardinality estimator changes in effect.

Things changed starting with SQL Server 2016.

Click through to see how SQL Server 2016 made it a bit easier.

Comments closed

Comparing Server Configurations With Powershell

Published 2017-09-14 by Kevin Feasel

Andy Levy shows how to use a dbatools cmdlet, Get-DbaSpConfigure:

I started with Get-DbaSpConfigure to retrieve the settings available from sp_configureas these were the most important to my comparison. I ran this against production as well as each of my test instances and saved the results of each to a variable. Because accessing my production instance requires either jumping through hoops or using SQL Authentication, I passed -SqlCredential (get-credential -Message "Prod" -UserName MySQLLogin) so I’d be prompted for that password instead of using Windows Authentication.

It’s good to have an automated process in place to script comparisons, either against a known good build or across servers which ought to be the same.  Things that ought to be the same often aren’t.

Comments closed