SQL Server 2005 High Availability
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/sqlserver2005highavailability.asp
-
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
-
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.
-
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
-
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 Varsha
-
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.
-
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.
-
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.
-
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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
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
-
What are my options for a high availability system that requires regular roll out of schema changes. I am looking for an HA solution that allows hot fixing of SQL objects rather than just a fail over.
Example hot fixes might include new tables or altered tables and possibly the SPs to update/insert from these tables. I need to be able to roll out these updates with minimal or zero down time. All suggestions welcome.
Thanks,
Jules
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply