Development

SQL Server – Trace flag changes in Dynamic data masking in SQL Server 2016 CTP 3.0

Hi friends,

In new CTP version SQL server 2016 which is 3.0, I was trying to explore the new masking function Random and following the same steps to apply masking which I used in previous CTP version 2.0.

I found that some changes has been made in Trace flag area and come to conclusion that in earlier CTP versions CTP 2.0 we need to enable the trace flags 209 and 219 while in CTP 2.1+ versions we do not required to enable these trace flags otherwise it will gives error –

Incorrect syntax near ‘masked’.

Let me show you a demo on this –

SQL Server 2016 CTP 2.0

If we try to apply masking on a column then below query without enabling trace flag gives error –

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

T2

So to remove this error we need to enable the Trace flag 209 and 219 and rewrite the query like this –

DBCC TRACEON (209,219,-1)

GO

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

It will get executed successfully.

SQL Server 2016 CTP 3.0

In this CTP version if we want to apply masking on a column then enabling trace flag is not required. If trace flag is enabled then it will give error ‘Incorrect syntax near ‘masked’’.

I have executed a query after enabling trace flags and it gives error –

DBCC TRACEON (209,219,-1)

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

T1

So first disabled the flag and just execute the ALTER statement only.

DBCC TRACEOFF(209,219,-1)

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

It gets executed successfully.

So friends, if you are using latest CTP version of SQL 2016 then we don’t need to enable trace flags 209 and 219.

Hope it will helps you. Have a happy learning.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s