Solved

simple doubt in mirroring vs transactional log shipping

Posted on 2011-09-09
8
236 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
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.

 
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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

785 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