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

x
?
Solved

SQL 2012 Mirroring

Posted on 2014-10-02
9
Medium Priority
?
134 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
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Viewers will learn how the fundamental information of how to create a table.

971 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