T SQL implementation of Database Mirroring

So how to implement T SQL for Database implementation.


Only writing the code will not get you easily understand the concept lying between the using SSMS and TSQL. So let us write and analyze the code step by step.
For this I will recommend first to use SSMS and note the steps.
First we need to take full back up and transaction log of principal and restore to mirror. For this post
Please click here.


First we need to configure Mirroring End points. These End points are more like socket where we want our server to listen to this specific port in concise words.

1)     /****** Object:  Endpoint [Mirroring******/

CREATE ENDPOINT [Mirroring]

                AUTHORIZATION [ALITEST\sqlmirror]

                STATE=STARTED

                AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)

                FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GO

Now Let us see what I have written,

First line indicate creation of new end point name mirroing.

second line indicate user authorization to use this end point.

third line indicate server is allow to listen this end point.

Fourth line indicate parameters for the end point more like configuration.

Fifth line we indicating this end is for mirroing and role can be set to partner, witness but in this case I set all.

Same way create end points for all the server i.e. mirror and witness also

2)     /****** Object:  Endpoint [Mirroring******/

CREATE ENDPOINT [Mirroring]

                AUTHORIZATION [ALITEST\sqlmirror]

                STATE=STARTED

                AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

                FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GO

3)     /****** Object:  Endpoint [Mirroring]   ******/

CREATE ENDPOINT [Mirroring]

                AUTHORIZATION [ALITEST\sqlmirror]

                STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

                FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GO

Now one Last step as noted in the SSMS.

This step only tells the servers who is who i.e. who is principal who is mirror which one is witness.

Setting Principal to be mirror partner 

*** Run this on principal first.

Alter Database <DB name> set partner='TCP:\\Servername'

Setting mirror  to be  Principal  partner 

*** Run this on  mirror .

Alter Database <DB name> set partner='TCP:\\Servername'

Setting principal to be  witness partner 

*** Run this on   principal  .

Alter Database <DB name> set witness ='TCP:\\Servername'

And if all goes well You have successively started mirroring via TSQL

P.S Run all the script separately on respective servers or this will fail.