Term
| database mirroring VS failover clustering |
|
Definition
| Mirroring is limited to specific databases, clusters involve the entire instance. p.451 |
|
|
Term
| What setting enables FILESTREAM data in Mirroring? |
|
Definition
| Mirroring CANNOT be configured against databases that have a FILESTREAM filegroup. p.451, 464 |
|
|
Term
| Name the 3 database mirroring roles |
|
Definition
| principal, mirror, witness server p.452 |
|
|
Term
|
Definition
| an instance has a database that allows transactions to be processed against it. Ex, the principal database. p.453 |
|
|
Term
| What state is the mirror database in? |
|
Definition
|
|
Term
| What is the max number of connections allowed on a mirroring database? |
|
Definition
| The mirroring database is in a recovering mode. It does not accept any connections or transactions. p.543 |
|
|
Term
| What is the only way to gain read access to the mirroring database? |
|
Definition
| A snapshot can give users read-only access to the database's data at a specific point in time. p. 456. |
|
|
Term
| What is the purpose of the witness server? |
|
Definition
| Ensures that the database can be served on only one SQL Server instance at a time and initiates the failover. p.453 |
|
|
Term
| Cardinality of Principal, Mirror, and Witness. |
|
Definition
Principal and Mirror are 1:1
Witness can service multiple pairs. p.453 |
|
|
Term
| What level of role is the principal, mirror, and witness? |
|
Definition
| principal and mirror are database level roles. Witness is an server level role. p.455 |
|
|
Term
| What editions of SQL Server is mirroring compatible with? |
|
Definition
| Standard & Enterprise. Witness can run on any version including Express. p.454 |
|
|
Term
| What endpoint is configured for mirroring? |
|
Definition
| TCP with a payload of DATABASE_MIRRORING. P.454 |
|
|
Term
| Default mirroring endpoint is set to port ____? |
|
Definition
5022
{mirror with lice-nun} |
|
|
Term
| The service account must have what on each mirroring instance? |
|
Definition
| Access to the SQL Server and CONNECT TO authority on the database mirroring endpoint. p.454 |
|
|
Term
| Draw a database mirroring configuration. |
|
Definition
|
|
Term
| On a multiple instance server, what endpoint needs to be specified? |
|
Definition
| Each instance needs its own endpoint with a unique port number. p.454 |
|
|
Term
| What are the 3 operating modes available to mirroring? |
|
Definition
| high availability, high performance, and high safety. p.455 |
|
|
Term
|
Definition
| Consists of a principal, mirror, and witness with automatic failover. Synchronous data transfer. p.462 |
|
|
Term
|
Definition
| Consists of principal and a mirror with manual failover. Asynchronous data transfer. p.462 |
|
|
Term
|
Definition
| Consists of principal and a mirror with manual failover. Synchronous data transfer. p.462 |
|
|
Term
|
Definition
| Available for connections and enables data modifications. p.462 |
|
|
Term
|
Definition
| Inaccessible to connections and receives transactions issued from the principal. p.462 |
|
|
Term
|
Definition
| Only used with high availability mode to arbitrate a failover. p.462 |
|
|
Term
| Synchronous data transfer |
|
Definition
Transactions sent to memory. Memory to principal log. System trigger sends transactions to mirror.
Application commits, mirror returns commit, principal returns commit to application. p.455 |
|
|
Term
| Which mirroring mode is best when principal and mirror are in close proximity? |
|
Definition
| High availability due to Synchronous data transfer. p.455 |
|
|
Term
|
Definition
| A runaway transaction can cause the database to become inaccessible; thus failing the ping test. OR if a large number of mirrors exist on a single instance, the ping test can saturate the network; thus failing. p.456 |
|
|
Term
| What quorum must agree for an automatic mirror failover to occur. |
|
Definition
| The witness and mirror must agree that the principal is inaccessible. p.456 |
|
|
Term
| Asynchronous data transfer. |
|
Definition
| Transactions are committed to the principal and acknowledged to the application. A separate process constantly sends the transactions to the mirror, which introduces latency. p.457 |
|
|
Term
| When is the high safety mode used? Why? |
|
Definition
| only when the mirror server needs to be serviced. Because it does not offer the benefit of automatic failover. p.458 |
|
|
Term
| What is the cache advantage for mirroring? |
|
Definition
| In addition to transactions, the cache is also transferred so the mirror is ready to go. p.458 |
|
|
Term
|
Definition
CREATE DATABASE AdventWorksSnap ON(
NAME = ..., FILENAME = ...)
AS SNAPSHOT OF AdventureWorks; |
|
|
Term
| Which mirroring role does not require a TCP endpoint? |
|
Definition
| principal, mirror, & witness ALL require a TCP endpoint. If all 3 exist on the same server, they all need a different port number. p.461 |
|
|
Term
| When creating a TCP endpoint what is the default value for STATE? |
|
Definition
| stopped. It needs to be set to STARTED to allow connections to be created. p.461 |
|
|
Term
| When creating a TCP endpoint what value is ROLE set to? |
|
Definition
PARTNER or ALL for principal & mirror.
WITNESS for witness. P.461/463 |
|
|
Term
| List the 4 general steps to initialize database mirroring. |
|
Definition
1. Both databases are set to Full recovery model.
2. Back up the primary database.
3. Restore mirror from primary backup & NORECOVERY.
4. Copy all necessary system objects to mirror. ie logins, linked servers, SSIS packages. p.464 |
|
|
Term
What 3 items must be true to execute:
ALTER DATABASE AdventureWorks SET PARTNER FAILOVER |
|
Definition
| The mirroring session is synchronized, in High Availability mode, and is executed from the principal. p.474 |
|
|
Term
| SQL to force a failover for High Performance mode, or when the principal is unavailable. |
|
Definition
| From mirror: ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS p.474 |
|
|
Term
|
Definition
| Process of bringing the primary back from mirroring after a failover. p.473 |
|
|
Term
| Summarize Failback After Graceful Failover |
|
Definition
If no transaction log backups have occurred since failover, gracefully failover via SET PARTNER FAILOVER. Else:
1. Stop transaction log backups on principal.
2. Bring failed partner back online.
3. Restore all transaction log backups keeping NORECOVERY.
4. Restart transaction log backups.
5. When the 2 system are back on synch, gracefully failover. p.473 |
|
|
Term
| Transparent Client Redirect |
|
Definition
| Functionality built into the new MDAC connection library that ships with VS 2005. Allows principal and mirror connections to be cached in the connection object. Failure of the principal causes a redirect of the client connection to the mirror without intervention or custom coding. p.559 |
|
|
Term
| How many threads are created per database in database mirroring and what is their purpose? |
|
Definition
| One thread per database participating in Database Mirroring sessions is created on the instance. The purpose of the database threads is to exchange messages between the principal and the mirror, such as transactions and acknowledgments. On the mirror, one thread per database is opened to manage the process of writing log records and maintaining the query and data caches. |
|
|
Term
| What is the purpose of the additional thread that a witness has in database mirroring? |
|
Definition
| It is used to manage all the messages between the witness and participating principal/mirror sessions. The primary messages that are sent on the witness thread are state changes of the principal/mirror and failover requests. |
|
|
Term
| If you do not remember the endpoint addresses when configuring database mirroring, what are two ways you can retrieve them? |
|
Definition
1. sys.database_mirroring_endpoints on each instance.
2. Configure Database Mirroring Security Wizard on the principal database. |
|
|
Term
| If your security access is defined using SQL Server logins, what additional action might you need to perform after failover to a mirrored database? |
|
Definition
| If you do not re-create the SQL Server logins in the same order that you created them on the principal, a mismatch between the login and the user in the database occurs. ALTER LOGIN to remap the logins. |
|
|
Term
| How do you fail back from a forced failover? |
|
Definition
| If the partners were synchronized at the time of the failover, you can apply transaction log backups to roll the failed partner forward in time, and then Database Mirroring finishes the resynchronization process. If the partners were not synchronized at the time of the failover, you need to remove mirroring and reinitialize. |
|
|
Term
| When does a forced failover occur? |
|
Definition
| When the principal fails while the mirroring session is in an unsynchronized state, causing transactions that were committed on the principal to become lost. This is only possible for the High Performance operating mode. |
|
|
Term
| What command to you execute to remove a mirroring session? |
|
Definition
| ALTER DATABASE SET PARTNER OFF |
|
|
