Асинхронные темы в SQL с Service Broker.

Когда нет времени ждать выполнения задачи в дело вступает асинхронное программирование.

Представим что нужно выполнить 5 задач , если мы запустим их в SQL они будут выполняться последовательно, после запуска задачи процесс будет ждать завершения

и только после этого начнется следующая задача.

С помощью компонента Service Broker можно выносить выполнение задач во внешние приложения используя обмен сообщениями и управлением очередями сообщений.

Приложение может быть на другой машине таким образом настраивается масштабируемость  без изменения работы логики основного приложения.

Если внешняя служба не доступна Service Broker будет помещать сообщения в очередь на отправку и возобновит работу когда  служба будет  доступна, без ущерба работы основного приложения, таким образом Service Broker гарантирует доставку сообщений.

Когда лучше использовать Service Broker

• Асинхронные триггеры
• Надежная обработка запросов
• Надежный сбор данных
• Распределенная серверная обработка для клиентских приложений
• Консолидация данных для клиентских приложений
• Широкомасштабная пакетная обработка

1.Включение Service Broker

1.Проверить состояние компонента Service Broker

SELECT name, is_broker_enabled

FROM sys.databases

2.Включить компонент Service Broker

ALTER DATABASE SERVICE_BROKER SET ENABLE_BROKER with rollback immediate

--отключить компонент

alter database SERVICE_BROKER set disable_broker with rollback immediate

--задать  новый идентификатор Service Broker, удаляет все диалоги, необходимо заново пересоздать маршруты с новым идентификатором

alter database SERVICE_BROKER set new_broker

3. Включить безопасное взаимодействие

--Главный ключ базы данных — это симметричный ключ, который применяется для защиты закрытых ключей сертификатов

--и асимметричный ключей, которые есть в базе данных.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='dswfdsdgfdg3241df'

--посмотреть список ключей

select * from sys.symmetric_keys

Быстро загрузить XML из файла средствами SQL

Когда нужно быстро загрузить XML из внешнего файла и не хочется делать через SSIS,  очень кстати будет следующий скрипт.

Допустим есть внешний файл следующей структуры.

1.  Загружаем  данные из файла   через OPENROWSET и BULK

2.  Подготавливаем документ к обработке sp_xml_preparedocument

3.  Разбираем файл инструкцией OPENXML

3.  Освобождаем память, удаляем документ sp_xml_removedocument

DECLARE @doc AS XML

SELECT @doc = P

FROM OPENROWSET (BULK 'C:\SHARED\SAMPLEXML.XML',SINGLE_BLOB) AS TEST(P)

DECLARE @hdoc int;

--Процедура sp_xml_preparedocument возвращает дескриптор, по которому можно обратиться к вновь созданному внутреннему представлению XML-документа.

--Считывает входной XML-текст, проводит его синтаксический анализ при помощи средства синтаксического анализатора MSXML (Msxmlsql.dll) и выдает проанализированный документ, готовый к потреблению.

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>';

select @doc

--OPENXML Параметры:

--idoc -Дескриптор документа внутреннего представления XML-документа.

--rowpattern -  Шаблон XPath, используемый для идентификации узлов

--flags  -Указывает на сопоставление, которое должно использоваться между XML-данными и реляционным набором строк

-- 0 по атрибутам; 1 по атрибутам затем по элементам; 2 по элементам затем по атрибутам;

SELECT *

FROM OPENXML(@hdoc,'/SampleXML/row',2)

WITH

    (TITLE NVARCHAR(100) '../@TITLE', --.. путь к вышестоящему элементу ../../ два элемента наверх

       GROUPNAME INT '@GROUP', -- сопоставление с атрибутом задается @

       ID INT,

       NAME NVARCHAR(100)

       )

--Удаляет встроенное представление XML-документа, заданного дескриптором документа и делает недействительным дескриптор документа.

EXEC sp_xml_removedocument @hdoc

Изучаем свою сеть с NMAP.

Есть одна очень полезная утилита о которой знает любой администратор. Скачать можно по ссылке.

Это Nmap —аббревиатура от «Network Mapper», дословно переводится  как «сетевой картограф»

Она позволяет детально изучить вашу сеть, проверить открытые порты , запущенные сервисы  и узнать много чего интересного.

Общий синтаксис:

Полное описание команд тут 
Цель сканирования, может быть один хост так и диапазон адресов.

• x-y  по указанной опции nmap 192.168.0-1.1-2 просканирует адреса 192.168.0.1, 192.168.1.1, 192.168.0.2, и 192.168.1.2
• * - то же самое что и 0-255.
• x,y – несколько хостов указываются через запятую. nmap 192.168.0.1,2,4 просканирует 192.168.0.1, 192.168.0.2, и 192.168.0.4.
• /n –позволяет сканировать подсеть. nmap 192.168.0.0/16 выполнит тоже сканирование что и  nmap 192.168.0-255.0-255.

#1: Обнаружение хостов в сети. Сканирование.  

Опции сканирования

-sL (Сканирование с целью составления списка)

-sP (Пинг сканирование)

-PN (Не использовать пинг сканирование)

-PS <список_портов> (TCP SYN пингование)

-PA <список_портов> (TCP ACK пингование)

-PU <список_портов> (UDP пингование)

-PO <список_протоколов> (пингование с использованием IP протокола)

-PR (ARP пингование)

Самый простой способ обнаружить хосты пинг сканирование (ICMP пакеты)

nmap -sP 192.168.0.1-255

Если в сети фаерволл блокирует  ICMP пакеты, в этом случае лучше сразу использовать TCP ACK и TCP Syn  пинг методы для обнаружения удаленных хостов.

nmap -PS 192.168.1.1-255

nmap -PA 192.168.1.1-255

#2: Найти обще используемые TCP порты используя TCP SYN Scan

MDX и T-SQL в одном запросе

Что делать когда нужно объединить в  одном запросе многомерные данные и реляционные ?

1. Чтобы воспользоваться этой возможностью, для начала необходимо создать связанный OLAP сервер.

EXEC master.dbo.sp_addlinkedserver

--имя

@server = N'FinanceOlapServer',

@srvproduct=N'',

--провайдер

@provider=N'MSOLAP',

--сервер

@datasrc=N'localhost',

--многомерная база

@catalog=N'FinanceCube_01'

Теперь воспользуемся функцией OPENQUERY которая выполняет передаваемый запрос к указанному связанному серверу.

Если возникает ошибка The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server.

Делаем по шагам.

1. сначала убираем регистрацию 32 и 64 разрядной DLL

regsvr32 /u "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

2.Затем заново регистрируем, сначала 32 битную версию

regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

3. Перезапускаем службу  SQL

2. Использование OPENROWSET и OPENDATASOURCE

(это альтернативный метод для доступа к таблицам на связанном сервере)

По умолчанию в SQL сервер распределенные запросы разрешены через связанный сервер,

если необходимо делать нерегламентированные распределенные запросы, без создания связанного сервера

необходимо активировать соответствующую опцию .

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO

Полезные запросы SQL

Несколько полезных запросов которые должны быть под рукой у программиста SQL!

1.Склеить  в одну строку данные в столбце

WITH T as

    (SELECT 1 as ID , 'TEST1' as NAME

             UNION ALL 

             SELECT 1 as ID , 'TEST2' as NAME

             UNION ALL 

             SELECT 1 as ID , 'TEST3' as NAME

             UNION ALL 

             SELECT 2 as ID , 'XXX' as NAME

             UNION ALL 

             SELECT 2 as ID , 'YYY' as NAME

             )

      

SELECT DISTINCT T1.ID,

(SELECT STUFF((SELECT ', ' + T2.NAME

    FROM T T2

       WHERE T1.ID  = T2.ID

    FOR XML PATH('')) ,1,1,'')) NAME

FROM T T1

С сортировкой

 with T as
(
select 1 as ID_Сотрудник, N'Здание 1'  as Строение, N'101'  as Номер_комнаты
UNION ALL
select 1 as ID_Сотрудник, N'Здание 2'  as Строение, N'102'  as Номер_комнаты
UNION ALL
select 2 as ID_Сотрудник, N'Здание 2'  as Строение, N'201'  as Номер_комнаты
UNION ALL
select 2 as ID_Сотрудник, N'Здание 1'  as Строение, N'202'  as Номер_комнаты
),

TP as
(
select distinct ID_Сотрудник , Строение from T
)

SELECT DISTINCT T1.ID_Сотрудник,
SUBSTRING(
(
    SELECT ',' + T2.Строение AS 'data()'
        FROM  TP T2
WHERE T1.ID_Сотрудник = T2.ID_Сотрудник
ORDER BY T2.Строение
        FOR XML PATH('')
), 2 , 9999)  Строение,
SUBSTRING(
(
    SELECT ',' + T2.Номер_комнаты AS 'data()'
        FROM  T T2
WHERE T1.ID_Сотрудник = T2.ID_Сотрудник
ORDER BY T2.Строение
        FOR XML PATH('')
), 2 , 9999)  Номер_комнаты
FROM T T1

Начиная с SQL Server 2017 доступна функция STRING_AGG

SELECT STRING_AGG( ISNULL(Строение, ' '), ',') As Строение

From Test

2.Динамический запрос с параметрами

DECLARE @SQLString nvarchar(4000);

DECLARE @ParmDefinition nvarchar(500);

DECLARE @name nvarchar(500);

DECLARE @max_VALUEOUT varchar(30);

SET @name = N'TEST';

SET @SQLString = N'SELECT @max_VALUEOUT = max(ID)

   FROM   (SELECT 1 as ID , ''TEST1'' as NAME UNION ALL  SELECT 8 as ID , ''TEST2'' as NAME   UNION ALL         SELECT 5 as ID , ''TEST3'' as NAME UNION ALL  SELECT 2 as ID , ''XXX'' as NAME) T

   WHERE NAME  like (''%''+@name +''%'')'

SET @ParmDefinition = N'@name varchar(100), @max_VALUEOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @name = @name, @max_VALUEOUT=@max_VALUEOUT OUTPUT;

SELECT @max_VALUEOUT as MAX_VALUE;