MS SQL 2012 Mirror Notes

Environment

Server A : Windows 2008 R2 Standard, SQL 2012 Enterprice, IP : nnn.nnn.nnn.nnn
Server B : Windows 2008 R2 Standard, SQL 2012 Enterprice, IP : xxx.xxx.xxx.xxx

Server A & B both switched SQL instance port from default 1433 to ZZZZ


Prepare

Server A & B both create one same window Account/Password 

SQL Server Configuration Manager
Server A & B both start SQL Server and SQL Server Agent with above account

SQL Server Management Studio
Sercurity -> Logins, Make sure above account with Server Roles "public" & "sysadmin"

Firewall
Make sure the mirror port has passed (default is 5022)



Start

Backup the database from Server A, SSMS : Tasks->Back Up

Copy the backup file to Server B

Restore the database in Server B

Select the backup file

Make sure Recovery state is "RESTORE WITH NORECOVERY"

Restore DONE

P.S Also backup & restore transaction logs if necessary

Back to Server A, SSMS : Tasks ->Mirror

Click "Confirure Security"

Configure Database Mirroring Security Wizard

I don't use Witness Server, so I select "No" here

Enter Listener port(default is 5022, I use 5023 in the demo) and Endpoint name

Click "Connect" to connect to MSSQL instance of Server B, note that due to I use another port for SQL instance(default is 1433), so you can see additional port number here

Leave them empty

Complete the wizard

Setup succeed

After closing the wizard, this message popup, I click "Do Not Start Mirroring" here, because there are still something need to finish

If click "Start Mirroring" above, it's highly possibility to see this error

In my case, because I use my custom port for SQL Instance, so I will need to remove it here

Then click "Start Mirroring", succeed

Server B

Other Notes

Remove a mirrored database
master -> alter database [database] set partner off
Then drop

While Principal DB is down, switch Mirror to Principal database
master -> alter database [DB Name] set partner force_service_allow_data_loss

Remove a restoring database
master -> restore database [DB Name] with recovery
Then drop

Manually create endpoint
master ->
CREATE ENDPOINT [endpoint name] STATE = STARTED
AS TCP (
      LISTENER_PORT = 5022
      ,LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
      ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   )

Manually set partner
master -> alter database [db name] set partner = 'TCP://xxx.xxx.xxx.xxx:5022'

Mirror GUI of SSMS actually does following setup for us
1. Create Endpoints on Server A & B
2. Set Partner of Principal DB of Server A to Server B
3. Set Partner of Mirror DB of Server B to Server A


In Database Mirroring Monitor or when we need to resume mirroring might see the situation

Solution :