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…

0

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…

0

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

Incremental Statistics – My New Favorite Thing

So many new features get added with each release of SQL it is hard to use them all. Sometimes, I often don’t realize how helpful a new feature is until I see it in action. Incremental Stats was introduced in SQL 2014, but I recently had the opportunity to help a customer implement it and…

0

APS 2016 External Object Changes

APS customers who use Polybase and will be soon upgrading to APS 2016 from AU5 or prior should consider this change and take action prior to upgrading. APS 2016 upgrades the underlying SQL instances to SQL 2016. Along with that comes Polybase that was added to SQL 2016. Polybase is a great feature to see…

0