Solved

simple doubt in mirroring vs transactional log shipping

Posted on 2011-09-09
8
242 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

679 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