1-Shared Memory: A simple protocol using no configuration which working on the same environment ” client which is on the same physical machine as SQL Server “ it’s provide a fast communication so it’s no useful for more (DBS) but it’s just for Troubleshooting.
2-TCP/IP: A Connection-Oriented Protocol that’s systems agree both connection with each other, TCP use different applications share it through Ports, when A services like SQL Server come to run it must use a specific port to access through it .. For a default SQL Server instance, the default port is 1433, the main protocol which clients communicate with SQL Server. Client and Server use Tabular Data Stream[TDS] which sit on the top of TCP/IP, it communicate with all networks and pass(DBS), it also include A Routing ,Traffic and Security system .. It’s configured by default TCP/IP is a good choice for network protocol if your application does not require SQL Server’s integrated security.
3-UDP: unlike TCP, is not connection oriented. Client can send a UDP communications to anyone it wants. There’s nothing in place to negotiate a communications connection, it’s great for short communications that doesn’t necessarily need an acknowledgement, it uses ports just like TCP. The SQL Browser or the SQL Server Listener Service uses UDP.
4-Named Pipes: It’s for a local Area Network [LAN] its pass information from one to other but in the same network, it uses a part of memory of one process to store/pass the information, it means the output of one process becomes the input for another process. Named Pipes is an excellent choice for network protocol if your application intends to use SQL Server’s integrated security and does not need other protocols for remote connections.
5-Virtual Interface Adapter VIA: it’s work with VIA hardware, conﬁguration details are available from your hardware vendor.
⇔ TCP/IP is the preferred protocol in a slow LAN, WAN or dial up network.
⇔ Named Pipes is more considerable in fast local area networks.
⇔ Endpoint: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. It’s about a transport protocol and a port number
Database Mirroring Endpoint
To make Database always on as a way of High-Availability we have to set an Endpoint to ensure the connection between the servers instances, it use the TCP protocol to send and receive messages between the server instances and This database mirroring endpoint use a Unique port number to establish the communication, The port number uniquely identifies a specific server instance. This figure show how two server instances on the same server are uniquely identified. ⇔ SQL server instance by default not have an Endpoint it must be create manual and configure it.
⇔ The DBA is the responsible for opening ports through firewall
SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = ‘DATABASE_MIRRORING’
Replication connections to SQL Server use the typical regular Database Engine ports (TCP port 1433 for the default instance, etc.) Web synchronization and FTP/UNC access for replication snapshot require additional ports to be opened on the firewall. To transfer initial data and schema from one location to another replication can use:-
- FTP (TCP port 21)
- sync over HTTP (TCP port 80)
- File Sharing.
♦ File sharing uses UDP port 137 and 138, and TCP port 139 if it using Net-BIOS. after Windows 2003, File Sharing uses TCP port 445.
♦ For sync over HTTP, replication uses the IIS endpoint (configurable ports it’s port 80 by default)
♦ The IIS process connects to the backend SQL Server through the standard ports (1433 for the default instance. During Web synchronization using FTP, the FTP transfer is between IIS and the SQL Server publisher.
Log shipping Considerations over the Firewall
While Log shipping uses SMB communication to copy the log files from the Network Share to the local destination folder and hence in order to configure over Firewall
♦ NetBIOS over TCP/IP is enabled
- TCP ports 137,139
- UDP ports 137,138
♦ Direct Hosting of SMB over TCP/IP
- TCP port 445
The following ports must remain open if going through a firewall:-
- Port 1433 must remain open for SQL to communicate back and forth.
- Port 135 required for SQL Agent to copy backup files to the shared folder on the standby server.
- Ports 137, 138, and 139 or 445 required to use UNC shares.