AzureSQL | Anything that is related to Azure SQL DB | Azure library

I have an Azure Devops build pipeline that is building and deploying to an existing AzureSql Database instance via the outputted .dacpac.

I would like to have the ability to run a script or execute API calls to create new AzureSql database instances based on that project. I have found the New-AzSqlDatabaseImport powershell cmdlet that ALMOST lets me do that, requiring a .bacpac rather than a .dacpac. I attempted to use the .dacpac and naturally the process failed.

1. Can I output a .bacpac from my SqlProj build process?
2. Alternatively is there a way to create a new database and have that database schema imported from the dacpac in a relatively smooth elegant fashion?

am trying to read data from Azure event hub and store this dataframe to Mysql table in spark streaming mode.

below is the my pyspark code

I am testing different strategies for a incoming breaking change. The problem is that each experiment would carry some costs in Azure. The data is huge, and can have some inconsistencies due to many years with fixes and transactions before I even knew the company.

I need to change a column in a table with million of records and dozens of indexes. This will have a big downtime.

ALTER TABLE X ALTER COLUMN A1 decimal(15, 4) --The original column is int

One of the initial ideas (Now I know this is not possible) is to have a secondary replica, do the changes there, and, when changes finish, swap primary with secondary... zero or almost zero downtime. I am referring to a "live", redundant replica, not just a "copy"

EDIT: Throwing new ideas:

1. Variations to what have been mentioned in one of the answers: Create a table replica (not the whole DB, just the table), apply a INSERT INTO... SELECT and swap the tables at the end of the process. Or... do the swap early to minimize downtime in trade of a delay during the post-addition of all records from the source

I have tried this, but takes AGES to complete. Also, some null and FK violations make the process to fail after processing for several hours. "Resuming" could be an option but it makes the process slower with each execution. Without some kind of "Resume", each failure have to be repeated from scratch

An acceptable improvement could be to IGNORE the errors (but create logs, of course) and apply fixes after migration. But afaik, AzureSql (nor SqlServer) doesn't offer an "ignore" option

1. Drop all indexes, constraints and dependencies to the column that needs to be modified, modify the column and apply all indexes, constraints and dependencies again.

Also tried this one. Some indexes take AGES to complete. But for now seems to be the best bet. There is a possible variation by applying ROW COMPRESSION before the datatype change, but I think it will not improve the real deal: index re-creation

1. Create a new column with the target datatype, copy the data from the source column, drop the old column and rename the new one. This strategy also requires to drop and regenerate indexes, so it will not offer lot of gain (if any) with regards #2.

A friend thought of a variation on this, which is to duplicate the needed indexes ONLINE for the column copy. In the meanwhile, trigger all changes on source column to the column copy.

For any of the mentioned strategies, some gain can be obtained by increasing the processing power. But, anyway, we consider to increase the power with any of the approaches, therefore this is common for all solutions

In my ASP.Net core web app, I connect to my DB using the following in startup

I am using pyspark on mac os and trying to read from AzureSQL and I get the below said error

Spark 2.4.6; Scala 2.11 ; java 1.8.0_251

I want to migrate upsert queries from Oracle DB to AzureSQL. Below shows an Oracle prepared statement that takes values from dual and does an upsert operation on the DUMMY table.

I'm building a webapp and keep encountering the following error when I restart the app and try to request a token using ITokenAcquisition.GetTokenForUserAsync

"One or more errors occurred. (IDW10502: An MsalUiRequiredException was thrown due to a challenge for the user. See https://aka.ms/ms-id-web/ca_incremental-consent. )"

I'm authenticating Users against Azure AD using Microsoft.Identity.Web.

When I inspect the token cache, it is empty after restarting the app. If the user was already signed in from an earlier session or because they are using a Domain joined device they seem to bypass the cache.

How do I force reauthentication or add a token to the cache?

Startup.cs

I'm new to AzureSQL.
I want to migrate data from Azure SQL periodically based on timestamp.

use DB1 GO
delete DB2.dbo.Orders where OrderDate < GETDATE()-90

The database DB1 and DB2 are in the same server.
As I currently understand, there is no SQL Server Agent when using AzureSQL.

Thanks

All,

We are migrating our database from SQLServer 2012 to AzureSQL PaaS DB. In lot of stored procedures, we have code as below:

USE ClaimDB1 GO

Delete ClaimDB2.dbo.Claims Where Claimdate < ‘12/01/2020’

The database ClaimDB1 and ClaimDB2 are in the same server.

Given that DML operations are not allowed on EXTERNAL TABLEs, what are the various patterns of accomplishing the same in the AzureSQL PaaS world?

Thanks, grajee