Microsoft Data Platform is changing – so am I

This month’s T-SQL Tuesday entry hosted by Koen Verbeeck (, 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.


Continue reading

Azure – SQL Database Premium RS

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.

Continue reading

Setting up SQL Server to SQL Database (Azure) Replication

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.

Continue reading

Using Extended Events in Azure

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.

Continue reading

Interactive query experience for SQL databases

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.



Clearing Plan Cache in SQL Database (Azure)

Sometimes when in a testing mode I do like to clear cache within my SQL database (Azure). Nowadays a lot of my test databases are Azure based so how I clear cache is different to how I would do it with an older “earthed” SQL Server.

Looking at my locally installed SQL Server.



Getting some basic information about the cache for a specific database.

 SELECT st.dbid, cp.objtype, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE dbid = 12


Normally I would run:

-- Database_ID

I have cleared it out.


So, lets go to the Azure world.



Again, get some basic information for my database ID.

SELECT st.dbid, cp.objtype, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE dbid = 6


-- Database_ID

Just does not happen: Msg 15247, Level 16, State 14, Line 9
User does not have permission to perform this action – I am not sure what permission I need? I am logging in as a server admin.

So I used the following instead.



Locking Resources in Azure

I look at locking resources within Azure as a safety mechanism where it can protect users from accidental deletion, it can get quite confusing within the portal if for example you have close to 100 databases.

There are 2 types of lock resources in Azure.

  • Delete – Obviously you can’t delete but you can read / modify a resource, this applies to authorised users.
  • ReadOnly – Authorised users can read a resource but they cannot edit or delete it.

For this blog post I create a delete lock on one of my SQL Databases. Under the settings of your SQL database you will see the LOCKS option.


You will then want to click ADD.


That’s it – let’s put it to a test

Message from the portal:

The lock named NoDel was unable to be added or edited for the following reasons: {“jqXHR”:{“readyState”:4,”responseText”:”{\”error\”:{\”code\”:\”AuthorizationFailed\”,\”message\”:\”The client does not have authorization to perform action ‘Microsoft.Authorization/locks/write’ over scope

Well, that is not what I want! You must be Owner or User Access Administrator.

 Once you have addressed the permission issues just click OK.


Now we can re-try the delete.


You know how the saying goes – An ounce of prevention is worth a pound of cure.

Copying your SQL Database

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.


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:



Restoring Azure SQL Database

A major benefit of Azure SQL Database (PaaS) is the fact that Microsoft manages the backups – it’s great because recovering to a point in time is straightforward.

With all editions of the database full backups are taken every week, differential backups are taken hourly, and transaction log backups are taken every 5 minutes. When you create a database a full backup is scheduled straightaway.

Let’s take a look.

An issue has occurred in SQLDB6 and I want to go back to XYZ AM/PM.


Click on your database and look for the RESTORE ICON.


Enter the database name to restore to and select the restore point.


You won’t have the ability to use the same name of the restoring database and the database that you want to replace; if you try you get the screen shot below: To get around this I think you would need to drop the old one once the new one has restored then do a rename.


SQL Logins

If you created a SQL Login using the contained database user model then fixing broken links after a restore is not an issue.

I have created logins using both the contained model and the traditional model as shown below to show the difference.


Contained database user

You can login straightaway (once the restore completes) with the same username and password.


Traditional Model

However if you used the older / classic approach you will need to fix the orphaned logins.


Under the context of master run:

FROM sys.sql_logins
WHERE type = 'S' and name = 'LuckyLemon'


So we know it exists in master, so run the following in the user database.

ALTER USER LuckyLemon WITH Login = LuckyLemon;


If you want to change the PW at this time then you can if you want via:

ALTER LOGIN LuckyLemon WITH PASSWORD = '<enterStrongPasswordHere>';

The morale of the story is… Try to use contained database users where possible.