Enabling Advanced Data Security (ADS) on Azure RRS feed

  • Question

  • Can somebody let me know if ADS is turned on, would it have any negative effect on Azure SQL database performance?
    Tuesday, June 11, 2019 6:30 PM

All replies

  • Can somebody let me know if ADS is turned on, would it have any negative effect on Azure SQL database performance?

    Goo day Shiva,

    >> Advanced data security is a set of tools and not one feature. When you enable SQL ADS then you enable all of these included features like Data Discovery & Classification, Vulnerability Assessment and Advanced Threat Protection and you can enable the Auditing as well.

    >> In theory any use of the SQL Server must use some resources and (again this is theoretical discussion only) can impact performance (even a simple select can result in locks and waits in other queries).

    Let's discuss the tools separately for example

    >> Classification tool:
    The classification engine scans the database in order to identify columns containing potentially sensitive data. Obviously this mean that something is executed on the server, but this probably negligible since it does not need to scan the data but only the metadata.
    The classification tool also stores the the properties in the database level which basically like we do in on-premises using sys.extended_properties and to get the information it need to use a JOIN on the properties...
    But this is a one-time execution and not a monitoring that scans every second...

    >> vulnerability assessment (***): This tool executes several tens of queries according to different rules which it checks. Officially the documentation claims that "The scan is lightweight".
    Again, this is not a tool that executed every second. You can configure this tool to Periodic recurring scans once every 7 days.

    >> Advanced Threat Protection (***): detects anomalous activities indicating unusual and potentially harmful attempts to access or exploit databases. It is done not in the database level but in the Azure level which mean it will not impact performance. For example you can monitor the client's information like IP before the connection to the SQL Server was established, and detect anomalous activities. If you already have Auditing then the information collected can be used for this tool as well. The massive parsing/processing of the information is done outside the scope of the SQL Server.

    >> Auditing (***): Auditing tracks database events and writes them to an audit log in your Azure Storage, Log Analytics, or Event Hub. This tool is based on Extended Events which obviously does cost something. Same as in on-premises database, I would not recommend to use this feature in all cases but only when needed.

    Note! You can enable Auditing without the Advanced Data Security. Using ADS allows to receive security alerts to email upon suspicious events.

    Note! Using Azure SQL Database you actually use a service. The physical resources which are available are not the same as the resources which are given to you for your service. Therefore this is more complex to answer directly Yes/No and how much resources will be used out of your service and how it will impact performance directly.

    --------------- more ---------

    Since the team that develop these features is located not far from me (only few KM from my home), let me contact directly someone from their team and check if they want to add more insights on the topic or maybe fix something which I said😃

    In fact, if you speak Hebrew then I have perfect video which you must check:
    Global Hebrew Virtual Group meeting recording: Advanced data security for your SQL estate by Ronit Reger (Microsoft PM)
    This exact question was asked during the meeting and was answered by Ronit at this time second 2059. You can go directly to the answer in the recording (again this is in Hebrew):
    By the way, you can download the presentation file from here:

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, June 12, 2019 5:43 AM
  • God day Ronin,

    You are awesome! Great information. Thank you a lot!

    Wednesday, June 12, 2019 7:55 AM
  • Hi,

    Thanks Ronen for the detailed answer!

    Indeed, as Ronen explained, Advanced Data Security has little if any impact on SQL DB performance. The queries run on the database by Vulnerability Assessment and Data Classification access metadata only, and are very lightweight. Advanced Threat Protection (ATP) processing is indeed done in a separate service, and does not consume SQL engine cycles.

    SQL Auditing, which complements ADS as an additional security feature, is implemented using the same engine auditing mechanism used in SQL Server. This is not built using extended events. It can have some performance impact depending on the workload and granularity of audited events. We would highly recommend testing this scenario on your particular workload to accurately assess the impact. Again, the impact is not expected to be very significant, but always best to test in your environment.


    --Ronit (MS)

    Wednesday, June 12, 2019 12:06 PM
  • Hi Ronit 😃

    Thanks for coming and sharing your insights. It is best to get the information directly from the team that develop the product

    Hi Shiva,

    Thanks for the nice words. I am happy to see that you liked the answer👀.
    Now you can even say that you spoke directly with the team that developing these features😃

    Please remember to close the thread by marking the answers which you got

    Have a great day

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, June 12, 2019 12:40 PM
  • Hey,

    You can enable ADS by navigating to Advanced Data Security under the Security heading for your SQL Database server or manged instance. To enable ADS for all databases on the database server or managed instance, click Enable Advanced Data Security on the server.

    You can check if ADS is enabled or not just refer to the below image :



    Azure Administrator | Apps4Rent

    Wednesday, June 12, 2019 8:48 PM
  • Good day Akshay,

    1. Seems like you did not you read the full question but just the title.

    The question was not related to how to implement ADS but regarding the impact of ADS om performance!

    2. Did you read the responses before you posted yours?

    * I HIGHLY recommend to read the full question, and the responses which the OP got (and on the way, check who provided the the response)

    Putting the technical point aside... Please do not propose your own response as answer!

    By posting an answer we already know that you think that this is THE ANSWER, else why did you post this?!? as well any other person who post an answer! he is sure that he post THE ANSWER. The Idea of proposing an answer is to let someone else say that he also think that this is THE ANSWER. If each person that think his response is the answer will propose his own answer then all responses will be marked.

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, June 12, 2019 9:26 PM