Spectre and Meltdown Impact on SQL Server

Meltdown - @SeniorDBA

Spectre and Meltdown Vulnerabilities

Modern CPUs that use of speculative execution has led to the vulnerabilities now known as Spectre and Meltdown. “Speculative Execution” means that if the CPU is processing instructions A, B and C, it might execute instruction B before it has the results of instruction A. This vulnerabilities potentially allows malicious software to read what should be protected memory on an unprotected computer system, allowing access to sensitive data such as passwords, encryption keys, or even cached data like the SQL Server buffer pool. Since speculative execution is intended to boost CPU performance, removing this feature will likely impact SQL Server performance by making queries run as much as 30% slower.

Continue reading “Spectre and Meltdown Impact on SQL Server”

Advertisements

Blockchain Technology in Plain English

Global - @SeniorDBA

Generally speaking, Blockchain is the digital and decentralized ledger that records transactions. Technically it is an algorithm and distributed data structure for managing electronic transactions without a central administrator. This makes it perfect for transactions among people who know nothing about one another. It was originally designed for the crypto-currency Bitcoin, and was initially driven by the rejection of government-guaranteed money and expensive bank-controlled payment transfers.

Continue reading “Blockchain Technology in Plain English”

Understanding SQL Server Index Fragmentation

Data Pages - @SeniorDBATo understand SQL Server index fragmentation, you have to first understand how SQL Server stores data. You may already know that SQL Server stores data on 8KB data pages, and that eight contiguous data pages form an extent. Each data page in both in clustered or non-clustered indexes contains data and pointers to the next and previous pages.

As data is added to a table, the index data must be saved to a data page. If there isn’t enough room on an existing page, the SQL Server engine will create a new data page to store the data, but it must also adjust the pointers on the existing data pages to reflect the new sequence of the impacted data pages. This also means that a new extent might have to be created. These new objects may be stored in a completely different part of the storage drive. This causes both database and disk fragmentation, which can make performance an issue over time.

Continue reading “Understanding SQL Server Index Fragmentation”

Network Account Security Checklist

Hackers - @SeniorDBA

Network security starts with creating and maintaining proper user accounts. While it is assumed that network security processes are obvious when it comes to user accounts, I thought it might be helpful to document some of the best practices for the less experienced people that might be tasked with maintaining this process at their company.

Remember that having an established procedure and setting realistic expectations allow you to provide some consistency into your IT processes. Consistent processes tend to be repeatable and reliable, which also means you reduce the chance of surprises and security headaches.

Unique User Accounts – Users should never be sharing user network accounts. Every user must get a unique network account, usually some combination their first and last name. Each user should be responsible for creating and maintaining their own password and they should know to never share their password with anyone. Remember to provide “least privilege” to each account. If the user requires additional access as their role changes the modification request should be made in writing, when possible, from an authorized supervisor.

Continue reading “Network Account Security Checklist”

Top 8 Features of SQL Server 2016


SQL Server - SeniorDBA

Microsoft recently announced the top eight features of SQL Server 2016. The earlier versions of SQL Server has had unparalleled performance, but Microsoft mat have outdone themselves this time with groundbreaking additional features. Microsoft SQL Server is the most popular commercial enterprise-level relational database management system available today. You will be able to scale up to 12TB of memory, reach up to 30x faster transactions and 100x faster queries with enhanced in-memory performance, and run real-time operational analytics over transactional data. Do you agree on their feature selection?

  • In-Memory OLTP helps meet business requirements for increased agility. In-memory OLTP has continued to mature in SQL Server 2016 by extending the functionality to more applications while enhancing concurrency. Microsoft expanded the T-SQL surface area, increasing the number of memory supported into the terabyte range as well as supporting a greater number of CPUs.

Continue reading “Top 8 Features of SQL Server 2016”

Finding Last Password Changed for an Active Directory User Account

You can check the Last Password Changed information for a user account in Active Directory. The information for last password changed is stored in an attribute called “PwdLastSet”. You can check the value of “PwdLastSet” using the Microsoft “ADSI Edit” tool.

  Continue reading “Finding Last Password Changed for an Active Directory User Account”

Reset SQL Server SA Password on Linux

The “sa” account is a critical account on all versions of SQL Server. If you forget your “sa” password, it can cause problems. This article will help you identify the steps required if you have forgotten the password of your “sa” account of your SQL Server instance on Linux.

The mssql-conf utility is a configuration tool that installs with SQL Server on Linux (Red Hat Enterprise Linux, SUSE Linux Enterprise server, and Ubuntu). This handy tool as a replacement for SQL Server Configuration Manager which exists when you install SQL Server on Windows.

Reset the “sa” account password 

  • Connect SQL Server using command-line tool with the existing password to make sure that your current password is working. You will skip this step if you don’t know the password.
sqlcmd -S <SQLInstanceName>-U <UserName> -P <Password>

  • To change the “sa” password, first stop SQL Server service on Linux:
sudo systemctl stop mssql-server
sudo systemctl status mssql-server
  • Reset the “sa” password by creating a new strong password :
/opt/mssql/bin/mssql-conf set-sa-password

Note: When you are resetting/changing “sa” password using sqlcmd in a bash terminal,  you must not use the special character “$”. 

  • Start and verify the status of SQL Server Service:
sudo systemctl start mssql-server
sudo systemctl status mssql-server

  • Connect SQL Server with the new password:
sqlcmd -S <SQLInstanceName>-U <UserName> -P <Password>

  • Once you are successfully connected, you know the new password is correctly altered.

Change password using sp_password

  • Login with any User account that has sysadmin access on the server.
  •  syntax: sp_password NULL, ‘<insert_new_password_here>’, ’sa’
  • command :-

sp_password NULL, 'Admin@1433', 'sa'

  • Log into the SQL Server instance using the “sa” account and the new password to verify it has been successfully changed.