SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 High Availability


SQL Server 2005 High Availability

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62454 Visits: 19102
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/sqlserver2005highavailability.asp

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2040 Visits: 1222

Thank you, Steve.

You mention read-only queries once or twice.
How would you go about creating a separate server for running reporting?

TIA,
Henrik Staun Poulsen,
Denmark





Kevin Martin
Kevin Martin
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 192

Your article mentions using log shipping as a solution for failing over to a remote location. This was the strategy that I implemented, but after encountering a problem with log shipping, Microsoft Support is strongly encouraging me to start using Database Mirroring instead. Personally I am not quite ready to start applying SP1 to my production servers so I will be sticking with log shipping, but I just thought you might want to know the party line is from Microsoft Support.


Ian Yates
Ian Yates
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1822 Visits: 445

I haven't used log shipping yet as it was previously only included in the enterprise/developer version of SQL 2000. We are about to set up a small accountant's office with a new server, etc (he is a friend of mine). Their main line of business app currently has a clunky MS Access DB backend but the new version coming out very soon is based upon SQL Server (lets hope they didn't just port the code and still use things like row pointers and cursors everywhere!)

Anyhow, there will also be an offsite Windows server using DFS to provide a nice near realtime backup of key parts of their filesystem. They will have SQL 2005 Workgroup (from Small Business Server R2) and probably SQL 2005 Express on the remote server.

Can we use log shipping in the case? That would be sending from the Workgroup edition to an Express edition?

If we use log shipping, does this impact on the regular backup strategy - ie, if I take a full backup of the DB to put to tape each night will it upset log shipping? As I said, I haven't used log shipping yet but did read quite a bit about how it works a while ago. It seemed to me that it automated the process of backing up a transaction log, sending it over the network and applying it at the remote server without needing scripts. Is it more clever than this - it would have to be to not upset the backup strategy??

Thanks for advice. Good article btw - nice to see the options considered from a management point of view as that's how we usually have to explain things to the customers to eventually get their dosh





Yelena Varshal
Yelena Varshal
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4582 Visits: 595

Nice!

I would add the following points in regards to High Availability:

- REQUIREMENTS (in Upper Case) document that specifies exactly what do the want

- Recovery Model

- DRP

- Virtual Server (as in Virtual Server software, not a cluster) can be deployed in 10 min from an image with all SW installed, just restore a last backup of the database.

- A DRP machine with OS and Database server may even stay shut down waiting for any database or app to be copied /restored. Financial benefits that you may have with DRP machine is that it is only one server standing by for many production servers.




Regards,
Yelena Varshal

dmanju
dmanju
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 12
Nice article. Just wanted to point out something.. In Logshipping when you mentioned that it could be used for reporting or as a read-only database, when ever the logs need to be applied, users are disconnected from the database. So, in most practical situations, the secondary database is not something users would want to use.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62454 Visits: 19102
A few answers for you and thanks for the posts.

Log shipping can impact the reports run on a seconday server. If you are restoring frequently, then either users will be kicked off or need to be. This is probably best suited for batch restores, like a series of restores once every 4 or 5 hours to allow users to work on the db in between. In an environment where you want to minimize data loss and are moving logs every 1-5 minutes, reporting probably isn't going to work well.

I think log shipping will work with Workgroup, but I don't believe it will work with Express. If you have a passive server receiving the logs, you shouldn't need a license for it, so I'd use a 2nd copy of Workgroup.

As far as the regular backup strategy. with log shipping you don't do the backups with the primary server. Since all logs are sent to the remove server(s), that is all you need. Be sure you maintain a copy of those logs somewhere in case you have issues with the secondary.

Database mirroring is the "hot" technology now and a high point of SQL Server 2005. Therefore it's being pushed. It supposedly is very stable, but you need to test it and if you are not using SQL Native Client or a version of ADO.NET that supports it, you cannot get automatic failover. DM is essentially transactional log shipping, moving over changes almost like transactional replication, at least that's what it looks like, and applying them to the remote database. We may set it up here at some point and do some testing, but for now if you can't duplicate hardware and test well, I'd stay away. Course if you can't do that then lots of your DR/HA plans are a mess.

On reporting. I like the idea of two secondary servers, one for DR/HA, one for reporting. It does depend on your needs. If you can tolerate slightly stale data, like 1/2 a day or something, delay the restores on one server and then complete them all overnight or during lunch. This gives you a fairly recent copy of the data good for most people.

The Database mirroring/periodic snapshots works, but it has outages as well. Just like log shipping.

If you need realtime reporting, with fairly recent data then replication is your best choice. Move the data needed to another server or 2 and share the load between them.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Aidy-344587
Aidy-344587
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 43

Steve,
Any thoughts on cost effective HA plans when Vitualisation and SANs are in the mix?
What recommendations would you make if the windows server is virtual and hence also the SQLserver? Further, if the drive holding the SQL data is on a replicated SAN would it be better to let the SAN software take care of the replication at a block level to a DR site (tempdb excluded). A Read/write production and a Read only warm standby would aleviate the log shipping problems mentioned above.

The HA plan you choose depends on the disruption you are expecting: Hardware Failure (NIC, Disk), OS repair/updates, Data corruption (by User / other) , network etc.





Wayne West
Wayne West
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2628 Visits: 3702
If you're using mirroring, from what I understand, you would need to do a snapshot of your primary database as the mirror is in recovery mode while the primary is up and thus can't be accessed.

There's a series of web casts on the MS web site here. I liked the first one, but it is kinda long. Still, excellent introduction. I haven't gotten to any of the other casts yet.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2040 Visits: 1222

Wayne,

Thank you for your suggestion. I'm not sure I like mirrowing, as it is not simple. Currently we have problems with the backup/restore process we have, as restores have started taking a bit toooooo long. So suggestions are still most welcome.

Best regards,

Henrik





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search