I think many have covered how you should backup your SQL Server database to Azure storage (also known as backup to URL) but what about restoring? Lets assume you have setup backups and they are working, this is what I usually do.
In the previous blog post I did a quick overview building a SQL VM (imaged) in Azure. It is now time to clarify some backup techniques because it can get confusing.
At a high level there are 3 techniques.
- Automated backup.
- Azure backup for SQL VM (that’s what MS call it).
- Manual backup, for example backup to URL.
I prefer not setting up manual backups to storage accounts, I have done it, I just find it painful to setup/support/fix. So my choice would be automated backup vs “Azure backup” for SQL VM. What’s the difference?
Let’s create a virtual machine in Azure that has an imaged copy of SQL Server on it. I want to do this because down the line I want to show how you can setup automated backups to a storage account based on an IaaS extension, when I do that I will most likely talk about the different backup options because there are many.
Hopefully you know that SSMS is a separate install from the main SQL Server install. You can find the binaries from Microsoft (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms) Using SSMS version 18.2, whilst playing around with the options I noticed a dark theme was available, easy to say I got a little excited, then I didn’t.
I was generally reading about SQL Server and how things have changed since the days of just having it within a Windows Eco-system only. It then led me to a cool website, SQL Server development – build an app using SQL server where you can get to see the high-level requirements of starting to build a solution based on a wide variation of languages and operating systems. https://www.microsoft.com/en-us/sql-server/developer-get-started/
There are many combinations possible with SQL Server being the back-end database of choice.
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)
Another re-post of a video from last year, this time showing you an in-built protection of setting max server memory for your SQL Server. You can clearly see that if you enter a silly figure such as 50 MB, the minimum memory amount allowable for max server memory is 128 MB. You will see SSMS (SQL Server Management Studio) change it to 128MB.
A quick video clip showing how to create a deadlock in SQL Server and find information about it.
I seem to be writing solely about Azure so to shake things up a bit I am going back to my “roots”. In SQL Server your differential backup is cumulative and NOT incremental and a differential will contain the data that has changed since the last full backup.
Let’s dig in using DBCC PAGE.
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.