SQL Server Static vs Dynamic Data Masking

Data masking is a data security technique in which a dataset is copied but with sensitive data obfuscated. This benign replica is then used instead of the authentic data for testing or training purposes.

Static Data Masking

Every organization has some confidential data and sensitive information stored in production databases. Since there is always an incessant need to migrate this live data to lower environments for several developments and testing purposes, it becomes important to ensure suitable protection has been provided to this critical data while copying production databases to non-production environments.

In order to reproduce production issues on environments like Dev, Staging, Test, UAT, etc., data professionals tend to create test data by simply copying production data to these lower life cycle environments. The development team typically has unrestricted access to all the sensitive information with no encryption or masking on the production database restore on these environments. This easily accessible data put confidential data of the organization at risk.

SQL Server 2019 with SSMS 18.0 (preview 5 and higher) introduces a new security feature called Static Data Masking. Previously it was available for the Azure SQL DB only.

Applying Static Data Masking against a production database and then creating a backup of the database with the mask applied, followed by restoring this masked copy to non-production environments. It is basically a feature that helps users create a masked copy of a SQL database. Once data is statically masked, it is permanently replaced in the cloned database and we can’t change it. This feature is used for several purposes like sharing sensitive data, database development, database troubleshooting, analytics and business reporting.

Read the full article which explains all the steps in details for Static Data Masking: https://www.mssqltips.com/sqlservertip/5939/sql-server-static-data-masking-example/

Dynamic Data Masking

Application developers often required to access production data for troubleshooting purposes and preventing them from accessing sensitive data without affecting their troubleshooting process is vital. We can use Dynamic Data Masking for sensitive fields and hide those details from such users by keeping original data intake. We can allow different users with different roles to see masked fields differently. Amazing isn’t it.

Dynamic Data Masking is a security feature introduced in SQL Server 2016 that limits the access of unauthorized users to sensitive data at the database layer.

Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.

Read the full article which explains all the steps in details for Dynamic Data Masking: https://www.sqlshack.com/dynamic-data-masking-in-sql-server/

Static Data Masking vs. Dynamic Data Masking

Static Data Masking	Dynamic Data Masking
Happens on a copy of the database	Original data not retrievable
Mask occurs at the storage level	All users have access to the same masked data
Happens on the original database	Original data intact
Mask occurs on-the-fly at query time	Mask varies based on user permission

That is All. I hope this will help !!!

SQL Server- Order By With CASE parameter

Let us discuss a scenario, where we want to order the table ascending and descending based on the sort direction and also based on columns passed in the variable. How will you do that?

Answer: using Order By CASE option.

Here is the sample SQL which shows us, how we can do that easily.

DECLARE @SortDirection VARCHAR(10);
DECLARE @SortBy VARCHAR(100);
SET @SortDirection = 'D';
SET @SortBy = 'InvoiceID';
SELECT *
FROM [Invoices]
ORDER BY
    CASE WHEN @SortDirection = 'A' THEN
        CASE
           WHEN @SortBy = 'OrderID' THEN OrderID
           WHEN @SortBy = 'InvoiceID' THEN InvoiceID 
        END
    END ASC
    , CASE WHEN @SortDirection = 'D' THEN
        CASE
           WHEN @SortBy = 'OrderID' THEN OrderID
           WHEN @SortBy = 'InvoiceID' THEN InvoiceID  
        END
    END DESC;

Simple and very useful. Isn’t is?

I hope this will help !!!

Choose between Azure Queue services – Queue Storage, Service Bus Queue, Service Bus Topics

Suppose you are planning the architecture for your music-sharing application. You want to ensure that music files are uploaded to the web api reliably from the mobile app. we then want to deliver the details about new songs directly to the app when an artist adds new music to their collection. This is a perfect use of a message-based system and Azure offers three solutions to this problem:

• Azure Queue Storage
• Azure Service Bus Queue
• Azure Service Bus Topics

Each has a slightly different feature set, which means you can choose one or the other, or use both, depending on the problem you are solving.

Choose Service Bus Topics if

you need multiple receivers to handle each message

Choose Service Bus queues if

You need an At-Most-Once delivery guarantee.
You need a FIFO guarantee.
You need to group messages into transactions.
You want to receive messages without polling the queue.
You need to provide a role-based access model to the queues.
You need to handle messages larger than 64 KB but less than 256 KB.
Your queue size will not grow larger than 80 GB.
You would like to be able to publish and consume batches of messages.

Queue storage isn’t quite as feature-rich, but if you don’t need any of those features, it can be a simpler choice. In addition, it’s the best solution if your app has any of the following requirements.

Choose Queue storage if

You need an audit trail of all messages that pass through the queue.
You expect the queue to exceed 80 GB in size.
You want to track progress for processing a message inside of the queue.

A queue is a simple, temporary storage location for messages sent between the components of a distributed application. Use a queue to organize messages and gracefully handle unpredictable surges in demand.

Summary

Use Storage queues when you want a simple and easy-to-code queue system. For more advanced needs, use Service Bus queues. If you have multiple destinations for a single message, but need queue-like behavior, use topics.

I hope this will help !!!

NOTE — Reference taken from Microsoft Learning Site

Choose whether to use message or event

What are the messages?

• A message contains raw data, produced by one component, that will be consumed by another component.
• A message contains the data itself, not just a reference to that data.
• The sending component expects the message content to be processed in a certain way by the destination component. The integrity of the overall system may depend on both sender and receiver doing a specific job.

For example, suppose a user uploads a new song by using the mobile music-sharing app. The mobile app must send that song to the web API that runs in Azure. The song media file itself must be sent, not just an alert that indicates that a new song has been added. The mobile app expects that the web API will store the new song in the database and make it available to other users. This is an example of a message.

What are Events?

• An event is a lightweight notification that indicates that something happened.
• The event may be sent to multiple receivers, or to none at all.
• Events are often intended to “fan out,” or have a large number of subscribers for each publisher.
• The publisher of the event has no expectation about the action a receiving component takes.
• Some events are discrete units and unrelated to other events.
• Some events are part of a related and ordered series.

For example, suppose the music file upload has been completed, and the new song has been added to the database. In order to inform users of the new file, the web API must inform the web front end and mobile app users of the new file. The users can choose whether to listen to the new song, so the initial notification does not include the music file but only notifies users that the song exists. The sender does not have a specific expectation that the event receivers will do anything particular in the responsiveness of receiving this event.

How to choose messages or events?

A single application is likely to use events for some purposes and messages for others. Before you choose, you must analyze your application’s architecture and all its use cases, to identify all the different purposes where its components have to communicate with each other.

For each communication, consider the following question: Does the sending component expect the communication to be processed in a particular way by the destination component?

If the answer is yes, choose to use a message. If the answer is no, you may be able to use events.

Q1: You have a distributed application with a web service that authenticates users. When a user logs on, the web service notifies all the client applications so they can display that user’s status as “Online”. Is the login notification an example of a message or an event?

A1: The login notification is an event. It contains only a simple piece of status data and there is no expectation by the authentication service for the client applications to react to the notice in any particular way.

Q2: you have a distributed application with a web service that lets users manage their accounts. Users can sign up, edit their profile, and delete their account. When a user deletes their account, your web service notifies your data layer so the user’s data will be removed from the database. Is the delete-account notification an example of a message or an event?

A2: The delete-account notification is a message. The key factor is that the web service has an expectation about how the data layer will process the message. The data layer must remove the user’s data from the database for the system to function correctly. Note that the message itself contains only simple information so this aspect of the communication could be considered an event. However, the fact that the web service requires the data layer to handle the notification in a specific way is sufficient to make this a message.

I hope this will help !!!

NOTE — Reference taken from Microsoft Learning Site

Choosing between Apache Kafka, Amazon Kinesis, Microsoft Event Hubs and Google Pub/Sub

Distributed log technologies such as Apache Kafka, Amazon Kinesis, Microsoft Event Hubs and Google Pub/Sub have matured in the last few years, and have added some great new types of solutions when moving data around for certain use cases.

To help you choose which one to use when, here is a decision flow chart. It is more of a general guide to which technologies to consider, and a few decision points to help you eliminate some technologies.

Ultimately Apache Kafka is the most flexible, and has great performance characteristics. But Apache Kafka clusters are challenging to setup, scale, and manage in production.

If you wish to go for a fully managed solution – Kinesis, Event Hubs and pub/sub offer alternative options depending on whether ordering and blob size are important to you.

On AWS, Amazon Managed Streaming for Apache Kafka (Amazon MSK) service available which is fully managed, highly available, and secure. You can also evaluate this option along with above all if you are using AWS Cloud for your workloads.

Confluent cloud has also re-engineered Apache Kafka for the cloud. They have given more focus on building apps and not managing clusters with a scalable, resilient and secure event streaming platform. It has made event streaming with Kafka simple on AWS, Azure and GCP clouds.

Hopefully this blog post will help you choose the technology that is right for you.