SQL 2014 - Mirroring

Witness in a Cluster? Shouldn't me in a Mirror?

Mirroring is deprecated and AlwaysOn is the natural replacement. With AlwaysOn you won't need a witness (with mirroring you can also have a solution without the witness) and the replica is available for read only (with mirroring the replica can't be accessed).
AlwaysOn is only available in Enterprise Edition so you need to upgrade your SQL Server instances if you want to use this feature and you'll need to set a Windows cluster to set an AlwaysOn solution.
There's a complete MSDN article about AlwaysOn and you should read it to get more knowledge about this feature.

Thats fine, we will have to go with the option of mirroring as we cannot afford enterprise edition. I have looked online and the steps are fairly simple to build.

What I want to understand with this setup, the two partner servers will be mirroring each other, where will the connection string require to point to and how will the witness server ensure that the failover kicks in automatically if one of the db's are offline.

Also if we had gone with the option of only having two partners mirroring, if one had failed, how will you be able to switch? will this be a manual process?

Thanks,

introlux

(...) the two partner servers will be mirroring each other, where will the connection string require to point to (...)

You should add the two server names in the connection string ("Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn") and the connection should be refreshed in case of failover. For better understanding I recommend you to read this MSDN article.

how will the witness server ensure that the failover kicks in automatically if one of the db's are offline.

Well, that's the function of the Witness. Is there to perform the failover automatically. It just keep pinging both servers to check if they are alive or not. If the principal doesn't respond it just starts the failover automatically.

For Always On you need SQL Enterprise version. In a 2 or any even number of nodes configuration the witness can be a network folder on a different machine which doesn't need any SQL server installed.

Is that 100%? A witness server I thought still requires a SQL instance which could be Express Edition or Web edition.

Can you please confirm this
Thanks,

introlux

If you use Always On than that is the case. Witness with SQL server is needed in the classic SQL mirroring if you want automatic failover. If you don't need automatic failover then you don't need witness.

Always On is the next level of mirroring where both primary and secondary nodes are online, secondary read only, and the databases part of the availability group will failover together and not db by db base. However, as I said you need SQL enterprise for it.

OK great! We will go down the mirroring option because we cannot budget for enterprise license. Any good sources of setup info articles to follow to ensure best practise is followed?

Try first to read MSDN article. They are very complete. Here is the one related do Mirroring.

Thanks for your help, I am currently struggling on getting all three servers talking to each other.

The error I am getting is attached.

introlux

Note: Using SQL authentication between servers and they are not connected via VPN or on the same domain - I have read somewhere I may need to use certificates to get this to work but I am not sure.
Error.JPG

There's any firewall between the servers? If so you'll need to check if ports are opened for SQL Server instances. Also check if each MSSQL instance is accepting remote connections.

Checked error logs and have seen this:

Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: XXX.XX.XX.XXX]

Firewall has been disabled. Checked all servers and all are listening via port 5022.

By the error it's trying to connect with a Windows user and not a SQL user. Which user are you using for SQL Server and SQL Agent services?

NT Service\MSSQLSERVER
NT Service\MSSQLSERVERAGENT

Shall I create a new user and logon as that?

Those users have local privileges only, i.e. they can't reach remote machines and that's why you're getting those errors.
I wrote an article about SQL Server service account and I recommend you to read it to understand better how it works.
What you need now is that the different domains can trust each others. Read this MSDN article to get more knowledge on this solution as well.

From what I have read, I require an AD to make this authentication work? Is it not possible to make this work without using AD?

I followed this setup: http://tecadmin.net/setup-database-mirroring-sql-server/#

It failed! I have now tried to delete the certificates and revert back but now I am getting an error for the witness server!

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to connect to server WITNESSSERVER. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476

------------------------------

The certificate chain was issued by an authority that is not trusted

------------------------------
BUTTONS:

OK
------------------------------

Vitor,

I have managed to work the certificates and I have them mirroring. However when I check using the Database Mirroring Monitor I get an error on the mirror instance. Can you shed some light on this?
Error2.JPG

Glad that you managed the certificates to work.
About that error the image shows very few. Did you read this MSDN article about Monitoring Database Mirroring? It shows how to check for issues. If you can post here a more detailed error.

Hi,

I have looked at this web site and its confusing me. I still do not really understand what commands to run to pull this info to check.

Can you get the error logs?

SQL Server Profiler is best!

Here is the error from the partner:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 172.31.31.70]
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I am using certificates and not domain, any idea?

Yeah, that error is leading to domain issues unless is misleading us if the certificates aren't working properly for that server. Maybe something missed during the creation of the certificates. Can you recreate them?

I doubt it as it will not even connect if the certificates were setup incorrectly like it did before.

Then for some reason it still trying to connect with the domain user. Can you find out the login name that he is trying to use?

I have tested the turn off SQL and it seems to be working fine as the roles were switched correctly and both seems to communicate when one db it stopped mirroring or forced fail over. So this error does not make sense at all!

Right! I know what the issue is, if you use the following command:

select @@servername

It gives the response: WIN-AP25LLH1RGB

So when using the name instance, it will not work as it requires the same DB SQL Instance name. I believe I need to run the following:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>;
GO

If however I do this, I believe it will mess the certificates? the databases on there etc?

So it just means I need to host hack each machine and use the SQL Name instance to get the mirroring monitoring working.

Any suggestions on this?

introlux

Thanks for the help!