SQL Server 2017 Read-Scale Availability Groups

SQL Server 2017 was recently launched, having multiple new features. One of these included something called “Read-Scale Availability Groups”. There was a good bit of discussion about this feature and one of the feedback items was that the details in Docs was very light. I hope to rectify the minimal amount of information through this…

2

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…

0

Microsoft Excel, TLS & SQL Server – Important Considerations

Background As you may know, TLS 1.0 is being deprecated due to various known exploits and will no longer be PCI compliant as of June 30th, 2018 (see PCI DSS v3.1 and SSL: What you should do NOW below).   You may also know that Microsoft has provided TLS 1.1/1.2 patches for the SQL Server…


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? …

3

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…

0

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…

0

SQL Server Workgroup Cluster FCM Errors

Background One of the new features of SQL Server 2016 is the ability to use SQL Server with Failover Cluster in a workgroup rather than joined to Active Directory. When working with SQL Server and Failover Clustering in a workgroup, many of the abilities that are normally used with Active Directory are no longer available,…

0

SQL 2016 – Temporal Tables – Triggers and Historical Data Tidbits

Hello again and welcome back to the series on Temporal Tables! Today we will take a look at two common questions.  What happens when I put a trigger on a Temporal Table and can I back populate the historical table?   Setup First thing’s first, let’s setup a demo.   Triggers Now, let’s add a…

1

Troubleshooting CDC enabling failure – Part 2

  In previous blog, I discussed the CDC failure due to guest user disabled in MSDB. My customer also experienced another issue at the same time:   Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 622 Could not update the metadata that indicates table [dbo].[Table_Name] is enabled for Change Data Capture. The failure occurred…


Troubleshooting CDC enabling failure – Part 1

My customer had recently experienced 2 interesting CDC issues and I would like to share the troubleshooting process and root cause for these issues today.   The issue: During the weekend, my customer tried to promote some changes to production server and while they tried to enable CDC on production server, they first encountered a…