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
Solved

SQL server 2005 mirroring

Posted on 2010-09-22
10
318 Views
Last Modified: 2012-05-10
Can SQL agent service remain stopped when setting up and running database mirroring?
0
Comment
Question by:tech2010
  • 5
  • 5
10 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33740997
Mirroring is set up using endpoints. Each DB knows its mirror partner and soforth. SQL Job agent is not involved. If you set up mirroring and jobs belong to the database and you want to be able to use the mirror transparently, then you need to create those jobs on the mirror partner.

TSQL code for setting up a mirror, this shows that only endpoints are used.
http://ukganguly.spaces.live.com/blog/cns!3DBBCE0B87F1CF26!658.entry?sa=417218956

//Marten
0
 

Author Comment

by:tech2010
ID: 33742068
thanks. i am using SQL server 2005. Does mirroring produce transaction logs as well just like log shipping does. What is the difference between mirroring and log shipping?

How can you restore point in time with mirroring? thanks
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33742212
Log shipping is logshipping. Mirroring is another technique. It's realtime, and you can force it not to commit a transaction until its committed on the mirror. This article is a start: http://www.extremeexperts.com/sql/articles/DBMirroring1.aspx

To restore point in time, did you think this question through? (not meant in any harsh way).
This has to do with backup of logs, and full recovery mode set on the database. Since a mirror is the same database, you can do a point in time restore on the mirror SQL host, aswell as on the SQL hosting the database. You would have to break the mirror ofcourse to restore it, but if its done with norecovery switch, it can be the mirror target again.

//Marten
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:tech2010
ID: 33742528
thanks, mirroring in realtime and transfer mode is tcp endpoints however log shipping use SQL agent jobs to ship the logs to secondary server and then restore logs.

Regarding restore point in time, yes i know what i have asked but the answer you gave me does not match with the question. sorry to say. but let me explain my question a little more, For example lets say we have SQL mirroing configured and working fine, Around afternoon time at 2pm we realized that there are some corruption in the database which has messed up the whole db and changes has replicated to the mirror server and we have to restore database as it was before 2pm (say restore as it was at 1:45pm) so how can we go back to 1:45pm. However with log shipping certainly we can do this because have transaction logs producing every few minutes and we can restore last night full backup and then all the logs up to 1.45pm.

Please explain, thanks
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33743192
The same applies to the mirror, a mirror is basically a hot failover technology.

If you need to go back, the database has to be in full recovery mode, and translog and if needed a tail log backup must exist.

Then break the mirror, restore the database to a point in time, recreate the mirror.

Log shipping has a latency, mirror does not. The techniques are different. With mirror you can configure a witness server and have automatic failover for example. The log shipping need manual intervention.

To sumarize, the point in time restore has to do with log backups. it does not have to do with logshipping or mirroring. Although the technique for log shipping requires logs, therefore these will exist for a point in time restore. But this restore has to be performed at the log shipping target also.

If anything else is unclear, ask away.
0
 

Author Comment

by:tech2010
ID: 33744033
I think you made be clear that indeed point in time restore is seperate from log shipping or mirroring. Log shipping requires transaction logs but mirroring does not produce transaction logs (.trn) files. So in order to do point in time restore we must have .trn files along with Full database backup.

So to overcome this issue while i am running mirroring. I have now also enabled Transaction Log Shipping on principle server on the principle database from the database properties and set it to produce .trn files every 15 minutes and delete files older than 3 days. These files will not transfered to mirror server into mirror database but i am just dumping it in a folder.

So in case if ever i need to go back for point in time restore, i will always have transaction logs readily available.

thanks alot.
0
 

Author Comment

by:tech2010
ID: 33745505
please tell me one thing:- If we do transaction log backup of a database, does it commit any database stored from log file into data file?

Also tell me is there any impact doing seperate transaction log backup in log shipping setup. I mean to say that if we have log shipping running which producing logs every 15 minutes and then shipping logs to secondary server. So if we do just adhoc database backup in which we select transaction log backup as part of database backup while log shipping is working, will this impact on log shipping or can this cause to break the log shipping of out of sync becauase that perticular log which i backed up adhoc will not be shipped to secondary server and the sequence of transaction logs will changes. Is this theory true? thanks
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33746926
A Backup does not commit any transactions. it empties already committed transactions, thereby making room for more transaktions.

A ad hoq log backup is a REALLY REALLY BAD IDEA. It will break the log chain. Do not do this, instead make a full backup with copy_only option if need be.

//Marten
0
 

Author Comment

by:tech2010
ID: 33748947
sure. so ad hoq log backup is bad idea with log shipping setup. But did you read my 3rd last post that to over come point in time issue i have enabled transaction logs every 15 minutes along with mirroring. Will this have any impact as well or not? thanks
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 33751300
Log backups are generally very fast. I don't see this to have any impact in my experience.

Good luck

//Marten
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

829 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