If you are a SQL Server developer or DBA, the chances are you are quite familiar working with SQL Server Management Studio. The longer you have been in the industry, more likely are you to take these tools for granted. Sometimes, the tool or our basic mistakes in understanding the tool can baffle the best of the best.
So, how many times have you seen something unexpected in SQL Server or with SQL Server Management Studio? If you are seasonal DBA, you might say – “many times”. This blog would share one such experience which I also had. Recently I was trying to configure Database Mirroring on a freshly created database. I was surprised to see that database mirroring option was missing in database properties. If you right click on the System database (master, model, msdb, tempdb) the options of “Mirroring” and “Transaction Log Shipping” will not be available but for me it was a freshly created user database.
I was under the impression that the option might not be available for database in simple recovery model but that was not the case. Irrespective of recovery model, the option should be available. I connected to the same instance remotely and to my surprise the options were available.
Above troubleshooting proved that the issue was not due to SQL Server Engine installation, but something incorrect with client tools installation.
I looked further and launched discovery report from “SQL Server Installation Center”. This can be launched by clicking “Installed SQL Server features discovery report”.
Here is the same report from a server where this option was not visible.
And here is the report from working server.
To fix the issue, I have run the SQL installation again and Added below on the server which has the problem.
After that I was able to get the option in SSMS. There are other option which might not be available in “basic” version of SSMS. Balmukund has blogged about one of such missing option over here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Hi Pinal,
I have SQL mirrored environment including Witness Server. Every week databases state change to “in recovery” mode & application doesn’t work in this case. To resolve the issue, I always restart the SQL services on both the SQL servers. It works perfectly for a weeks or so & again state changed to “in recovery”. I’ve checked the logs of databases but these are not heavy in size.
Also we are using m3.medium (1CPU & 3.7GB RAM) & m3.large (2CPU & 7.5GB RAM) instance type.
In Server event logs I found below error:-
“The Mirroring connection to TCP://Witness Server:5022” has timed out for database “Database name” after 10 seconds without a response. Check the service & network connection.
Can you please suggest on this, what could be the reason.
Instead of machine restart, have you tried restarting endpoints? There are some intermittent network issue?
Hi Pinal,
First of all Thank you for your reply.
I always restart SQL services on both the SQL servers instead of machine restart. Both the database server & witness are not in Domain.
For above issue I’ve changed the mirroring timeout value to 60 seconds but errors are still there in events.
Requesting you to please suggest.
Regards,
Sunny Chopra
looks like some issue with TCP layer on machines. You should contact Microsoft support team via support case.
Hello, I have different question but related to this topic. Suppose if both principal and mirror server is down can witness server takes all transaction and sync up once both server come online ?
If principal and mirror are down – there is no database available for transaction. Witness doesn’t have database at all. Only principal is a writeable copy of database.