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

CREATE INDEX Monitoring Progress

One of the challenges for DBA’s are to work on heavy load and critical systems where maintenance windows are usually very short, recently I was asked for a customer if there was any possibility to track or estimate the progress of a CREATE INDEX statement and my response was sys.dm_exec_query_profiles. Starting with SQL Server 2014…


SQL 2016 – Temporal Tables – Can you change the schema?

I was recently asked if after you setup a Temporal Table, can you modify the schema?  It was a great question because if you cannot change the schema, then you would have to drop and recreate the table every time you need to change the schema which means you would potentially lose all the history. …

2