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
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 :
