Arun Sirpal explains some of the security features exposed in Azure SQL Database:
You do not really have to use all of them, this is down to you and your requirements but at least you have decisions to make. I will mention TDE (Transparent Data Encryption), I know a lot of people will opt for TDE in Azure. The big advantage of TDE in Azure over the earthed flavour is that Microsoft does a lot of the work for you, especially around the key management side of things. Also assuming your database is in a geo-replication partnership it will be protected by a different key on each of the servers. Microsoft will also rotate your certificate at least every 90 days, doing this with a local based SQL Server can be quite manual and fiddly (well I think it is).
Read the whole thing if you’re thinking of moving forward with Azure SQL Database, or if you already have a database up in Azure and haven’t checked the latest offerings yet.
Arun Sirpal lets out a dirty secret:
We are here to talk about mistakes we used to make. There is one mistake that I am going to discuss and is something that I used to do 10 years ago, obviously I do not do this anymore.
I USED TO SHRINK DATABASES
There, I said it! Why did I do this? Well I never knew any better. Why shouldn’t you do this?
There are situations where a database shrink operation makes sense. Those cases are quite rare.
Arun Sirpal walks through the SQL Server Diagnostics preview:
Notice the region to upload – If you are using a work machine I would suggest getting authorisation. The great thing here is that this is GDPR compliant.
Once ready hit the upload button, it goes through 3 phases. Upload, Analysis and a recommendation.
It sends your dump files to an external service, which is important enough to point out. If you want more details on the product, Rony Chatterjee has a FAQ.
Arun Sirpal points out an oddity in sys.databases:
If you query sys.databases, such as:
SELECT is_encrypted,name,user_access_desc FROM sys.databases WHERE database_id = 2 OR database_id = 7
It “might” throw you off. Would you not expect to see is_encrypted set to 1 for TempDB?
I thought I remembered earlier editions of SQL Server showing is_encrypted = 1 for tempdb, and I definitely remember 2016 showing 0 even when the database is encrypted.
Arun Sirpal shows how to install and use WinDbg to read SQL Server dump files:
Disclaimer: I only issue basic commands, when in the WinDBG command line you can issue all sorts, but for someone like me there is no real point.
Select the file and let it go to work – you will see BUSY messages.
Really understanding how the debugger works is a great skill to have.
Arun Sirpal ran into an issue with memory-optimized tables:
Naturally (whether right or wrong) I thought that it could be corruption and I didn’t want to rule it out. I headed straight to the error log to see if it could guide me, all this did was confuse me. However after some research it led me to conclude that “Unable to call into the C compiler GetLastError = 2” for the specific error code shown below meant that a specific file(s) could not be found, I was thinking exactly what file(s)?
This is a snippet from the error log when the database went through recovery – Seems to even struggle during the analysis phase and the checkpoint failure was throwing me off slightly.
It’s worth reading in case you experience this error.
Arun Sirpal has an Extended Events session which tracks growth events on a database file:
A quick post that is hopefully useful, I wanted a quick way to find the time, size of the database file size change and who caused it.
I went down the extended events route using sqlserver.database_file_size_change event. By the way I am no Extended Events expert, I write a lot via trial and error I am trying to wean off Profiler.
Read on for the script as well as a query which shreds the XML and returns a result set.
Arun Sirpal shows how to set up Azure SQL Database to use Azure Active Directory accounts:
I think it is important to highlight a couple of points, more specifically around the requirement of ADALSQL.DLL and proper setup of AD which I will highlight below and reference some links, please do this as it lays the foundation for you.
ADALSQL.DLL
You need ADALSQL.DLL which is part of the latest SQL Server Management Studio (SSMS) to test access. This stands for Active Directory Authentication Library for SQL Server.
This goes through some of the issues Arun had setting everything up and provides workarounds and explanations.
Arun Sirpal shows how to create a new MySQL database in Azure:
Here you have the concept of compute units. No such thing as DTUs here but just as confusing.
Compute Units are a measure of CPU processing throughput guaranteed to be available to a single Azure Database for MySQL server. A Compute Unit is a blended measure of CPU and memory resources. In general, 50 Compute Units equate to half-core, 100 Compute Units equate to one core, and 2000 Compute Units equate to twenty cores of guaranteed processing throughput available to your server. I am not going to rehash official documentation on these concepts so I recommend reading https://docs.microsoft.com/en-gb/azure/mysql/concepts-compute-unit-and-storage
Different database product, different metric, it seems. Check out Arun’s post as he walks you through the process step by step.
Arun Sirpal is a dangerous man of mystery and danger, but mostly danger:
I did a dangerous thing, and I want to make sure that YOU DO NOT do the same.
I was creating a couple of extended events sessions and was playing around with some actions. I ended up with the following code where I was after a guy called Shane:
The probability that you intend to set a breakpoint in SQL Server via Extended Event is quite low (low enough that if you’re doing it, you should already know what you’re doing), but click through to see exactly what damage you can do.
Kevin Feasel
2017-07-13
Cloud, Security
No Comment