Database mirroing is High availability feature of SQL Server  available from SQL Server 2005 and later,

mirroring requirements

both the SQL Services must started with the same user name and the user should have Administrator credentials for that domain, as below

1

step 1:-

Take the full backup  and transaction log backup from the primary server and restore it on the secondary server in no recovery mode(in this mode no users can connect to the database).

step 2:-

Open SQL Server Management studio connect to the primary server.

Right click on the database. Go toTask then click mirror

2

Click on configure security

3

click next

4

here if you are using synchronous with automatic fail over then select a witness, witness server can be a SQL  Server Express edition, if not simply select NO, in this example i am not using witness server , so we will have manual Failover

5

assign the port for principal server and check to add in exception in Firewall and in anti Virus.by default 5022 is assigned by SQL Server.

7

before selecting a port number foryou have to get conneted with the mirror server and thereafter set the port number

9

after connecting and assigning port number click next

click  next

click on Start mirroring, before make sure that FQDN(Fully Qualified Domain Name ) is set, to check FQDN , tou can even give the IP od the server

to have a test the failover just click on the failove button as below

for manual Failover

use master
go
ALTER DATABASE test SET PARTNER FAILOVER

This query is used to run on Principle/Primary Server, if your Principle server is SHUTDOWN for any reason, then the only thing you have to do is change the Mirrored Server to a Priciple server and the Database has to be accessed by the users.

Log on to your Mirrored server, open SSMS, and write this query

use master
go
alter database test set partner off
go
restore database test with recovery

note:- As i have used test as a database, you have to replace your database name with test

5 thoughts on “Database mirroring in simple steps

  1. perfect solution

    work for me this solution

    In case of crash happen in primary, we can still use mirror DB without witness server.

    Perfect, thank you for posting this solution !!!

    -shabbir

Leave a Reply

Your email address will not be published. Required fields are marked *