MS SQL Server 2008 Instances

PROBLEM STATEMENT:

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 24x7 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.

SOLUTION:

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, andthe 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.

SQL Server 2008- Replication

Problem Statement:

An organization needs to configure replication whereby it is able to send different updates to different subscribers at different locations. It should replicate a certain set of information to one subscriber as well as another set to another subscriber. It is to be ensured that subscribers are able to work offline and later synchronize the modifications with the publisher.

Solution:

In this scenario, merge replication would be able to meet the required goal. Use merge replication typically to meet the following requirements:

Allow subscribers to modify information in an offline state and then replicate it back to the publisher. A subscriber can also replicate information to other subscribers. Provide data filtering. In data filtering, filter different updates to different subscribers at different locations. Allow information to be replicated to users who are mobile and are not always connected to the SQL Server. Provide the ability to resolve conflicts that occur during replication.