Hey guys!!
The end of the year is approaching, everyone is getting ready for New Year's Eve and that's why I would like to share with you the “TOP 10 technical articles of 2019 that you liked the most”, that is, the articles that I published in 2019 and that you viewed the most. I hope you enjoyed this short list and that some article might be useful to you if you haven't seen it before.

Without much ado, let's get to the list.

#10 – SQL Server – How to avoid brute force attacks on your database

In this article, I demonstrated how brute force attacks on SQL Server occur and how to try to defend against this type of attack.

Brute force attack is the simplest and most time-consuming technique to break into systems and databases. It consists of using password databases to test each of these passwords or carrying out a systematic check of all possible keys and passwords until one of them can successfully log in to the destination.

This type of attack can be used when it is not possible to take advantage of other weaknesses in a cryptography system (if any) that would make the task easier, as the time needed to test all possible passwords can go from a few seconds (3 characters) to thousands of years, depending on the number of characters in the password and the complexity of the characters used.

#9 – SQL Server – How to identify and collect information from long-running queries using Extended Events (XE)

In this article I shared with you how to identify and collect information from long-running queries using Extended Events (XE), in an article very similar to the SQL Server – How to identify and collect information from long-running queries using Trace (SQL Server Profiler), which uses Profiler (Trace) technology.

What motivated me to write this article was that Profiler is a feature that has been marked as deprecated for a long time, it is a much older technology and the code is not friendly or readable at all. So, thinking about bringing a more modern and intuitive solution to you, I decided to share this solution using XE.

#8 – SQL Server – Performance Tuning Tips: Implicit conversion? NEVER AGAIN!

In this article I would like to comment on a performance problem in queries that we encounter a lot here in our daily lives at Fabrício Lima – BD Solutions, one of the best and most recognized Performance Tuning companies in Brazil. We are talking about something that is often terribly simple to solve and inexplicably and extremely common, implicit conversion.

Implicit conversion occurs when SQL Server needs to convert the value of one or more columns or variables to another data type in order to enable comparison, concatenation or other operations with other columns or variables, since SQL Server cannot compare a varchar column with another int type, for example, if it did not convert one of the columns so that they both have the same data type.

#7 – SQL Server – When should you use ORDER BY in the query and when should you not use it at all!

In this article, I shared with you when you should use ORDER BY and when you shouldn't use it at all, because it has no effect in practice and just makes our query take longer and consume more resources.

The main purpose of this article was to break the myth that data is physically ordered in the table when you do INSERT… FROM SELECT and ORDER BY, causing many programmers to insist on using ORDER BY in INSERT operations, a scenario that I encounter a lot in consulting clients and is much more common than I would like.

#6 – SQL Server – NOLOCK vs READPAST: Do you know the difference between the two?

In this article, I was able to demonstrate in practice the use of 2 query hints widely used by developers to avoid locks when reading data, which are NOLOCK and READPAST, and effectively demonstrate the effect of these hints on a query.

The idea of ​​writing this article came from a question sent to the group “SQL Server – DBA”, from Telegram, and also an old desire to write about this whenever I see environments where almost all queries have NOLOCK.

After reading this post, you will be able to understand exactly how these 2 hints work and will use them wisely and only when convenient. No need to put NOLOCK/READPAST in all your queries, huh!

If your environment has a lot of competition and locks, blocks and deadlocks are frequent and a problem for you, I suggest thinking about a more complete approach than using these hints, which would be to use the Read Committed Snapshot (RCSI) isolation mode, which allows you to use the Read Committed mode without blocking reads when open transactions occur. As not everything is rosy, there are some side effects when using this mode, such as a possible drop in performance. If you want to know more about him, I suggest reading the article Read Committed Snapshot Isolation: Writers Block Writers (RCSI), by the great master Brent Ozar.

#5 – SQL Server – How to avoid and protect yourself from Ransomware attacks, such as WannaCry, on your database server

In this blog's 350th article, I shared with you my experience during several tests I carried out on Ransomware on SQL Server database servers, such as WannaCry, which I downloaded and “infected” my VM just to carry out these tests, understand how it acts and how we can protect ourselves against this type of attack, which, incredible as it may seem, is still common in the daily lives of DBA's who work in consulting companies.

According to the Internet Security Booklet, Ransomware is a type of malicious code that makes data stored on equipment inaccessible, usually using encryption, and that demands ransom payment (ransom) to reestablish access to the user, where ransom payment is usually made via bitcoins or other cryptocurrencies.

The best-known Ransomware to date is WannaCry, which was considered the largest attack of this type ever recorded to date, starting on 05/12/2017, attacking around 150 countries and infecting more than 230 thousand systems, although there are several others running on the network.

#4 – SQL Server – Useful day-to-day DBA queries that you always have to look for on the Internet

In this article, I had the pleasure of being able to share with you several useful DBA day-to-day scripts that you always have to look for on the Internet when you need to make a certain query. My idea in this article was to make your life easier and have an article with several scripts, for different purposes, for you to favorite in your browser and always have the information you want in one place?

#3 – SQL Server – Security Checklist – An SP with more than 70 security items to validate your database

In this article, I shared with you a project that I have been developing since November 2018 and today has more than 6,000 lines of code, which is a very complete Security Checklist (probably the most complete and comprehensive you will find on the Internet), with more than 70 Security items to validate your database, including configurations and parameters, permissions, programming objects and much more!

After seeing so many companies, developers (and sometimes DBA's themselves) neglect the security aspect, where we see environments in which the application uses the user “sa”, we find thousands of connection attempts with incorrect passwords and no one does anything, environments WITHOUT BACKUP and many other absurdities, we decided to create a very practical and easy way to quickly get an overview of how the instance's security is, in a friendly format and with technical information at the same time, and which allows you to easily export to Excel and demonstrate the various problems to the client. found, the impact it can have on the environment and how to resolve it.

Discover the definitive solution to the vast majority of your SQL Server security problems in this article.

#2 – General Personal Data Protection Law (LGPDP or LGPD) applied to SQL Server databases

In this article, I was able to address a topic that is very popular in the area of ​​technology in general, which is the General Personal Data Protection Law (LGPDP or LGPD), a “cousin” of the GDPR that is in force in Europe, and should become a reality in Brazil from August 2020, bringing several changes to the way IT professionals work in their daily lives and the way products (Software, databases, etc.) are developed.

LGPD is the acronym for General Data Protection Law, whose objective is to increase the privacy of personal data and avoid cases such as major information leaks and scandals that involve precisely the misuse of personal information that we have been following in recent years. The creation of this law places Brazil on the list of more than 100 countries that, today, can be considered adequate to protect privacy and the use of data on the global stage.

Unlike everything else I have read on this topic, the objective of creating this article was to focus this analysis specifically on SQL Server databases, demonstrating how we can improve the security of our database and comply with this new law.

#1 – SQL Server – Using calculated columns (or computed columns) for Performance Tuning

In this article, I would like to share with you something that I see a lot on a daily basis when I am carrying out Tuning consultancy, which are time-consuming queries, with high I/O and CPU consumption, and which use WHERE or JOIN functions in tables with many records and how we can use a very simple calculated (or computed) column indexing technique to solve this problem.

As I comment in the article Understanding how indexes work in SQL Server, when using functions in WHERE or JOINS clauses, we are violating the concept of SARGability of the query, that is, we are causing this query to no longer use Seek operations in the indexes, since SQL Server needs to read the entire table, apply the desired function and then compare the values ​​and return the results.

What I want in this article is to show you this scenario happening, how to identify it and some possible solutions to improve query performance. So, let's go!

Good guys!
I hope you enjoyed this little list, a big hug and see you in the next post!