Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
30 Cards in this Set
- Front
- Back
|
What port is the database mirroring endpoint defined for?
|
5022
|
|
|
What are the three operating modes that you can configure for database mirroring?
|
high availability
high performance high safety |
|
|
What servers does high availability mode in database mirroring require?
|
A principal server, a mirror server and a witness server.
|
|
|
In High Availability mode, how is failure detected?
|
High Availability mode uses a ping between each instance participating in the Database mirroring session.
|
|
|
How are transactions handled in databases operating in high availability mirroring mode?
|
SQL Server first writes all transactions into memory buffers within the SQL Server memory space. The system writes out these memory buffers to the transaction log. When SQL Server writes a transaction to the transaction log, the system triggers Database Mirroring to begin transferring the transaction log rows for a given transaction to the mirror. When the application issues a commit for the transaction, the transaction is fi rst committed on the mirror database. An acknowledgment of the commit is sent back to the principal, which then enables the commit to be issued on the principal. At that point, the acknowledgment is sent back to the application, enabling it to continue processing. This process guarantees that all transactions are committed and hardened to the transaction log on both the principal and mirror databases before the commit is returned to the application.
|
|
|
What is a "split-brain" problem?
|
If the mirror were allowed to determine that it should serve the database by itself, it could introduce a situation whereby the database would be accessible to transactions on more than one server.
|
|
|
What are the general steps for automatic failure detection and failover in the High Availability operating mode?
|
1. The principal and mirror continuously ping each other.
2. The witness periodically pings both the principal and the mirror. 3. The principal fails. 4. The mirror detects the failure and makes a request to the witness to promote itself to the principal database. 5. The witness cannot ping the principal but can ping the mirror, so the witness agrees with the role reversal, and SQL Server promotes the mirror to the principal. 6. The principal server comes back online from the failure and detects that the mirror has been promoted to the principal. 7. SQL Server demotes the original principal to a mirror, and transactions begin flowing to this database to resynchronize it with the new principal. |
|
|
What is the High Performance operating mode?
|
High Performance operating mode uses a principal and a mirror database, but it does not need a witness server. This operating mode provides a warm standby confi guration that does not support automatic failure detection or automatic failover. High Performance operating mode does not automatically fail over because transactions are sent to the mirror asynchronously. Transactions are committed to the principal database and acknowledged to the application. A separate process constantly sends those transactions to the mirror, which introduces latency into the process. This latency prevents a Database Mirroring session from automatically failing over because the process cannot guarantee that the mirror has received all transactions when a failure occurs. Because the transfer is asynchronous, High Performance operating mode does not affect application performance, and you can have greater geographic separation between the principal and mirror. However, due to the data transfer being asynchronous, you can lose transactions in the event of a failure of the principal or when you force a failover of the mirroring session.
|
|
|
What is high safety operating mode?
|
High Safety operating mode transfers transactions synchronously, but it does not have a witness server. The synchronous transfer guarantees that all transactions committed at the principal are first committed at the mirror, and it requires the same performance considerations as the High Availability operating mode. However, the lack of a witness prevents automatic failover to the mirror if the principal fails. If the principal fails in High Safety operating mode, you must
promote the mirror manually to serve the database. |
|
|
One benefit of database mirroring is that it does not have caching issues, why is this?
|
In addition to sending transactions to the mirror, database mirroring performs periodic metadata transfers. The purpose of these metadata transfers is to cause the mirror to read pages into the data cache. This process maintains the cache on the mirror in a "semi-hot" state.
|
|
|
What is transparent client redirection in terms of database mirroring?
|
The new version of Microsoft Data Access Components (MDAC) that ships with Microsoft Visual Studio 2008 contains a Database Mirroring–related feature within the connection object called Transparent Client Redirect. When a client makes a connection to a principal, the connection object caches the principal as well as the mirror. This caching is transparent to the application, and developers do not need to write any code to implement this functionality.
|
|
|
Which database mirroring mode is not recommended for normal operations?
|
High Safety - its synchronous transfers have a high-performance impact without the benefit of automatic failover.
|
|
|
What is the global communications thread in database mirroring?
|
When you initiate a Database Mirroring session, Service Broker is enlisted to manage a global communications thread. The global communications thread is used to detect when a participant in the Database Mirroring session is not available so that failover can occur when configured in High Availability operating mode and status messages can be passed between the session members.
|
|
|
How many threads are created per database in database mirroring and what is their purpose?
|
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.
|
|
|
What is the purpose of the additional thread that a witness has in database mirroring?
|
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.
|
|
|
What are the four general steps to prepare for Database mirroring?
|
1. Ensure that databases are set to use the Full recovery model.
2. Back up the primary database. 3. Restore the database to the instance hosting the mirror database by using the NORECOVERY option. 4. Copy all necessary system objects to the instance hosting the mirror database. |
|
|
Why must you copy system objects when you configure Database mirroring?
|
It enables applications to function after a failover. The most common objects to transfer are logins, linked server, SQL Server Integration Services (SSIS) packages, SQL Server Agent jobs, custom error messages.
|
|
|
What can you use to transfer objects to the instance hosting the mirror database?
|
You can use SSIS, which includes the Transfer Logins task for transferring logins from one instance of SQL Server to another while keeping any passwords encrypted. SSIS also provides tasks for transferring SQL Server Agent jobs, error messages, and other types of objects.
|
|
|
If you do not remember the endpoint addresses when configuring database mirroring, what are two ways you can retrieve them?
|
-- You can query sys.database_mirroring_endpoints on each instance to get the endpoint address
-- You can start the Configure Database Mirroring Security Wizard by first right-clicking on the principal database and selecting Tasks, Mirroring. Then, in the Database Properties dialog box, click Configure Security and walk through each step. Because you have created the endpoints already, the wizard automatically retrieves information about them. When the wizard fi nalizes, it automatically enters the endpoint addresses into the appropriate fields for confi guring Database Mirroring. |
|
|
If your security access is defined using SQL Server logins, what additional action might you need to perform after failover to a mirrored database?
|
When you create a SQL Server login, a dummy security identifi er (SID) is generated. This SID is then used to link the user in a database to the login for a server. 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 that can grant elevated permissions to a login. In this case, you need to execute ALTER LOGIN to remap the logins. However, because the mirror is inaccessible, you can perform this step only after a failover has occurred.
|
|
|
How do you fail back from a forced failover?
|
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.
|
|
|
Which server objects are your primary concern in the event of a failover?
|
The instance containing the mirror database must also have all the logins present that are required for applications and users to connect to the database. If the applications use linked servers, they also need to exist to prevent application failures. Other server objects such as SSIS packages and jobs are not as critical during the failover and can generally be addressed shortly after a failover after all applications are online.
|
|
|
When does a forced failover occur?
|
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.
|
|
|
What command do you execute to cause a mirrored session to fail over in High Performance Mode?
|
ALTER DATABASE <database name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
|
|
|
What command to you execute to remove a mirroring session?
|
ALTER DATABASE <database name> SET PARTNER OFF;
|
|
|
What are the two modes that a secondary database participating in log shipping can be in?
|
Standby Mode or No Recovery Mode. When the secondary database is in Standby Mode, users can connect to and issue SELECT statements against the database. When the secondary database is in No Recovery Mode, users cannot connect to the database. In either mode, you can restore transaction logs to the secondary database. You cannot restore transaction logs when users are connected to the database, so you should not use Standby Mode for high-availability architectures.
|
|
|
What are the three components of log shipping?
|
The primary database is accessible and accepts transactions. The secondary database has a continuous chain of transaction logs applied to keep it up to date with the primary. The monitor instance sends alerts when the log shipping session is out of sync.
|
|
|
What happens to log shipping if the recovery model is changed to Simple?
|
Since log shipping depends on transaction log backup, this change would cause log shipping to cease functioning.
|
|
|
What affect would issuing a BACKUP LOG....WITH TRUNCATE_ONLY have on log shipping?
|
Since log shipping depends on transaction log backup, this change would cause log shipping to cease functioning.
|
|
|
What is the basic process for initializing log shipping?
|
1. Create a share on both the primary and secondary because the backups need to be accessed across servers.
2. Create jobs to back up transaction logs, copy logs to the secondary, and restore the logs. 3. Restore a full backup to the secondary database. 4. Restore all subsequent transaction logs. 5. Start up jobs to automate restoration of the logs. 6. Copy any instance-level objects upon which the secondary database depends to service applications. |
