2

I just started a new gig, and have discovered that two instances of SQL 2012 (SP2), installed on Core Server 2012 R2, have a different name than the server it's running on. They are both virtual servers. For example, the server name is Server2 and Server3 (can RDP to it and can connect to it multiple ways using this name) and can even connect with SSMS from other machines. However, when I run @@Servername, it returns "CO-SQLTEMPLATE"--on BOTH servers. The reason this is troublesome is because these two instances participate in AlwaysOn replication, and there are several problems...the most glaring one is automated backups will not work. Using the HADR systables and functions, it either shows that none of the replicas are "primary" or all of them are (!!!). Also, it is not clear that the replication is actually working. In a couple of the AGs, it shows that the databases are synchronized with the Primary (Server1), but I don't trust that since there are essentially 2 instances with identical names.

I've been hunting around on Google, and cannot find a source that indicates where SQL Server gets its instance name from during installation. I've never seen this issue before, and it is truly bizarre.

This is my last ditch effort to try to find a solution before I just wipe the 2 servers clean and start from scratch (which, obviously, I'd rather not do).

1
  • All--it finally sunk into my thick skull what I believe commentators were trying to tell me. SQL Server was installed on a machine/image at some point when the machine/image was named "CO-SQLTEMPLATE". Then, when the VM was built using that image, the OS name was changed to "Server2" and "Server3", but nobody followed up with SQL Server and performed the sp_dropserver & sp_addserver to get the two to match. A sentence in this: (learn.microsoft.com/en-us/sql/t-sql/functions/…) article finally drove the point home. Thanks everyone for your help!
    – SQL_Hacker
    Commented Jun 19, 2017 at 21:28

1 Answer 1

2

Sounds like someone thought it would be easy to create a "go-to" SQL Server VM that you can clone. Nobody is bothering to change the standard CO-SQLTEMPLATE SQL Server name. If replication is actually working, I would question whether it is working properly because the SQL Server name should match the VM/Physical server name--minus the instance. What's worse, people are probably connecting to SQL Server using the VM/Physical name and don't know about CO-SQLTEMPLATE.

If you're not going to trash and rebuild, you will probably want to rename SQL Server to match the Windows Server name where it's installed:

exec sp_dropserver 'CO-SQLTEMPLATE'
exec sp_addserver 'MyWIndowsServerName'.

I wouldn't trust that SQL Server setup. If you can, I'd check the Availability Groups folder to see which if the replicated database says myDB (Primary) or myDB (Secondary). Then on the server with myDB (Primary) break and rebuild the AlwaysOn replication and rebuild the replication with the AlwaysOn Wizard. (However, I don't add the listener with the wizard as that part seems buggy to me. I add the listener separate).

But before that, if the servers are as messed up as you say, I'd validate that the Windows cluster is even working properly via Failover Manager. And, validate that DNS actually contains the names for your Windows Servers and your Listener (and possibly a witness)--if you have one.

Caution: Your servers might even be worse than you imagined with users connecting to both instances of CO-SQLTEMPLATE on two different servers as they both "could" represent two completely different sets of data and are completely unrelated. Because, after all, they used the handy-dandy VM template to make their SQL Server life easier.

1
  • Indeed...things could be worse than they seem on the surface...which is why I'm trying to fix it. :) We did try to add a new AG the other day, and it won't accept it, which it was thought was due to a permission issue (weird AD delegation policy...long story). But, it may be due to the fact that the cluster itself is not healthy.
    – SQL_Hacker
    Commented Jun 19, 2017 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.