Когда мы хотим создать на SQL Server-е автоматическое задание, которое бы периодически запускало хранимую процедуру, то используется SQL Server агент. Что делать, если аналогичную процедуру, например, резервное копирование, требуется выполнить для многомерной базы данных на OLAP-сервере? Для решения этой задачи также можно воспользоваться SQL Agent-ом. Только сначала на SQL Server-е необходимо создать связанный сервер к источнику данных:
В качестве провайдера используется MOLAP, поскольку для задач администрирования в Analysis Services используется не MDX, а XML/A. После создания связанного сервера можно написать хранимую процедуру, которая будет выполнять резервное копирование многомерных баз данных:
Функция CheckSrvAndDb выполняет простую обработку ошибок. По каталогу sys.servers проверяется наличие связанного сервера. Также проверяется есть ли на сервере база с заданным именем. Для этого используется системный каталок $system.dbschema_catalogs. Запрос к этому каталогу можно запускать и в редакторе MDX-запросов.
В хранимой процедуре мы строим динамическое выражение на языке XML/A. Этот код может выполнять резервное копирования баз данных в Analyis Services. Его нельзя запускать через функцию openquery, так как он не возвращает табличных данных. Вместо этого требуется применить конструкцию exec ( ... ) at SSAS, указав после at имя связанного сервера. Для бэкапа обязательно требуется указать пароль и расположение. Есть параметры со значениями по умолчанию для задания опций сжатия и перезаписи. Вcе настройки отражаются в выражении XML/A.
Подобным образом можно написать несложную хранимую процедуру для восстановления многомерной базы данных из бэкапа:
В этой процедуре задаются похожие опции, что и при бэкапировании. Обе процедуры созданы с опцией шифрования, чтобы никто не смог запустить профайлер и посмотреть с каким паролем вызываются процедуры. Теперь вызовы подобных процедур можно прописать как шаги заданий SQL агента, и периодически выполнять задачи резервного копирования или восстановления многомерных баз данных.
use
master
exec
master.dbo.sp_addlinkedserver @server = N'SSAS', @srvproduct = N'', @provider = N'MSOLAP', @datasrc = N'EDYNAK\SHADOW'
exec
master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SSAS', @useself = N'False', @locallogin = null, @rmtuser = null, @rmtpassword = null
exec
master.dbo.sp_serveroption @server = N'SSAS', @optname = N'data access', @optvalue=N'true'
exec
master.dbo.sp_serveroption @server = N'SSAS', @optname = N'rpc', @optvalue=N'true'
exec master.dbo.sp_serveroption @server = N'SSAS', @optname = N'rpc out', @optvalue = N'true'В качестве провайдера используется MOLAP, поскольку для задач администрирования в Analysis Services используется не MDX, а XML/A. После создания связанного сервера можно написать хранимую процедуру, которая будет выполнять резервное копирование многомерных баз данных:
if
sessionproperty ( N'quoted_identifier'
) = 0
set quoted_identifier on
go
if
sessionproperty ( N'ansi_nulls' ) = 0
set ansi_nulls on
go
if
object_id ( N'dbo.CheckSrvAndDb', N'FN' ) is null
exec ( N'create function
dbo.CheckSrvAndDb ( @i int ) returns int as begin return 0 end' )
go
alter
function dbo.CheckSrvAndDb
(
@SrvName nvarchar ( 200 ),
@DbName nvarchar ( 200 )
)
returns nvarchar ( max )
as
begin
declare @DataSource nvarchar ( 200 )
if not exists
(
select
1
from
sys.servers
where name = @SrvName
)
begin
return N'Не обнаружен связанный
сервер с именем "' + @SrvName + '".'
end
select @DataSource = data_source
from sys.servers
where name = @SrvName
if not exists
(
select
*
from
openquery
(
SSAS,
'
select *
from
$system.dbschema_catalogs
'
)
where
cast ( catalog_name as nvarchar ( max ) ) = @DbName
)
begin
return N'База данных с именем
"' + @DbName
+ '" не найдена на
OLAP-сервере "' + @DataSource + '".'
end
return N''
end
go
if objecT_id ( N'dbo.BackUpMdDatabase', N'P' ) is null
exec ( N'create proc dbo.BackUpMdDatabase as return' )
go
alter
proc dbo.BackUpMdDatabase
(
@DbName nvarchar ( 200 ),
@SrvName nvarchar ( 200 ),
@Password nvarchar ( 200 ),
@FilePath nvarchar ( 300 ),
@Compr bit = 1,
@Overwrite bit = 0
)
with
encryption
as
begin
set nocount, xact_abort on
declare @ErrMsg nvarchar ( max ), @cmd nvarchar ( max ), @sql nvarchar ( max )
begin try
set
@ErrMsg = dbo.CheckSrvAndDb ( @SrvName, @DbName )
if isnull ( @ErrMsg, N'' ) <> N''
begin
raiserror
( @ErrMsg, 16, 1 )
end
set
@cmd = N'
<Backup
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>'
+ @DbName + '</DatabaseID>
</Object>
<File>'
+ @FilePath + '</File>
<ApplyCompression>'
+ case when @Compr = 1 then N'true' else N'false' end + '</ApplyCompression>'
+ nchar ( 13 ) +
case when @Password is null then N'' else '<Password>' + @Password + '</Password>' end
+ nchar ( 13 ) +
N'<AllowOverwrite>' +
case when @Overwrite = 0 then N'false' else N'true' end + N'</AllowOverwrite>' +
nchar ( 13 ) +
'</Backup>
'
set
@sql = N'exec ( ''' + @cmd + N''' ) at ' + quotename ( @SrvName )
exec
sp_executesql @sql
end try
begin catch
set
@ErrMsg = error_message ()
end catch
if isnull ( @ErrMsg, N'' ) <> N''
begin
raiserror ( @ErrMsg, 16, 1 )
end
end
goФункция CheckSrvAndDb выполняет простую обработку ошибок. По каталогу sys.servers проверяется наличие связанного сервера. Также проверяется есть ли на сервере база с заданным именем. Для этого используется системный каталок $system.dbschema_catalogs. Запрос к этому каталогу можно запускать и в редакторе MDX-запросов.
В хранимой процедуре мы строим динамическое выражение на языке XML/A. Этот код может выполнять резервное копирования баз данных в Analyis Services. Его нельзя запускать через функцию openquery, так как он не возвращает табличных данных. Вместо этого требуется применить конструкцию exec ( ... ) at SSAS, указав после at имя связанного сервера. Для бэкапа обязательно требуется указать пароль и расположение. Есть параметры со значениями по умолчанию для задания опций сжатия и перезаписи. Вcе настройки отражаются в выражении XML/A.
Подобным образом можно написать несложную хранимую процедуру для восстановления многомерной базы данных из бэкапа:
if
object_id ( N'dbo.RestoreMdDatabase', N'FN' ) is null
exec ( N'create proc dbo.RestoreMdDatabase as return' )
go
alter
proc dbo.RestoreMdDatabase
(
@DbName nvarchar ( 200 ),
@SrvName nvarchar ( 200 ),
@Password nvarchar ( 200 ),
@FilePath nvarchar ( 300 ),
@Overwrite bit = 0
)
with
encryption
as
begin
set nocount, xact_abort on
declare @ErrMsg nvarchar ( max ), @cmd nvarchar ( max ), @sql nvarchar ( max )
begin try
set
@ErrMsg = dbo.CheckSrvAndDb ( @SrvName, @DbName )
if isnull ( @ErrMsg, N'' ) <> N'' and not ( isnull ( @ErrMsg, N'' ) like N'База
данных%' )
begin
raiserror
( @ErrMsg, 16, 1 )
end
set
@ErrMsg = N''
set
@cmd = N'
<Restore
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>' + @FilePath + '</File>
<DatabaseName>' + @DbName + '</DatabaseName>
<Password>' + @Password + '</Password>
<AllowOverwrite>'
+ case when @Overwrite = 0 then N'false' else N'true' end + '</AllowOverwrite>
</Restore>
'
set
@sql = N'exec ( ''' + @cmd + N''' ) at ' +
exec
sp_executesql @sql
end try
begin catch
set
@ErrMsg = error_message ()
end catch
if isnull ( @ErrMsg, N'' ) <> N''
begin
raiserror ( @ErrMsg, 16, 1 )
end
end
goВ этой процедуре задаются похожие опции, что и при бэкапировании. Обе процедуры созданы с опцией шифрования, чтобы никто не смог запустить профайлер и посмотреть с каким паролем вызываются процедуры. Теперь вызовы подобных процедур можно прописать как шаги заданий SQL агента, и периодически выполнять задачи резервного копирования или восстановления многомерных баз данных.
