MS SQL network ports

Quick cheat sheet for port numbers used by SQL Server services or services that SQL Server may depend on:

21	TCP	FTP (replication)
80	TCP	HTTP endpoints, Reporting Services, HTTP replication
135	TCP & UDP	RPC, WMI, MSDTC, SQL Agent file copy, and TSQL Debugger (RPC used for multiple purposes including SSIS and clustering.)
137	UDP	File & Print Sharing (replication) and Cluster Admin
138	UDP	File & Print Sharing (replication)
139	TCP	FileStream and NetBIOS Session Service (clustering)
443	TCP	HTTPS endpoints and Reporting Services
445	TCP & UDP	FileStream, SMB (clustering), and File & Print Sharing
500	UDP	IPSec
860	TCP	iSCSI
1024-5000	TCP	Original dynamic ports for named instances. (WinSock standard.)(See ports 49152-65535.) The DoD Database STIG requires static ports.
1433	TCP	SQL Server database engine
1434	TCP & UDP	SQL Server database engine, DAC, and SQL Server’s «Browse» button.
2382	UDP	Analysis Serviceswhen using dynamic ports with named instances
2383	TCP	Analysis Services
2393-2394	TCP	Analysis Services version 7
2725	TCP	Analysis Services
3260	TCP	iSCSI
3343	UDP	Cluster network driver
3389	TCP	Remote Desktop Protocol (RDP)
3882	TCP	DTS/SSIS
4022	TCP	Conventional port for the SQL Broker service
4500	UDP	IPSec
5000-5099	UDP	Clustering
5022	TCP	AlwaysOn’s default port for primary and secondary replicas
7022	TCP	Conventional port for Database Mirroring
8011-8031	UDP	Clustering internode RPC
49152-65535	TCP	Latest dynamic ports for named instances. (WinSock standard.)(See ports 1024-5000.) The DoD Database STIG requires static ports.

Microsoft recommends non-default ports for maximum security.

Check ports in use: SELECT ServerProperty(«ProcessID»)
At a command prompt: «netstat -ano»

AlwaysOn Ports

Each instance w an Availability Group (AG) must have a database mirroring endpoint, and they endpoints bust be started (query sys.database_mirroring_endpoints and sys.tcp_endpoints).
Logins from a remote server must have CONNECT permission. Each instance must have access to ports on all partners.

MSSQL в Nagios

Появилась задача — мониторинг состояние MSSQL сервера средствами Nagios.

был выбран плагин check_mssql_health (https://labs.consol.de/nagios/check_mssql_health/)

для работы были уставлен модуль Perl

cpan
install DBD::Sybase

на сервера БД был добавлен пользователь nagiosadmin , с паролем nagiosadmin и правами serveradmin

В command.cfg добавляем команду проверки:

# ' check_mssql_health' command definition
define command{
command_name check_mssql_health
command_line $USER1$/check_mssql_health --server=$HOSTADDRESS$ --username=nagiosadmin --password=nagiosadmin --mode=$ARG1$ --commit --units=$ARG2$ --warning=$ARG3$ --critical=$ARG4$
}

В проверку хоста добавляем

define service{
        use                     generic-service
        host_name               srv-1c-db01
        service_description     MSSQL transactions
        check_command           check_mssql_health!transactions!B!40000!50000
        }

define service{
        use                     generic-service
        host_name               srv-1c-db01
        service_description     MSSQL connected-users
        check_command           check_mssql_health!connected-users!B!150!200
        }

define service{
        use                     generic-service
        host_name               srv-1c-db01
        service_description     MSSQL cpu-busy
        check_command           check_mssql_health!cpu-busy!B!80!90
        }

define service{
        use                     generic-service
        host_name               srv-1c-db01
        service_description     MSSQL io-busy
        check_command           check_mssql_health!io-busy!B!80!90
        }

define service{
        use                     generic-service
        host_name               srv-1c-db01
        service_description     MSSQL full-scans
        check_command           check_mssql_health!full-scans!B!800!900
        }

define service{
        use                     generic-service
        host_name               srv-1c-db01
        service_description     MSSQL locks-waits
        check_command           check_mssql_health!locks-waits!B!80!90
        }

define service{
        use                     generic-service
        host_name               srv-1c-db01
        service_description     MSSQL total-server-memory
        check_command           check_mssql_health!total-server-memory!GB!80!90
        }

В итоге получаем :

Восстатовление базы из состояния suspect

Может не самый красивый, но заодно действенный метод

alter database TESTDB set emergency
go
alter database TESTDB set single_user
go
dbcc checkdb (‘TESTDB ‘, REPAIR_ALLOW_DATA_LOSS)
go
alter database TESTDB set multi_user