Press "Enter" to skip to content

Category: Administration

Test Restores

Published 2016-10-12 by Kevin Feasel

Steve Jones implores you to test those database backups by restoring them somewhere:

What do you do? Hopefully you recognize the issue and can fix the issue. Maybe more importantly, you have a backup of the missing certificate.

Most people don’t deal with encryption, but you never know when your backup job might start failing, perhaps writing to a damaged file that appears to work (if you write as a device) but really isn’t capturing the backup file. Perhaps you don’t know that your backups are being written to a location and deleted a day later, but the process that is supposed to copy them to tape or a remote file share is broken.

Any number of things can happen. The point is that you want to be sure that you are actually getting useable backup files.

That means testing restores.

Read the whole thing.

Comments closed

CAP_CPU_PERCENT

Published 2016-10-12 by Kevin Feasel

Robert Davis looks at the CAP_CPU_PERCENT option in Resource Governor:

The need for this setting came about because MAX_CPU_PERCENT is not applied unless the server is busy. This could lead to a situation where queries in a low priority resource pool starts running while the server is idle and are allowed to consume all the CPU they can. Then high priority queries spin up, and they can’t immediately get the CPU they need due to the low priority queries not being capped. CAP_CPU_PERCENT came along and was designed to set a hard limit that the queries in a pool could not go over even if the server is idle. For example, if you cap the CPU at 25%, the queries in the pool will not exceed 25% no matter how idle the server is.

Problem solved, right?

When the end of a section is a yes/no question, the answer is usually “no.”  Read on before this burns you.

Comments closed

Nothing New Under The Sun

Published 2016-10-12 by Kevin Feasel

Kevin Hill reminisces and warns:

Installation defaults that are going to bite you (not version specific, and the installer is getting better):

  • Files all on the C drive

  • One TempDB data file (improved in SQL 2016)

  • Backups on C drive

  • No automated backups

  • Allow SQL to use ALL the memory

  • Allow SQL to use ALL the CPUs

  • Builtin\Administrators group not default*

  • Compressed backup set to OFF

There’s good advice here, so read on.

Comments closed

Who Monitors The Monitors?

Published 2016-10-12 by Kevin Feasel

Dave Mason discusses monitors and what happens when they fail:

I was reminded of this recently in my little SQL Server world. I have a number of garden variety alerts set up, plus some other more custom monitoring stuff, which is mostly tied to DDL triggers and event notifications. The one thing all of them have in common is database mail. You can probably guess where I’m going with this. Yep, database mail stopped working. A couple weeks passed before I realized it. Fortunately, out of all the alerts I should have been notified about, none of them were serious.

How would I prevent this happening in the future? I guess I could build another system to monitor my monitoring system. Something like System C, which monitors System B, which monitors System A. But where would that end? System D? System E? Where should the line be drawn? I don’t know that there’s a right answer here, although admittedly, the farther into the alphabet you get, the more absurd it sounds.

At some level, process becomes the answer.  In my case, not before I create a few more systems…

Comments closed

KB2919355

Published 2016-10-12 by Kevin Feasel

Allan Hirt notes that you need KB2919355 installed before you can install SQL Server 2016 on Windows Server 2012 R2 or Windows 8.1:

In my case, I created a new VM with a fresh installation of Windows Server 2012 R2. I also ran Windows Update to ensure it had everything Windows Server thought it required. Figure 3 reflects this status.

As you can see in Figure 4, KB2919355 is not listed as one of the ones WU installed, so it has to be an optional update.

Looking at the list of optional updates in Windows Update in Figure 5, 2919355 is not shown. This means you need to download and install it manually.

It’s never quite as easy as “just run this patch,” so do read on for another gotcha.

Comments closed

Aborting Index Rebuilds

Published 2016-10-07 by Kevin Feasel

Arun Sirpal shows how to use the ABORT_AFTER_WAIT attribute on an index rebuild command:

Looking into the locking you will see that ONLINE operation uses (Sch-M) on the corresponding table as part of the process (actually takes Shared Table Lock (S) at the beginning of the operation, and a Schema Modification Lock (Sch-M at the end)).

So to be granted a SCH-M lock you can’t have any conflicting locks, so what happens when / if you have a process that is updating the table and you want to use the ONLINE rebuild? Yes you will be blocked. With 2014 onwards we can control what happens if we get into this situation and for this post I am going to abort the other query causing me to wait.

Not sure I like the “Kick the other guy(s) off” part that much, but I can see uses.  It’s probably more likely to go the opposite route, cancelling the rebuild if the server’s too hot.

Comments closed

Renaming Servers

Published 2016-10-04 by Kevin Feasel

Steve Jones shows how to rename a server using Powershell:

I’m sure many people are experimenting with VMs and SQL Server. If you’re like me, many of you just default to installing Windows 7/10 or Windows Server xx Standard for your testing. Those systems work fine, but I’ve been trying to build slimmer systems, which means looking at Server Core. Installing Server Core is much the same as other versions, though you end up with only a command line. If you’re like me, using VMWare, you also might end up with a server name like “WIN-LKR3R4FfL5T”.

I want to change that. It’s a fine name if I’m working locally. It’s not to much fun connecting across a network. This post looks at how to rename that machine.

This is probably a good idea to do before installing any major software.  Renaming a server under SQL Server is possible, but there are a few extra steps to the process.

Comments closed

Resource Governor

Published 2016-10-03 by Kevin Feasel

Mark Wilkinson walks through Resource Governor:

Now that we know which statistics we can gather, we need to actually start gathering them. While the DMVs for Resource Governor are great, they will only give you an aggregate of the usage information since the last time the statistics were reset, or the last time services were restarted.

In most cases it makes sense to store your data in a separate table so you can calculate differentials between two time periods. For our example we are only going to be interested in request counts and CPU usage. For this, we will create the following table

Read the whole thing.

Comments closed