Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 И снова про автономные транзакции с помощью CLR  [new]
Stibrus
Member

Откуда: msk.ru
Сообщений: 9
Добрый день! Возникла потребность логгирования процесса обработки данных на SQL Server 2005 в автономной транзакции. Интернет предлагает 3 стандартных обходных маневра - с loopback linked server'ом, табличной переменной и написанием хранимой процедуры на CLR. Т.к. первые 2 варианта мне по тем или иным причинам не нравятся (покупной продукт, клиентское приложение - "черный ящик", требования к производительности и надежности), попробовал сделать хранимую процедуру на CLR. Забегая вперед, скажу, что несмотря на все рецепты и уверения Микрософт на МСДН, автономное соединение из CLR подхватывает "внешнюю" транзакцию!

Создаем таблицу
+

CREATE TABLE [dbo].[EventLog](
[ID] uniqueidentifier default NEWID() not null primary key,
[EventBody] varchar(max)
) ON [PRIMARY]
GO


Создаем хранимую процедуру CLR
+

public static class Logger
{
private const string cmdWriteLog =
"INSERT INTO [dbo].[EventLog] ([EventBody]) VALUES (@EventBody)";

private const string unspecified = "<UNSPECIFIED>";

[SqlFunction]
public static void LogWrite(string pEventBody)
{
try
{
using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew))
{
using (var connection = new SqlConnection("context connection=true"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = cmdWriteLog;

command.Parameters.Add("@EventBody", System.Data.SqlDbType.Text);
if (pEventBody == null || string.IsNullOrEmpty(pEventBody))
command.Parameters["@EventBody"].Value = string.Empty;
else
command.Parameters["@EventBody"].Value = pEventBody;

command.ExecuteNonQuery();
transaction.Complete();
}
} // Connection
} // Transaction scope
}
catch (Exception) { }
}
}


Прописываем нашу сборку на сервере
+

sp_configure 'clr enabled', 1
RECONFIGURE

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogWriteInternal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LogWriteInternal]


IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlServerFunctions' and is_user_defined = 1)
DROP ASSEMBLY [SqlServerFunctions]


CREATE ASSEMBLY SqlServerFunctions FROM 'c:\Test\SqlServerFunctions.dll' WITH PERMISSION_SET = SAFE

CREATE PROCEDURE [dbo].[LogWriteInternal]
(
@pEventBody nvarchar(max)
)
AS EXTERNAL NAME SqlServerFunctions.Logger.LogWrite


И, наконец, тестируемся. Если вызываем процедуру без явного объявления транзакции, сообщение пишется в лог. Если делаем явное объявление/откат транзакции, сообщение не пишется!

BEGIN TRAN

DECLARE
@pEventBody nvarchar(max)
SET @pEventBody = 'This is an event body'
EXEC [dbo].[LogWriteInternal] @pEventBody

ROLLBACK TRAN

Вопрос: то ли Микрософт обманывает, и функция CLR получает соединение, из которого происходит вызов (вместо открытия нового соединения), то ли TransactionScopeOption.RequiresNew не создает автономную транзакцию, а подключает меня к "уже имеющейся".

Кто-нибудь сумел победить это, и сумел сделать автономную транзакцию с помощью CLR? Поможите, люди добрые!
28 фев 15, 16:45    [17325899]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Stibrus,

да вроде все логично

автор
If you need to connect to the same server on which the stored procedure or function is running, use the context connection in most cases. This has benefits such as
running in the same transaction space
and not having to reauthenticate.


вот тут костыль через CLR описан, идея в том что они Context Connection используют только для получения инфы о сервере, а затем устанавливают Regular Connection на основе полученной инфы. И устанавливают Enlist = false, чтобы не участвовать в ambient транзакции. Но оно вам надо? может лучше через linked server?
28 фев 15, 17:33    [17326002]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
churupaha,

в статье поиском найти "Simulating Autonomous Transactions"... а то статья о другом трохи.
28 фев 15, 17:38    [17326011]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
Stibrus
Member

Откуда: msk.ru
Сообщений: 9
churupaha
вот тут костыль через CLR описан, идея в том что они Context Connection используют только для получения инфы о сервере, а затем устанавливают Regular Connection на основе полученной инфы. И устанавливают Enlist = false, чтобы не участвовать в ambient транзакции. Но оно вам надо? может лучше через linked server?


Благодарю! Не могу найти ссылку на МСДНе, которая сбила меня с толку, но это случилось :) Я подумаю над Вашим советом с linked server, т.к. описанный по Вашей ссылке метод, кажется, подразумевает много неприятностей (ALTER DATABASE .... SET TRUSTWORTHY ON, установку сборки в режиме UNSAFE).
28 фев 15, 20:50    [17326401]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
event notification для userconfigurable_* + sp_trace_generateevent
15757563
28 фев 15, 21:31    [17326480]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Stibrus
(ALTER DATABASE .... SET TRUSTWORTHY ON, установку сборки в режиме UNSAFE).


Я ни в коем случае вас не агитирую в конкретно этом случае использовать CLR, наоборот.

Просто прокомментирую выделенное по поводу развертывания UNSAFE сборки. Правильно так:

1) подписуете сборку ключем
2) на стороне sql server

use master;
go

create assymmetric key __my_clr_assembly_key from executable file = N'c:\temp\my_clr_assembly.dll'
go

create login __my_clr_assembly_login from asymmetric key __my_clr_assembly_key;
go


grant unsafe assembly to __my_clr_assembly_login;
go

/*
     если у вас external access assembly

     grant external access to __my_clr_assembly_login;
*/

use db1
go

create assembly my_clr_assembly from N'c:\temp\my_clr_assembly.dll' with permission_set = unsafe /* external_access */
28 фев 15, 21:57    [17326545]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+

/* !!!!! */ alter database db1 set trustworthy OFF /* !!!!! */ 
28 фев 15, 21:59    [17326550]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
Stibrus
Member

Откуда: msk.ru
Сообщений: 9
invm
event notification для userconfigurable_* + sp_trace_generateevent
15757563




invm
event notification для userconfigurable_* + sp_trace_generateevent
15757563


Благодарю, но в моем случае загвоздка в том, что управляющее приложение - коробочный продукт, я не могу "протолкнуть информацию наверх", чтобы там ее обработать.

churupaha
Я ни в коем случае вас не агитирую в конкретно этом случае использовать CLR, наоборот.


Еще раз спасибо, всё заработало "на 5 баллов". Насчет выбора между loopback и CLR - поставлю вопрос перед DBA на работе, посмотрим, что ответят. Есть один момент, который вызывает у меня сомнение: как себя поведет linked server loopback в распределенной транзакции, если, например, будет вовлечен Оракл?
1 мар 15, 00:37    [17327003]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
Stibrus
Member

Откуда: msk.ru
Сообщений: 9
Забыл сказать: у меня всё заработало с UNSAFE, хотя кое-где почему-то пишут, что понадобится EXTERNAL_ACCESS
1 мар 15, 00:39    [17327011]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Stibrus
Есть один момент, который вызывает у меня сомнение: как себя поведет linked server loopback в распределенной транзакции, если, например, будет вовлечен Оракл?


remote proc transaction promotion

msdn
If this option is set to FALSE (or OFF), a local transaction will not be promoted to a distributed transaction while calling a remote procedure call on a linked server.


Stibrus
Забыл сказать: у меня всё заработало с UNSAFE, хотя кое-где почему-то пишут, что понадобится EXTERNAL_ACCESS


Заработает и это. Просто сделаете:

use master;
go

...

grant external access assembly to __my_clr_assembly_login;
go

use db1
go

create assembly my_clr_assembly from N'c:\temp\my_clr_assembly.dll' with permission_set = external_access
1 мар 15, 09:53    [17327357]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Stibrus
я не могу "протолкнуть информацию наверх", чтобы там ее обработать.
Не нужно ничего "проталкивать". Все работает на уровне сиквела.
1 мар 15, 10:19    [17327389]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
Stibrus
Member

Откуда: msk.ru
Сообщений: 9
invm
Stibrus
я не могу "протолкнуть информацию наверх", чтобы там ее обработать.
Не нужно ничего "проталкивать". Все работает на уровне сиквела.
Если речь про Service Broker, то надо обдумать. Как-то не смотрел в его сторону.

З.Ы. Когда заводил вопрос, не нашел поиском тему по вашей ссылке, наверное, из-за названия.
1 мар 15, 10:41    [17327427]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
Stibrus
Member

Откуда: msk.ru
Сообщений: 9
invm
event notification для userconfigurable_* + sp_trace_generateevent
15757563

Объясню, чем мне не подходит sp_trace_generateevent: мне нужна информация не в реалтайме (в профайлере), а "задним числом", и притом структурированная. То, что прекрасно подходит для целей ДБА, не покроет потребностей бизнес-пользователя.

Т.е. мне нужно отделять
- Дату и время события
- Уровень события (ERROR/INFO/WARNING.....)
- Источник события (хранимая процедура)
- Инициатор события : вызвавшая рабочая станция
- Инициатор события : вызвавший пользователь
- Инициатор события : вызвавшее приложение
- По возможности: идентификатор бизнес-объекта
- По возможности: тип бизнес-объекта
- И только потом идет детальная информация о том, что случилось, в виде свободного текста.

Типовые сценарии:
- импортированный ранее из системы (А) документ (ID) при передаче в систему (B) выдал ошибку
- просят посмотреть, почему месяц назад джоб поменял атрибут документа (ID): не вносил ли кто-то изменения, которые могли "спровоцировать" джоб
- наконец, типовой сценарий: кто и когда менял документ (ID)

Доставать такие данные из нетипизированных MEMO-полей, даже если там XML - очень долго и ненадежно в плане достоверности.
1 мар 15, 11:57    [17327548]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Stibrus
Доставать такие данные из нетипизированных MEMO-полей, ..., очень долго и ненадежно в плане достоверности.


Комментарий относится только к выделенному (не к задаче).

можно быстро и удобно
1 мар 15, 12:37    [17327654]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Stibrus
Объясню, чем мне не подходит sp_trace_generateevent: мне нужна информация не в реалтайме (в профайлере), а "задним числом", и притом структурированная. То, что прекрасно подходит для целей ДБА, не покроет потребностей бизнес-пользователя.
Stibrus
Доставать такие данные из нетипизированных MEMO-полей, даже если там XML - очень долго и ненадежно в плане достоверности.
Какой реалтайм? Какой профайлер? Какие MEMO-поля? Вы вообще о чем?
Хоть пробовали читать, что такое event notification?

Вот вам болванка для дальнейших упражнений:
+
use master;

if db_id('DBAuditExample') is not null
begin
 alter database DBAuditExample set single_user with rollback immediate;
 drop database DBAuditExample;
end;

create database DBAuditExample;
alter database DBAuditExample set recovery simple;
alter database DBAuditExample set enable_broker with rollback immediate;
go

use DBAuditExample;
go

if exists(select * from sys.server_event_notifications where name = 'enSimpleAudit')
 drop event notification enSimpleAudit on server;
go

if object_id('dbo.spHandleMessages', 'P') is null
 exec('create procedure dbo.spHandleMessages as begin set nocount on; end;');
go

create queue dbo.qSimpleAudit with
 status = on,
 retention = off,
 activation (status = on, procedure_name = dbo.spHandleMessages, max_queue_readers = 5, execute as owner),
 poison_message_handling (status = on);
go

create service svcSimpleAudit
 authorization dbo
 on queue dbo.qSimpleAudit
 (
  [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
 );
go

create table dbo.SimpleAuditData
(
 ad_id int identity,
 ad_OccurredAt datetime not null,
 ad_Login sysname,
 ad_Application nvarchar(128),
 ad_Host nvarchar(128),
 ad_ShortText nvarchar(128),
 ad_LongText nvarchar(4000),
 constraint PK_SimpleAuditData primary key (ad_id)
);
go

alter procedure dbo.spHandleMessages
as
begin
 set nocount on;

 declare @handle uniqueidentifier, @message xml, @message_type sysname;

 waitfor
 (
  receive top (1)
   @handle = conversation_handle,
   @message_type = message_type_name,
   @message = message_body
  from
   dbo.qSimpleAudit
 ),
 timeout 1000;

 if @@rowcount = 0
  return 0;

 if @message_type = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
  with s as
  (
   select
    n.value('EventType[1]', 'sysname') as EventType,
    n.value('PostTime[1]', 'datetime') as PostTime,
    n.value('TextData[1]', 'sysname') as TextData,
    n.value('BinaryData[1]', 'varbinary(8000)') as BinaryData,
    n.value('DatabaseID[1]', 'int') as DatabaseID,
    n.value('DatabaseName[1]', 'sysname') as DatabaseName,
    n.value('ApplicationName[1]', 'sysname') as ApplicationName,
    n.value('HostName[1]', 'sysname') as HostName,
    n.value('SessionLoginName[1]', 'sysname') as SessionLoginName
   from
    @message.nodes('/EVENT_INSTANCE') x(n)
  )
  insert into dbo.SimpleAuditData
   (ad_OccurredAt, ad_Login, ad_Application, ad_Host, ad_ShortText, ad_LongText)
   select
    s.PostTime, s.SessionLoginName, s.ApplicationName, s.HostName, s.TextData, cast(s.BinaryData as nvarchar(4000))
   from
    s
   where
    DatabaseID = db_id();

 if @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
  end conversation @handle with cleanup;

 if @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
  end conversation @handle with cleanup;

end;
go

create event notification enSimpleAudit
on server
for USERCONFIGURABLE_0
to service 'svcSimpleAudit', 'current database';
go

Использование:
declare @b varbinary(8000) = cast(N'Long text' as varbinary(8000));
exec sp_trace_generateevent 82, N'Short text', @b;
2 мар 15, 01:07    [17329722]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
Stibrus
Member

Откуда: msk.ru
Сообщений: 9
invm,

Благодарю. Есть подозрение, что для моих целей это "из пушки по воробьям", но с темой обязательно ознакомлюсь. Пригодится если не для текущего проекта, то для чего-то еще.
2 мар 15, 22:02    [17334623]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
Создал триггер по указанной технологии. Однако, получаю странные записи.
пример триггера:

ALTER TRIGGER [dbo].[Hist]
ON [dbo].[R1]
AFTER INSERT,DELETE,UPDATE
BEGIN
SET NOCOUNT ON
if not exists (select * from inserted)
begin
	set @b = cast( (select id from deleted for xml raw, type) as varbinary(8000));
	exec tarif.dbo.sp_trace_generateevent 82, N'r1_delete', @b;
END
if not exists (select * from deleted)
begin
	set @c = cast( (select id  from inserted for xml raw, type) as varbinary(8000));
	exec tarif.dbo.sp_trace_generateevent 82, N'r1_create', @c;
end
else
begin
	set @b = cast( (select id from deleted for xml raw, type) as varbinary(8000));
	set @c = cast( (select id from inserted for xml raw, type) as varbinary(8000));
	exec tarif.dbo.sp_trace_generateevent 82, N'r1_udate_delete', @b;
	exec tarif.dbo.sp_trace_generateevent 82, N'r1_udate_insert', @c;
end

В результате я получаю XML для всех событий, кроме N'r1_delete', для которых у меня пустая строка почти всегда. Однако, когда я удаляю запись из SSMS (админ), то вижу полноценный XML. Возникает вопрос - почему для пользователей пусто, срабатывает триггер, но not exists (select * from inserted) понимается неверно?
3 мар 15, 11:37    [17336089]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Владислав Колосов,

Если бы not exists (select * from inserted) интерпретировалось неверно, вы бы вообще не получали r1_delete.
Посмотрите профайлером, что реально приходит в данных события.
3 мар 15, 11:53    [17336303]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
Разбил на три триггера.
Похоже, что MERGE активирует триггеры INSERT и UPDATE при вставке.
3 мар 15, 13:12    [17336897]     Ответить | Цитировать Сообщить модератору
 Re: И снова про автономные транзакции с помощью CLR  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Владислав Колосов
Похоже, что MERGE активирует триггеры INSERT и UPDATE при вставке.
Это документировано:
https://technet.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
Trigger Implementation

For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table


Если у вас триггер на insert, update, delete и в merge есть ветки insert, update и delete, то триггер будет вызван трижды.
3 мар 15, 14:13    [17337304]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: И снова про автономные транзакции с помощью CLR  [new]
DbDude
Member

Откуда:
Сообщений: 22
invm,

А есть ли аналог для Extended Events?
24 окт 19, 14:50    [22001760]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить