Survey report on 445 ports used in SQL Server AlwaysOn

Source: Internet
Author: User
Tags failover ldap knowledge base

SQL Server AlwaysOn Zhong 445 Survey report on port usage


Part I: official documentation from Microsoft


Microsoft Knowledge Base article 832017:service overview and network port requirements for Windows


For our production environment SQL Server AlwaysOn FCI & AG Hybrid deployment, based on Active Directory and cluster Service.


The following information is referenced from KB 832017 and describes the related port requirements based on these two services:


1. Active Directory (Local Security Authority)

Active Directory runs under the Lsass.exe process and includes the authentication and replication engines for Windows DOMA in controllers. Domain controllers, client computers and application servers require network connectivity to Active Directory over Specifi C hard-coded ports. Additionally, unless a tunneling protocol is used to encapsulate traffic to Active Directory, a range of ephemeral TCP por TS between 49152 to 65535 is required.


List of dependent ports:

Application protocol

Protocol

Ports

Active Directory Web Services (ADWS)

Tcp

9389

Active Directory Management Gateway Service

Tcp

9389

Global Catalog

Tcp

3269

Global Catalog

Tcp

3268

Icmp


No Port number

LDAP Server

Tcp

389

LDAP Server

Udp

389

LDAP SSL

Tcp

636

IPsec ISAKMP

Udp

500

Nat-t

Udp

4500

Rpc

Tcp

135

RPC randomly allocated high TCP ports1

Tcp

1024-5000
49152-655352

Smb

Tcp

445


2. Cluster Service

The Cluster service controls Server Cluster operations and manages the Cluster database. A cluster is a collection of independent computers, act as a single computer. Managers, programmers, and users see the cluster as a single system. The software distributes data among the nodes of the cluster. If a node fails, other nodes provide the services and data that were formerly provided by the missing node. When a node was added or repaired, the cluster software migrates some data to that node.


System Service Name: CLUSSVC

Application

Protocol

Ports

Cluster Service

Udp

3343

Cluster Service

Tcp

3343 (this port is required during a node join operation.)

Rpc

Tcp

135

Cluster Administrator

Udp

137

Randomly allocated high UDP ports1

Udp

Random port number between 1024x768 and 65535
Random port number between 49152 and 655352

Note:

Additionally, for successful validation on Windows Failover Clusters on + and above, allow inbound and outbound traffic For ICMP4, ICMP6, and Port 445/tcp for SMB.


Part Two: The reply from Microsoft Data Platform MVP Mail list



Part III: In-depth learning about the principles of AD and WSFC


1. Ad-to-445 dependency

When each client is powered on, it needs to access a SYSVOL share on the DC inside the ad and download the Group Policy. Each boot will be accessed through this sharing Group Policy.


SYSVOL:

SYSVOL is a shared folder that stores copies of the domain's public file servers and replicates between all domain controllers in the domain. The Sysvol folder is created when you install AD, and it is used to store information such as GPOs, script, and so on. Also, the information that is stored in the Sysvol folder is copied to all DCs in the domain.


Netlogon:

The Netlogon (network logon) service registers all SRV resource records for the domain controller. These records appear in the DNS server's forward query area in your domain name in the _msdcs, _sites, _tcp, and _udp folders. Other computers use these records to query information about the domain's Active Directory.


2. WSFC dependency on 445

The cluster database is one of the main concepts in the operation of WSFC, and the cluster database contains the current state of the cluster application, for example, the current node 1 runs a DHCP role, the status is on-line, a file server role is running, the status is offline, and the cluster configuration, cluster member configuration, cluster resource additions, creation , start, delete, stop, offline and other state changes, the cluster database is to help each node know what the other side of the Cluster service running, once the other side down, will follow the status information inside the cluster database connected to the shared storage for failover on-line operation.


WSFC is used in the replication cluster database through administrative sharing. The cluster database is in the node C and registry, and when one node modifies the cluster information, it is replicated to the other nodes and to the witness disk.


One node goes down, other nodes refer to the cluster database, access the shared disk, and go online to the app hosted by that node. The daily runtime is synchronized at each node to ensure that each node records consistently.


Part IV: Learning about new features related to Windows Server 2016 and SQL Server 2016/2017


1. Domain Independent Availability Groups (a new feature introduced in SQL Server 2016)

Https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups


In Windows Server + SQL Server 2016 environments, AlwaysOn AG supports the construction of AG in a workgroup environment, or across a domain environment. Even if no domain ag,ag is required to support WSFC, and WSFC needs to open 445 ports, FCI so far, still need to be deployed in a domain environment. This feature does not eliminate 445 port access.


2. Distributed availability groups (a new feature introduced in SQL Server 2016)

Https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups


Distributed availability groups, which can extend AlwaysOn ag across two different wsfc, to expand AG. This feature also does not eliminate 445 port access.


3. Read-scale availability groups (a new feature introduced in SQL Server 2017)

Https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups


In SQL Server 2016 and earlier versions, all availability groups require clustering. Clusters are used to provide business continuity for high availability and disaster recovery (HADR). Additionally, the secondary replica is configured to perform read operations. If the target is not high availability, configuring and running the cluster consumes considerable operational overhead. SQL Server 2017 introduces a read scale availability group that does not require clustering.


If the business requirement is a resource that transforms a task-critical workload running on the primary replica, the user can now use read-only routing or connect directly to a readable secondary replica. Without the need to rely on integration with any clustering technology. These new features are supported by SQL Server 2017 on Windows and Linux platforms.


This is not a high availability configuration. There is no need to monitor infrastructure, coordinate fault detection, and automate failover. Without clustering, SQL Server cannot provide a low recovery time objective (RTO) that can be provided by an automated high availability solution. If you need high availability features, use Cluster Administrator (Windows Server failover cluster on Windows or Pacemaker on Linux).


The Read scale Availability group provides disaster recovery capabilities. Recovery point Objective (RPO) 0 is available when a read replica is in synchronous-commit mode. To fail over the read scale availability group, see fail over the primary replica on the read scale availability group.


Overall, this feature has only the DR function and does not support ha. This feature is intended to be used only to deploy multiple read replicas on multiple servers, sharing read pressure.


4. SQL Server availability Groups on Linux

Https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-overview


Configure AG on Linux based on pacemaker.


5. SQL Server Failover Cluster Instance on Linux

Https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-concepts


Configuring an FCI official document on Linux is also rudimentary and, from shared storage, currently supports iSCSI, NFS, and SMB protocols. Look forward to follow-up support for fiber protocols and hybrid deployment with AG.


Part V: Solutions and recommendations for the prevention of worm viruses


Under analysis, there are 3 solutions:

1. Upgrade the appropriate security patches

For important security patches It is recommended to hit, from secondary to primary in Maintenance window rolling upgrade.


2. Modify the database server share port

Based on previous experience, you can modify 445 ports on the client and server side, and the share will function properly. However, this scenario does not prevent access to the share.


3. Place the database server into the DMZ zone

Based on past experience, e-commerce company in order to pass the PCI international authoritative certification, in the construction of the PCI environment, the production payment and account database into the environment, in fact, is a DMZ area, restricting access to the Regional database server IP and port.


I suggest:

1. Hit the security patch on the SMB vulnerability.

2. Access the database server to the DMZ zone.


Survey report on 445 ports used in SQL Server AlwaysOn

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.