Speak guys !!
New year is coming, everyone is getting ready for New Year's Eve and for that reason, 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 most viewed . I hope you like this short list and that some article might be useful for you, if you haven't seen it before.
Not much wind and let's go to the list.
# 10 - SQL Server - How to prevent brute force attacks on your database
In this article, I demonstrated how brute force attacks occur on SQL Server and how to try to defend against such attacks.
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 systematically checking all possible keys and passwords until one 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 an encryption system (if any) that would make the task easier, as the time required to test all possible passwords can be more than a few seconds (3 characters) for 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 time consuming queries using Extended Events (XE)
In this article I shared with you how to identify and collect time consuming query information using Extended Events (XE), in a very similar article to SQL Server - How to identify and collect time consuming query information using Trace (SQL Server Profiler), which uses the technology of Profiler (Trace).
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. So, thinking about bringing you a more modern and intuitive solution, I decided to share this solution using XE.
# 8 - SQL Server - Performance Tuning Tips: Implicit Conversion? NEVER!
In this article I would like to comment on a query performance issue that we encountered a lot here in our day to day Fabrício Lima - Data 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 for comparison, concatenation, or other operation with other columns or variables because SQL Server cannot compare a column of type varchar with another of type int, for example, if it did not convert one of the columns so that both have the same data type.
# 7 - SQL Server - When you should use ORDER BY in your query and when you should not use it at all!
In this article, I shared with you when to use ORDER BY and when we shouldn't use it at all, because it has no effect on practice and just leaves our query taking longer and consuming 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 I encounter. quite a lot in consulting clients and is far 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 data reading locks, which are NOLOCK and READPAST, and effectively demonstrate the effect of these hints on a query.
The idea of writing this article came through a question sent in the group "SQL Server - DBA", from Telegram, and also an old desire to write about it whenever I see environments where almost every query has 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 more putting NOLOCK / READPAST on 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 of 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 Read Commited mode without locking readings when open transactions occur. Since not everything is flowers, there are some side effects when using this mode, such as possible performance degradation. If you want to know more about it, I suggest reading the article Read Committed Snapshot Isolation: Writers Block Writers (RCSI), by the great master Brent Ozar.
# 5 - SQL Server - How to prevent and protect yourself from Ransomware attacks, like WannaCry, on your database server
In this article number 350 of the blog, I shared with you my experience during several tests that I did on Ransomwares on SQL Server database servers, like WannaCry, which I downloaded and “infected” my VM just to perform these tests, understand how he acts and how we can protect ourselves against this type of attack, which, oddly enough, 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 a device inaccessible, usually using encryption, and requires ransom to re-establish user access, where payment of Rescue is usually done via bitcoins or another cryptocurrency.
The most well-known Ransomware to date is WannaCry, which was considered the largest attack of its kind to date, starting on 12 / 05 / 2017, attacking around 150 countries and infecting more than 230 thousand systems, although there are several others running over the network.
# 4 - SQL Server - Useful daily DBA queries that you always have to keep looking for on the Internet
In this article, I had the pleasure to share with you, several useful scripts of the day to day of the DBA that you always have to look 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 bookmark 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've been developing since November 2018 and today has over 6.000 lines of code, which is a very complete (probably the most comprehensive and comprehensive) Security Checklist you'll find on the Internet, including with over 70 Security items to validate your database, including settings and parameters, permissions, programming objects, and more!
After so much watching companies, developers (and sometimes DBAs themselves) neglect the security part, where we see environments where the application uses the "sa" user, we find thousands of attempts to connect with the wrong password and nobody does anything , NO BACKUP environments and so many other nonsense, we decided to create a very practical and easy way to quickly get an overview of how instance security is doing, in a friendly format and with technical information at the same time, allowing to easily export to Excel and demonstrate to the customer the various problems encountered, the impact this can have on the environment and how to solve it.
Know in this article the ultimate solution to the vast majority of your SQL Server security issues.
# 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 Law for the Protection of Personal Data (LGPDP or LGPD), a “cousin” of GDPR that is in force in Europe, and it should become a reality in Brazil from August 2020, bringing several changes in the way in which IT professionals work in their daily lives and in the way products (software, databases, etc.) are developed.
LGPD stands for General Data Protection Law, which aims to increase the privacy of personal data and to prevent cases such as the massive leaks of information and scandals that involve the misuse of personal information that we have been following in recent years. The creation of this law places Brazil in the list of more than 100 countries that today can be considered adequate to protect privacy and data use in the global scenario.
Unlike everything I've ever read about material on this topic, the purpose of writing 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 it. 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 I see a lot in everyday life when I am performing Tuning consulting, which are time consuming, I / O and CPU consuming queries that 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 Index Functioning in SQL Server, when using functions in WHERE or JOINS clauses, we are hurting the concept of query SARGability, meaning that this query will no longer use Seek operations on 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 going on, how to identify it and some possible solutions to improve query performance. So, come on!
Good guys!
I hope you enjoyed this short list, a big hug and until the next post!
