SQL server 2005 mirroring

Can SQL agent service remain stopped when setting up and running database mirroring?
tech2010Asked:
Who is Participating?
 
Marten RuneConnect With a Mentor SQL Expert/Infrastructure ArchitectCommented:
Log backups are generally very fast. I don't see this to have any impact in my experience.

Good luck

//Marten
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
tech2010Author Commented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
tech2010Author Commented:
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
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
tech2010Author Commented:
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
 
tech2010Author Commented:
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
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
tech2010Author Commented:
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
All Courses

From novice to tech pro — start learning today.