Lesson 6: Removing Database Mirroring in VS .NET

Creator PDF-417 2d barcode in VS .NET Lesson 6: Removing Database Mirroring

After it is configured, you should remove database mirroring only in extreme cases. By removing database mirroring, you terminate all data transfer between the principal and the mirror databases. In this lesson, you learn when to remove database mirroring and how to perform this task.

After this lesson, you will be able to:

You should remove database mirroring only under the following circumstances:

You no longer want to mirror a database. The principal has been damaged to such an extent that it would be easier to reinitialize the entire environment.

You can remove a database mirroring session by clicking Stop Mirroring within the Database Properties, Mirroring page for the principal database. This step immediately terminates the database mirroring session. When you click Stop Mirroring, the GUI issues the following single command to terminate the database mirroring session:

Instead of using the GUI, you can alternatively issue this command at either the principal or mirror database to remove database mirroring.

How do you terminate a database mirroring session You can immediately terminate a database mirroring session by clicking Stop Mirroring on the Database Properties, Mirroring page or issuing a single ALTER DATABASE command from either the principal or the mirror database.

17

In this exercise, you will practice removing database mirroring. 1. Right-click the AdventureWorks database on the principal instance and choose Properties. 2. In the Mirroring page, click Stop Mirroring.

Clicking Stop Mirroring in the GUI is equivalent to issuing the ALTER DATABASE AdventureWorks SET PARTNER OFF Transact-SQL command from either the principal or the mirror database.

Remove a database mirroring session only in an extreme case, such as when the principal becomes damaged beyond repair, or if you have decided that database mirroring is no longer needed. To remove database mirroring, you can click Stop Mirroring on the Mirroring page or issue an ALTER DATABASE command from either the principal or the mirror.

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

Answers to these questions and explanations of why each answer choice is right or wrong are located in the Answers section at the end of the book.

1. To terminate the database mirroring session, you can issue the ALTER DATABASE command against which partners (Choose all that apply.) A. Principal B. Mirror C. Witness D. Distributor

Review the chapter summary. Review the list of key terms introduced in this chapter. Complete the case scenario. This scenario sets up a real-world situation involving the topics of this chapter and asks you to create solutions. Complete the suggested practices. Take a practice test.

Summary

Database mirroring provides a major new technology for achieving high availability for your databases. Data is securely synchronized between two databases a principal database and a mirror database on separate instances of SQL Server. When configured in High Availability operating mode, database mirroring synchronously transfers transactions between the principal and mirror databases. This operating mode requires a witness server, which arbitrates the automatic failover to the mirror when the principal becomes unavailable. When configured in High Performance operating mode, database mirroring asynchronously transfers transactions between the principal and mirror databases. Because of the asynchronous transfer, application performance is not affected, but the failover to the mirror requires manual intervention. Perform manual failover at the mirror server by issuing a single ALTER DATABASE statement with the FORCE_SERVICE_ALLOW_DATA_LOSS option. Removing database mirroring is an easy step within the GUI or via the TransactSQL ALTER DATABASE statement, but it should be done only in extreme cases or if you do not want to mirror a database any more.