SQL Server
-
Configure SQL Server Failover Cluster Instance on Azure Virtual Machines with MSDTC
Configure SQL Server Failover Cluster Instance If you are running a SQL Server Failover Cluster Instance on premises and looking to migrate to Azure, you might be a little intimidated. This architecture is a bit complex because it uses quite a few different technologies all working together. My goal is to help clarify the pieces…
-
MSDTC Supported Configurations
The MSDTC Configuration Conundrum MSDTC configuration is not as straight forward as you might think. It’s a different choice if you are using a local MSDTC, clustered MSDTC, on-premises, Azure, Failover Cluster, or Availability Group. Every one of those variables leads you down a different path with different choices. The goal of this article is…
-
Read-Scale Availability Group Setup
What is a Read-Scale Availability Group? A Read-Scale Availability Group is a Clusterless Availability Group. It’s sole purpose and design is to scale out a read workload. More importantly is what it is not. It is NOT a High Availability or Disaster Recovery solution. Since this design has no cluster under it, you lose things…
-
Domain Independent Cluster and Availability Group
What is a Domain Independent Cluster? Did you know that you can have a Windows cluster that is a domain independent cluster and run a SQL Server Availability Group on top of it? The technology has been around for a while, but a lot of DBAs are not aware of it. Why? All the glory…
-
Verify Allocation Unit Size for SQL Server
Why Do I Care? Everything in SQL Server is stored on disk in 8K pages. The Microsoft recommended best practice is to format using a 64K allocation unit size. Processes such as the checkpoint and Lazy Writer try to write in extents when possible. Extents are a collection of 8 pages and since each page…
-
How to Upgrade an AG Cluster to Windows 2016
Starting in Windows Server 2012 R2 you now have a way to upgrade a cluster to Windows 2016. The best part is it’s not an OS upgrade, but a rebuild. The magic is that you can join a Windows 2016 server to a Windows 2012 R2 cluster. You can upgrade your cluster with as little…
-
Sync SQL Logins and Jobs
In this post I’ll point you to some options to sync SQL logins and then I’ll demo my favorite option in a video. If you are using Availability Groups or Mirroring you know you need to sync SQL logins and jobs among replicas. What are your options to sync SQL Logins and maybe even jobs? …
-
Implementing Buffer Pool Extension
Buffer Pool Extension was released in SQL 2014 so it’s not new. It is also not advertised very much, but that’s not for lack of usefulness. RAM is far faster than SSD and certainly arm based disks, but most databases are too big to cache into RAM. BPE allows us to extend the buffer cache…
-
Query Store Questions From The Field
I have had several Query Store questions come in from customers lately. Some of the questions stemmed from things read on the internet so this video is aimed at both answering the questions and debunking some false information you might find out there. We want concrete answers to the following questions, so we’ll demonstrate them…
-
Trace Flag 1117 Growth and Contention
In this video I talk about Trace Flag 1117 and how it was designed to help keep data file growth with multiple data files in TempDB consistent. You’ll see how growth occurs if the files are disproportionate and also the effect it has on TempDB contention with SQL Server special pages (PFS, GAM, SGAM). For…
-
New Challenges
I’m sure everyone remembers the movie The Matrix. You probably also remember the scene where Morpheus asks Neo if he wants to take the red pill or the blue pill. Almost 19 years ago I was Neo and I chose the red pill. For me that pill was Verizon. Back then it was WorldCom (We…
-
Database Mail Breaks with TLS 1.0 Disabled
Discovery I ran across an issue where I had just configured database mail on a new server, went to send a test email, and I waited to receive it. I waited…and waited…and waited, but still nothing. I started to investigate and verified that database mail was indeed enabled, Service Broker was running, and the mail…