Azure DevOps + Azure SQL

GurpreetGill

Hi

I am using the RG's Azure DevOps Build extension for building the SCA project. I am trying to use my existing Azure SQL database as shadow database.

When running the pipeline using Azure SQL login credentials, it throws error as 

The shadow database cannot be deployed to an instance of Microsoft Azure SQL.

Any hint.

Thanks
Gill

Robyn

Hi Gill, 

Thanks for your post.

Your shadow database should be deployed to a local instance of SQL Server. The shadow database can't be in Azure because SQL Change Automation will need to drop and recreate it. You can specify a custom connection string for the shadow database in your user settings file.
(https://documentation.red-gate.com/sca/developing-databases/concepts/development-database/using-azure-to-host-your-development-database) 

Does this help?

GurpreetGill

Hi Robyn

Local DB, does not support the Azure SQL keywords like EXTERNAL PROVIDER

In PostDeployment Script i am trying to create user as below

CREATE USER AzureAD_USer FROM EXTERNAL PROVIDER
which failed the validation in case i use local instance of SQL Server.

So, i want to create Shadow Database in Azure. 

I will take care of Creating Shadow Database and Delete the same after build.

Please help

Thanks
Gill

Robyn

Unfortunately, the shadow database cannot be in Azure. You need to specify a custom connection string as per the instructions in the help centre article below:
https://documentation.red-gate.com/sca/developing-databases/concepts/development-database/using-azure-to-host-your-development-database 

GurpreetGill

I am very sad on this. No able to build my Azure SQL project.

That is a BIG BIG limitation, RG need to speed-up to match the market changes.

Thanks
Gill

Robyn

Our closest solution may help you. Take a look at https://documentation.red-gate.com/sca/developing-databases/concepts/verifying-projects/excluding-objects-from-script-verification

GurpreetGill

Thanks Robyn, but this solution only work for deployment not for the Build.

The build failed with error as Incorrect syntax near 'PROVIDER',  even i wrap the code with  variable IsShadowDeployment = 0

<div>IF '$(IsShadowDeployment)' = 0

</div><div>BEGIN

</div><div>    CREATE USER AzureAD_USer FROM EXTERNAL PROVIDER

</div><div>END

</div><div>ELSE

</div><div>BEGIN

</div><div>    PRINT 'NSkipping User creation because IsShadowDeployment=1';'

</div><div>END

</div>

Sergio R

The shadow database is only used in development. In the build step, you can specify an Azure Database as the Temporary Database and your build should work.
Please note that the build process will clean the Temporary Database, you should have a dedicated database for this.

GurpreetGill

Thanks Sergio, can you please help me what are the steps to make the Azure Database as Temporary Database.

When  i set the in Azure DevOps build pipeline it throw error.

The shadow database cannot be deployed to an instance of Microsoft Azure SQL.

Please help.


Thanks
Gill 

Sergio R

It looks like there's a bug that prevents using a SQL Azure database as a temporary database, I have created a bug report for this issue (ref. # SCA-2665).
Until this is fixed, to work around this issue I recommend:

- Exclude this user from script generation by using a filter: https://documentation.red-gate.com/sca/developing-databases/concepts/filtering

- Edit the migration scripts and delete the user there

GurpreetGill

Hi Sergio, any update

Thanks

Sergio R

This bug hasn't been fixed yet.
I will update this thread once this is fixed or there are other updates.
In the meantime, I would suggest that you try the workaround I have mentioned

Thank you for your patience,

GurpreetGill

Hi Sergio, any update

Thanks

Sergio R

Sorry, this behavior hasn't changed yet. I will keep you updated.

GurpreetGill

Hi Sergio, any update

Sergio R

Hi,

No, I am sorry, this hasn't been implemented yet.

Sergio R

Hi,

Starting with version 4.2.20133 it is now possible to use Shadow Database hosted in Azure SQL.

We would recommend only doing this when strictly necessary as in terms of performance it is not as a good as the other options.

To use a shadow on Azure you either need to use a new project or set up custom scripts provisioning for an existing project (https://documentation.red-gate.com/sca/developing-databases/concepts/provisioning).

Kind Regards,

GurpreetGill

Thank you Sergio R, I will check this and let you know.