前言

问题背景

在 BI 平台日常运维中，SSAS 模型需要定时进行数据刷新处理。然而存在一个严重问题：

模型处理作业运行时间长达数小时（通常 05:00 - 09:00），在此期间 SSAS 模型处于数据重新更新的状态，BI 平台无法正常访问，导致用户无法使用系统。

解决方案

采用 “备份-还原-切换” 方案，通过以下策略解决访问中断问题：

┌─────────────────────────────────────────────────────────────────┐
│                        每日自动化流程                            │
├─────────────────────────────────────────────────────────────────┤
│  前一日 23:55 │ SSAS 备份 → 生成正式库的备份文件                  │
│  当日 00:00   │ SSAS 还原 → 创建带时间戳的还原库（供查询使用）     │
│  当日 01:30   │ 切换数据源 → BI 平台指向还原库（可正常访问）       │
│  当日 05:00   │ 模型处理 → 正式库进入处理状态（用户无感知）        │
│  当日 09:00   │ 处理完成 → 正式库恢复可用                         │
│  当日 09:10   │ 切回数据源 → BI 平台指向正式库                    │
│  当日 10:00   │ 删除还原库 → 释放磁盘空间                         │
└─────────────────────────────────────────────────────────────────┘

方案优势

优势	说明
零停机访问	模型处理期间，BI 平台始终有可用的还原库支撑查询
用户无感知	数据源切换自动化，用户无需任何操作
全自动运行	通过 SQL Agent 作业定时执行，无需人工干预

适用场景

SSAS 模型处理时间较长（超过 2 小时）
BI 平台需要 7×24 小时可访问
模型处理时间与业务访问时间存在重叠

流程展示

流程 -> 标题

1.SSAS备份及SSAS备份删除 --> 4.SSAS备份相关脚本
2.SSAS_timestamp还原 --> 1.3
3.抽数作业中模型处理前将平台里的数据源正式库改为还原库 --> 6.3
4.模型处理成功后再将数据源的还原库改为正式库 --> 6.4
5.删除SSAS_timestamp还原库 --> 1.4

0.前期准备

0.1SQLSERVER服务的用户说明

-- 数据库的用户：通过sql操作文件时,需要给MSSQLSERVER用户赋予执行脚本的权限
NT Service\MSSQLSERVER
-- SSAS模型库的用户：通过xmla脚本操作文件时,需要给MSSQLServerOLAPService用户赋予执行脚本的权限
NT Service\MSSQLServerOLAPService
-- 执行作业时的的用户：通过作业操作文件时,需要给SQLSERVERAGENT用户赋予执行脚本的权限
NT Service\SQLSERVERAGENT

/*其他用户
NT SERVICE\MsDtsServer150
NT Service\MSSQLFDLauncher
NT AUTHORITY\LOCALSERVICE
*/

0.2脚本添加用户权限

选中需要授权的文件，鼠标右键打开属性面板

0.3备份文件的目录添加everyone用户

脚本备份数据库的目录直接添加everyone用户并赋予所有权限(执行清理超过七天文件的脚本需要)

0.4创建空链接服务器SSAS

USE [master]
GO

/****** Object:  LinkedServer [SSAS]    Script Date: 2024/1/16 13:37:21 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SSAS', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'.'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

1.SSAS还原相关脚本

1.1新建文件：构建还原与删除脚本.bat

文件内容如下
双击构建还原与删除脚本.bat 文件即可执行该脚本，可以双击测试一下输出结果

输出文件：RookieBI_RestoreScript.xmla(还原脚本)、RookieBI_DeleteScript.xmla(删除脚本)

@echo off
setlocal enabledelayedexpansion

:: 获取最新备份文件
for /f %%i in ('dir /b /o-d "D:\Analysis Backup\RookieBI_*.abf"') do (
    set "latestBackup=%%i"
    goto :break
)
:break

:: 提取时间戳
:: 时间戳的位置是从第9个字符开始的 RookieBI_
set "timestamp=!latestBackup:~9,-4!"  

:: 构建还原脚本
(
    echo ^<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"^>
    echo     ^<File^>D:\Analysis Backup\!latestBackup!^</File^>
    echo     ^<DatabaseName^>RookieBI_!timestamp!^</DatabaseName^>
    echo     ^<AllowOverwrite^>true^</AllowOverwrite^>
:: 备份文件还原路径D:\Analysis Backup\ 模型数据存储在该路径下
    echo     ^<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"^>D:\Analysis Backup\^</DbStorageLocation^>
    echo ^</Restore^>
) > "D:\testData\RookieBI_RestoreScript.xmla"

:: 构建删除脚本
(
    echo ^<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"^> 
    echo     ^<Object^>
    echo     ^<DatabaseID^>RookieBI_!timestamp!^</DatabaseID^>
    echo     ^</Object^>
    echo ^</Delete^>
) > "D:\testData\RookieBI_DeleteScript.xmla"

1.2通过sql脚本执行构建还原与删除脚本.bat文件

输出文件为RookieBI_RestoreScript.xmla(还原脚本)、RookieBI_DeleteScript.xmla(删除脚本)

-- 启用显示高级选项配置
EXEC sp_configure 'show advanced options', 1;
-- 重新加载配置以使更改生效
RECONFIGURE;
-- 启用 xp_cmdshell 存储过程，允许执行外部命令
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- 通过xp_cmdshell执行脚本(需要全路径)
EXEC xp_cmdshell 'D:\testData\构建还原与删除脚本.bat';

-- 关闭 xp_cmdshell 存储过程，不允许执行外部命令
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

1.3读取 `RestoreScript.xmla` 文件里的命令还原最新的SSAS备份文件

可以直接通过sql脚本执行，比如在模型处理的包里，新建一个sql脚本步骤

DECLARE @xmlaContent NVARCHAR(MAX)

-- 读取 XMLA 文件内容, 还原RookieBI_timestamp模型
SELECT @xmlaContent = BulkColumn
FROM OPENROWSET(BULK 'D:\testData\RookieBI_RestoreScript.xmla', SINGLE_CLOB) AS x;

-- 执行 XMLA 查询
EXEC (@xmlaContent) AT SSAS;

1.4读取 `DeleteScript.xmla` 文件里的命令删除SSAS_timestamp模型

可以直接通过sql脚本执行，比如在模型处理的包里，新建一个sql脚本步骤

DECLARE @xmlaContent NVARCHAR(MAX)

-- 读取 XMLA 文件内容, 删除RookieBI_timestamp模型
SELECT @xmlaContent = BulkColumn
FROM OPENROWSET(BULK 'D:\testData\RookieBI_DeleteScript.xmla', SINGLE_CLOB) AS x;

-- 执行 XMLA 查询
EXEC (@xmlaContent) AT SSAS;

2.创建 SSAS还原.dtsx

2.1复制1.2的代码到组件中

通过sql脚本执行bat文件

2.2复制1.3的代码到组件中

2.3部署SSAS还原包

2.4添加权限

添加用户 NT SERVICE\SQLSERVERAGENT 到SSAS管理员中，不然执行作业的时候会报错

2.5新建SSAS还原作业

在计划里可定时执行还原作业

3.本地测试示例

构建还原脚本.bat、RestoreScript.xmla内容

4.SSAS备份相关脚本

4.1通过T-SQL脚本备份

备份文件为 .abf 文件

-- SSAS备份脚本 T-SQL
use master
go
DECLARE 
@myXMLA nvarchar(max), 
@datetime varchar(20)

SET @datetime=format(getdate(),'yyyyMMdd-HHmmss')

SET @myXMLA =N'
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
	<Object>
		<DatabaseID>test</DatabaseID>
	</Object>
	<File>D:\sqlData\Backup\test_'+@datetime+'.abf</File>
</Backup>
'
Exec (@myXMLA) At SSAS;

-- SSAS备份脚本 T-SQL
USE master;
GO

DECLARE @myXMLA nvarchar(max), @datetime varchar(20);
SET @datetime = FORMAT(GETDATE(), 'yyyyMMdd-HHmmss');

SET @myXMLA = N'
{
    "backup": {
        "database": "test",
        "file": "D:\\sqlData\\Backup\\test_' + @datetime + '.abf",
        "allowOverwrite": false,
        "applyCompression": true
    }
}
';

EXEC (@myXMLA) AT SSAS;

可以直接创建作业，选择脚本类型为T-SQL，将脚本直接复制过来即可

DatabaseID、File标签中的值修改为自己的SSAS数据库名，备份所在路径

4.2清理超过7天的abf备份文件.bat

新建文件清理超过7天的备份文件.bat

通过调用该脚本可以清理指定路径下超过7天的备份文件

@echo off
setlocal enabledelayedexpansion

:: 设置变量
set "sourceDir=D:\sqlData\Backup"
set "errorFlag=0"

:: 获取当前日期（以 yyyyMMdd 格式）
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /value') do set datetime=%%I
set "currentDate=!datetime:~0,8!"

:: 遍历目录及子目录下的.abf文件
for /r "%sourceDir%" %%F in (*.abf) do (
    set "filePath=%%F"
    set "fileDate=%%~tF"
    
    :: 计算文件的日期与当前日期的差值
    set "fileDate=!fileDate:~0,4!!fileDate:~5,2!!fileDate:~8,2!"
    
    set /a "dateDiff=currentDate - fileDate"
    
    :: 删除7天前的文件
    if !dateDiff! gtr 7 (
        del /f /q "!filePath!"
        if !errorlevel! neq 0 set "errorFlag=1"
    )
)

:: 返回相应的退出代码
if %errorFlag% equ 1 (
    exit /b 1
) else (
    exit /b 0
)

:: 路径D:\sqlData\Backup  清理文件*.abf (清理超过7天的文件)
cmd /e:on /c "forfiles /p "D:\sqlData\Backup" /s /m *.abf /d -7 /c "cmd /c del /f /q /a @path" 2>&1 | findstr /V /O /C:"ERROR: No files found with the specified search criteria." 2>&1 | findstr ERROR && EXIT 1 || EXIT 0" & exit %errorlevel%

4.3通过sql调用bat脚本

-- 启用显示高级选项配置
EXEC sp_configure 'show advanced options', 1;
-- 重新加载配置以使更改生效
RECONFIGURE;
-- 启用 xp_cmdshell 存储过程，允许执行外部命令
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- 通过xp_cmdshell执行脚本(需要全路径)
EXEC xp_cmdshell 'D:\sqlData\Restore\清理超过7天的abf备份文件.bat';

-- 关闭 xp_cmdshell 存储过程，不允许执行外部命令
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

5.数据库备份相关脚本

5.1通过T-SQL脚本备份

备份文件为 .bak 文件

use master
go
-- 创建一个完整备份
DECLARE @datetime NVARCHAR(50)
SET @datetime = format(getdate(),'yyyyMMdd-HHmmss')

DECLARE @BackupFileName NVARCHAR(100)
SET @BackupFileName = 'D:\sqlData\Backup\Test_' + @datetime + '.bak'

BACKUP DATABASE Test
TO DISK = @BackupFileName
WITH FORMAT, INIT, NAME = 'FullBackup';

-- 可选：创建一个差异备份（如果需要）
-- BACKUP DATABASE YourDatabaseName
-- TO DISK = 'D:\sqlData\Backup\YourDatabaseName_Diff.bak'
-- WITH DIFFERENTIAL, FORMAT, INIT, NAME = 'DiffBackup';

5.2清理超过7天的bak备份文件.bat

新建文件清理超过7天的备份文件.bat

通过调用该脚本可以清理指定路径下超过7天的备份文件

@echo off
setlocal enabledelayedexpansion

:: 设置变量
set "sourceDir=D:\sqlData\Backup"
set "errorFlag=0"

:: 获取当前日期（以 yyyyMMdd 格式）
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /value') do set datetime=%%I
set "currentDate=!datetime:~0,8!"

:: 遍历目录及子目录下的.bak文件
for /r "%sourceDir%" %%F in (*.bak) do (
    set "filePath=%%F"
    set "fileDate=%%~tF"
    
    :: 计算文件的日期与当前日期的差值
    set "fileDate=!fileDate:~0,4!!fileDate:~5,2!!fileDate:~8,2!"
    
    set /a "dateDiff=currentDate - fileDate"
    
    :: 删除7天前的文件
    if !dateDiff! gtr 7 (
        del /f /q "!filePath!"
        if !errorlevel! neq 0 set "errorFlag=1"
    )
)

:: 返回相应的退出代码
if %errorFlag% equ 1 (
    exit /b 1
) else (
    exit /b 0
)

:: 路径D:\sqlData\Backup  清理文件*.bak (清理超过7天的文件)
cmd /e:on /c "forfiles /p "D:\sqlData\Backup" /s /m *.bak /d -7 /c "cmd /c del /f /q /a @path" 2>&1 | findstr /V /O /C:"ERROR: No files found with the specified search criteria." 2>&1 | findstr ERROR && EXIT 1 || EXIT 0" & exit %errorlevel%

5.3通过sql调用bat脚本

-- 启用显示高级选项配置
EXEC sp_configure 'show advanced options', 1;
-- 重新加载配置以使更改生效
RECONFIGURE;
-- 启用 xp_cmdshell 存储过程，允许执行外部命令
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- 通过xp_cmdshell执行脚本(需要全路径)
EXEC xp_cmdshell 'D:\sqlData\Backup\清理超过7天的bak备份文件.bat';

-- 关闭 xp_cmdshell 存储过程，不允许执行外部命令
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

6.更新数据源相关脚本

6.1新建文件：构建更新数据源脚本.bat

文件内容如下
双击构建更新数据源脚本.bat 文件即可执行该脚本，可以双击测试一下输出结果

@echo off
setlocal enabledelayedexpansion

:: 获取最新备份文件
for /f %%i in ('dir /b /o-d "D:\Analysis Backup\RookieBI_*.abf"') do (
    set "latestBackup=%%i"
    goto :break
)
:break

:: 提取时间戳
:: 时间戳的位置是从第9个字符开始的 RookieBI_
set "timestamp=!latestBackup:~9,-4!"

:: 构建更新脚本：切换为最新备份文件数据源
(
echo update DashboardTest.dbo.DbConnect set ConnectString=replace^(ConnectString, 'Catalog=RookieBI', 'Catalog=RookieBI_!timestamp!'^) where Id='6718A7AE-4345-A8BA-B382-3A095D478EE7';
echo update DashboardTest.dbo.DbConnect set DbName=replace^(DbName, 'RookieBI', 'RookieBI_!timestamp!'^) where Id='6718A7AE-4345-A8BA-B382-3A095D478EE7';
) > "D:\testData\RookieBI_BackupName.sql"

:: 构建更新脚本：切换回原来的数据源
(
echo update DashboardTest.dbo.DbConnect set ConnectString=replace^(ConnectString, 'Catalog=RookieBI_!timestamp!', 'Catalog=RookieBI'^) where Id='6718A7AE-4345-A8BA-B382-3A095D478EE7';
echo update DashboardTest.dbo.DbConnect set DbName=replace^(DbName, 'RookieBI_!timestamp!', 'RookieBI'^) where Id='6718A7AE-4345-A8BA-B382-3A095D478EE7';
) > "D:\testData\RookieBI_Name.sql"

6.2通过sql脚本执行构建更新数据源脚本.bat文件

注：这个脚本可以直接放到模型还原.dtsx 这个包里

执行结果是输出两个脚本文件RookieBI_BackupName.sql与RookieBI_Name.sql

-- 启用显示高级选项配置
EXEC sp_configure 'show advanced options', 1;
-- 重新加载配置以使更改生效
RECONFIGURE;
-- 启用 xp_cmdshell 存储过程，允许执行外部命令
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- 通过xp_cmdshell执行脚本(需要全路径)
EXEC xp_cmdshell 'D:\testData\构建更新数据源脚本.bat';

-- 关闭 xp_cmdshell 存储过程，不允许执行外部命令
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

6.3通过sql脚本执行RookieBI_BackupName.sql

将数据源模型名称切换为最新备份文件名称 RookieBI_timestamp

下面的方式二选一

1.直接通过作业实现：
目前模型SSAS还原的作业定时为每日23:55，则这个切换数据源的作业可以定时在半个小时后，建议定时为每日凌晨00:25之后

2.放到模型处理的包里：
可以放在模型处理的包里，新建一个sql脚本步骤放到模型处理前

-- 创建临时表存储文件内容
CREATE TABLE #temp (content NVARCHAR(MAX));
-- 将文件内容插入临时表
INSERT INTO #temp (content) SELECT * FROM OPENROWSET(BULK 'D:\testData\RookieBI_BackupName.sql', SINGLE_CLOB) AS content;
-- 从临时表中读取SQL语句
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = content FROM #temp;
-- 执行动态SQL语句
EXECUTE sp_executesql @sql;
-- 删除临时表
DROP TABLE #temp;

本地环境示例：

6.4通过sql脚本执行RookieBI_Name.sql

将数据源模型名称切换为原名称 RookieBI

下面的方式二选一

1.直接通过作业实现：
目前模型处理结束时间在九点到十点左右，则这个切换数据源的作业可以定时在十点左右，建议定时为每日10:00之后

2.放到模型处理的包里：
可以放在模型处理的包里，新建一个sql脚本步骤放到模型处理后

-- 创建临时表存储文件内容
CREATE TABLE #temp (content NVARCHAR(MAX));
-- 将文件内容插入临时表
INSERT INTO #temp (content) SELECT * FROM OPENROWSET(BULK 'D:\testData\RookieBI_Name.sql', SINGLE_CLOB) AS content;
-- 从临时表中读取SQL语句
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = content FROM #temp;
-- 执行动态SQL语句
EXECUTE sp_executesql @sql;
-- 删除临时表
DROP TABLE #temp;

Leo的博客

SQL Server数据库与SSAS模型库自动化运维

前言

问题背景

解决方案

方案优势

适用场景

流程展示

0.前期准备

0.1SQLSERVER服务的用户说明

0.2脚本添加用户权限

0.3备份文件的目录添加everyone用户

0.4创建空链接服务器SSAS

1.SSAS还原相关脚本

1.1新建文件：构建还原与删除脚本.bat

1.2通过sql脚本执行构建还原与删除脚本.bat文件

1.3读取 `RestoreScript.xmla` 文件里的命令还原最新的SSAS备份文件

1.4读取 `DeleteScript.xmla` 文件里的命令删除SSAS_timestamp模型

2.创建 SSAS还原.dtsx

2.1复制1.2的代码到组件中

2.2复制1.3的代码到组件中

2.3部署SSAS还原包

2.4添加权限

2.5新建SSAS还原作业

3.本地测试示例

4.SSAS备份相关脚本

4.1通过T-SQL脚本备份

4.2清理超过7天的abf备份文件.bat

4.3通过sql调用bat脚本

5.数据库备份相关脚本

5.1通过T-SQL脚本备份

5.2清理超过7天的bak备份文件.bat

5.3通过sql调用bat脚本

6.更新数据源相关脚本

6.1新建文件：构建更新数据源脚本.bat

6.2通过sql脚本执行构建更新数据源脚本.bat文件

6.3通过sql脚本执行RookieBI_BackupName.sql

6.4通过sql脚本执行RookieBI_Name.sql

Leo

前言

问题背景

解决方案

方案优势

适用场景

流程展示

0.前期准备

0.1SQLSERVER服务的用户说明

0.2脚本添加用户权限

0.3备份文件的目录添加everyone用户

0.4创建空链接服务器SSAS

1.SSAS还原相关脚本

1.1新建文件：构建还原与删除脚本.bat

1.2通过sql脚本执行 构建还原与删除脚本.bat文件

1.3读取 RestoreScript.xmla 文件里的命令还原最新的SSAS备份文件

1.4读取 DeleteScript.xmla 文件里的命令删除SSAS_timestamp模型

2.创建 SSAS还原.dtsx

2.1复制1.2的代码到组件中

2.2复制1.3的代码到组件中

2.3部署SSAS还原包

2.4添加权限

2.5新建SSAS还原作业

3.本地测试示例

4.SSAS备份相关脚本

4.1通过T-SQL脚本备份

4.2清理超过7天的abf备份文件.bat

4.3通过sql调用bat脚本

5.数据库备份相关脚本

5.1通过T-SQL脚本备份

5.2清理超过7天的bak备份文件.bat

5.3通过sql调用bat脚本

6.更新数据源相关脚本

6.1新建文件：构建更新数据源脚本.bat

6.2通过sql脚本执行 构建更新数据源脚本.bat文件

6.3通过sql脚本执行RookieBI_BackupName.sql

6.4通过sql脚本执行RookieBI_Name.sql

MySQL用户权限说明

SQL Server之SSMS使用笔记

Leo

1.2通过sql脚本执行构建还原与删除脚本.bat文件

1.3读取 `RestoreScript.xmla` 文件里的命令还原最新的SSAS备份文件

1.4读取 `DeleteScript.xmla` 文件里的命令删除SSAS_timestamp模型

6.2通过sql脚本执行构建更新数据源脚本.bat文件