An annoying Bug in the Restore Dialog
Today, thanks to a customer, I discovered one of those annoying little things that can really drive you nuts.
Basically, they were trying to restore a backup using the SSMS Restore Database window and they kept getting “No backupset selected to be restored” whenever a backup file was selected.
You just had to select a file for restore and click OK…
… to be met with an error message in the Restore Database window:
The weird thing about it is that the backup file restored perfectly fine from a T-SQL script:
So it had to be something wrong with SSMS, but what?
Looking closer at the restore script, one thing stands out. Look at the file name:
Yep, there’s a leading whitespace in the file name. Could that be the source of the problem?
Let’s try again with the GUI in a slightly different way. This time I will copy the folder path from the “Backup File Location” textbox…
… and paste it directly in the “File name” textbox, right before the file name:
This time everything works as expected.
- This is a bug in SSMS: go on and vote this Connect item to have it fixed in a future version.
- Don’t use the GUI to restore a database.
- Don’t use the GUI at all.
Blame it on Connect
Some weeks ago I blogged about the discouraging signals coming from Connect and my post started a discussion that didn’t go very far. Instead it died quite soon: somebody commented the post and ranted about his Connect experience. I’m blogging again about Connect, but I don’t want to start a personal war against Microsoft: today I want to look at what happened from a new perspective.
What I find disappointing is a different aspect of the reactions from the SQL Server community, which made me think that maybe it’s not only Connect’s fault.
My post was in the headlines of SQL Server Central and was also included in the weekly links that Brent Ozar sends out with the Brent Ozar Unlimited newsletter, so it got a lot of views that day. Looking at my wordpress stats, I see that thousands of people read my post (to be fair, I can only say that they opened the page, I cannot tell whether they read the post or not) and some hundreds of people clicked the link to the original Connect item that started my rant.
Nobody upvoted the item. Yup, nobody.
Ok, very few people love the Data Collector and I rarely see it used in the wild, so, yes: I can understand how nobody cares about a bug in it. But, hey, it’s not my only Connect item that got no love from the community. Here’s another one, involving data corruption when using linked servers. See? Only 9 upvotes.
Here’s another one yet, that involves the setup program. No upvotes except mine.
What’s the point I want to drive? The voting system and the comments are the only way we have to improve the content on Connect. If we disregard the tools we have in our hands, there’s no use in complaining about the feedback system at all.
We need more community engagement
Filing our own items on Connect is not enough: we have to get involved in the platform to make our voice heard in more ways. When we find an item that we’d like to get fixed, we should definitely upvote it. At the same time, when we find items that are poorly described or are related to an issue that can be solved without bothering the support team, we should interact with the OP and ask for clarification or provide an alternative answer. When appropriate, we should also downvote poor questions.
Some popular Q&A sites like StackOverflow have built successful models based on this paradigm, like it or not. Moreover, the “points” system has proved successful at driving user engagement, which is something totally missing from Connect: you file your complaint and never come back.
Some online communities have moderators, who can play a fundamental role in the community. They can flag inappropriate items, edit and format questions and comments. The can also close questions or put them on hold. If part of the problem with Connect is the signal/noise ratio, more power to moderators is a possible answer.
Can PASS help?
In this post, Kevin Kline says that one of the ways that PASS should improve itself could be playing a better role in advocacy, telling Microsoft what are the features we really would like to see in SQL Server vNext and what are the bugs we really need to get fixed in the product. The idea is that Microsoft would (or at least should) listen more attentively to a whole community of users rather than to single individuals.
It’s a great idea and I think that PASS should really go for it. Unfortunately, something like that will never substitute Connect, because it’s a platform to collect feedback for all Microsoft products and not only for SQL Server. Moreover, how PASS is planning to gather the user feedback is still unclear: would it be using a voting system like Connect’s? How would that be different from Connect itself then?
Another thing that I think drives people away from Connect is its dreadful slowness. Connect is slow and nobody uses slow sites. It seems to be getting better lately, but we’re still not there. StackOverflow is probably using a fraction of Microsoft’s hardware and money to run all the StackExchange network at the speed of light. Part of its success is the responsiveness and Connect has a long way to go to catch up.
Connect has its issues, we all know it, but it’s not all Microsoft’s fault. The individual users can do something to improve the quality of the feedback and they definitely should. Everybody can start now! More votes means more attention, less votes means less love. Simple and straightforward.
On the other hand, the communities can contribute too. How they can contribute is not clear yet, but some communities (like PASS) have lots of people that volunteer and make their voice heard. It would really be a shame if that voice got lost.
Microsoft, please do your part. Users and communities want to contribute: help yourself by helping them and you won’t regret it. Responsiveness is the keyword here: we need a more responsive site and more responsive support engineers.
Who’s up to the challenge?
The big disconnect with Connect
A couple of years ago I blogged about a bug on the Data Collector that I couldn’t resolve but with an ugly workaround. At the end of that post, I stated that I wouldn’t have bothered filing the bug on Connect, due to prior discouraging results. Well, despite what I wrote there, I took the time to open a bug on Connect (the item can be found here), which was promptly closed as “won’t fix”.
Nothing new under the sun: “won’t fix” is the most likely answer on Connect, no matter how well you document your issue and how easy is the bug to reproduce. I really am sorry to say that, but it’s a widespread feeling that Connect has become totally pointless, if it ever had a point. The common feeling about Connect is that bugs are usually closed as “won’t fix” or “by design” without any further communication, while suggestions are completely disregarded.
How did we get here? Why is Microsoft spending money on a service that generates frustration on users? Where does this idiosyncrasy come from?
If I had to give Microsoft advice on how to improve Connect, I would focus on one simple point:
One of the things I see over and over on Connect is the lack of communication between users and support engineers. Once the item is closed, it’s closed (with few notable exceptions). You can ask for more information, add details to the item, do anything you can think of, but the engineers will stop responding. Period.
This means that there is no way to steer the engineer’s understanding of the bug: if (s)he read it wrong, (s)he won’t read it again.
I can understand that anybody with a Microsoft account can create bugs on Connect without having to pay for the time spent on the problem by the engineers: this can easily lead to a very low signal/noise rate, which is not sustainable. In other words, the support engineers seem to be flooded by an overwhelming amount of inaccurate submissions, which makes mining for noteworthy bugs an equally overwhelming task.
However, I think that the current workflow for closing bugs is too abrupt and a more reasonable workflow would at least require responding to the first comment received after the item is marked for closure.
How is CSS different?
In this particular case, I decided to conduct a small experiment and I opened the same exact bug with CSS. Needless to say that the outcome was totally different.
The bug was recognized as a bug, but this is not the main point: the biggest difference was the amount and the quality of communication with the support engineer. When you file a bug with CSS, a support engineer is assigned to your case and you can communicate with him/her directly by email. If the engineer needs more information on the case, (s)he will probably call you on the phone and ask for clarification. In our case, we also have a TAM (Technical Account Manager) that gets CC’ed to all emails between us and CSS.
Where does the difference lay? Just one: money.
If you want to contact the CSS, you have to pay for support. If the bug turns out to be a documented behavior instead, you pay for the time spent by the engineers working on it. This is totally absent from Connect, where everyone can file bugs without having to pay attention to what they do: there will be nothing to pay at the end of the day.
Is Connect really pointless?
One thing I discovered with my experiment may surprise you: CSS reads Connect items and if there is one matching your case, they will take it into account. This is really good news in my opinion and sheds a totally new light over Connect.
Another thing I discovered is that there is much more information behind a Connect item than it’s visible to users. When the engineers process items, they produce comments that are attached to the different workflow steps involved in the triage. Unfortunately, this is invisible to the end users, that are left with the minimal information that the engineers decide to share.
However, the important lesson learned from this experiment is that Connect may be frustrating for end users, but it is far from pointless: the information gathered while triaging bugs contributes to the quality of the paid support and, ultimately, to the quality of SQL Server itself. What still is unsatisfactory is the feedback to Connect users, that are getting more and more discouraged to file new items.
An appeal to Microsoft
Dear Microsoft, please, please, please improve the feedback on Connect: more feedback means less frustration for users that submit legitimate and reasonable bugs. Less frustration means more sensible feedback from your users, which in turn helps your CSS and improves the quality of SQL Server. Not everybody can open cases with CSS: this doesn’t mean that they are not contributing positevely to your product (and you know it), so please reward them with a better communication.
Do you need sysadmin rights to backup a database?
Looks like a silly question, doesn’t it? – Well, you would be surprised to know it’s not.
Obviously, you don’t need to be a sysadmin to simply issue a BACKUP statement. If you look up the BACKUP statement on BOL you’ll see in the “Security” section that
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
But there’s more to it than just permissions on the database itself: in order to complete successfully, the backup device must be accessible:
[…] SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. […]
While this statement sound sensible or even obvious when talking about file system devices, with other types of device it’s less obvious what “permissions” means. With other types of device I mean tapes and Virtual Backup Devices. Since probably nobody uses tapes directly anymore, basically I’m referring to Virtual Backup Devices.
VDI (Virtual Backup device Interface) is the standard API intended for use by third-party backup software vendors to perform backup operations. Basically, it allows an application to act as a storage device.
The VDI specification is available here (you just need the vbackup.chm help file contained in the self-extracting archive).
If you download and browse the documentation, under the “Security” topic, you will find a worrying statement:
The server connection for SQL Server that is used to issue the BACKUP or RESTORE commands must be logged in with the sysadmin fixed server role.
Wait, … what???!?!??!! Sysadmin???????
Sad but true, sysadmin is the only way to let an application take backups using the VDI API. There is no individual permission you can grant: it’s sysadmin or nothing.
Since most third-party backup sofwares rely on the VDI API, this looks like a serious security flaw: every SQL Server instance around the world that uses third-party backup utilities has a special sysadmin login used by the backup tool, or, even worse, the tool runs under the sa login.
In my opinion, this is an unacceptable limitation and I would like to see a better implementation in a future version, so I filed a suggestion on Connect.
If you agree with me, feel free to upvote and comment it.