Servernames

@@SERVERNAME. Sometimes, it isn't the same as the actual server's name. @@Servername is incorrect, it's wrong, it doesn't match. Argh!! It's easy enough to map though:


SELECT @@SERVERNAME AS[@@SERVERNAME],
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName

The problem arises, when someone decides to rename the Server after SQL has been installed. The new Servername will get recognised by SQL during startup: and, default or instance, it will happily allow you to connect to SQL using the new Servername afterwards.

But....

The sys.servers (or sysservers, if you are in SQL 2000) table still holds the old information, hence the erroneous information from @@SERVERNAME (if you run SELECT SERVERPROPERTY('ServerName') you will get the correct information).

So. You can change the sys.servers table information thus (having disabled replication AND dropped any remote logins first AND switched off mirroring AND scripted out all your maintenance plans AND deleted them...):

sp_dropserver old_servername\instancename
GO
sp_addserver new_servername\instancename, local
GO

If you don't disable replication or drop the remote logins, then there will be errors. Don't say you weren't warned. You may need, if you have linked servers, to re-add those. Client aliases using named pipes will also need to be checked and corrected.

If you didn't script out and delete your maintenance plans before you got going, there is now (as of SQL 2008) a script to deal with the fact that they cannot be changed nor deleted....

Which all goes to show that, really, once you have decided what the Server is going to be called, you should stick with it. Avoid calling servers the same as the previous server, but with 'New' on the end. Or calling anything 'Temp'. Or using a default name. SQL2000Win2003 might be descriptive, but it won't help if you don't identify which one of the four servers with that name it really is.... Particularly, avoid having a server named, say, George, calling the new server, NewGeorge, and then creating a third server to replace NewGeorge which is, confusingly, called George, but isn't the same server as the Original George was. Also confusing is a server which was called Mabel, but is now called Sybil, while another server used to be called Sybil but is now called Fiona, while there still exists a server which has been called Mabel all along.

In other words. THINK.

It's the little things

Me, I like to faff about with my resultsets in Excel. This probably makes me incredibly old fashioned, and I'm sure various of my friends would call me a flat-earther. Tant pis.

However, I got all overexcited just now. Instead of highlighting the resultset, copying it using ctrl+c and pasting it into the spreadsheet at row #2, and then swearing greatly while I transferred the column names individually, I right clicked on the resultset after highlighting it.

Now, I've been using SSMS for SQL 2008 for, oh, six months. How did I fail to notice that there was an option to "Copy with Headers"?! If this has been an option in SQL 2005, well, please don't tell me. I'd rather not know that I missed it for, oh, two years.

syspolicy_purge_history failed on the cluster

This one is apparently a bug. It's a job that SQL creates when it installs SQL 2008, and it will perpetually error on the "Erase Phantom System Health Records" step, owing to the fact that it will insist on calling the method on the node name, rather than on the virtual servername

viz

"MessageExecuted as user: Domain\AgentServiceAccount. A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\SQLClusterInstanceNameNodeName\Default).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Method invocation failed because [Microsoft.SqlServer.Management.PowerShell.Extensions.Machine] doesn't contain a method named 'EraseSystemHealthPhantomRecords'. '. Process Exit Code -1. The step failed."

The way to deal with it is to change the script a bit:

"Get-Item SQLSERVER:\SQLPolicy\VirtualServerName\InstanceName".

And, if it's the default instance, or there is only one SQL instance on the cluster, go for

Get-Item SQLSERVER:\SQLPolicy\SQLClusterInstanceName\default"

In other words, just get rid of the Node information, and all will be fine

KB article.

Not that I have quite worked out what this job does: keeps things tidy if you have policy management set up, I imagine. Which is useful.