Always On Failover Cluster Instance

Posted on August 12, 2019June 19, 2020 by Garland MacNeill

Recently, I was working with a gentleman in our IT department and we were discussing adding redundancy to a couple of SQL Servers that were used for web apps. Turns out, VMware’s disk consolidation can, and will, take a server offline. That tends to make customers unhappy, and rightly so. Anyway…I digress.

We were discussing adding redundancy to a couple of single-node environments and the gentleman kept referencing Always On, so naturally, I thought he was talking about Availability Groups. Another lesson here is to listen completely and not just for buzz words. So did he, in a fashion. He had seen what the licensing changes were with SQL2016 SP1, basic availability groups were an option on the standard license. I pointed out that basic AGs only allowed for one DB to be put into the AG per listener. In our case, that wouldn’t work. The IT gentleman continued to push, pointing out that AlwaysOn Failover Cluster Instance would work under the Standard license with two nodes.

Then it hit me

Microsoft’s marketing team throws a monkey wrench in the terminology. When I looked up what AlwaysOn Failover Cluster Instance really is, I realized it read a lot like traditional SQL clusters. You know, two nodes, shared storage, only node active at a time, with 30-90 second fail overtimes. That IS a traditional SQL cluster.

I don’t know why MS decided to rename something that has been around since forever, but they did. It’s probably more likely that I missed a press release or some other email. If that’s the case then this post is probably redundant. However, if someone else gets caught unaware, hopefully, this comes up on Google to quickly help them.

Space: Unused vs Unallocated

Posted on March 7, 2018March 7, 2018 by Garland MacNeill

I had previously blogged about working on a SQL job that deleted data older than 13 months. Today I thought I would follow up with the database and check to see how much space was available in the database. So I ran a disk usage report out of SSMS and got the following result:

Wait, what?

I was looking at the report and trying to decipher what the difference is between unused space and unallocated space. It’s not very intuitive is it? My OCD was going to let this go, so I started Googling. What I found was a lot of confusion over the matter until I found this forum post on MSDN.

Essentially the difference between unused and unallocated is this:

Unallocated – free space in side the data file. (what we think of)

Unused – empty space/extents that are reserved for an object.

I couldn’t understand how I would have empty space in an object after I had removed data. Then it dawned on me, it is most likely space reserved in the indexes that will be released when indexes are rebuilt.

Ok all and all, not that great of a revelation, but when you’re looking at the report it doesn’t make a lot of sense at a glance. Hey Microsoft, some kind of pie chart legend would be good here. Just saying.

Check for corruption

Posted on July 16, 2017July 25, 2017 by Garland MacNeill

If you work, or have worked, in a shop with lots of SQL servers then you either have, or will, experience an issue with storage. Like for instance you’re storage controllers go down, knocking down your SQL instances. As we all know, this is a good way to corrupt databases. That’s why Sys Admins should never just hit the power button on a database servers, but I digress.

I’ve recently encountered this problem recently, a couple of times in fact. While weekly DBCC checks are run, I was tasked with a way to check lots of databases for corruption quickly. It was decided that we would only do a physical check with DBCC, the databases should be under 1GB in size, and we wanted to be able to run the query across multiple servers quickly. Obviously the size of the database can be modified.

I came up with this.

--create table variable

declare @dbcc_physical Table (
 name varchar(50) not null
);

-- select database names who are under 1GB in size, and store in table variable
INSERT INTO @dbcc_physical
SELECT name from sys.master_files
WHERE type = 0 
AND size < 1024
AND name != 'modeldev';


-- create dynamic sql to execute dbcc check
DECLARE @tableCursor CURSOR,
@databaseName VARCHAR(100);

SET @tableCursor = CURSOR FOR SELECT * FROM @dbcc_physical;

OPEN @tableCursor;
FETCH NEXT FROM @tableCursor INTO @databaseName;
WHILE(@@FETCH_STATUS = 0)
BEGIN;
EXECUTE ('DBCC CHECKDB(' + @databaseName + ') WITH PHYSICAL_ONLY;');

FETCH NEXT FROM @tableCursor INTO @databaseName
END;
 
CLOSE @tableCursor;
DEALLOCATE @tableCursor;

SQL 2017 and the future

Posted on April 19, 2017April 21, 2017 by Garland MacNeill

WOW. That’s all I have to say after watching Microsoft’s 4/19 Data AMP Presentation. SQL 2017 is the official name of SQL vNext, otherwise known as SQL on Linux. We knew about SQL on Linux last year, so this isn’t some new revelation. That is, until Microsoft announced Python and R support for SQL 2017. And not just support, but IN QUERY support. Then there was demo’s of a ton of other stuff geared towards data warehouse and business intelligence, as well as extended capabilities of Azure.

Did I mention Python support for SQL on Linux (sorry SQL 2017)? Ya, I’m a little too excited about that I think. You can’t blame me though. 20 years ago, it would have dismissed as a pipe dream because Linux was this cute little cousin to Unix. 10 years ago, it would have been heresy to mention SQL running on an open source technology. Today, it became reality. The little IT kid in me as all kinds of … well, giddy.

The landscape is changing

What else did I get out of this presentation? Well I could try to talk about PowerBI, the Data Lake stuff, the Data Warehouse capabilities, or Azure but I would be talking out my backside as I just don’t have much experience with those technologies. And I’m starting to realize that might just be a problem.

We all have our specialities, but I’m starting to wrap my mind around the idea that maybe dipping a toe into these other worlds isn’t a bad thing. At least from the sense that you’ll get a better understanding of the overall picture. Not to mention, you never know when knowing a little a bit about other areas of SQL might pay off or help you solve a problem quicker.

What did resonate pretty well with me though, is Azure is becoming a force. Maybe it has been for awhile and I just haven’t looked up long enough, or often enough to notice. If you can get access to the latest technology without the expense of the hardware, finding the expertise to run said hardware, and it scales quickly….just how are on premise data centers suppose to keep up? Not cheaply, that’s for sure.

SQL 2017 Enhancements — Highlights of some of SQL 2017’s enhancements

Career Path

Which brings me to my ultimate realization after today’s presentation. Career wise, DBAs have to evolve. I know it’s been said a thousand times, but the days of running backups, granting permissions, and the other daily dba tasks are riding off into the sunset for all but those who work in data centers such as Azure, Google, or AWS.

That means for the rest of the DBAs to continue to be employed as data professionals they have to learn new skills. Unless I am missing something, that means focusing on development skills. Not just SQL either. They are going to have to know things like R, Python, PowerShell, and probably some C#, including .NET framework. That might not be the only thing, as Thomas LaRock (b/t) thinks there might be other areas to invest in as well.

I mean, you can’t ignore these areas any longer. Microsoft was demoing Data Lake enhancements against databases that were Petabytes in size. That’s plural Petabyte. I remember when they told us we’d never be able to fill a gigabyte drive. I was just getting accustomed to the handful of database that I support that are under 10TB.

On the upside, I think it has supplied me with some direction as to where I need to focus my energy.

Tempdb Saga

Posted on May 4, 2016June 8, 2020 by Garland MacNeill

Tempdb and AAGs

I have been working on standing up about a multi-node Availability Group “cluster” (I put quotes around the cluster because…well…AGs aren’t clusters, are they?). Now that the big node is stood up, I noticed some alerts about tempdb contention. Honestly, I’m not shocked. This environment is going to be a large data warehouse environment, and the developers using it aren’t always mindful of the result size of their queries, so I had already allocated eight tempdb files. I know Microsoft recommends adding four files at a time after establishing the first 8.

Buford mocking me — **Buford is mocking me**

So with that, I add four tempdb files and don’t think too much about it. Except, when I added the files, I didn’t think to expand them (one of those just before lunch things). A couple of days later, I noticed the added files were just sitting there, not being used. They were still the same size I had created them at. Hmmm, why is that? A little research turned up that SQL will give more weight to the larger, more active files. Damn you proportional fill!!!

Guess I’ll fix that

So I start out trying to rectify my n00bish mistake. The first task, getting the first eight files shrank down to make room to enlarge the other 4.

[box]I know I could have just restarted. Yes, I know that shrinking tempdb is futile. Yes, I know that, according to Brent Ozar, I am playing in the public outhouse. But as this server was kind of in use, I decided against a restart. Besides, it’s an opportunity to learn right?[/box]

As I was saying, I need to rebalance these files out. So I needed to shrink some of them down. That’s when I started running into the work table errors. Dammit.

Error: Page blah of blah: could not be moved because it is a work table page.

Off to Google. Within a few minutes of Googling, I find an article at Brent Ozar addressing the problem. Tara Kizer to the rescue, use DBCC FREEPROCCACHE. Success, some of the files shrink.

[box]Tara’s article also links to Paul Randall’s blog and Microsoft’s KB article telling us it’s ok to shrink to TempDB. Thanks for finding that Tara![/box]

Others, not so much. In fact, I get another “error”:

Luckily I find an answer over on SQLServerCentral. All I need to do is bump the size of the file(s) I can’t shrink by a few MBs to get SQL to quit thinking they are in the process of being shrank…shrunk?…whatever the correct adjective is. Thankfully that worked and I could 1) shrink the original 8 files to a consistent size and 2) increase the size of the 4 added files.

What’s the moral of the story? I shouldn’t add temp files right before lunch and when you add temp files, make sure you add them at the right size…..otherwise, SQL will ignore them until the restart.