We all want high performing applications and when you are in the cloud that is no different, if anything it is even more important. With this post I will discuss some areas where I have been “stung” by so you can learn from my mistakes when using Azure SQL Database. Then I will dedicate a section on what tools you can use to help with your performance tuning exercises.
I have decided to do a 4-part series on Cloud “Fear Busting” scenarios. Why? Over the past few years working with the cloud (Azure) I have come across 4 main “fears” or “concerns” that stand out in my mind that people have highlighted when adopting cloud technology for their database tier. Each “fear” with form a blog post where I am hoping that after reading each post you will be “less” fearful. More specifically I will be looking at these topic areas:
- I have security fears for SQL Database.
- Performance Issues that I faced – Learn from my mistakes.
- There is no going back – can I get the data back?
- I’m a DBA – Will I lose control?
Hopefully you will join me.
They are watching me and my Azure SQL Database and recently I noticed a low impact performance recommendation was made. Naturally I became very interested. Within your database (when in the portal) under operations you may notice something similar to the below:
Setting up AD authentication with Azure SQL Database sounds simple, it is assuming you plan carefully. I did run into issues but once rectified it felt great using AD authentication in Azure rather than just SQL logins.
A small but nice little feature I have been using recently can be found within Query Store.
This month’s T-SQL Tuesday entry hosted by Koen Verbeeck (http://sqlkover.com/t-sql-tuesday-89-invitation-the-times-they-are-a-changing/), a blog post about how we feel about the ever changing times within our technology space. Personally I love this new era of cloud computing and do not feel threatened in anyway.
I want to show you how I went from having multiple single SQL databases in Azure to a database elastic pool within a new dedicated SQL Server. Once setup I create and use elastic jobs. This post is long but I am sure you will find it useful.
A very quick post for today, I was navigating my way through the Azure portal within my SQL databases section and came across an edition that I had never seen before and I became very intrigued. It is called Premium RS (currently in preview mode) meaning we now have four tiers to select from – basic, standard, premium and premium RS.
If you ever need to move a copy of a SQL database in Azure across servers then here is a quick easy way.
If you have created a SQL Database in Azure (PaaS) and need to make a copy of it on the same server I will show you how via a quick method.
When connected to your master database execute the following: It creates SAP2 based on SAP.
CREATE DATABASE SAP2 AS COPY OF SAP
To track the progress you can use the following query.
SELECT * FROM sys.dm_database_copies WHERE partner_database = 'SAP2'
Replication state 1 means seeding, this means that the SAP2 database has not yet completely synchronised with the original database. 0 means pending, which means that the copy is being prepared.
Once this has completed you can check sys.databases to confirm that the database has successful been created.
SELECT * FROM sys.databases WHERE name = 'SAP2'
If you really want you can query sys.dm_operation_status to see the operations that have been performed on a database. I would expect to see some sort of operation around copying on my original database.
SELECT * FROM sys.dm_operation_status WHERE major_resource_id = 'SAP' ORDER BY start_time DESC;
If you are interested in other Azure based SQL queries check out an older post of mine: https://blobeater.wordpress.com/2016/10/18/azure-sql-database-dmvs/