Okay honestly I have done this once. I have deleted Azure SQL Databases and then try and find the quickest way to recover. The Azure portal is actually pretty good when it comes to deleting resources, for example it will usually ask you to re-type the name of the resource to confirm deletion, so you can tell what a bad mistake I made.
The infamous setting that we all know and love – MAXDOP. Did you know that you can actually control MAXDOP when using Azure SQL Database? You might not be able to tinker with the Cost Threshold for Parallelism setting but you sure can with MAXDOP.
No, not quite. I have had many interesting conversations around this topic and I don’t think (personal opinion) that DBAs will disappear from the world of IT. It will definitely change, the so-called “production DBAs” affected the most. With Azure SQL Database (and other company offerings – think Oracle Autonomous DB) you will need to adapt (both short term and long term) some argue with me that the long-term looks harder to predict for DBAs which is true so I can’t talk about that. I mean for example how good will automatic tuning get? Better than the average DBA? I am looking at things from a short-term perspective. What tasks are expected of you to carry out in this new world? Well, this is from experience.
In my mind there are a couple of ways to move a database across resource groups. They vary from scripting to just using the Azure portal. I am going to use the Azure portal and do the following.
- Export a database in resource group X to a storage account Z.
- Import the file from the storage account Z into a database that is in resource group Y.
It’s just like a “backup and restore” strategy, all with the assumption that you are working within the same subscription ID.
At first I was nervous but then I realized it was me triggering “Someone has logged on to your server from an unusual location” message against my Azure SQL database. The process of investigating the “rouge” login didn’t help with what Microsoft offered , I mean it seems hit and miss whether I get routed to the correct audit information via the investigation steps.
So you want to do a clean-up exercise in Azure and remove some databases. You go to delete a database that is no longer required (as shown below).
I use elastic pools. They are a fabulous way of saving money when running many Azure SQL Databases, that is assuming you understand the resource utilization patterns of the databases involved.
Lets just get straight to the point, Azure SQL Database across all service tiers gives you the customer a SLA of 99.99% up-time. This means potential unavailability periods shown below.
Have you ever heard of SQL Reserved vCores? Well I never until recently. With this concept you have the ability to save money by PRE-PAYING for your compute resources for Azure SQL DB where you might be currently using a pay-as-you-go plan.
Using a query label in Azure SQL DW (Data Warehouse) can be a really handy technique to track queries via DMVs. You might want to do this to see what problematic queries are doing under the covers.
Let’s check out an example. First I will show you how things would look without using a query label. I connect to SQL DW and issue the following basic example query.
SELECT sum(p.StandardCost),ps.[EnglishProductSubcategoryName] FROM [dbo].[DimProduct] p JOIN [dbo].[DimProductSubcategory] ps on P.ProductSubcategoryKey = ps.ProductSubcategoryKey GROUP BY p.standardCost, ps.[EnglishProductSubcategoryName] ORDER BY ps.[EnglishProductSubcategoryName]
So what query would you use to see what is going on within this MPP environment? You would normally use sys.dm_pdw_exec_requests and/or sys.dm_pdw_request_steps. Something like:
SELECT r.session_id, r.start_time, r.end_time,rs.location_type, rs.status,rs.command,rs.step_index, rs.operation_type,rs.request_id FROM sys.dm_pdw_exec_requests r JOIN sys.dm_pdw_request_steps rs ON r.request_id = rs.request_id
However you probably would not want to manually go through the output to try and piece the output together, this is where labels are handy. The original code now becomes:
SELECT sum(p.StandardCost),ps.[EnglishProductSubcategoryName] FROM [dbo].[DimProduct] p JOIN [dbo].[DimProductSubcategory] ps on P.ProductSubcategoryKey = ps.ProductSubcategoryKey GROUP BY p.standardCost, ps.[EnglishProductSubcategoryName] ORDER BY ps.[EnglishProductSubcategoryName] OPTION (LABEL = 'QUERY: MySalesCodeSum');
We then use this LABEL within the DMV based query.
SELECT r.session_id, r.start_time, r.end_time,rs.location_type, rs.status,rs.command,rs.step_index, rs.operation_type,rs.request_id FROM sys.dm_pdw_exec_requests r JOIN sys.dm_pdw_request_steps rs ON r.request_id = rs.request_id WHERE r.[label] = 'QUERY: MySalesCodeSum' ORDER BY step_index
Now you will have the output relevant to the original query only.
High level SQL DW is creating temp tables such as:
CREATE TABLE [tempdb].[dbo].[TEMP_ID_16] ([ProductSubcategoryKey] INT, [StandardCost] MONEY ) WITH(DATA_COMPRESSION=PAGE);
DMS does the famous ShuffleMoveOperation then the compute node actually ends up running:
SELECT [T1_1].[col] AS [col], [T1_1].[EnglishProductSubcategoryName] AS [EnglishProductSubcategoryName] FROM (SELECT SUM([T2_2].[StandardCost]) AS [col], [T2_1].[EnglishProductSubcategoryName] AS [EnglishProductSubcategoryName] FROM [tempdb].[dbo].[TEMP_ID_15] AS T2_1 INNER JOIN [tempdb].[dbo].[TEMP_ID_16] AS T2_2 ON ([T2_1].[ProductSubcategoryKey] = [T2_2].[ProductSubcategoryKey]) GROUP BY [T2_2].[StandardCost], [T2_1].[EnglishProductSubcategoryName]) AS T1_1 ORDER BY [T1_1].[EnglishProductSubcategoryName] ASC
Quite cool right?