Up to now we have gotten by without having any local DR copies of servers (if a sql server goes down we are usually able to get it back in less than 3 hours). But I want more now. I want to trim the "down" window to no more than 5 or 10 minutes. (Immedate failover would be nice but is not an essential requirement. The essential requirement is to loose no data!)
I have a spec of knowledge in these areas:
SQL 2005 Clustering (requires approved hardware, quorum disk, etc. involved)
SQL 2005 Replicaiton
SQL 2005 Log Shipping.
SQL 2005 Database mirroring. ( needs three servers)
Which approach do you think is the most straightforward, sparing of hardware, yet reliable way to get us back up and running after a sql server failure.
TIA,
barkingdog
All of the above, none of the above, some combination of the above. It isn't a yes or no answer and it isn't a one size fits all. Each option has pros and cons. I teach a 5 day hands on class on High Availability and at the end of about 46 hours of instruction, we cover about 1/4 of the topic. It gives you a basic foundation for understanding the pros and cons of each technology as well as how it fits into an environment. The hard part is the implementation. There are 800+ page books on the SQL Server HA stuff from SQL Server 2000. I'm working on one for SQL Server 2005 that is probably going to weigh in at a two volume set of almost 1700 combined pages. (About 800 pages of it are already done and I still have at least 3 technologies and 17 subjects to cover.) It depends upon your environment, business requirements, any needs for the application etc. If you can be a little more specific than "I want better availability", it might be possible to more closely target something.|||We want to consolidate several sql 2000 development servers into a single sql 2005 server. BUT if the sql 2005 server goes down, until we can fix it, the developers are going to have a lot of free time on their hands! The goal here is to minimize the amount of down time (say less than 20 minutes) so our developers can get back on the road ASAP. Of course, if the problem is hardware-related we could be down for a lot longer than 20 minutes, implying another server (maybe even a virtual server?) should be part of the environment.
Barkingdog
|||Based on the fact that it is a development environment, I would simply drop in log shipping and be done. It would require developers to reconnect to a different SQL Server instance that might be missing a small amount of data, but that is unimportant in a dev environment. It gives you basic redundancy without going overboard. And you also have to keep in mind that everything in a dev environment should be able to be recreated by a developer, so you are simply looking fdor redundancy to reduce the amount of stuff that needs to be recreated in the event of a failure.|||
I am reading about log shipping. It seems to be very focused on "one database at a time" type operations. One way is to use SSMS, right-click the database of interest, go to Properties, indicate it is the primary database.... and do this for each database on the server.
I want to apply log shipping to ALL the databases at once, not one step at a time. Is there any accomodaiton to do this?
TIA,
Barkingdog
|||I have just finished my first pass reading about Log Shipping. In point of fact, while the concept is simple, the implementation has lots of details, is error prone, and requires changes to the way things normally work around here.
My greatest concern with all such approaches is that we will set it up, it will run fine on auto pilot, we will forget about the details, and then the emergency hits. Since we are no longer "sharp" it is very possible that we could forget to do something or do the wrong something, undermining the purpose of LS. (I know ... have good documentation....) I have the same issue with other "Disaster Recovery" type products. too.
Barkingdog
|||Since you mentioned Database Mirroring, my assumption is SQL Server 2005 for all of this. You don't have to click through SSMS. You can script the log shipping implementation. It will require that you connect to two different instances and execute the portions of the script in order, but you don't have to click through a GUI.
As for the other side, no one is going to be able to help with that issue. High Availability isn't technology in a box. It requires people, processes, and technology to work together to achieve your availability goals. Deploying technology without well tested and documented procedures for accomplishing a failover and possibly a failback is just a waste of time. The technology is only as good as the processes that are in place to ensure the technologies can meet your business needs. The processes are only as good as the documentation that you have in place. The processes are only as good as the people implementing and performing the processes. You can't have one without the other.
Implementing a high availability solution is 5% about technology. The other 95% is all about processes and failover procedures.
|||Excellent answers thanks.
Your advise about failover procedures is sage-like. I do find that mirroring is more to my "liking". And why it may not be needed on a developer box it would be good practice ground for our sql 2005 production servers (which also need falover).
Barkingdog
sql