Boolean Hell – Missed rows when grouping in SQL

I was recently asked about the concept of Boolean hell and thought it would be a good post to discuss. Most database professionals should be familiar with the concept even if they don’t know the exact name of it. I think the best way to explain this will be with an example so let’s have…

No process is on the other end of the pipe

Recently I got the below error when trying to connect to SQL Server. There error was caused in this case because the max memory setting was set to 1024 MB (on purpose). The error message mentioned that memory could be an issue as shown below in bold. The client was unable to establish a connection…

Adding a subscribe button to your WordPress site

Recently in a move to improve the subscription rate and overall search ranking of my site I decided to add a subscribe button to all my posts. The button would point the reader to my YouTube channel. This achieves 2 goals Improves the number of links to my channel as well as blog Makes it…

Memory for Servers – Hardware

Most database administrators are at least to some extent familiar with what memory does for SQL server. This is especially true for scenarios such as configuring minimum memory in maximum memory, checkpoint, lazy writer etc. However, there is a shocking lack of understanding how the hardware itself plays a key role in the memory utilisation of the server. Ask any database administrator…

Managing inventory in SQL Server –Part 1

A fairly important aspect of retail software is inventory management. Often this requires some kind of counter that reduces the count of stock items based on the number of orders placed. This poses a particularly difficult challenge in relational databases because we need to ensure consistency. Think of a product table which has a stock…

Securing SQL Server: – Data Classification and Vulnerability Assessment

  A big challenge with implementing security is just being able to keep track of the different tables and columns where the data is located. This features in SQL 2019 or SSMS specifically makes the task much easier. We cover how to automatically and manually classify sensitive information, we then explore how to audit SQL…

Securing Azure AD using the free stuff

One of the first things I did in 2021 was to log into Azure portal and check my Active Directory. You can imagine how much of a shock it would have been to me to see the screenshot below. Apparently I have been a target for a number of attacks from Pakistan, Indonesia and Vietnam….

Masking Data prior to SQL 2016

I was recently asked by a friend of mine what would be the best way to mask data in SQL Server. Now you may be aware that dynamic data masking was introduced in Microsoft SQL server version 2016. However for older versions of SQL server there are primarily just a few variations of same approach…

How to analyze a Presidential debate?

During the last US presidential election we extracted some important critical information from the presidential debate to help profile the candidates. This year we are performing the same kind of activity using a different approach which will be detailed below. The intention of this post is not to support or negate any candidate. It is…

Securing SQL Server: – Transparent Data Encryption -TDE

https://youtu.be/iDfH7dKEFto In this video we cover TDE or transparent data encryption. It is a feature that allows the DBA to protect files in the disk such as MDF, LDF and NDF. It doesn’t prevent the DBA from viewing the data. But it does protect the database from being restored on an unauthorized server where other…

Azure Data Sync Issues

Azure data sync is a great tool for those people looking for a quick and easy way to move data from on premise systems to the cloud. It works great when you have a fairly simple table structure and no transformations before the data is ready to load to the destination. Recently we came across…

Securing SQL Server – Principal of least Privileges

In this second video of the series we cover “Principle of least privileges”. It means we grant only the minimum required permission to any account. We explore how to grant these permissions and also how once granted, bad coding practices and carelessness can undo all the hard work in one swoop. This is one of…

Securing SQL Server – Dynamic Data Masking

In this video we cover the feature Dynamic Data Masking. While technically a security feature DDM can be easily bypassed and therefore is often used in conjunction with other features like encryption. It adds a layer of masking on the final output and as such can be bypassed easily by rewriting the query as demonstrated…

Microsoft SQL Server Security: – Defense in Depth Series

In this series we cover all the different ways you can protect your Microsoft SQL Server database from attacks. We cover the methodology being used to secure databases. When and how to use them and certain pitfalls. https://www.youtube.com/watch?v=aITXHwWHXJw&feature=youtu.be Defense in DepthThe series covers the following concepts Principle of least privilegesDynamic Data MaskingRow Level SecurityAlways EncryptedTransparent…