SQL SERVER – How to Restart SQL Azure Database?

SQL SERVER - How to Restart SQL Azure Database? restartazure-1 I have said this many time earlier at various forums and I can still say that – “with my current consulting work, I am learning at least one new thing every day.” This is one of the reason why I love my work! In this blog I am going to share an interesting fact which was shared with me by my customer – How to Restart SQL Azure database? 

My customer contacted me some performance tuning related consultancy. While we were doing some serious work, someone said why don’t we restart SQL Service and all the issues with be resolved. I told them that restart might work but do you really want to take downtime to fix this performance issue? Restart is not a fix but a band-aid. Then I told example of Azure database which is a Platform as a Service (PaaS) offering of Microsoft SQL Server.  In this you would get a database and a logical server to manager. This is a multi-tenant system where there is no option to restart the server. But my client said that it is possible do restart SQL in Azure Database!

I became curious and asked – How can we restart SQL Azure database and why would someone do that?

He told that once they had severe blocking in SQL Azure database and they were killing the processes by using KILL commands. They knew that it was an issue with application where the transactions were not getting closed properly and application team was working on it.

Solarwinds

I became curious and asked – So, how did we restart SQL Azure database?

He informed me that there are two ways to restart SQL Azure database. The right way to do that is by changing the pricing tier of the database. Basically, scale the database to another tier and then scale it back!

SQL SERVER - How to Restart SQL Azure Database? restart-azure-01

Other option which they told me was kind of unusual. They told that restart can also be performed by running below command

DBCC STACKDUMP('Manual Failover – Reason: something')

I became curious about this command and searched more and found here: How can I create a dump of SQL Server?. When I ran the same command in my local SQL Server, it generated a dump and ERRORLOG shows below.

***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
*****************************************************************************
* BEGIN STACK DUMP:
* 12/21/17 13:18:50 spid 24
* StackDump (all) : Manual Failover – Reason: something

SQL SERVER - How to Restart SQL Azure Database? restart-azure-02

But there was NO restart of SQL Service on my lab server. At this point I had no idea whether this would work in Azure.

CONCLUSION

When I asked with my friends who know Azure better than me and they told that none of above the methods would actually restart SQL Server service in Azure, but it would perform failover of database to another server which is hosting copy of the database. The end effect would be that the connection would now go to new server and would give you a feeling of restart.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Account Added Automatically in SQL Server Used by SharePoint
Next Post
SQL SERVER – SQL Vulnerability Assessment – Security Analysis

Related Posts

3 Comments. Leave new

Leave a Reply

Menu