Solved

simple doubt in mirroring vs transactional log shipping

Posted on 2011-09-09
8
246 Views
Last Modified: 2012-05-12
If i have set up transactional log shipping b/w 2 servers and if performs the manual backup of ldf or trn, the LSN get broken and jobs in secondary servers starts erroring.ie transactional log shipping is broken b/w principle and secondary.

but in case of mirroring:
I have setup mirroring b/w 2 servers,and if i performs manual backup then mirroring doesnt get broken.

I know in case 1 we are just transferring the log shipping,that's why LS getting broken in case 2. it is just a mirrored copy.
but want know if there is any backup or restore operation is performed on principle server,what will be internal performs on the secondary servers?Will there be any intimation to secondary server?  
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36516759
any updates experts!
0
 
LVL 13

Expert Comment

by:dwkor
ID: 36517015
I'm not sure I completely understand your question but there is the option "COPY ONLY" you can select during backup. This option allows to perform backup and don't break LSN.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36517109
no here my concern is not about how to resolve the issue?

In mirroring if i am taking back up then what will be happening to secondary server? will there be intimation to secondary server?or what will happen internally in the database mirroring session?
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 19

Accepted Solution

by:
Rimvis earned 500 total points
ID: 36521074
Hi VIVEKANANDHAN,

In case on log shipping, transaction log backup is taken periodically and restored on standby server. So, if you make you own backup, log chain is broken. As dwkor noted, you can make backups with "COPY ONLY".

DB mirroring does NOT use  BACKUP/RESTORE. Instead, each transaction is "replayed" on standby server. Mirroring can be either synchronous or asynchronous. In case of asynchronous mirroring, transactions are delivered to standby server asynchronously, and impact on primary server is minimal. But in case of failure it can result some data loss. this type of mirroring is supported only in Enterprise Edition. In case of synchronous mirroring transaction is committed on primary server only when it is applied on standby. So, it can heavily impact performance on primary server.  Network lag between servers is primary concern in this case.

0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36544726
I have mirroring b/w two servers without witness servers in synchronous mode.

For example:I HAVE FULL BACKUP once in a day.

Now I consider every 5 mins once 9GB of transaction data are happening in my sql box and my hard drive contain ldf file has only 150 GB.(residing in O:\drive)

Every 5 mins once Transaction log is taken by maintenance plan. and stored in E:\ drive.

Now e:\drive became full and ldf drive also became full.
Now if run transaction log back which will be placed at H:\drive, will my mirror go into suspended state?


Actually:
In our environment, I took the .trn back up,immediately mirror went into suspended state.
1.I know there is no connection between back and mirroring.But want to know why and how that happened?I tried to resume the mirroring session,but it was in synchronizing mode and went to suspend mode in a short time.

2.If I take full backup,will also go into suspended mode?

3.If yes,then why mirror don't goes to suspended state when we run maintenance plan?

Like to understand back end or root cause.

Resolution steps I took:
1.Made the recovery to simple.
2.Shrink the database.
3.delete 4 hours old .trn files out of 8 hours,keeping recent 4 hours data alone.
4.Made the recovery to FULL.
5.re-ran the maintenance plan for taking .trn back up.
6.re-establise the mirroring again.
7.changed the retention period mentioned in maintenance plan from 8 hours to 2 hours.



Transaction Log shipping:

If I take .trn backup in the same folder where transactional log shipping is happening,will it break LSN?

I suspect it will break,but not very much clear how is breaking in this case.But trying to understand more.

I know that,if i take in different folder then LSN will be broken.


                                 

















                         















0
 
LVL 19

Expert Comment

by:Rimvis
ID: 36547500
>>Now I consider every 5 mins once 9GB of transaction data are happening in my sql box and my hard drive contain ldf file has only 150 GB.

So basically your log would fill up in 1.5 hours, if log backup stops? I would say you need more space for you log.


>> Every 5 mins once Transaction log is taken by maintenance plan. and stored in E:\ drive.

Is that a part of you backup plan? How often do you do complete backup?


>> In our environment, I took the .trn back up,immediately mirror went into suspended state.

You should check SQL server error log. Maybe there you would find some clue.


>> If I take .trn backup in the same folder where transactional log shipping is happening,will it break LSN?

I think yes. But I'm not sure how SQL server is managing backup/restore in log shipping.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36547934
>>Is that a part of you backup plan? How often do you do complete backup? <<
it completes within 2 to 3 mins
 
So when mirror is in place,taking .trn backup will not hamper the mirroring?

0
 
LVL 19

Expert Comment

by:Rimvis
ID: 36553403
>> it completes within 2 to 3 mins
No, I was asking, do you perform coplete backups of the database?


>>So when mirror is in place,taking .trn backup will not hamper the mirroring?
No, it should not
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question