Have you ever wanted to capture the T-SQL, waits, sessions IDs (etc) at a specific time for Azure SQL Database? Sure there are a few ways to do this. Extended Events comes to mind but I wanted to do something different.
Category Archives: Azure SQL DB
Azure SQL Database and Transaction Log
Checking out the transaction log in Azure SQL Database. If you are curious like me, you will want to know about what your transaction log is doing in the cloud. The following queries have been tested and run okay within Azure SQL Database. It gives you some great insight.
Azure SQL Database – Reduce the workload before initiating another service objective update
Getting straight to the point, I initiated a very common task recently, another scale up request. However, a new message popped up. “The service objective assignment for database on server could not be completed as the database is too busy. Reduce the workload before initiating another service objective update”.
Azure SQL DB VS SQL Server For the DBA
Being the start of the new year and with new projects most likely starting again I would like to share with you an article I wrote a while back, but still quite relevant.
Hopefully you find it useful for those wanting to know key differences between Azure SQL Database and a locally installed SQL Server from a DBA perspective and whether or not these key differences can be seen as advantages or disadvantages.
Azure SQL Database DTU Versus vCore
I wrote a guest article for an IT consultancy on Azure SQL Database, more specifically DTUs and vCore options. At a high level I discuss what both options are, the differences and what you should select. Hopefully you will find it useful. Please see the link below:
Azure SQL Database – Fixing My Scaling Failure
This is kind of a follow up from my last blog post about a scale down request issue. (https://blobeater.blog/2018/11/07/azure-sql-database-aborting-scale-request/) I was confused, so confused that I ended up logging a support request with Microsoft. The issue was I wanted to scale down a database from S1 to Basic however it would take hours for a 1GB database. Obviously something was up, but what?
Azure SQL Database – Aborting Scale Request
Scaling up or down an Azure SQL Database is a very common task. Whilst common it is also very easy to do via the Azure portal or even PowerShell. When you scale a database please be aware that it creates a replica of the original database at the new performance level and then switches connections over to the replica but what do you do if you want to cancel the scale request?
Azure SQL Database – Who Changed My Auto Tuning Settings?
One of the features Microsoft wants us to use for Azure SQL Database is Automatic Tuning. Automatic Tuning is a feature where you can think of it as entering the world of self-running and self-tuning databases.
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.
Azure SQL DB – Failed to update database because the database is read-only
Quite an interesting situation I found myself in where I was perplexed for about 5 minutes. I was connected to an Azure SQL Database where I was configuring some users where then I executed a query and was presented with the following message:
Msg 3906, Level 16, State 2, Line 2
Failed to update database “testdb” because the database is read-only.
SQL Server – Reading BACPAC Files
If you have been reading my blog for a while now you would know that a common technique to move to Azure SQL DB is to use BACPAC files. Just a reminder, see the below image.