I always enjoy reading through Microsoft Connect items and seeing what people smarter than me are looking for out of SQL Server. Inspired by this post from Mr Brent Ozar himself, I thought I’d make a note about one of the more interesting ones and the advantages/disadvantages as I see them.
A bit of background: I work for a software vendor that provides document information management systems (among other things). The servers that run this can be anything since Microsoft 2008 and are run on the customer’s hardware. The SQL Server may well be virtualised and it’s not unusual to see the server being a shared SQL/Web box (although this can be a nightmare to troubleshoot). This makes an interesting situation on each customer’s machine as they’re all unique, any triage tools need to be compatible back to SQL 2008 and versatile to catch as much information as possible. It also means that troubleshooting usually needs to be completed on the customer’s live site as we do not have anything locally that will mirror the specs exactly.
While browsing Microsoft connect one day I came across this connect item which seemed like a good idea. The premise is that there would be functionality to export and import some sort of settings file which, in my case, would allow for a certain amount of troubleshooting remotely by grabbing this file and installing onto a test server.
Advantages:
- Would definitely help troubleshooting.
- Could even post on Stack Overflow to help diagnose settings issues remotely.
- Would avoid needing to go through a long list of settings on a new install to ensure they’re all set correctly.
Disadvantages:
- Won’t allow for any troubleshooting of the data itself (although a .bak file would help there).
- This would be a nightmare at a server setting level, and some settings are based upon hardware configuration (think MAXDOP).
- What about tempdb files and backup locations, they’re going to need physical file locations passing through. This is going to require a certain level of GUI.
- It’s going to reduce the level of knowledge of people performing new installs, this seems like a short term time saver at the detriment of people’s knowledge overall.
This one has been closed by Microsoft as “Won’t Fix” but this doesn’t always mean a death knell for the suggestion. This particular one is a bit of a minefield though so I’m not expecting it to get implemented any time soon.
I’d be interested to know your thoughts around this.
Edit: As Paul mentioned in the comments, the connect item above is to do with SSMS settings, not server level. Because of this I’ve opened the connect item below, please feel free to upvote it if you believe it may be useful;