So I’ve heard about endpoints, but what are they and how do I use them?

In a nutshell Endpoints are ways that people, or applications can connect to the SQL Server.  There are several different kinds of end points which can be created; four to be specific.  Two are system specific, the SERVICE_BROKER and DATABASE_MIRRORING endpoints can only be used for the SQL Service Broker and Database Mirroring respectively. The other two are for general use.  They are the SOAP and TSQL endpoints.

Without knowing it you use an endpoint to connect to the SQL Server each time you connect.  There are actually 5 endpoints created by default on each instance of SQL Server.  You can check then out by querying the sys.endpoints DMV.  When you connect to the SQL Server using TCP (port 1433 by default) you are using the TSQL Default TCP endpoint.  By default all users have the rights to connect to this endpoint.  You can create other TCP endpoints on different ports for specific users to connect to.  This would be handy if you have several applications coming into the SQL Server from a single server, and you wanted to be able to separate there traffic through the firewall so that the network admins could watch the traffic in the event of an issue.  You could create a TCP endpoint for each application, and assign only that application IP rights to use that endpoint.  You then have the application specify the port number that it will be connecting through in the connection string.

The SOAP endpoints are used in a similar way, but instead of allowing regular TSQL connections they allow SOAP calls to be made directly against the database.  (I’m not that up to speed on SOAP so that’s about all I’ve got on that topic.)

 Endpoints are created with the CREATE ENDPOINT command with various switches depending on what kind of endpoint you are creating and how much security you require on the endpoint.

The endpoint that I’ve used the most would have to be the service broker endpoint.  It’s used to allow SQL Server service broker on one SQL Server to talk to the Service Broker of another SQL Server.

One thing to remember about endpoints is that they are used for inbound connections only.  Outbound connections do not require or use an endpoint.



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?