SQL 2005 Failover/Replication

[UberGeek1001]

Hey all. So we're replicatiing SQL 2005 between 2 locations. One is on the West Coast, the other on the East Coast. We have a server in each location and need to fail it over to the SQL instance on the West Coast when the server on the East Coast goes down. We're using a NetApp's cluster to store the data and we're running SnapMirror/Snapmanager to replicate the data between the West/East loations. The debate is: If server 1 goes down and fails over to server 2, the SQL data wouldn't be up to date since there would potentially be that lag on replication so some logs/transactions could be missed. However, there are 3rd party utilities that we can use to achieve this like products made by Red-gate and a few others. So...can anyone tell me if that's the direction we should be going and/or if there is TRULY an app that does real time replication/failover so IF server 1 goes down, it automatically fails over to server 2 like it should and there would be zero manual intervention like a restore. Or, would we have to be notified about the automatic failover, then come in and do a restore to bring the failed server up to speed? ANY HELP or SUGGESTIONS would be really appreciated guys/gals. Thanks in advance! :

 

[mrdenny]

You would need something that does synchronous replication between the Netapps. This would probably need to be provided by NetApp. Because of the time it takes to get from the West Coast to the East Coast you won't want to use sync to go that far. It's typically recommended that you use sync to go no further than 100 kilometers at the most. Any further and the time it takes to get from each system to another will be to long and will slow down the SQL Server.

The loss of a couple of blocks is normal when doing asyncronous replication between SAN devices.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[UberGeek1001]

Hey Denny...thanks for the reply. Maybe I am not asking the question right. On the NetApps side, we have the data replicating Synchronously with SnapManager/SnapMirror...so that's not the issue. The issue/question is: On the Server side, the Logs/Transactions that aren't in place would make the data "unhappy" wouldn't it? So, what we're in seek of is a true server to server replication, not replicating the NetApps Data, but replicating Synchronously with the servers so it mirrors the data and is up to date in the even of a failure. Does that make sense? Check out Red-Gate.com and let me know what you think. THANK YOU for our help man!

 

[mrdenny]

If you are using synchronous replication between the SAN on the east coast and the SAN on the west coast then the data on both SANs should be identical at all times.

This assumes that the SAN supports the concept of consistancy groups so that changes to all the drives are written across the WAN at the same time.

Data is written to the transaction log the instant the transaction is commited. With the SAN running in sync mode the transaction will be on the remote site as soon as the transaction is commited on the active site.

With sync replication in place, you should be able to build a geographically distributed cluster and have automatic failover between the sites.

If you aren't happy with the SAN replication, give SQL 2005's native database mirroring a try. It's quite a good solution, and it's free as it's already included with the product.

Database mirroring also supports automatic failover, but I believe this requires the Enterprise edition to have mirroring do the automatic failover. (This can easily be worked around with a little bit of T/SQL coding.)

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[UberGeek1001]

Okay...I'll run it past them. THANKS a lot for your help man!

 

[mrdenny]

No problem.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog