Simple-Talk article

The folks at Simple-Talk were kind enough to give me an opportunity to write an article for them about Powershell, Reg-Gate SQLCompare & Snapshots.

The result is here, please let me know what you think by voting. Thanks!

Granting use of Graphical Execution Plan in SQL2005

I recently had the need to allow non-sysadmin, non-db_owner users to use the graphcal execution plan in SQL2005 on a query using tempdb (temp table). Assuming this worked just like "SET SHOWPLAN_* ON" I granted the user Showplan on the DB in question.

To test, I created a user with access to only the DB in question & tested "SET SHOWPLAN_TEXT ON", "SET SHOWPLAN_XML ON" & "SET SHOWPLAN_ALL ON" with no issues. However, when I tried to use the graphical execution plan ("Include Actual Execution Plan" option on the query) it failed with the below error...
Msg 262, Level 14, State 4, Line 29
SHOWPLAN permission denied in database 'tempdb'.

So, for a "normal" user who uses the graphical execution plan it seems my only option is to explicitly add them to tempdb & grant Showplan permissions (this would entail adding them to model as well so their perms persist on startup when tempdb is recreated). The other option would be to grant Showplan perms to guest on model/tempdb which given the official & observed (for example) vulnerabilities associated with SHOWPLAN seems a little crazy.

I don't normally knock MS around too much, but I really dislike both these options. Basically I'm being forced to choose between a gaping security hole (the guest scenario), a smaller security hole + a (to me) bad practice of granting a user access to a system DB that they really shouldn't need OR denying the perms to a user who's actually trying to do right.

Using Powershell to gather NIC info

In the course of troubleshooting a performance issue (in part caused by replication latency), we noticed that the NIC on our SQL server had been changed from Full Duplex to Half Duplex. Needless to say as soon as we switched back to Full Duplex our latency dropped back to what we'd come to expect. This was renforced by the behavior we observed when observing replication latency where it would be delivering transactions in bursts which lines up with the Half Duplex arcitecture of one-way communication.

As as outgrowth of this I thought it would be prudent to check the rest of our environment to make sure we didn't have any more mis-configured NICs. The majority of our Prod SQL Instances are clustered & have 2 NICs, one for the Heartbeat network & one for the Public Network. Per MS best practices for Clustered SQL the Heartbeat NIC is set to "100 Mb Full" & the Public NIC is set to "1000 Mb Full" so differentiating bewteen the NICs is important.

My first thought was to use the Win32_NetworkAdapter class which has properties for speed & duplex, unfortunantly looking at msdn it appears the properties haven't been implemented yet (this was verified in my testing). After viewing an excellent post on the topic by Hugo Peeters, I decided his method would work with a few small changes. My main gripe about this is the fact that MS forces us to (yet again) go to the registry for information that should be in WMI. Since the registry path changes by vendor (and in some cases model number) you'll need to modify your version of the script to account for the NIC vendors/models in your environment.

The script is here, I was not able to get it to show in a usable format on the page so we'll try this instead.