I tried method given in this answer by Ramankant Dadhichi but deploying failed: https://dba.stackexchange.com/questions/244167/backup-a-database-from-azure-sql-managed-instance-and-restore-to-on-premise-sql?newreg=ec931412355c4730acfff21d2c7c78cd I have my database in Azure SQL Managed Instance. I extracted a dacpac using SSDT. But, now when I try to deploy the extracted file using SSMS, I get the following error: > Could not deploy package. > Error SQL0: The element [releaseengineer] cannot be deployed. This element contains state that cannot be recreated in the target database. > Error SQL0: The element [Reporter] cannot be deployed. This element contains state that cannot be recreated in the target database. (Microsoft.SqlServer.Dac)

Databases on Azure Managed Instance are encrypted by default. And these dbs have service-managed Transparent Data Encryption (TDE). One of our databases is a restored database from a .bak file, and hence its *Encryption Enabled* property was set to False. We want this database to be encrypted, as well. Can we enable this database just by setting *Encryption Enabled* to True?. Will it cause any issues? Are there any other steps that need to be performed to encrypt this database? We heard you have to create a Master Key etc. but for all our other databases that have service-managed TDE, we don't see any Master Key.

I have two SQL Managed Instances 1 - Primary Production 2 - Reporting / Analytic workloads Besides traditional SQL replication (transactional) what is the best way to get data in near real-time from the primary to the secondary server. The secondary is not used in HA or DR at all and only for reporting workloads that we don't want to run on the primary managed instance. My problem with transactional replication is certain tables in the primary instance are truncated which complicates replication. I also don't want to have to drop publications and generate snapshots, impacting the production applications. Log shipping would be an option but it disables the database on the secondary while restoring and isn't nearly real-time.   Really what I'm looking for is something like AG, which doesn't appear to be available in same region servers, unless I'm wrong. Researching Managed Instance Link seems perfect, except I'm not seeing that the source can be a Managed Instance, unless I'm wrong. The target database on the secondary can be read-only if needed.

I have a SQL Managed Instance in Azure with some blocking/deadlocking going on. This DB was on-premise and had all the scripts installed, so I uninstalled them and then installed the Azure specific scripts (Install-Azure.sql), downloaded fresh from Brent Ozar's website. All the scripts seem to work ok, except sp_blitzlock. SpBlitz indeed confirms "94 average deadlocks per day. To find them, run sp_BlitzLock." I am running it as a system administrator, and both main tables are blank. (when I ran it as my EntraID account, I got a permissions issue: Msg 50000, Level 11, State 1, Procedure sp_blitzlock, Line 335 [Batch Start Line 0] A session with the name system_health does not exist or is not currently active. I do not get any error when running it as an instance sysadmin. The data is just blank. It's possible there's no data, but I doubt it given what we've observed elsewhere and what sp_blitz confirmed. I downloaded the latest First Responder Kit, and am using sp_BlitzLock version 8.21 and sp_Blitz version Jul 1 2024 12:00AM. This is a SQL Managed Instance in Azure, instance version is 12.0.2000.8. I am just executing sp_blitzlock; in SSMS to run it. It may be worth pointing out we recently upgraded the service tier from General Purpose to Business Critical. That overall improved performance and locking, but I'm wondering if it triggered a new instance behind the scene which is why no deadlocking data appears. (Although why would it show up in sp_blitz?) Any thoughts?

How long will it take to restore 40GB full backup in Azure SQL database, Azure SQL Managed Instance?

We have a requirement to migrate around 800 GB of data from on-premises SQL Server (2012 and 2017) spread across multiple databases to Azure SQL MI. We have run the DMA on the source data and have got some issues (datatype not supported any more, coding style changes etc.). We are planning to use DMS to perform the data migration My questions are: 1. Which stage I should perform the remediation, in the source itself before the migration or in the target Azure SQL MI 2. Which option is preferred for the data migration

We moved a database from on-prem to Azure Managed Instance. But now we can't use External Tables, so we're taking a different approach. Is there a way to drop the external tables so I can use the object names for local tables? If I run the normal instruction DROP EXTERNAL TABLE [dbo].[MyExternalTable] It shows the error >Msg 102, Level 15, State 1, Line 1 > >Incorrect syntax near 'EXTERNAL'. If I run the command to drop like a normal table, I get a different error mentioning that this is an external table. DROP TABLE [dbo].[MyExternalTable] It shows the error >Msg 3705, Level 16, State 1, Line 1 > >Cannot use DROP TABLE with 'dbo.MyExternalTable' because 'dbo.MyExternalTable' is a EXTERNAL TABLE. Use DROP EXTERNAL TABLE. Is there any way around this?

I'm the DBA of one Azure SQL Managed Instance and one of our clients asked us if is possible automate a backup and restore in order to have a copy of one of the database before one of our ETL procces start. That's because they want to do all insertions and work on the copy database instead of the main production database and after finish everything and if data validations are OK, change database name. So I thought use a SQL Job just for backup a database to one blob storage and then restore using a backup file from a blob storage, the problem start when I realized that could not use a proxy account (related to the credential with access to blob storage)in the SQL Job and also there are some limitations in TSQL (WITH CREDENTIAL for example) Now with those restrictions I don't know how can automate this task. If I am not wrong, ELASTIC JOBS are only for Azure SQLDatabase Thanks in advance

What are different options to move database from "Azure sql server" to "Azure sql managed instance", Looks like below options are not possible 1) Azure migration service - Not supporting azure sql server as source 2) bacpac and use sqlpackage to import, this is not working and getting struck with no result only options i see is through Azure data factory with self hosted integration runtime is there any better options to move from "Azure sql server" to "Azure managed instance"

I have been looking at the managed instance link feature. Specifically, using the DAG feature to replicate a database on SQL Server 2019 Enterprise Edition, to SQL Managed Instance. I have hit a wall. - SQL Server 2019 does not support Azure AD logins, only SQL Server 2022 does at the time of writing, and we are not licensed for v2022 :( - Our end users (non-sysadmins) access the Azure SQL Managed Instance using their Azure AD logins only. - The replication of the database using managed instance link, from SQL 2019 to MI is one way only, so the SQL MI has only a read only copy of the database (so cannot add \ alter users there). - The database users on the read\write version of the DB on SQL 2019 are in Domain\User format, which then means that on the SQL MI those same users are not in the required format, user@domain.com, and there is the problem - users on the MI have no access to the read-only copy with their Azure AD logins. If I want to keep managed instance link, I can't see how to get around this apart from our end users using SQL logins (where I would then script out a created matching SID). Has anyone got any other thoughts that I might not have considered?

I'm deploying database schema-only (no data) changes to our Azure Sql Managed Instance via Azure DevOps task SqlAzureDacpacDeployment@1, and for some tables it is always dropping and re-creating computed columns and/or default constraints. Some of these tables are very large and to drop/re-add a computed column or default constraint is very time consuming. Anyone know why this is happening, and is there a way to prevent it? As mentioned, this is a schema-only deployment, there is no data being inserted. This is a snippet of what is being executed by the DevOps task:

"C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /Action:Publish 
    /SourceFile:"D:\a\1\drop\CCDatabase\bin\Release\CCDatabase.dacpac" /TargetServerName:"***" 
    /TargetDatabaseName:"***" /AccessToken:"********" /p:TreatVerificationErrorsAsWarnings=True 
    /p:DropObjectsNotInSource=True /p:DoNotDropObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials;LinkedServerLogins;LinkedServers;
    /p:BlockOnPossibleDataLoss=False

This is what I see in the log, and I've verified the columns/constraints are being dropped via Sql Profiler.

Dropping Default Constraint [VZ].[DF_Location_CreatedDateUtc]...
Dropping Default Constraint [VZ].[DF_Location_Hold_CreatedDateUtc]...
Dropping Default Constraint [VZ].[DF_Location_Stage_CreatedDateUtc]...
Altering Table [CRM].[QuarterlyGoal]...
Altering Table [D365].[CUSTCUSTOMERV3STAGING]...
Starting rebuilding table [SM].[TechnicianAvailability]...
Starting rebuilding table [VZ].[Location]...
Starting rebuilding table [VZ].[Location_Hold]...
Starting rebuilding table [VZ].[Location_Stage]...

Here is an example of a table that is being rebuilt: This one had the default constraint [DF_Location_Hold_CreatedDateUtc] dropped and the table data copy to a temp table, and then renamed back to original table name: Table definition:

CREATE TABLE [VZ].[Location_Hold]
(
    [VehicleNumber] VARCHAR(30) NOT NULL, 
    [UpdateUtc] DATETIME NULL, 
    [UpdateLocal] DATETIME NULL, 
    [Latitude] DECIMAL(9,6) NULL, 
    [Longitude] DECIMAL(9,6) NULL, 
    [GeoLocation] AS geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) +')', 4326) PERSISTED, 
    [SpeedKm] DECIMAL(5, 2) NULL, 
    [DisplayState] VARCHAR(20) NULL, 
    [CreatedDateUtc] DATETIME NULL CONSTRAINT [DF_Location_Hold_CreatedDateUtc] DEFAULT SYSUTCDATETIME(),
    CONSTRAINT [PK_Location_Hold] PRIMARY KEY ([VehicleNumber])
)
GO

This is what was captured in the profiler trace:

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [VZ].[tmp_ms_xx_Location_Hold] (
    [VehicleNumber]  VARCHAR (30)   NOT NULL,
    [UpdateUtc]      DATETIME       NULL,
    [UpdateLocal]    DATETIME       NULL,
    [Latitude]       DECIMAL (9, 6) NULL,
    [Longitude]      DECIMAL (9, 6) NULL,
    [GeoLocation]    AS             geography::STPointFromText('POINT(' + CAST ([Longitude] AS VARCHAR (20)) + ' ' + CAST ([Latitude] AS VARCHAR (20)) + ')', 4326) PERSISTED,
    [SpeedKm]        DECIMAL (5, 2) NULL,
    [DisplayState]   VARCHAR (20)   NULL,
    [CreatedDateUtc] DATETIME       CONSTRAINT [DF_Location_Hold_CreatedDateUtc] DEFAULT SYSUTCDATETIME() NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_Location_Hold1] PRIMARY KEY CLUSTERED ([VehicleNumber] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [VZ].[Location_Hold])
    BEGIN
        INSERT INTO [VZ].[tmp_ms_xx_Location_Hold] ([VehicleNumber], [UpdateUtc], [UpdateLocal], [Latitude], [Longitude], [SpeedKm], [DisplayState], [CreatedDateUtc])
        SELECT   [VehicleNumber],
                 [UpdateUtc],
                 [UpdateLocal],
                 [Latitude],
                 [Longitude],
                 [SpeedKm],
                 [DisplayState],
                 [CreatedDateUtc]
        FROM     [VZ].[Location_Hold]
        ORDER BY [VehicleNumber] ASC;
    END

DROP TABLE [VZ].[Location_Hold];

EXECUTE sp_rename N'[VZ].[tmp_ms_xx_Location_Hold]', N'Location_Hold';

EXECUTE sp_rename N'[VZ].[tmp_ms_xx_constraint_PK_Location_Hold1]', N'PK_Location_Hold', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This table simply had the computed column dropped and then re-added: Original table definition:

CREATE TABLE [CRM].[QuarterlyGoal]
(
	[QuarterlyGoalRecId]    INT             IDENTITY (1, 1) NOT NULL, 
    [StartDate]             DATE            NOT NULL, 
    [EndDate]               DATE            NOT NULL, 
    [UserId]                INT             NOT NULL, 
    [RevenueAmount]         DECIMAL(11, 2)  NOT NULL,
    [IsActiveQuarter]       AS
        (CASE WHEN (
            [StartDate] = CAST(GETDATE() AS DATE)) 
                THEN 1 
                ELSE 0 
        END),
    CONSTRAINT [PK_QuarterlyGoal] PRIMARY KEY CLUSTERED ([QuarterlyGoalRecId] ASC),
    CONSTRAINT [FK_QuarterlyGoal_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id])
);

This is what was in the profiler trace:

ALTER TABLE [CRM].[QuarterlyGoal] DROP COLUMN [IsActiveQuarter];
...
ALTER TABLE [CRM].[QuarterlyGoal]
    ADD [IsActiveQuarter] AS (CASE WHEN ([StartDate] = CAST (GETDATE() AS DATE)) THEN 1 ELSE 0 END);

What minimal permissions can be assigned to a database user so that user can read performance of indexes on tables/views in that database? I am using latest version of SQL Server and Azure SQL Managed Instance.

The company I work for put everything on Azure. We use SQL Server replication to move data from one big collection db server (Managed Instance) to our other database servers (say 20 in total). Every day we publish around 10 million (some days will be more, say 100 million plus) new/updated data in various databases. We can only have one publisher (my understanding), and fairly often, we see replication commands get built up, things slow down, and our DBAs will firefight to get things moving. In the database server where all the data is collected, we enabled CDC change tracking. I am wondering if I should create 10 or 20 Azure functions (C# code) to periodically pull changes from CDC. These Azure functions will then copy the changes to our 20 database servers (say all these servers need all these data). Would this be a reasonable alternative to replication? For me, each Azure function acts like a distributor, so we suddenly will have 10 or 20 distributors other than just one. I could use Azure Data Factory to do it, but it is way too expensive compared to Azure functions in my case. Is this a good idea or would we have any big issues?

I have Azure SQL MI - SQL agent job notification setup to send an email whenever SQL agent job fails. However, I noticed that job failure email notification does not show correct host name within email subject body. for example: Azure SQL MI host name is

-region-dev-sqlmi.random.database.windows.net

but notification email has different host name in email subject. for example, email comes with host name as

\\ABCDEF123GHI456\zyab456rdse333

Azure SQL MI- SQL agent job has some limitations so can't change properties. What could be the reason behind agent job notification display incorrect host name ?

I am trying to setup SQL agent job that will only execute if it is primary replica of Azure SQL MI of failover groups. I am trying to get primary replica using sys.dm_hadr_database_replica_states where is_primary_replica =1 but when I run it on SQL MI in different region , it also returns value 1. I am using dbo.fn_hadr_group_is_primary(@AGName)=1 function for on-prem so looking for something similar for Azure SQL MI.

I want to import a database from a .bacpac file to a SQL Server Managed Instance. I have read SqlPackage Import parameters and properties in the documentation. It says that there is a flag DatabaseMaximumSize=(INT32). I wanted to know if there's a limit that SqlPackage can support? For example, if I got 8 GB of RAM available, will SqlPackage be able to load larger .bacpac files than that, meaning it doesn't load it all to the memory? I'm not talking about runtime performance, but memory allocation. What happens if the bacpac is larger than my available RAM? There is for example dacfx in .NET Core which has an import method which receives a bacpac, but the option to tell it not to load everything to the memory doesn't work there.

should the multisubnetfailover=True parameter be added to connection strings to Azure failover groups?

I have a copy data activity that is moving data from a managed instance to a sql database. The flow of the process is: - truncate a staging table on the sql database as a distinct activity - call a stored procedure as a source in the copy activity - land the data in the staging table on the sql database in the copy activity There is a retry on the copy activity because we are having transient issues, and this is the guidance from Microsoft to handle these errors. My question(s) then is - if the data is being copied to the staging table, and this is interrupted by a transient error, and then the retry is called, will the staging table be empty because a transaction is rolled back? Or will some of the data from the first try still be there, and then I will end up with duplicate data? I have spent some time digging around, including https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview#resume-from-last-failed-run , but cannot find anything to clarify.

I have on-prem SSIS package with execute SQL task. Execute SQL task has been runnning without any issue with following parameter mapping when executing against on-prem SQL 2019 database.

On-prem parameter mapping
Variable Name  –   User::UserName
Direction      –   Input
Data Type      –   VARCHAR
Parameter Name –   NewParamterName
Parameter Size –   0

However, When i run the same execute sql task with the same parameter mapping against Azure SQL MI database then it fails with "parameter name is not recognized". SSIS execute sql task fails with following parameter mapping executing against Azure SQL MI database.

Variable Name  –   User::UserName
Direction      –   Input
Data Type      –   VARCHAR
Parameter Name –   NewParamterName
Parameter Size –   0

SSIS execute SQL task runs fine with following parameter mapping executing against Azure SQL MI database.

Variable Name  –   User::UserName
Direction      –   Input
Data Type      –   VARCHAR
Parameter Name –   0
Parameter Size –   -1

Why would execute sql task in on-prem ssis will fail with "parameter name is not recognized" against Azure SQL MI database but runs fine against on-prem database?

I am trying to connect to Azure SQL MI from on-prem SSIS package. However, I am unable to connect even with MS Oledb driver 18. I did udl connection test to Azure SQL MI from on-prem ssis server with MSOLEDBSQL driver with "ActiveDirectoryIntegrated" as Authentication and it worked.However, it does not work when trying to setup new connection manager with same connection property from visual studio. How can I connect to Azure SQL MI from on-prem SSIS using "ActiveDirectoryIntegrated" authentication?