SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 restore after an database mirroring failure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tanislavm
Starting Member

6 Posts

Posted - 09/26/2013 :  09:47:10  Show Profile  Reply with Quote
hi,


I have an principal and an mirror server.At a certain point the principal server fails and the database instance on mirror server is used.We use database mirroring.Now mirror server is elevated as principal server?Normally the principal server is connected to an external storage and the mirror server to other external storage.
What are the steps to be taken after the principal server is repaired if we like to have 100% availability?

tnx a lot,
marius

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 09/26/2013 :  20:23:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
Basically:

1. Take a full database backup from whichever server is currently the principal.
2. Take a transaction log backup.
3. Restore the database and log backup to the intended mirror server. Make sure to use the NORECOVERY option.
4. Execute ALTER DATABASE db SET PARTNER='TCP://name_of_current_principal.domain.com:5022' on the MIRROR server. Change the database, server, domain and port as needed.
5. Execute ALTER DATABASE db SET PARTNER='TCP://name_of_current_mirror.domain.com:5022' on the PRINCIPAL server. Make changes as needed.

Once those steps are completed you should have restored the mirroring session, and can fail over to the other server. You can use existing backups as well and skip to step 3, as long as you restore the most recent full backup, and all subsequent transaction log backups in chronological order. Otherwise follow steps 1 and 2.
Go to Top of Page

tanislavm
Starting Member

6 Posts

Posted - 09/27/2013 :  04:17:21  Show Profile  Reply with Quote
Hi Robvolk,


Only for my clarification.

After the faulty principal server is repaired,this will be now the mirror server.right?
In step 4 this mirror server is promoted to principal server for database.
In step 5 the actual principal server(who taken over the workload,after the initial primary server fails) is made as mirror server.


A thing also.On step 1 we take a full backup.Then why is necessary to take after that the transaction log backup?

tnx a lot,
marius
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 09/27/2013 :  07:19:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
Let's clarify a point: "principal" and "mirror" are roles, not servers. Either server could perform either role for a database. It's better for this discussion to call the servers A and B, and start from the premise that A was the original principal and needed repair, and B was the original mirror, but is now principal since failover.

We also have to clarify "repaired". If you had to replace a drive, or reinstall SQL Server, or any other operation that would impact the data and log files of the database on server A, then it's likely that mirroring would be broken and have to be reinitiated. That's what the sequence I provided earlier does.

If that's not the case, and SQL Server was still running on server A, then the system should automatically synchronize it with server B as the principal. You can check this in Management Studio by looking at the databases on each server. After the name you should see "Principal" or "Mirror" followed by a status (Synchronized, Synchronizing, Disconnected). If you see "Restoring" after the name, then the database is not mirrored, and you'd need to use all or part of the sequence I posted to start mirroring.

Database mirroring is essentially continuous log shipping. All information in the transaction log is copied from the principal and transmitted to the mirror. In order to guarantee the log sequence is unbroken, a log backup must be restored to a mirror prior to establishing the mirror partnership. I suppose Microsoft could have coded it to avoid this requirement under certain conditions, but it was probably not worth the effort and the current process is safer overall.
Go to Top of Page

tanislavm
Starting Member

6 Posts

Posted - 09/29/2013 :  04:15:38  Show Profile  Reply with Quote
Hi Robvolk,


In order that the database instance to automatically fails over from principal to mirror, there is necessary to have an witness(role) server?
Or alternatively to configure an sql cluster between principal and mirror?

you also written:"If that's not the case, and SQL Server was still running on server A, then the system should automatically synchronize it with server B as the principal."

So now the database instance runs on server B.Is there the possibility that this instance to failover back on server A automatically?

tnx a lot,
marius
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 09/30/2013 :  07:58:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yes, a witness (and high safety mirroring) is required for automatic failover.

Windows clustering is a separate feature from database mirroring, and setting up the mirror and principal as you describe would not work as a clustered instance. You are probably thinking of SQL Server 2012 Availability Groups.

As I said earlier, mirror and principal are roles, not servers, so the failover (failback) as you describe would not happen automatically, unless there was a failure on server B.
Go to Top of Page

tanislavm
Starting Member

6 Posts

Posted - 10/01/2013 :  05:50:45  Show Profile  Reply with Quote
Hi Robvolk,

just to verify with you something.

When a new transaction starts, first an event is written in binary log,then the transaction in committed, means that upon the even will be executed the sql command(create table, insert,update,so on.right?

In this light,beside of disk failure,why the database could be corrupted. To me corruption could be only in binary log,or am I wrong?

tnx a lot,
marius
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 10/01/2013 :  07:16:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
The transaction log is written first to aid in database recovery. If for whatever reason a transaction can't complete, the log is used to roll back any intermediate changes that were written. Corruption typically occurs during a write to disk, either in the transaction log, or when the data file is written with the committed changes. A corruption in the log file may or may not cause changes to be rolled back.

The best resource I know for learning about and fixing corruption is here: http://www.sqlskills.com/blogs/paul/category/corruption/
Go to Top of Page

tanislavm
Starting Member

6 Posts

Posted - 10/02/2013 :  03:41:00  Show Profile  Reply with Quote
Hi Robvolk,


Tnx so much for your so valuable comments.

A thing I like to verify.

I think that any sql language(MySQL,mssql,pl/sql,so on) use MySQL server and thus database mirroring.right?

Which one would be better in an mission critical framework?

tnx a lot,
marius
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 10/02/2013 :  07:32:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
Each database product uses a different engine and supports different types of data replication. MS SQL Server is the only one that uses database mirroring. As far as which one is best, you have to decide that based on their features.
Go to Top of Page

tanislavm
Starting Member

6 Posts

Posted - 10/04/2013 :  05:48:57  Show Profile  Reply with Quote
Hi Robvolk,


Mssql uses for HA windows cluster?

tnx,
marius
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 10/04/2013 :  20:27:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
Failover clustering is one option for high availability in SQL Server. It's not required for database mirroring.

There are several options for high availability, Microsoft has a white paper describing them here: http://download.microsoft.com/download/5/B/D/5BD13FFA-5E34-4AE1-9AA0-C6E6951B8FC8/SQL%20Server%202008%20R2%20High%20Availability%20Architecture%20White%20Paper.docx

Note that it does not include Availability Groups, which were added in SQL Server 2012.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000