SQL 2005 Database Mirroring Tutorial

SQL 2005 Database Mirroring Tutorial (Without Domain)

Introduction

This tutorial is for people who are really really frustrated.

One of my project need to use mirroring to replicate data between 2 database servers. I look for help online and found some useful websites. However, when I follow the instructions, some error messages stop me.....

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

Error: 1443, Severity: 16, State: 2

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.

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 "HOSTS" file on the 3 servers (C:\WINDOWS\system32\drivers\etc)

OK ! Now we shall start!

HOST A = Principal Server

HOST B = Mirror Server

HOST W = Witness Server

You need to copy the certificate between the servers manually.

Here is the SQL code:

-- HOST A
create master key encryption by password = 'abc123!!';
GO

create certificate HOST_A_cert with subject = 'HOST_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 HOST_A_cert, encryption = disabled, role = all);
GO

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

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

create certificate HOST_B_cert with subject = 'HOST_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 HOST_B_cert, encryption = disabled, role = all);
GO

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

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

create certificate HOST_W_cert with subject = 'HOST_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 HOST_W_cert, encryption = disabled, role = witness);
GO

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

-- HOST A again
create login HOST_B_login with PASSWORD = 'abc123!!';
GO

create user HOST_B_user from login HOST_B_login;
GO

Create certificate HOST_B_cert
Authorization HOST_B_user
From file = 'c:\HOST_B_cert.cer';
GO

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

create user HOST_W_user from login HOST_W_login;
GO

Create certificate HOST_W_cert
Authorization HOST_W_user
From file = 'c:\HOST_W_cert.cer';
GO

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

-- HOST B again
create login HOST_A_login with PASSWORD = 'abc123!!';
GO

create user HOST_A_user from login HOST_A_login;
GO

Create certificate HOST_A_cert
Authorization HOST_A_user
From file = 'c:\HOST_A_cert.cer';
GO

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

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

create user HOST_W_user from login HOST_W_login;
GO

Create certificate HOST_W_cert
Authorization HOST_W_user
From file = 'c:\HOST_W_cert.cer';
GO

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

-- HOST W again
create login HOST_A_login with PASSWORD = 'abc123!!';
GO

create user HOST_A_user from login HOST_A_login;
GO

Create certificate HOST_A_cert
Authorization HOST_A_user
From file = 'c:\HOST_A_cert.cer';
GO

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

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

create user HOST_B_user from login HOST_B_login;
GO

Create certificate HOST_B_cert
Authorization HOST_B_user
From file = 'c:\HOST_B_cert.cer';
GO

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

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

-- HOST 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!

Alan - alan328@gmail.com

Recently, I open a new online shop: Modes4U which sales Tenshi Neko, Rockabilly Bags; please have a look!

"Mode" in germany means "Fashion", so, Modes4U = Fashion for you.

Update 2008/05/18:
I started a new website for search engine optimization, I am helping people on SEO topics. My new website's URL is seo-expert.hk!

Update 2008/04/27:

I am planning to create a step by step version of this tutorial, but it take longer than I imagine.

And I am working on my new website iPhone Daily, it happen to be a Chinese website for iPhone News.

Update 2008/05/09:

I created a new website to talk about iPHONE related topics. The website URL is iPHOTO Daily http://iphonedaily.com

My eshop Modes4U has some new items: CubicEx , Akuma Neko , Mimori , Men T-Shirts . Another good news is, we offer FREE shipping for purchases over Euro$60.

Wholesale is welcome for all items on my eshop! If you are looking for Tenshi Neko Wholesale, Rockabilly Wholesale, please contact us at wholesale@modes4u.com

Update 2008/11/18:
Time to get some ideas of Christmas gifts, here are some great ideas for Christmas gifts.
Best Christmas Gift Ideas

PART2 - FAQ

Sometimes, I get emails with questions of some common error messages. Since most of the people get the same error, I think it is more helpful If I list the question here. Hope it can save you some "try and error" time.

Question 1:

This is the most commonly asked question.

When you run the command "alter database myDatabase set partner = 'TCP://abc.domain.net:7024';"

You may get the following error message:

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://abc.domain.net:7024" 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.

Most of us think this error is related to the part "can not be reached" and "Check the network address name and that the ports"

But, this error message is not related to networking problem. For whatever reason, it is just a "general" error message... once SQL Server 2005 cannot connect to the partner, it shows this error message.... so, don't believe it....

Solution:

1. Make sure that you are using the same username and password to run the SQL Server service on both machines.

- It is because SQL Server connect to each other with NT Auth. that using same username and password can prevent a lot of problems.

- Using Configuration Manager to set the user for running the service.

2. Make sure that the username for running SQL Server service got all the rights.

- Use "Surface area configuration" to assign the rights to the current user.

3. Make sure that you can connect to both machines with Windows Auth

- To check it, just open SQLManagement Studio, connect to both server with Windows Auth

- Update: I find out that SQL Server 2005 talk with each other with "Named Pipes" connection. You MUST enable "Named Pipes" on all servers!

Question 2:

This is the problem that I am facing: .NET SQL Driver cannot "automatic failover".

Problem:

I wrote a program with C#, with "Failover Partner" in the connection string.

It works fine if I manually failover with SQL Management Studio.

However, if I unplug the primary server's network cable, SQL Server do able to failover automatically, but the C# program is not able to connect to the failover partner..... The most funny thing is, it was OK when I test the application at my office, but it dose not work after deploy to production environment.

Update 2008/05/16:

I finally got the hot fix from MS by contacting their CS through their website. It fixed the ".NET application not working after failover" problem, but the SqlDataAdapter.Fill() give me error message like "Transportation Level Error..."

I really don't understand why MS fix this problem so late.... 2 years after SQL2005 release date.... the current version is SQL 2008 and .NET framework 3.5!!!

It is more frustrating that MS make a post on their website but not letting people to download the file.... Come on.... it is a BIG BUG...

Anyway, here is the file

http://alan328.com/Documents/NDP20-KB944099-X86.zip

Try at your own risk! I am not responsible for anything ~

You need to have .NET 2.0 SP1 before you can install this Hot Fix.

Update 2008/05/08:

Finally I got an answer for the root cause of this problem. It is a bug of .NET Framework 2.0 SQL Native Client, as state here:

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"

It is because .NET Framework 2.0 SQL Native Client employs Auto Connection Pooling, meanwhile, the connections in the pool fail to react to the Failover event. At this moment, there is no update/hot fix from Microsoft yet.

Update 2008/05/07:

I realized the root cause of this problem is Domain Controller on the same server. My project only use 2 server and a desktop to form the SQL Server Mirroring. At the very beginning, I only manage to make it work with Domain, so, I use one of the server as the Domain Controller, but it turn out not a good chooice. It is because all the user account and access rights are controlled by the domain controller, it is die, everything die with it.... so, DON'T use Domain Controller if you can.

Solution:

1. I tried many different ways, but fail to get it work. So, I give up the "automatic" feature.

- using "try{ } catch { }" twice to failover in a controlled maner.

My source code looks like this:

SqlConnection con = new SqlConnection(conStr);

try{

con.Open();

}catch{

try{

con.ConnectionString = conStr2;

con.Open();

iniFile.Write("ConStr1="+conStr2);

iniFile.Write("ConStr2="+conStr1);

string tmp = conStr1; conStr1 = conStr2; conStr2 = tmp;

}catch(Exception ex){

MessageBox.Show("Fail to connect to DB: "+ex.Message);

}

My config file contains 2 connection strings. 1st try to use the normal connection string, if not able to connect, then try to use the 2nd connection string.

If successfully connect to DB with the 2nd connection string, then update the config file by exchanging the 2 connection strings. So, next time the program would connect to the "failover partner" 1st.

With this work-around, my program can "automatic" failover successfully. Most importantly, I am not depending on the SQL Driver's automatic failover feature anymore.

Question 3:

What do I need to do if I want to start over from the beginning again?

ANS:

You just need to drop everything and you can start again.

Example:

drop endpoint endpoint_mirroring
drop user HOST_W_user
drop login HOST_W_login
drop certificate HOST_W_cert

drop master key

Good Luck! Enjoy the fun with Automatic Failover!