Press "Enter" to skip to content

Category: Administration

Copying Databases With dbatools

Published 2017-07-17 by Kevin Feasel

Mike Robbins has a video showing how to copy databases from several SQL Server instances using dbatools:

The video starts out by checking the default instance of SQL Server on a server named SQL17 to see if any user databases exist. Then the names of five different SQL Servers are piped to ForEach-Object. Within the ForEach-Object loop, $_ is a variable for the current object. It’s translated to each individual server name as it iterates through the list of SQL Servers, copying the user databases to SQL17. Only one user database exists on each of the source SQL Servers. The databases are backed up to the specified network share and restored to the destination server. The network share and any sub-folders that are specified must already exist. The account that SQL Server runs as on each of the servers must also have access to the network share. The names of the SQL Servers used in the demo correspond to the version of SQL Server they’re running. The SQL05 server is running Windows Server 2008 (non-R2) and does not have any version of PowerShell installed which means the Copy-SqlDatabase function is extremely versatile.

Click through to watch the video and see how quickly you can get going.

Comments closed

Certificate Copying

Published 2017-07-17 by Kevin Feasel

Brian Carrig shows how to create certificates from binary:

Sometimes it is necessary to copy a certificate from one database to another database. The most common method I have seen to do this is involves taking a backup of the certificate to disk from one database and then restoring the certificate to the other database.

There is however, a lesser known alternative option available, provided you are working with SQL Server 2012 and above. Sadly despite it being 2017, this is not as foregone a conclusion for SQL Server DBAs as it should be. This alternate option is known as CREATE CERTIFICATE FROM BINARY. There are a few caveats with this option. Chief among them is that you cannot use a variable for the binary value, so you will likely end up needing to use some dynamic SQL.

One of the nice aspects to this feature from an administration and a security perspective is that you do not need to worry about accidentally leaving a copy of your certificate on a disk somewhere or having to remember to delete it after you have imported it into your user database.

Read on to see it in action.  Also, it’s about time that Brian started blogging.

Comments closed

Grooming The Error Log

Published 2017-07-14 by Kevin Feasel

Mark Wilkinson explains how to keep your SQL Server error logs in check:

We typically think of error logs as somewhere to go to find issues, but what if your error logs ARE the issue? Like most anything else in SQL Server, if you neglect your error logs you can run into trouble. Even on a low-traffic SQL Server instance, a bad piece of code, or a hardware issue, could easily fill your error logs, and with the introduction of Hekaton in SQL Server 2014, the SQL Server error log started getting a lot more data pumped into it than you might have been used to before. What this means for the DBA is that you can quickly start filling your main system drive (if your SQL install and error logs are in the default location) with massive error logs. So what questions should you be answering about error logs to make sure you don’t run into problems?

Read on to learn more.

Comments closed

Windows OS Setup For DBAs

Published 2017-07-10 by Kevin Feasel

Jeff Mlakar continues his environmental diagnostics guide, this time looking at Windows Server settings:

If Anti-Virus software is running on the SQL host machine there should be exclusions for the following types of files:

  • MDF – these file extensions are associated with SQL Server database files
  • LDF – these file extensions are associated with SQL Server transaction log files
  • BAK – these file extensions are associated with SQL Server backup files
  • TRN – these file extensions are associated with SQL Server trace files

All database related files should be white-listed by your anti-virus product!

If your company has some draconian policy that states every machines must have AV running on it then whitelist the database files. Most SQL machines are not outward facing and have no internet access. They cannot be reached directly from outside the network and there is little need to run scans on it.

Read the whole thing.

Comments closed

Integrating dbatools Into VSCode

Published 2017-07-10 by Kevin Feasel

Andrew Pruski shows how to integrate dbatools Powershell commands into Visual Studio Code:

The first dbatools commands that I looked at are: –

These commands do exactly what they say on the tin. Pretty standard stuff for DBAs but what’s cool is how we can use Visual Studio Code to quickly and easily check that all our databases are being backed up and have a recent (good) CHECK DB.

I’m going to setup two scripts to run the dbatools commands against my SQL instances via Visual Studio Code Tasks.

Read on to see how Andrew did it.

Comments closed

Maintenance Plans In SQL Server 2016

Published 2017-07-10 by Kevin Feasel

Kendra Little describes some changes to maintenance plans in SQL Server 2016:

Working with maintenance plans is supposed to be easy, but I find it to be quite difficult.

In part, this is because they can often be buggy. To write this post today, I had to go back and install SSMS 16.5, because I wasn’t able to configure logging or change some aspects about my test maintenance plan in SSMS 17. (I use case sensitive instances, and this bug also impacts things like the maintenance plan log dialog box.)

And in part this is because the documentation for maintenance plans doesn’t tend to be as clear as the documentation for TSQL commands. So in the interest of saving other folks time, I wanted to share what I learned about the Rebuild Index Task, Reorganize Index Task, and Update Statistics Task in SQL Server 2016.

Though if you’re reading this, you probably already have a better solution than maintenance plans…

Comments closed

Database Connection Leaks

Published 2017-07-10 by Kevin Feasel

Michael J. Swart explains how to find database connection leaks:

So, if your application experiences connection timeouts because of a database connection leak, the stack traces may not help you. Just like an out-of-memory exception due to a memory leak the stack trace has information about the victim, but not the root cause. So where can you go to find the leak?

Even though database connection leaks are a client problem, you can find help from the database server. On the database server, look at connections per process per database to get a rough estimate of the size of each pool:

This is a good thing to remember, particularly if you have a busy system.

Comments closed

Checking For Instant File Initialization

Published 2017-07-06 by Kevin Feasel

Klaas Vandenberghe shows how to use Powershell to determine whether Instant File Initialization is turned on:

Sometimes we want to apply a filter to an array or other collection of objects, but keep both the items that pass the filter and those that fail it. Instead of cycling twice through the collection, there’s a one-step method.

Instant File Initialization is a privilege assigned in the local security policy. Here’s some explanation by MSSQL Tiger Team.
There’s a lot to tell about it, but I’m not going to do that here. Let’s just assume it’s a good thing to assign that privilege to the account with which the SQL Service runs.

Klaas explains how to use Powershell filtering with Where-Object and the Where method for people new to Powershell, and then uses this to figure out if IFI is enabled.

Comments closed

Monitoring On Linux

Published 2017-07-06 by Kevin Feasel

Steven Schneider shows how Microsoft’s SQLCAT monitors SQL Server on Linux:

The following solutions were tested:

  • Graphing with Grafana and Graphite
  • Collection with collectd and Telegraf
  • Storage with Graphite/Whisper and InfluxDB

We landed on a solution which uses InfluxDB, collectd and Grafana. InfluxDB gave us the performance and flexibility we needed, collectd is a light weight tool to collect system performance information, and Grafana is a rich and interactive tool for visualizing the data.
In the sections below, we will provide you with all the steps necessary to setup this same solution in your environment quickly and easily. Details include step-by-step setup and configuration instructions, along with a pointer to the complete GitHub project.

I’ve been a big fan of Grafana since Hortonworks introduced it as the primary monitoring tool in HDP 2.5.  We use Grafana extensively for monitoring SQL on Windows and SQL on Linux.

Comments closed

Fixing Power Settings With T-SQL

Published 2017-07-06 by Kevin Feasel

Randolph West shows how to use T-SQL and xp_cmdshell to switch a server’s power settings from Balanced to High Performance:

Windows has the same setting. It’s in the Power Options under Control Panel, and for all servers, no matter what, it should be set to High Performance.

Here’s a free T-SQL script I wrote that will check for you what the power settings are. We don’t always have desktop access to a server when we are checking diagnostics, but it’s good to know if performance problems can be addressed by a really simple fix that doesn’t require the vehicle to be at rest.

(The script also respects your settings, so if you had xp_cmdshell disabled, it’ll turn it off again when it’s done.)

Click through for the script.

Comments closed