Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 27 June 2016

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.