DBA

Background process can block restore of database

We can use database snapshots for the development of new databases or fine tunning complex upgrade scripts. All active connections must be closed before the database can be restored from a database snapshot. Closing of active connections manually via Activity Monitor isn’t an option that can be easily automated so…

Read more
T-SQL

Maximum nesting level of CASE statement

Did you know that the CASE statement has a maximum nesting level of 10? The same limit exists for IIF() function which is just a syntactical sugar and is internally transformed into multiple CASE statements. I have prepared two samples you can play with it. Let’s start with the query…
Azure

Dynamic Data Masking in Azure SQL

Dynamic Data Masking is available for Azure SQL Database as one of the main security features. There is more on Data Masking on Microsoft Docs. I don’t want to repeat all these technical details, but rather provide you with a simple tutorial on how to set up Dynamic Data Masking…
Azure

Configure Azure SQL Transparent Data Encryption With Your Own Key

Transparent Data Encryption is by default configured for Azure SQL Database to use service managed key. This means that Azure is managing encryption keys for you with 90 days rotation. If you have some regulatory or data privacy requirement you can change the default setting and encrypt Azure SQL Database…

Read more
SQL Server

Migration of Master Data Services model and data between instances

A migration of models between two Master Data Services instances is an easy step until we will discover that the UI available option works only for model definitions (metadata) and we must move data in a separate step. But don’t scratch your head. It’s just one or two command lines…
T-SQL

APPROX_COUNT_DISTINCT()

APPROX_COUNT_DISTINCT() is nice new function announced currently to be in public preview for Azure. Being well known for Oracle users is now joining (like many other things:)) also the Microsoft world. This function is designed to provide aggregations across large data sets where responsiveness is more critical than absolute precision….

Read more
T-SQL

Removing decimal places from a number without rounding

ROUND() is a well-known function in SQL Server. Most of us know only that two parameters can be used like ROUND(99,95, 2). This will do standard mathematical up/down rounding of numbers in the first parameter for the number of decimal places from the second parameter. But there is also an…
Azure

Backup and restore database in Azure Storage Account

Creating backups of user databases from an on-premise environment to Azure Blob Storage is an easy task. You need to configure Storage Account in Azure where your database backups will be sent. Then you can reference this newly created storage container directly from SQL Server Management Studio when creating a…

Read more