CODEWITHSUNDEEP
sql-serverazure-sql-database
dumbledad
dumbledad

Reputation: 17527

How do I stop and start a SQL Azure database?

I am running an Azure based site that for historic reasons uses two databases on two servers. I have copied the data from the static data-source so that both reside on the same server. I am fairly sure I have removed all references to the old server from the live code, and indeed the Azure dashboard shows no connections over the last month. But to be 100% sure before I delete the server I would like to stop it and test the live site. If anything goes wrong I would then like to start it again. In SQL Server Management Studio this is usually straightforward, I can right-click on a server in the SQL Server Management Studio object explorer and select 'Stop' and then if needed 'Start'. However 'Stop' and 'Start' are not listed in the options for SQL Azure servers, nor is there anything I can see in the management pages on the Azure portal.

So my question is simple - how do I stop and start a SQL Azure database?

Upvotes: 23

Views: 60779

Answers (7)

Lee Eyre
Lee Eyre

Reputation: 11

I know this is an old thread, but this may be useful for someone in the future. We had an issue where our SQL Azure database was running really slow, every query taking 25 seconds or more which usually were a few milliseconds. Microsoft told us to run the following command, which immediately fixed the issue.

DBCC STACKDUMP('Failover')

In out case, the issue was on the read-only replica so we needed to run this when connected with ApplicationIntent=ReadOnly in the connection string, so that it was the read-only replica which failed over.

SQL Commands that were running at the time errored, but the failover was instant so nobody noticed any downtime.

Upvotes: 1

Chris Magnuson
Chris Magnuson

Reputation: 5949

Warning, the top google result for "restart sql server database azure" gives one of the options for restarting as running DBCC STACKDUMP('Manual Failover – Reason: something').

Running that definitely causes something to happen, but in our case after 5 minutes the DTUs still weren't registering and the portal wasn't able to pull information on the size of the database.

After waiting 10 minutes for it to come back we ended up changing the tier and roughly 18 minutes later the tier change finished and the database was accessible again.

Upvotes: 2

Sÿl
Sÿl

Reputation: 623

If this helps: in my situation, the database was locked. I could not alter tables, schema or anything else.

The way I found to handle this case was forcing read only and read write with rollback immediate.

 ALTER DATABASE [MyDB]
 SET READ_ONLY
 WITH ROLLBACK IMMEDIATE; 

 ALTER DATABASE [MyDB]
 SET READ_WRITE
 WITH ROLLBACK IMMEDIATE;

Upvotes: 2

Aurel
Aurel

Reputation: 423

A quick and dirty rename can work pretty good. Admin rights required!

USE master;  
GO  
ALTER DATABASE MyDatabaseIsAvailable
Modify Name = MyDatabaseIsNoMoreAvailable;  
GO

do the opposite once you're done. The good thing is you can 'stop' a single Database when you have more than one on the same server

Upvotes: 2

Jowen
Jowen

Reputation: 5393

We had an issue with a Sql Azure db and were pretty sure it was in the server/service. We couldn't find the stop & start button, but did find a workaround:

Scale the database to another tier and then scale it back!

It solved our db issue.

Everybody says you don't need a stop/start button... but sometimes the theory differs from practice. Even Azure has issues :)

Upvotes: 79

Sirisha Chamarthi
Sirisha Chamarthi

Reputation: 1293

You can simply block the IPs (remove all the firewall rules, so that no one can connect)

Upvotes: 7

JuneT
JuneT

Reputation: 7860

as Praggie mentioned, you cannot stop/start the SQL Azure servers. A SQL Azure Database resides on a shared host. there are other tenants on that server. you have access to the database, but not to the corresponding hosting server.

you can rename the database and if there's any app connecting to it, then they'd fail.

Upvotes: 9

Related Questions