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
45 views

How automate backup and restore in Azure SQL Managed Instance?

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 ...
SakZepelin's user avatar
0 votes
0 answers
19 views

Local SQL to Managed Cloud SQL via ADF. Copy Data throughput fell off a cliff

I have an on-site mini PC acting as a data extraction and replication SQL server that copies data nightly from the ERP production database that's running some legacy linux architecture. The copying is ...
funkyman50's user avatar
0 votes
0 answers
19 views

Is Microsoft SQL Managed Instance maximum backup file size still limited to 195 GB?

Is this 195 GB maximum strip size still accurate? https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=azuresqldb-mi-current#limitations-for-sql-managed-instance. Microsoft ...
Rosdi Kasim's user avatar
0 votes
0 answers
24 views

Huge storage increase when executing a alter table query in Managed Instance Database

I have a table which consist of more than 2 million records in a azure managed instance database. When I'm trying to add columns to the table using alter command, storage space is increasing ...
SLN Perera's user avatar
0 votes
0 answers
31 views

Permissions for setting a baseline for SQL vulnerability assessment rule

I'm getting a SQL vulnerability error discovered by Microsoft Defender for Cloud. The error points to the following Vulnerability Assessment rule. But when I try to set a baseline (recommended by this ...
nam's user avatar
  • 291
3 votes
2 answers
116 views

How do I resolve this python runtime error in Azure SQL Managed Instances?

We recently setup a new managed instance through the program that allows you to try it for free. When trying to run the following code from Microsoft's documentation: EXECUTE ...
Joe Obbish's user avatar
  • 32.1k
1 vote
0 answers
150 views

Azure SQL Managed Instance Gives 'target principal name is incorrect' when using DNS alias

We have a Azure SQL Managed Instance setup. All connects okay, using SQL Authentication using SSMS using it's internal hostname (e.g mysvr.database.windows.net). We have a DNS alias configured, and ...
Jayden's user avatar
  • 141
0 votes
1 answer
53 views

Looking for best option(s) to have near real-time data synced from SQL Managed Instance to another SQL Managed Instance

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 ...
Cody's user avatar
  • 11
0 votes
0 answers
54 views

How to check with TSQL if I'm connected to an Azure Managed Instance in a Failover Group?

How can I check with TSQL if I am connected to an Azure Managed Instance that's in a Failover Group? For Always-On we can use this: SELECT SERVERPROPERTY('IsHadrEnabled') But in the case of Azure ...
Mike's user avatar
  • 658
0 votes
0 answers
56 views

How to get Email Alerts for Errors severity 17-25 on SQL Managed Instance?

Discovered that In Azure SQL Managed Instance, there's no Alerts feature under SQL Server Agent. According to documentation, Alerts feature for SQL Agent is not yet ready for MI. Hopefully it will be ...
Aleksey Vitsko's user avatar
0 votes
0 answers
55 views

link feature - Azure SQL Managed Instance multiple databases

Is it possible to replicate two databases in the same DAG to an Azure MI? Or is it possible to configure two DAG (single db) pointing to the same Azure SQL MI? https://learn.microsoft.com/en-us/azure/...
MBuschi's user avatar
  • 4,440
0 votes
0 answers
24 views

Using CDC on Read only Secondary Managed instance

We are planning to migrate our on-premises database to Azure Managed instance. Is it possible to use Read Only secondary of the managed instance for CDC.
Anoop Agarwal's user avatar
0 votes
2 answers
132 views

In SQL Server, if I have two otherwise identical stored procedures with different names, will they use the same execution plan?

Azure SQL Managed Instance. Here is the scenario. I have a stored procedure that produces a count of rows that match some criteria. But, depending on the Customer that is chosen, the statistics can be ...
CB_Ron's user avatar
  • 291
0 votes
0 answers
100 views

Error while using linked server in job. The login is from an untrusted domain and cannot be used with Integrated authentication

In SQL managed instance, I have a job. In job procedure, it is using linked server which is also a SQL managed instance. With test login I can read the data using Linked server. I have created the job ...
Narendra's user avatar
1 vote
1 answer
66 views

Can I develop a Dashboard Report for Data Allocated vs Max Storage for my Azure Database?

I have an Azure SQL Server Database - Managed Instance I'd like to monitor. Here is the widget that Azure shows me on the database summary page. I'd like to put this widget, or at least the ...
Yossi Geretz's user avatar
0 votes
1 answer
64 views

How can I copy a database in Azure SQL Server to Azure SQL Managed Instance in different Azure Subscription

I created an Azure SQL managed instance using terraform in Azure Subscription A. I am trying to copy the databases in an existing Azure SQL Server in Subscription B to the SQL Managed Instances I ...
achustle's user avatar
0 votes
1 answer
103 views

Azure SQL Backup Restore

How long will it take to restore 40GB full backup in Azure SQL database, Azure SQL Managed Instance?
SOUser's user avatar
  • 13
0 votes
2 answers
281 views

What is the quickest and most efficient way to remove an in-memory filegroup from a large SQL Server 2019 database?

I have a database with an in-memory filegroup that I want to remove (I won't go into many details why, but I want to move the database to another place without this filegroup). I'm trying to move the ...
dcop7's user avatar
  • 29
0 votes
1 answer
86 views

Do I need a clustered index on a table with only few columns?

Table: Address ( AddressID bigint, Street VARCHAR(150), City VARCHAR(200), State CHAR(2), State VARCHAR(5) ) Table size: 5GB, 60 million rows. AdressID is sequential. Remark: 95-98% of ...
nam's user avatar
  • 291
1 vote
2 answers
83 views

How to interpret the stats of index usage? [closed]

Question: How do we interpret the stats shown in the diagram below? The stats seem to indicate bad index designing, how can it be improved. I realize this a sort of broad question since it depends on ...
nam's user avatar
  • 291
3 votes
2 answers
421 views

How to gather Index usage information

Following query correctly outputs number of seeks, scans, etc. I have question on understanding some rows in the output. In some output rows of the query, the index name is shown as NULL and the ...
nam's user avatar
  • 291
1 vote
1 answer
60 views

Why permission error when using T-SQL but not when using SSMS

When trying to run the following to create a Server Role, I get permission error shown below. But I can successfully create the same role using SSMS. Question: Why? CREATE SERVER ROLE TestRole ...
nam's user avatar
  • 291
1 vote
1 answer
228 views

SQL Server to Managed Instance - New managed instance link feature

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. ...
NoNoYepNo's user avatar
0 votes
0 answers
450 views

How to move Azure SQL managed instance cross tenants?

How to move Azure SQL managed instance cross tenants? I have some critical DBs hosted by Tenant_A's SQL MI A which need to be moved to Tenant_B's SQL MI B. They are all TDE encrypted by CMK. Is there ...
DBALUKE HUANG's user avatar
0 votes
0 answers
62 views

Get product version if an Azure SQL managed Instance

Our SQL Azure managed instance was SQL2019 (15), we took a Prod backup to restore to the DEV and it errors "The database was backed up on a server running version 16.00.4001" Yes, they ...
user avatar
0 votes
1 answer
67 views

Why don't I get Index Seek for my non-clustered index

Version: Microsoft SQL Azure (RTM) - 12.0.2000.8 (Azure Managed Instance) I'm working with a table (ActivityAttributes) that has ~190Mn rows. I've created an Index as below. CREATE NONCLUSTERED ...
Amila's user avatar
  • 1
0 votes
1 answer
357 views

Log shipping from SQL Server Managed Instance to a database on a Virtual Machine, is it possible?

We are looking into Managed Instances as a step forward from Availability Groups on Azure VMs. One of the features we are using now is log shipping from AG primary to an instance that is not a member ...
TheNixon's user avatar
  • 361
0 votes
1 answer
198 views

Minimal permissions to read performance of indexes in SQL server

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 ...
nam's user avatar
  • 291
0 votes
1 answer
197 views

is it a good idea to replace azure sql MI replication with CDC and ADF or Azure Function?

The company I work for put everything on azure and we use sql server replication to move data from one big collection db server (a azure sql MI) to our other database servers (say 20 in total). Every ...
jerry xu's user avatar
0 votes
1 answer
1k views

How to set Password Expiration Date in SQL Server

As shown in the screenshot below, we don't see an option to set password expiration date. Question: How can we set a password expiration date of an existing login using SSMS or t-sql. We are using ...
nam's user avatar
  • 291
0 votes
1 answer
200 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 ...
nam's user avatar
  • 291
3 votes
2 answers
2k 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 ...
nam's user avatar
  • 291
0 votes
1 answer
275 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 ...
Ashok's user avatar
  • 1
0 votes
1 answer
493 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:...
nam's user avatar
  • 291
0 votes
1 answer
290 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 ...
nam's user avatar
  • 291
0 votes
2 answers
320 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 ...
nam's user avatar
  • 291
0 votes
1 answer
2k 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 ...
nam's user avatar
  • 291
2 votes
1 answer
870 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] ...
nam's user avatar
  • 291
-1 votes
1 answer
257 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 ...
nam's user avatar
  • 291
0 votes
1 answer
560 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 ...
nam's user avatar
  • 291
2 votes
2 answers
5k 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 ...
nam's user avatar
  • 291
1 vote
2 answers
372 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: ...
nam's user avatar
  • 291
0 votes
1 answer
663 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 ...
Sranda's user avatar
  • 360
1 vote
1 answer
242 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 ...
Sajit Gangadharan's user avatar
1 vote
1 answer
38 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 ...
simon coleman's user avatar
1 vote
2 answers
358 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 ;...
Benjamin's user avatar
  • 231
0 votes
0 answers
452 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 ...
Tushar's user avatar
  • 1
1 vote
0 answers
251 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 ...
nathjc's user avatar
  • 11
0 votes
1 answer
874 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 ...
Schmocken's user avatar
  • 101
1 vote
1 answer
142 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 ...
Luis Cazares's user avatar