August 20, 2011 at 10:25 am
My company web application is using 8 databases. I need to create a warm standby.
Which method is better for so many databases - log shipping, replication or data mirroring?
I am a database developer and now I become an accidental DBA. I never create any warm standby before.
BTW, we use sql server 2008 R2.
I want to use window clustering but my boss said the company does not have money to buy a SAN box.
Thanks
August 20, 2011 at 11:32 am
Just to clarify:
Clustering is for HA (High Availability) - and wouldn't be a choice for DR unless you want to get into geographic clusters (which isn't really DR, but can be complimentary to a DR solution).
For a warm/hot standby system, either log shipping or database mirroring are the options you should look into. With log shipping, you have to 'ship' the transaction log backups to the other system to be restored, with database mirroring the transactions are sent directly.
Also, with database mirroring - you can setup your client connections with the partner setting which will allow the application to automatically access the mirrored database if it fails over. With log shipping - you have to change the connections or update DNS entries. However, you might have to do this either way.
One disadvantage to either approach is that it is per database. You would have to monitor the systems and if one database fails over - do you need all of the databases failed over and accessible on the same server? If you have code in one database that accesses objects in another database - then you need to make sure those databases all failover at the same time.
You also have two options with database mirroring - high safety, or high performance. With high safety you have a two-phase commit which requires the transaction to be committed on the mirror before it will commit on the primary. If the network connection between the two systems has any latency at all - this will not work and you would have to use high performance. High performance could lead to possible data loss.
And finally, with log shipping you can get access to the other database using either database snapshots (enterprise edition only) or STANDBY. You can even set it up to lag behind the production system so you can have a way of accessing data prior to any changes that were made that would need to be undone. With database mirroring you only have the option of using database snapshots to access the mirrored database - and there is no possibility of setting up a lag type system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 21, 2011 at 5:09 pm
Loner (8/20/2011)
I need to create a warm standby.
Do the standby databases need to be readable or are they purely standby? What are you trying to achieve here, DR or a HA solution?
Loner (8/20/2011)
I want to use window clustering
This implies you're looking for a HA solution!
Loner (8/20/2011)
but my boss said the company does not have money to buy a SAN box.
Don't need one!
There are many options for creating cluster suitable shared storage using open source Linux systems, a sort of basic version of a NetApp filer if you like!
Using iSCSI, multiple NICsetworks and MPIO you can build in storage performance and redundancy too!
See my article on Creating a 2 node virtual cluster on this site, it applies to physical nodes to.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply