Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2005 High Availability Expand / Collapse
Author
Message
Posted Friday, May 19, 2006 5:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:37 PM
Points: 33,189, Visits: 15,329
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
Post #281549
Posted Wednesday, May 24, 2006 4:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:07 AM
Points: 1,364, Visits: 989

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




Post #282343
Posted Wednesday, May 24, 2006 7:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, May 23, 2014 3:02 PM
Points: 449, Visits: 95

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.

 

Post #282404
Posted Wednesday, May 24, 2006 7:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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 




Post #282424
Posted Wednesday, May 24, 2006 10:44 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:35 PM
Points: 3,475, Visits: 580

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

Post #282526
Posted Thursday, May 25, 2006 2:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2008 9:25 AM
Points: 103, 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.
Post #282940
Posted Sunday, May 28, 2006 9:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:37 PM
Points: 33,189, Visits: 15,329
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
Post #283320
Posted Friday, July 7, 2006 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 18, 2013 1:07 PM
Points: 1, 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. 

 




 

Post #292827
Posted Friday, August 31, 2007 2:56 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
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.
Post #395684
Posted Monday, September 3, 2007 4:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:07 AM
Points: 1,364, Visits: 989

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

 




Post #395818
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse