DR: Replication Vs. Log Shipping Vs. Clustering Vs. Database Mirroring........
Aug 8, 2006
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.
I am confuse and cant decide on how to setup high availability on our SQL 2005. Here's what on my mind and on resources list:
I plan to have mirroring on my SQL1 to SQL2 with the help of SQL3 as witness. So this would be automatic failover. My idea on mirroring is when SQL1 goes down, SQL3 would tell SQL2 to run and be the primary. It will automatically failover to SQL2. Right? My questions are:
1) How can I revert back to SQL1 once it is ready?
2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?
3) If number 2 is false (i hope so), how would the data be synchronize from SQL2 back to SQL1. Those transaction that were made while SQL1 is down.
4) How about the connection string from the web applications? Would it be automatically point to SQL2? We have load balancing setup in place, would this help web application connection to automatically point to SQL2?
Another setup:
We have SAN in place (not yet used, but is planning to use for this SQL thing), EMC to be specific. My question would be:
1) For SAN setup, the data storage would be centralize. So would that mean that SQL1 and SQL2 services will use the same data and log file from the SAN storage?
2) How would you call this setup then? Can this be clustering type of high availability? Will clustering work under load balancing setup? I believe mirroring is not possible here? Right?
3) How can I setup my 3 SQL servers with the same theory in mind: when SQL1 goes down, SQL2 will take over. Data will be synchronize when SQL1 is up and running again. With automatic failover and reverting back to primary.
I read so much topics about this, but the more I research, the more I get confuse.
Any suggestions, comments, advice is greatly appreciated!
I am developing an enterprise class solution using SQL Server 2005 and MS .NET v2 and am tying determine if SQL Server 2005 (which edition and if so how) would be adequate for my proposed solution. Any feedback, tips, comments would be greatly appreciated.
As a background the solution I am developing will be web services based and used by multiple offices around the globe by over 500 users. I have already developed a prototype using a single SQL Server 2005 instance but as this solution is going to be used by offices around the world I want to have an IIS Server and SQL Server 2005 server instance in each office with "links" back to the primary SQL Server 2005 cluster in Australia.
One of my thoughts was to set up replication between the offices that would happen at midnight remote office local time and then set up triggers to update the primary cluster when assoociated data was changed on the remote sites or on the primary cluster. Does anyone know or can anyone suggest alternatives to this strategy?
I effectively need some sort of inter site caching functionality with store and foreward capabilities ...
Can anybody tell me the differences, advantages and disadvantages between these three solutions? When do I may use one or another? Could you recommend me any documentation?
I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?
On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?
Does anyone have any experience of mirroring and is using it in prefence to log shipping?
Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring
For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?
I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs. From SQL BOL, it says:
"Topic: Database Mirroring and Log Shipping ... To run in high-safety mode with automatic failover the mirroring session is configured with an additional server instance known as the witness. If the principal database is lost for any reason after the database is synchronized and if the mirror server and witness can still communicate with each other, automatic failover occurs. An automatic failover causes mirror server to assume the principal role and bring its database online as the principal database. For more information, see Automatic Failover [ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log shipping backup location is accessible to the new principal/primary server, its backup jobs begin to ship log backups to that location. The database mirroring synchronous mode guarantees that the log chain is unaffected by a mirroring failover and that only valid log is restored. The secondary servers continue to copy log backups without knowing that a different server instance has become the primary server. ..." Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx
Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is unaffected by a mirroring failover and that only valid log is restored?
Let me elaborate the situation (if anything I said is incorrect, please correct me ) Here is the time line of the failover happens:
------- tn-1 ---------- tn ---------- tf -------- tn+1 ---------------> t
----------------> t: the time line. tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn. tf: the moment that mirroring failover occurs in the database mirroring session. the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.
Here is the question that I want to ask: In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover? That's the point that I interested in.
Hi All, Is anyone can tell me the difference between log shipping and clustering? I look at it and I think that this is the same thing as they use two different servers. TIA
SQL Server 2005 SP2Can I combine Log Shipping and Clustering? That is, can Iship logs from the primary node in my cluster to an offsitewarm standby SQL server box?Any help appreciated.TIAaj
Guys, I really need a help . I have one database (1TB) on two servers in different location( About 1200 miles distance). SO which options will be good: Clustering or Database Mirroring
I am totally confused . As i read from SQl-server- performance , Clustering doesn't protect data. It is server level.
I know there was a previous thread on setting up DB Mirroring and Clustering where DB Mirroring would failover first because its faster than clustering. But is there a way to set up servers to allow clustering to failover first and then use DB Mirroring failover if the cluster node fails.
Can a publisher be mirrored? What are the implications, issues, gotchas? Transactional, Merge or Transactional w/ Updating Subscribers is what I'm considering.
Bottom line is I would like to use mirroring, but only one mirror will not suffice.
We are doing Reporting for a transaction system. since we do not want to hamper the live database we are planning to do the transactional replication.
Few questions for transactional system.
1. If we replicate a database , then what ever changes happened for the source db will be transferred automatically?
for ex: If i change a column name of a table in source system, then will it transferred automatically to the replicated db?
2. If we do any change to any of the tables in source system, do we need to recreate the replication and reload the entire data?
3. Also we are planning to enable cdc on this replicated db to enable incremental load to my warehouse. So if we disable the cdc and do a full load into the replicated db, then do we need to perform full refresh on warehouse?
4. Can we replicate on a table level? so that if we reload only the changed table and then reload then there wont be any impact on the over all flow of other tables.
clustering sounds expensive and arcane. Is it ever a better choice than mirroring for high availability? Perhaps when the size of the db copy is too prohibitive under the mirroring option?
We have 2 env. : Testing and Production, both are running Windows 2003 Enterprise Server with SQL Server 2005. The difference is Testing is NOT running Windows cluster but Production do so, what is the best way to transfer a database from testing to production?
We have another systems that both testing and production are running on NON-cluster and we use backup/restore to transfer the database, can it apply in this case.
And I found that there are a tools called DTC, which can transfer all DB objects from one DB to another, is it a best way to transfer between non-cluster and cluster env.?
we have about 4 different SQL Servers (sql 2000 standard) where each server has about 7 databases. We wanted to have a disaster recovery plan where there will be a single sql2000 standard server as a standby server. we wanted to have all other sqlserver databases (about 7x4) replicated or logshipped to the remote standby server.
We thought about to have all the databases logshipped to the standby server. But since the sql server we have are Standard servers, we thought about using Simple Logshipping tool in the resource kit.
Each server databases are having full backup in the morining and there will be transaction log backup every hour. and then it will be overwritten on the following morings full backup and then the transaction log.
1) the problem we have now is if we go with the Stand by server, we need to modify the daily backup plan to have just one full backup and then the incremental transaction log backup(otherwise standby won't be syncronized). we need to have the backups done as per the backup plan we currently have. Can you please advice me on how can we achive this?
2) If you think the replication is the better one, please direct me on which replication is best and can you also please advice me if we do replication how hard it is to administer replication as well as other adminitration task???
I am little bit hesistant to go with replication as it might create more problem when there is any other problems. (Note: these servers are all Prductions servers)
As a finger in the air generalisation would log shipping or replication provide the least impact on a network?
We are moving some servers from our local LAN to a remote location on the other side of an MPLS cloud (anyone understand those network guys?!) and the replication that is currently running will need to move or be replaced. Obviously this traffic is going to have to fit in amongst the general office to internet traffic now where previously it was only on the LAN so we want to reduce the traffic as much as possible.
Many thanks for any thoughts that you might have ...
Are there any articles on the pro's and con's for creating a warm stand-by using log shipping vs. replication? Other than the obvious latency issues, is one better than the other?
I recently devised a way to implement Log Shipping on my standby server. It presently working well. We are however a 24x7 shop, and since we backup nightly, we have about a 5 hour window of vulnerabilty (2 hours backup of Prod, followed by about 3 hours Restore on Standby - then apply logs every hour) It has been suggested to only back up once weekly, but that makes me very uncomfortable, even though we have to standby up to date. Now, the debate is whether Replication for a standby is a better method. I'm not so sure. Isn't it really more for distributed data, OLAP, etc, rather than a reliable method of disaster recovery? And also, doesn't replication have more overhead cost on the server than log shipping (even if dist db is remote) Seems to me there is more chance and places of failure with REPL than LOG SHIPPING. Any experience or input is greatly appreciated. Thank you.
We are researching various ways to create an offsite disaster recovery solution. We are talking about either Transactional Replication or Log Shipping. Which would you use?
Using: SQL2000
Down time max of 4 hours.
My concerns are once a failure occurs and we are running production at the offsite facility how quickly could we get back to the primary cluster?
We currently have a production box that log ships several databases to multiple servers. One of these servers acts as our "reporting" server. One of the things that I'm not satisfied with is the fact that if we need to make a change to any underlying stored procedure that is used for reporting, we need to deploy that stored procedure to our production database and then wait for it to be log shipped to our reporting server.
Because of this, we have a ton of stored procedures in production that are never actually used for production.
I'd like to implement replication to get data from production to our reporting box. This way we can update stored procedures as necessary.
Is anyone aware of any gotch's / caveats / issues with having replication and log shipping configured on the same "publisher"? Also, if I recall correctly, now with SQL2K5, any DDL is automatically replicated to any and all subscribers...
I need to implement a dirt cheap replication method for some dirtcheap servers. We are using the SQL Server workgroup license. Isthere anything in this that prevents using log shipping forreplication in Workgroup versus Enterprise?ThanksTravis
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
In our environment on log shipping is configured but there are some jobs in primary servers the jobs are with SSIS packages with dependency on some files/folders also. Now how can I move the jobs to secondary server so that it can be enabled in time of DR replication.
We have a sql server 2000 sp3 server on a win2k3 server standardedition. This is on dell hardware with a dell scsi dasd enclosuredirectly attached. We want to duplicate the hardware and replicate ormirror the database to the backup server and do a manual or automaticfailover in case of outage(more than likely manual). Wondering rightnow if replication - with say software like doubletake - is preferredover sql database mirroring or vice versa. Thanks
I am setting up transactional replication on a database A with read only subscriber database B (for reporting purpose). I have also setup mirror on database A. I tried to manually failover to mirror...mirroring works fine but transactional replication is interrupted.
