Sql Server Mirroring

Sql Server Mirroring


SQL server mirroring works based on principal of Transaction log shipping.

Alan, has published this tutorial is for people who are really really frustrated.
Most likely, you would get this error message:

Error: 1418 - Microsoft SQL Server - The server network address can not be reached or does not exist. Check the network address name and reissue the command. The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.
That error message dosen't tell you the truth....  there is nothing to do with "can not be reached or does not exist".

There are 2 possible causes:

 You forgot to use "NO RECOVERY" when restoring database on the MIRROR server
 NT Authentication fail.... some unknow reason...  a lot of people reported this problem
When you view the SQL Server Log, you would see some error message like :

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 Server.)'.' for 'TCP://mymirror.mydomain:5022'

or


Error: 1443, Severity: 16, State: 2


or


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.


Well, if you are having the above error messages, I hope this tutorial could help you.

 Update 2008/05/16 - Hot fix from Microsoft

After contacting Microsoft through their website. I got a email with the Hot Fix for the .NET Framework...  I tested it and it worked now! Yeah!

There is still problem with SqlDataAdapter.Fill(), after fail over, I got a error message: " Transportation Level Error ..."

I really don't understand why MS not providing the hot fix for download..... so, I uploaded the hot fix here...   see FAQ below to download the hot fix!
 
Update 2008/05/08 - IMPORTANT! 


If you encounter the problem that :

SQL Server Mirror failover successfully
BUT, .NET SQL Native Client cannot connect to the current Principal Server
And, you are using .NET Framework 2.0

When you check SQL Server Event Log, you may see this error message:


Error: 18456, Severity: 14, State: 16.
Login failed for user DomainName/UserName


You can save your time now....  because there is no solution yet.

Microsoft reported that it is a bug of .NET Framework 2.0 SQL Native Client when handling Connection Pooling.


Read More: FIX: Error message when you use the SQL Native Client data provider to connect to an instance of SQL Server 2005 that is configured to use database mirroring: "Internal .Net Framework Data Provider error 6"
 

Update 2008/05/06

I received emails asking the same questions. So, I decided to update this tutorial and add more details.

I setup 2 new SQL 2005 Server and a SQL Express 2005 to form a pair of mirroring server and a witness server.


I noticed that "Named Pipes" connection is a MUST.

And, this is the root cause of the error mentioned in FAQ Q1.


And I tested that is completed OK to setup mirroring WITHOUT Domain!

I recommend don't use Domain if you don't need it.


SQL Express 2005's default port is not 1433.

You need to check which port it is using and open your firewall.
 
Update 2008/03/25

Since this page was published, it received quite many views.

If you search on Google.com, you may find this page by:


- search with 'sql mirroring', result on page 4

- search with 'sql 2005 mirroring', result on page 3

- search with 'sql mirroring tutorial' or 'sql 2005 mirroring tutorial', 1st search result
 

Solution: Using Certificates
Forget about Windows Authentication or Domain Account.....   just use Certificates is good enough.
What you need is:
- 2 Servers and 1 Witness
- Create a database on Principal Server
- Backup the database 2 times: 1st time do a "FULL" backup, 2nd time do a "Transaction Log" backup
- Restore the database on Mirror Server, MUST use option "NO RECOVERY" !!! After restore, the mirror database will not take any request, that is completely normal.
- Fully Qualified Domain Name for all 3 servers.....  you can do this by: 1. Setup a domain, or 2. Change the computer name and modify the "ServerS" file on the 3 servers (C:\WINDOWS\system32\drivers\etc)  
OK ! Now we shall start!
Server A = Principal Server
Server B = Mirror Server
Server W = Witness Server
You need to copy the certificate between the servers manually.
Here is the SQL code:
-- Server A
create master key encryption by password = 'abc123!!';
GO

create certificate Server_A_cert with subject = 'Server_A certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO

Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate Server_A_cert, encryption = disabled, role = all);
GO

Backup certificate Server_A_cert to file = 'c:\Server_A_cert.cer';
GO

-- Server B
create master key encryption by password = 'abc123!!';
GO

create certificate Server_B_cert with subject = 'Server_B certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO

Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate Server_B_cert, encryption = disabled, role = all);
GO

Backup certificate Server_B_cert to file = 'c:\Server_B_cert.cer';
GO

-- Server W
create master key encryption by password = 'abc123!!';
GO

create certificate Server_W_cert with subject = 'Server_W certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO

Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate Server_W_cert, encryption = disabled, role = witness);
GO

Backup certificate Server_W_cert to file = 'c:\Server_W_cert.cer';
GO

-- Server A again
create login Server_B_login with PASSWORD = 'abc123!!';
GO

create user Server_B_user from login Server_B_login;
GO

Create certificate Server_B_cert
Authorization Server_B_user
From file = 'c:\Server_B_cert.cer';
GO

Grant CONNECT ON Endpoint::endpoint_mirroring to [Server_B_login];
GO
------
create login Server_W_login with PASSWORD = 'abc123!!';
GO

create user Server_W_user from login Server_W_login;
GO

Create certificate Server_W_cert
Authorization Server_W_user
From file = 'c:\Server_W_cert.cer';
GO

Grant CONNECT ON Endpoint::endpoint_mirroring to [Server_W_login];
GO

-- Server B again
create login Server_A_login with PASSWORD = 'abc123!!';
GO

create user Server_A_user from login Server_A_login;
GO

Create certificate Server_A_cert
Authorization Server_A_user
From file = 'c:\Server_A_cert.cer';
GO

Grant CONNECT ON Endpoint::Endpoint_mirroring to [Server_A_login];
GO

-------
create login Server_W_login with PASSWORD = 'abc123!!';
GO

create user Server_W_user from login Server_W_login;
GO

Create certificate Server_W_cert
Authorization Server_W_user
From file = 'c:\Server_W_cert.cer';
GO

Grant CONNECT ON Endpoint::Endpoint_mirroring to [Server_W_login];
GO

-- Server W again
create login Server_A_login with PASSWORD = 'abc123!!';
GO

create user Server_A_user from login Server_A_login;
GO

Create certificate Server_A_cert
Authorization Server_A_user
From file = 'c:\Server_A_cert.cer';
GO

Grant CONNECT ON Endpoint::Endpoint_mirroring to [Server_A_login];
GO

-------
create login Server_B_login with PASSWORD = 'abc123!!';
GO

create user Server_B_user from login Server_B_login;
GO

Create certificate Server_B_cert
Authorization Server_B_user
From file = 'c:\Server_B_cert.cer';
GO

Grant CONNECT ON Endpoint::endpoint_mirroring to [Server_B_login];
GO

-- Server B again
alter database good set partner = 'TCP://server1.ace.local:7024';
GO

-- Server A again
alter database good set partner = 'TCP://server2.ace.local:7024';
GO

alter database good set witness = 'TCP://mc.ace.local:7024';
GO 

Now, everything is fine!
Hope you enjoy it!


Error:
 There is already a master key in the database. Please drop it before performing this statement.

solution:
 
 use master

drop master key

error:
Cannot drop master key because certificate 'Server_A_cert' is encrypted by it.
drop

endpoint Name_of_your_end_point

Tags:

MS SQL server shutdownThe shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005MS SQL server mirroring remove end points master keygodaddy hosting MS SQL server configurationsql server default portsbackup sql databasesql server agent backupDatabase mirroring is currently provided for evaluation purposes only and is not to be used in production environments To enable database mirroring for evaluation purposes use trace flag 1400 during startupsql server free spaceSql server cpu utilisationMS SQL server service pack

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.