Solved

SQL 2012 Mirroring

Posted on 2014-10-02
9
113 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 250 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 42

Expert Comment

by:EugeneZ
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 24

Accepted Solution

by:
DBAduck - Ben Miller earned 250 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strangeā€¦
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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