Came across with one of the Azure SQL Database scenario where, couldn’t perform a manual failover for Active Geo-Replication from the Portal.
Notification appears in the Azure portal, as well as in the Activity Logs, with status ‘started’ but it doesn’t perform the failover request.
Getting an error, the operation cannot be performed because the geo-replication link is part of a Failover Group. You must remove the database from the group in order to individually terminate or failover.
Checked the Activity Logs for both primary/secondary db but not found any failed operation. …
Came across in a situation, where I was not able to connect to SQL Managed instance and the main reason was a trigger which was preventing me to logon to SQL managed instance
While connecting, I was getting below login failed “Changed database context to ‘master’.” Error.
Login failed for login <Login Name> due to trigger execution. Changed database context to ‘master’. Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
To resolve this issue, we had to connect to SQL instance using DAC (dedicated administrator connection)
Created a DAC connection followed Microsoft document: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators?view=sql-server-ver15
Added a NSG to…
I had encountered an issue where due to some reason, I had initiated the reboot of PostgreSQL server, but it never came back. It just stuck in ‘restarting’ state for whatever reason.
Now, since the infrastructure is owned by the Microsoft, I had to reach them to find what’s happening with my server, but in the meantime, we also have an option to restore the server with an off course different name. (Point in time restore from our main server)
The one issue of restoring server with a different name would be, we need to modify all our application connection…
SQL Server Managed Instance the database got deleted from the Azure portal but it’s still showing up in the SSMS (SQL Server Managed Instance)
One of my customer’s SQL Managed Instance (MI), has an automation where he was deleting database every night and then restoring it.
The automaton was running fine and it was serving the purpose, but one day after running the drop command for couple of DBs, the databases were dropped from the Azure portal, however, the databases were still visible in SSMS (SQL Server Managed Instance) hence the customer was not able to perform the restore operation.
How to create an user and logins in Azure SQL PaaS DB
Here is the requirement of one of my customer who would like to add SQL server logins to the server that have access to different databases within the server
To be more specific, the requirement was to create a separate login for each user and they would like to give an access to specific database.
Proposed two options here-
Option 1 : Create a login and provide access to specific Database.
Option 2 : If they want to provide access to specific DB, we can simply create…
SQL Managed Instance database properties window getting “Subquery returned more than 1 value” error message
While opening the database properties of one SQL MI, getting below following error message
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. \(.Net SqlClient Data Provider\)
This seems to be a known issue and could happens because there are multiple database_guids for the same database_name in msdb..backupset.
To resolve this, we need to clear the backup history in the backend by deleting the…
Azure SQL “Scale up and Scale down operation of database stuck”
We do have a Azure SQL Database and we did a schedule automation to resizes the DB core from 4 to 8 and back to 4 every day.
We build this automation, to Save some cost. During business pick time, when we gets the traffic, we scale up the DB so that it can take the load and post business hour, we would like to scale down our DB as no traffic to our application.
Our automation was working as expected but one day our automation failed and DB…
In case if you see the in SQL Server Managed instance error log having the following messages.
Cannot open database ‘model_msdb’ version 908. Upgrade the database to the latest version.
This is a benign message and can be safely ignored. This error is harmless and appear in the error log due to upgrades in the SQL Managed instance backend. We can ignore this error message.
Secondly, in on-premise SQL server or SQL on VM/IaaS, we have a control on SQL server services like SQL Agent or SQL Browser, however, in SQL server Managed instance we don’t have as such…