Em 30/04/2017
Olá, boa noite….
Final de noite de domingo, véspera de feriado e nosso Brasil desde a última sexta – feira dia 28/04 vivendo fortes emoções na política, economia, esporte e principalmente cidadania. Alias dia 28/04/2017 uma das datas mais importantes da minha vida, neste dia comemorei mais uma primavera como gostam de dizer alguns dos meus familiares, já se vão 37 anos, muitos destes anos dedicados a minha esposa, filhos, filha, trabalho e principalmente a áreas de educação e tecnologia.
Aproveito para agradecer a todos os amigos, colegas, familiares, alunos, enfim pessoas que por algum momento passaram pela minha vida nestes últimos 37 anos.
Dando continuidade, este é o novo post da sessão Para que serve, sendo o post de número 14, muito bom, lentamente esta sessão esta ganhando corpo e força com os meus seguidores.
Hoje vou destacar um conteúdo bastante simples e direto, mas muito interesse e bastante útil, que consiste basicamente em como através da linguagem Transact-SQL podemos identificar ou até mesmo descobrir quais portas de rede estão em uso em uma instância ou servidor SQL Server baseadas no protocoloTCP/IP e na versão IPV4 do protocolo IP.
Isso pode parecer algo bastante simples de ser feito, na verdade é mesmo, mas até a versão do SQL Server 2008 R2 SP1 era um pouco chato e até mesmo complexo para se obter esta simples informação, cenário que muito drasticamente a partir da versão 2012 e se mantem presente na versão 2016.
Desta forma, seja bem – vindo ao #14 – Para que serve – Identificando as portas de rede TCP/IP através da DMV – sys.dm_tcp_listener_states.
Introdução
Obter informações sobre as portas de rede utilizadas por uma instância ou servidor SQL Server, por mais simples que parece ser era considerada por muitos profissionais de bancos de dados uma das tarefas mais chatas e até mesmo tediosas pelo simples fato de não existir especificamente uma ferramenta da Microsoft dedicada para este cenário, mesmo assim existem algumas possibilidades que podemos ou não considerar práticas ou inseguras.
A seguir apresento as possibilidades mais conhecidas:
BPCheck: Não pode ser considerada dentre as possibilidades a mais conhecida, muito menos a mais simples, mas sim a mais completa no conjunto de dados retornados para o usuário. OBPCheck – Best Practices and Performance Check, criado em 28-07-2011 por Pedro Lopes (Senior Program Manager for the Microsoft SQL Server Product Group – Tiger Team), com base na versão 2005 do SQL Server e mantido até as versões atuais.
Posso dizer, que este é um daqueles scripts mágicos criados pelos maiores profissionais do SQL Server espalhados pelo mundo, dentre os quais o Pedro Lopes faz parte, o nível de complexidade existente no código fonte deste arquivo comprova o grau de conhecimento e capacidade técnica que este profissional apresenta.
Microsoft SQL Server 2008 e 2008 R2: Microsoft trabalhou e adicionou a partir da versão 2008 R2 SP1 uma forma não muito usual, nem muito interessante de se obter informações sobre as portas de rede fazendo uso da DMV – Dynamic Management View (Visão de Gerenciamento Dinâmico): sys.dm_server_registry, onde era possível coletar informações com base nas chaves de registro do Windows, o que sinceramente não podemos dizer que é algo muito indicado ou até mesmo seguro, mesmo assim era a única forma direta através do Management Studio de se encontrar estas informações. Esta DMV apresenta o seguinte conjunto de colunas:
| Nome da coluna | Tipo de dados | Descrição |
| registry_key | nvarchar(256) | Nome da chave do Registro. Permitir valor nulo. |
| value_name | nvarchar(256) | Nome do valor da chave. Este é o item mostrado na coluna Nome do Editor do Registro. Permitir valor nulo. |
| value_data | sql_variant | Valor dos dados da chave. Este é o valor mostrado na coluna Dados do Editor do Registro para uma determinada entrada. Permitir valor nulo. |
Microsoft SQL Server 2012: Talvez pode ser considerada até o presente momento a forma mais de se obter através de uma ferramenta gráfica neste caso o Management Studio as informações relacionadas a portas e protocolos de rede TCP/IP, fazendo-se uso da DMV – Dynamic Management View (Visão de Gerenciamento Dinâmico): sys.dm_tcp_listener_states, introduzida neste versão do SQL Server. Esta DMV apresenta o seguinte conjunto de colunas:
| Nome da coluna | Tipo de dados | Descrição | ||
|---|---|---|---|---|
| listener_id | int | A ID interna do ouvinte. Não permite valor nulo.
Chave primária.
| ||
| ip_address | nvarchar48 | O endereço IP do ouvinte que está online e está sendo escutando no momento. IPv4 ou IPv6 é permitido.Se um ouvinte possuir os dois tipos de endereços, eles serão listados separadamente.Um curinga de IPv4, exibido como “0.0.0.0”. Um curinga de IPv6, exibido como “::”.
Não permite valor nulo.
| ||
| is_ipv4 | bit | Tipo de endereço IP
1 = IPv4
0 = IPv6
| ||
| port | int | O número da porta na qual o ouvinte está escutando.Não permite valor nulo. | ||
| Tipo | tinyint | Tipo de ouvinte, um dos seguintes:
0 = Transact-SQL
1 = Service Broker
2 = Espelhamento do banco de dados
Não permite valor nulo.
| ||
| type_desc | nvarchar(20) | Descrição do tipo, um dos seguintes:
TSQL
SERVICE_BROKER
DATABASE_MIRRORING
Não permite valor nulo.
| ||
| state | tinyint | O estado do ouvinte do grupo de disponibilidade, um dos seguintes:
1 = Online. O ouvinte está escutando e processando solicitações.
2 = Reinício pendente. o ouvinte está offline, pendente de uma reinicialização.
Se o ouvinte do grupo de disponibilidade estiver escutando na mesma porta que a instância do servidor, esses dois ouvintes sempre terão o mesmo estado.
Não permite valor nulo.
| ||
| state_desc | nvarchar(16) | Descrição doestado, um dos seguintes:
ONLINE
PENDING_RESTART
Não permite valor nulo.
| ||
| start_time | datetime | Carimbo de data/hora que indica quando o ouvinte foi iniciado. Não permite valor nulo. |
Bom, agora que já conhecemos as possibilidades de se coletar as informações relacionadas a portas e protocolos de rede, vamos colocar a mão na massa ou melhor no teclado e por em prática o uso das DMVs: sys.dm_server_registry esys.dm_tcp_listener_states.
Exemplos
1 – Identificando a Default Port através da sys.dm_server_registry:
SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Default Port’ FROMsys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpPort%’
AND CONVERT(float,value_data) > 0
Go
2 – Identificando a Dynamic Port através da sys.dm_server_registry:
SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Dynamic Port ‘ FROMsys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpDynamicPort%’
AND CONVERT(float,value_data) > 0
Go
3 – Obtendo a relação de Listeners, Ports, Protocols e demais dados relacionadas a network através da sys.dm_server_registry:
select Registry_key, Value_Name, Value_Data FROM sys.dm_server_registry
where registry_key like ‘%SuperSocketNetLib%’
Go
4 – Identificando a Default Port através da sys.dm_tcp_listener_states:
SELECT port As ‘Default Port’ FROM sys.dm_tcp_listener_states
WHERE is_ipv4 = 1
AND [type] = 0
AND ip_address <> ‘127.0.0.1’
Go
5 – Obtendo a relação de Listeners, Ports e Protocols através da sys.dm_tcp_listener_states:
Select listener_id, ip_address, is_ipv4,
Port, Type, type_desc, state_desc,
start_time
from sys.dm_tcp_listener_states
Go
Show de bola, legal, legal, aqui estão os exemplos, se você obter realmente o uso da DMV sys.dm_server_registry em comparação com a DMV sys.dm_tcp_listener_states pode ser considerado bem mais complexo e confuso, pois torna-se necessário conhecer um pouco da estrutura de chaves de registro do Windows, bem como, o que representa a sequência de valores apresentados na coluna Registry_Key o que para muitos profissionais não é algo são comum de ser entendido.
Nenhum comentário:
Postar um comentário