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.
82
questions
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 ...
- 201
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 ...
- 139
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 ...
- 7,973
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 ...
Randy
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, ...
- 7,973
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 ...
- 1
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 ...
- 33
-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: ...
- 417
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? ...
- 113
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: ...
- 101
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 ...
