Cluster or Mirror

Hi,
I'm currently looking in the possiblities for a SQL server install. The web
project needs a SQL database with a high availability. Speed and size are not
that important as the load will be medium (especially for the new enterprise
class servers), but it cannot eb offline for more then minutes. Assuming
there is suffient budget what option would be preferred:
HP DL380 cluster with SAN : diskset in SAN RAID 1+0 with SQL enterprise and
SQL virtual server setup
OR
2x Fully redundent HP DL380's with own diskset (RAID 1+0), one active SQL
Std, and one passive. Replication using DoubleTake or Neverfail alikes.
The fail-switch should also be automatic and should react within max 5
minutes.
Any idea's pro + cons?
Thanks
Have you looked at Log Shipping as an alternative?
Regards
Darryl Pollock
Squirrel Consulting
www.remotesquirrel.com - Performance Monitoring - Anytime ! Anywhere!
"christophe" <christophe@.discussions.microsoft.com> wrote in message
news:250E4D88-6464-4E88-80AA-E0D8262B4CD1@.microsoft.com...
> Hi,
> I'm currently looking in the possiblities for a SQL server install. The
> web
> project needs a SQL database with a high availability. Speed and size are
> not
> that important as the load will be medium (especially for the new
> enterprise
> class servers), but it cannot eb offline for more then minutes. Assuming
> there is suffient budget what option would be preferred:
> HP DL380 cluster with SAN : diskset in SAN RAID 1+0 with SQL enterprise
> and
> SQL virtual server setup
> OR
> 2x Fully redundent HP DL380's with own diskset (RAID 1+0), one active SQL
> Std, and one passive. Replication using DoubleTake or Neverfail alikes.
> The fail-switch should also be automatic and should react within max 5
> minutes.
> Any idea's pro + cons?
> Thanks
|||yes, but the data can only be out of sync for max a few minutes and from what
I read replication is faster for something like that. I also need an
automated switch to the standby server in case of a failure of the primary
server. The application only knows 1 SQL server connection.
"Darryl Pollock" wrote:

> Have you looked at Log Shipping as an alternative?
> Regards
> Darryl Pollock
> Squirrel Consulting
> www.remotesquirrel.com - Performance Monitoring - Anytime ! Anywhere!
>
> "christophe" <christophe@.discussions.microsoft.com> wrote in message
> news:250E4D88-6464-4E88-80AA-E0D8262B4CD1@.microsoft.com...
>
>
|||Hi
Just remember, Cluster or disk/volume replication level does not protect you
against database/disk corruption. The corruption is faithfully propogated to
the other drives in the case of replication and is on the single copy on the
SAN.
For SQL 2000, Microsoft Windows Clustering is the way for the least downtime
and no human intervention is required. Clusters typically fail over in <20
seconds, depending on how many transactions need to rolled forward/back.
Regards
Mike
"christophe" wrote:
[vbcol=seagreen]
> yes, but the data can only be out of sync for max a few minutes and from what
> I read replication is faster for something like that. I also need an
> automated switch to the standby server in case of a failure of the primary
> server. The application only knows 1 SQL server connection.
> "Darryl Pollock" wrote:
|||I would argue that the time required to failover is a function of how long
it takes for the transaction log to be applied from the failed node to the
failed over node.
For most production systems with VLDBs you should be dumping the log very
frequently, ie under 5 minutes.
While 20 s is an often quoted statistic, I think 1 - 2 minutes might be more
realistic.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:0B6AD0FE-AFC4-46F3-AAAD-031C1F179600@.microsoft.com...
> Hi
> Just remember, Cluster or disk/volume replication level does not protect
you
> against database/disk corruption. The corruption is faithfully propogated
to
> the other drives in the case of replication and is on the single copy on
the
> SAN.
> For SQL 2000, Microsoft Windows Clustering is the way for the least
downtime[vbcol=seagreen]
> and no human intervention is required. Clusters typically fail over in <20
> seconds, depending on how many transactions need to rolled forward/back.
> Regards
> Mike
> "christophe" wrote:
what[vbcol=seagreen]
primary[vbcol=seagreen]
The[vbcol=seagreen]
size are[vbcol=seagreen]
Assuming[vbcol=seagreen]
enterprise[vbcol=seagreen]
active SQL[vbcol=seagreen]
alikes.[vbcol=seagreen]
5[vbcol=seagreen]

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 Failover Problems

Hi,

I encountered 2 problems when my application reconnects to the mirror database after the principle database is not available.

My Connection String:

Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=TestFailover;Persist Security Info=True;User ID=sa;Connect Timeout=45

Firstly, the following exception will be prompted when failover occurs:

A transport-level error has ocurred when received results from the server. The specified network name is no longer available

Q1: Is it normal to have this exception? Otherwise, how can I avoid this exception?

Secondly, I tried to capture the exception and retry the operation, then failover successed but the time is very slow. A simple update operation takes about 2 mins to complete which can be finished within a second under normal situation. I tried to set the Connect Timeout to 30s, but the application pending for long long time.

Q2: How can I improve the time need for failover?

A1: I don't know if you can avoid the exception.

A2: What version of the software are you using? Sounds like there is a timeout that is taking its time, either a connection timeout or a Tcp timeout. How are you testing the redirect? Try crashing the principal server with a SHUTDOWN WITH NOWAIT command.

Thanks,

Mark

|||

If you are on RTM, then upgrading to SP1 should fix your problem. In RTM a dead machine caused a stall in TCP waiting for a default TCP timeout of 30 secs which is longer than the default login timeout of 15 secs.

Thanks to Chris for the information.

Mark