Solved

SQL server 2005 mirroring

Posted on 2010-09-22
10
321 Views
Last Modified: 2012-05-10
Can SQL agent service remain stopped when setting up and running database mirroring?
0
Comment
Question by:tech2010
[X]
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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

626 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