SQL Server 2017 Encrypted Backups And Compression

Let’s work through some code to do an encrypted backup. This feature is available to you if you are using SQL Server 2014 onwards but I decided to use SQL Server 2017.

To encrypt during backup, you must specify an encryption algorithm, and an “encryptor” to secure the encryption key. I have decided to use the following options:

  • Encryption Algorithm: AES 256
  • Encryptor: A certificate

Continue reading

SQL Server Tracking Tempdb Size

Here is a quick Extended Events script I knocked up where I wanted to track Tempdb file size changes for both the data and log file. I wanted to know who caused the tempdb growth, when it was done, what the T-SQL was and what sizes were involved. Not exactly complicated but hopefully useful.

Continue reading

SQL Server Tracking Suspect Pages

Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?

Continue reading

Azure SQL Database Resumable Online Index Rebuild

If you know me by now I like rebuilding indexes and that is no different in Azure. Now we have the ability to resume a paused rebuilding operation rather than cancelling it (Feature currently in public preview). I like this because I have the flexibility to pause it if I feel that it is taking up too much DTU (Database Transaction Unit) usage hence I can free up resources for other operations.

Continue reading

[Video] SQL Server Deadlocks

SQL Server Deadlocks – Also known as “The deadly Embrace” occurs when there are 2 processes and neither can advance and get the relevant lock it needs because the other process involved is preventing it from getting the resource it requires.

deadlocks

In this quick video clip I create a deadlock scenario and check how to get some deadlock information via system health extended events session.

Below is the T-SQL for querying the system health session. This was against a SQL Server 2014 install so you may need to change the file location.

SELECT top(1)
CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime2') AS [TimeOfDeadlock],
CONVERT(xml, event_data).query('/event/data/value/child::*') AS [DeadlockGraph]
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\system_health*.xel', null,  null, null)
WHERE object_name = 'xml_deadlock_report'
ORDER BY [TimeOfDeadlock] DESC

SQL Server TDE – Is TempDB Encrypted?

A really quick one today, something that made me think for a minute and I thought it might make others think too. So you have enabled TDE  – Transparent Data Encryption (you can see these previous posts here: https://blobeater.blog/?s=tde&submit=Search) on your SQL Server database and in the back of your mind you know TempDB gets encrypted too.

Continue reading

SQL Server Memory Metrics

A very quick post for today, recently I have been working on some code to gather metrics around SQL Server memory, more specifically, how much memory is on your server, your total / target memory and PLE. (If you want to know more about total vs target see this link: https://blobeater.blog/2017/03/01/sql-server-target-vs-total-memory/)

Continue reading

Using Extended Events in Azure

Over the past 6 months I have been trying to push myself to use extended events (XEvents) over SQL trace, once you get past the learning curve it’s probably the way to go. If you are operating in the Azure space then you have no choice. Extended events are what you will need to use if you want to collect information against SQL database. There are some subtle differences on how you write the T-SQL between SQL database (Azure) and your locally installed SQL Servers.

Continue reading

Checking Time Zones in SQL Server

I was building some basic queries around time zone manipulation and I am happy to say that I enjoyed myself as I found a way to get a time based on a geographic region.

This new feature is available to you from SQL 2016 which obviously means SQL database too (Azure). It is called AT TIME ZONE.

AT TIME ZONE implementation relies on following registry hive where time zones are installed: KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. This is exposed via the following query


SELECT * FROM sys.time_zone_info

Looking at books on line via https://msdn.microsoft.com/en-us/library/mt612790.aspx it is wrong.

Look at the screen shot, it states it is supported from SQL 2008 – well it is not, it only works on 2016.

times

Running it on a non SQL 2016 server you will get the following message:

Msg 208, Level 16, State 1, Line 1 Invalid object name ‘sys.time_zone_info’.

On a correct SQL version it will return the following. (Snippet)

times2

This information is key as we will use it to build the time zone queries (which is very basic).

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time' AS [My Location]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'US Mountain Standard Time' AS [US Mountain Standard Time]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Central Asia Standard Time' AS [Central Asia Standard Time]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Central Standard Time' AS [AUS Central Standard Time]

Based on the zone offset you will get the relevant correct time.

times3