问题[snapshot](dba)

我有一个负载相当大的 Azure SQL DB (OLTP)，我们称之为 AppDB。AppDB 在事务上是一致的，但会不断地向其中写入新行。现在我需要每 30 分钟根据 AppDB 状态填充 ReportingDB。报告人口作业针对 AppDB 运行几个中等大的查询，不幸的是，这些查询不能包装在事务中，但仍然必须全部在一致的数据上运行。也就是说，我不能有情况Query 1 runs=> new rows inserted into AppDB=> Query 2 runs。我所有的查询都必须以查询 1 开始时的方式查看数据。

我希望我可以使用快照创建一个只读快照以供报告作业使用。根据描述，这样的快照的创建应该很快，并且随后的“写入时复制”性能命中应该是可控的。快照的生命周期平均不到 10 分钟。

但是现在看起来 Azure SQL 不支持CREATE DATABASE ... AS SNAPSHOT OF ...，它只支持CREATE DATABASE ... AS COPY OF ...，我预计会慢很多（可能不打算用于报告快照）。

在 Azure SQL DB 中创建快速且短暂的只读快照以进行报告的好方法是什么？

PS我们已经考虑过复制，但由于政策限制，目前这不是我们的选择。

我们每周四都有代码部署，其中一些更改涉及数据库更改 - 在代码部署之前，我们会进行 SQL 备份，以防出现错误，这通常需要 45 分钟，每个人都必须等到它完成。我可以利用数据库快照，如果代码测试良好，只需删除快照，如果有问题，使用快照回滚所做的任何更改？

前言

在我工作的公司，我们在 ETL 流程中使用数据库快照来确保数据处于一致状态。快照文件被放置在一个单独的磁盘上（在我们的例子中是 X:）。

其中一个源数据库是一个 3TB 的数据库，其数据经常更新。在创建此数据库的数据库快照时，我们遇到了快照文件增加并且出现错误的时刻：

“遇到操作系统错误 112（磁盘空间不足。）。”

该错误始终出现在索引维护窗口中，或者在源数据库中运行批量进程时。从那时起，我们增加了 X:\ 驱动器上的可用磁盘空间，并更改了索引维护/dbcc checkdb 作业的计划。正如预期的那样，错误不再发生。

问题

有时我们需要一些大型表的初始加载（600.000.000 行/每个表 300GB）。本周我们在加载这些大表时遇到了不同的错误：

在文件“X:\Data\DatabaseName_snapshot.ss”中的偏移量 0x000056fb976000 处写入期间，操作系统向 SQL Server 返回错误 665（请求的操作由于文件系统限制而无法完成）。

SQL Server 错误日志和系统事件日志中的其他消息可能会提供更多详细信息。

此错误仅在创建快照后 18 到 25 小时内发生。

已经尝试过的事情

1. 禁用所有索引维护/dbcc checkdb 作业和其他更改大量数据的进程（用户输入除外）。
2. 监控快照磁盘上的可用空间（X:\ 400GB）。发生错误时，大约 80% 的可用空间是可用的。
3. 在 stackexchange 上寻找类似的问题。发现的数据库快照进入未回答的可疑模式（WIN2K8R2 上的 SQL 2014） 。
4. 立即查看文章https://blogs.msdn.microsoft.com/psssql/2015/06/10/operating-system-error-665-file-system-limitation-not-just-for-dbcc-anymore/。

有没有人有更多的想法来解决这个问题？

我们使用的是 SQL Server 2014 SP2 CU1，企业版和 Windows Server 2012。

我正在尝试为我们的集成测试定义一种使用测试数据库（在 SQL Server 中）的方式。

我的想法是在集成测试程序集启动时执行这些步骤：

• 创建一个完全空的数据库
• 运行“创建数据库对象”脚本来创建所有相关的数据库对象（表、视图、序列等）
• 填写“基础数据”（查找值等）
• (db)_Basis为未来的集成测试拍摄称为“基线”的数据库快照

现在，在每个测试类（包含 1-n 个测试）之前，我计划简单地执行“从快照还原”以返回到数据库的定义明确、或多或少“空”的状态。到目前为止，它就像一个魅力。

但是，有一组集成测试需要在大型测试数据库上运行 - 所以我希望在每个测试夹具（具有 n 个单独测试的类）之前执行此操作

• 从(db)_Basis快照恢复数据库
• 将这 50'000+ 行数据插入数据库
• 创建另一个快照(db)_With_Testdata快照

然后对于每个测试，将数据库重置为定义良好的(db)_With_Testdata快照版本，运行测试，验证结果等等。

问题是：我似乎不能同时拥有两个数据库快照 - 一旦我这样做，我就无法将我的数据库恢复到其中任何一个......我不断收到此错误：

消息 3137，级别 16，状态 4，第 9 行
数据库无法还原。主名称或快照名称指定不正确，未删除所有其他快照，或者缺少文件。

消息 3013，级别 16，状态 1，第 9 行
RESTORE DATABASE 异常终止。

这真的是 SQL Server 数据库快照的工作原理吗？似乎非常限制.....如果我不能直接回到原始的“（db）_Basis”快照，我会理解 - 但仅仅因为我现在有两个快照，我什至不能回到最近的一个?!?!?

MSDN建议CREATE DATABASE权限是创建快照所必需的。

任何可以创建数据库的用户都可以创建数据库快照；但是，要创建镜像数据库的快照，您必须是 sysadmin 固定服务器角色的成员。

情况似乎并非如此。当尝试通过具有此建议的最低权限要求的用户创建快照时，出现以下错误：

Msg 1817, Level 14, State 1, Line 4
Only the owner of database "example" or the system administrator can create a database snapshot on it.

由于我想设置一个自动流程来按计划创建快照，因此我显然想避免授予它db_owner或sysadmin权利。

是否可以按照 MSDN 的建议进行设置，或者他们的文档有误？

非常感谢您的任何建议。

/*as sysadmin*/

USE [master];
CREATE DATABASE example;
CREATE LOGIN testuser WITH PASSWORD='xxx'; 
CREATE USER testuser FOR LOGIN testuser WITH DEFAULT_SCHEMA=dbo;
go

USE [example];
CREATE USER testuser FOR LOGIN testuser WITH DEFAULT_SCHEMA=dbo;
go

USE [master];
GRANT CREATE DATABASE to testuser;
go


/*as testuser*/

USE [master];
create database example_snapshot_01
on (
name = example,
filename = 'C:\...\example_snapshot_01.ss'
)
as snapshot of example;
go


/*
Error:
    Msg 1817, Level 14, State 1, Line 4
    Only the owner of database "example" or the system administrator can create a database snapshot on it.
*/

我知道数据库快照背后的逻辑及其工作原理。但我的问题是如何使用数据库快照来恢复数据？我们可以备份数据库快照并在新服务器上恢复它吗？

我正在使用 MS SQL 服务器

谢谢你。

我注意到我不能拥有一个 SQL Server 数据库的多个快照，然后从其中一个恢复（我不相信，删除除了我想要恢复的以外的所有快照）。

有没有办法保留同一个数据库的多个快照并能够恢复到其中任何一个？

还是购买一些执行此操作的第 3 方软件或对每个版本的数据库进行完整备份的唯一方法？

数据库快照文件是稀疏文件。只有当页面被修改时，才会写入稀疏文件。读取快照必须能够知道稀疏文件是否包含复制页面。SQL Server 如何知道从哪里读取？这些信息存储在哪里？

我从 2011 年开始在这里找到这篇文章，并想验证它是否仍然有效，并且只要您正在捕捉所有数据，就对 ebs 卷（PostgreSQL 仍在运行）进行热快照是一个很好的建议。

PostgreSQL 文档表明只要整个数据目录（WAL 和所有表，不同的表空间可能会导致问题）都在同一个快照中拍摄，低级别快照就可以了。

我目前正在使用 PostgreSQL v9.2。

我有一个使用 Oracle 的应用程序。在许多事情中，此应用程序具有时间表条目。主管以适当的费率（直接或加班）输入员工的工作时间，分配给收费代码 - 并保存。

我们需要每天向第三方报告（上传）小时数据。如果上传后更改小时数，我们需要提交调整上传，并附上 delta。

起初我以为我会用触发器来处理这个问题。在时间卡表中添加时间戳列，每次上传时，我们都会更新所有未加盖时间戳的时间卡的时间戳。然后，只要发生插入或更新，触发器就会触发......如果有时间戳，它会添加旧值和新值之间的增量并将其存储在新表中 - 我们将其作为调整上传。在我的脑海里——这很好而且很整洁。

但是，在仔细检查数据（查看 ROWID）后，我意识到每当给定时间表中的任何值发生更改并执行 SAVE 操作时，应用程序都会发出 DELETE 操作，然后是 INSERT 操作。所以任何时间戳都会被抹去——我所能做的就是用一堆无用的数据来膨胀我的调整表。

因此，我开始认为，如果我每次报告小时数时都创建 Timecard 表的快照......那么我可以简单地将 timecard 与快照进行比较 - 并获得调整。然后我只需在调整后刷新快照。

快照选项让我担心 - 在我正在尝试这些东西的测试服务器上 - 我只有 2500 条记录可供使用 - 在生产中它接近一百万。另外，我不确定是否删除所有记录，然后在一个大小真的很聪明的表上执行 INSERT INTO 语句？

我查看了 CREATE SNAPSHOT - 因为我认为日志会比创建重复表更有效 - 但我没有看到您可以像查询常规表一样查询快照。

另一种选择是将时间卡表结构复制到一个新表（timecardsnap），然后在时间卡表上创建一个触发器（对于每一行），然后引入一些逻辑来检查它是否已经被导出 - 如果它有, 插入增量 .. 如果它没有更新现有数据。但是，如果更改了收费代码，这就会出现问题——因为我最终会重复值。鉴于其 DELETE / INSERT - 两个独立的操作 - 不容易查看旧值与新值。

解决此问题且不会对服务器性能产生负面影响的最佳方法是什么？

编辑：Oracle 10g。

更新 - 1 月 3 日 - 下午 5 点 MST

我开始玩临时表的想法。我基本上把表可能的操作分成了三种可能的操作类型，并问自己每个应该执行什么动作。

删除时 - 我们需要取消刚刚删除的所有时间 插入时 - 我们需要将新的时间添加到下一次导出中，减去已经上传的时间。我们还需要确保不存在尚未导出的先前添加。

为此，我创建了两张表：一张用于存储 PENDING 更改，另一张用于存储 EXPORTED 数据。在应用程序中添加和删除行时，挂起的表会发生变化，而 EXPORTED 保持静态。

我使用术语“拆分”来指代我们的关键字段——EMPID、WORKDAY、COSTCODE、DEPT，因为它们基本上定义了我们记录值（小时）的粒度级别。

所以现在我有了两个表 - 我创建了一个触发器。触发器基本上说：

如果 DELETE 操作：

• 将刚刚删除的所有小时数 (OLD) 乘以 -1
• 将小时数添加到 PENDING 表。

如果 INSERT 操作：

• 从 PENDING 表中删除与相同拆分匹配的所有小时
• EXPORTED 表中匹配相同拆分的总小时数。
• 将新插入的小时数 (NEW) 和已导出的小时数的增量添加到 PENDING 表。

然后，每当发生导出时，我们只需将 PENDING 表中的所有时间按拆分汇总，并附加到 EXPORTED 表。

那应该可以解决问题。我还将编写一个 UPDATE 触发器，以防应用程序确实在其他地方对 TIMECARD 表进行更新......只是为了确定。

更新 - 1 月 3 日 - 下午 5:45 MST

我阅读了闪回并得到了一个测试查询 - 它很快而且我喜欢这样一个事实，即没有任何机会被错过。我想我会用这个作为最终解决方案——但同时我会实现我所拥有的。我将设置另一个表来记录导出数据时的 TIMESTAMPS。这样，如果 DBA 增加可用的闪回空间 - 我已经记录了我的导出点。