SQL disaster recovery solution over WAN

I read this document

http://www.solarwinds.com/support/failoverengine/docs/SQLFailoverOptions.pdf

I'm looking for an affordable solution to replicate my whole monitoring system on a remote site.

We have FOE for the NPM part.

Is there anybody who has some experience or knowledge of proposed options (pro, cons, integration level with NPM, cost etc)?

Thanks,

Daniele

Find more posts tagged with

sql_clustering

Failover

Accepted answers

mezdem

Log shipping does work very well and is the cheapest option, a bit of work to setup and maintain. This would allow you to have a full coy of your data on site 2, but check you npm database as I know that shipping my NPM transactions would be a huge mine is around 110 transactions/per second (average) so I would not do this for mine. You have 30 days to get your primary server working before you have to buy a license! (see MS notes below)

Other types we use are SQL replication - this can be bulk or transaction based - good option but you need a 2nd sql license so ££ unless you have this on site 2.

sql clustering is good but pricy and depends what infrastructure you have in place. Basically if your data is stored on a network area or SAN then that SAN resource will need to available for the 2nd sql (failover) node to see - you need to work out what outage would cause you to lose the sql server in the first place and whether that outage would of taken your data resource offline too! as sql failover is primarily for physical server failures, not data failures.

You will also require a 2nd instance of orion and poller ready to take over/attached to your replica database - keep upgraded etc...

Passive Servers and Failover Support

One new feature offered in SQL Server 2005 is enhanced failover support. For example, two or more servers running SQL Server can be configured so that if one server fails, its processing will be picked-up, recovered, and continued by the other server. SQL Server 2005 offers three types of failover support:

Database mirroring is a new SQL Server 2005 technology for increasing database availability. It transfers transaction log records directly from one server to another and can quickly fail over to the standby server.
Failover clustering is a process where the operating system and SQL Server 2005 work together to provide availability in the event of an application failure, hardware failure, or operating-system error. Failover clustering provides hardware redundancy through a configuration in which critical resources are transferred automatically from a failing machine to an equally configured server to help ensure continuity of service.
Backup log shipping increases the availability of a SQL Server by automatically copying and restoring the database's transaction logs to another database on a standby server. Because the standby database receives all changes to the original database, it is an exact duplicate of the original database—out of date only by the delay in the copy-and-load process. You then have the ability to make the standby server a new primary server if the original primary server becomes unavailable. When the original primary server becomes available again, you can make it a new standby server—effectively reversing the server roles.

When doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license if the number of processors in the passive server is equal to or less than the number of processors in the active server. The passive server can take the duties of the active server for 30 days. Afterwards, it must be licensed accordingly.

Database mirroring and failover clustering are available for SQL Server 2005 Standard Edition and SQL Server 2005 Enterprise Edition. Backup log shipping is available in SQL Server 2005 Workgroup Edition, SQL Server 2005 Standard Edition, and SQL Server 2005 Enterprise Edition.

All comments

bshopp

I have seen many deployments. A lot depends on the skill set available in house. Ex. are you working with your DBA's or do you maintain Orion DB yourself.

NeverFail who we partnered with have an offering just for the Orion DB, see here.

mezdem

Other types we use are SQL replication - this can be bulk or transaction based - good option but you need a 2nd sql license so ££ unless you have this on site 2.

You will also require a 2nd instance of orion and poller ready to take over/attached to your replica database - keep upgraded etc...

Passive Servers and Failover Support

Database mirroring is a new SQL Server 2005 technology for increasing database availability. It transfers transaction log records directly from one server to another and can quickly fail over to the standby server.
Failover clustering is a process where the operating system and SQL Server 2005 work together to provide availability in the event of an application failure, hardware failure, or operating-system error. Failover clustering provides hardware redundancy through a configuration in which critical resources are transferred automatically from a failing machine to an equally configured server to help ensure continuity of service.
Backup log shipping increases the availability of a SQL Server by automatically copying and restoring the database's transaction logs to another database on a standby server. Because the standby database receives all changes to the original database, it is an exact duplicate of the original database—out of date only by the delay in the copy-and-load process. You then have the ability to make the standby server a new primary server if the original primary server becomes unavailable. When the original primary server becomes available again, you can make it a new standby server—effectively reversing the server roles.

ddmt

Thanks for your replies.