Home > Workload Solutions > SQL Server > White Papers > Dell PowerMax 2500 and 8500 Best Practices for Mission Critical SQL Server Databases > Introduction
SQL Server is an ACID-compliant database technology, which means that the database integrity is preserved even if a host power outage occurs (system crash). SQL Server uses the database transaction log to record all database updates and persist the log records before the updates are made or a transaction is committed. If a transaction fails, during a host power outage, the transaction log has the information necessary to return the database to a consistent state that includes all committed transactions.
When the database starts after a failure, outstanding committed transactions in the transaction log are applied, and uncommitted transactions are rolled back, allowing the database to regain its consistency as of the time of the power outage. This procedure occurs automatically during database startup. Users can access the database for transactions only when the process completes and the database is consistent.
For more information about the SQL Server transaction log, see The Transaction Log (SQL Server).
This core resiliency of SQL Server is why consistent storage replications such as PowerMax snapshots and clones are so useful and easy to use. When SnapVX creates a consistent snapshot of the SQL Server database, the snapshot contains all database and log files. When that snapshot is used, SQL Server identifies its files in a state similar to a power outage and performs the “crash recovery” process of regaining database consistency as of the time of the outage, which is the exact time the snapshot was taken.
No additional API-type integration is required between SQL Server and storage replications to create point-in-time (also known as “restartable”) copies of the database. Because SnapVX can be set to create automatic snapshots in short intervals or on-demand, it can serve as a way of protecting the production database, or as an easy and efficient way to create database point-in-time copies.
Note: Do not confuse point-in-time copies of the SQL Server databases and a database backup. While SnapVX can create point-in-time copies without any further integration with the SQL Server database, a valid database backup requires an API integration such as VDI. SQL Server backups can be created using Dell AppSync and PowerProtect product lines. In addition, SQL Server 2022 allows T-SQL snapshot backups.
An important feature of the SQL Server database is the Attach command, which allows the SQL Server instance to add a database copy. The command lets the database administrator specify the location of the database and transaction log files and allows attaching the database copy with a different name than the original.
Using the Attach command, the DBA can mount multiple copies of the same database with different names to a single instance and point to new locations of the database files. Whether the source database uses drive letters, mount-points, CSV (on Windows), or the EXT4 and XFS directory structure (on Linux), the snapshot can be mounted to a host with a new database name and using a different choice of file-placement. This powerful ability of the SQL Server database works well with PowerMax replications.
Although the SQL Server instance might contain many user databases, each instance has its own set of database and transaction log files, therefore, it is a stand-alone, self-contained entity. The PowerMax system can operate separately on each database if the storage volumes are not mixed. A storage snapshot can be created, restored, or mounted to another instance, with any combination of one or more of the databases.