Exam 70-458 – Objective 3 – Implement high availability

Implement AlwaysOn

• Implement a mirroring solution using AlwaysOn
  • Availability modes (http://technet.microsoft.com/en-us/library/ff877931.aspx)
    • Asynchronous-commit mode
    • Synchronous-commit mode
  • Steps to perform
    • Create mirroring endpoint (http://technet.microsoft.com/en-us/library/ff878259.aspx)
    • Enable AlwaysON (http://technet.microsoft.com/en-us/library/ff878259.aspx)
    • Create an availability group (http://technet.microsoft.com/en-us/library/ff878307.aspx)
      • ALTER AVAILABILITY GROUP GroupA ADD DATABASE DB
    • Create an availability group listener (http://msdn.microsoft.com/en-us/library/hh213080.aspx)
    • Add a secondary replica (http://technet.microsoft.com/en-us/library/hh213078.aspx)
      • ALTER DATABASE db SET HADR AVAILABILITY GROUP = GroupA (http://technet.microsoft.com/en-us/library/ff878535.aspx)
  • Troubleshoot configuration (http://technet.microsoft.com/en-us/library/ff878308.aspx)
• Failover (http://technet.microsoft.com/en-us/library/hh213151.aspx)
  • Modes
    • Automatic – only available if using synchronous-commit availability mode on a replica
    • Planned manual failover – perfomed when databases are SYNCHRONIZED
    • Forced manual failover – performed when databases are not SYNCHRONIZED

Implement database mirroring (http://technet.microsoft.com/en-us/library/ms189852.aspx)

• Set up mirroring (http://technet.microsoft.com/en-us/library/ms190941.aspx)
  • Create endpoint
  • Perform FULL backup
  • Perform LOG Backup
  • Restore on secondary FULL and LOG with NORECOVERY
  • SET PARTNER servers
• Monitor the performance of database mirroring (http://technet.microsoft.com/en-us/library/ms190030.aspx)

Implement replication (http://technet.microsoft.com/en-us/library/ms151198.aspx)

• Troubleshoot replication problems (http://technet.microsoft.com/en-us/library/ms151756(v=sql.105).aspx)
  
• Identify appropriate replication strategy (http://technet.microsoft.com/en-us/library/ms152531.aspx)
  • Snapshot –  when data changes are substantial but infrequent (http://technet.microsoft.com/en-us/library/ms151832.aspx)
    • Data changes infrequently.
    • Replicating small volumes of data.
    • A large volume of changes occurs over a short period of time.
  • Transactional (http://technet.microsoft.com/en-us/library/ms151176.aspx)
    • You want incremental changes to be propagated to Subscribers as they occur.
    • The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
    • The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
    • The Publisher has a very high volume of insert, update, and delete activity.
    • The Publisher or Subscriber is a non-SQL Server database, such as Oracle.
  • Peer-to-peer (http://technet.microsoft.com/en-us/library/ms151196.aspx)
    • Catalog queries and other reads are spread across multiple nodes. This enables performance to remain consistent as reads increase.
    • If one of the nodes in the system fails, an application layer can redirect the writes for that node to another node. This maintains availability.
    • If a node requires maintenance or the whole system requires an upgrade, each node can be taken offline and added back to the system without affecting the availability of the application.
  • Merge (http://technet.microsoft.com/en-us/library/ms152746.aspx)
    • Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
    • Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
    • Each Subscriber requires a different partition of data.
    • Conflicts might occur and, when they do, you need the ability to detect and resolve them.
    • The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher to reflect the net data change (that is, the fifth value).

Preparation resources

• AlwaysOn Availability Groups (SQL Server)
• Microsoft SQL Server AlwaysOn solutions guide for high availability and disaster recovery
• AlwaysOn architecture guide: Building a high availability and disaster recovery solution by using AlwaysOn Availability Groups

Click here to go back to main page for exam 70-458.