In February 2017, Microsoft announced the general availability of Blob Auditing for Azure SQL Database. While auditing features were available before in Azure, this is a huge leap forward, especially in having more granular control over what audit records are captured. Before Blob Auditing, there was Table Auditing. This is something I like to equate to the C2 auditing feature of SQL Server. It’s only configurable options were ON or OFF. In reality, Table Auditing has a few more controls than that, but you get the idea. There was no way to audit actions against one specific table. Blob Auditing...
Continue reading...PowerShell
AzureRM Module Version
When working with the AzureRM PowerShell module, remember the module is constantly being updated to take advantage of new features added to Azure. To check the version of the AzureRM module currently installed on your system, use the following command. The screenshot below shows I am running version 1.4.0 and the output of Get-AzureRmSqlDatabaseAuditingPolicy command-let. Next, I install the latest AzureRM module. After the install is complete, I rerun the same commands. The screenshot below shows that I am now running version 3.8.0 followed by the same Get-AzureRmSqlDatabaseAuditingPolicy command-let. Notice the difference in the output? There are several differences, but in...
Continue reading...SQLPSX is Finally Getting Updated
The most current code is now on Github, with the Codeplex version being depreciated. You can read all about the planned updates from Mike Shepard. The Future of SQLPSX https://powershellstation.com/2016/07/13/the-future-of-sqlpsx/ SQLPSX Update https://powershellstation.com/2016/07/31/sqlpsx-update/
Continue reading...The Case of the NULL Query_Plan
As a DBA, we’re often asked to troubleshoot performance issues for stored procedures. One of the most common tools at our disposal is the query execution plan cached in memory by SQL Server. Once we have the query plan, we can dissect what SQL Server is doing and hopefully find some places for improvement. Grabbing the actual XML query plan for a stored procedure from the cache is fairly easy using the following query. From this point, we can open the XML query plan in Management Studio or Plan Explorer to start our investigation. But what happens if SQL Server...
Continue reading...Use Powershell to Pick Up what Database Mirroring Leaves Behind
Database mirroring has been around since SQL Server 2005, and it’s turned out to be an excellent step up from log shipping. However, like log shipping, it is still only a database-level disaster recovery solution. Meaning that any logins, server role memberships or server-level permissions will not be mirrored over to the mirror server. This is where the DBA needs to plan ahead and create their own custom jobs to script and/or document these types of shortcomings. My solution is to use Powershell. In this example, I have setup database mirroring for the AdventureWorks2012 database. For this demo, both instances, TEST1 and TEST2, are on...
Continue reading...
