Questions tagged [azure-sql-managed-instance]

SQL Server Managed instance, a deployment option of Azure SQL Database providing compatibility with the latest SQL Server on-premises Database Engine with PaaS features and capabilities.

Filter by
Sorted by
Tagged with
0 votes
1 answer
22 views

Enabling transparent data encryption on Azure Managed Database

Databases on Azure Managed Instance are encrypted by default. And these dbs have Service-managed Transparent data encryption (TDE). One of our database is a restored database from a .bak file, and ...
  • 181
1 vote
2 answers
91 views

Unable to open database properties window in SSMS

I am using Azure SQL MI. Using SSMS, I backed up an SQL db to an Azure container. After that I am unable to open the Properties windows of that database using SSMS. When I right click on the database ...
  • 181
0 votes
1 answer
41 views

Migrate SQL Server 2012 databases on premises to Azure SQL

I have SQL Server 2012 databases on premises and I would like to migrate these to Azure SQL However the Application Vendor has not certified their Application on Azure SQL but the following versions ...
  • 1
0 votes
1 answer
57 views

Azure SQL Vulnerability assessment error

In our SQL Managed Instance database, we created a user-defined database role and added 20 users to it. Now, during scanning by Microsoft Defender for Cloud, we are getting the following error/warning:...
  • 181
0 votes
1 answer
64 views

How to interpret output of query store in sql server

I ran the following query shown in the image below. I understand the left pane of the query store below that says query (with id 1962) took about 151000 ms to run. Question: But I don't understand the ...
  • 181
0 votes
1 answer
82 views

SQL database is taking up 1.5 TB but only using about 1 TB

SQL Server is almost 98% full. Its one database is taking up 1.5 TB but only using about 1 TB. Can we get any of that space back, if so, how? We are using Azure SQL Managed Instance. File Growth ...
  • 181
0 votes
1 answer
65 views

GRANT ALTER to role vs. GRANT ALTER ON SCHEMA

Suppose you create a user defined database role as follows: CREATE ROLE TestRole AUTHORIZATION dbo Question: Which of the following two permissions are more restrictive? Or, do the both statements ...
  • 181
1 vote
1 answer
93 views

How to add Azure AD user to a Database role

Question: What may be a cause of the error shown below. The user running the code below is a member of db_securityadmin database role: ALTER SERVER ROLE db_ddladmin ADD MEMBER [myDomainName.com\Juan] ...
  • 181
-1 votes
1 answer
41 views

What permissions are needed to create User Defined Database role

Question: If you are not a db_owner or a sysadmin, what minimal permissions are needed to perform the following tasks: Create user defined database role, say, MyDbRole List item all users inside ...
  • 181
0 votes
1 answer
78 views

REVOKE CREATE TABLE is not removing the permission

I gave the following permission to user1 so user1 can create tables on database1: GRANT CREATE TABLE TO user1 WITH GRANT OPTION I verified that user1 can create table. Then I ran the following ...
  • 181
2 votes
2 answers
341 views

Permission to grant SELECT, UPDATE, DELETE, ALTER on all tables

In SQL Server 2019, I have created a user defined database role MyDbRole. Now I want to grant following permissions to MyDbRole for all tables in the database. CREATE, SELECT, UPDATE, DELETE, ALTER ...
  • 181
0 votes
1 answer
102 views

SQL Server tempdb files growth increment size

In our Azure SQL Managed Instance, we have 12 tempdb data files each of size 11GB. The Log file growth value is set to a max default size of 2TB. The growth value of each data file is 32GB. Question: ...
  • 181
0 votes
1 answer
152 views

Migrate databases between Azure managed Instance and Azure SQL VM

(scroll to the end of the post to see latest development) There's a necessity for migration of 3 databases from Azure MI to Azure SQL VM (OS 2022, SQL 2019 standard). Each database has 500GB+. Each ...
  • 115
1 vote
1 answer
50 views

Prevention of Data loss when switching from Primary to secondary database in Azure SQL during failover test

During the failover test in azure database from Primary to secondary. I see that everytime there is a loss of 3 seconds worth data during the switch. I want to minimise this data loss. The azure ...
1 vote
1 answer
30 views

Sustained substantial increase in Logical Writes for all plans & dbs on an Azure Managed Instance

We habe a managed instance with a 12 or so dbs supporting the same application across 2x customers. This has been running find on a 4-core sql managed instance, but at almost exactly midnight local ...
1 vote
2 answers
110 views

Azure SQL - altering MAX DOP value at Database scope level is not taking place

I am trying to change the MAX DOP value for my Azure Managed SQL which currently is set to ZERO. Here is the script I am running: USE [my-sql-db] GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =5 ;...
  • 221
0 votes
0 answers
171 views

Migration SQL managed Instance DB to another tenant SQL MI

I am stuck with a SQL migration between 2 tenants. Any help will be really appreciated.  There is one SQL Server managed instance TDE enabled is sitting in one tenant and we have to move the same to a ...
  • 1
1 vote
0 answers
43 views

High usage on Azure log analytics

I have a relatively simple web app set up with a SQL database, and we have log analytics. We have "Auditing" turned on for the SQL server, and for some reason I'm seeing 2.8GB of usage a day ...
  • 11
0 votes
1 answer
326 views

In ADF, are copy data activities wrapped in transactions?

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 ...
  • 101
1 vote
0 answers
81 views

Drop external table in Azure Managed Instance

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 ...
1 vote
1 answer
108 views

Azure Database Performance Throttling - Use Replication or?

We keep a "rather large" azure database where we load all of our company data into every night. It uses temporal tables to keep track of changes to the data Now we want to start reading this ...
  • 113
3 votes
1 answer
144 views

SQL Server Data Tools -- Stopped connecting to Azure SQL DB

All updates are installed on VS 2017 (15.9.47) and 2019 (16.11.13) I am trying to perform a data comparison between two DB's via Microsoft’s tool , On Prem and Azure SQL managed instance Everything ...
  • 31
-2 votes
1 answer
62 views

What are the features that Azure VM based SQL server supports and the managed instance doesn't support?

I'm deciding on whether to install SQL server on azure VM versus configuring a managed instance. I understand that the following features are not supported by managed instance: SSIS SSAS SSRS Manual ...
  • 2,950
0 votes
0 answers
83 views

Azure Key Vault Backup/Restore Across Subscriptions

I have 2 Azure SQL Managed Instances, each on a separate subscription. I am trying to see if I can take an Azure Key Vault Key used for TDE in one subscription and back it up to then restore to a Key ...
1 vote
1 answer
35 views

Azure MI - clustered and nonclustered indexes with the same columns - different execution plans

I have a problem to understand why I am getting 2 different execution plans based on whether clustered index is involved or not. It's happening in Azure managed instance, I have no possibility to test ...
  • 115
0 votes
0 answers
66 views

Full Text Index Crawler - MAXDOP - FT_IFTS_RWLOCK

We ran into a rather interesting situation regarding our Server and it's Full Text Catalog yesterday. I'm wondering if the experts here can shed some light on it as I've not been able to find ...
0 votes
0 answers
57 views

Is there a way for a SQL Server Login to gain permissions from multiple groups?

I have a security solution that I am trying to build. But I am unable to get it to a state that I am happy with. I am hoping someone will be able to shed some light on the correct path or deny the ...
user avatar
4 votes
0 answers
224 views

Azure SQL Managed Instance - Reboots - places to look?

We have been seeing what appears to be reboots on our SQL Managed Instances (Standard, Failover Group). One node (the current primary, USEast) has been apparently rebooting for the last 24 hours, ...
0 votes
1 answer
555 views

Row-Level Security with Azure AD Groups on Azure SQL Managed Instance

I have an Azure SQL Managed Instance on which I need to implement Row-Level Security on some tables to filter the data queried by some users. All my users authenticate with Azure Active Directory ...
1 vote
0 answers
137 views

Data migration assistant showing breaking changes/ behaviour changes for SQL 2019 but not for Azure SQL Managed instance

I am trying to assess the potential issues while migrating a SQL Server 2012 on-prem database to Azure, with SQL on Azure VM and Azure SQL Managed instance as targets. For SQL on Azure VM, DMA is ...
  • 111
0 votes
0 answers
186 views

Validation error while trying to migrate on-prem SQL Database to Azure SQL managed instance using Azure DMS

I have successfully created Azure Database Migration Project under Azure Database Migration Service during the last phase of the project I am getting validation error No valid full backup found for ...
0 votes
1 answer
80 views

On-premises SQL to Azure SQL MI Migration

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 ...
0 votes
0 answers
46 views

tsql do update queries that join other tables lock the joined tables also?

Azure SQL Managed Instance. If I am updating a temp table, and joining to another table or tables, do the joined tables also get locked, even though the update statement is only updating the temp ...
  • 249
1 vote
0 answers
137 views

Extended events error in Azure Managed instance

I have setup extended events on Managed instance. Below are the steps followed: create a master key Create a credential for Azure Blob storage with shared access token Create the extended event and ...
-1 votes
1 answer
39 views

What is the equivalent of on prem ha dr solutions in the azure cloud PaaS and SaaS?

On prem ha dr solutions are: Log shipping Mirroring Replication FCI AG What is the equivalent of each of the above in the azure cloud PaaS (azure sql managed instance) and SaaS (azure sql ...
  • 2,950
0 votes
1 answer
218 views

Shrink the data file on Azure SQL MI

I have a 600GB DB with 400GB free space in the data file on Azure SQL MI. I am planning to shrink it and save the storage, but it's a critical DB with the user sessions. There are two plans: Plan A: ...
1 vote
1 answer
384 views

Configuring Azure SQL Server and SQL DB instance with MFA/2FA for On-premise AD DS synched account

I've synched my OnPremise AD DS users into Azure using Azure AD connect. How can I enable the user of my Azure SQL Server and Azure SQL DB instance to use MFA/2FA when connecting with SSMS using the ...
1 vote
0 answers
39 views

Azure SQL DB and SQL MI Security Concerns

I have 7 different SQL Managed Instances, which were newly migrated over from SQL VM. I also have 7 different Azure SQL Databases, which were also newly created. I want to make sure that I completely ...
1 vote
2 answers
3k views

Maintenance Plan folder missing in SQL Managed Instance (via VM)

Accessing our new SQL Managed Instance via an Azure VM/SSMS, the Maintenance Plan folder is missing from the Object Explorer pane. Reading other posts (that all relate to other versions of SQL Server)...
  • 778
0 votes
1 answer
552 views

Changes to server configuration option remote access are not supported in SQL Database Managed Instances

Having just set up our new SQL Server Managed Instance, restored a sample database for testing, and run Azure's vulnerability assessment, it produces this high risk finding: VA2120 - Features that ...
  • 778
1 vote
0 answers
263 views

Cannot use the special principal 'dbo' when addressing SQLMI risk VA2108

We've just set up a new SQL Managed Instance on Azure, and have restored a database to it for testing. After running an initial Azure Vulnerability Assessment, the results include this: VA2108 - ...
  • 778
1 vote
2 answers
115 views

What is the best practice location for Memorize Optimization (OLTP) files?

Best practices for log and mdf files is to split across drives (e.g. G:\SQL Data & I:\SQL Logs). What are the best practices for memory optimization files? Should they reside on their own drive? ...
0 votes
0 answers
40 views

Azure SQL MI PITR got failed

We were trying to restore a database on the same SQL MI with older date and different name but it got failed and we did get any information from the error message. This is also causing issue while ...
3 votes
1 answer
538 views

Azure Elastic Pool - is it supported for MySQL?

I know that Elastic Pool in Azure is supported for SQL Server. But I could not find any information if it is supported for MySQL. I could not find any information in documentation at all what types of ...
  • 645
1 vote
1 answer
691 views

Can we configure logins to only connect to read-only replicas in Azure SQL Database automatically?

For Read-Only routing, rather than explicitly using "ApplicationIntent=ReadOnly" into our application connection string or for individual users to specify it in additional properties, can ...
  • 131
1 vote
1 answer
1k views

What is the SQL Server equivalent for Oracle character set AL32UTF8?

While porting data from oracle to Azure sql database I am facing the following issue where, empty data in Oracle is converted as ➞➞➞ in SQL Server Pic1: Data as in Oracle DB; Pic2: Data as in Sql ...
  • 131
0 votes
1 answer
489 views

How to query an Extended Events target file on a SQL Azure Managed Instance by using T-SQL?

I'm setting up an Extended Events trace in a Managed Instance (NOT Azure SQL DB) for the first time. These are the steps I've carefully followed: Created a Master key on my Database. Created a ...
  • 422
1 vote
0 answers
81 views

SQL Managed Instance Restore to lower environments

I was trying to find an article or design pattern to fit my use case but I cannot find one. My use case: I have a Production Subscription with a SQL Managed Instance that holds all production data. ...
0 votes
0 answers
435 views

SQL Server Snapshot replication - Failing to start the Publisher snapshot agent

I am trying to set up a Snapshot replication using an Azure SQL Managed Instance. While trying to see the Snapshot Agent status, I see this error. Failed to connect to Azure Storage '' with OS error: ...
0 votes
1 answer
53 views

Grant permissions to allow user to check if session is on read replica or not

Azure SQL Managed Instances offer access to read replicas from the availability group (in specific tiers at least). I am writing a test script for our application to connect to read/write and read ...