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

SSIS and your SFTP options

The SQL Server intergration Services (SSIS) has always had a FTP task but it is odd that it has never had an SFTP task (up to 2017). SFTP is the Secure File Transfer Protocol using the Secure Shell SSH. There are third party SFTP tasks that can be deployed to your Server running SSIS but the simplest way to use SFTP is through an execute process [...] Read more

SSAS: Multidimensional to Tabular in minutes

On a development box I have SQL Server Analysis Services (SSAS) installed and had tested a multidimensional model. Now I need to test another project which is a tabular model. As you probably know, you can only have one Analysis Service running on each database instance and it has to be either Multidimensional mode or Tabular mode or SharePoint [...] Read more

Depricated feature: Database Mirroring

You may come across legacy SQL Server boxes from 2005 onwards that are configured using database mirroring to provide a High Availability (HA) database for an application or website (BTW - this was written in 2019 and I still see SQL 2000 out in the wild!!). I would strongly recommend moving the Distaster Recovery (DR) strategy to use Availability [...] 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

SQL Server and Docker Containers.

It has been possible to run the Linux version of SQL Server in a Docker container for a couple of years now. This is a nice way to have a clean and simple development database on your laptop that can be easily turned on and off when required. The SQL 2017 Linux edition had many unsupported features but is still a great way to have a local SQL [...] 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

Duplicate backup files.

It goes without saying that your database backups are important. The storage and restoring of backups must be carefully described in your disaster recovery (DR) planning and tested regularly. This isn't a post about DR planning. There may be occasions when you need to have a backup file distributed to two locations for safety - a common-sense [...] Read more

Using Azure VM SSD for SQL Server TempDB

I'm a big fan of virtualization, I can't remember the last time I was plugging network cables into the back of a "real" piece of hardware. I currently run a number of SQL Server boxes on Azure VMs. There is a temporary storage solid state D drive available on Azure VMs which is used to store temporary data. Microsoft give a detailed explanation of [...] Read more

Sending SQL Server email notifications without local mail services.

Many organisations are embracing the move to cloud based solutions. One common scenario is a move from on-premises servers to cloud based VMs running as servers (as opposed to a managed service Azure SQL database). You may have also moved all your office email into the cloud using Office 365, the bonus is you don't need to worry about running a [...] Read more

SQL Server Data Classification and GDPR

In February 2018 a new release of the SQL Server Management Studio (SSMS) 17.5 was released with a fanfare about the new Data Discovery and Classification functionality. This is an area of increased interest with most organisations currently undertaking detailed information audits in preparation of the changes to the data protection laws with GDPR [...] Read more

Old school cross tabs and PIVOT.

In all data integration jobs you will come across a problematic data source. It's either a SCADA system, old Payroll software or a selection of log files - it is all part of the fun of getting useful information out of digital data. For over 10 years now I've specialised in the Microsoft SQL Server product and I've become very used to T-SQL. This [...] Read more

Manage long running jobs in SQL Server Agent

It has been a very busy few months at Ventient Energy, some news coverage is here, so I've not had the time to post as often as I would like. However, I have some great Power BI and SSIS posts lined up so stay tuned. In this quick post we want to monitor SQL Server Agent jobs and take action if a job takes longer than some known duration. In my [...] Read more

Zip and unzip tasks in SSIS

I've been doing a lot of work with SQL Server Integration Services (SSIS) downloading zip files and unzipping the data. There isn't a "native" zip task in SSIS at the time of writing but I've always used the Execute Query task with 7Zip since back in my SQL 2005 days (7zip has been around since 1999!). I'll go through that in more detail later in [...] 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

Numerical data and histograms.

In this post I am focusing on presenting continuous or discrete numerical data using a histogram. Visualising your underlying data using a histogram is probably the easiest way to understand the behaviour of a given data value. Histograms are very basic and aren't very impressive in today's world of dazzling infographics, but generating histograms [...] Read more

Using XML weather observations in SQL Server.

The UK Met Office not only have an excellent web site they also have the fantastic DataPoint project providing free access to their data feeds via API calls. The data are returned as JSON or XML and can be used freely in accordance with the Open Government Licence. I wanted to compare readings gathered by our own weather detectors and stored in a [...] Read more

SQL Server 2005 end of support.

It is hard to believe that this version of SQL is coming to an end! This was the first Microsoft SQL database that I worked on coming from Oracle 9i. I have worked on many SQL Server projects since then and have come to both love and hate the Integration and Reporting Services. I'm currently working on projects that use the following Microsoft [...] Read more