Category: Administration

Monica Rathbun had a major performance problem; this is how she solved it:

Symptoms:

• Very High Disk Latency as high as 300,000 milliseconds (ms) is not unusual
• Average: 900 – 15,000ms
• Memory Pressure
• Slow User Experience

Problem:

• Bad hardware
• Over-provisioned VM Hosts (what happens on one VM effects the other)
• Old NetApp SAN
• No infrastructure budget for new hardware

Challenge: Make the system viable with no hardware changes or tweaks

Those disk latencies are scary.  I like the systematic approach Monica takes, and the end result was very positive.

Jason Brimhall shows how to figure out who dropped that database:

What do you do when you run into that missing database situation and the inevitable denial that will ensue?  This is when an audit can save the day.  Through an audit, you can discover who dropped the database and when it happened.  Then you have hard data to take back to the team to again ask what happened.  Taking the info from a previous article of mine, we can alter the script I published there and re-use it for our needs here.

This is available in the default trace or, as Jason points out, you can create an Extended Event (which data can live much longer than that in the default trace).

Kenneth Fisher reviews the new Data Migration Assistant:

First things first the DMA is a replacement of the Upgrade Adviser. In fact it’s an upgrade of the Upgrade Adviser. It has some amazing new features.

• You can install this on a workstation. It doesn’t have to be installed on the server itself.

• You can have multiple projects saved with data from multiple server/instances.

• There is the option to get compatibility issues and/or new feature recommendations.

• You can check issues/feature recommendations for upgrades to 2012, 2014 or 2016.

Looks like it’s a step up from the old Upgrade Advisor.

Dave Mason has a few scripts he uses to pull database metrics from his SQL Server instances:

There is a total of “N” linked servers. The query selects from each one and combines the results via UNION ALL. Here’s a sample of the output on my CMS:

What we’ve seen so far could be used as a template for other queries, such as a backup report, a database files report, etc. The code could be put in a stored procedure and run as needed for automated tasks. I’ve used similar code to retrieve data for an SSRS report.

Let’s examine a problem you’re likely to encounter by including @@SERVERNAME within the query

Give it a read.

Wayne Sheffield shows what to do when you need to rename your SQL Server instance:

Sometimes you make a mistake, and forget to rename a syspred’d server before installing SQL Server. Or perhaps your corporate naming standard has changed, and you need to rename a server. Maybe you like to waste the time involved in troubleshooting connection issues after a server rename. In any case, you now find yourself where the name of the SQL Server is different than the physical name of the server itself, and you need to rename SQL Server to match the server’s physical name.

You could always rerun the setup program to rename the server. Fortunately, SQL Server provides an easier way to do this. You just need to run two stored procedures:sp_dropserver and sp_addserver.

Click through for details, including important considerations.

Dave Mason defends the honor of linked servers:

I seem to be in the minority when it comes to SQL Server linked servers. When it’s another SQL Server instance on the other end, I quite like them for administrative purposes. But other SQL pros have some reservations and gripes. I’ve even seen the word “hate” thrown around freely. Most of the complaints seem to fall into one of these categories: poor performance, insufficient permissions, poorly configured security, and challenges related to remote execution of queries.

I think Dave’s reasoning makes a lot of sense.  Linked servers are not themselves evil.  I think it’s likely a mistake to incorporate them into your mainline application (a mistake I’ve made in the past), but for the kinds of administrative tasks Dave mentions, it’s certainly not a bad idea.