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).

Improve this question
9
  • Just to clarify - you are running two different virtual machines, with each VM running a default instances of SQL Server 2012? If this is the case, is one of them a clone of the other that you ran sysprep on?
    – Nic
    Jun 19, 2017 at 19:40
  • 1
    I believe the answer to your question can be found here.
    – John Eisbrener
    Jun 19, 2017 at 19:47
  • Nic - Yes, two different virtual servers running default instances of SQL 2012. I don't know about the clone part...these installations were done before my time. The sysadmin guy who performed the install indicated that a "clone" (image) was made of a server with SQL already installed on it. I think that is the main problem. I'm just trying to figure out if I can rename the instance to match the server name(s).
    – SQL_Hacker
    Jun 19, 2017 at 19:55
  • @John - When I run Select SERVERPROPERTY('Server2'), the result is NULL. Same with 'Server3'.
    – SQL_Hacker
    Jun 19, 2017 at 19:56
  • See this from books online.
    – SqlWorldWide
    Jun 19, 2017 at 19:58

1 Answer 1

Reset to default
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.

Improve this answer
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
    Jun 19, 2017 at 20:24

Your Answer

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

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