I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code:
I do not always use the Azure portal to make database changes or to check for certain information. I use it a lot of for blogging purposes but for some tasks I rather just run code via SSMS – SQL Server Management Studio.
Creating copies of your Azure SQL Database is a common and relatively simple process. You can issue a TSQL statement on the master database such as:
CREATE DATABASE CodeDBcopy AS COPY OF CodeDB
After demo building and testing features out within Azure SQL Database I like to delete the databases to save costs. Very rarely the following happens when trying to delete a database within Azure.
I could not read my error log on one of my local SQL Servers, when I executed the following code:
I received the below:
Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State 1, Line 2 Error log location not found.
It’s good to be proactive and one way is to setup alerts and it is no different when using Azure SQL Database. I like creating alerts for my Azure SQL Databases and I encourage you to do the same.
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
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.
You have the ability to actually pause SQL Server, if you are in SQL Server Management Studio (SSMS), you might have noticed it as the below image.
Have you ever wanted SQL Server Management Studio (SSMS) 17 to have a dark theme? Seeing the below image (visual experience color theme options) really got me excited.