Solved

simple doubt in mirroring vs transactional log shipping

Posted on 2011-09-09
8
232 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
Comment Utility
any updates experts!
0
 
LVL 13

Expert Comment

by:dwkor
Comment Utility
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
Comment Utility
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
 
LVL 19

Accepted Solution

by:
Rimvis earned 500 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
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
Comment Utility
>>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
Comment Utility
>>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
Comment Utility
>> 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now