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 MaskingDynamic Data Masking
Happens on a copy of the databaseOriginal data not retrievable
Mask occurs at the storage levelAll users have access to the same masked data
Happens on the original databaseOriginal data intact
Mask occurs on-the-fly at query timeMask 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 !!!

10 Reasons why Azure SQL is the Best Database for Developers

Azure SQL is the relational and post-relational database that I’d like to say has “batteries included”. I borrowed that term from Python, one of my preferred languages, and that’s also the main reason why it is a great database for developers. A lot of features that you would normally find in different, more specialised, products are nicely integrated into just one. Using different specialised product can bring great advantages but also poses a huge challenge: data must be moved around all those products and the burden of keeping consistency is on developer’s shoulders. And it is a big one, one that will increase application complexity by an order or magnitude. And as a developer I just prefer to follow the KISS principle. Azure SQL allows me to focus only on what it’s unique to my company and application, delegating all the responsibilities related to data to something specialised on that. That’s a big win for everyone.

Read full article – Here

How to find missing Index in Sql Server

The below query will show missing index suggestions for the specified database. It pulls information from the sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_details DMVs. You can also narrow it down to a specified table by uncommenting the AND statement and specifying the table name.


USE YourDatabase
GO
 
SELECT db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
	,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);

This script also generates a CREATE INDEX script for each record, so that you can take a better look at the index and decide if you want to use it.

It’s important to mention that these Missing Index statistics get reset every time SQL Server is restarted, so if you’ve recently rebooted the server you may not have many index stats accumulated yet.

I hope this help !!!

How to Unlock Table in SQL Server?

To find out the tables on which locks are applied, use below step 1 and step 2 to kill that process id to release/unlock the table lock.

Step 1: Get the request session id by executing following SQL statement:

SELECT
    OBJECT_NAME(P.object_id) AS TableName,
    Resource_type,
    request_session_id
FROM
    sys.dm_tran_locks L
JOIN
    sys.partitions P ON L.resource_associated_entity_id = p.hobt_id
WHERE
  OBJECT_NAME(P.object_id) = 'table_name'

Step 2: Kill the request session id which has kept lock on the table ‘table_name‘. Assume its request_session_id is 61. Execute following query:

 Kill 61

We can check or get closer look or find all blocking or locking on database tables by following script:

   SELECT
     blocking_session_id AS BlockingSessionID,
     session_id AS VictimSessionID,

     (SELECT [text] FROM sys.sysprocesses
      CROSS APPLY sys.dm_exec_sql_text([sql_handle])
      WHERE spid = blocking_session_id) AS BlockingQuery,

     [text] AS VictimQuery,
     wait_time/1000 AS WaitDurationSecond,
     wait_type AS WaitType,
     percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

Hope this will help !!!