Solved

SQL 2012 Mirroring

Posted on 2014-10-02
9
116 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Title # Comments Views Activity
Want Win 10 Pro to search like Server 2010 or 2012 27 103
Installing .NET 3.5 on Windows Server 2012 1 54
Can I skip a node in XML? 9 29
Upgrading to SQL Server 2015 Express 2 27
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

679 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