Clustering and Disaster Recovery
-
Anyone have experience with Double-Take software from NSI Software ?
http://www.nsisw.com/pro/doubletake/
Seems like an improvement on Log Shipping for upto the minute Disaster Recovery. We are planning to use MSCS clusters for SQL 2000, but what if the server room caught fire
?? Their software replicates at the file level but how do they maintain ACID ? Also MS has a solid partnership with them in the now hot NAS market segment. Replies greatly appreciated.-srini
-
I personally don't have any experience with them but I would also investigate replication as it supports the two phase transaction to maintain ACID copliance on all tables that are replicated.
Wes
-
Which replication ? Regular Transactional does not need 2PC, only immediate updating needs it. Also if 2-way trans replication along with immediate updating is used with 2PC, i cannot republish the subscribed data. This is our proposed configuration and products like SQL-UP from Incepto http://www.incepto.com will not work as they are based on replication. thanks anyway
-srini
-
Yeah, we have and I hate it and am looking for something new. We have had issues with it not failing over. Sometimes it fails over correctly--other times it doesn't start the SQL services on the backup machine.
Also, since the data is mirrored at the file level, you could have an inconsistent transaction if the primary machine abends in the middle of a transaction. This happened once when it failed over in the middle of a large transaction and only part of it was on the backup machine. As such, I don't think it does anything to maintain ACID.
Shawn
-
Sorry, I also forgot to mention that the latest version of DT had a known issue with W2K and Norton Antivirus and SQL Server 2K that cause the server to run out of memory and abend. Can't remember the details off the top of my head but I can get it off of our server bible database.
Shawn
-
thanks shawn. Looks like log shipping is a safer option
-sk
-
looks like someone comes from the novel world 😉 Srini, let us know what you end up going with. Replication can be stable if setup correctly.
Good luck,
Wes
-
quote:
looks like someone comes from the novel world 😉 Srini, let us know what you end up going with. Replication can be stable if setup correctly.Good luck,
Wes
Yes i know but we are thinking of 2-way transactional replication with immediate updating and you cannot republish in this configuration. Going for the log is better in this case. Will publish our final design
-srini
-
We tried replication under SQL 6.5, and, as I recall, replication would not push DDL (i.e., table and stored procedure) changes from the source server to the destination server. I’m thinking that the same behavior exists in SQL 7.0 and 2000. (Someone please correct me if I’m wrong.)
We tested log shipping in SQL 2000, but it looked like a nightmare to manage if you have a large number of databases. In our environment, the Log Shipping software would backup a transaction log, copy the backup somewhere else (which can take a lot of time and disk space), and then restore the transaction log. There just seemed to be a lot of places to look if something went “bump in the night.” Besides, you can only apply the logs while no one is connected to the database--the “spid whacker” that comes with Log Shipping just didn’t seem to suit our needs.
To create a warm standby server, we now do the following:
- Restore all databases on the warm standby server just once--the databases on that server are left in standby (read-only) mode, which gives our Developers a copy of Production to use as a playground for testing read-only queries (especially reports) with large volumes of realistic data
- Run a full database backup on all databases of the live server nightly
- Run transaction log backups on the live server on an hourly basis throughout the day--this backups get written to a third server so that we can restore them during the day to the warm standby server, if necessary in order to make it the live server in the event of a failure or a "data oops"
- Restore the transaction log backups from the live server to the warm standby server on an nightly basis around midnight
- Resync the logins and default databases on the warm standby server with the live server
This process seems fairly reliable. The last time we had to restore the warm standby server was when we created it over 4 months ago.
-
Thanks for the insight.
Any thoughts as to how the shadow copy features of 2003 Server might play into this scenario or failover schemes in general?
What are the practical applications of this new feature with respect to SQL Server strategies?
TIA
-
quote:
We tried replication under SQL 6.5, and, as I recall, replication would not push DDL (i.e., table and stored procedure) changes from the source server to the destination server. I’m thinking that the same behavior exists in SQL 7.0 and 2000. (Someone please correct me if I’m wrong.)We tested log shipping in SQL 2000, but it looked like a nightmare to manage if you have a large number of databases. In our environment, the Log Shipping software would backup a transaction log, copy the backup somewhere else (which can take a lot of time and disk space), and then restore the transaction log. There just seemed to be a lot of places to look if something went “bump in the night.” Besides, you can only apply the logs while no one is connected to the database--the “spid whacker” that comes with Log Shipping just didn’t seem to suit our needs.
To create a warm standby server, we now do the following:
- Restore all databases on the warm standby server just once--the databases on that server are left in standby (read-only) mode, which gives our Developers a copy of Production to use as a playground for testing read-only queries (especially reports) with large volumes of realistic data
- Run a full database backup on all databases of the live server nightly
- Run transaction log backups on the live server on an hourly basis throughout the day--this backups get written to a third server so that we can restore them during the day to the warm standby server, if necessary in order to make it the live server in the event of a failure or a "data oops"
- Restore the transaction log backups from the live server to the warm standby server on an nightly basis around midnight
- Resync the logins and default databases on the warm standby server with the live server
This process seems fairly reliable. The last time we had to restore the warm standby server was when we created it over 4 months ago.
SQL 2000 does push DDL changes in replication with some caveats though.Regardging your warm standby solution, how different is it from log shipping except for the "spid whacker" not working ?
-srini
-
srini_kris,
In function, our solution is virtually identical to log shipping, but the restores are handled in a single script, without copying backup files multiple times over the network. When something goes "bump in the night," I can find the error in a single log file instead of hunting through 40 databases in a GUI.
Jon
-
quote:
srini_kris,In function, our solution is virtually identical to log shipping, but the restores are handled in a single script, without copying backup files multiple times over the network. When something goes "bump in the night," I can find the error in a single log file instead of hunting through 40 databases in a GUI.
Jon
Thanks. We might implement log-shipping and it is helpful to know about problems b4 hand. However we only have 2 databases to worry about. Do you have replication (trans) in your configuration ?
-sk
-
No, we removed replication from our environment for the same reason--it can just be a real pain to locate the source of problems when there are many databases being replicated.
-
hmm, sounds like a problem. 2Way trans replication is a requirement for us
sk
Accepting all invitesViewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply