connecting 2 SQL cluster enviroments

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

connecting 2 SQL cluster enviroments

Author

Topic

wakko303
Starting Member

7 Posts

Posted - 2008-04-11 : 05:53:39

Hi All,

Do you have an idea for the following case?:
Right now we are planning to connect two data-centre (WEST & EAST).
Everthing will be build from scratch.
Both are running a 2-node single instance sql 2005 A/P cluster.
The instances host critical reporting data (emergency).
We would like to transfer the data from WEST to EAST.
A minor delay in the EAST is acceptable. Which technology would you recommend to transfer data from the WEST to EAST?

Log-Shipping (I think the problem is that the target is in a recovery mode and not accessable for users, but there should be snaphsot mode for Read-Only)I also heard that Log-shipping will be not supported anymore in future releases.

Database-mirroring (Is there an issue, if a sql 2005 node is member of cluster and principal/mirror node at the same time?

Thanks for ideas

tomy74
Starting Member

32 Posts

Posted - 2008-04-11 : 09:03:46

Hi Wakko..,

You cannot use database mirroring within a cluster, but you may consider using database mirroring as a method for creating a hot standby for a cluster instance database. If you do, be forewarned that because a cluster failover is longer than the timeout value on database mirroring, a High Availability mode mirroring session will react to a cluster failover as a failure of the principal server. It would then put the cluster node into a mirroring state.

For more details check the following URL where u can find a table about Comparing SQL Server 2005 High Availability technologies:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Regards,

Tarek Ghazali

SQL Server MVP
http://www.sqlmvp.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-11 : 09:47:44

Agreed . You shouldn't use mirroring within Cluster. If it is different location , then I would recommend using Aynschronous Mirroring which will support if something happens to EAST Datacenter.
Tell me What edition of SQL Server you have? and distance between datacenter?

wakko303
Starting Member

7 Posts

Posted - 2008-04-11 : 09:49:35

Hi Tarek,

thanks for your response.
Meanwhile I found the following:

[url]http://msdn2.microsoft.com/en-us/library/ms191309.aspx
[/url]

So my understanding from this article is, that Mirroring between two cluster environments is possible.

Best

Abir

wakko303
Starting Member

7 Posts

Posted - 2008-04-11 : 09:57:49

Sodeep,

SQL Server 2005 Enterprise Editions 64-Bit
The distance between the locations: around 173 mi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-11 : 10:03:40

Then you have the benefit of doing Asynchronous Mirroring. The redo-phase for log restore will be really good with Enterprise Edition as it uses multiple threads to reduce latency. Synchronous Mirroring(Automatic Failover) won't be good in this scenerio.

tomy74
Starting Member

32 Posts

Posted - 2008-04-11 : 10:10:53

Hi Abir or wakko,

Please note that u have implemented A/P cluster so while the cluster is unavailable, the database is unavailable.

Regards,

Tarek Ghazali

SQL Server MVP
http://www.sqlmvp.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-11 : 10:22:45

Sorry didn't get it tommy.Can you please elaborate?

wakko303
Starting Member

7 Posts

Posted - 2008-04-11 : 10:28:50

Thank you for your ideas.
The second data-centre is for DR and reporting purposes.
So I think we can live with some delay and asynchronous mode.

>>implemented A/P cluster so while the cluster is unavailable, the database is unavailable<<

Do you mean when the Active fails over to the Passive in the main data-centre, the mirroring is unavailable?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-11 : 10:35:34

If its reporting purposes then database mirroring is bad option bcoz your database will be in NoRecovery mode in Secondary Server. Well you can apply database snapshot,but from my personal experience don't do that as it adds overhead and more disk space.

Have you Thought about Peer-to-Peer Replication or Transactional Replication?

tomy74
Starting Member

32 Posts

Posted - 2008-04-11 : 10:37:24

Hi sodeep,

You cannot configure mirror DB within active passive cluster =>
Node B cannot be mirror database, but the cluster (A and B) it can be used as principal database for external mirror outside the A/P cluster.

Abir or wakko,

It's possible to implement it on A/A cluster but u have to add witness (PC/server) for autotmatic failover and I wouldn't design it that way. I don't like having multiple
failovers triggered by a single failover, but it does accomplish a minimal downtime along with multiple failover options. check my first reply.

Regards,

Tarek Ghazali

SQL Server MVP
http://www.sqlmvp.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-11 : 10:41:25

I am talking about Mirroing between 2 different location.

tomy74
Starting Member

32 Posts

Posted - 2008-04-11 : 10:47:38

Hi Guys,

In the first reply I have explained about the restriction inside the cluster itself, for Abir scenario for 2 locations and Both are running a 2-node single instance sql 2005 A/P cluster. she can use the location A as principal and the location B as mirror,

Regards,

Tarek Ghazali

SQL Server MVP
http://www.sqlmvp.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-11 : 11:30:01

Thats my point of understanding too.

wakko303
Starting Member

7 Posts

Posted - 2008-04-11 : 11:31:07

>> He can use the location A as principal and the location B as mirror,

2 questions about the mirror location B:

1.) Is the mirror available for reporting access or is it in recovery mode?

2.) location B nodes can be mirror and cluster member at the same time.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-11 : 12:00:11

1)No, It will be on NORECOVERY mode(can use database snaphot but don't recommend)for reporting purposes.

2)Mirroring is at database level. Cluster will be working on server level.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-12 : 01:00:10

You can create db snapshot for reporting on DR site.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources