Arun Sirpal takes us through Elastic Jobs against Azure SQL Databases:
The purpose of an Elastic Job is to execute a T-SQL script that is scheduled or executed ad-hoc against a group of Azure SQL databases. Targets can be in different SQL Database servers, subscriptions, and/or regions. This blog post is quite long and heavy (code wise) so grab a coffee and follow through.
The architecture you could follow is shown below.
All of the code is in Powershell and Arun talks us through it.
Arun Sirpal takes us through Azure SQL Database Serverless:
This is best used for those single databases that are ever changing with unpredictable patterns. With the concept of being billed per second (based on the vcores used) rather than per hour means that pricing can become more granular especially now with auto-pause becoming possible. The auto-pause delay defines the period of time the database must be inactive before it is automatically paused (only charged for storage). You should only use this if you can afford some delay in compute warm-up after idle usage periods, otherwise it is best to stick with provisioned compute tiers ( classic tiers).
I could see this being useful for dev or test databases, or maybe a personal site with heavy external caching.
Arun Sirpal tries out something new in SQL Server 2019:
Yes, a really nice new shiny feature where we have the ability to suspend and resume the encryption scan for TDE – Transparent Data Encryption which is available in SQL Server 2019. (Tested against the latest version CTP 2.4)
Arun shows the syntax you can use to suspend and resume should you need to do that.
Arun Sirpal takes us through finding the right size for Azure SQL Database:
Do you want to identify the correct Service Tier and Compute Size ( was once known as performance level) for your Azure SQL Database? How would you go about it? Would you use the DTU (Database Transaction Unit) calculator? What about the new pricing model vCore? How would you translate you current on-premises workload to the cloud?
It can be a form of trial and error especially if you are new to this but I really do recommend trying out the PowerShell script that you can access once you have installed DMA – Database Migration Assistant.
Read on to see how to run this tool and potentially save some money.
Arun Sirpal shows us how we can use T-SQL to set and modify firewall rules within Azure SQL Database:
For this post I want to actually show you the TSQL code to do this, hopefully it will become a good reference point for the future. Before we step into the code lets understand the differences between database level and server level rules.
For server level rules they enable access your entire Azure SQL server, that is, all the databases within the same logical server. These rules are stored in the master database. Database level rules enable access to certain databases (yes you could also run this within master) within the same logical server, think of this as you being more granular with the access where they are created within the user database in question.
Personally, I try and always use database level rules, this is especially true when I work with failover groups.
Click through for instructions on how to work with both server and database level rules.
Arun Sirpal shows us the extended event to watch to learn who changed that auto-tuning setting:
It is said to be safe, reliable and proven using complex algorithms and built-in intelligence where it can do the following (see this link for more details: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning)
- CREATE INDEX – identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.
- DROP INDEX – identifies redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). Please note that at this time the option is not compatible with applications using partition switching and index hints.
- FORCE LAST GOOD PLAN – identifies SQL queries using execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan.
Personally I don’t enable the option where it is allowed a “free-for-all” when creating/dropping indexes and forcing certain query plans. I like controlling the change, especially for production databases. To force this concept I wanted to use Extended Events to know when / if someone changed my settings for automatic tuning against my database.
Click through for the script.
Arun Sirpal shows how to use Azure Logic Apps to auto-scale Azure SQL Database:
When I was presenting my Azure SQL Database session at DataRelay (used to be SQLRelay) I was asked (over coffee) about auto scaling capabilities. Quite simply there is nothing out of the box to achieve this. The idea of auto scaling would be good where you would need a burst to fulfill higher demand in terms of workload for a time duration, you know, something like “end of the day, Friday night sale” for your database.
Classically you would probably go down the PowerShell route via a runbook, but I am different.
In this case, the automation is timer-based rather than load-based.
Arun Sirpal shows us how easy it is to fail over Azure SQL Database to another region:
So what happens now if I connect to the read/write endpoint? (I test this via SSMS)
The dreaded IP address / create a new firewall rule message. Why? Well this setup utilized a “server” level firewall rule and the server in the US did NOT have the IP address mapped in, you can see from the below screen shot that there are no firewall rules configured.
Fixing this is easy, you could just add the IP address on the secondary server as another server level rule but you should seriously consider using a database level firewall rule, the setup will get replicated to the secondary server making failover experience smoother.
Read on to see how to set this.
Arun Sirpal shows us the “undoing a big mistake” button:
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.
Let’s look at how to delete a database then recover it.
I’m curious how long it stays there before dropping off into the abyss.
Can't find what you're looking for? Try refining your search: