Tag Archives: SQL Server

Is your SQL Server environment ready for GDPR? Part 2

In my previous blog post of this topic, I talked about the definition of what GDPR is and also described the first two phases of Microsoft’s recommended workflow in order to be in compliance with this data regulation.

The Discovery and Manage phase was about discovering where the sensitive data was located and how it can be accessed also to create access controls to the system in compliance of the “least privilege” principle enabling only authorized access to the database system and data. Here is the visual representation of Microsoft’s GDPR recommended workflow:

Let’s move forward defining and describing the the last two steps:

Protect:

Here is where the data protection effort begins, such task requires proper security controls that are appropriate for the specific data types and usage scenarios.

In my opinion this is the most important phase in the workflow, because is where the data becomes the main focus. The application of protection solutions and monitoring mechanisms are crucial to protect the data.

SQL Server tools you can use during the protect phase:

    • Encryption, which can be applied at different levels (database, server, client)
      • Azure SQL database and Azure SQL Database Warehouse requires encryption for all connections
    • TLS, Microsoft currently supports the 1.2 version
    • TDE, this solution encrypts data at rest is often required by compliance regulations and various industry guidelines as a core requirement, as it ensures the full protection of data at the physical layer.
    • Always encrypted, allows to encrypt sensitive data inside client applications never revealing the encryption keys to the database layer. This is a very good solution when encryption at rest and on the wire is not sufficient.
    • Auditing for Azure SQL Database, tracks database activities by writing events to an audit log. It does require a storage account to save the logs for further analysis.
    • SQL Database threat detection, this is a built-in feature of Azure SQL Database which detects anomalous database activities indicating potential security threats to the database.
    • SQL Vulnerability assessment, part of SSMS starting from the 17.4 version. I have a dedicated blog post about this tool, it scans a SQL instance and its databases looking for data privacy standards.
    • SQL Server audit, tracks database activities and analyze and investigate historical activity to identify potential threats or suspected abuse and security violations.

Report:
This is the last phase of this workflow, deals with the continuous process of reviewing the controls and security of the system, to better ensure ongoing compliance with GDPR principles.

The basis for reporting relies on maintaining documentation and records, so we have a couple of options available to fulfill this requirement:

    • SQL Server audit, maintaining audit logs for all database activities ensures the existence of a persistent record of database access and processing activities at all times. These records can then be analyzed and packaged to provide evidence needed for various record-keeping requirements
    • Temporal tables, a new built-in feature starting from SQL Server 2016. System-versioned temporal tables designed to keep a full history of data changes and allow easy point in time analysis.

Once all the phases are complete, do not forget that the only way to achieving and maintaining compliance to GDPR is through regular checks of the security state of data and systems, to ensure that they meet the standards expected by the organization

Thanks for reading!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

I will be speaking at SQL Saturday # 788 – Denver

I’m very thrilled to announce that I will be participating as speaker in Denver’s SQL Saturday that will take place on September 15th. This is a huge step for me as technical speaker, because it was just June when I participated in Denver’s SQL Server User Group speaking about Query Store and now I had the chance to participate on Denver’s SQL Server annual great event.

And it is a big deal because there will be very recognized people around like Bob Ward who is the keynote speaker of this year, there will be participation from Glenn Berry, Kellyn Pot’Vin-Gorman, Tracy Boggiano, Jess Borland, Jason Brimhall and more!

Here is a picture of me with Steve Jones and Glenn Berry from Denver’s 2017 SQL Saturday:

Steve Jones & Glenn Berry – Denver SQL Server Saturday 2017

At that time I have no idea I will make the list of speakers for the year 2018, which I’m very grateful and proud.

I will be speaking about how to perform a SQL Server Health Check, this session is about the importance of conducting regular health checks on SQL Server instances to ensure that they are currently healthy, and continue to remain healthy.

This event will take place at University of Denver – Sturm Hall, 2040 S Race St, Denver, Colorado, 80210, United States.

Please remember as any SQL Saturday event you can register and attend for free, it just a matter to sign up here.

Looking forward to see you there!!!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

MAX DOP calculator

Every time I conduct a SQL Server health check, it’s pretty common for me to find wrong configurations at instance level. But one of the most common mistakes I see on the wild, is SQL instances having the “max degree of parallelism” (MAXDOP) set to “1” (single core) , probably this behavior is related to a misconception of how parallelism works or because some DBAs thinks this parameter reduces the “CXPACKET” wait types, who knows …

In order configure MAXDOP properly, DBA’s should read and understand what Microsoft’s has as best practice guideline in the KB# 2806535. This article has a table with the explanation on how to calculate the right value for this configuration:

If you are not familiar with NUMA nodes, logical CPUs or CPUs at all this can be a little bit confusing …

NUMA nodes, CPU specs, logical CPU’s, wait …. WHAT???

I understand, we live in a very busy world where we need to do things quick and make everyone’s work easier. Just imagine having to make this calculation every time you want to change the MAXDOP configuration, you truly need something to make your life easier… enter MAXDOP calculator.

The concept of a MAXDOP calculator was created by a Microsoft field engineer long time ago, the article helps you to set the right value for the MAXDOP configuration, the problem I noticed with this tool is that requires a lot of manual intervention. Anytime you would need some help calculating the correct MAXDOP value you will have to visit the website and provide the input from a couple of queries, there is also an Excel version but I do see the same problem. Don’t get me wrong I think this is a wonderful idea but I do personally don’t like manual stuff.

Based on this need, I have created the same calculator but this time in T-SQL. The same concept but in runs in a SQL environment directly, returning the correct MAXDOP value without using any input as the original approach.

Here is an example of the output:

MAXDOP recommendation at Instance level for also T-SQL statement suggestion to re-configure value

At you can see, it provides you the current and recommended MAXDOP configuration also including the syntax to change it in case it is wrong. For SQL Server 2016 or major, it also returns the MAXDOP value configured at database level.

MAXDOP recommendation at DB and Instance level for SQL Server 2016 (and major) also T-SQL statement suggestion to re-configure MAXDOP

If you want to give try it, you can download the T-SQL script from my GitHub repository.

The requirement to execute this T-SQL script is to have SQLCMD mode enabled in SSMS, you can take a look at my previous post to learn how to enable it.

Please feel free to provide me feedback about this tool, stay tuned for more DBA mastery tips!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.