Cluster Mirroring

I've read a lot of the information about this topic in MSDN. My boss askme to understand and to let working an example application of Cluster Mirroring. The problem is that i dont understand well yet what is a Cluster and what is the main idea behind it.

If someone really understand and can explain me clearly whats behind it, i will be very grateful. Thanks a lot for the help. ( Sorry for me english :( )

Ok, Basically a cluster is a two-headed monster. Chop one of it's heads off and nothing will happen, it will still carry on chasing you.

In computer terms, the simplest cluster is a two-node cluster (two headed monster) , two physical computers sharing the same name and IP address. If one of the computers making up the cluster fails (chop one head off) the cluster will still carry on functioning (chasing you) on the other computer participating in the cluster.

The general idea behind it is to provide high-availability for your apps by eliminating a single point of failure.

|||

Thanks bobbins!

But i have another question ... when you have a mirror database with a witness, you also provide high-availability. Because if something happen the mirror will continue chasing you .. Why is much better the cluster ? Thanks a lot again

|||

There are many discussions/articles on Clustering vs Mirroring vs Replication vs Log Shipping

In short, I think clustering is still a better solution for high-availability, PROVIDED that you do have a SAN

We're using mirroring now until we can get a SAN in, then we'll move to clustering

sqlsql

Cluster and Mirroring with 2005

I am looking for some advice on the following problem.
I need a redundant SQL solution at our main location and a warm site in
a different, off site location. The warm site would only become a hot
site if we had a complete failure at the main location.
What I was thinking was to create a failover cluster at our main
location incase of simple hardware failure, the other server could take
over. At the warm site I would have a witness server and have the
database mirrored to a sql server there. I am going to be using sql
server 2005.
Is this a possible solution? If not, can you please point me in the
right direction. $$$ is not really an issue.
That is certainly a possible scenario. Remember that mirroring works at the
database level, not at the server level. You will have to do some
customization if you want to make it work for multiple databases. There are
also performance, latency, and network bandwidth costs for mirroring. The
lower your tolerance for data loss and outage time, the higher those costs.
If money really isn't an issue, you may want to look at SAN-level data
replication technologies. Since you will have a SAN for the cluster anyway,
this may be a better option. Make sure the SAN vendor can keep multiple
LUNS in synch or you may end up with mismatched data and log partitions,
causing a corrupt and unusable database.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<dwclark@.gmail.com> wrote in message
news:1130250702.646778.85520@.g47g2000cwa.googlegro ups.com...
>I am looking for some advice on the following problem.
> I need a redundant SQL solution at our main location and a warm site in
> a different, off site location. The warm site would only become a hot
> site if we had a complete failure at the main location.
> What I was thinking was to create a failover cluster at our main
> location incase of simple hardware failure, the other server could take
> over. At the warm site I would have a witness server and have the
> database mirrored to a sql server there. I am going to be using sql
> server 2005.
> Is this a possible solution? If not, can you please point me in the
> right direction. $$$ is not really an issue.
>
|||I noticed that you said 'warm site'. By 'warm', I assume you can afford
losing some data during a site failover. If you can afford data loss of up
to 15 minutes or so, the plain old log shipping may be the simplest and
cheapest solution to consider. I'd keep it simple if a simple solution is
enough.
Linchi
<dwclark@.gmail.com> wrote in message
news:1130250702.646778.85520@.g47g2000cwa.googlegro ups.com...
>I am looking for some advice on the following problem.
> I need a redundant SQL solution at our main location and a warm site in
> a different, off site location. The warm site would only become a hot
> site if we had a complete failure at the main location.
> What I was thinking was to create a failover cluster at our main
> location incase of simple hardware failure, the other server could take
> over. At the warm site I would have a witness server and have the
> database mirrored to a sql server there. I am going to be using sql
> server 2005.
> Is this a possible solution? If not, can you please point me in the
> right direction. $$$ is not really an issue.
>
|||Database mirroring is certainly an option, but so would be log shipping,
remote mirroring, or stretch clustering.
You might want to check out the following. Although for SS2K, most of the
information still applies, with the exception of DB mirroring.
Microsoft SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
SQL Server 2000 High Availability Series
Implementing Remote Mirroring and Stretch Clustering
http://www.microsoft.com/technet/pro...y/hasog05.mspx
SQL Server 2005 Mission Critical High Availability
http://www.microsoft.com/technet/pro...s/default.mspx
Sincerely,
Anthony Thomas

<dwclark@.gmail.com> wrote in message
news:1130250702.646778.85520@.g47g2000cwa.googlegro ups.com...
> I am looking for some advice on the following problem.
> I need a redundant SQL solution at our main location and a warm site in
> a different, off site location. The warm site would only become a hot
> site if we had a complete failure at the main location.
> What I was thinking was to create a failover cluster at our main
> location incase of simple hardware failure, the other server could take
> over. At the warm site I would have a witness server and have the
> database mirrored to a sql server there. I am going to be using sql
> server 2005.
> Is this a possible solution? If not, can you please point me in the
> right direction. $$$ is not really an issue.
>

Client-side Redirect problem

Hi there.

I have a database mirroring session running, and both principal and mirror are working fine. But when i unplug the network cable from principal server, creating a failover scenario, the client doesn′t get redirected to the mirror.

I get an error saying: "A transport-level error has ocurred when received results from the server. The specified network name is no longer available."

connection = "Data Source=SISAD;Failover Partner=Projecto-SWS1;Initial Catalog=SIERE;Persist Security Info=True;User ID=sa;Password=testing;Connection Timeout=100";

//this method is called many times by a thread, emulating some request to the server

public static void Inserir(int codAutor)

{

SqlConnection sqlConnection = new SqlConnection(connection);

SqlCommand command = new SqlCommand("insert into Autor values(@.codAutor,'test')", sqlConnection);

command.CommandType = CommandType.Text;

command.Parameters.AddWithValue("@.codAutor", codAutor.ToString());

command.CommandTimeout = 200;

sqlConnection.Open();

command.ExecuteNonQuery();

sqlConnection.Close();

}

I think the error is because the ExecuteNonQuery() method return info about the rows affected on the server. But in this case how can i create a failover scenario with client-side redirect ?

Thanks in advance.

I think i′ve resolved the problem...

The issue here is that connections are separated into pools by connection string, when using ADO.NET connection pooling.

It seems that when failover occurs, the attempts to reconnect to the database are retrieving connections from the pool, and the datasource is still the failed principal and not the new principal.

I deactivated connection pooling, and now the clients are connecting to the mirror when when principal fails.

Can someone give me some feedback about this ? It′s seems that i can′t use connection pooling when using client-side redirect.

|||

Hi RDSC,

I believe your assessment is correct. The logic to failover to the partner server only occurs when a new connection is established. Rather than disabling connection pooling altogether, you can clear this individual pool (I believe that there is a clearpool connection property that you can set) and should see the connections succeed. If you do try this, please let me know how it turns out.

Thanks,

Il-Sung
Program Manager, SQL Server Protocols.

|||

Hi there.

You are allright. In my code i can see if there is a connection error, and than set clearpool property. Next time the connection will try to connect to the new principal. So connection pooling can be used with client side redirect.

There is something you should be aware. If clearpool property is used, the state of the connection changes to "Closed", because connections from the pool are set to be discarded, so if you still want to use that connection, you have to open it again.

Thanks for you help Sung Lee.

Client-side Redirect problem

Hi there.

I have a database mirroring session running, and both principal and mirror are working fine. But when i unplug the network cable from principal server, creating a failover scenario, the client doesn′t get redirected to the mirror.

I get an error saying: "A transport-level error has ocurred when received results from the server. The specified network name is no longer available."

connection = "Data Source=SISAD;Failover Partner=Projecto-SWS1;Initial Catalog=SIERE;Persist Security Info=True;User ID=sa;Password=testing;Connection Timeout=100";

//this method is called many times by a thread, emulating some request to the server

public static void Inserir(int codAutor)

{

SqlConnection sqlConnection = new SqlConnection(connection);

SqlCommand command = new SqlCommand("insert into Autor values(@.codAutor,'test')", sqlConnection);

command.CommandType = CommandType.Text;

command.Parameters.AddWithValue("@.codAutor", codAutor.ToString());

command.CommandTimeout = 200;

sqlConnection.Open();

command.ExecuteNonQuery();

sqlConnection.Close();

}

I think the error is because the ExecuteNonQuery() method return info about the rows affected on the server. But in this case how can i create a failover scenario with client-side redirect ?

Thanks in advance.

I think i′ve resolved the problem...

The issue here is that connections are separated into pools by connection string, when using ADO.NET connection pooling.

It seems that when failover occurs, the attempts to reconnect to the database are retrieving connections from the pool, and the datasource is still the failed principal and not the new principal.

I deactivated connection pooling, and now the clients are connecting to the mirror when when principal fails.

Can someone give me some feedback about this ? It′s seems that i can′t use connection pooling when using client-side redirect.

|||

Hi RDSC,

I believe your assessment is correct. The logic to failover to the partner server only occurs when a new connection is established. Rather than disabling connection pooling altogether, you can clear this individual pool (I believe that there is a clearpool connection property that you can set) and should see the connections succeed. If you do try this, please let me know how it turns out.

Thanks,

Il-Sung
Program Manager, SQL Server Protocols.

|||

Hi there.

You are allright. In my code i can see if there is a connection error, and than set clearpool property. Next time the connection will try to connect to the new principal. So connection pooling can be used with client side redirect.

There is something you should be aware. If clearpool property is used, the state of the connection changes to "Closed", because connections from the pool are set to be discarded, so if you still want to use that connection, you have to open it again.

Thanks for you help Sung Lee.

Client-side problem with mirroring

Hi!

I posted this on the adonet forum but didn't get any response so I might as well post it here. Hope anyone can help me.

-

I have set up mirroring and trying to get it to work from my .NET
application. Mirroring seems to work ok.
When I do a manual failover the witness is updated properly in the
sys.database_mirroring_witnesses table so it seems to be configured
correctly. I also can shutdown the master database and the mirror takes over.

My application is configured with Server set to my master database and
FailOverPartner to my mirror database.

If I am connected to the master database and do a manual fail-over all my
attempts to communicate with the database fail with an exception, even after
repeated retries.
(System.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.).)

If I do a manual fail-over and restart my application it connects to the
mirror database and everything is working. If I now do a manual fail-over I
get the same exception, but after a few retries it connects to the master
database.

The field Connection.Datasource always shows the master database regardless
if it's connected to the mirror.

What am I doing wrong? Is this how it's supposed to work?

-

I have been pulling my hair over this problem and I just can't get it to work.

I'm using SQL Server Authentication to access the database and I can connect
to the master when it's acting as principal and I can connect to the mirror
when it's acting as principal, but when doing a fail-over, manual or shutting
down the master server it never tries to connect automatically to the mirror
even if it's changed role to principal.

Do the client need access to the witness in any way?

I'm using version 2.0.50727 of System.Data.dll.

I'm wondering if when you say "master database", you are refering to the system database called master or the principal database. It isn't clear. So I am going to assume that you mean "principal" database when you say "master" database, but your comments are very confusing. Also remember that Principal and Mirror are roles, it is better to state Server A and Server B and the role they are currently in. Also, when you say master database, everyone in the world thinks you are refering to the system database called "master," which cannot be mirrored. We'll get it figured out. :)

"My application is configured with Server set to my master database and
FailOverPartner to my mirror database."

You mean Server set to Server A and Failover_Partner is set to Server B, correct? You should not be putting database names in where there should be server names...

"Do the client need access to the witness in any way?"

No.

"I'm using version 2.0.50727 of System.Data.dll."

Can you verify that you are using the same System.data.dll as in this KB? http://support.microsoft.com/kb/912151/

"but after a few retries it connects to the master
database."

By retries, do you mean that the application is processing the network error and issues a reconnect?

Thanks,

Mark

Client-Side Database Mirroring Details

Hi,
I've been tasked with investigating different methods of achieving database
redundancy.
In doing so, I'm currently in the process of writing a test application to
prove that Database Mirroring in SQL Server 2005 is a suitable solution.
Of the established requirements I'm finding difficulties with the following:
1. Identifying the name/instance of SQL Server my application is currently
connected to.
2. Identifying the name/instance of SQL Server cached as the failover
partner, and confirming it state at any time.
3. Identifying the name/instance of SQL Server cached as the witness, and
confirming it state at any time.
4. Forcing a failover via my test application.
I've already got some of this functionality via sp_dbmmonitorresults, but
this relies on the Server Agent running and also adds to the network traffic
.
I was hoping for a solution using calls to the SQL Native Client.
I’m using SQL Server 2005 Enterprise Edition SP1.
My application is written in Visual C++ 6 as is the product this is intended
for.
Also, if anyone knows of any good links to web pages or forum topics with
regards to methods of implementing database retry/reconnect client-side when
SQL Server fails over, I would be grateful. I’ve implemented this
functionality already by deriving my own version of CDatabase, but I’m ope
n
to new suggestions.
Thanks!I haven't really worked on it, But this link seemed interesting..
http://blogs.msdn.com/sqltips/archi..._Mirroring.aspx
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||There is some useful information and one or two scripts, but it doesn’t
really answer my question.
"Omnibuzz" wrote:

> I haven't really worked on it, But this link seemed interesting..
> http://blogs.msdn.com/sqltips/archi..._Mirroring.aspx
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||As I had said, I hadn't really worked on it.. I guess we will have to wait
for reply from someone else
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

Client-Side Database Mirroring Details

Hi,
I've been tasked with investigating different methods of achieving database
redundancy.
In doing so, I'm currently in the process of writing a test application to
prove that Database Mirroring in SQL Server 2005 is a suitable solution.
Of the established requirements I'm finding difficulties with the following:
1. Identifying the name/instance of SQL Server my application is
currently connected to.
2. Identifying the name/instance of SQL Server cached as the
failover partner, and confirming it state at any time.
3. Identifying the name/instance of SQL Server cached as the
witness, and confirming it state at any time.
4. Forcing a failover via my test application.
I've already got some of this functionality via sp_dbmmonitorresults, but
this relies on the Server Agent running and also adds to the network traffic
.
I was hoping for a solution using calls to the SQL Native Client.
I’m using SQL Server 2005 Enterprise Edition SP1.
My application is written in Visual C++ 6 as is the product this is intended
for.
Also, if anyone knows of any good links to web pages or forum topics with
regards to methods of implementing database retry/reconnect client-side when
SQL Server fails over, I would be grateful. I’ve implemented this
functionality already by deriving my own version of CDatabase, but I’m ope
n
to new suggestions.
Thanks!On Thu, 27 Jul 2006 05:43:03 -0700, GMouzourou wrote:

>Hi,
>I've been tasked with investigating different methods of achieving database
>redundancy.
>In doing so, I'm currently in the process of writing a test application to
>prove that Database Mirroring in SQL Server 2005 is a suitable solution.
>Of the established requirements I'm finding difficulties with the following
:
> 1. Identifying the name/instance of SQL Server my application is
> currently connected to.
> 2. Identifying the name/instance of SQL Server cached as the
> failover partner, and confirming it state at any time.
> 3. Identifying the name/instance of SQL Server cached as the
> witness, and confirming it state at any time.
> 4. Forcing a failover via my test application.
>I've already got some of this functionality via sp_dbmmonitorresults, but
>this relies on the Server Agent running and also adds to the network traffi
c.
>I was hoping for a solution using calls to the SQL Native Client.
Hi GMouzourou,
For the first three questions, have you looked at the system management
views? More precisely, the column mirroring_role_desc in sys.databases,
and all of the columns in sys.database_mirroring and
sys.database_mirroring_witness.
For the last question, you can manually force a failover with the
command
ALTER DATABASE dbname SET PARTNER FAILOVER;
If you want to test automatic failover, try shutting down the server:
SHUTDOWN WITH NOWAIT;
Hugo Kornelis, SQL Server MVP

Client Side Redirect - Database Mirroring

Hi All,

My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)

I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.

Principal - SQL 2005 Enterprise Edition, SP1

Mirror - SQL 2005 Enterprise Edition, SP1

Witness - SQL 2005 Express, SP1

I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors?

(NB, the user running the code is a sys admin on all 3 servers)

Thanks in advance.

Imports System.Data.SqlClient

Public Class Form1

Dim DCTADS As New SqlClient.SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"

DCTADS.Open()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cmd As New SqlClient.SqlCommand

cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"

cmd.Connection = DCTADS

Dim dr As SqlClient.SqlDataReader

dr = cmd.ExecuteReader

MsgBox(DCTADS.DataSource.ToString)

If dr.Read Then

MsgBox(dr("fulldesc").ToString)

End If

dr.Close()

End Sub

End Class

Here is more detailed discussion for client redirect (http://msdn2.microsoft.com/en-us/library/ms366348.aspx). From the code I did not see any problem. One thing to check is to make sure the TCP/IP and "allow remote connection" are enabled on both principal and mirror because by default these two settings are disable after SQL server is installed.

Client Application and SQL Mirroring

I have an application where the SQL Database is configured within (I give it
the server name, database name and SQL access info). I did not write the app
and was wondering if it could be used with SQL mirroring high availability
without having to reconfigure the client app.
I skimmed over the link 'SQL Server 2005 Books Online Overview of Database
Mirroring' http://msdn2.microsoft.com/en-us/library/ms189852.aspx and still
am not clear about how the client application sees the mirrored database on a
failure when using the top level mirroring feature - High Availability.
Thanks
New2SQLHi
From www.connectionstrings.com
/*
If you connect with ADO.NET or the SQL Native Client to a database that is
being mirrored, your application can take advantage of the drivers ability
to automatically redirect connections when a database mirroring failover
occurs. You must specify the initial principal server and database in the
connection string and the failover partner server.
Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial
Catalog=myDataBase;Integrated Security=True;
*/
"New2SQL" <New2SQL@.discussions.microsoft.com> wrote in message
news:189496E6-29DC-484B-961F-F8E895363558@.microsoft.com...
>I have an application where the SQL Database is configured within (I give
>it
> the server name, database name and SQL access info). I did not write the
> app
> and was wondering if it could be used with SQL mirroring high availability
> without having to reconfigure the client app.
> I skimmed over the link 'SQL Server 2005 Books Online Overview of Database
> Mirroring' http://msdn2.microsoft.com/en-us/library/ms189852.aspx and
> still
> am not clear about how the client application sees the mirrored database
> on a
> failure when using the top level mirroring feature - High Availability.
> Thanks
> New2SQL
>