RSS

Mirroring SQL Server Part 3

25 Dec

in this part we will learn how to make resume and pasue to the mirroring session how to check the status to your database mirroring then how to make remove to the Mirroring Session

Suspend\Resume Database Mirroring Session

  • Why we need to Pass [suspend] database mirroring session ?
  1. For rolling upgrade [if I want to upgrade my mirroring server must be pass mirroring then upgrade then resume it again]
  2. Fix problem hardware or software problem
  3. Automatic page repair
  4. Forced Services [you can use it when the principle server down and the mirroring server is online you will >>>Connect to the mirror server >>>>Issue the following statement >>>>>ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS where <database_name> is the mirrored database. >>>> The mirror server immediately transitions to principal server, and mirroring is suspended. >>>> then you will go to fix the problem in principle server and when it return to work you will make resume to the mirroring server ]

How you can make suspend \Resume Mirroring session?

Go to Principle Server [Elmasry-PC\Principle]

1-    Check your Mirroring mode [Right Click on Mirrorin_DB >>>> Tasks >>>> Mirror >>>> you will see the mode is high performance mode because the last example make in this mode ]

2-    Now pause the mirroring DB the SQL will ask you Are you want to pass mirroring of this database? Answer YES

Paused mirroring Session

3-    Now Check the status to your database

4-    Now Right click on Mirroring_DB >>>> Tasks >>>> Launch Database mirroring monitor  you will see the database is suspended in the principle server and the mirroring server and you can check this by the time from the history this toll is very good to make check your mirroring session from time to another time

5- Now I will make some transaction on my database Mirroring_Db in principle server to the unsent log 
CREATE TABLE test_unsent_after_suspend
(ID INT , NAME NVARCHAR(50))
GO
DECLARE @id INT
SET @id = 1
 WHILE @id < 1000
 BEGIN
 INSERT INTO test_unsent_after_suspend VALUES(@id , ‘Mostafa Elmasry SQL DBA’)
 SET @id = @id +1
 END
 6- Let’s go to check the unsent log from the mirroring monitor

unsent Log

 7- i will go now to return the priciple server to work by make resume to the Mirroring_DB [same stsps like pause]

 

Remove Mirroring Session

1-      In principle server  right click on Mirrorin_DB >>> tasks >>> mirror >>> Remove Mirror

2-      Delete Endpoint from 3 Servers [principle-Mirroring-Witness] >>>Serrver objects >>>Endpoints>>>Database Mirroring >>>>Select your Endpoint .

3-      Refresh the Mirroring server you will see the Mirroring_Db status is restoring if you want to open it and use it you can make it by this Code

Wait me My friend in the Mirroring Part 4 (How to Create mirroring by Code)

Good Luck

 
1 Comment

Posted by on December 25, 2011 in Mirroring SQL Server 2008

 

One response to “Mirroring SQL Server Part 3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s