Mirroring in SQL Server 2012

Question

I've got newly installed Win 2008 R2 machines, in a workgroup, in the same subnet, and of course SQL Server 2012.

I allowed login with SQL Server authentication, thinking it may help with each workstation having a different user account database. I created identical logins for each of three machines. Two have the full SQL Server software and one has SQL Server Express. Then I tried to mirror two different databases, producing the same result.

The first, I just right clicked on database in the SQL Server Management Studio, made a new database, then confirmed the recovery model was set to full. Backed it up and the transaction log. Restored the two items to my other server. (To start I'm not using the Witness yet in hopes to keep it as simple as possible) When restoring the data and log, for both I am restoring it without recovery. Then when I try to mirror, I get one warning about Fully qualified domain names, click through it because my servers are not in a domain, and then when it tries to mirror and comes back with:

An error occurred while starting mirroring.

Additional Information:
Alter failed for database 'test'. (Microsoft.SqlServer.Smo)
An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address "TCP://(SERVERNAME):5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, ErrorL 1418)

I have tried it with the AdventureWorks database too with the same result.

In Event Viewer I am seeing "Database Mirroring connection error 4. 'An error occurred while receiving data.' '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://(Mirrored SERVERNAME):5022'.

In the SQL Server log I am seeing

Error 1443. Severity 16. State 2
Database mirroring has been terminated for database 'test'. This is informational only. No user action is required.

and

Error 1474. Severity 16. State 1.
Database Mirroring connection error 4. 'An error occurred while receiving data.' '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://(Mirrored SERVERNAME):5022'.

I have been doing searches on google trying to get my head around this, but I think because I am new to all this, I am having a hard time understanding where to go from here.

Answer 1

Sorry for the previous useless response - skipped over the fact that you are setting up a mirror without a domain. You will need to use certificates for this.

I found an article that might help you with that part. Check it out: http://mssqlnet.wordpress.com/2012/06/20/microsoft-sql-server-database-mirroring-in-a-workgroup-without-domain-with-certificate/

Answer 2

I tried to follow the way mentioned in the link above. I am using the certificate concept.

I have 3 instances on the same machine.Default one is for witness and other 2 instances are principal and mirror servers.

(1) Inside witness when I am running below query:

CREATE CERTIFICATE Server2_cert
AUTHORIZATION login_mirroring
FROM FILE = '....\backup\Server2_cert.cer'


CREATE CERTIFICATE Server1_cert
AUTHORIZATION login_mirroring
FROM FILE = '...\backup\Server1_cert.cer'


Msg 15208, Level 16, State 19, Line 1
The certificate, asymmetric key, or private key file is not valid or does not exist; or
you do not have permissions for it.

(2) Under instances Default,principal and mirror servers I have created the endpoint (similar to one mentioned in the link) which listen to 5024,5022 and 5023.

On the principal I ran below where database1 is the DB to be mirrored

ALTER DATABASE database1 SET PARTNER = 'TCP://server_name:5023'


Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://server_name:5023" can not be reached or does not 
exist. Check the network address name and that the ports for the local and remote 
endpoints are operational.

One thing to mention that I am doing exactly what is mentioned in the link. So I am trying to setup DB mirroring without using domain certificates.