Hi everyboby,
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?
Thanks a lot,
Radamante71.
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
Hi
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?
Thanks in advance for any feedback
Robert
Hello All Database Mirroring Experts,
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.
Thanks a lot,
Terence
SQL Server 2012 SP2 - WIndows 2008 R2.
We are currently doing Mirroring from one domain to another (On a different subnet/network) via
Network1
DomainABCSQLAccount1 - Password1 -----> SQL Server Service Account
DomainABCSQLAccount2 - Password2 -----> SQL Server Agent Service Account
Network2
DomainABCSQLAccount1 - Password1 -----> SQL Server Service Account
DomainABCSQLAccount2 - Password2 -----> SQL Server Agent Service Account
And it works. We are planning to do the Log shipping but this time different Domain but same userID and Password
Network1
DomainABCSQLAccount1 - Password1 -----> SQL Server Service Account
DomainABCSQLAccount2 - Password2 -----> SQL Server Agent Service Account
Network2
DomainDEFSQLAccount1 - Password1 -----> SQL Server Service Account
DomainDEFSQLAccount2 - Password2 -----> SQL Server Agent Service Account
Is this possible without TRUST between the domains ?
Dear,
Are possible build a solution with SQL Server 2005 Cluster and Database Mirroring? I have the following scenario:
1) Site A
SQL Server 2005 with 2 nodes and active/passive
2) Site B
SQL Server 2005 for Disater Recovery with Log Shipping.
Are possivel change Log Shipping by the Database Mirroring? If is possible, are better:
a) Synchronous(with or without witness)
b) Asynchronous
Thanks a lot! Sandro.
sgpcosta@hotmail.com
Suppose I have a 3rd party SQL backup software to backup my databases, can I also setup a stand-by server for log shipping?
I'm not sure if this is possible? If not, is SQL mirroring a choice instead of log shipping?
Thanks a lot!
Hello.
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 need some help on SQL server log shipping.
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)
Thanks for your help.
--Ragulan
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?
Colleen
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?
Thanks for any input... :D
which enables schema changes like new columns, replication or log shipping ? i can't remember which one it was ?
View 1 Replies View RelatedI just had a general question...
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...
Thank You!
In a SQL 2005 environment, is it possible to replicate AND log ship from one production server to two different backup servers?
Thanks,
Terry
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
View 1 Replies View RelatedIn 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.
View 3 Replies View RelatedWe 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
View 1 Replies View RelatedI 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.
I have followed the steps as in article
http://msdn2.microsoft.com/en-us/library/ms151799.aspx
I have also used PublisherFailoverPartner parameter...but replication still complains that it cannot connect to publishing database.
Any ideas or direction is greatly appreciated.
Hi MSSQL friend,
We have a SQL2005 server which contains some replicated databases. We use merge replication and the publications are all local.
In order to make the server failsafe we need some kind of mirroring. Is this possible with replication?
Any ideas how to accomplish a failsafe scenario with replication?
Sincerely Edward
Does anyone have an opinion on which is more efficient?
Currently have a cluster setup and I'd like to setup mirroring or transactional replication on top of that to replicate/mirror the data to an offsite building that is still on the local LAN as a DR solution to a SAN failure.
I'm using replication and want to mirror my publisher to get high availability. As I understand the documentation the principal and the mirror must both use the same distributor instance. So if the distributor fails the replication fails. This seem like a big weakness. Maybe I'm missing some thing. Is there a way to set it up so that replication continues if a distributor fails -- some mechanism that will switch in a "back-up" distributor? I'm looking for a configuration that will continue to run in the event of any single failure. This means that both the publisher and the distributor must be covered by mechanisms that keeps them going in the event of a failure. Mirroring covers the failure of the publisher but I don't see a mechanism that will conver the failure of the distributor.
My application is developed in .net / c#. Data is accessed through .net sql server data provider etc.
Hello y'all,
Can someone tell me what's the main difference between mirroring and replication?
Thanks! Kindest Regards
Looking for anyone that has had any experience with using SQL Merge replication while mirroring the publisher database. Thinking about doing this as a recovery plan in the event of a publisher failure.
Any advice would be appreciated.
Thank you!
dear all,
I have a case.
I have 1 production server and wanto to add 1 more server.
my production server is using sql 2000 stnd edition.
I want to have an indentical database from my production server.
the problem is my second server is using sql 2005 stnd edition.
I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.
any idea or suggestion will be great....
regards,
-dedys
After reading about it, i'm still confused. Is replication and database mirroring the same? they seem to accomplish the same thing.
View 3 Replies View RelatedI am having trouble finding any documentation on how linked servers are set-up under SQL 2005 for log-shipping and replication. Under SQL 2000, the only linked servers "visible" were ones that were user-defined. Under SQL 2005, it seems when you set-up log-shipping or replication, you get these new linked servers which are automatically defined. I would like to understand them and in particular, how security is maintained. We recently had a production issue where after some problem with a server which resulted in having to restart SQL Server, and shrink msdb, log-shipping started to fail authentication when trying to run alert jobs. Only after rebooting the server, and also the log-ship target server, and waiting 9 hours, did the authentication correct itself. I would like to know how it resolved itself and what took so long?
View 3 Replies View RelatedHi all,
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.
Thank you in advance.
Ray Nichols
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.
Here is the situation we are trying to resolve. The client has 2 locations, each has local appliations running on a database. db schema on both locations are the same, data is different and won't overlap.
The requirements are:
1. at each location the application can read and write
2. near zero down time for applications on each site
3. db on one site also has the data from the other site for DR purpose
The client is running on SQL 2005 STD SP2
We looked at the approach of setting up db mirroring on each location + 2-way transactional replication between both locations. The mirroring was fine, and I was able to set up transactional replication from mirrored publisher to a non-mirrored subscriber. But, from what I experienced and from reading, there is no way to have the subscriber db to be mirrored, since Distribution Agents simply doesn't have the option to specify Failover Partnr for mirroring, so I guess it is not supported. Any comments on that?
Assuming that's correct, then the only way of using SQL out-of-box technique seems to be using Clustering on each location instead of mirroring, then the 2-way transactional replication works on clustered subscribers I think (although I haven't tested it). Peer-2-Peer replication would have been a good candidate between sites in this case, but STD version of sql 2005 ruled that out.
Any suggestions and comments are welcome.
Thanks
Hello,
My company is moving to a SQL Server-based packaged application early next year. We€™re planning our SQL Server architecture but have some questions that I can€™t readily find answers for. I€™m hoping someone here can point me in the right direction.
We have three servers, I€™ll call them A, B, and C. We want to duplicate all changes to certain databases on server A to server B, then duplicate changes to selected databases and tables on server B to server C.
Ideally we€™d run SQL Server 2005 Enterprise Edition on all three servers, but the packaged application vendor does not support SQL Server 2005 yet, only SQL Server 2000. Our license agreement with them does not allow us to use replication on server A. We€™re free to do whatever we want on our other SQL Servers, but server A must sit alone, untouched, like a monolith on a far-away moon. (I€™m lobbying to have the server named Tycho, or TMA2.) Stranger still, they€™re OK with log shipping from server A to other servers. We€™ve tried to explain that replication and log shipping are both core function built into SQL Server, and that if one is acceptable, then both should be. Their fear is that replication could cause performance and stability problems, and to eliminate this possibility they€™re ruling out replication on server A.
Given these constraints we€™re resigned to using SQL Server 2000 Enterprise Edition on servers A and B, and SQL Server 2005 Enterprise Edition on server C. We plan on periodically shipping logs from server A to server B and applying them at server B.
We€™d like to know if it is possible to also use transactional replication on server B to duplicate changes from server B to server C. I€™ve used log shipping and replication in the past, but never at the same time. My understanding is that a database goes into recovery mode while a transaction log is being applied and that any user changes to the database after the log has been applied will cause later log applications to fail. The scripts I€™ve seen that are used to apply the transaction logs put the database into single user mode after the log has been applied to prevent this.
This raises a few questions:
If we try to RESTORE a log to a database being used as a source for transactional replication articles, will the RESTORE fail? Or will the RESTORE start and break the transactional replication? I€™ll test this on my own, but it€™d be nice to know if anyone has already experienced this.
Is it possible for us to have a database in read-only mode serve as the source for transactional replication articles? (I can€™t imagine why not, ever though it seems counter-intuitive - why would you want to replicate transactions from a database that has no transactions?)
If the answer to number two is yes, can we suspend transactional replication on a database, RESTORE a log to the database, put the database into read-only mode after the RESTORE, and restart the replication on the database?
Thanks in advance for sharing your wisdom, everyone!
--
Thomas C. Mueller
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 ...
Thanks
Andrew