- Exam 70-458 – Objective 1 – Manage Data
- Exam 70-458 – Objective 2 – Implement Security
- Exam 70-458 – Objective 3 – Implement high availability
- Exam 70-458 – Objective 4 – Design and implement a data warehouse
- Exam 70-458 – Objective 5 – Extract and transform data
- Exam 70-458 – Objective 6 – Load data
- Exam 70-458 – Objective 7 – Configure and deploy SSIS solutions
- Exam 70-458 – Objective 8 – Build Data Quality solutions
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)
- Availability modes (http://technet.microsoft.com/en-us/library/ff877931.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
- Modes
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).
- Snapshot – when data changes are substantial but infrequent (http://technet.microsoft.com/en-us/library/ms151832.aspx)
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.