You will very likely know that SQL Server 2008 / R2 end of support is on July 9th 2019. Not that long to start thinking about and assessing your options. For this post I just want to discuss couple of things that you can do and I specifically mention Azure technologies, think of this as a high level starter guide.
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.
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:
I would like to share a link to my Azure SQL Database Stairway series hosted over at SQLServerCentral.
As stated on the website a stairway guide is a series focused on a single topic and is arranged into no more than a dozen easily-navigable tutorials that we call ‘steps’. Each step is the length of a typical magazine tutorial, and emphasizes practical, hands-on learning, with just enough background theory to help you understand the topic.
If you decide to use IP addresses to control what services have access to your Azure SQL Database, then understanding firewall rules are important. Within this important area you have the ability to create firewall rules that are scoped to the database or server level as shown below.
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?
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?
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.
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.
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.