Sql-server – Mirroring in SQL Server 2012

mirroringsql-server-2012

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.

Best Answer

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/