0

Hi i have a azure sql managed instance and when i execute the below query i get the error as

The operation cannot be performed on database "" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

Query :

alter database <dbname> set ENABLE_BROKER with ROLLBACK immediate

how do i solve this?

I tried the below

ALTER DATABASE [dbName] REMOVE SECONDARY ON SERVER [serverName]

but it gives me error as This feature is not supported through T-SQL on SQL Database Managed Instance.

ashish
  • 273
  • 1
  • 5
  • 16
  • Ref: https://stackoverflow.com/questions/41288542/operation-cannot-be-performed-on-database-because-it-is-involved-in-a-database-m – Leon Yue Jan 12 '21 at 07:39
  • What Service Tier is your Managed Instance? A Premium/Business Critical uses Availability Groups under the covers to provide redundancy which may be a factor here. You might try changing the tier to Standard/General Purpose to then retry the command and see if that works. – Martin Cairney Jan 12 '21 at 07:40
  • @MartinCairney I am in general purpose – ashish Jan 12 '21 at 07:42
  • Then check whether the issue identified by @Leon is the culprit – Martin Cairney Jan 12 '21 at 07:43
  • @LeonYue Yes geo replication is on. But how do i drop the replicated database? I have edited what i tried as per the link suggested by you – ashish Jan 12 '21 at 07:45
  • @MartinCairney Yes geo replication is on. But how do i drop the replicated database? I have edited what i tried as per the link suggested by Leon Yue – ashish Jan 12 '21 at 07:45
  • @LeonYue yes i tried with admin account and i got the error as This feature is not supported through T-SQL on SQL Database Managed Instance. – ashish Jan 12 '21 at 07:56
  • @ashish how about this statement: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/remove-a-secondary-replica-from-an-availability-group-sql-server?view=sql-server-ver15#TsqlProcedure? ALTER statement should be executed on the master database on which the primary database server is located. – Leon Yue Jan 12 '21 at 08:01
  • @LeonYue how do i get the availability group name? I dont see Always On High Availability node in object explorer – ashish Jan 12 '21 at 08:03
  • @ashish ref here: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/view-availability-group-properties-sql-server?view=sql-server-ver15 – Leon Yue Jan 12 '21 at 08:05
  • @LeonYue yes i saw the link but i dont see that node there. – ashish Jan 12 '21 at 08:10
  • @ashish I'm sorry I can't test this query for you because I don't have the same environment. Here's the query statement I found: https://dbtut.com/index.php/2019/01/21/how-to-list-availability-group-names-database-names-ips-ports-dns-names-in-sql-server/ – Leon Yue Jan 12 '21 at 08:15
  • Hi @ashish, did you get any progresses now? – Leon Yue Jan 13 '21 at 01:09

1 Answers1

0

You can use PowerShell to manage the Geo-Failover Group. I think you will need to remove the failover group first with Remove-AzSqlDatabaseInstanceFailoverGroup , make you changes and then recreate the Failover Group.

Martin Cairney
  • 1,714
  • 1
  • 6
  • 18
  • But how will this solve the issue of what i am trying to achieve that is setting the ENABLE_BROKER with ROLLBACK immediate by altering? – ashish Jan 12 '21 at 13:45
  • Once you remove the Failover Group you shouldn't be blocked from running the ALTER as per the initial error message – Martin Cairney Jan 12 '21 at 14:03