Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

simple doubt in mirroring vs transactional log shipping

Posted on 2011-09-09
8
Medium Priority
?
249 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Accepted Solution

by:
Rimvis earned 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

719 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