SQL server 2005 mirroring

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

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

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

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

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.

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.

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

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

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