Usage of Endpoints:
Endpoints on both the principal, mirror and witness (if being used)
which allows the SQL server instances to communicate with each other.
Database mirroring is a very handy HA(High Availability) solution available per database for SQL 2005/2008 and is available in both Enterprise and Standard edition. This solution also provides both automatic and manual failover. Therefore it is a HA solution available to almost all SQL server users.
Database mirroring overview is made up of a principal server role and a mirroring server role plus an optional witness server. The principal database is available for serving client and user connections. When transactions are applied to the principal database these transactions are then submitted to the mirror servers database, however the mirror server database cannot serve user requests as the mirror database is left in a recovering state.
The general steps for configuring database mirroring involves the following steps: –
1. Create Endpoints on both the principal, mirror and witness (if being used) which allows the SQL server instances to communicate with each other.
2. Create a copy of the principal database on the mirror server by restoring a backup of the database from the prinicpal server to the mirror server with the NO RECOVERY option.
3. Start the mirroring session to tell SQL server which endpoints are connected to which partner and witness server.
It seems that the most often overlooked or misconfigured components are the endpoints. Since all mirroring information is done over the endpoints it is obviously a critical component to check if things are not working.
Many people seem to believe that the endpoints are stored in the principal, mirror and witness databases. Why each component involved in the mirroring communication requires an endpoint to actual endpoint configuration is stored in the master database of the instance. Therefore it is possible to check the configuration on all nodes in the mirroring configuration.
The following T-SQL code will output information for the endpoints and if you are having issues with your new mirroring session you should run this to check the endpoints are configured correctly on your principal, mirror and witness (if being used).
SELECT perm.class_desc, perm.permission_name, endpoint_name = e.name, e.state_desc, e.type_desc, t.port, perm.state_desc, grantor = prin1.name, grantee = prin2.name FROM master.sys.server_permissions perm INNER JOIN master.sys.server_principals prin1 ON perm.grantor_principal_id = prin1.principal_id INNER JOIN master.sys.server_principals prin2 ON perm.grantee_principal_id = prin2.principal_id LEFT JOIN master.sys.endpoints e ON perm.major_id = e.endpoint_id LEFT JOIN master.sys.tcp_endpoints t on t.endpoint_id = e.endpoint_id WHERE perm.class_desc = 'ENDPOINT' AND e.type_desc = 'DATABASE_MIRRORING'
order by endpoint_name ASC
This will provide you with very useful information such as the endpoint state (started, stopped, etc), TCP port that the endpoint is using and permissions that have been assigned to the endpoint along with which user has been granted connect permissions to the endpoint. You should execute this on each of the mirror instances such as the principal, mirror and witness.
Obviously with this information you can confirm that the SQL servers can communicate over the TCP ports. You can confirm this via telnet <servername> <PORTNUMBER>. If there is a firewall issue, etc this simple check and test will let you know.
You should also pay particular attention to which grantee has been assigned connect permissions to which endpoint. Your designated user from the principal instance needs connect permissions on the endpoint in the mirror instance and the mirror instance user requires connect permissions to the endpoint on the principal instance.
We have two physical servers on the different area protected by firewalls. We need to use DB Mirroring for our purpose. So which ports we need to open between two area for using MS SQL 2008 DB Mirroring?
There is no default port used for MS SQL database mirroring. It's set by the administrator. So, discuss with your SQL admin and your Firewall admin and choose an appropriate port.
Administrator chosen port. To determine the port, execute the following query:
11. Start the mirroring
you have mirrored you SQL! Go to both servers and it should look like this now.
Note: You might find an issue when you start mirroring and encounter this error The mirror database, “YourDatabaseName”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478) As the error suggest you need to back up the Principal SQL Server Transaction Logs and Restore it to the Mirroring SQL Server using the same restore options when you restored the database. If this happen you can cancel the wizard and start configuring again after this step from step 4.