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…


SSIS: Setting the same variable in multiple projects in the catalog

Recently I was assisting a customer with a review of their SSIS implementation and one the security implementations that they had in place was service accounts for SQL Server Integration Services packages. While using SQL Authentication to establish the connections to SQL Server, one of the challenges on the team was that the password for…


Azure RM: Configure a second Availability Group with a Listener using the External Load Balancer

In a previous post, we have reviewed Azure RM: How to create SQL Server AlwaysOn Availability Groups Listener configuration with Azure External Load Balancer, this is the second part of the delivery, have fun. In Azure Resource Manager a Load balancer can include one or more front end Public IP addresses, otherwise known as a…


APS Blocked Partition Switch

  In SQL Server, when you perform a partition switch, a schema lock is acquired briefly to do the operation. This can get blocked by read operations that have a schema-s lock on the table. APS works a little differently as a lot of the locking is controlled within the PDW code before the request…

0

The EXECUTE permission was denied on xp_msver

Recently after installing SQL Server and configuring SSIS for project deployment in a SQL Server 2012 environment, a customer was experiencing issues when attempting to execute a package deployed to the Integration Services catalog or through SQL Agent calling the package. Developers’ accounts were working correctly, however this issue would occur when attempting to execute…


SQL 2016 – Temporal Tables – How do you drop a Temporal Table?

As we continue our journey with Temporal Tables, the next topic will be how do you drop them.  While this may seem like an odd thing to write about, dropping a Temporal Table does have a couple extra steps.   Let’s begin by creating a temporal table:     Let’s compare the menu options when we…

0