SQL Server 对账：踏上校验之路-猿码集

一、SQL Server 对账的重要性

在企业中，财务对账是重要的一环。而对于数据库管理员来说，对数据库进行对账同样重要。因为数据库中的数据可能会因为各种原因出现异常，而异常的数据如果不及时处理，可能会影响企业的正常运营。

SQL Server 是微软公司推出的关系型数据库管理系统，在企业中广泛应用。因此，对 SQL Server 进行对账尤其重要。常见的 SQL Server 对账方式有如下几种：

1. 数据库备份的对账

备份文件的完整性对于平安可靠的数据恢复非常重要。一般数据库管理员都会制定备份计划来定期备份数据库，业内常用的备份方法有：完整备份、差异备份和日志备份。在备份数据库之后，需要对备份文件进行对账，以确保备份文件没有被损坏或被篡改。


SELECT 
    mf.physical_device_name, 
    mf.device_type, 
    mf.is_read_only, 
    bs.backup_size/1024/1024 as BackupSizeMB, 
    bs.compressed_backup_size/1024/1024 as CompressedBackupSizeMB, 
    bs.first_lsn, 
    bs.last_lsn, 
    bs.checkpoint_lsn, 
    bs.database_backup_lsn, 
    bs.backup_start_date, 
    bs.backup_finish_date, 
    bs.expiration_date, 
    bs.backup_set_id, 
    bs.server_name, 
    bs.machine_name, 
    bs.recovery_model, 
    bs.has_backup_checksums, 
    bs.is_copy_only 
FROM 
    [msdb].dbo.backupset bs 
    INNER JOIN [msdb].dbo.backupmediafamily mf ON bs.media_set_id = mf.media_set_id 
WHERE 
    bs.database_name = 'AdventureWorks' 
ORDER BY 
    bs.backup_start_date DESC

上面的 SQL 语句可以列出 AdventureWorks 数据库的备份文件列表，包括备份设备的物理名称、备份大小、备份起止时间等信息。这些信息可以对数据库备份文件进行对账。

2. 数据库事务的对账

事务是 SQL Server 数据库的核心概念之一，对 SQL Server 进行事务的对账也非常重要。对于企业而言，事务的一致性、可靠性非常重要，因此在保证高并发的同时，需要保证事务的可靠性。一般情况下，通过事务的回滚和提交来保证事务的一致性。

以下 SQL 语句可以查询数据库中的所有事务，并列出事务的提交时间、事务状态等信息：

SELECT a.session_id, a.transaction_id, a.transaction_begin_time, a.name AS transaction_name, a.transaction_type, a.transaction_state, t.transaction_sequence_num, t.first_lsn, t.last_lsn, t.transaction_begin_lsn, t.transaction_end_lsn, t.commit_lsn, t.last_commit_time FROM sys.dm_tran_active_transactions a -- 活动事务 INNER JOIN sys.dm_tran_database_transactions t ON a.transaction_id = t.transaction_id -- 事务的 LSN 信息 ORDER BY transaction_begin_time ASC;

管理员可以根据查询结果，对事务 log 进行再次验证，以保证事务执行是正确的，数据的一致性得到了有效的保障。

3. 数据库表数据的对账

数据是企业中最重要的财务资产之一，因此需要保证数据的完整性和可靠性。对表中的数据进行对账，可以做到最大程度地发现和排除可能存在的数据异常问题。

以下 SQL 语句可以查询 AdventureWorks 数据库的 SalesOrderHeader 表的行数、平均订单值：


SELECT 
    COUNT(*) as TotalRecords, 
    AVG(TotalDue) as AverageOrderValue 
FROM 
    Sales.SalesOrderHeader;

对查询结果进行检查，可以发现表的数据是否正常，如记录数是否正确，平均值是否在合理范围内等。

二、SQL Server 对账的实施步骤

SQL Server 对账的实施步骤如下：

1. 要进行对账的 SQL Server 实例的备份文件

在进行对账之前，需要进行数据库备份，以便在对账出现问题的时候进行数据恢复。

以下 SQL 语句可以备份 AdventureWorks 数据库：

BACKUP DATABASE AdventureWorks TO DISK='d:\\SQLServerBackups\\AdventureWorks.bak'

其中，BACKUP DATABASE AdventureWorks 表示备份 AdventureWorks 数据库，TO DISK='d:\\SQLServerBackups\\AdventureWorks.bak' 表示备份到盘符为 d 的磁盘中。

2. 对备份文件进行哈希值计算并保存

计算哈希值可以保证备份文件不被篡改。以下 SQL 语句可以计算 AdventureWorks 数据库的备份文件的哈希值：

EXEC xp_filehash 'd:\\SQLServerBackups\\AdventureWorks.bak';

其中，d:\\SQLServerBackups\\AdventureWorks.bak 表示 AdventureWorks 数据库的备份文件路径。

3. 监控 SQL Server 事务

以下 SQL 语句可以监控 SQL Server 实例中的所有事务：

SELECT nr.spid, nr.blocked, nr.dbname, nr.hostname, nr.program_name, er.blocking_session_id, nr.client_net_address, nr.nt_username, er.request_id, nr.open_tran, er.start_time, er.command, er.database_id, nr.cpu_time, nr.elapsed_time, nr.logical_reads, nr.reads, nr.writes, er.status, er.transaction_isolation_level, er.wait_type, er.wait_time, er.wait_resource, SUBSTRING(st.text, (er.statement_start_offset/2)+1, (CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END - er.statement_start_offset)/2 + 1) AS statement_text FROM sys.dm_exec_requests er LEFT JOIN sys.dm_exec_sessions nr ON er.session_id = nr.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st WHERE er.session_id != @@SPID AND er.session_id > 50 AND nr.dbname = 'AdventureWorks' ORDER BY er.start_time;

这个查询结果会列出所有正在执行的事务，同时会显示事务执行 SQL 语句的相关信息。

4. 对表数据进行校验

以下 SQL 语句可以检验 AdventureWorks 数据库中的 SalesOrderHeader 表：


SELECT 
    COUNT(*) as TotalRecords, 
    AVG(TotalDue) as AverageOrderValue 
FROM 
    Sales.SalesOrderHeader;

根据查询结果，管理员可以检查表中的数据是否正常，如记录数是否正确，平均值是否在合理范围内等。

三、总结

SQL Server 对账是企业保障数据安全、数据质量、避免财务纠纷的有效措施。对数据库备份文件、事务、表数据进行校验可以有效保障 SQL Server 数据库的安全可靠性，减少数据丢失和数据异常情况的发生。

SQL Server 对账：踏上校验之路