Press "Enter" to skip to content

Category: Administration

NUMA Node Count

Published 2016-05-26 by Kevin Feasel

Denny Cherry shows us how to find the number of NUMA nodes a SQL Server has:

The next way to see what your NUMA configuration is, is to look at the ERRORLOG file. Every time SQL Server is started it will log the NUMA configuration towards the beginning of the ERRORLOG file. That line will look something like this example.

Server Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

Denny shows four different ways of getting this answer, and then provides some general advice on the maximum number of nodes you probably want.

Comments closed

Monitoring MapR With ELK

Published 2016-05-19 by Kevin Feasel

Mathieu Dumoulin shows how to feed MapR metrics into ElasticSearch and monitor with Kibana:

There are several ways to keep the data updated: a cron job, a linux daemon running as a service, or a stream tool such as Streamsets.

The easiest way might be to run the task as a cron job with an interval of one to thirty seconds depending on monitoring needs. This may be suitable for a proof of concept or a small test cluster or even a production cluster. The main drawback of using a cron is that the control over the execution is limited to running the script and resources aren’t shared, meaning we are opening and closing a connection to Elasticsearch as well as doing the work to call the rest endpoint for each invocation.

Kibana makes for some pretty dashboards.

Comments closed

Minion CheckDB Beta

Published 2016-05-19 by Kevin Feasel

Sean McCown has released a beta of Minion CheckDB:

We’ve had many of you asking to be part of the Minion CheckDB beta and now is the time. We’re putting the finishing touches on the 1st beta and it’s looking great with some fabulous features.
So this is the open call for beta users. If you’d like to meet Codex before anyone else then send me an email.
We have some requirements though. We don’t want dead beta users. This is your chance to shape the product and we want to hear from you. So if you’re serious about putting the product through its paces then we definitely want you. So you should be ready to provide real feedback, report bugs as you find them, and work with us to fix them.

Considering that I’ve bothered Sean about this at every SQL Saturday the two of us have been at this year, I’d better get moving and join the beta.

Comments closed

DBA Scripts Update

Published 2016-05-18 by Kevin Feasel

Rob Sewell has updated his DBA-Database project:

By making use of the dbo.InstanceList in my DBA database I am able to target instances, by SQL Version, OS Version, Environment, Data Centre, System, Client or any other variable I choose. An agent job that runs every night will automatically pick up the instances and the scripts that are marked as needing installing. This is great when people release updates to the above scripts allowing you to target the development environment and test before they get put onto live.

I talked to a lot of people in Hannover and they all suggested that I placed the scripts onto GitHub and after some how-to instructions from a few people (Thank you Luke) I spent the weekend updating and cleaning up the code and you can now find it on GitHub here

Check out his solution, especially if you do not already have an administrative database on your instances.

Comments closed

Disable Performance Counters

Published 2016-05-18 by Kevin Feasel

Dan Taylor shows how to disable SQL Server performance counters using a registry setting:

About 6 or 7 years ago, I had an issue where my SQL Server performance counters were not available when looking within performance monitor. I went thru the steps of unloading and reloading the counters according to Microsoft documentation and by looking at many of the blog posts out there. The performance counters still would not show.

My next step was to dig into the registry and look at the performance counters entry for SQL Server.

I’d have to imagine that the number of good use cases for disabling performance counters is low, but it’s occasionally necessary for troubleshooting.

Comments closed

“RAID” And Backups

Published 2016-05-17 by Kevin Feasel

Kenneth Fisher explains that you can set up backup strategies similar to different RAID types:

RAID 0

Splitting the backup data between multiple files.

This is actually a fairly common way to speed up a large backup. IO is one of the slowest part of the whole process so by splitting the backup into multiple files we can reduce our backup time by quite a bit. Having multiple files will also increase your restore time but you do have to make sure that all of the files are available. If you lose one file then the whole backup is useless.

This is done by listing multiple locations for the backup.

This is an interesting use of RAID as metaphor.

Comments closed

Working With Windows Server Core

Published 2016-05-16 by Kevin Feasel

Sander Stad has a couple of blog posts on working with Windows Server Core edition.  First, what happens if you lose your command prompt?

In my enthusiasm I clicked the “X” on the top right corner. So this happened:

Mayhem! How do I get my command screen back? Reboot?! NO WAY!

After figuring that out, Sander also explains how to perform updates:

The Windows Server Core Edition is a really good option because there aren’t that many binaries as there would be in a full installation. Due to the smaller number of objects you more stability, simplified management, reduced maintenance and a reduced risk to get attacked.

So you have a Windows Server Core Edition installed but want to update the server manually. Maybe this is a virtual machine on your local PC that needs updating and you don’t have WSUS running.

About everything you previously managed using the GUI in Windows is now done with the sconfig. You can edit your server’s name, the domain, network settings, date/time, shutdown the server but also manage the updates.

Admittedly, most Core installations will probably be in environments with a lot of automation around them, but sometimes you’re just doing a one-off thing.

Comments closed

Setting Up A Linked Server To Oracle

Published 2016-05-12 by Kevin Feasel

Jon Morisi steps in to show how to set up a linked server connection to an Oracle database:

In this dialog box, the “TNS Service Name” drop down box should display your entries from the tnsnames.ora file.  Next, enter your Oracle User ID and click “Test Connection”, at which point you’ll be prompted for your password.  Everything should test successfully at this point.

Now would be a good time to restart.  Unfortunately, yes you need to restart…

You can do an additional test via sqlplus.  Open a windows command prompt and enter the following:

sqlplus user/pass@[addressname]

(Where addressname is one of your connections from tnsnames.ora)

I readily admit that I’m glad I don’t need to work with Oracle.  Nonetheless, if you do need to integrate the two, this step-by-step guide will show you how.

Comments closed

Database Detachments And File Permissions

Published 2016-05-12 by Kevin Feasel

Daniel Hutmacher looks at what happens when you detach a database:

On most database servers, the SQL Server service account is granted full control of the directories that host the database files. It goes without saying that the service account that SQL Server runs on should be able to create, read, write and delete database files. Looking at a sample database on my local server, the .mdf and .ldf files don’t actually inherit permissions from their folder, although the permissions are very similar to that of the folder.

This all makes sense once you read the explanation, but it’s not intuitive behavior.  Read Daniel’s gotcha near the end of the post.

Comments closed

DB File Space Usage

Published 2016-05-11 by Kevin Feasel

Andy Mallon looks into sys.dm_db_file_space_usage:

SQL Server 2016 is coming out next month, and it’s chock full of new features that I can learn. Row-level security is interesting. Dynamic data masking sounds fun.

If you’re a regular reader of my blog, you probably know I try to approach questions from a unique angle. Instead of blogging about something cutting edge or sexy, I decided to scroll through the list of system views until I found one I didn’t recognize.

sys.dm_db_file_space_usage

The name is pretty self-explanatory, but I never noticed this existed until now. Seems like the type of DMV that I should have known about, but I didn’t. Quick look at BOL, and I got the verbose description from Microsoft:

Andy goes on to compare the outputs from this DMV to methods he’s historically used.

Comments closed