RSS

Category Archives: Mirroring SQL Server 2008

In this session i will explain Mirroring In SQL server 2008 from A to Z

SQL Server Transaction Log Shipping vs Mirroring – A Comprehensive Guide

SQL Server Transaction Logs record the entire information about the transactions and data modifications made by the transactions in the server. In order to create backup or ensure the availability of transaction logs at the instance of any disaster, SQL offers two processes- SQL Log Shipping and SQL Log Mirroring.

In this section, we will describe both these processes and the parameters in which they differ from each other. Read the rest of this entry »

 
 

Tags: ,

Error When make Mirroring between SQL Server Standard Edition and Enterprise Edition

we can have Mirroring between Standard and Enterprise editions. But when you do this via SSMS (Graphical Interface), you might end up with following error message.

But when we use T-SQL statements, we were able to setup database mirroring and which worked perfectly fine too.

Error Message:

This mirroring configuration is not supported. Because the principal server instance, <server_name>, is Standard Edition, the mirror server instance must also be Standard Edition.

531x484xSQLServerMirroringEditionError_thumb_jpg_pagespeed_ic_yWV4PufZF4

 
Leave a comment

Posted by on February 20, 2013 in Mirroring SQL Server 2008

 

MSSQL 2008R2 Mirroring in Workgroup

Problem

Now if I have 3 Servers with 3 SQl server Instance but this servers not trusted = Not in domain this servers in workgroup but now I won’t to create mirroring but the last step in Mirroring Configuration need from the Services Account What I will write and the servers with no trusted . so what we can do to make this servers is trusted .

Solution

We will make link between this SQL Server instance this link not the linked server feature but that’s meaning we will make master key and Certificate on all Servers then Create users in the 3 Servers then give grant Connection to this Users on this Mirroring Endpoint Example

Server name                            Certification                user

Server A(Principle)                 CERT_A                       User_A

Server B (Mirror)                     CERT_B                       User_B

Server C (Witness)                   CERT_C                       User_C

now I create difference Certificate  and user on the servers then I will

Create Certificate Server B and C  on server A

Create Certificate Server A and C on Server B

Create Certificate Server A and B on Server C

Also I make this Steps on user then give the 3 user in the Server A and B and C grant Connection on Mirroring Endpoint by this Way I make Bridge between the 3 Servers

Step 1 On Principle Server :

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD =‘Admin123’

GO

CREATE CERTIFICATE Principle_cert

WITH SUBJECT = ‘Principle certificate’

GO

CREATE ENDPOINT End_Mirroring

STATE = STARTED

ASTCP (LISTENER_PORT = 5025 , LISTENER_IP=ALL)

FOR DATABASE_MIRRORING

(AUTHENTICATION = CERTIFICATE Principle_cert , ENCRYPTION=REQUIRED ALGORITHM RC4 , ROLE=ALL)

GO

BACKUP CERTIFICATE Principle_cert

TO FILE =‘C:\certificate\Principle_cert.cer’

GO

Step no 2 On Mirror Server :

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Admin123’

GO

CREATE CERTIFICATE Mirror_cert WITH SUBJECT = ‘Mirror certificate’

GO

CREATE ENDPOINT End_Mirroring

STATE=STARTED

ASTCP (LISTENER_PORT= 5026,LISTENER_IP=ALL)

FOR DATABASE_MIRRORING

(AUTHENTICATION=CERTIFICATE Mirror_cert , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE=ALL)

GO

BACKUP CERTIFICATE Mirror_cert

TO FILE=‘D:\certificate\Mirror_cert.cer’;

GO

Step No 3 on Principle Server : 

Copy the Certificate ” Mirror_cert  ” From Mirror Server to the Principle Server because I will Create the Certificate Mirroring Server on Principle Server With the User name Mirror_user

USE MASTER

GO

CREATE LOGIN Mirror_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Mirror_user FOR LOGIN Mirror_login

GO

CREATE CERTIFICATE Mirror_cert 

AUTHORIZATION Mirror_cert 

FROM FILE=‘C:\certificate\Mirror_cert .cer’  —–the Path of your Certificate After you Copy it

GO

GRANT CONNECT ON ENDPOINT ::End Mirroring TO [Mirror_login] ——-Give privilege to the User Mirror_login to Connect to the Mirroring Endpoint ” End_Mirroring “

GO

Step No 4 on Mirror Server : 

Copy the Certificate ” Principle_cert  ” From Principle Server to the Mirror Server because I will Create the Certificate Principle Server on Mirroring Server With the User name Principle_user

USE MASTER

GO

CREATE LOGIN Principle_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Principle_user FOR LOGIN Principle_login

GO

CREATE ERTIFICATE Principle_cert

AUTHORIZATION Principle_cert

FROM FILE=‘C:\certificate\Mirror_cert .cer’  —–the Path of your Certificate After you Copy it

GO

GRANT CONNECT ON ENDPOINT :: End Mirroring TO [Principle_login] ——-Give privilege to the User Principle_login to Connect to the Mirroring Endpoint ” End_Mirroring “

Step No 5 on Witness Server : Create master Key and Certificate Witness_Cert

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘Admin123’

GO

CREATE CERTIFICATE Witness_cert

WITH SUBJECT=‘Witness certificate’

GO

CREATE ENDPOINT End_Mirroring

STATE=STARTED

ASTCP (LISTENER_PORT= 5027,LISTENER_IP=ALL)

FOR DATABASE_MIRRORING

(AUTHENTICATION=CERTIFICATE Witness_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = Witness)

GO

BACKUP ERTIFICATE witness_cert

TO FILE=‘C:\certificate\Witness_cert.cer’

GO

Step No 6 on Principle Server : 

Copy the Certificate ” ًWitness_cert  ” From Witness Server to the Principle Server because I will Create the Certificate Witness Server on Principle Server With the User name Witness_user

USE MASTER

GO

CREATE LOGIN Witness_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Witness_user FOR LOGIN Witness_login

GO

CREATE CERTIFICATE Witness_cert

AUTHORIZATION Witness_user FROM FILE=‘C:\certificate\Witness_cert.cer’

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Witness_login]

GO

Step No 7 on Mirror Server : 

Copy the Certificate ” ًWitness_cert  ” From Witness Server to the Mirror Server because I will Create the Certificate Witness Server on Mirror Server With the User name Witness_user

USE MASTER

GO

CREATE LOGIN Witness_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Witness_user FOR LOGIN Witness_login

GO

CREATE CERTIFICATE Witness_cert

AUTHORIZATION Witness_user FROM FILE=‘C:\certificate\Witness_cert.cer’

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Witness_login]

GO

Step No 8 on Witness Server :

Copy the Certificate ” Principle_cert  ” From Principle Server and  ” Mirror_cert  ” From the Mirror Server

” Principle_cert  “

USE MASTER

GO

CREATE LOGIN Principle_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Principle_user FOR LOGIN Principle_login

GO

CREATE CERTIFICATE Principle_cert

AUTHORIZATION Principle_user

FROM FILE=‘D:\certificate\Principle_cert.cer’

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Principle_login]

GO

” Mirror_cert  “

CREATE LOGIN Mirror_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Mirror_user FOR LOGIN Mirror_login

GO

CREATE CERTIFICATE Mirror_cert

AUTHORIZATION Mirror_user

FROM FILE=‘C:\certificate\Mirror_cert.cer’

GO

GRANTCONNECT ON ENDPOINT::End_Mirroring TO [Mirror_login]

GO

Step No 8 :

take Full backup + Log backup from your Database Mirroring on Principle Server then Go to Mirroring Server and restore this Backup With NORECOVERY Option

Step No 9 :

GO TO the principle Server in make your Easy mirroring configuration but in the last Step don’t write any Service Account let it Empty then After this Step Mirroring will be Complete Successfully

See this post if you don’t know how to Configure mirroring Database With SQL management Studio

http://wp.me/p1Oidq-6w  Mirroring part 1

http://wp.me/p1Oidq-7h  Mirroring part 2

http://wp.me/p1Oidq-7w  Mirroring part 3

 

 
1 Comment

Posted by on January 4, 2013 in Mirroring SQL Server 2008

 

How to monitor Database mirroring

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

Database monitore

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

T-SQL

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

3

 

4

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

5

6

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

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

7

 

 
 

How to add or replace database mirroring witness to an existing Mirroring Database?

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

Mirroring operation mode

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

Configure Witness

3- Check Witness Server

Check Witness Server

4- mirroring will return the principle server configuration

principle Configuration

5- Select Witness Server

Select Witness Server

6- Configure Witness Server and the port listner

Configure Witness

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 .

Account Services

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

Witness is Configuerd

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 SQL SERVER MIRROING IS RUNNING OR NOT ?

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

Check port

2-  Event Viewer for Windows Server

Event Viewr

3-  SQL Server Database Log

SQl Server Log

4-  Check Endpoint Status

Check Endpoint

 

 
2 Comments

Posted by on December 31, 2012 in Mirroring SQL Server 2008

 

What is the Stander listener port in Database Mirroring Configuration?

If you Create the 3 SQL Servers in the same PC

Begin

you must use difference port

Principle Server use the Stander port number 5021

Mirroring Server use the Stander port number 5022

Witness Server use the Stander port number 5023

End

Else The 3 SQL in 3 different PC

You can use one port like 5021

Don’t Forget to Disable Firewall or must be open this ports on the Firewall

 
Leave a comment

Posted by on December 29, 2012 in Mirroring SQL Server 2008