SQL 2014 - Mirroring

Hi,

We are creating an environment using the following: (Full quorum)

Witness = Microsoft 2012 R2 with SQL 2014 Web Edition
Partner 1 = Microsoft 2012 R2 with SQL 2014 Standard Edition
Partner 2 = Microsoft 2012 R2 with SQL 2014 Standard Edition

I have also seen the feature called AlwaysOn, can anyone please explain the difference in the setup above that I intend to go with and the AlwaysOn option?

Thanks,

introlux
introluxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
introluxAuthor Commented:
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
0
lcohanDatabase AnalystCommented:
"AlwaysOn Availability Groups relies on the Windows Failover Clustering (WSFC) cluster to monitor and manage the current roles of the availability replicas that belong to a given availability group and to determine how a failover event affects the availability replicas. A WSFC resource group is created for every availability group that you create. The WSFC cluster monitors this resource group to evaluate the health of the primary replica."

Your "witness" server above does similar work to keep your  SQL Server Failover Clustering up and running. To understand exactly the difference between the two you need to read a bit more at:
https://msdn.microsoft.com/en-us/library/ff929171(v=sql.110).aspx

and important difference between AlwaysOn and "witness" like "disaster recovery":

"AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level"
...
"An FCI can leverage AlwaysOn Availability Groupsto provide remote disaster recovery at the database level. For more information, see Failover Clustering and AlwaysOn Availability Groups (SQL Server)."

https://msdn.microsoft.com/en-us/library/ms189134(v=sql.120).aspx
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Vitor MontalvãoMSSQL Senior EngineerCommented:
(...) 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.
0
ZberteocCommented:
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.
0
introluxAuthor Commented:
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
0
ZberteocCommented:
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.
0
introluxAuthor Commented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try first to read MSDN article. They are very complete. Here is the one related do Mirroring.
0
introluxAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
introluxAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
introluxAuthor Commented:
NT Service\MSSQLSERVER
NT Service\MSSQLSERVERAGENT

Shall I create a new user and logon as that?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
introluxAuthor Commented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I never did this but from this MSDN article it's possible by creating the certificates that you talked about.
Doesn't seems very hard to perform it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
introluxAuthor Commented:
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
------------------------------
0
introluxAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
introluxAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you get the error logs?
0
introluxAuthor Commented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
introluxAuthor Commented:
I doubt it as it will not even connect if the certificates were setup incorrectly like it did before.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
introluxAuthor Commented:
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!
0
introluxAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You usually only need to do that when you change the server name. Did a server name change occur?
Anyway, before you go with that solution, perform a backup of the master database so if anything went wrong you can restore it.
0
introluxAuthor Commented:
Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.