|
|
-
Задают вопросы, что с моим блогом, почему я перестал в него писать.
1. На пост типа http://blogs.msdn.com/alexejs/archive/2009/08/15/9871237.aspx или http://blogs.msdn.com/alexejs/archive/2009/08/22/9880919.aspx или и т.д. уходит до черта времени. На пост типа "Вышла новая версия 100.0, ура!" времени уходит меньше. Написать таких постов в единицу времени можно больше. Просматриваются они лучше. Спрашивается, на фига рвать попу, если в нынешних условиях выгодней иметь 10 рацпредложений по копейке, чем одно на рубль?
2. Черт с ней, с выгодой, главное, чтобы труд приносил пользу людям. После http://blogs.msdn.com/alexejs/archive/2009/08/31/dri-parent-child.aspx я убедился, что всем все по барабану. В своем блоге я делюсь знаниями, тут единственный раз обратился с просьбой. Тем более, просил не для себя. Мне кажется, сделать SQL Server лучше - в наших общих интересах. Просьба, по сути, была проигнорирована. Понятно, проще ныть, что у M$ в SQL Server это не работает, чем шевельнуть пальцем, чтобы это поправить. Не хочу давить на совесть, но такое отношение мне непонятно.
По этим причинам у меня нет больше желания сюда ничего писать.
|
-
Для полноты картины стоит коснуться потребителя ETW-событий. Потребитель - это приложение, которое читает логи, созданные в ходе ETW-сессии, или цепляется к ней напрямую и потребляет генерируемые ей события в реальном времени. Консьюмить залоггированую трассу неинтересно. Не буду отвлекать на увлекательный рассказ про парсинг csv или xml или еще какого-нибудь формата. Какие-то потуги в этом направлении предпринимались во второй серии, Скрипты 1-3. Примером потребителя реального времени явлется всем хорошо известный Windows Event Viewer. Подобных приложений можно написать сколько угодно, каждое для своей области. Скажем, Performance Profiling Tools for WPF. 
Рис.1 Real-time провайдером может выступать утилита tracerpt, которую мы использовали для преобразования etl в читабельные форматы (см. первая серия, Рис.8, 9). Стартуем сессию сбора событий из SQL Trace (вторая серия, Скрипт 7), направив вывод не в etl-файл, а в real-time (ключ -rt): logman start SqlDataCollector -ets -rt -p "MSSQLServer Trace" 0 0
Рис.2 Видим среди запущенных ETW-сессий сессию по имени SqlDataCollector, которая стримит свои рез-ты в real-time. Tracerpt может подключиться к этой сессии, улавливать ее выходной стрим и направить его непосредственно в какой-либо читабельный формат: tracerpt -rt SqlDataCollector -o c:\Temp\SqlDataCollector.xml -of XML 
Рис.3 Образуется файл c:\Temp\SqlDataCollector.xml, который будет прирастать по мере поступления событий. Все время сбора он будет занят, открыть и прочесть его удастся по остановке (logman stop) сессии SqlDataCollector. Написание собственного потребителя реального времени находится не слишком в струе светлых идей популяризации и продвижения .NET. Если провайдера ETW худо-бедно можно изобразить .NET-средствами, как мы видели в предыдущем посте, до потребителя managed world еще, увы, не добрался. Например, вот примерный пример примеривания потребителя применительно к событиям IIS. Разумеется, со временем нашлись добрые люди, написавшие .NET-обертку вокруг native API и Сшных вызовов, чтобы потреблять события ETWшной сессии из VB.NET и С#. Образцы народного творчества можно найти в блогах и на кодплексе. Словом, интеграция ETW c .NET здесь на том же уровне, что и при создании провайдера на основе манифеста. Наверное, в .NET когда-нибудь появится нормальная поддержка ETW, поскольку это актуальная перспективная технология integrated throughout Windows, offering higher performance, flexibility, zero-config tracing and a lot of other advantages.
|
-
В конце первой серии говорилось, что, начиная с Висты, в ETW поддерживается мультиплексирование. Это очень позитивное нововведение, которым мы сейчас конкретно займемся. Еще в посте про SQL Profiler я говорил о ценности консолидированной картины мира, когда мы сводили воедино профайлерную трассу с графиками Performance Monitor. Продолжу развивать эту мысль. Ясно, что настройка и отладка сложного многокомпонентного приложения - процесс нетривиальный. Он становится еще нетривиальней, если каждая компонента пишет информацию о ходе своей работы в собственный журнал, и эти журналы затем приходится состыковывать, чтобы понять последовательность действий в общем масштабе приложения: чем занималась компонента А, вызвавшая компоненту Б, и компонента Б, вызвавшая компоненту В, прежде чем в компоненте В вылезла какая-нибудь бякость. В особенности если учесть, что А, Б, В могли быть разработаны независимо. Йа нопешу афегительно сложное преложенее, которое обращается к SQL Server, и сведу события от них обоих в единую трассу. В сценарии будут участвовать два ETW-провайдера: штатный SQL Serverный MSSQLServer Trace, который мы посмотрели во второй серии, и самопальный, задачей которого является трассировка кастомного клиентского приложения. Благодаря возможности мультиплексирования они будут шарить одну ETWшную сессию. Для начала стоит изобразить кастомный провайдер.
Программирование контроллера, провайдера и потребителя описывается в документации MSDN. Как говорилось в одном посте, those whose curiosity has been piqued can jump into the MSDN documentation, which is not very good IMO but better than nothing.
Там описываются примеры с использованием native API. Как говорилось в другом посте, The ETW API has been around for a number of years, so it’s implemented in unmanaged code. As I write this, there are no shipping managed libraries to make working with it simpler. Internally, various groups here are looking at the best way to make ETW a first class citizen in the managed world. Это писалось 5 лет назад, и сейчас в графе .NET-поддержка можно ставить галочку. Не слишком жирную, though. Написание своего провайдера обрисовано в посте .NET Event Tracing for Windows от апреля нынешнего года. В нем предлагается следующая последовательность действий. Во-первых, надлежит создать манифест провайдера в виде XML-документа. Апологеты инволюции к иерархическим БД под названием XML в качестве одного из преимуществ приводят его наглядность по сравнению с проприетарными бинарными форматами и самоописательность. Прекрасно, значит, поглядев примеры в %MSSDK%\Samples\WinBase\Eventing, свой манифест они смогут составить самостоятельно. Для всех остальных в %MSSDK%\bin существует тула ecmangen.exe, способная генерить этот манифест. Не зная, что допускается в узлах по правилам игры, от наглядности XML мало прока. С таким же успехом генератор, в который это знание заложено, мог бы производить манифест в бинарном формате. Без разницы, как воплощать это знание: в виде элементов и атрибутов или побайтно отсчитывая с такой-то позиции по такую-то. Хотя нет, XML занимает больше места.
Во-вторых, на основе манифеста при помощи тулы mc.exe (Message Compiler, также входит в SDK) приготовляется 4 файла (временный, .bin, .h, .rc), из которых существенным в данном случае является ресурсный. В-третьих, берется rc.exe (Resource Compiler, также должен лежать в C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin), при помощи которого из .rc файла делается ресурс .res. Ресурс затем вставляется в Project Properties вижуал студийного проекта, что образует класс с именем манифеста, используя который в коде, можно инициализировать и отправлять события. Все просто, если не считать, что .NET здесь несколько притянут за уши. Искреннее отношение человека к данному процессу можно почитать здесь.
Сергей Мелещук в своем блоге (How to do ETW logging from .NET application) приводит другой способ генерации событий из .NET. Он не рассматривает задачи создать произвольный собственный ETW-провайдер с нуля при помощи манифеста и класса System.Diagnostics.Eventing.EventProvider. В System.Diagnostics имеется класс TraceSource, который позволяет писать из приложения сообщения трассировки в разные места. Место определяется абстрактным классом TraceListener, который в .NET 2.0 воплощался в ConsoleTraceListener - вывод отладочных сообщений в консоль, TextWriterTraceListener - в текстовый файл, XmlWriterTraceListener - в XML, EventLogTraceListener - в журнал Windows. В 3.5 к ним добавился EventProviderTraceListener, направляющий отладочные сообщения в ETW. Добавление такого слушателя автоматически создает новый ETW-провайдер, хотя и не позволяет такой полноты контроля над его обликом, как в первом случае. Я взял за основу пример Сергея и немного его модифицировал применительно к нашему сценарию.
Удалим для чистоты эксперимента созданный в первой серии SqlDataCollector:
Рис.1
Создадим каким-либо способом новый гуид. В моем случае это получился {4E7F098A-3E72-4D5B-9EA3-1B41A83527B0}.
Рис.2
Создадим текстовый файл c:\Temp\SqlDataCollector.cfg, куда напишем следующие две строчки:
"MSSQLServer Trace" 6 0
{4E7F098A-3E72-4D5B-9EA3-1B41A83527B0}
Рис.3
где вторая есть свежесозданный гуид.
Откроем окно командной строки и вобьем следующую команду. Сейчас поясню, что в ней к чему.
logman create trace AppDataCollector -o c:\Temp\SqlDataCollector.etl -pf c:\Temp\SqlDataCollector.cfg -ow --v
Скрипт 1
Logman create trace создает новый коллектор данных на основе одного или нескольких провайдеров данных. По условию задачи у нас должно быть два провайдера: MSSQLServer Trace и самопальный, пишущих в один файл. Если создавать два коллектора
logman create trace Коллектор1 -o c:\Temp\SqlDataCollector.etl -р Провайдер1
logman start Коллектор1
logman create trace Коллектор2 -o c:\Temp\SqlDataCollector.etl -р Провайдер2
logman start Коллектор2
при старте второго возникнет ошибка, что файл занят, следовательно, нужно их сводить в один коллектор. Ключ -р, как можно посмотреть в logman create trace /?, позволяет задать только одного провайдера - A single Event Trace provider to enable. В случае нескольких их нужно прописывать в отдельном файле, который передается в ключе -pf. В нашем случае это c:\Temp\SqlDataCollector.cfg (Рис.3). Первая строчка инициализирует провайдер MSSQLServer Trace. Мы ее уже видели во второй серии (Скрипт 7). Вторая соответствует самопальному провайдеру. С ним дело обстоит чуть хитрее. В примере Сергея используется класс EventProviderTraceListener. Как говорится в документации, конструктор EventProviderTraceListener инициализирует новый экземпляр класса с помощью указанного идентификатора поставщика. Где ж его взять, коли поставщик еще не создан? Команда logman create trace ... -р Провайдер будет ругаться, если ей указать имя несуществующего провайдера. К счастью, если вместо имени подсунуть гуид, она поверит взаймы, что такой есть. Это может быть совершенно произвольный гуид. На рис.3 он передается во второй строчке. Ключ -ow говорит перезаписывать файл c:\Temp\SqlDataCollector.etl, ежели таковой уже имеется. Ключ --v отменяет действие ключа -v, чтобы файл выхлопа был один. Иначе он будет создавать SqlDataCollector_000001.etl, SqlDataCollector_000002.etl и т.д. Пускаем сессию сбора данных для свежесозданного коллектора
logman start AppDataCollector
Скрипт 2
и выполняем следующее консольное приложение.
using System;
using System.Diagnostics;
using System.Diagnostics.Eventing;
using System.Data.SqlClient;
class Program
{
private static Guid etwProviderId = new Guid("{4E7F098A-3E72-4D5B-9EA3-1B41A83527B0}");
private static string etwProviderName = "AppDataCollector";
static void Main(string[] args)
{
EventProviderTraceListener etwListener = new EventProviderTraceListener(etwProviderId.ToString(), etwProviderName);
TraceSource ts = new TraceSource(etwProviderName, SourceLevels.All);
ts.Listeners.Clear();
ts.Listeners.Add(etwListener);
ts.TraceEvent(TraceEventType.Information | TraceEventType.Start, 0, "Поехали");
ts.TraceData(TraceEventType.Verbose, 0, String.Format("Здравствуйте. Я - приложение {0}", System.Reflection.Assembly.GetEntryAssembly().Location));
SqlConnection cnn = new SqlConnection("server=localhost;database=TestFS;trusted_connection=true"); cnn.Open();
SqlCommand cmd = new SqlCommand("exec sp_trace_generateevent @eventid = 82, @userinfo = N'Пользовательское событие №0', @userdata = 0x12345", cnn);
ts.TraceData(TraceEventType.Verbose, 0, String.Format("Отправляю на SQL Server команду {0}", cmd.CommandText));
cmd.ExecuteNonQuery();
ts.TraceInformation("И смотрю, что из этого получилось");
cnn.Close(); ts.Close();
}
}
Скрипт 3
Это одновременно будет обращающийся к SQL Server клиент и сам себе режиссер, то есть ETW-провайдер. Все свои действия он протоколирует, посылая их в TraceSource. К любому TraceSource по умолчанию подстегивается консольный слушатель, нам он неинтересен, отсюда ts.Listeners.Clear(). Вместо него добавляется EventProviderTraceListener, отсылающий трассировку ETW-провайдеру, которого он сам же и создает. Единственно, мы говорим, чтобы он создал его с определенным гуидом, тем самым, который нам поверил в долг логман. Обращаю внимание, что это может быть совершенно произвольный гуид, потому что некоторые упорно выбирают {E13C0D23-CCBC-4E12-931B-D9CC2EEE27E4} (провайдер .NET Common Language Runtime). Не буду отвлекаться на рассказ про методы TraceEvent, TraceData и TraceInformation. Можно посмотреть в документации или в совершенно замечательном .NET Instrumentation Workshop. TraceSource отправляет информацию с этих методов привязанным к нему слушателям, то есть в данном случае ETW-провайдеру с гуидом {4E7F098A-3E72-4D5B-9EA3-1B41A83527B0}. Затем на SQL Server отправляется запрос. Это Скрипт 8 из предыдущей серии. Как и в прошлый раз, SQL Server на него реагирует, отправляя событие своему провайдеру MSSQLServer Trace. Оба провайдера: и MSSQLServer Trace, и самопальный {4E7F098A-3E72-4D5B-9EA3-1B41A83527B0}, - завязаны в данном случае на одну сессию:
Рис.4
которая кидает сообщения в c:\Temp\SqlDataCollector.etl (см. Скрипт 1). Туда же после реакции SQL Server падает заключительное TraceInformation от приложения. В итоге имеем объединенный трейс-лог, что и требовалось продемонстрировать. Останавливаем сессию, конвертируем etl и смотрим:
logman stop AppDataCollector
tracerpt C:\Temp\SqlDataCollector.etl -lr -o C:\Temp\SqlDataCollector.xml
Скрипт 4
Рис.5
Рис.6
|
-
В предыдущем посте мы разбирали структуру ETW-события и выяснили, что даже в более или менее стандартном по структуре заголовке имеется лишь несколько полей, заполняющихся автоматически, и несколько наборов предопределенных значений для некоторых других полей. Все остальное отдано на откуп провайдерам, в результате чего творится полный бардак. Четких правил, регламентирующих категоризацию событий, в природе нет, и каждый провайдер изгаляется, кто во что горазд. Один может определить одно родовое событие, которое он дальше дробит на таски, опкоды и прочие ключевые слова, повышая уровень детализации события. Другой может не заморачиваться, а просто заводить на каждую деталь по событию. Также возможна масса промежуточных стадий помешательства. Глядя на " SQL Server и ETW. Ч.1"\Рис.9, можно видеть, что ETW-провайдер MSSQLSERVER Trace является сторонником капитального сноса крыши. Загрузим представленный XML в SQL Server:
declare @x xml = (select BulkColumn from openrowset(bulk 'c:\Temp\SqlDataCollector.xml', SINGLE_CLOB) t)
;with xmlnamespaces('http://schemas.microsoft.com/win/2004/08/events/event' as e, 'http://schemas.microsoft.com/win/2004/08/events/trace' as t)
select
x.value('(e:System/e:Provider/@Guid)[1]', 'uniqueidentifier') as ProviderGuid,
x.value('(e:RenderingInfo/t:EventName)[1]', 'nvarchar(255)') as EventName,
x.value('(t:ExtendedTracingInfo/t:EventGuid)[1]', 'uniqueidentifier') as EventGuid
from @x.nodes('Events/e:Event') t(x)
Скрипт 1
Рис.1
чтобы выяснить, а) может ли одному профайлерному событию соответствовать несколько ETWшных и б) чем ProviderGuid отличается от EventGuid'a? Кстати, заодно пример совмещения with xmlnamespaces() и with cte (), которое иногда вызывает затруднения.
...
;with xmlnamespaces('http://schemas.microsoft.com/win/2004/08/events/event' as e, 'http://schemas.microsoft.com/win/2004/08/events/trace' as t),
cte as (
select
x.value('(e:System/e:Provider/@Guid)[1]', 'uniqueidentifier') as ProviderGuid,
x.value('(e:RenderingInfo/t:EventName)[1]', 'nvarchar(255)') as EventName,
x.value('(t:ExtendedTracingInfo/t:EventGuid)[1]', 'uniqueidentifier') as EventGuid
from @x.nodes('Events/e:Event') t(x)
)
select count(distinct ProviderGuid), count(distinct EventGuid), eventname from cte group by eventname
-------------------------
1 1 Audit Login
1 1 Audit Logout
1 1 EventTrace
1 1 RPC:Completed
1 1 SQL:BatchCompleted
Скрипт 2
Видим, что а) по-видимому, не может.
...
select * from cte where ProviderGuid <> EventGuid
----------------
ProviderGuid EventName EventGuid
9E814AAD-3204-11D2-9A82-006008A86939 EventTrace 68FDD900-4A3E-11D1-84F4-0000F80464E3
Скрипт 3
и б) практически, ничем.
Всякий раз специфика области, для которой писан провайдер, и его изрядная степень свободы приводят к тому, что автор провайдера должен добротно документировать изрекаемые им сообщения, иначе в них будет проблематично разобраться. Пример хорошо документированных провайдеров и форматов их сообщений можно, например, наблюдать здесь - .NET Framework 4 CLR ETW Events. В случае MSSQLSERVER Trace благодаря паре несложных запросов и знакомству с SQL Trace структура сообщения приобретает более-менее ясность. Все равно, глядя на Рис.9 в предыдущей серии, задаешься вопросом: какие именно профайлерные события собирает провайдер MSSQLSERVER Trace, неужто все? Какие колонки по каждому событию, тоже все автоматом? Можно ли ставить фильтры на события в зависимости от значения той или иной колонки, как в профайлере? В Books On-Line искать на них ответ, по обыкновению, бестолку. Лучше обратиться к блогу "Tips, Tricks, and Advice from the SQL Server Query Processing Team".
События, информацию по которым MSSQLSERVER Trace засылает в ETW, перечислены в файле %ProgramFiles%\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\etwcnf.xml в шаблонах:
Рис.2
Здесь <Event id=...> - это идентификаторы профайлерных событий. Понять, что какой значит, можно, обратившись к посту "Программно сгенерить трассу профайлера. Ч. 1, 2"\Скрипт 3.
По умолчанию она берет список событий из template c id = 0. Вот эти события:
select * from sys.trace_events where trace_event_id in (10, 12, 14, 15, 17)
----------------------------------
trace_event_id category_id name
10 11 RPC:Completed
12 13 SQL:BatchCompleted
14 8 Audit Login
15 8 Audit Logout
17 10 ExistingConnection
Скрипт 4
Что совпадает со списком событий Скрипт 2, когда трасса была запущена из Reliability and Performance Monitor.
Какие колонки брать по каждому ивенту, оговаривается в файле %ProgramFiles%\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\etwcls.mof:
Рис.3
Как в MSSQLSERVER Trace фильтровать события по типу "Программно сгенерить трассу профайлера. Ч. 1, 2"\(Рис.4 или Скрипт 4), я не нашел. Зато я знаю, как ему подсунуть произвольный шаблон из Рис.2. Создадим в etwcnf.xml еще один шаблон. Этот шаблон будет соостоять из всего одного профайлерного события UserConfigurable:0. UserConfigurable-события хороши тем, что с ними не нужно ждать милостей от природы. Можно их вызывать по своему усмотрению.
...
<Template id="6" Name="New Template">
<!--82 = UserConfigurable:0 -->
<Event id="82" />
</Template>
</EventConfiguration>
Скрипт 5
Запускаем ETW-сессию вручную из-за того, что я не знаю, как ей подсунуть параметр -p (или -pf) в случае запуска из-под Reliability and Performance Monitor.
logman start SqlDataCollector -ets -o c:\Temp\SqlDataCollector.etl -p "MSSQLServer Trace" 6 0
Скрипт 7
Параметры логмана можно посмотреть на технете. -ets : создает и стартует сессию event trace session с опциями в командной строке. -о : выходной файл. Несмотря на то, что у коллектора по имени SqlDataCollector, созданного в Reliability and Performance Monitor, выходной файл был прописан - см. предыдущий пост\Рис.7, при ручном запуске она на него не обращает внимания и создает в той директории, откуда был запущен логман, если не указать явно. -p { GUID | provider [( flags [ ,flags ... ])] Level ] | -pf { FileName } : Specifies the providers (trace data collectors) to use for trace data collection... Use the -pf option to list multiple providers. The -pf option identifies the input file containing the provider names. The provider names are enclosed by quotation marks (""), or with GUIDs enclosed by braces, flag masks, and integers (enable level). The flags are either in hexadecimal (OXFFFF) or (flag, flag) format. "MSSQLServer Trace" - это название ETW-провайдера. 6 - id только что созданного темплейта в etwcnf.xml. 0 - запустить.
Если выглянуть из командной строки в Reliability and Performance Monitor, можно заметить, что SqlDataCollector в списке сессий (предыдущий пост\Рис.6) перешел из состояния Stopped в состояние Running.
Чтобы не открывать SSMS, оттуда же из командной строки инициируем событие 82, чтобы MSSQLSERVER Trace его поймал и заслал в ETW:
sqlcmd -S (local) -E -Q "exec sp_trace_generateevent @eventid = 82, @userinfo = N'Пользовательское событие №0', @userdata = 0x12345"
Скрипт 8
Стопим сессию:
logman stop SqlDataCollector -ets
Скрипт 9
Превращаем c:\Temp\SqlDataCollector.etl в читабельный формат - см. предыдущий пост\Рис.9.
Рис.4
Рис.5
Мы видим, что, помимо традиционной шапки в виде первого события EventTrace, теперь файл содержит только одно событие, и оно соответствует SQL Trace'ному событию №82. Это действительно результат Скрипта 8, в чем можно убедиться по элементам <EventData>\<TextData> и <BinaryData>.
В следующей серии мы напишем своего ETW-провайдера, которого попытаемся поженить с SQL Serverным. Stay tuned.
|
-
Вначале небольшая вводная на тему ETW, чтобы не получилось, как здесь - http://bytes.com/topic/net/answers/405066-what-windows-event-trace-windows-event-tracing.
Классической статьей по ETW служит http://msdn.microsoft.com/ru-ru/magazine/cc163437.aspx.
Event Tracing for Windows (ETW) является базовым унифицированным механизмом трассировки событий в Windows. По сути, ETW - это однонаправленный пакетный ввод/вывод, предназначенный для журналирования. Под журналирование на уровне каждого процессора отводятся буфера, которые скидываются на диск в асинхронном режиме специальным потоком. Эти операции выполняются в режиме ядра, и оттого занимают копейки в сравнении с, допустим, fprintf(). Даже на древних пентиумах ETW с темпом 20 тыс. событий в секунду сажала процессор не более, чем на 5%. Механизм реализован на уровне ядра; использовать его, т.е. производить события и потреблять их могут как драйверы ядра, так и пользовательские приложения. Данные о событии могут иметь какую угодно структуру на усмотрение приложения, его производящего, т.е. это просто блок бинарщины. Однако к нему прикрепляется заголовок со служебной и идентифицирующей информацией, которую мы посмотрим ниже на примере Рис.9.
Провайдер событий - это dll или ехе, которая их производит, - потребитель, соответственно, тот, кто потребляет. Список зарегистрированных провайдеров можно посмотреть при помощи утилиты командной строки logman:
logman -query providers
Рис.1
Либо можно пойти в Administrative Tools \ Reliability and Performance Monitor (или вызвать в команде Run perfmon.msc) и создать новый пользовательский Data Collector Set
 
Рис.2
который будет собирать данные из ETW
Рис.3
следующим шагом будет предложено выбрать ETW-провайдера из списка доступных:
 
Рис.4
Справа высветился тот же список, что и на Рис.1. В списке провайдеров к SQL Server имеют отношение MSSQLSERVER Trace (в случае именованного экземпляра вместо MSSQLSERVER будет стоять имя инстанса - см. пост "Именованый экземпляр по умолчанию"), RsFx0102 Kernel Log (драйвер FileStream), XEvent Package 0 (расширенные события) и др. В данном посте нас будет интересовать первый товарищ. Он просто тупо шлет в ETW все, что происходит в SQL Trace.
Помимо провайдера и потребителя, есть еще контроллер, который управляет этим процессом. Контроллер обращается к ядру с просьбой начать трейс-сессию, создается хэндл (через callback в пользовательском режиме или прерывание в ядерном), контроллер идет к провайдеру и вручает ему этот хэндл. Это называется, он его заенейблил. Чтобы посмотреть активные трейс-сессии, можно написать собственное приложение, а можно это сделать из Reliability and Performance Monitor, выбрав слева Data Collector Sets \ Event Trace Sessions. Можно оттуда же запустить новую сессию. Кликнем правой кнопкой в Event Trace Sessions и выберем New -> Data Collector Set. Откроется диалог создания группы сборщиков податей Рис.2-4. Скажем ей, чтобы результаты своей жизнедеятельности она сохраняла в c:\Temp и чтобы стартовала без проволочек:
 
Рис.5
У нас появилась новая сессия:
Рис.6
Сразу по открытии сессии провайдер начинает слать события в этот хэндл, а ядро их принимает и рассовывает по буферам. Если буфера заполнились или таймаут проэкспайрился, буфера флушатся в файл .etl, который читает потребитель. Директория для файла задавалась на Рис.5, а имя оговаривается в параметрах трейс-сессии. Там же указываются количество буферов и таймаут. Кликнем два раза по сессии SqlDataCollector:
 
Рис.7
В принципе, потребителю возможно напрямую цепануться к хэндлу и получать события в реальном времени.
Ну ладно, давайте смотреть, что она насобирала. Остановим сессию SqlDataCollector, выбрав Stop из ее контекстного меню или кликнув на черный квадрат Малевича на Рис.6. Когда контроллер заканчивает сессию, провайдер извещается и прекращает производить события. Файл C:\Temp\SqlDataCollector.etl имеет неудобоваримый формат для чтения. Лучше превратить его во что-нибудь осмысленное, например, csv:
tracerpt -l C:\Temp\SqlDataCollector.etl -of CSV -o C:\Temp\SqlDataCollector.csv
Рис.8
или что-нибудь еще:
tracerpt C:\Temp\SqlDataCollector.etl -lr -o C:\Temp\SqlDataCollector.xml
Рис.9
В элементе <System> находится стандартный заголовок сообщения, структура которого одинакова что для провайдера MSSQLSERVER Trace, что для какого-нибудь еще. Level характеризует строгость и может принимать предопределенные значения 1 - критическое, 2 - ошибка, 3 - предупреждение, 4 - информационное и 5 - подробное. Также ETW-провайдер может определить свои собственные значения до 255. Task и Opcode - категоризация на уровне приложение/метод. Имена туда писать не получится, т.к. под Task выделено два байта, под Opcode - 1. Придется кодировать. Кодировка является усмотрением ETW-провайдера. Предопределенными считаются опкоды 1 - старт и 2 - стоп. Keywords позволяет дальше категоризировать события. Это 56 бит, что может, например, соответствовать маске из 56 категорий событий. В маске взводятся те биты, в какие соответствующие категории попадает событие. Опять-таки категоризация является прерогативой конкретного провайдера. TimeCreated - временная отметка момента происхождения события. Channel используется, в основном, если трасса предназначается для просмотра в Event Viewer; это Application, System и пр., что виднеется в дереве слева под Event Viewer. В элементе <EventData>/<Data> передается полезная нагрузка. Ее структура варьируется в зависимости от провайдера и типа сообщения. В данном случае в ней передается информация о событии SQL Trace. Каждый элемент <DataName> есть, по сути, колонка в профайлере. Само название профайлерного события присутствует в элементе <RenderingInfo>/<EventName>.
ETW существует в Windows, наверное, с версии 2000. Начиная с Висты, поддерживается мультиплексирование, т.е. отношение "многие-ко-многим" между провайдерами и сессиями. Например, этот вывод (Рис.9) можно замикшировать с результатами еще каких-нибудь ETW-провайдеров и получить согласованную по времени целостную картину мира в одном флаконе.
|
-
Уважаемые коллеги.
Активный участник нашего SQL Serverного сообщества Александр Григорьевич Бондарь зафайлил на коннекте пожелание исправить поведение декларативной ссылочной целостности в случае, когда таблица связана сама на себя.
Скрипт воспроизведения приводится на https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=470632&wa=wsignin1.0, он не бог весть, какой хитрый, тем не менее я прокомментирую, что называется, на пальцах.
Когда две таблицы связаны отношением первичный-внешний ключ и мы хотим, например, грохнуть какой-нибудь РК из родительской таблицы, возможны, как известно, три поведения: NO ACTION - не пущать, если в дочерней таблице есть записи, ссылающиеся на эту родительскую, CASCADE - удалить ссылающиеся дочерние записи из таблицы внешнего ключа (и далее по каскаду, если у нее, в свою очередь, есть дочерние таблицы), SET NULL/SET DEFAULT - поставить в дочерних записях поле внешнего ключа в NULL или значение по умолчанию, елико сие возможно. Аналогично для обновления.
Все хорошо за исключением случая, когда РК-FK отношение определено на одной таблице. Типичный пример - реализация иерархии, как, допустим, делается в измерении parent-child. Здесь она предлагает ставить только NO ACTION, т.к. боится зацикливания. Какое зацикливание? Что плохого, если, допустим, при удалении родительской записи ставить NULL у нее непосредственных детей, что будет означать, что они осиротели? Я считаю, нам надо поддержать Александра Григорьевича и проголосовать за то, чтобы эту фичу поправили. Никого не собираюсь с пеной у рта агитировать, но если вы считаете так же, просто обращаю внимание, что риквест имеет место быть и можно зайти по вышеприведенной ссылке и кликнуть на правую звездочку, чтобы поднять его рейтинг. Также можно кликнуть рядом на Validation: View or Add и отметить Yes, I am able to validate that this issue occurs, потому что действительно такое поведение наблюдается в том числе в 2008-м; скрипт там имеется, можете проверить.
Сделаем SQL Server лучше.
|
-
Коль скоро речь зашла о о скриптовании в SMO, стоит отметить еще одну возможность на эту тему под названием CaptureSQL.
Однажды я нарвался на ошибку при создании в SMO CLRной табличной функции. Это приблизительно функция Dir из поста "Как перелOжить файловую папку в базу"\Скрипт 6.
Рис.1
Обычно Inner Exception вносит ясность, информативно сообщая об истинных причинах недовольства. В данном случае UDF просит установить ей свойство TableVariableName. Я, помнится, еще удивился. Табличная переменная имеет смысл для T-SQLной табличной функции:
create function tvf() returns @t table (fld int) as begin insert @t values (1), (2), (3) return end
Здесь мы явно указываем, что функция будет CLRной: f.ImplementationType = Microsoft.SqlServer.Management.Smo.ImplementationType.SqlClr; а CLRной она, вроде как, без надобности. Ну ладно, раз просит, ей виднее. Недолго думая, забабахал ей строчку
f.TableVariableName = "@t";
Скрипт 1
На, подавись ты. Она и подавилась:
Рис.2
Причем, пребывая в состоянии подавленности, она перестала выдавать осмысленные сообщения об ошибках даже в Inner Exception. Наиболее разумные вещи - это An exception occurred while executing a Transact-SQL statement or batch и Incorrect syntax near the keyword 'EXTERNAL'. Хотелось бы понять, что именно в синтаксисе ей не по нутру.
Читатели, которые рискнули потратить время, пролистав пост "Программно сгенерить трассу профайлера. Ч. 1, 2", естественно, воспользуются для этой цели профайлером. Событие User Error Message выводит, по сути, закладку Messages в SSMS. Событие Exception показывает стандартную информацию о номере и строгости ошибки. Событие SQL:BatchStarting содержит вызвавший ее T-SQLный скрипт:
Рис.3
Однако существует увидеть образовавшийся в результате SQLный скрипт средствами SMO, не выходя за пределы Visual Studio. Для этого в код на Рис.2 достаточно добавить две строчки: в начале
srv.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;
Скрипт 2
которая означает, что все SQL-команды, которые SMO будет генерить в результате наших над ним измывательств, он не будет отправлять на сервер, а будет собирать их у себя в виде коллекции строк, которые затем можно посмотреть:
foreach (string s in srv.ConnectionContext.CapturedSql.Text) Debug.WriteLine(s);
Скрипт 3
Рис.4
В случае длинного SQL-скрипта его можно перенести в SSMS и там пройти построчно дебаггером. В данном случае очевидно, что ошибка синтаксиса происходит из-за пресловутой табличной переменной @t, которую все-таки не надо было указывать в случае CLRной функции. Стоит заменить в Скрипте 1 "@t" на пустую строку, все начинает прекрасно работать:
using System;
using System.Collections;
using System.Diagnostics;
using SMO = Microsoft.SqlServer.Management.Smo; //C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
//Также требует references на
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
using System.IO;
using System.Data;
class Program
{
static void Main(string[] args)
{
CreateFunctionDir();
}
public static void CreateFunctionDir()
{
SMO.Server srv = new Microsoft.SqlServer.Management.Smo.Server();
srv.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql;
SMO.Database db = srv.Databases["TestFS"];
SMO.SqlAssembly asm = db.Assemblies["MyAssembly"];
SMO.UserDefinedFunction f = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction();
f.Parent = db; f.Name = "Dir";
f.TextMode = false;
f.ImplementationType = Microsoft.SqlServer.Management.Smo.ImplementationType.SqlClr;
f.AssemblyName = asm.Name;
f.ClassName = "FileSystem";
f.MethodName = "Dir_InitMethod";
SMO.UserDefinedFunctionParameter p1 = new Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter(f, "@folder", SMO.DataType.NVarChar(266));
f.Parameters.Add(p1);
p1 = new Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter(f, "@shallowTraversal", SMO.DataType.Bit);
f.Parameters.Add(p1);
f.FunctionType = Microsoft.SqlServer.Management.Smo.UserDefinedFunctionType.Table;
SMO.Column c = new Microsoft.SqlServer.Management.Smo.Column(f, "ID", SMO.DataType.HierarchyId);
f.Columns.Add(c);
c = new Microsoft.SqlServer.Management.Smo.Column(f, "FullName", SMO.DataType.NVarChar(266));
f.Columns.Add(c);
c = new Microsoft.SqlServer.Management.Smo.Column(f, "DateCreated", SMO.DataType.DateTime2(3));
f.Columns.Add(c);
c = new Microsoft.SqlServer.Management.Smo.Column(f, "DateModified", SMO.DataType.DateTime2(3));
f.Columns.Add(c);
c = new Microsoft.SqlServer.Management.Smo.Column(f, "LastAccessed", SMO.DataType.DateTime2(3));
f.Columns.Add(c);
c = new Microsoft.SqlServer.Management.Smo.Column(f, "Properties", SMO.DataType.Xml(""));
f.Columns.Add(c);
c = new Microsoft.SqlServer.Management.Smo.Column(f, "Size", SMO.DataType.BigInt);
f.Columns.Add(c);
c = new Microsoft.SqlServer.Management.Smo.Column(f, "IsDir", SMO.DataType.Bit);
f.Columns.Add(c);
f.TableVariableName = "";
f.Create();
foreach (string s in srv.ConnectionContext.CapturedSql.Text) Debug.WriteLine(s);
}
}
Скрипт 4
Это была краткая иллюстрация возможности SMO по захвату SQL. Я пытался измыслить практические сценарии ее использования и не преуспел. Профайлер, как мы видели, позволяет делать все то же самое, плюс он обладает более широким спектром возможностей, т.к. не ограничен одной текущей сессией и T-SQLными событиями. Можно представить ситуацию, когда на предприятии нет выделенного DBA, и администратору приходится отвечать за все: за сервера баз данных, за почтовые сервера, за AD и т.д. Он использует PowerShell как универсальный рабочий инструмент автоматизации своих действий, из которого обращается к каждому из серверов в соответствии с его объектной моделью или WMI и иными административными интерфейсами. Например, SMO в случае SQL Server. Даже если предположить, что ему вдруг понадобилось превратить SMOшный скрипт в SQL, почему не использовать профайлер для перехвата действий, которые вызывает SMO на стороне сервера? Права ALTER TRACE у него по-любому должны иметься. Может быть, отталкиваться от обратной ситуации, когда разработчик не обладает сколь-либо серьезными административными привилегиями на SQL Server, и ему требуется проконтролировать, как в действительности отзовется код, написанный им с помощью SMO, на стороне сервера. С другой стороны, SMO, скорее, административный интерфейс, нежели доступ к данным общего назначения, так что разработчик будет, вероятно, использовать ADO.NET. Словом, если уважаемые читатели располагают убедительным примером, когда профайлер неприменим, а CaptureSQL выручает, давайте обсудим в комментариях.
В SMO имеется возможность скриптования, полезность которой очевидна. Она реализована в классе Microsoft.SqlServer.Management.Smo.Scripter, бегло рассмотренном в предыдущем посте. Речь идет не о захвате того, что происходит на лету, что позволяет делать и профайлер, а об автоматизации кнопки Generate SQL Script, т.е. генерации скриптов ранее созданных объектов. Совершенно замечательной возможностью скриптера является построение дерева зависимостей между объектами. Выполнение этой операции вручную довольно затруднительно.
Sys.sql_expression_dependencies (она же sql_dependencies, она же sysdepends, а также sp_depends, которая лазит по последней) не является исчерпывающим справочником отношений между объектами, т.к. отслеживает весьма ограниченное число типов зависимостей - см. BOL, http://msdn.microsoft.com/ru-ru/library/ms345449.aspx. Кстати, статья примечательна по двум причинам. Во-первых, творческий подход переводчика, переведшего вьюшку как просмотр. Действительно, традиционный перевод как представление уже прискучил. Единственно, ему нужно было определиться и остановиться на каком-нибудь одном варианте, а не терзаться творческими муками в пределах одной статьи. Во-вторых, DMV не отслеживает важных в данном примере зависимостей foreign key и принадлежностей CLRных модулей сборкам:
select object_name(referencing_id), referencing_class_desc, c1.name, referenced_entity_name, c2.name from sys.sql_expression_dependencies d
left join sys.columns c1 on d.referencing_id = c1.object_id and d.referencing_minor_id = c1.column_id
left join sys.columns c2 on d.referenced_id = c2.object_id and d.referenced_minor_id = c2.column_id
|
(No column name) |
referencing_class_desc |
name |
referenced_entity_name |
name |
|
TestFTS |
OBJECT_OR_COLUMN |
type |
GetFileExtension |
NULL |
|
TestFTS |
OBJECT_OR_COLUMN |
type |
TestFTS |
FullName |
Скрипт 5
Показывается зависимость колонки type в таблице TestFTS, которая имеет дефолтное значение dbo.GetFileExtension(FullName) от функции GetFileExtension и от колонки FullName. В то же время не показывается зависимость для таблицы ttt от таблицы TestFTS
create table ttt (id int, fk HierarchyID references TestFTS(id))
которая не даст просто так удалить таблицу TestFTS:
drop table TestFTS
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'TestFTS' because it is referenced by a FOREIGN KEY constraint.
Скрипт 6
Эти зависимости нужно искать в sys.foreign_keys:
select name, object_name(parent_object_id), object_name(referenced_object_id) from sys.foreign_keys
|
name |
(No column name) |
(No column name) |
|
FK__ttt__fk__43F60EC8 |
ttt |
TestFTS |
Скрипт 7
В sys.sql_expression_dependencies не отражается также зависимость функции GetFileExtension() от сборки MyAssembly. Ее нужно смотреть в sys.assembly_modules:
select a.name, o.name, o.type_desc, am.assembly_class, am.assembly_method from sys.assembly_modules am join sys.assemblies a on am.assembly_id = a.assembly_id join sys.objects o on am.object_id = o.object_id
|
name |
name |
type_desc |
assembly_class |
assembly_method |
|
MyAssembly |
Dir |
CLR_TABLE_VALUED_FUNCTION |
FileSystem |
Dir_InitMethod |
|
MyAssembly |
SplitPath |
CLR_TABLE_VALUED_FUNCTION |
FileSystem |
SplitPath_InitMethod |
|
MyAssembly |
LoadDir |
CLR_STORED_PROCEDURE |
FileSystem |
LoadDirWithFileContent |
|
MyAssembly |
GetFileExtension |
CLR_SCALAR_FUNCTION |
FileSystem |
GetFileExtension |
|
MyAssembly1 |
Main |
CLR_STORED_PROCEDURE |
Class1 |
Main |
Скрипт 8
Из-за того, что зависимости невозможно получить из единого справочника, а приходится собирать по разным местам, самостоятельное построение дерева зависимостей превращается в нетривиальную задачу. Далее, предположим, это как-то удастся сделать, но это будут голые object_id без скриптов. В зависимости от типа объекта придется сочинять drop table или drop proc или drop function и т.д. В T-SQL нет команды drop object <object_id> (а, наверное, зря). Аналогично, в SMO нету общей коллекции объектов базы, есть по отдельности db.Tables, db.StoredProcedures, db.UserDefinedFunctions и т.д. В зависимости от типа объекта придется ветвить, из какой коллекции мы хотим его дропнуть. К тому же сборки, хоть и создаются внутри базы данных, не имеют object_id и не входят в sys.objects. Object_id имеют процедуры и функции, созданные на основе ее модулей. С использованием возможности SMO CaptureSQL можно получить SQLный скрипт манипуляций над объектами SQL Server, когда последовательность действий над ними известна, то есть известны как сами объекты, так и зависимости между ними. Нижеприведенный скрипт выполняет передеплоймент сборки в явном виде: он удаляет таблицу TestFTS, зависимую от функции GetFileExtension, т.к. использует ее для дефолтного значения одного из своих полей, удаляет саму эту функцию, а также функцию Dir и процедуру LoadDir, удаляет сборку MyAssembly, откуда они берутся, а затем пересоздает их в обратной последовательности и получает SQLный скрипт всего этого безобразия. Я не стал приводить создание таблиц, поскольку пример на таблицы имеется в документации.
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
cls
$srv = new-object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList ""
$srv.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::ExecuteAndCaptureSql
$db = $srv.Databases["TestFS"]
#Удаление объектов
$o = $db.Tables["ttt", "dbo"][0]; if ($o -ne $null) { $o.Drop() } #при необходимости можно уточнить схему, но тогда возвращается не единичный объект, а коллекция
$o = $db.Tables["TestFTS"]; if ($o -ne $null) { $o.Drop() }
$o = $db.StoredProcedures["LoadDir"]; if ($o -ne $null) { $o.Drop() }
$o = $db.UserDefinedFunctions["Dir", "dbo"][0]; if ($o -ne $null) { $o.Drop() } #коллекция UserDefinedFunctions содержит все ф-ции - CLR, SQL, скалярные, табличные, ...
$o = $db.UserDefinedFunctions["GetFileExtension"]; if ($o -ne $null) { $o.Drop() }
$o = $db.UserDefinedFunctions["SplitPath"]; if ($o -ne $null) { $o.Drop() }
$o = $db.Assemblies["MyAssembly"]; if ($o -ne $null) { $o.Drop() }
#Создание сборки
$asm = new-object -TypeName "Microsoft.SqlServer.Management.Smo.SqlAssembly"
$asm.Name = "MyAssembly"
$asm.Parent = $db #иначе на след.строчке Exception setting "AssemblySecurityLevel": "You must set Parent property."
$asm.AssemblySecurityLevel = [Microsoft.SqlServer.Management.Smo.AssemblySecurityLevel]::Unrestricted #лежит в microsoft.sqlserver.sqlenum.dll
$asm.Create("C:\Temp\LoadFolderToSQL\bin\Debug\ClassLibrary1.dll")
#Currently, only one file per assembly is supported - http://msdn.microsoft.com/ru-ru/library/microsoft.sqlserver.management.smo.sqlassembly.sqlassemblyfiles.aspx
#Создание CLRной хранимой процедуры
$sp = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.StoredProcedure" -ArgumentList $db, "LoadDir" #аргументы - БД, название процедуры; можно еще указать схему
$sp.TextMode = $false
$sp.ImplementationType = [Microsoft.SqlServer.Management.Smo.ImplementationType]::SqlClr
$sp.AssemblyName = $asm.Name
$sp.ClassName = "FileSystem" #public partial class ... в dll
$sp.MethodName = "LoadDirWithFileContent" #метод, как он называется в библиотеке классов
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.StoredProcedureParameter" -ArgumentList $sp, "@folder", $([Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(255))
$sp.Parameters.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.StoredProcedureParameter" -ArgumentList $sp, "@shallowTraversal", $([Microsoft.SqlServer.Management.Smo.DataType]::Bit)
$sp.Parameters.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.StoredProcedureParameter" -ArgumentList $sp, "@tblName", $([Microsoft.SqlServer.Management.Smo.DataType]::SysName)
$sp.Parameters.Add($o)
$sp.Create()
#Создание CLRной скалярной функции
$f = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunction"
$f.Parent = $db; $f.Name = "GetFileExtension"
$f.TextMode = $false
$f.ImplementationType = [Microsoft.SqlServer.Management.Smo.ImplementationType]::SqlClr
$f.AssemblyName = $asm.Name
$f.ClassName = "FileSystem"
$f.MethodName = "GetFileExtension"
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter"
$o.Name = "@fileName"; $o.Parent = $f; #без этого не даст присвоить DataType параметру
$o.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(266)
$f.Parameters.Add($o)
$f.FunctionType = [Microsoft.SqlServer.Management.Smo.UserDefinedFunctionType]::Scalar
$f.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(5)
$f.Create()
#Создание CLRной TVF
$f = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunction" -ArgumentList $db, "Dir"
$f.TextMode = $false
$f.ImplementationType = [Microsoft.SqlServer.Management.Smo.ImplementationType]::SqlClr
$f.AssemblyName = $asm.Name
$f.ClassName = "FileSystem"
$f.MethodName = "Dir_InitMethod"
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter" -ArgumentList $f, "@folder", $([Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(266))
$f.Parameters.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter" -ArgumentList $f, "@shallowTraversal", $([Microsoft.SqlServer.Management.Smo.DataType]::Bit)
$f.Parameters.Add($o)
$f.FunctionType = [Microsoft.SqlServer.Management.Smo.UserDefinedFunctionType]::Table
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "ID", $([Microsoft.SqlServer.Management.Smo.DataType]::HierarchyId)
$f.Columns.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "FullName", $([Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(266))
$f.Columns.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "DateCreated", $([Microsoft.SqlServer.Management.Smo.DataType]::DateTime2(3))
$f.Columns.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "DateModified", $([Microsoft.SqlServer.Management.Smo.DataType]::DateTime2(3))
$f.Columns.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "LastAccessed", $([Microsoft.SqlServer.Management.Smo.DataType]::DateTime2(3))
$f.Columns.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "Properties", $([Microsoft.SqlServer.Management.Smo.DataType]::Xml(""))
$f.Columns.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "Size", $([Microsoft.SqlServer.Management.Smo.DataType]::BigInt)
$f.Columns.Add($o)
$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "IsDir", $([Microsoft.SqlServer.Management.Smo.DataType]::Bit)
$f.Columns.Add($o)
$f.TableVariableName = ""
$f.Create()
$srv.ConnectionContext.CapturedSql.Text | %{ Write-Host $_}
Скрипт 9
Рис.5
В случае, когда дерево зависимостей от объекта априорно неизвестно, следует прибегнуть к Microsoft.SqlServer.Management.Smo.DependencyТree, DependencyCollection и кодогенерации DDL средствами скриптера, как показывалось в предыдущем посте.
|
-
Я ожидал, что в комментах к предыдущему посту какая-нибудь добрая душа выложит Powershellьный вариант кода, и я с радостью им воспользуюсь, чтобы всякий раз не открывать Visual Studio. Нет, никто не сподобился. Пассивная масса.
Пришлось самому на старости лет засучать рукава и, кряхтя, осваивать PowerShell. Какой забавный язык! (с) граф типа отец Боярского в "Собаке на сене".
Ну, не взыщите. Приведенный ниже скрипт на PowerShell является аналогом сишарпейного кода "Автоматический редеплоймент сборки"\Скрипт 4.
Имеется dll, из которой на SQL Server по имени $srvName в базе $dbName сделана сборка по имени $asmName, а из нее разные процедуры и функции. В dll чего-нибудь поменяли, и сборку нужно передеплоить: убить все ее процедуры и функции, саму сборку, создать ее заново, пересоздать существовавшие на ее основе процедуры и функции. Если от ее процедур и функций успели стать зависимыми еще какие-нибудь объекты, цепочка, соответственно, удлиняется с двух сторон.
Если изменение в dll означало, что из нее удалились или добавились какие-нибудь методы, на основе которых существовали или предстоит создать SQLные модули, $scriptToCreate надо сохранить в файл, сделать возможность его откорректировать и уже потом выполнять.
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
function Main()
{
cls
$srvName = ""; $dbName = "TestFS"; $asmName = "MyAssembly"
$srv = new-object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $srvName
$db = $srv.Databases[$dbName]
$asm = $db.Assemblies[$asmName];
$scr = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Scripter" -ArgumentList $srv
$scr.Options.SchemaQualify = $true #чтобы объекты скриптовались со своими схемами (dbo, ...)
$scr.Options.IncludeIfNotExists = $true #предваряются оператором if exists при удалении и if not exists при создании
$scr.Options.ScriptDrops = $true #создается скрипт на удаление
$scr.Options.WithDependencies = $true #при генерации скрипта на удаление означает, что в скрипт также включаются referencing objects, т.е. которые зависят от данного
$scr.Options.DriUniqueKeys = $true #таблице TestFTS требуется ограничение unique на rowguidcol, его также нужно заскриптовать
$sb = New-Object -TypeName "System.Text.StringBuilder"
$scr.Script(@($asm)) | ForEach-Object -Process { [Void] $sb.Append($_ + "`n`n") } #[void], чтобы избежать uncaptured output в ф-ции, к-й будет относиться к ее рез-там
$scriptToDrop = $sb.ToString(); $sb.Length = 0
$scr.Options.ScriptDrops = $false #генерируется скрипт на создание
$dt = $scr.DiscoverDependencies(@($asm), $false) #parent = false означает, что под связанными объектами понимаются не родительские, а дочерние, т.е. referencing
$dc = $scr.WalkDependencies($dt) #скрипт получается в порядке от дочерних объектов к родительским, что неправильно, т.к. сначала должен создаваться родительский объект, а уже потом дочерние на его основе
$dc1 = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.DependencyCollection"
for ($i = $dc.Count; $i -gt 0; $i--) { [Void] $dc1.Add($dc[$i - 1]) } #переставляем в обратном порядке
$scr.ScriptWithList($dc1) | ForEach-Object -Process { [void] $sb.Append($_ + "`n`n") } #генерим скрипты от явно построенной коллекции связанных объектов
$scriptToCreate = $sb.ToString();
$scriptToCreate = ReplaceAssemblyBytesToFilePath $scriptToCreate $asm.SqlAssemblyFiles
Write-Host "Вы согласны с тем, что будут выполнены следующие скрипты:`n"
$scriptToDrop
$scriptToCreate
Write-Host "[Y/N]?"
[char] $key = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown").Character
if ($key.ToString().ToUpper() -eq "Y") { $db.ExecuteNonQuery($scriptToDrop); $db.ExecuteNonQuery($scriptToCreate) }
}
#<summary>
#Процедура производит замену в скрипте создания сборки
#create assembly ... from 0x4D5A900003000 ... with permission_set = ...
#бинарщины на пути к файлам этой сборки:
#create assembly ... from 'C:\...\bin\Debug\ClassLibrary1.dll' with permission_set ...
#</summary>
#<param name="scriptToCreate">
#Сгенеренный скрипт создания сборки и, возможно, других объектов.
#Однако предполагается, что в этом скрипте оператор create assembly один.
#В противном случае каждую сборку надо скриптовать отдельно.
#</param>
#<param name="files">Файлы из SqlAssembly.SqlAssemblyFiles</param>
#<returns></returns>
function ReplaceAssemblyBytesToFilePath($scriptToCreate, $files)
{
$pattern = new-object -TypeName "Text.RegularExpressions.Regex" -ArgumentList "create assembly \[\w+\]\r\nauthorization \[\w+\]\r\nfrom ", $([Text.RegularExpressions.RegExOptions]::IgnoreCase)
$match = $pattern.Match($scriptToCreate)
$sb = new-object -TypeName "System.Text.StringBuilder" -ArgumentList $scriptToCreate.Substring(0, $match.Index + $match.Length);
$files | ForEach-Object -Process { [Void] $sb.Append("'"); [Void] $sb.Append($_.Name); [Void] $sb.Append("', "); }
[Void] $sb.Remove($sb.Length - 2, 1) #убираем последнюю запятую (пробел оставляем)
$pattern = new-object -TypeName "Text.RegularExpressions.Regex" -ArgumentList "with permission_set", $([Text.RegularExpressions.RegexOptions]::IgnoreCase)
$match = $pattern.Match($scriptToCreate, $match.Index)
[Void] $sb.Append($scriptToCreate.Substring($match.Index))
return $sb.ToString()
}
Main
|
-
Коллега обратился с вопросом от партнера. У человека есть лицензия на коммерческую редакцию русского SQL Server 2008 32-бит. Вправе ли он в рамках этой лицензии установить вместо него английский SQL Server 2008 x64 той же редакции?
Я предположил, что да, поскольку:
· Лицензии на SQL Server 2008 не имеют битности в названии.
· Стоимости английской и русской версии равны.
но на всякий случай решил прояснить этот вопрос, обратившись в Licensing Group.
Подтвердили.
|
-
Не все сборки могут быть автоматически продеплоены из VS (см., напр., "Использование COMовских dll в SQL CLR"). В этом случае приходится их собирать как обычные библиотеки классов и каталогизировать вручную, выполняя CREATE ASSEMBLY и создавая содержащиеся в ней процедуры и функции (см. "Полнотекстовый поиск. Наполнение таблицы."\Скрипт 4). По мере того, как сборка обрастает функциональностью и количество содержащихся в ней процедур, функций, агрегатов, UDT растет, скрипт становится все более монструозным. Кроме того, SQL Server не дает удалить сборку, предварительно не удалив созданные на ее основе модули. На мой дилетантский взгляд, это ограничение имеет смысл, если модули, в свою очередь, имеют зависимые от них объекты, как например, таблица TestFTS ((см. "Полнотекстовый поиск. Наполнение таблицы."\Скрипт 5), которая использует в качестве значения по умолчанию для одного из полей результат CLRной функции GetFileExtension(). Точно так же нельзя удалить таблицу, пока имеется другая, зависящая от нее по foreign key. Но когда зависимостей нет, таблица спокойно удаляется, не требуя предварительного удаления содержащихся в ней колонок. Так же, наверно, можно было поступить и со сборками. Если ее модули не имеют дальнейших зависимостей, удаление сборки могло бы автоматом удалять и модули. Но это мои досужие философствования, которые к делу отношения не имеют, тем более, что чаще всего дальнейшие зависимости у модулей есть, как в нашем случае. Для того они и создаются. Стало быть, при передеплойменте нужно удалять зависимые объекты, модули, сборку, а потом все пересоздавать в обратной последовательности. Писать всякий раз руками для этого скрипт ломает. Обшаривать sys.assemblies, sys.assembly_files, sys.assembly_modules и другие DMV, чтобы руками составлять строчку со скриптом, тоже не есть практическое решение, тем более, что полезного поля definition, где в явном виде содержится скрипт создания процедуры/функции, как в sys.sql_modules, для CLRных процедур и функций нет.
В SSMS есть замечательная функциональность, когда кликнув правой кнопкой по объекту в Object Explorer, можно сказать Script ... as, и она сгенерирует на выбор скрипт его создания или удаления. Но хотелось бы не кликать всякий раз вручную, а автоматизировать процесс генерации скрипта. Как мы знаем, практически каждый клик в SSMS можно автоматизировать при помощи программной модели SMO, именно поэтому я так возбудился на эту тему в предыдущем посте. Если нельзя засунуть вызовы SMO на уровень сервера, давайте напишем элементарное консольное приложение, которое будет генерить скрипты удаления и создания для сборки и всех зависимых от нее по цепочке объектов и выполнять эти скрипты.
Простейшее использование скриптования в SMO не составляет сложности - практически у каждого объекта имеется метод Script. Имеется также отдельный класс Scripter, содержащий более тонкие возможности по скриптованию объектов. Мы соединяемся с заданным экземпляром SQL Server, получаем из коллекции баз нужную базу, находим в ней нужную сборку и говорим создать скрипты для удаления (Options.ScriptDrops = true) и создания (Options.ScriptDrops = false). Можно скриптовать сразу несколько объектов, в нашем случае массив состоит из всего одного - нашей сборки: new SMO.SqlSmoObject[] { asm }. Options.WithDependencies = true означает скриптовать не только переданный скриптеру объект, но и связанные с ним.
static void Main(string[] args)
{
string srvName = "", dbName = "TestFS", asmName = "MyAssembly";
SMO.Server srv = new SMO.Server(srvName);
SMO.Database db = srv.Databases[dbName];
SMO.SqlAssembly asm = db.Assemblies[asmName];
SMO.Scripter scr = new SMO.Scripter(srv);
StringBuilder sb = new StringBuilder();
scr.Options.SchemaQualify = true;
scr.Options.WithDependencies = true;
scr.Options.ScriptDrops = true;
scr.Options.IncludeIfNotExists = true;
foreach (string s in scr.Script(new SMO.SqlSmoObject[] { asm })) sb.Append(s + "\n\n");
string scriptToDrop = sb.ToString();
scr.Options.ScriptDrops = false;
sb.Length = 0;
foreach (string s in scr.Script(new SMO.SqlSmoObject[] { asm })) sb.Append(s + "\n\n");
string scriptToCreate = sb.ToString();
Debug.WriteLine(scriptToDrop); Debug.WriteLine(scriptToCreate);
}
Скрипт 1
Результат получается в виде коллекции строк, которые StringBuilder собирает в одну (sb.Append). Вот, что получилось в нашем случае для удаления сборки:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileExtension]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetFileExtension]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoadDir]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LoadDir]
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyAssembly' and is_user_defined = 1)
DROP ASSEMBLY [MyAssembly]
Скрипт 2
И для ее создания:
IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Interop.Shell32' and is_user_defined = 1)
CREATE ASSEMBLY [Interop.Shell32]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000...
WITH PERMISSION_SET = UNSAFE
IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyAssembly' and is_user_defined = 1)
CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000...
WITH PERMISSION_SET = UNSAFE
Скрипт 3
Скрипт 2 полностью устраивает. В Скрипте 3 не устраивают две вещи. Первое, если мы перекомпилили dllю и хотим пересоздать в SQL Server сборку на ее основе, оператор CREATE ASSEMBLY должен содержать путь к этой dlle, а не прочитанный из нее прошлый раз бинарный контент. Второе - в Скрипте 3 она поменяла направление зависимости. Представим себе объект как узел в иерархии объектов. Под связанными объектами можно понимать как дочерние ссылающиеся на него (referencing) объекты, т.е. зависящие от него, так и родительские (referenced, выше по иерархии), на которые ссылается он, т.е. от которых он сам зависит. Нас интересуют дочерние объекты, т.е. зависящие от этой сборки, и в скрипте удаления она абсолютно правильно выбрала их. Но в скрипте создания Options.WithDependencies поменяла направление, и она пошла скриптовать вверх по иерархии. Это не есть хорошо. Для того, чтобы это дело поправить, требуется прибегнуть к более тонким настройкам скриптера. Их у него есть, но документированы они отвратительно. Знать, параметры каких типов принимает или возвращает тот или иной метод, вообще говоря, недостаточно, чтобы понять, для чего он нужен и как работает. Практически изо всех членов класса Scripter идут в качестве иллюстрации ссылки на пример http://msdn.microsoft.com/ru-ru/library/ms162153.aspx, в котором задействовано от силы один, два и обчелся. Пример не позволяет представить цельной картины принципов работы скриптера, мало того, "Создание сценария зависимостей для базы данных на языке Visual C#" в нем просто не работает, потому что это какая-то смесь С#, VB.Net, французского, нижегородского... Неудивительно, что по форумам народ постоянно задает вопросы про скриптер, а тамошние гуры ничтоже сумняшеся копипастят этот пример, больше нечего: http://stackoverflow.com/questions/539067/how-can-i-programatically-clone-a-database-schema-in-sql-server. Впрочем, у нас таких гур тоже хватает, которые заработали немеренное число постов за счет копи-паста, а самостоятельно подумать уже нечем, потому что модераторы. Остается надувать щеки и гнобить новичков.
Если кратко, тонкие настройки скриптера позволяют построить дерево иерархии объектов. Это делается при помощи метода DiscoverDependencies(new SMO.SqlSmoObject[] { asm }, false), где в первом параметре передаются базовые объекты, а во втором - направление движения по иерархии: true = вверх от каждого базового объекта к тем, от которых он зависит, или false = вниз, к тем, которые зависят от него. Построенное дерево иерархий можно посмотреть и при необходимости подправить в нем узлы. На основе дерева строится коллекция объектов, т.е. уже плоский список, в каком порядке они будут обходиться, чтобы сгенерировать скрипт для каждого. Это делается при помощи метода WalkDependencies(дерево). Коллекцию, то есть порядок, при необходимости тоже можно подправить. В коде это иллюстрируется. Наконец, генерация скриптов, которая делается при помощи метода ScriptWithList(коллекция). Этого нам на первый раз достаточно.
В построенном скрипте создания при помощи шаблона регулярных выражений отыскивается строка с бинарным контентом сборки и заменяется на пути к ее файлам. Полные имена файлов сборки находятся в sys.assembly_files, а в случае SMO это коллекция в свойстве SqlAssemblyFiles объекта сборки. Для иллюстрации идеи, наверно, все. Понятно, что делалось на коленках, так что простор для исправлений и совершенствований, но для моей узкой задачи автоматизации редеплоймента сборки, переносящей файлы из папки в таблицу, оно, кажется, даже работает:
using System;
using System.Text;
using System.Diagnostics;
using SMO = Microsoft.SqlServer.Management.Smo; //C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
//Также требует references на
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
using System.Text.RegularExpressions;
class Program
{
static int dependencyTreeLevel;
static void Main(string[] args)
{
string srvName = "", dbName = "TestFS", asmName = "MyAssembly";
SMO.Server srv = new SMO.Server(srvName);
SMO.Database db = srv.Databases[dbName];
SMO.SqlAssembly asm = db.Assemblies[asmName];
SMO.Scripter scr = new SMO.Scripter(srv);
StringBuilder sb = new StringBuilder();
scr.Options.SchemaQualify = true; //чтобы объекты скриптовались со своими схемами (dbo, ...)
scr.Options.IncludeIfNotExists = true; //предваряются оператором if exists при удалении и if not exists при создании
scr.Options.ScriptDrops = true; //создается скрипт на удаление
scr.Options.WithDependencies = true; //при генерации скрипта на удаление означает, что в скрипт также включаются referencing objects, т.е. которые зависят от данного
scr.Options.DriUniqueKeys = true; //таблице TestFTS требуется ограничение unique на rowguidcol, его также нужно заскриптовать
foreach (string s in scr.Script(new SMO.SqlSmoObject[] { asm })) sb.Append(s + "\n\n");
string scriptToDrop = sb.ToString(); sb.Length = 0;
scr.Options.ScriptDrops = false; //генерируется скрипт на создание
//при этом Options.WithDependencies = true означает, что будут также заскриптованы referenced objects, т.е. объекты, от которых зависит данный
//нас это не устраивает, нам по-прежнему нужны referencing objects, поэтому прибегаем к явному построению дерева связанных объектов
SMO.DependencyTree dt = scr.DiscoverDependencies(new SMO.SqlSmoObject[] { asm }, false); //parent = false означает, что под связанными объектами понимаются не родительские, а дочерние, т.е. referencing
dependencyTreeLevel = 0; //это будет глубина текущего уровня для отступов при выводе
EnlistNodesInDependencyTree(dt.FirstChild);
SMO.DependencyCollection dc = scr.WalkDependencies(dt); //скрипт получается в порядке от дочерних объектов к родительским, что неправильно, т.к. сначала должен создаваться родительский объект, а уже потом дочерние на его основе
SMO.DependencyCollection dc1 = new SMO.DependencyCollection();
for (int i = dc.Count; i > 0; i--) dc1.Add(dc[i - 1]); //переставляем в обратном порядке
foreach (string s in scr.ScriptWithList(dc1)) //генерим скрипты от коллекции явно построенных связанных объектов
sb.Append(s + "\n\n");
string scriptToCreate = sb.ToString();
scriptToCreate = ReplaceAssemblyBytesToFilePath(scriptToCreate, asm.SqlAssemblyFiles);
Console.WriteLine("Вы согласны с тем, что будут выполнены следующие скрипты [Y/N]?");
Console.WriteLine();
Console.WriteLine(scriptToDrop); Console.WriteLine(scriptToCreate);
if (Console.ReadKey().KeyChar.ToString().ToUpper() == "Y")
{
db.ExecuteNonQuery(scriptToDrop); db.ExecuteNonQuery(scriptToCreate);
}
}
/// <summary>
/// Процедура визуализирует построенное дерево связанных объектов.
/// </summary>
/// <param name="firstNodeOnLevel">Первый узел дерева</param>
static void EnlistNodesInDependencyTree(SMO.DependencyTreeNode firstNodeOnLevel)
{
if (firstNodeOnLevel == null) return;
SMO.DependencyTreeNode dtn = firstNodeOnLevel; dependencyTreeLevel++;
do
{
Debug.WriteLine(new String(' ', dependencyTreeLevel), dtn.Urn);
EnlistNodesInDependencyTree(dtn.FirstChild);
dtn = dtn.NextSibling;
}
while (dtn != null);
}
/// <summary>
/// Процедура производит замену в скрипте создания сборки
/// create assembly ... from 0x4D5A900003000 ... with permission_set = ...
/// бинарщины на пути к файлам этой сборки:
/// create assembly ... from 'C:\...\bin\Debug\ClassLibrary1.dll' with permission_set ...
/// </summary>
/// <param name="scriptToCreate">
/// Сгенеренный скрипт создания сборки и, возможно, других объектов.
/// Однако предполагается, что в этом скрипте оператор create assembly один.
/// В противном случае каждую сборку надо скриптовать отдельно.
/// </param>
/// <param name="files">Файлы из SqlAssembly.SqlAssemblyFiles</param>
/// <returns></returns>
static string ReplaceAssemblyBytesToFilePath(string scriptToCreate, SMO.SqlAssemblyFileCollection files)
{
Regex pattern = new Regex(@"create assembly \[\w+\]\r\nauthorization \[\w+\]\r\nfrom ", RegexOptions.IgnoreCase);
Match m = pattern.Match(scriptToCreate);
StringBuilder sb = new StringBuilder(scriptToCreate.Substring(0, m.Index + m.Length));
foreach (SMO.SqlAssemblyFile file in files) { sb.Append('\''); sb.Append(file.Name); sb.Append("', "); }
sb.Remove(sb.Length - 2, 1); //убираем последнюю запятую (пробел оставляем)
pattern = new Regex(@"with permission_set", RegexOptions.IgnoreCase);
m = pattern.Match(scriptToCreate, m.Index);
sb.Append(scriptToCreate.Substring(m.Index));
return sb.ToString();
}
}
Скрипт 4
Рис.1
Потребовалось дополнительно отметить скриптование в таблице ограничения unique (scr.Options.DriUniqueKeys = true), т.к. это условие на колонку $rowguid является обязательным, когда в таблице присутствуют файлстримовские поля - см. пост "Введение в FILESTREAM", создание таблицы Media. Можно проверить, что действительно сборка и зависимые от нее объекты были пересозданы:
select name, create_date from sys.assemblies where name = 'MyAssembly'
select a.name, am.assembly_method, o.create_date from sys.objects o join sys.assembly_modules am on o.object_id = am.object_id join sys.assemblies a on am.assembly_id = a.assembly_id
и т.д.
Рис.2
Написание Powershellьного варианта Скрипт 4 предоставляется читателям в качестве самостоятельного упражнения.
|
-
SMO (в прошлом SQL-DMO) - это SQL Server Management Objects, .Netовская библиотека для управления объектами SQL Server, очень удобная тем, что ее структура классов повторяет иерархию объектов, как они видятся в SQL Server Management Studio, и позволяющая запрограммировать в ней практически любой клик.
Вполне очевидно, что полезность этой библиотеки многократно возросла, если бы написанные с ее помощью административные задачи можно было оформлять не только как клиентские приложения, но и в виде логики на стороне SQL Server. Скажу больше, на мой взгляд, она прямо-таки напрашивается выполняться на серверной стороне. Но не все вещи, которые нам представляются очевидными, реализованы в жизни, иначе она была бы неправдоподобно хороша.
У меня было очень сложное клиентское приложение
using System;
using System.Diagnostics;
using SMO = Microsoft.SqlServer.Management.Smo; //C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
//Также требует references на
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SMO.Server srv = new SMO.Server();
Debug.WriteLine(srv.Name);
}
}
}
Скрипт 1
которое я мечтал оформить в виде CLRной хранимой процедуры:
using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
//Также требует references на
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
public class Class1
{
[SqlFunction]
public static void Main()
{
SMO.Server srv = new SMO.Server();
SqlContext.Pipe.Send(srv.Name);
}
}
Скрипт 2
Ее нужно делать в виде обычной библиотеки классов, потому что из SQL Serverного проекта ссылаемые dllи не видны - см. пост "Использование COMовских dll в SQL CLR". Чучелом или тушкой - на SQL Server 2005 она не пролезала никак. Стоило сказать CREATE ASSEMBLY, как выдавалась ругань:
Assembly 'ля-ля-ля' references assembly 'microsoft.sqlserver.batchparser, version=9.0.242.0, culture=neutral, publickeytoken=...', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Как написано здесь - http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/270dc01a-aff4-4707-bd7e-aca81b1399d3 - SMO не поддерживается внутри SQL Server. Обычно, что можно сделать в сценарии, когда какая-нибудь системная dll не поддерживается, это вручную зарегистрировать ее на базе. Однако в случае SMO это работать не будет. Точка.
Можно сказать, что препятствие носило фатальный характер, потому что, как обойти его, не знал никто ни до выхода SQL Server 2005, ни после. От отчаяния на коннекте появился следующий пост: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126386, на который примерно через 9 месяцев разродился ответ, что We have selected your request for consideration in the next version of SQL Server. Недавно предоставился повод проверить, насколько успешным было рассмотрение. Я попробовал продеплоить сборку Скрипт 2 на SQL Server 2008.
if object_id('Main', 'PC') is not null drop proc Main
if exists(select 1 from sys.assemblies where name = 'MyAssembly1') drop assembly MyAssembly1
create assembly MyAssembly1 from 'C:\Temp\ClassLibrary1\bin\Debug\SMOTest.dll' with permission_set = unsafe
go
create proc Main as external name MyAssembly1.Class1.Main
go
Скрипт 3
Вы не поверите, таки Command(s) completed successfully. Если бы она при этом еще работала, было бы совсем счастье.
exec Main
Скрипт 4
Счаз. Полного счастья не бывает.
Msg 6522, Level 16, State 1, Procedure Main, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "Main":
System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application.
System.Exception:
at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor()
at Microsoft.SqlServer.Management.Common.ServerConnection..ctor()
at Microsoft.SqlServer.Management.Smo.ExecutionManager..ctor(String name)
at Microsoft.SqlServer.Management.Smo.Server..ctor()
at Class1.Main()
Если закомментарить обращение к SMO, все работает на ура:
public class Class1
{
[SqlFunction]
public static void Main()
{
//SMO.Server srv = new SMO.Server();
SqlContext.Pipe.Send("ля-ля-ля");
}
}
Скрипт 5
Обычно, когда ей нужны подкрепляющие assemblies, она не стесняется про них спрашивать прямым текстом. Например, когда тягаем LINQ, к нему требуются c:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll и C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.Runtime.Serialization.dll. Она об этом так и пишет:
Msg 10301, Level 16, State 1, Line 1
Assembly 'System.Data.Linq' references assembly 'system.runtime.serialization, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(failed to retrieve text for this error. Reason: 15105)). Please load the referenced assembly into the current database and retry your request.
Здесь - молчок. Ни гу-гу ни про batchparser, ни про Microsoft.SqlServer.ConnectionInfo с Microsoft.SqlServer.Management.Sdk.Sfc. Можно предположить один из двух выводов. Первое - они рассмотрели и не нашли нужным, потому что рассмотреть в положительную сторону никто не обещал. Тогда слова про disabled functionality относятся ко всем SMO, и можно, стало быть расслабиться. Второе - слова относятся к моей неуклюжей попытке установления соединения. Возможно, там надо было написать что-то вроде строки с context connection=true, и тогда SMO нынче все-таки может работать внутри CLR на стороне SQL Server. Если вам известен ответ, напишите его в комментах, за что вам выразит глубочайшую признательность не токмо худый и смиренный аз, но и все читающее сообщество.
Во всяком случае на create assembly она уже не ругается, что наглядно подтверждает возросшую функциональность, устойчивость и масштабируемость 2008-го. Пойду посмотрю, может, в R2 уже и exec работает.
|
-
В предыдущих сериях картины в таблицу была затащена файловая папка, где хранились посты в данный блог, доклады семинаров, статьи, вспомогательная литература и другие материалы, относящиеся к работе sqlclub. Каждый файл теперь является записью, а атрибуты и контент - полями. По файлстримовскому полю, где в бинарном виде хранится содержание файлов, построен полнотекстовый индекс. Однако большинство файлов хранятся в формате Office 2007 (.docx, .xslx, .pptx и пр.), а фильтра для него SQL Server 2008 по умолчанию не знает. Фильтр - это такая dll, которая умеет превращать бинарный контент файла в осмысленный текст, чтобы его можно было полнотекстно проиндексировать. Она выполняется не внутри SQL Server, a в fdhost.exe (см. Введение). Для каждого индексируемого типа файла имеется фильтр. В противном случае в полнотекстовом логе выдается сообщение см. пост "Создание полнотекстового индекса", после Рис.5.
Рис. 1
Зачастую один фильтр умеет управляться с несколькими типами файлов. Тип указывается в отдельной колонке при создании полнотекстового индекса - см. "Создание полнотекстового индекса"\Скрипт 2. Ор внутри полнотекстового лога означает, что для каких-то встретившихся в таблице TestFTS типов у нее не нашлось зарегистрированных фильтров.
Типы файлов, которые понимает SQL Server, что называется, "из коробки", т.е. для которых он знает, какие фильтры применять и их у него есть, перечислены в реестре в HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters:
Рис.2
Запоминаем содержимое поля Data для типа и идем с ним в папку выше, которая называется CLSID. Например, {F07F3920-7B8C-11CF-9BE8-00AA004B9986} там соответствует такая картина:
Рис.3
из которой понятно, что для индексации типа .doc хост fdhost.exe использует библиотеку offfilt.dll, это IFilter для документов Office 97 - 2003, полный путь к которой (HKEY_CLASSES_ROOT\CLSID\{f07f3920-7b8c-11cf-9be8-00aa004b9986}\InprocServer32) = %systemroot%\system32\OffFilt.dll.
Информацию на Рис.2-3 можно получить, не прибегая к реджистри. Для этого в SQL Server имеется DMV sys.fulltext_document_types:
Рис.4
Осталось понять, каких расширений не достало в Рис.1. Для этого можно запомнить оттуда Full-text key value и сделать запрос
select top 10 ID.ToString(), FullName, type from TestFTS where ID.IsDescendantOf('/1/1/') = 1 and ID.GetLevel() = 3
из которого, например, следует, что файлы с расширением .url (/1/1/4/) и .htm (/1/1/6/) не вызвали неприятия, а файлы с расширением .csv (/1/1/2/), ps1 (/1/1/3/) и т.д. не были распарсены по словам и, соответственно, проиндексировнаы . Но можно поступить проще. Имеются замечательные DMV (см. "Создание полнотекстового индекса"\продолжение Скрипта 10), которые позволяют получить список (и количество) проиндексированных слов в разбивке по полнотекстовым колонкам (если их > 1) для каждой конкретной записи. Например,
if object_id('tempdb..#DocID_UniqueInd_Mapping', 'U') is not null drop table #DocID_UniqueInd_Mapping
create table #DocID_UniqueInd_Mapping
(
DocID int primary key,
[Key] HierarchyID not null
)
declare @table_id int = object_id('TestFTS')
insert into #DocID_UniqueInd_Mapping exec sp_fulltext_keymappings @table_id = @table_id
--select t.FullName, t.type, kbd.display_term from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), @table_id ) kbd inner join #DocID_UniqueInd_Mapping m on kbd.document_id = m.DocID
--join TestFTS t on m.[Key] = t.ID order by 1
select t.FullName, t.type, count(kbd.display_term) from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), @table_id ) kbd inner join #DocID_UniqueInd_Mapping m on kbd.document_id = m.DocID
join TestFTS t on m.[Key] = t.ID group by t.FullName, t.type order by 1
Скрипт 1
Рис.5
Сount = 1 означает, что разбивка по словам не произошла - там образовалась всего одна строчка c display_term = END OF FILE. Мы видим, что непосредственно из коробки SQL Server не понимает форматов Office 2007 и не может проиндексировать файлы с расширениями .docx и др. Этот пробел берется восполнить 2007 Office System Converter: Microsoft Filter Pack, который берется здесь - http://www.microsoft.com/downloads/details.aspx?FamilyId=60C92A37-719C-4077-B5C6-CAC34F4227CC&displaylang=en. Он весит 3-4 метра в зависимости от того, для х86 или для х64. Установка производится бесхитростно, в очередной раз я не успел снять красивый скриншот с прогрессбаром
Рис.6
Потом открываем BOL и читаем Как изменить список зарегистрированных средств разбиения по словам и фильтров. Выполняем
exec sp_fulltext_service @action = 'load_os_resources', @value = 1
exec sp_fulltext_service @action = 'update_languages'
exec sp_fulltext_service @action = 'restart_all_fdhosts'
Скрипт 2
Microsoft Filter Pack 1.0 привносит dllи, лежащие в %ProgramFiles%\Common Files\microsoft shared\Filters, добавляющие поддержку форматов Office 2007 .docx, .docm, .pptx, .pptm, .xlsx, .xlsm, .xlsb, .zip, .one, .vdx, .vsd, .vss, .vst, .vdx, .vsx, .vtx. В HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters они не появляются. Я повторил запрос рис.4 и убедился, что эти и другие расширения добавились в список типов файлов, индексируемых SQL Server. Индекс у меня находился в режиме ручного Change Tracking, поэтому я выполнил запрос "Создание полнотекстового индекса"\Скрипта 15, подождал немного, пока процесс индексации таблицы не исчезнет из sys.dm_fts_index_population и заглянул в полнотекстовый лог. В нем iFTS по-прежнему бурно выражал недовольство по поводу отсутствия необходимых фильтров. Как выяснилось чуть позже, это были другие фильтры. Не разобравшись, я запустил полную популяцию "Создание полнотекстового индекса"\Скрипт 18, этого показалось мало, от широты души перестроил каталог "Создание полнотекстового индекса"\Скрипт 19, протер фары и прочитал КВ "How to register Microsoft Filter Pack IFilters with SQL Server 2005 and with SQL Server 2008", где еще более широкие душою люди рекомендуют в 2008-м за неимением msftesql перестартовать в целом SQL Server. Хвала создателю, до этого не дошло. Запустив для очистки совести Скрипт 1 в очередной раз, я увидел, что на Рис.5 напротив .docx-ов стали прирастать циферки, то есть 2007-й офисный фильтр заработал нормально, преобразуя бинарщину в словесный поток.
Хуже обстоит дело с plain text фильтром query.dll, применяемым по умолчанию для кучи типов файлов, включая .сs, .csv, .sql и др. В SQL Server путь к нему видится абсолютно дебильно как C:\Windows\system32\%systemroot%\system32\query.dll, потому что программисту, как и водителю, следует выходить на работу трезвым. Вы сами видите, к чему приводит нарушение этого правила, несмотря на то, что в реестре по CLSID = {C1243CA0-BF96-11CD-B579-08002B30BFEB} все обозначено абсолютно корректно:
Рис.7
Из этого, в Windows Search, например, тот же самый plain text filter работает:
 
Рис.8
 
Рис.9
А SQL Server из-за неправильно прописанного пути фильтра его не находит, хоть ты тресни. Скрипт 1 и Рис.5 показывает, что .sqlи не индексируются:
select t.ID.ToString(), t.FullName, t.type, count(kbd.display_term) from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), @table_id ) kbd inner join #DocID_UniqueInd_Mapping m on kbd.document_id = m.DocID
join TestFTS t on m.[Key] = t.ID group by t.ID, t.FullName, t.type order by 1
Рис.10
и полнотекстный лог это, разумеется, подтверждает. Можно удостовериться, это те самые IDшники.
Рис.11
Такая же порнография творится для фильтров C:\Windows\system32\%systemroot%\system32\nlhtml.dll и C:\Windows\system32\%systemroot%\system32\xmlfilter.dll.
Это не единственный косяк с Filter Pack'ом. Есть еще КВ, http://support.microsoft.com/kb/960502. К счастью, мне пока не требуется искать в pptx дальше третьего слайда. Я также готов поступиться .csproj и проч. но в .cs и .sql у меня хранилось много текста, который бы хотелось видеть индексированным, елико сие возможно.
Вначале, полюбовавшись на Рис.2, я решил добавить ключ по имени .sql в HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters на Рис.2. Сказал New -> Key и вбил ему в дефолтное значение class_id из строчки с %ProgramFiles%\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\msfte.dll на Рис.7. Это тоже фильтр общего назначения, используемый по умолчанию. Разница в том, что он SQL Serverный, а query.dll поставляется с Windows.
Рис.12
Перевыполнил Скрипт 2 - ничего не произошло. Добавил в Скрипт 2 второй строчкой
exec sp_fulltext_service @action = 'verify_signature', @value = 0
снова ничего. Последовательно перестартовал SQL Server, машину, протер фары - фиг. Для .sql остается фильтром query.dll, как было на Рис.7. Раз они не умеют по-человечески парсить %systemroot%, злобно подумал я, проще всего убрать его нафиг из пути. Отправился в реджистри на Рис.7 и подставил в дефолтное значение HKCR\CLSID\{c1243ca0-bf96-11cd-b579-08002b30bfeb}\InprocServer32 фактическую строчку C:\Windows\system32\query.dll. Как водится, выполнил Скрипт 2 и запрос Рис.4. SQL Serverу замена явно пришлась по душе, потому что в графе version и manufacturer нарисовались осмысленные значения:
Рис.13
Поперла фишка, подумал я и перезапустил популяцию. Не тут-то было. Снова туча ошибок в логе, как на Рис.11 и единички напротив .sql на Рис.10. Хотел расстроиться, но обратил внимание, что единички стоят только напротив .sql, а, например, .cs'ы, .csv и все прочие, находившиеся под опекой query.dll, стали индексироваться и выдавать про себя осмысленные количества слов. Елы-палы! Пошел в реджистри и убил созданный на Рис.12 ключ. Снова Скрипт 2, далее перепопуляция индекса, проверяем (sys.dm_fts_index_population), что закончилась, смотрим Скрипт 1:
Рис.14
А, блин, сказали суровые сибирские лесорубы. В смысле, наоборот. Заработала, зараза.
Еще хотелось иметь индексный поиск по pdf'овским документам. Когда-то pdf'овский фильтр нужно было отдельно скачивать с сайта Аdobe. Как написано на http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611, начиная с версии 7.0.5, функциональность iFilter включена в Acrobat и Reader. В версии 8 добавлена поддержка Висты и Windows Desktop Search, а также, как полагается, производительность, устойчивость и т.д. Вместо установки отдельного плагина iFilter теперь рекомендуется обновить нашу копию Adobe Acrobat или Reader для получения наиболее актуальной функциональности iFilter. Обновлять мне было нечего, поскольку виртуалка в очередной раз благоухала свежестью и первозданностью; я просто пошел скачал Adobe Reader последней на данный момент версии 9.10 и поставил его туда. Привычно выполнил Скрипт 10 и узрел в результатах Рис.4 строчку .pdf:
Рис.15
Перезапустил популяцию и повторил запрос Скрипт 1
select t.ID.ToString(), t.FullName, t.type, count(kbd.display_term) from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), @table_id ) kbd inner join #DocID_UniqueInd_Mapping m on kbd.document_id = m.DocID
join TestFTS t on m.[Key] = t.ID where t.type = '.pdf' group by t.ID, t.FullName, t.type order by 1
Рис.16
Pdf'ы стали парситься, как и все допреж. Теперь по всем этим типам файлов в таблице: .pdf, .txt, .htm, .doc, .docx, .xls, .xlsx, .csv, .cs, .sql и т.д. можно пускать полнотекстовые запросы.
|
-
Продолжение. См. пред.серии "Введение" и "Наполнение таблицы".
Подготовительные действия на этом закончены, можно переходить к созданию полнотекстовых индексов. При этом можно идти двумя путями: визардо-гуевым и Т-SQLным. Я буду придерживаться второго, потому что скрипты публиковать быстрее, чем скриншоты. Ознакомиться с первым можно в документации - http://msdn.microsoft.com/ru-ru/library/aa337082.aspx.
Для начала стоит создать полнотекстовый каталог. В 7.0 - 2005 полнотекстовый каталог был действительно каталогом файловой системы, куда клались полнотекстовые индексы. В 2008 полнотекстовые индексы хранятся внутри SQL Server, поэтому понятие полнотекстового каталога носит в нем абстрактный характер. Это просто логическое объединение полнотекстовых индексов.
if exists (select 1 from sys.fulltext_catalogs where name = 'Cat1') drop fulltext catalog Cat1
create fulltext catalog Cat1 with accent_sensitivity = off as default authorization dbo
Скрипт 1
Был создан полнотекстовый каталог по имени Cat1, владельцем которого является dbo. Аccent_sensitivity = off означает, что и и й, е и ё не будут различаться. По умолчанию эта установка берется из коллации базы, но, поскольку она у меня Cyrillic_General_CI_AS, нечувствительность к надстрочным знакам пришлось оговорить особо. Не то, чтобы это имело какое-то значение в последующей демонстрации, просто хотелось показать. В 2005-м в операторе create fulltext catalog можно было указать, в каком месте файловой системы его создать (опция IN PATH). Также внутри себя SQL Server создавал системные объекты, ссылавшиеся на файлы каталога, и можно было ему сказать, на какой файл-группе их хранить (опция ON FILEGROUP), чтобы все полнотекстовое хозяйство бэкапилось вместе с этой файл-группой. В sys.database_files файлы этой файл-группы виделись с type_desc = "fulltext". Прикольно выразился Robert Vieira в своей книжке "Professional SQL Server 2005 Programming": For the vast, vast majority of installations, you're going to want to stick with the primary filegroup (which is default). So, in short, now that I've told you about this option, forget about it. Забыть стоит еще и потому, что в 2008-м обе этих опции лишены смысла. Поскольку полнотекстовые индексы теперь хранятся не в файловой системе, а в базе данных, каждому полнотекстовому индексу, как и обычному, можно сказать, на какой файл-группе он должен располагаться. Опции IN PATH и ON FILEGROUP в синтаксисе остались, но не имеют эффекта. В связи с этим, если скриптовать из SSMS 2008 полнотекстовый каталог на 2005-м, они в скрипте не отразятся, что может быть не вполне ожидаемым результатом для 2005-го.
Ниже создается полнотекстовый индекс по полю Content таблицы TestFTS. Полнотекстовый индекс на таблицу может быть только один, поэтому отдельное имя ему не требуется, а если нужно проиндексировать несколько полей, их всех нужно перечислить – см. http://msdn.microsoft.com/ru-ru/library/ms187317.aspx. Поле Content содержит не текст, а бинарное содержание файла, которое требуется распарсить при помощи фильтра. Применение нужного фильтра производится на основе расширения файла, вынесенного в колонку Type.
if exists(select 1 from sys.fulltext_indexes where object_id = object_id('dbo.TestFTS')) drop fulltext index on TestFTS
create fulltext index on TestFTS (Content type column Type language 1049)
key index ixId
on (Cat1, filegroup FTS)
with change_tracking = auto, stoplist = system
Скрипт 2
Language 1049 означает, что к распарсенному результату будут применяться брейкер и стеммер (см. "Введение") для русского языка. Русский полнотекст поддерживается в SQL Server 2008, что называется, из коробки; никаких дополнительных действий для включения поддержки предпринимать не требуется – см.
select * from sys.fulltext_languages order by name
Скрипт 3
Полнотекстовый индекс создается в каталоге Cat1 (см. Скрипт 1), в файл-группе FTS (см. "Наполнение таблицы"\Скрипт 2).
Если при создании полнотекстового индекса не указывать язык, будет взят язык по умолчанию. Посмотреть (и при желании поменять его) можно через sp_configure:
exec sp_configure 'default full-text language'
name minimum maximum config_value run_value
--------------- --------- ----------- ----------- ------------ -----------
default full-text language 0 2147483647 1033 1033
Скрипт 4
Для полнотекстового индекса необходим обычный уникальный индекс, идентифицирующий строки таблицы. В его качестве подсовывается созданный ранее ixId – см. "Наполнение таблицы"\Скрипт 6. В предыдущем посте, говоря о структуре полнотекстового индекса как об инверсивном индексе, я для простоты представил более идеалистическую картину. Во-первых, я не стал упоминать г) порядковый номер колонки, т.к. полнотекстовый индекс может строиться по нескольким полям таблицы. Во-вторых, в качестве а) используется не РК, и даже не key index, а свой идентификатор записи в таблице под названием DocID. Он является тупо целочисленным. Отсюда следуют два неприятных вывода. Во-первых, количество строк в полнотекстно-индексированной таблице не может превышать максимального целого в пределах отведенной на DocID длины. В 2005-м это было 4 байта, т.е. при кол-ве строк таблицы > 2**32 полнотекстовый поиск загибался. В 2008-м, слава богу, сделали bigint. Во-вторых, требуется перетолмачить ключи уникального индекса, указанного в качестве key index при создании полнотекстового, в DocID. Если key index был по целочисленному полю, перетолмачивать ничего не требовалось, она брала его в качестве DocID; в противном случае создавалась таблица соответствия key index <-> DocID, что, понятно, не прибавляло производительности. В 2005-м ее затащили из FTS внутрь SQL Server, что преподносилось как важное завоевание трудящихся. В 2008-м FTS стал iFTSом, и я, честно говоря, ждал, что под это дело DocID отождествят с key index, а дурацкую перекодировку уберут. Не тут-то было. Видимо, полнотекстовый поиск у нас теперь хоть и интегрированный, но все же не до такой степени. Чтобы в следующей версии было, куда интегрировать его еще глыбже. Посмотреть перекодировку можно при помощи хранимой процедуры sp_fulltext_keymappings.
declare @table_id int = object_id('dbo.TestFTS')
exec sp_fulltext_keymappings @table_id = @table_id
Скрипт 5
Блин, когда же они наконец догадаются сделать поддержку выражений в параметрах процедур?
рис.1
Key - это бинарное представление ключей нашего уникального индекса над таблицей по полю HierarchyID. На самом деле соответствие хранится во внутренней системной таблице, а эта процедура просто делает из нее выборку. Существует изрядно внутренних таблиц на тему полнотекста; их можно перечислить так:
select * from sys.internal_tables where name like '%fulltext%' or name like '%fts%' order by name
Скрипт 6
рис.2
Легко видеть, что все они сопровождаются ObjectID той пользовательской таблицы, к которой относятся. Например, вот здесь хранится перекодировка key index <-> DocID:
declare @s varchar(10) = str(object_id('dbo.TestFTS'))
exec ('select * from sys.fulltext_index_docidmap_' + @s)
Скрипт 7
Понятно, что поскольку таблица internal, выполнять его надо с DACa. Также по этой таблице можно убедиться, что длина DocID в 2008-м составляет 8 байт:
select * from sys.all_columns where name = 'docid' and object_id = object_id('sys.fulltext_index_docidmap_' + @s)
Скрипт 8
Посмотреть, как в общих чертах выглядит полнотекстовый индекс, можно по таблицам sys.ifts_comp_fragment_<object_id полнотекстно индексированной таблицы>_ля-ля-ля. Это фрагменты полнотекстового индекса.. Все существующие фрагменты перечислены в DMV sys.fulltext_index_fragments, где ля-ля-ля – колонка fragment_id. Для начала нужно написать вспомогательную функцию
if object_id('dbo.SwapBytes', 'FN') is not null drop function dbo.SwapBytes
go
create function dbo.SwapBytes(@x varbinary(max)) returns varbinary(max) as begin
declare @y varbinary(max) = cast('' as varbinary(max)), @i bigint = 0, @l bigint = len(@x), @b1 binary(1), @b2 binary(1)
while 1 = 1 begin
set @i += 1
if @i > @l break
set @b1 = substring(@x, @i, 1)
set @i += 1
if @i > @l begin set @y += @b1; break end
set @b2 = substring(@x, @i, 1)
set @y += @b2 + @b1
end
return @y
end
Скрипт 9
так как в колонке Keyword они идут по принципу little endian: 0x00770069006E0064006F00770073, тогда как T-SQLная конвертация varbinary в nvarchar построена по принципу big endian:
select cast(N'windows' as varbinary(max))
---
0x770069006E0064006F0077007300
C помощью функции Скрипт 9 не составляет труда конвертнуть Keyword из varbinary в nvarchar:
declare @s varchar(10) = str(object_id('dbo.TestFTS'))
exec ('select cast(dbo.SwapBytes(keyword) as nvarchar(max)), colid, pid, docidmin, docidmax, dupseq, lv1cnt, complv1, complv2 from sys.ifts_comp_fragment_' + @s + '_336')
Скрипт 10
рис.3
Идейно то же самое можно получить при помощи DMF
select * from sys.dm_fts_index_keywords(db_id('TestFS'), object_id('TestFTS'))
для всей таблицы скопом и при помощи DMF
select * from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), object_id('TestFTS'))
в разбивке по строкам. В вывод добавляется поле DocID, по которому можно связать слова со строками таблицы, пользуясь перекодировкой DocID <-> unique index в sp_fulltext_keymappings (см.Скрипт 5).
Потоки ползают по таблице, каждый по своим записям и строят такие куски полнотекстового индекса. Процесс консолидации кусков в единый индекс называется master merge. Он происходит автоматом через каждые 500 тыс. проиндексированных записей, либо принудительно по команде:
alter fulltext catalog Cat1 reorganize
Скрипт 11
Список каталогов можно посмотреть в DMV sys.fulltext_catalogs, а перечень фрагментов - в sys.fulltext_index_fragments. Если после Скрипта 10 взглянуть на fulltext_index_fragments, мы увидим там всего один фрагмент с совокупными значениями data_size и row_count. Поле Status может иметь следующие значения (http://msdn.microsoft.com/ru-ru/library/cc280700.aspx): 0 — фрагмент только что создан и еще не использован; 1 — используется для вставки во время заполнения или слияния полнотекстового индекса; 4 — закрыт и готов к запросу; 6 — используется для входа слияния и готов к запросу; 8 — помечен для удаления и не будет использоваться для запросов и как вход слияния. Состояние 4 или 6 означает, что фрагмент является частью логического полнотекстового индекса и к нему можно выполнять запросы; это запрашиваемый фрагмент. Состояние процесса слияния можно узнать как select fulltextcatalogproperty('Cat1', 'MergeStatus'). Здесь все бесхитростно (http://msdn.microsoft.com/ru-ru/library/ms190370.aspx): 0 = слияние в единый файл не выполняется; 1 = слияние в единый файл выполняется.
Как написано в BOL, не рекомендуется во время мастер-слияния перестраивать или реорганизовывать некластерные индексы на эту таблицу.
Количество проиндексированных строк можно посмотреть при помощи ф-ции objectpropertyex:
select objectpropertyex (object_id('TestFTS'), 'TableFulltextItemCount')
select count(1) from TestFTS
----------
872
(1 row(s) affected)
-----------
872
(1 row(s) affected)
Скрипт 12
Change Tracking при создании полнотекстового индекса, по-видимому, использует сходные механизмы отслеживания изменений, что и Change Tracking, хотя привычных для Change Tracking структур при этом на таблицу не создается. Отслеживание изменений происходит синхронно с изменениями, но полнотекстовый индекс обновляется асинхронно. Идентификаторы отслеженных изменившихся записей DocID подсовываются процессу индексирования во время его очередной актуализации. Принимаются во внимание модификации только в поля, покрытые полнотекстовым индексом, плюс type column, плюс в колонки, входящие в key index. Проверить, включен ли Change Tracking для полнотекстового индекса над таблицей, можно так:
select objectpropertyex (object_id('TestFTS'), 'TableFullTextChangeTrackingOn')
---
1
Скрипт 13
Команда
alter fulltext index on TestFTS set change_tracking manual
Скрипт 14
не отменяет отслеживание изменений, но означает, что обновление полнотекстового индекса будет происходить не в фоновом режиме, а по команде
alter fulltext index on TestFTS start update population
Скрипт 15
С точки зрения оценки трудоемкости работы во время следующего обновления полнотекстового индексирования было бы полезно знать, а сколько таких измененных записей уже накопилось. Например, индекс у нас сейчас перестраивается вручную (Скрипт 14). С момента его последнего обновления (Скрипт 15) никаких модификаций в записях не происходило, поэтому первый вызов функции дает 0. Поменяем какую-нибудь запись:
select objectpropertyex (object_id('TestFTS'), 'TableFulltextPendingChanges')
update dbo.TestFTS set Content = cast('aaa' as varbinary(max)) where FullName = '\\192.168.0.1\c$\Trainings\Sqlclub\rsdn.txt'
select objectpropertyex (object_id('TestFTS'), 'TableFulltextPendingChanges')
-------------------
0
(1 row(s) affected)
(1 row(s) affected)
-------------------
1
(1 row(s) affected)
Скрипт 16
Если теперь сделать Скрипт 15, то снова станет 0. В отличие от Change Tracking и CDC реальное изменение значения поля не проверяется. Можно обновить на предыдущее значение, она отметится. Повторные модификации этой записи на что бы то ни было не учитываются, потому что ее уже и так записали в обновление индекса.
Логичным продолжением будет посмотреть, не только сколько всего обновлений накопилось, но и в каких именно записях. Увы, это опять же не Change Tracking и не CDC Один мужик здесь - http://www.eggheadcafe.com/conversation.aspx?messageid=34200947&threadid=34200947 - озадачился этим вопросом, но конкретики так и не добился.
Можно отключить Change Tracking. Тогда об изменившихся записях ей не узнать никак, и остается полная перепопуляция индекса, когда все строки таблицы по-новой перебираются и все поля в составе полнотекстового индекса по-новой лопатятся. Хотя eсли в таблице есть поле типа timestamp (rowversion), она сможет использовать его, чтобы определить записи, изменившиеся с момента последнего обновления полнотекстового индекса. Тогда нужно написать так:
alter fulltext index on TestFTS set change_tracking off
select objectpropertyex (object_id('TestFTS'), 'TableFullTextChangeTrackingOn')
alter fulltext index on TestFTS start incremental population
Скрипт 17
Но это только при наличии в таблице поля timestamp. Если нет, из деликатности ругаться не станет, а будет все равно делать по-своему полную переиндексацию, т.е. фактически
alter fulltext index on TestFTS start full population
Скрипт 18
Запустим Скрипт 18 и дополнительно к нему сразу выполним
select * from sys.dm_fts_index_population p join sys.dm_fts_population_ranges r on p.memory_address = r.parent_memory_address
where p.database_id = db_id() and p.table_id = object_id('TestFTS')
select * from sys.dm_fts_active_catalogs where database_id = db_id()
select r.session_id, r.request_id, r.start_time, r.status, r.command, db_name(r.database_id), r.wait_type, r.wait_time,
r.cpu_time, r.scheduler_id, r.reads, r.writes, r.lock_timeout, w.context_switch_count, w.pending_io_count, w.state
from sys.dm_exec_requests r join sys.dm_os_workers w on r.task_address = w.task_address order by r.start_time, r.command
select * from sys.dm_fts_fdhosts
Скрипт 19
Как говорилось выше (http://blogs.msdn.com/alexejs/archive/2009/06/13/9745778.aspx), полнотекстовый поиск в SQL Server 2008 стал наполовину интегрированный, потому что ту его часть, что отвечала за компиляцию, оптимизацию и выполнение полнотекстовых запросов, короче, full-text engine, засунули в SQL Server. Фильтры, ворд брейкеры и стеммеры остались в fdhost, потому что их при желании может написать любой, и тащить это все in-process с SQL Server стремно. iFTS любит память практически так же сильно, как OLAP, (а кто, скажите, ее не любит) и отжирает при всяком удобном случае, как поросенок. Max Server Memory нужно выставлять с учетом того, что fdhost живет вне sqlservr.exe, а он тоже кушать хочет. Оценки необходимой полнотексту памяти - см. BOL, "Настройка и оптимизация производительности полнотекстовых индексов". При построении полнотекстового индекса SQL Server'у нужно считать у себя подлежащие индексации страницы и отдать их в fdhost.exe, чтобы тот их разбил по словам, словоформам и пр. Этим занимается товарищ FT GATHERER, который мы наблюдаем на Рис.4 в третьем резалтсете. Нижний резалтсет говорит, что fdhost.exe выполняет в настоящий момент один батч (распарсинг по словам для популяции индекса) о двух потоках. Из-за того, что потоков 2, в верхнем резалтсете мы наблюдаем процесс популяции в параллели по двум диапазонам. Fdhost здесь никакой магии от себя не привносит: если iFilter умеет выполняться многопоточно, он будет выполняться многопоточно. И тут вдруг бац, в следующей записи лежит файл такого типа, для которого имеется только однопоточный iFilter. Ничего фатального, запустится еще один экземпляр fdhosts.exe, который будет выполняться однопоточно. Батч можно посмотреть в sys.dm_fts_outstanding_batches. Как написано в BOL, memregion_memory_address - это адрес общей памяти для fdhost.exe, следовательно, тот, у кого он не нулевой, и есть товарищ, на которого ссылается sys.dm_fts_fdhosts (batch_count = 1).
|
database_id |
catalog_id |
table_id |
batch_id |
memory_address |
crawl_memory_address |
memregion_memory_address |
retry_hints_description |
doc_failed |
batch_timestamp |
|
11 |
5 |
1237579447 |
76 |
0x0519BEF8 |
0x0B262750 |
0x00000000 |
NO RETRY |
0 |
0x0000000000001BED |
|
11 |
5 |
1237579447 |
77 |
0x0B263D48 |
0x0B2630B8 |
0x0BF226F0 |
NO RETRY |
0 |
0x0000000000001BED |
Рис.4
Fdhost можно видеть в обычном диспетчере задач. Вот он с ProcessID = 3936, как указывает sys.dm_fts_fdhosts (см. Рис.5).
Рис.5
При полной популяции индекса мастер-слияние фрагментов (см. Скрипт 11) происходит по умолчанию, поэтому на Рис.2 в этом случае мы увидим только один фрагмент. SQL Server сообщает о прогрессе построения полнотекстового индекса в логах полнотекста. Логи полнотекста лежат там же, где Error Log'и (см., напр., пост "Как определить дефолтную локацию для файлов БД") и называются SQLFT<ля-ля-ля>.LOG.(N). Их полезно читать, чтобы своевременно отлавливать траблы. Например, сообщение
2009-08-21 18:00:56.95 spid26s Warning: No appropriate filter was found during full-text index population for table or indexed view '[TestFS].[dbo].[TestFTS]' (table or indexed view ID '1237579447', database ID '11'), full-text key value '/6/6/2/1/4/'. Some columns of the row were not indexed.
означает, что либо я неправильно указал ей колонку с типом файла, либо фильтр для этого типа не установлен. Мы поговорим об этом в следующем посте.
В практической задаче имелась разновидность таблицы TestFTS (не файлстримовская, т.е. varbinary(max) лежало в базе). Кроме файлового контента, в полнотекстовый индекс были включены еще два поля nvarchar (длиной 1000 и 255). Полнотекстовый контент менялся непрерывно. В таблице находилось примерно 25 млн.записей. Размер таблицы ~40 гиг. Процессор Intel x64, двухведерный, 2.20 GHz. Full population занимает ~5 часов. Полнотекстовый индекс был вынесен на отдельную файл-группу, но отдельного диска, чтобы положить ее туда, не было. Полнотекстовый индекс был сконфигурирован подобно Скрипту 2. Чтобы полная популяция не начиналась сразу после команды создания полнотекстового индекса, следует написать CREATE FULLTEXT INDEX ON ... WITH CHANGE_TRACKING OFF, NO POPULATION и выполнить Скрипт 18, когда удобно. Если запустить популяцию, когда этот индекс уже популируется, будет выдано предупреждение, и команда аннулируется. Старая популяция продолжается своим чередом.
При перепопуляции можно продолжать выполнять полнотекстовые запросы, в том числе, при перестроении полнотекстового каталога:
alter fulltext catalog Cat1 rebuild with accent_sensitivity = off
Скрипт 19
Перестроение полнотекстового каталога дропает старый индекс и создает новый, который начинает популироваться. Запросы по нему могут идти, когда индекс находится в стадии популяции. Понятно, что когда в нем данных толком нет, они ничего не находят, зато выполняются быстро, потому что индекс еще маленький J.
Полную популяцию можно приостанавливать и возобновлять:
alter fulltext index on TestFTS pause population
alter fulltext index on TestFTS resume population
Скрипт 20
Если популяции в этот момент не происходило, команды игнорируются. Состояние популяции можно узнать при помощи
select objectpropertyex(object_id('TestFTS'), 'TableFulltextPopulateStatus')
Скрипт 21
Где 0 = Бездействует, 1 = Производится полное заполнение, 2 = Производится добавочное заполнение, 3 = Выполняется распространение отслеженных изменений, 4 = Выполняется индексирование фонового обновления (например автоматическое отслеживание изменений), 5 = Полнотекстовое индексирование приостановлено или не хватает ресурсов на его выполнение. Это я скопипастил из BOL - http://msdn.microsoft.com/ru-ru/library/ms188390.aspx. Там же можно найти еще полезные проявления функции objectpropertyex на тему полнотекстовой индексации. Все они начинаются со слова TableFulltext во втором аргументе.
При включенном Change Tracking, неважно, ручном или автоматическом, сказать NO POPULATION нельзя. Можно включить Change Tracking после начальной популяции полнотекстового индекса (Скрипт 18). При интенсивной работе с таблицей стали возникать тормоза при выполнении пользовательских запросов, т.е. они с Change Tracking стали мешаться друг другу. В sys.dm_os_wait_stats накручивался FULLTEXT GATHERER, т.е. полнотекстовая индексация то и дело лочила данные, не давая до них доступиться отсальным желающим. Change Tracking был переведен в ручной режим (Скрипт 14). Обновление индекса (Скрипт 15) было поставлено в джобу с периодичностью раз в 20 мин.
|
-
В предыдущем посте мы разобрали общие теоретические положения устройства полнотекстового поиска внутри SQL Server, который теперь интегрирован внутрь SQL Server и по этой причине называется интегрированный полнотекстовый поиск - iFTS. В этом посте мы будем закреплять их на практике. Во-первых, прежде, чем полнотекстовый поиск использовать, надо, чтобы он был. Проверить, установлен ли полнотекстовый поиск на данном экземпляре SQL Server, можно так:
select fulltextserviceproperty('IsFulltextInstalled')
-----------
1
Скрипт 1
Полнотекстовый поиск ставится в общем сетапе SQL Server, нужно только не забыть отметить соответствующую фичу, когда ставите галки против всяких репликаций, клиентских тулзов и букс онлайн. Фича, как нетрудно догадаться, называется Full-text search. Если не отметили ее при установке, запустите снова сетап и скажите кое-что добавить к имеющейся установке.
Рис.1
Рис.2
Рис.3
Во-вторых, далее при работе с полнотекстом в 7.0 - 2005 надо было заенейблить БД для полнотекстового поиска при помощи процедуры sp_fulltext_database @action= 'enable'. В 2008 этот пункт делать не надо. Он, подразумевается, уже выполнен автоматически за нас. Каждая пользовательская БД в 2008 изначально заенейблена для полнотекстового поиска и специально енейблить ее не требуется. Процедура sp_fulltext_database поддерживается по соображениям совместимости, но в BOL предупреждается, что this feature will be removed in a future version of Microsoft SQL Server. Полнотекстовый поиск по системным базам master, model, tempdb поддерживался в 2000-м, отменился в 2005-м.
В-третьих, в базе нужен материал, по которому будут гоняться полнотекстовые запросы. Для демонстрационных целей я буду использовать базу TestFS, над которой мы тренировались, когда разбирали filestream. Скрипт ее создания можно посмотреть в начале поста "Введение в FILESTREAM". Единственно, я добавлю к ней еще одну файл-группу для иллюстрации помещения полнотекстовых индексов.
use tempdb
if exists(select 1 from sys.databases where name = 'TestFS') begin
alter database TestFS set single_user with rollback immediate
drop database TestFS
end
create database TestFS on
primary (name = TestFS_data, filename = 'c:\Temp\TestFS_data.mdf'),
filegroup FS contains filestream
(name = TestFS_media, filename = 'c:\Temp\TestFS_media'),
filegroup FTS (name = TestFS_fts, filename = 'c:\Temp\TestFS_fts.ndf')
log on (name = TestFS_log, filename = 'c:\Temp\TestFS_log.ldf')
use TestFS
Скрипт 2
Грузить файлы в таблицу я буду со стороны сервера при помощи хранимой процедуры LoadDir, написанной в посте “Как переложить файловую папку в базу”. Чтобы iFTS знал, какой фильтр к какому файлу применять, нужно расширение файла. Расширение должно храниться в отдельной колонке, которая указывается при создании полнотекстового индекса. Получение расширения файла из его полного имени средствами T-SQL - достаточно муторная и медленная процедура, поэтому я добавил еще один метод в CLRный код. Указать явно детерминированный характер этой функции требуется для последующей персистенции поля. Атрибут SqlFacet ограничивает длину возвращаемого nvarchar - http://bytes.com/groups/net-c/444789-attribute-return-value-how. Эта функция будет использоваться для вычисляемого поля, содержащего тип файла. Если длина поля превышает 260 символов, оператор CREATE FULLTEXT INDEX ... TYPE COLUMN ... отказывается ее воспринимать. В данном случае SqlFacet – это выпендреж, т.к. длина результата будет значиться так, как мы ее зададим при деплойменте: CREATE FUNCTION ... RETURNS NVARCHAR(260) AS EXTERNAL NAME ...
/// <summary>
/// Функция возвращает расширение файла
/// </summary>
/// <param name="fullName">Полное имя файла</param>
/// <returns>Расширение</returns>
[SqlFunction(IsDeterministic = true)]
[return: SqlFacet(MaxSize = 260)]
public static SqlString GetFileExtension(SqlString fullName)
{
return Path.GetExtension(fullName.Value);
}
Скрипт 3
Заведение сборки на стороне SQL Server и создание необходимых модулей:
alter database TestFS set trustworthy on
if object_id('Dir', 'FT') is not null drop function Dir
if object_id('GetSqlErrLogPath', 'FS') is not null drop function GetSqlErrLogPath
if object_id('GetFileExtension', 'FS') is not null drop function GetFileExtension
if object_id('LoadDir', 'PC') is not null drop proc LoadDir
if exists(select 1 from sys.assemblies where name = 'MyAssembly') drop assembly MyAssembly
go
create assembly MyAssembly from 'C:\Temp\LoadFolderToSQL\bin\Debug\ClassLibrary1.dll' with permission_set = unsafe
go
create proc LoadDir @folder nvarchar(255), @shallowTraversal bit, @tblName sysname as external name MyAssembly.FileSystem.LoadDirWithFileContent
go
create function GetFileExtension(@fileName nvarchar(500)) returns nvarchar(260) as external name MyAssembly.FileSystem.GetFileExtension
Скрипт 4
Под загрузку текстовой информации предназначена таблица TestFTS, куда я, не мудрствуя лукаво, положу контент своих постов с данного блога и других форумов. Можно было брать их напрямую с веба; для демонстрации разных IFilter я возьму их локальные копии в виде вордовых документов, txt и пр. у себя из файловой системы.
Если бы текст находился в виде текста типа (n)varchar, можно было брать и применять к нему полнотекстовые операции. Но в таблице будут лежать содержания файлов различных форматов: .docx, .pdf, ... в колонке типа varbinary(max). Чтобы получить из этой бинарщины текст, нужен модуль iFTS под названием фильтр. Фильтры бывают разные в зависимости от формата файла. Чтобы iFTS знал, какой фильтр применять к данной varbinary(max)-ячейке, рядом нужна ячейка с указанием типа файла. Отсюда колонка type.
Значение по умолчанию для файлстримовского поля предназначено на случай, если появится желание его грузить при помощи SqlFileStream – см. Пост "Частичное обновление FILESTREAM", Скрипт 1. Как мы с вами знаем из введения, вставка NULLового значения в файлстрим не приводит к образованию файла в папке, соответствующей данной колонке, следовательно, .PathName() от NULLовой ячейки будет NULL, следовательно, new SqlFileStream(filePath, txCtx, FileAccess.ReadWrite) от нее не создастся.
if object_id('TestFTS', 'U') is not null drop table TestFTS
create table TestFTS(ID HierarchyID, FullName nvarchar(1000), size bigint, DateModified datetime2, DateCreated datetime2, LastAccessed datetime2,
Properties xml, isDir bit, [guid] uniqueidentifier default newid() unique rowguidcol not null, type as dbo.GetFileExtension(FullName) persisted,
Content varbinary(max) filestream default (0x0))
Скрипт 5
Загружаем в таблицу содержимое интересующей файловой папки при помощи процедуры LoadDir (Скрипт 4). Прогресс работы процедуры LoadDir, как мы ее в свое время написали, можно наблюдать в создаваемом ею файле SqlFSLoader.log, который находится там же, где и все логи SQL Server. Разброс времени при загрузке 140 меговой папки из 680 файлов занял 9.5 - 13 мин. Длительность зависит не только от объема, но и от количества файлов. Так, загрузка 700-метрового каталога из 40 файлов занимала 5.5 - 8 мин. Вообще, загрузка контента из файловой системы в блоб с атрибутом файлстрим происходит быстрее, чем в обычный блоб.
exec LoadDir 'c:\Demo', 0, 'TestFTS'
Скрипт 6
Для создания полнотекстового индекса потребуется уникальный индекс, который будет идентифицировать строки таблицы. В принципе, один уникальный индекс уже есть благодаря ограничению unique на колонку [guid]. Но оператору создания полнотекстового индекса он не нравится.
Msg 7653, Level 16, State 1, Line 1
'UQ__TestFTS__497F6CB5182C9B23' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.
Мне не жалко, я могу еще создать. Колонка, по которой он создается, должна быть NOT NULL, иначе оператор create fulltext index не воспримет его в качестве key index, отсюда, предварительно нужно сказать ... alter column ID ... not null.
if exists (select 1 from sys.indexes where name = 'ixId' and object_id = object_id('dbo. TestFTS')) drop index TestFTS.ixId
alter table TestFTS alter column ID HierarchyID not null
create unique index ixId on TestFTS(ID)
Скрипт 7
Персистинг колонки Type также делается в угоду оператору создания полнотекстового индекса, чтобы тот не орал:
Msg 9929, Level 16, State 1, Line 1
Computed column 'Type' cannot be used as full-text type column for image or varbinary(MAX) column. This computed column must be deterministic, precise or persisted, with a size less or equal than 260 characters.
Размер файлстрима не отражается в общем размере базы, т.к. файл-стримовскую файл-группу она в своей бухгалтерии не учитывает. Можно видеть, что размер базы TestFS составляет, по мнению стандартных отчетов SSMS (см. пост «Автоматическое выполнение отчетов»), 15 мегабайт:
Рис.4
тогда как основная масса сосредоточена в файлстримовской папке:
Рис.5
|
-
Предыдущие посты по этой теме: · Change Tracking · Репликация таблиц средствами Change Tracking · Краткое введение в сервис-брокер · Репликация средствами Change Tracking. Небольшое упражнение на FOR XML PATH и XQuery. В посте Репликация таблиц средствами Change Tracking мы рассмотрели вариант синхронизации таблиц tbl_1 и tbl_2 при помощи появившегося в SQL Server 2008 механизма отслеживания изменений Change Tracking. В данном посте мы разовьем этот сценарий на случай, когда таблицы находятся на разных серверах. В качестве транспорта будет использоваться появившийся в SQL Server 2005 механизм асинхронного взаимодействия сервис-брокер. Таблица tbl_1 будет находиться на сервере Маша. Change Tracking будет отслеживать происходящие над ней изменения (delete, insert, update). Эти изменения будут превращаться в XML-сообщение и доставляться сервис-брокером на сервер Дубровский, где XML превратится обратно в DML-команды, которые будут применены к таблице tbl_2 на этом сервере. Вместо сервис-брокера можно задействовать свой транспорт по доставке XML, тогда этот сценарий может применяться, когда Маша и Дубровский оба SQL Expressы. Сервис-брокер входит в состав SQL Express, однако два SQL Expressа через него общаться не могут. Необходимо, чтобы хотя бы одна сторона имела взрослую редакцию. В упрощенном примере обе таблицы будут находиться на одном сервере в одной базе ChangeTracking_Test. use tempdb if exists(select 1 from sys.databases where name = 'ChangeTracking_Test') begin alter database ChangeTracking_Test set single_user with rollback immediate drop database ChangeTracking_Test end create database ChangeTracking_Test use ChangeTracking_Test Скрипт 1 В базе со стороны Маши будут работать два процесса: имитация пользовательской активности, вносящая в tbl_1 случайные изменения, и периодическая синхронизация. Чтобы заморозить tbl_1 на момент синхронизации, используется уровень изоляции snapshot. Его нужно включить на базе со стороны Маши: alter database ChangeTracking_Test set single_user with rollback immediate alter database ChangeTracking_Test set read_committed_snapshot on alter database ChangeTracking_Test set multi_user alter database ChangeTracking_Test set allow_snapshot_isolation on Скрипт 2 На базе со стороны Маши должен быть поднят Change Tracking: if not exists (select 1 from sys.change_tracking_databases where database_id = db_id('ChangeTracking_Test')) alter database ChangeTracking_Test set change_tracking = on (change_retention = 10 minutes, auto_cleanup = on) Скрипт 3 Со стороны Маши и со стороны Дубровского должен быть задействован сервис-брокер. Практикой хорошего тона при использовании сервис-брокера является иметь мастер-ключ на базе, чтобы потом не вылезла ошибка Краткое введение в сервис-брокер\Скрипт 3. create master key encryption by password = 'AbraCadabra' if (select is_broker_enabled from sys.databases where name = 'ChangeTracking_Test') = 0 alter database ChangeTracking_Test set enable_broker with rollback immediate Скрипт 4 На стороне Маши имеется таблица tbl_1 if object_id('dbo.tbl_1', 'U') is not null drop table tbl_1 create table tbl_1 ( id1 int identity, id2 int default (datepart(ns, sysdatetime()) / 100), fld1 varchar(10), fld2 sql_variant, primary key(id1, id2) ) Скрипт 5 с которой будет синхронизироваться таблица tbl_2 на стороне Дубровского: if object_id('dbo.tbl_2', 'U') is not null drop table tbl_2 create table tbl_2 ( id1 int, id2 int, fld1 varchar(10), fld2 sql_variant, primary key(id1, id2) ) Скрипт 6 Включаем отслеживание изменений по tbl_1: if not exists (select 1 from sys.change_tracking_tables where object_id = object_id('tbl_1')) alter table tbl_1 enable change_tracking Скрипт 7 На стороне Маши создаем вспомогательную таблицу для хранения предыдущей версии синхронизации и журналирования результатов use ChangeTracking_Test if object_id('dbo.Sync_Log', 'U') is not null drop table dbo.Sync_Log create table dbo.Sync_Log (dt datetime default sysdatetime(), version bigint default change_tracking_current_version(), source sysname, destination sysname, status nvarchar(200), deleted bigint, inserted bigint, updated bigint) insert dbo.Sync_Log (version, source, destination) values (change_tracking_min_valid_version(object_id('dbo.tbl_1')), 'tbl_1', 'tbl_2') Скрипт 8 Работающий на периодической основе со стороны Маши скрипт синхронизации будет отлавливать при помощи функции ChangeTable изменения в tbl_1 и превращать их в XML вида Небольшое упражнение на FOR XML PATH и XQuery\Скрипт 2. Я для него даже создал схему: if exists (select 1 from sys.xml_schema_collections where name = 'CT_Changes_tbl_1_xsd') drop xml schema collection CT_Changes_tbl_1_xsd create xml schema collection CT_Changes_tbl_1_xsd as N'<?xml version="1.0" encoding="utf-16"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="CT_Changes"> <xs:complexType> <xs:sequence> <xs:element ref="Record" maxOccurs="unbounded"/> </xs:sequence> <xs:attribute name="table_name" type="xs:string" use="required" /> <xs:attribute name="version_since" type="xs:long" use="required" /> <xs:attribute name="version_upto" type="xs:long" use="required" /> </xs:complexType> </xs:element> <xs:element name="Record"> <xs:complexType> <xs:sequence> <xs:element ref="PK" minOccurs ="1" maxOccurs ="1"/> <xs:element name="fld1" type="xs:string" minOccurs="0" /> <xs:element name="fld2" type="xs:string" minOccurs="0" /> </xs:sequence> <xs:attribute name="operation" type="xs:string" use="required" /> <xs:attribute name="change_no" type="xs:long" use="required" /> <xs:attribute name="commit_time" type="xs:dateTime" use="required" /> </xs:complexType> </xs:element> <xs:element name="PK"> <xs:complexType> <xs:sequence> <xs:element name="id1" type="xs:int" /> <xs:element name="id2" type="xs:int" /> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>' Скрипт 9 Конфигурируем сервис-брокер, создавая тип сообщения для передачи изменений (он будет валидироваться схемой CT_Changes_tbl_1_xsd), контракт, по которому будут передаваться сообщения этого типа (имена чувствительны к регистру невзирая на коллацию), очереди для сообщений, сервисы как конечные точки и открываем диалог, в рамках которого сервис Маша будет передавать сервису Дубровский сообщения по только что определенному контракту, короче, Краткое введение в сервис-брокер\Скрипты 4 - 9. if exists(select 1 from sys.services where name = 'Masha') drop service Masha if exists(select 1 from sys.services where name = 'Dubrovsky') drop service Dubrovsky if exists(select 1 from sys.service_contracts where name = 'CT_Changes_tbl_1_Contract') drop contract CT_Changes_tbl_1_Contract if exists(select 1 from sys.service_message_types where name = 'CT_Changes_tbl_1_MessageType') drop message type CT_Changes_tbl_1_MessageType create message type CT_Changes_tbl_1_MessageType validation = valid_xml with schema collection CT_Changes_tbl_1_xsd create contract CT_Changes_tbl_1_Contract (CT_Changes_tbl_1_MessageType sent by initiator) if exists(select 1 from sys.service_queues where name = 'QueueMashi') drop queue QueueMashi create queue QueueMashi if exists(select 1 from sys.service_queues where name = 'QueueDubrovskogo') drop queue QueueDubrovskogo create queue QueueDubrovskogo create service Masha on queue QueueMashi (CT_Changes_tbl_1_Contract) create service Dubrovsky on queue QueueDubrovskogo (CT_Changes_tbl_1_Contract) declare @ch uniqueidentifier begin dialog conversation @ch from service Masha to service 'Dubrovsky' on contract CT_Changes_tbl_1_Contract Скрипт 10 С отдельного коннекта в SSMS на стороне Маши запускаем имитацию пользовательской активности Репликация таблиц средствами Change Tracking\Скрипт 5. Модифицируем скрипт синхронизации Репликация таблиц средствами Change Tracking\Скрипт 7. Скрипт состоит из 3-х частей, разделенных комментарными линиями. Первая часть осталась без изменений. Вторая изменена с тем, чтобы он не напрямую применял изменения к tbl_2, а превращал их в XML (Небольшое упражнение на FOR XML PATH и XQuery\Скрипт 3) и кидал в очередь брокеру. Третья, как и первая, осталась без изменений. Она опциональна. В ней я пользуюсь тем, что таблицы на самом деле находятся в одной базе, и сравниваю их, чтобы убедиться, что синхронизация работает. while 1 = 1 begin ----------------------------------------------------------------------------------------------------- waitfor delay '00:01:00' set transaction isolation level snapshot begin tran declare @lastVersion bigint --здесь будет храниться последняя версия, которой синхронизирована tbl_2 declare @curVersion table (curVersion bigint); delete from @curVersion --здесь будет храниться текущая версия изменений select @lastVersion = isnull(max(version), 0) from dbo.Sync_Log where source = 'tbl_1' and destination = 'tbl_2' --берем последнюю версию из нашего журнала insert dbo.Sync_Log (source, destination) output inserted.Version into @curVersion values ('tbl_1', 'tbl_2') --отмечаем в журнале текущий факт синхронизации --Если autocleanup успел почистить изменения tbl_1, которые еще не были доставлены на tbl_2, поднимаем аварийную ситуацию. if @lastVersion < change_tracking_min_valid_version(object_id('dbo.tbl_1')) begin declare @msg nvarchar(200) = 'Часть изменений потеряна! Требуется ручная синхронизация!' update dbo.Sync_Log set status = @msg where version = (select curVersion from @curVersion) --фиксируем ее в журнале raiserror (@msg, 21, 1) with log --и вызываем строгую ошибку, которая прерывает выполнение скрипта end --Если за период с прошлой синхронизации ничего нового не произошло, можно не париться. if @lastVersion = change_tracking_current_version() goto konec ----------------------------------------------------------------------------------------------------- --Превращаем результат changetable в xml и передаем его в очередь. declare @x xml = ( select 'tbl_1' as [@table_name], @lastVersion as [@version_since], change_tracking_current_version() as [@version_upto], ( select ct.SYS_CHANGE_OPERATION as [@operation], ct.SYS_CHANGE_VERSION as [@change_no], sct.commit_time as [@commit_time], ct.id1 as [PK/id1], ct.id2 as [PK/id2], t.fld1 as fld1, t.fld2 as fld2 from changetable(changes tbl_1, @lastVersion) ct join sys.dm_tran_commit_table sct on ct.sys_change_version = sct.commit_ts left join tbl_1 t on t.id1 = ct.id1 and t.id2 = ct.id2 for xml path('Record'), type ) for xml path('CT_Changes') ) declare @ch uniqueidentifier = ( select top 1 ce.conversation_handle from sys.conversation_endpoints ce join sys.services s on ce.service_id = s.service_id join sys.service_queues sq on s.service_queue_id = sq.object_id where s.name = 'Masha' and ce.far_service = 'Dubrovsky' and ce.is_initiator = 1 and ce.state <> 'ER' ) --всегда открыт только один диалог, инициатором которого является Маша ;send on conversation @ch message type CT_Changes_tbl_1_MessageType (@x) --в него и зафигачиваем этот XML ----------------------------------------------------------------------------------------------------- konec: --Сравнение копии с оригиналом. declare @n1 bigint, @n2 bigint select @n1 = count(1) from (select * from tbl_1 except select * from tbl_2) t --сколько записей в оригинале не хватает в копии select @n2 = count(1) from (select * from tbl_2 except select * from tbl_1) t --и наоборот update dbo.Sync_Log set status = case when @n1 <> 0 or @n2 <> 0 then 'Обнаружено ' + cast(@n1 as varchar(20)) + ' записей в tbl_1, не совпадающих с tbl_2, и ' + cast(@n2 as varchar(20)) + ' записей в tbl_2, не совпадающих с tbl_1.' else 'OK' end where version = (select curVersion from @curVersion) --отражаем несовпадения в журнале, а если их нет, то ОК commit set transaction isolation level read committed end Скрипт 11 Запускаем этот скрипт с нового коннекта в SSMS. Каждую минуту в очередь Дубровского будет капать сообщение от Маши. Можно их посмотреть select *, cast(message_body as xml) from QueueDubrovskogo и убедиться, что в message_body приходит нечто по образу Небольшое упражнение на FOR XML PATH и XQuery\Скрипт 2. На несовпадения между tbl_1 и tbl_2, о которых сообщается в таблице Sync_Log, пока не обращаем внимания. Мы убедились, что Change Tracking исправно отслеживает изменения в tbl_1, а сервис-брокер исправно доставляет их на сервер с tbl_2. Теперь на Дубровском напишем процедуру очереди, которая будет разгребать валящиеся сообщения, превращать XML обратно в операторы DML и применять их к tbl_2, чтобы синхронизировать ее с tbl_1. Джойним tbl_2 по полям РК c записями, полученными из XML. Те, у которых operation="D", удаляются, "I" - вставляются, "U" - обновляются. Апдейты dbo.Sync_Log, которые идут после каждой операции, предназначены для контрольных целей. По-хорошему, Sync_Log нужно было разделить, сделав журнал на стороне отправки и журнал на стороне приема. Я не стал этим заморачиваться, беззастенчиво воспользовавшись тем, что в данном примере стороны физически совпадают. Процедура очереди будет написана на основе Краткое введение в сервис-брокер\Скрипты 21-22 и Небольшое упражнение на FOR XML PATH и XQuery\Скрипты 4-5. if object_id('ProcessSyncMessages', 'P') is not null drop proc ProcessSyncMessages go --Процедура производит синхронизацию tbl_2 с tbl_1 create proc ProcessSyncMessages as begin declare @ch uniqueidentifier, @msgtype sysname, @body varbinary(max) --Читаем из очереди сообщение с изменениями while 1 = 1 begin waitfor (receive top(1) @ch = conversation_handle, @msgtype = message_type_name, @body = message_body from QueueDubrovskogo) if @@rowcount = 0 return if @msgtype <> 'CT_Changes_tbl_1_MessageType' return select @@rowcount, @msgtype declare @x xml = @body declare @curVersion bigint = (select x.value('@version_upto[1]', 'bigint') from @x.nodes('CT_Changes') d(x)) --Удаляем удаленные записи delete t from tbl_2 t join (select x.value('(PK/id1)[1]', 'int') id1, x.value('(PK/id2)[1]', 'int') id2 from @x.nodes('CT_Changes/Record[@operation="D"]') d(x)) ct on t.id1 = ct.id1 and t.id2 = ct.id2 update dbo.Sync_Log set deleted = @@rowcount where version = @curVersion --их количество вносим в журнал --Добавляем новые insert tbl_2 select x.value('(PK/id1)[1]', 'int') id1, x.value('(PK/id2)[1]', 'int') id2, x.value('fld1[1]', 'nvarchar(10)') fld1, x.value('fld2[1]', 'nvarchar(10)') fld2 from @x.nodes('CT_Changes/Record[@operation="I"]') d(x) update dbo.Sync_Log set inserted = @@rowcount where version = @curVersion --их количество вносим в журнал --Обновляем модифицированные ;with cte as (select x.value('(PK/id1)[1]', 'int') id1, x.value('(PK/id2)[1]', 'int') id2, x.value('fld1[1]', 'nvarchar(10)') fld1, x.value('fld2[1]', 'nvarchar(10)') fld2 from @x.nodes('CT_Changes/Record[@operation="U"]') d(x)) update t2 set t2.fld1 = cte.fld1, t2.fld2 = cte.fld2 from tbl_2 t2 join cte on t2.id1 = cte.id1 and t2.id2 = cte.id2 update dbo.Sync_Log set updated = @@rowcount where version = @curVersion --их количество вносим в журнал end end go alter queue QueueDubrovskogo with activation ( status = on, procedure_name = ProcessSyncMessages, max_queue_readers = 1, execute as self ) Скрипт 12 Пускаем по-новой, выжидаем, смотрим, что получилось. 
| |
|