ClearMeasure/AliaSQL

Running Rebuild against azure fails while trying to drop connections

Closed this issue · 7 comments

bladeradius commented

Firstly, great job everyone with this amazing package.

When running Rebuild against an Azure SQL database Aliasql fails due to Master.dbo.SYSPROCESSES being an invalid object on Azure databases.

"Dropping connections for database xyz
Failure: Invalid object name 'Master.dbo.SYSPROCESSES'.
Erroring script was not run in a transaction and may be partially committed."

Looking through the source code it seems that DatabaseConnectionDropper runs the DropConnections.sql file which contains the SQL statements Azure does not like.

What is everyone's thoughts on this?

ericdc1 commented

In my experience, you can't drop an azure database and just recreate it via script. The databases have to be made via the website. In practice, rebuild is great for development and testing but once the database is on a production system you really should be doing Updates only.

bladeradius commented

Thanks for the quick reply and I agree! I believe you can drop and re-create databases on Azure with just scripts. What if we have a test and development environment including corresponding databases on Azure just like a local environment? This is the case for us.

ericdc1 commented

That is entirely possible. If you can some up with the drop and create scripts I would be glad to implement. I will look and see what I can find in the mean time. Here is the code that does this now. https://github.com/ClearMeasure/AliaSQL/blob/master/source/AliaSQL.Core/SqlFiles/DropConnections.sql

ericdc1 commented

I came up with a way to handle this. Azure SQL doesn't support accessing the Master DB, using Kill, or putting a database in single user mode. The code now checks the database version and if it is Azure it uses the more basic "drop database" command while still using the drop connections and single user mode method for regular SQL Server. Here is the commit. https://github.com/ClearMeasure/AliaSQL/blob/e03cbad1110c4fb68b31aab30a3b89387436b73d/source/AliaSQL.Core/Services/Impl/DatabaseDropper.cs

The nuget packages have been updated.

bladeradius commented

Thank you so much for quickly implementing this! I will update and test it out!

EliotJones commented

Hi @ericdc1, getting an issue with the rebuild command now where the database does not exist. I think the check for version number to fix this issue on azure now causes an exception:

Rebuild [DBNAME]_Original on .\[SERVER] using scripts from C:\Path\To\Scripts
Failure: Cannot open database "[DBNAME]_Original" requested by the login. The login failed.
Login failed for user '[DOMAIN]\[USERNAME]'.

This is when running schema_compare.ps1 so maybe it is intended behaviour?