Generating ALTER TABLE Script for MASKING COLUMNS from existing database

If we are having a database with masking columns(Dynamic Data Masking) and not sure about how to get all the masking columns information as a ALTER TABLE script,  we can use below script for the same.

SELECT 'ALTER TABLE ' + SCHEMA_NAME(t.schema_id) + '.' + object_name(c.object_id) + ' ALTER COLUMN ' + c.name + ' ' + UPPER(tp.name) +

                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')

                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'

                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')

                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'

                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')

                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'

                     WHEN tp.name = 'decimal'

                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'

                    ELSE ''

                END + ' MASKED WITH (FUNCTION='''+ c.masking_function COLLATE SQL_Latin1_General_CP1_CI_AS + ''')'+ CASE c.is_nullable WHEN 1 THEN ' NULL;' ELSE ' NOT NULL;' END

FROM sys.masked_columns as c

JOIN sys.tables AS t

ON c.object_id = t.object_id

JOIN sys.types as tp

on c.user_type_id = tp.user_type_id

The advantage of longRetry parameter in Azure Data Factory

We are using Azure Data Factory to load data from Azure storage blobs to SQL Server on-premises.
During data loading, we faced SQL Error: 10054 , which was due to "Connection Forcibly Closed by Remote Server".

On further analysis, it was due to some server patch activity happening on our SQL Server environment. So, this error is bound to happen once in a while, when server goes down.

The solution for solving this problem is enabling retry mechanism. But, even retry immediately will not solve the issue. So, we have to use longRetry option in the ADF activity policy.

Originally, the activity policy was set as:

       

"policy": {

          "concurrency": 1,

          "executionPriorityOrder": "OldestFirst",

          "style": "StartOfInterval",

          "retry": 1,      

          "timeout": "23.23:23:23"

        },


We changed the activity policy to:

      

"policy": {

          "concurrency": 1,

          "executionPriorityOrder": "OldestFirst",

          "style": "StartOfInterval",

          "retry": 1,

          "longRetry": 3,

          "longRetryInterval": "00:20:00",

          "timeout": "23.23:23:23"

        },


The number of times slice will be attempted is: Retry times x No. of Long Retry times
So, in the above case, it will be 3 x 3 = 9 times, the activity slice will try to run.

We are keeping the longRetryInterval as 20 minutes, hoping that the server patch activity will get completed within 20 minutes and retry will be successful.

We can read more about it: https://github.com/twright-msft/azure-content/blob/master/articles/data-factory/data-factory-create-pipelines.md