csm setup for active/backup sql server

[zhichao]

Hi

There are two sql servers to be in active/backup mode.

The users' requirement are:

1. The csm should direct all traffics to server1. When server1 is down, it should direct to server2 and stay with server2.

2. Even if server1 is back, it should still stay with server2.

This is to prevend database corruption.

Any idea how to do these?

I am thinking to use:

1. weight 0 on server2 to make it backup. (I tried backup server command, but it did not work... And CCO does not have good docu on this)

2. use sticky with network mask /0 to make all users always stay with one active server and never preempt.

Any other suggestion?

[Gilles Dufour]

I don't think you can do this with standard commands.

The backup or weight solution would work for the first part but will not garuanteed that server2 stays active even if server1 comes back up.

I think you need a way to keep server1 down if it fails.

You could use a probe and set the 'recover' option to 65535 and the 'failed' interval to 65535 so making it impossible for server1 to come back up without manual intervention.

The danger is if both server1 and server2 go down.

Regards,

Gilles.

[zhichao]

Thanks!

Thats a good solution! I will try it.

For q1, someone suggested me to use 'backup real' command. I could not make it work, so I used weight 0.

Is there any problem with this weight 0 command?

Regards

[zhichao]

Hi

My solution worked :)

I used

sticky 10 netmask 0.0.0.0 address source timeout 65535

vserver SQLSERVER

virtual 172.25.196.199 any

serverfarm SQLFARM

sticky 65535 group 10

Thanks

[Gilles Dufour]

indeed, that's a a good solution.

I'm glad you could find it yourself.

[rraja2006]

Dear zhichao:

We are trying to do something similar.

Could you post your relevant config for the SQLFARM and real servers-- including the probe part? We would appreciate it.

Thanks

Raja

[zhichao]

module ContentSwitchingModule 9

ft group 1 vlan 500

priority 100 alt 10

preempt

!

vlan 196 client

ip address 172.25.196.254 255.255.252.0 alt 172.25.196.253 255.255.252.0

gateway 172.25.196.1

!

vlan 600 server

ip address 172.25.196.254 255.255.252.0 alt 172.25.196.253 255.255.252.0

!

probe SQL_PROBE tcp

interval 2

retries 1

failed 2

port 4000

!

serverfarm SQLFARM

nat server

no nat client

real 172.25.196.201

weight 0

inservice

real 172.25.196.200

weight 100

no inservice

probe SQL_PROBE

!

sticky 10 netmask 0.0.0.0 address source timeout 65535

!

vserver SQLSERVER

virtual 172.25.196.199 any

serverfarm SQLFARM

sticky 65535 group 10

replicate csrp sticky

replicate csrp connection

persistent rebalance

inservice

Hope it is useful.