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.
After using Microsoft SQL Server for over 10 years going back to MySQL feels weird BUT with Azure it is really easy to build a MySQL database and I have a feeling that this might be the start of an incoming change for some. For this post I want to show you what Azure currently offers I then create a MySQL database and connect to it via Azure Cloud Shell.
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.
I had a need to setup transactional replication from my SQL Server to SQL Database (Azure) where I only needed a selection of tables that I wanted to off-load to Azure. For this post I will be going through the setup, post checks with T-SQL / replication monitor, checking the incoming commands with Azure’s query performance insight tool and finally hooking it up to Power BI.
Logging into the Azure portal is a daily task of mine and my eyes light up when I see features in preview mode. With that being said Azure Advisor Recommendations is what I will be writing about today.
Over the past 6 months I have been trying to push myself to use extended events (XEvents) over SQL trace, once you get past the learning curve it’s probably the way to go. If you are operating in the Azure space then you have no choice. Extended events are what you will need to use if you want to collect information against SQL database. There are some subtle differences on how you write the T-SQL between SQL database (Azure) and your locally installed SQL Servers.
This is a new feature where you can issue TSQL commands via a TSQL editor straight from the Azure portal, why this is great is because sometimes you do not want to keep switching between your SSMS (SQL Server Management Studio) connection and the Azure portal to issue basic commands and that assumes that you have the right version of tools installed and correct ports opened locally.
This is in public preview and you can do the following:
- Query dynamic management views for real-time workload insights (Which is what I will be doing).
- Issue ad-hoc queries.
- Manage your user authentication.
To access the editor do the following: Go to your SQL database select the Tools button and then select the Query editor button.
Here you will see the Query editor option.
You will need to login, I have only SQL authentication setup but if you use Active Directory then you have the choice to use the other options.
If you fail you will obviously be told.
You want to see a blue authentication image!
Now let’s issue a query – instead of switching over to my local SSMS connection. How about some database level wait stats? I really need to look into the IO_completion wait type!
Then if you really want you can save your query (which saves it to your local machine as a SQL file):
Saved locally as per below.
Or you can open a SQL file locally (Open query option) and let that be imported into your Azure portal session, for example you could have the waits stats script pre-written and just open it up whenever you like.
It will not create a new window, it basically overwrites the contents of the current open query window, I can work with that.