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 (http://blog.sqlauthority.com)