Azure Shadow DB not getting drop

GurpreetGill

 

Hi

 

I am using SQL Change Automation in Visual Studio, Facing an issue, whenever i click on Refresh button ( for refreshing list of database object changes pending import in SCA window ) a shadow database been created and never get dropped.

 

My project having settings, as Source Database from Azure SQL Server and also  under project property Debug->Shadow database-> checked checkBox for "Skip Shadow database deployment when building inside Visual Studio"

 

Any clue how to handle this?

 

Thank you

Gill

Kendra_Little

Hi Gill,

The shadow database for SQL Change Automation doesn't get automatically dropped. If you manually wish to drop the database, it is OK as it will get recreated when it's needed. We don't automatically drop this because keeping it in place can speed up the process of verifying -- sometimes significantly. 

You do have a couple of options when it comes to maintaining the database, if you don't want to manually drop it:

• You could place the shadow database elsewhere -- even on a non-Azure SQL Database instance, if you wish. Please do note that this will only realistically work if you're not using TSQL which only compiles/successfully executes against Azure  SQL Database. (For example if you have a pre-script which creates External Tables.) To configure the shadow database location, you edit your user settings file.
• You could use the Serverless tier for your Shadow database if you still wish to use an Azure SQL Database for verification, but wish to not be billed for idle time. This can be done by editing the CreateDatabase.sql script in the Provisioning folder and specifying the SERVICE_OBJECTIVE you wish to use in the script.  Please note that when you use the Serverless tier, it is normal for SQL Server to throw an error when you wake up / resume a database.

Hope this helps,
Kendra