Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2012 Mirroring

Posted on 2014-10-02
9
Medium Priority
?
130 Views
Last Modified: 2014-10-13
Hi Experts, we have had SQL mirroring setup on 2 SQL Standard 2012 servers (2012 R2 Datacenter). As of now the mirrored partner has gone down hard and the Principal databases are in Disconnected state. We need to rebuild the mirrored partner on new hardware. Is there something we should be aware of when rebuilding this mirror or can we stand up another SQL server and setup mirroring?

TIA
0
Comment
Question by:abhijitm00
[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
9 Comments
 
LVL 4

Expert Comment

by:Marek
ID: 40358903
If your principal database is intact you can just rebuild the mirroring configuration with another mirror server.
0
 

Author Comment

by:abhijitm00
ID: 40359286
Thanks for replying Marek.

We have rebuilt the secondary server with the same name and IP. Will we just need to enable Mirroring on the Principal to set it up or do we need to a backup and restore of the full sql database followed by the transaction logs?
0
 
LVL 4

Expert Comment

by:Marek
ID: 40359296
You would need the backups to rebuild mirroring, it's like configuring mirroring from scratch.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:abhijitm00
ID: 40359763
Should the log backup that needs to be taken be a copy only backup or an actual transaction log? Thanks
0
 
LVL 4

Assisted Solution

by:Marek
Marek earned 1000 total points
ID: 40359788
To estabilish mirroring you need full and log backup on the principal database
0
 

Author Comment

by:abhijitm00
ID: 40360016
Thanks. If the backup is older for e.g. in our case backups are taken every 15 minutes on the principal server. By the time I get down to restore another backup would have run and I would not have the most current backup. I was under the impression that during mirroring the mirror database will catch up with the Principal. Won't that be the case? Thanks once again
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 40360255
use AlwaysOn Availability Groups instead of
deprecated database mirroring

If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping.
more:
Deprecated Database Engine Features in SQL Server 2014
http://msdn.microsoft.com/en-us/library/ms143729.aspx
0
 
LVL 4

Expert Comment

by:Marek
ID: 40360303
For mirroring config you can follow http://msdn.microsoft.com/en-us/library/ms190941.aspx
Although mirroring is marked as deprecated, if you can't do AlwaysOn, mirroring may be still a good choice, since that does offer failover if you have a witness server configured (as wintess you can use even express edition)
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1000 total points
ID: 40360331
Mirroring requires you to restore the database full and the transaction log, in your case you either need to pause the log backups until you can successfully implement mirroring because the transaction logs are synchronized to the mirror from the principal and if you backup the transaction log on the Principal before establishing the mirror then the transaction logs LSN will not match up and it will not be able to send the tlogs.

So restore full, pause the backups of the log, back up the log and restore it over to the mirror, establish mirroring then resume the log backups.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 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