Help
Options
Didn't know it?
click below
click below
Knew it?
click below
click below
Don't Know
Remaining cards (0)
Know
retry
shuffle
restart
0:00
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
70-432 - Unit 8
Implementing High Availability
| Question | Answer |
|---|---|
| What 4 Windows Server versions / editions support clustering and how many nodes does each support? | 1. Server 2003 Enterprise 4 2. Server 2003 Datacenter 8 3. Server 2008 Standard 2 4. Server 2008 Enterprise 16 |
| What is the term used for the database that contains all the configuration information for a cluster? | Quorum database |
| What is the difference between a standard windows cluster and a majority node set cluster? | Standard cluster - single quorum database Majority Node cluster - a copy of the quorum database is stored on each cluster node. This eliminates the quorum database as being a potential single point of failure. |
| What is the minimum number of nodes in a majority node cluster? | 3 |
| What 2 items must be done to support file system encryption in a clustered environment? | 1. Kerberos must be enabled 2. Cluster service account must be trusted |
| T or F: A cluster service account cannot use a regular windows user account. | True! It must have administrator privileges |
| T or F: IDE drives can be used in a clustering environment. | False - only SCSI or Fibre drives |
| What clustering configurations require only Fibre drives? | More than 2 nodes, Datacenter edition, or 64bit. SCSI is not accepted in these instances |
| What is the maximum number of drive letters available to a cluster? | 22 A, B, C, and D are allocated to local resources on each node |
| What is the filepath to the quorum? | %SystemRoot%\Cluster\QoN.%ResourceGUID%$\%ResourceGUID%$\MSCS |
| What should be done to server antivirus software to ensure it does not interfere with cluster operations? | Exlude the MSCS directory and all data file related directories |
| What is the name of the basic test that clustering uses to ensure the availability of another node? | LooksAlive test - ping test from one node to another |
| What networking step should be taken to prevent anomalous failovers? | Configure the public and private networks on separate subnets. |
| What 6 best practice steps should be taken for the private network in a clustering environment? | 1. Disable all services except TCP/IP 2. Remove the default gateway 3. Remove DNS server addresses 4. Disable DNS registration 5. Disable NetBIOS over TCP/IP 6. Disable LMHOSTS lookup |
| What service needs to be enabled to allow clustering health checks? | Remote Procedure Call (RPC) service |
| T or F: Physically attached storage (i.e. internal drives) can be configured in the cluster. | False - only Fibre channel cabinet drive arrays or a SAN can be configured |
| How many network connections are required for clustering? | Two - public traffice and private (Looksalive and other clustering related traffic) |
| T or F: Dynamic discs are not supported in a clustering environment. | True |
| What is the difference between a single instance cluster and a multiple instance cluster? | Single instance: 1 SQL instance per cluster Multi: Multiple SQL instances per cluster |
| What is the difference between Active/Active and Active/Passive? | Active/Active: Applications run on all nodes in a cluster Active/Passive: Applications run on a single node in the cluster |
| What should be done with the SQL Browser service? | If named instances exist, enable it. Otherwise, disable it. |
| What is the name of the test that clustering uses to ensure the availability of the SQL Server instance? What does the test do? | IsAlive test - creates a connection to SQL Server instance and issues: SELECT @@SERVERNAME |
| What type of Windows accounts and groups can you use with a SQL Server cluster instance? | Domain users and domain groups SIDs for accounts and groups must be resolvable across all nodes |
| A single drive letter may be used with how many clustered instances? | A drive can only be configured by 1 instance |
| In a clustered environment, the SQL Server service start-up type should be set how? | It should be set to manual as the Windows cluster needs control over the services. Automatic would cause errors |
| What 3 roles are involved in database mirroring and what does each one do? | 1. Principal role - serves databases to applications 2. Mirror role - in a recovering state and does not allow connections. Can source snapshots. 3. Witness Server - used for automatic failure detection and failover from a primary to mirror database |
| How many mirrors can a principal database have and vice versa? | A principal database server can have only 1 mirror. A mirror may have only 1 principal |
| T or F: Like a principal or mirror database, a witness server can monitor only one pair at a time. | False - it can handle multiple |
| What database mirroring table stores a row for every pair serviced by the witness server? | sys.database_mirroring_witness |
| What editions of SQL Server 2008 are required for a principal or mirror role? | 2008 Standard or Enterprise |
| What editions of SQL Server 2008 are required for a witness server role? | Any edition including SQL Server 2008 Express. |
| What TCP port serves as the default for database mirroring? | 5022 - recommended that you change this |
| Name the 3 database mirroring modes: | 1. High Availability 2. High Performance 3. High Safety |
| For the High Availability database mirroring mode: 1. How does it operate? 2. How does it perform? 3. What requirements does it have? 4. automatic failover? | 1. Commit issued to mirror transaction log first then principal trans log. Both must happen before next operation. 2. higher overhead - esp at greater distance 3. Requires witness server 4. Yes - if witness is enabled - uses ping to detect |
| For the High Performance database mirroring mode: 1. How does it operate? 2. How does it perform? 3. What requirements does it have? 4. automatic failover? | 1. Commit issued principal trans log. Transactions are committed to mirror asynchronously. 2. Does not affect performance due to asynch mirror commit 3. Does NOT require witness 4 No - warm standby - must be manually failed over - potential data |
| For the High Safety database mirroring mode: 1. How does it operate? 2. How does it perform? 3. What requirements does it have? 4. automatic failover? | 1. Commit issued to mirror transaction log first then principal trans log. Both must happen before next operation. 2. Same as High Avail 3. NO witness server 4. No - warm standby - must be manually failed over - no data loss |
| When should the High Safety database mirroring mode be used? | Only when replacing the existing witness server. Otherwise use High Avail |
| How is caching handled for database mirroring? | Metadata is transferred periodically so it is semi-up-to-date. |
| What DMV can be queried to retrieve endpoint information related to database mirroring? | sys.database_mirroring_endpoints |
| T or F: For database mirroring, the principal, mirror, and witness endpoints can all be defined on the same SQL Server instance. | False - they must all be on separate instances |
| What database mirroring endpoint role(s) are required for transactions to be exchanged between principal and mirror databases? | The endpoint must be created with a role of PARTNER or ALL. |
| What recovery model is required for initializing database mirroring? | Full Recovery Model |
| What are the 4 steps necessary to initialize database mirroring? | 1. Databases are set to Full recovery model 2. Back up primary database 3. Restore backup to the mirror instance using NORECOVERY option 4. Copy all req. system objects |
| T or F: Databases with a FILESTREAM filegroup cannot be configured for database mirroring. | True |
| What 5 common object types need to be copied from the principal to the mirrored database? | 1. Logins 2. Linked Servers 3. SSIS Packages 4. SQL Agent Jobs 5. Custom error messages Note: SSIS can be used to transfer many of these - linked servers must be manually recreated though. |
| If security access is based upon SQL Server logins rather than Windows accounts, what additional step might need to be performed after a failover has occurred? | An ALTER LOGIN command might need to be issued to remap the SIDs for the logins to match the order they were originally created in on the principal |
| What causes a forced failover? | Only happens in High Perf Mode: Principal fails while mirroring session is unsynchronized. Thus |
| What command should be issued to the database mirror to cause a session to fail over when operating in High Performance mode? | ALTER DATABASE <DB> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS; |
| What command should be issued to re-initialize database mirroring? | ALTER DATABASE <DB> SET PARTNER OFF; |
| Hod do you fail back from a forced failover? | If partners were synched at failover, apply a transaction log backup to the failed partner to catch up. If not synched, then you need to re-initialized database mirroring. |
| What command should be issued to the database mirror to cause a session to fail over when operating in High Availability mode? | ALTER DATABASE <DB> SET PARTNER FAILOVER; Note: This must be executed from the principal |
| Log shipping utilizes a chain of what kind of backups? | Transaction Log backups |
| When is log shipping typically used? | To maintain a reporting server or to sync up a principal an mirror in preparation for database mirroring. |
| When restoring a backup for log shipping, what 2 modes can be specified? | Standby Mode - allows select queries Or NORECOVERY |
| T or F: With log shipping it is possible to ship one database from Server A to Server B while shipping a different database from ServerB to Server A. | True - Log shipping is database based - not server based |
| What are the 3 roles involved in log shipping? | 1. Primary database 2. Secondary database 3. Monitor instance |
| What SQL Server service needs to be enabled in order for log shipping to function? | SQL Server Agent service Log shipping relies on stored procedures which configure jobs to perform the log shipping and maintain the log shipping tables. |
| If using maintenance plans to perform transaction log backups, what changes should you make prior to setting up log shipping? | Edit the maintenance plan to exclude the primary database |
| What 2 actions will break log shipping? | Changing the database recovery model to Simple & restoring a database to the secondary with the option WITH RECOVERY |
| Name the 7 most common instance-level objects that need to be copied to the secondary server instance while setting up log shipping for the first time? | 1. Security objects 2. Linked servers (manually recreated) 3. SSIS pakcages 4. Endpoints 5. SQL Server Agent objects 6. Instance-level DDL triggers 7. Replication |
| In order to transfer logins from one server instance to another, what SQL Server feature can be used? | SSIS |
| What additional step(s) might be necessary when re-creating linked servers? | Make sure that any ODBC and OLE DB providers are installed on the linked server |
| T or F: Log shipping contains a mechanism to detect a failure of the primary and automatically fail over to the secondary. | False |
| What are the 2 reasons that might require you to failback to the original primary after it is repaired and back online? | 1. Company policy 2. Performance degradation while operating on the original secondary |
| What is the name given to the basic building block of replication? Against what can it be defined? | Articles It can be defined against a table, view, stored procedure, or function |
| What is the name given to groupings of articles that define the replication set? | Publications |
| Name the 3 types of filters available for replication: | 1. Static Row Filter - WHERE State = 'TX' 2. Dynamic Row Filter - WHERE USERNAME = suser_name() 3. Join Filter - filters based on relationship with parent table |
| Name the 3 replication roles: What function does each one serve? | 1. Publisher - maintains master copy of data within a replication architecture 2. Distributor - main engine within a replication architecture 3. Subscriber - receives changes from the distributor |
| T or F: A subscriber is only allowed to receive data from one publisher. | False - a distributor can collect data from multiple publishers and push them to a single subscriber. A distributor can also take a single publisher and push them to multiple distributors |
| Name the 5 replication agents: What does each agent do? | 1 Snapshot - extracts schema and data from pub 2 Log Reader - extracts committed trans from trans log on pub 3 Distribution - applies snapshots and sends trans 4 Merge - applies snapshots to sub 5 Queue Reader - used only w/ queued updating option |
| Name the 3 replication methods: What does each method do? | 1. Snapshot - sends full data set each replication cycle 2. Transactional - begins with snapshot - applies transaction 3. Merge - begins with snapshot - pub & sub not always connected |
| Which 2 replication agents does the Snapshot replication method use? What 4 options does it have that relate to the subscriber? When is it normally used? | Agents: Snapshot & Distribution Options: 1 Drop & recreate 2 Keep existing table unchanged 3 Delete data matching incoming snapshot 4 Leave table structure in tact Use: NOT High Availability - trans b/w snapshots not saved. Causes outages on Su |
| What 2 options does Transactional replication have that relate to the subscriber? When is it normally used? What 2 alternative architectures does it support? | Options: 1. Immediate updating subscribers 2. Queued updating subscribers Use: High Availability or Reporting Arch: bidrectional and peer to peer |
| When is merge replication normally used? What makes it unique? | Use: Normally used for mobile, disconnected processing. Not as optimized as Trans w/ queued subscribers Unique: designed to enable changes at both publisher and subscriber by default |
| In what 4 applications of replication can data conflicts occur? | 1. Merge Replication 2. Transaction replication w/ queued updating 3. Bidirectional transactional replication 4. peer-to-peer transactional replication |
| What 3 types of data conflicts can occur with replication? | 1. Duplicate primary key 2. Conflicting update 3. Update a nonexistent row |
| What are the 2 most common conflict resolvers in SQL Server? | 1. Publisher always wins 2. Subscriber always wins |
| Name the 5 minimally logged transactions? What 3 apply to replication? How does the recovery model affect replication for these transactions? | 1. Create Index 2. Truncate Table *R* 3. Bulk Insert *R* 4. BCP *R* 5. Select ... Into When the recovery model is Simple or Bulk-logged, replication will not pick up changes to these 3 operations. |
| In transactional replication, what does the immediate updating subscriber option do? | A change made at 1 subscriber is immediately pushed up to the publisher and then replicated back to all other subscribers (except for the one where the change was made) Incompatible with high availability since trans fails if publisher is not available |
| In transactional replication, what does the queued updating subscriber option do? | A change made at a subscriber is kept in a queue to be pushed back to the publisher. To mitigate data conflicts, changes are applied to other subscribers during a failure scenario |
| T or F: The transactional replication immediate updating subscriber option and queued updating subscriber can be used simultaneously. | True! Queued can be used as a failover mechanism when the publisher is not available. |
| What edition(s) of SQL Server are required to implement peer-to-peer transactional replication? | Enterprise only |
| Which transactional replication architecture provides better performance: peer-to-peer or bidirectional? | Bidirectional |
| What special transaction is used for transactional replication that provides timing statistics? | Tracer token |
| What 2 stored procedures validate that the publisher and subscriber are synchronized? | sp_publication_validation runs sp_article_validation for all articles in a publication |
| T or F: Merge replication replicates only the most recent version of a changed record. | True |
| What 4 tables in the distribution database are used for transactional replication? | 1. MSlogreader_history - tracks LSNs 2. MSrepl_commands 3. MSrepl_transactions 4. MSdistribution_history |
| What 3 tables are used by Merge Replication? What 2 tables are used to determine changes needed between publisher and subscriber? | 1. MSmerge_contents ** 2. MSmerge_genhistory ** 3. MSmerge_tombstone |
| What 2 stored procedures can be executed to validate merge replication? | sp_validatemergepublication - entire publication sp_validatemergesubscription - single subscriber |
| Which type of replication is most reliable when outages take place? Why? | Merge replication is more reliable because a full history is maintained by both the publisher and subscriber |
| How many database mirroring endpoints are required? | 1 per instance of SQL Server |
| T or F: Reconfiguring an in-use database mirroring endpoint is a safe activity for a DBA. | FALSE If the endpoint is reconfigured, it might restart, which can appear to be an error to the other server instances. This is particularly important in high-safety mode with automatic failover as this could cause it to failover. |
| In order to configure encryption for a failover cluster instance on a domain, what must you do? | Obtain a FQDN server certificate (e.g. SQLFailOverCluster.domain.com) for the machines SQL1.domain.com, SQL2.domain.com and SQL3.domain.com |
| What 3 or 4 SQL Agent Jobs are required for log shipping? | 1. Backup Job - Primary 2. Copy Job - Secondary 3. Restore Job - Secondary 4. Alert Job - Monitor Server (if used) |
| To add or remove SQL Server 2008 nodes in a failover cluster, what must you run? | SQL Server Setup |
| What 2 permissions must you have to be able to transfer an object from one schema to another? | CONTROL permission on the object being transferred and ALTER permission on the target schema |
| How many days is the default retention period for a replication publication? | 14 days |
| How many days after the replication publication expiration do you have to reinitialize a subscription? At what point is the subscription dropped? | - 14-27 days you re-initialize - Day 28 the push subscription is dropped (a pull subscription is still retained) |
| T or F: Transactional replication sends changes to the reporting server without allowing changes to be sent back to the publisher. | True |
| T or F: Replication supports automatic failover. | False - it requires manual detection and manual failover. |
| Automatic failover (hot standby) is supported by what high availability features? | Failover clustering and Database Mirroring (when in High Availability mode) |
| Which high availability feature allows for automatic failover without affecting performance? | Failover clustering - Database mirroring can fail over to the mirror automatically. However, transactions have to be committed on both the principal and mirror, which affects the performance of applications. |
| The development team wants to use Transact-SQL (T-SQL) statements to create the business logic required to solve merge replication conflicts. How should you implement this solution? | Use the Microsoft COM-based Stored Procedure conflict resolver. |
| What is the average failover time for failover clustering vs database mirroring? | Failover Clusters: 10-15 seconds Database Mirroring: 1-3 seconds |
| Upon creation of an endpoint, what is its default state? | Stopped |
| T or F: Check constraints can be configured as NOT FOR REPLICATION. | True - also foreign key constraints |
| Until yesterday, the log shipping implementation was working fine. However, some changes made yesterday caused the log shipping process to stop working. What might the problem be? | Backing up the transaction log of the primary database as well as changing the SQL Server Agent service account could break log shipping |
| A reporting server needs a duplicate copy of the database. Users must be able to retrieve data from this database at any time without being locked out by another process. The data can be as much as 30 minutes old. What technology is needed? | Replication - log shipping doesn't allow users to be connected during the restore. Since report users need to be constant and data can only be 30 min old, replication is needed. |
| You are implementing a log shipping solution that will copy information from five primary servers into a single secondary server. The secondary server demands different paths for the database data and log files due to space. Best method to initialize? | Use SQL Server Management Studio (SSMS) and use alternate folders to restore each secondary database. |
| T or F: The Log Shipping Wizard is an acceptable method for configuring log shipping? | False - this dates back to SQL 2000. SQL Server Management Studio (SSMS) has replaced this wizard. |
| T or F: Database mirroring does not change the instance name. | False - during the failover the server name does in fact change. This can break applications until connections are moved and re-created. |
Created by:
jkconnections