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…

0

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…


SSISDB Reporting with Power BI

Back in 2012 when the project deployment model came out for SSIS, many questions were asked regarding its advantages vs the old legacy deployment model. I still see quite a few customers today who use the old legacy package deployment model. The upgrade/conversion process from package deployment to project is quite straight-forward, and I encourage…


Quick Tip – Shift+Alt for multiple line edits

We’ve all been in the position where we are doing research and need to add commas or single quotes around a list and it’s a pain to go through one line at a time to add them.   While working with Scott Epperly, Support Engineer with Microsoft, I saw him quickly add a comma before about 10…

1

SSIS: Capturing PerfMon Counters During Package Execution

While troubleshooting SSIS package performance, having the ability to view the performance monitor counters can be handy for determining where bottlenecks may be occurring. Usually, however, this is a very reactive request. The package is slow, so perfmon counters are enabled. The package is then run and *hopefully* the issue reoccurs. A lesser known feature…


Split a file group into multiple data files

Periodically we are asked how to split an existing filegroup into multiple data files. The quick answer is that SQL Server does not have a built-in way for you to do that automatically, but you can do it yourself. The process is relatively simple and I have provided a script that demonstrates one technique. The…

4

SQL Diagnostics Project Part 2 – Custom SQL Nexus Imports

Several years ago, I wrote a blog post as to how you can collect your own custom SQL data from a SQL: Server instance (usually one from a customer) using the PSSDiag utility from Codeplex. At the end of the blog post, I mention that I was going to get around to each showing you…

0

Trouble shooting Availability Group Listener in Azure SQL VM

Last week, I have had one mystery challenge while creating Availability Group listener in Azure. We followed Configure one or more Always On Availability Group Listeners – Resource Manager and found out the listener didn’t work as expect. Let me walk you through what we have experienced.   In this blogpost, for demonstration purposes, we…