Recently we renamed a server (VM Guest) that was hosting a stand-alone SQL Server, default instance. There always seems to be something I forget when this happens and this was no exception.
It seems this time I forgot about a linked server; in our environment we create a linked server called LocalHost, which loops back to itself (this is for a monitoring program that monitors long running jobs); when the server was renamed we started seeing lots of NT Authority\Anonymous Login failures in the SQL Error Log, and the process that monitors long running jobs was failing.
A prayer never hurts when renaming a box hosting SQL Server, and I'm the kind of guy who can be inspired by Britney even with the added weight!
So I decided to put this little blurp out there so I'd have a checklist to verify things.
It seems most of this is a rehash of something I found here: http://msdn.microsoft.com/en-us/library/ms143799.aspx , But I was too lazy too read down to the linked servers portion ! Sometimes articles on MSDN and other peoples blog posts I save the the links to have a way of disappearing, so I wanted to create my own blog post, just for selfish reason that I need my own library of "checklists" and "how to".
Checklist for renaming a sql server:
1. Rename the server (this is the sql instance, the server I will defer to the proper operational teams to handle the necessary rename, active directory, dns stuff).
sp_addserver <new_name>, local
sp_addserver <'new_name\instancename'>, local
2. Fix Linked Servers
I just drop them and readd them
3. Fix connections.
4. Verify SQL Agent and SSIS Jobs.
One Item I'm looking for help on is the groups that SQL Server 2005 creates, these still exist with the old computer name in them, it definetly looks odd, but appears functional.