Press "Enter" to skip to content

Category: Administration

Building a SQL Cluster Lab

Published 2019-10-31 by Kevin Feasel

Ryan Adams has started a series on building a Windows cluster in Hyper-V and layering SQL Server on top of it:

Before we start to build a SQL Cluster Lab, let’s look at the desired result. You will build a 3-node cluster replicating an environment that has two data centers. As a result, the first two nodes will reside in data center 1 and the third node in data center 2. We are creating this architecture because it is the most common architecture I see for Availability Groups. It is multi-subnet and can solve for both HA and DR.

You will notice the domain controller in the middle. That piece is certainly not representative of a production environment. However, we are using it in our lab for several different functions and being a router is one of them.

Part 1 is the only part which is currently up, but this looks like it will be a good one. Go buy a couple more sticks of RAM for your PC and get reading.

Comments closed

SQL Server 2019: Database Snapshots and In-Memory Filegroups

Published 2019-10-31 by Kevin Feasel

Niko Neugebauer points out a small but interesting addition to SQL Server 2019:

Database Snapshots can serve for so many great purposes, such as Quick Restores and DWH/BI/Reporting reading operations, between others.

In-Memory is still the feature that I love a lot, and while the most active Programming Feature has not seen a lot (or enough) development, because frankly we are back to “Chicken & Egg, Who was first” kind of problem, I see little but important developments and most importantly feature integration. The original implementation in Sql Server 2014 and further improvements in Sql Server 2016 & Sql Server 2017 have improved the programming surface to being useful member of the toolkit, but some of the current limitations are dreading for a number of people and projects.

Well, it seems that Microsoft has been silently working on the improvements and one of them is the support for the Database Snapshots of the In-Memory File Groups in the Sql Server 2019.

I haven’t used database snapshots in a while, but I was fond of them for testing purposes and even wrote an ugly WinForms app to let devs manage them at a prior company.

Click through for Niko’s demonstration as well as a limitation with this.

Comments closed

Creating a Better Index Maintenance Script

Published 2019-10-30 by Kevin Feasel

Erik Darling, despite being on Team Profiler, has something important to say:

If you’re the kind of person who cares about various caches on your server, like the buffer pool or the plan cache, then you’d wanna measure something totally different. You’d wanna measure how much free space you have on each page, because having a bunch of empty space on each page means your data will take up more space in memory when you read it in there from disk.

You could do that with the column avg_page_space_used_in_percent.

BUT…

Read the whole thing.

Comments closed

Simulating ON DELETE CASCADE

Published 2019-10-29 by Kevin Feasel

Aaron Bertrand has put together a procedure which simulates what a cascading delete would look like given your existing foreign keys:

A user recently asked about an interesting scenario involving tables with foreign key relationships. They wanted to generate the DELETE statements that would allow them to manually delete from child tables first (perhaps in stages), based on criteria they define, and tell them – before performing the deletes – how many rows the operation would affect in each table. They wanted output like this:

DELETE dbo.ChildTable1 WHERE ParentID < <some constant>; -- This would delete 47 row(s).

DELETE dbo.ChildTable2 WHERE ParentID < <some constant>; -- This would delete 14 row(s).
...
DELETE dbo.ParentTable WHERE ID < <some constant>; -- This would delete 11 row(s).

Click through for the solution as well as several caveats.

Comments closed

Emergency Mode in SQL Server

Published 2019-10-28 by Kevin Feasel

Paul Randal answers a reader question:

I had a blog comment question a few days ago that asked why emergency-mode repair requires the database to be in EMERGENCY mode as well as SINGLE_USER mode.

All repair operations that DBCC CHECKDB (and related commands) performs require the database to be in single-user mode so there’s a guarantee that nothing can be changing while the checks and repairs are done. But that doesn’t change the behavior of what repair does – that needs emergency mode too.

Read on for an explanation of what emergency mode is and why we need it to run CHECKDB repair operations.

Comments closed

Database Restoration and the Plan Cache

Published 2019-10-25 by Kevin Feasel

Andy Mallon has some tests for us:

If you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine)

Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: ” SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations”. This message is logged every five minutes as long as the cache is flushed within that time interval.

Yikes. That first sentence sounds like it is going to clear the cache for the entire instance.

Read on as Andy tests this and (spoiler alert) changes the documentation.

Comments closed

Accelerated Database Recovery

Published 2019-10-22 by Kevin Feasel

Andy Mallon explains the concept of Accelerated Database Recovery:

Accelerated Database Recovery(ADR) is a new feature intended to speed up the recovery process, which could be very slow, particularly when there are long-running, large transactions. ADR is not just for recovery after a crash, but also helps in other scenarios where the transaction log needs to be recovered–including Availability Group secondary redo and Failover Cluster Instance failovers.

This is one of the most interesting new features in SQL Server 2019.

Comments closed

Upgrading Servers in an Availability Group

Published 2019-10-18 by Kevin Feasel

Thomas Rushton has a checklist for upgrading servers connected by an Availability Group:

This is a checklist put together and followed for an upgrade of a pair of physical SQL Server 2012 servers which hosted a single availability group of several terabytes of data with minimal downtime.

The availability group was configured with synchronous commit and automatic failover.

Minimizing downtime here is great, but it’s not automatic: you still need to do work on your end to get this right.

Comments closed

Deploying CUs to Multiple Instances with Powershell

Published 2019-10-07 by Kevin Feasel

Jeff Iannucci embraces the power of the shell:

This all started because we had some 14 new SQL Server 2017 instances that we were setting up, but we hadn’t yet applied the most recent cumulative update that we are using in our environment.  I started using the Update-DbaInstance cmdlet in the script below to apply to one server, but then I looked at the list of outstanding requests and thought about something Buck Woody once told me.

“You don’t have time for that. You’re going to be dead soon.”

He’s a fantastic fellow, but we should all be grateful he didn’t become a physician.

Click through for the five-line script and an explanation of what each line does.

Comments closed