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 (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)

Group: Administrators
Points: 281172 Visits: 19905
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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3489 Visits: 1255

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
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 213

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
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8912 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16198 Visits: 608

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-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 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 (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)

Group: Administrators
Points: 281172 Visits: 19905
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 (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10022 Visits: 3710
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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3489 Visits: 1255

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