SQL 2014 Security Updates

It has been a busy time. I've moved house, moved country and moved jobs. Things have happened fast but the dust is starting to settle and I'll soon get into a swing of posting more often. A few things of note for February 2020, other than the Covid 19 virus, were: SQL Security update. There was a SQL Security Update in February for SQL 2014. [...] Read more

Integration Task to gather API data using PowerShell

At the time of writing there isn't a SQL Server Integration Services (SSIS) task that will collect JSON or XML formatted data directly from an API call. There are various add-ons and products that are available however, it is straightforward to achieve this using PowerShell and since you are likely to use PowerShell to automate other tasks on [...] Read more

Removing User-Created Statistics

In the past I have inherited databases that have a large number of user defined statistics the table. The statistics name structure suggests that they were added by the performance analyser from a much earlier version of SQL Server. The existence of user-created statistics raises a warning in the excellent sp_Blitz SQL Health check scripts from [...] Read more

Automation in Azure. Example script to avoid weekends.

Since writing this entry, new features in Azure from June 2019 can provide similar functionality - see the following post for more details. Using virtual machines (VMs) on Microsoft’s Azure platform are charged by the hour. Non-critical, test or development servers can be switched off when not in use, saving you money, especially if you are using [...] Read more

Using Powershell in SSIS.

Powershell is a great tool for managing SQL Server, Azure and all things Microsoft (okay - most things Microsoft, I still haven't been able to automate many Reporting Services tasks). If you are a DBA managing SQL Server then I strongly recommend that you look at dbatools.io which will supercharge your Powershell toolset. Powershell can allow you [...] Read more

SQL Server alert to monitor disk space.

In SQL Server you can set up alerts that access the Windows Management Instrumentation (WMI) directly. I was looking at an unrelated Powershell task when I stumbled across an example of using the WMI query WQL on Microsoft's Dev Centre pages that shows the use of __InstanceModificationEvent system class on the Win32_LogicalDisk object. FreeSpace [...] Read more

Powershell and SQL Server Agent anomaly.

Powershell is a great tool for DBAs based on Microsoft platforms and it is getting even more useful now that it can be used to directly manage cloud services in Azure. Today I encountered a very strange situation when using a Powershell job step with SQL Server Agent on SQL2012. Below is a very simple Powershell script that I use to manually [...] Read more