What is Database Mirroring End Points? Usage of End Points? More info about End Points in DATABASE MIRRORING

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).

USE master 

GO 

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. Database Mirroring Administrator chosen port. To determine the port, execute the following query: SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING' There is no default port for Database mirroring however Books online examples use TCP port 7022. It is very important to avoid interrupting an in-use mirroring endpoint, especially in high-safety mode with automatic failover. Your firewall configuration must avoid breaking quorum. For more information, see Specifying a Server Network Address (Database Mirroring). It depends on the endpoint authentication type used. The DBM endpoint itself will use only one port, typically configured at 5022 (TCP). But DBM supports two types of authentication: WINDOWS and CERTIFICATE. If CERTIFICATE is used then the DBM port is enough. However when WINDOWS authentication is used then a Kerberos or NTLM handshake has to succeed between the two servers and these require their own ports,  Typically you'll need 135 (TCP) and 88 (TCP/UDP), sometimes 445. Again, these are not required if the DBM authentication is configured to use CERTIFICATE. Altering Mirroring Endpoints (Ports) on SQL Server We are in a middle of a SQL Server 2012 migration project and one of the main tasks is to completely mirror all of the databases. Having said that it does not mean we did not mirrored it before, some of the legacy databases are already mirroring and we have one dedicated server to do the job and that’s what it only does. With this project we want to install a new instance of SQL Server 2012 to be as witness for other SQL Server 2012 databases on top of SQL Server 2008 already installed in the server, so after installation we then setup the mirroring and then we had this error: TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'YourDatabase'.  (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476 ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address TCP://servername:5022 can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476 ------------------------------ BUTTONS: OK ----------------------------- Now that is giving you an exception because port 5022 is used by the old SQL Server Witness, which means you need to change this port so that it would work. Run this TSQL command in your Witness server instance to check if indeed 5022 is being used and at the same time what other ports you can use SELECT * FROM sys.tcp_endpoints To change that all you have to do is select a port number not on the list below as well as any port number that is not used by your server and applications, so for this example lets use 5023, you have to issue this command on your SQL Server Witness ALTER ENDPOINT [Mirroring] AS TCP (listener_port = 5023) Once done restart you will see “command(s) completed successfully“, you need to restart the SQL Server for it to take effect.  Now go back and configure your mirror again and it will now connect to the 5023 port Let see example How to Configure Database Mirroring in SQL Server: Mirroring a database is not really a hard task to do, you just need this easy and simple 11 steps to start mirrored SQL Server.  Doing it will not just improve you disaster recovery capabilities on your application but also you are leveraging the high availability database mirroring feature you find in SQL Server 2005 and above, which means it will allow failover of database in the event you lose your main SQL Server. Before starting this be sure that you have 3 SQL Servers in different locations for best result.  2 Servers need to have the identical SQL Server instance which means same version (either Standard or Enterprise) also its highly recommended that also the service pack and if any cumulative updates are the same on both servers.  But for the third server it can be SQL Server Standard, Enterprise, Workgroup, or Express.  Witness Server will be the one pinging the other 2 servers if there’s something wrong, this is the server that has the ability to recognize whether to initiate an automatic failover, this will not contain any database that’s why its nonsense to use a SQL Server other than Express edition. 1. Verify the following a. You have 3 SQL Servers for Principal, Mirror and Witness b. SQL Server is using an Active Directory account. Ideally, use the same account for all SQL Servers. b. Primary Database is in Full Recovery model. 2. Back up the database on the Principal SQL Server 3. Create a database with the same name from the Principal SQL Server on the Mirroring SQL Server, then restore the backup on the Mirroring SQL Server with the option to Overwrite the existing database checked and RESTORE WITH NORECOVERY option You will notice it’s in a Restoring mode. Don’t panic this is normal as you have chosen the NORECOVERY option and it will be in a permanent Restoring state to prevent users accessing the database.  It will be only user accessible if the database fails over to the Mirror and now the old Principal will go to the recovering state. 4. Start the mirroring configuration process on the Principal SQL Server, Right-click the Database –> Properties –> Mirroring and click Configure Security. 5. On the Include Witness Server screen, select Yes and click next 6. Now choose Principal SQL Server Instance 7. Now choose Mirror SQL Server Instance 8. Choose a Witness Instance 9. Now enter the SQL Server Service Accounts for each SQL Server Instance, but if all of your SQL instances are using the same account then just leave it blank 10. Completing the Wizard 11. Start the mirroring you have mirrored you SQL!  Go to both servers and it should look like this now. Principal Server:  Mirrored Server: 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.