| Author |
Topic |
|
tanislavm
Starting Member
6 Posts |
 Posted - 2013-09-26 : 09:47:10
|
| 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
15732 Posts |
Posted - 2013-09-26 : 20:23:57
|
| 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. |
 |
|
|
tanislavm
Starting Member
6 Posts |
Posted - 2013-09-27 : 04:17:21
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-09-27 : 07:19:19
|
| 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. |
|
|
|
tanislavm
Starting Member
6 Posts |
Posted - 2013-09-29 : 04:15:38
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-09-30 : 07:58:05
|
| 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. |
|
|
|
tanislavm
Starting Member
6 Posts |
Posted - 2013-10-01 : 05:50:45
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-10-01 : 07:16:07
|
| 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/ |
|
|
|
tanislavm
Starting Member
6 Posts |
Posted - 2013-10-02 : 03:41:00
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-10-02 : 07:32:20
|
| 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. |
|
|
|
tanislavm
Starting Member
6 Posts |
Posted - 2013-10-04 : 05:48:57
|
| Hi Robvolk,Mssql uses for HA windows cluster?tnx,marius |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-10-04 : 20:27:22
|
| 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.docxNote that it does not include Availability Groups, which were added in SQL Server 2012. |
|
|
|
|
restore after an database mirroring failure