You maintain SQL Server 2008 instances for a large global manufacturer. You currently have two SQL Server 2008 instances, SQL01 and SQL02.
SQL01 contains several large production databases that are directly accessed by users, and also contains numerous third-party applications.
The Manufacturing database is used 24×7 to support all activities related to the manufacturing process. The database uses the full recovery model.
You want to provide failover capability for the Manufacturing database. You also need to provide read-only reporting access to manufacturing details. The data is used for reports and does not have to be the most recent data, but it must be readily available and current.
You want to accomplish this with the least effort possible and allow for minimal ongoing administration effort.
Which action should you take?
A. Periodically use the Import and Export Wizard in BIDS to transfer the needed data from SQL01 to SQL02 to be used for reporting.
B. Implement a partitioned view of the data and provide query access to the view.
C. Implement database mirroring with SQL01 as the principal server and SQL02 as the mirror server. Periodically create a snapshot of the mirror database for reporting.
D. Configure log shipping.
Correct Answer: C
Explanation/Reference:
Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.
In the curren scenario, you should implement database mirroring with SQL01 as the principal server and SQL02 as the mirror server, and periodically create a snapshot of the mirror database for reporting. Database mirroring provides failover capability by maintaining two separate database copies on different SQL Server instances.
The principal server contains the currently used data, and the mirror server maintains a copy of the data that can be used if the principal server fails. To implement mirroring, you back up the database on the principal and restore it to the mirror server with no recovery.
Although clients cannot directly access the mirror database, you can create a database snapshot on the mirror database and provide read-only access on the snapshot. Clients would be able to access the data as it existed when the snapshot was taken to produce reports. This solution would meet the failover and reporting requirements in this scenario.
You should not configure log shipping. Log shipping would be more difficult to administer than using database mirroring with a snapshot on the mirror. With log shipping, transaction log backups from a primary database are automatically transferred to another database and then applied.
You should not implement a partitioned view of the data and provide query access to the view because this would not meet the failover requirements in this scenario. Partitioned views are used when you have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views are implemented by creating a view that queries several tables and combines the results using the UNION ALL operator. A partitioned view can be implemented across multiple servers to improve performance and increase data availability, and to make a database solution more scalable. This decentralizes the processing load and administration effort to individual servers, but the view provides for collective data access.
You should not periodically use the Import and Export Wizard in BIDS to transfer the needed data from SQL01 to SQL02 to be used for reporting.
Although you could transfer information to another server instance and use the data for reporting purposes, this would not provide the failover capability required in this scenario. The Import and Export Wizard creates SSIS packages to perform the data transfer tasks. These packages can be saved and reused.
