Category: SQL Server 2008

monitor Database mirroring by SQL Management:

1- I will Stop mirroring Server to input transaction into Principle Server and See the log what happened in It Go to your database Mirroring in Principle Server >> Write Click >>> tasks >>>Lunch Database mirroring monitor

2- Create Table on Principle Server  and insert 10000 Record into New table

CREATE TABLE TEST( A INT, B INT)

DECLARE @I INT=0

WHILE @I <100000

BEGIN

INSERTINTO TEST VALUES(@I,@I)

SET @I=@I+1

END

SELECT COUNT(*) FROM TEST

3- now run the Script and insert the Data then let’s go to Check the monitor to See what happened

4- Then Start the mirroring Server and Check the Monitor again

*********************************************************************************************************************

Check the database mirroring information :

SELECT M.database_id , D.Name , M.mirroring_state_desc  ,

M.mirroring_role_desc,M.mirroring_partner_name , M.mirroring_partner_instance,

M.mirroring_witness_name , M.mirroring_witness_state_desc

FROM sys.database_mirroring as M inner join Sys.databases as D

on M.database_id = D.database_id

Where D.database_id > 4

After i finish my work in Database Mirroring with operation Mode (high Safty ). i explorer it will not be Automatic failover becouse this option if you need it you must configure your mirroring with operation mode (High Availability Synchonous) and this Operation Mode need 3 Servers (Principle , Mirroring , Witness) So i will Show now how to add or Replace mirroring Database Witness Server

1- Now this my mirroing operation mode it’s high safty without automatic and the witness Server not Configure

2- Click on Configure Secuirty after this mirroring will Ask you if you nedd to Configure Witness Server or not Select yes

3- Check Witness Server

4- mirroring will return the principle server configuration

5- Select Witness Server

6- Configure Witness Server and the port listner

7- Put Services Account in my experince if you working in Workgroup and your all instance in the same windows like thie example create new user Administrator on your pc and add him in SQl Server login the use it ElSE if you use Workgroup but the SQl Servers not all in the Same pc you will use 3 Windows or 2 create Certification in your SQL Server and i will show it in the next post who to create Mirroroing With Login Certification .

8- After this Step Click finish No your database witness is Configured

Now your Configuration is Complete and if you comapre between image no 8 and image no 1 you will see the difference you will see now we have Mirroring With operation mode high safty With Automatic failover

NOTE :

in Step no 6 you will See i write the port no is 5023 but when i finish my Steps and arraive to step no 8 then click on ok i reicive this Error :

TITLE: Microsoft SQL Server Management Studio ——————————

Alter failed for Database ‘ACC2012’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

—————————— ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://IT-PC:5023’. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=1456&LinkId=20476

—————————— BUTTONS:

OK ——————————

i don’t know what is error i search more and more on the internet but i don’t found good Soluation but when i checked the SQL Server log i see somthing say { SQL Server can not listen to the port No 5023 }

So i Recounfigured the mirroring again and Change the port for the Witness Server form 5023 to 5024 it’s Working now very good so before any thing or before you go to internet Please try to check :

SQL Server log

Windows Event Wiewr

This very good help to know what’s the really problem

HOW TO CHECK YOUR MIRROING IS RUNNING OR NOT ?

you can Check :

1-  port number listener : open CMD and write this Command  ” netstat -an ” you will see the 3 port for the 3 Servers Principle – mirroring – Witness also I see the 3 port because I setup the 3 SQL Server Instance in the same  Windows Server

2-  Event Viewer for Windows Server

3-  SQL Server Database Log

4-  Check Endpoint Status