EndPoints -- These are used to define how servers will communicate each other. Database mirroring is based on TCP endpoints to send and receive information between servers instances that are exists on mirroring sessions. One TCP endpoint will be used for multiple mirroring sessions. In database mirroring only committed transaction data is sent to mirror instance, rolled-back transaction data is not sent to the mirror server, however in log shipping both committed and rolled-back information will sent to secondary server.
Requirements
Service Accounts will need to be granted permission to connect to endpoints on the other partner instances (principal, mirror and witness). If SQL Server is not running in single domain the certificate authentication need to be used.
Creating Endpoints -- Configuring endpoints (port listeners) where the principal and mirror are going to establish communication.
Principal – Create Endpoint Principal_Endpoint state=Started as TCP(Listener_port = 5022) for database_mirroring (role = partner) – Enable as Partner only
Mirror – Create Endpoint Mirror_Endpoint state=Started as TCP(Listner_port=5023) for database_mirroring (role = all) – Enable as mirror or witness server
To view mirroring endpoints
Select name, role_desc, state_desc from sys.database_mirroring_endpoints;
Important points
Database mirroring is high availability solution for database
Database mirroring can be configured only with FULL recovery model database
Express edition can be configured only as witness server
Database mirroring cannot be configured with multiple mirror servers
Mirroring is specific to database, so logins will not moved to mirror server, need to move them manually
We cannot configure mirror for master, model, msdb and tempdb databases
Include failover partner connection string in ADO.NET to make use of automatic failover
One witness can be configured with multiple mirror servers
Mirror can be used as read-only using snapshots.
Snapshots can be created on mirror server.
Write a script on active instance to enable Agent jobs when failover happened, if automatic failover is configured.
No comments: