Sometimes even the easiest tasks can result in unexpected behavior. With this post I want to share with you a couple of dark clouds that I encountered when trying to rename a database living in Azure.
One of the great things of Azure is the ease of creating resources and tweaking them to your needs. The Azure Portal is the ultimate platform to manage these resources and configure them on the fly. Sometimes however you will need to revert back to command lines or external tools in order to achieve the tweak that is not available from the Azure Portal interface (yet).
In my case I wanted to rename the database in Azure and noticed that there wasn't any option to do this in Azure Portal. In that case Microsoft SQL Server Management Studio is my typical fallback tool whenever I want to apply hacks to the database. I fired it up and executed what I would normally do to rename a database:
- Select the database
- Click one more time on the name or press F2
- Rename it
An alternative to this approach is to execute a SQL script like follows:
ALTER DATABASE [ORIGINAL-NAME] MODIFY NAME = [RENAMED-NAME]
In my case this resulted in the following error:
Msg 42019, Level 16, State 2, Line 1
ALTER DATABASE operation failed. Internal service error.
The reason was that I executed the query against the database (right-click on the database and select New Query). If you execute the query against the master database
everything works fine.
Unfortunately for me this was not the only issue I ran into. Renaming the database via Microsoft SQL Server Management Studio initially caused the application to stop responding. After a couple of minutes it responded again and informed me that the timeout expired. Using Azure Portal I tried to change settings like the pricing tier and instantly got notified about an undefined ErrorCode and ErrorMessage that the operation on the server and database was in progress.
To resolve this I stopped the Azure Web App that referenced the database in it's connection string. This did not solve it directly so I decided to force the connection to be killed using the following SQL commands:
SELECT * FROM sys.dm_exec_sessions
KILL [session ID]
Hope this helps!