Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL server 2005 mirroring

Posted on 2010-09-22
10
Medium Priority
?
325 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

876 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