How do you setup mirroring in SQL 2005 with IP addresses for server names? (EC2 / no AD)

Question

I am setting up a mirror in Amazon EC2 and I have all servers with dedicated IPs. When i try to connect my mirror to my principal with the following command

alter database DB_NAME set partner = 'TCP://202.42.3.54:1433'

(IP changed from real for security) I get an error saying

The server network address "TCP://202.42.3.54:1433" can not be reached or does not exist. Check the network address name and reissue the command.

I am sure the server exists and is visible from the other server. I recall having issues when the endpoint server name is not the same that the server knowns for itself in @@SERVERNAME, Does anyone think this could be the issue?

Given that we don't have any domain setup, nor ActiveDirectory the only way i can think of using the actual server names is to setup a hosts file, but I find that solution will end up being hard to manage and very ugly.

Any options or ideas?

Answer 1

With SQL DB mirroring and replication, if @@servername doesn't match the actual hostname, you WILL have issues. This typically will happen if a server is renamed after SQL is installed. Also, the servers need to be able to communicate via hostname, whatever handles that is up to you (DNS, hosts files, WINS, etc)

If your hostname does not match the @@servername query, there is a simple, non-destructive script to fix. In the example, "oldhost" will be the name that the query returns. After running the script, you will need to restart the SQL services.

exec sp_dropserver 'oldhost\instance'
go
exec sp_addserver 'currenthostname\instance',local
go