- 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
Configure and maintain a backup strategy
- Manage different backup models, including point in time recovery
- Recovery models (http://technet.microsoft.com/en-us/library/ms189275.aspx)
- FULL
- SIMPLE – No point in time recovery
- BULK-LOGGED – Can recover to last backup, no point in time recovery
- Should take transaction log backup before and after
- Recovery models (http://technet.microsoft.com/en-us/library/ms189275.aspx)
- Protect customer data even if backup media is lost
- Possible media errors (http://technet.microsoft.com/en-us/library/ms189055.aspx)
- CONTINUE_AFTER_ERROR (http://technet.microsoft.com/en-us/library/ms175185.aspx)
- Perform backup/restore based on proper strategies including backup redundancy
- COPY_ONLY
- Partial Backups (http://technet.microsoft.com/en-us/library/ms191539.aspx)
- Recover from a corrupted drive
- Manage a multi-terabyte database
- Implement and test a database implementation and a backup strategy (multiple files for user database and tempdb, spreading database files, backup/restore)
- Back up a SQL Server environment
- Back up system databases (http://msdn.microsoft.com/en-us/library/ms190190.aspx)
Restore databases (http://msdn.microsoft.com/library/ms187048.aspx)
- Restore a database secured with TDE
- Enable TDE following these steps: (http://msdn.microsoft.com/en-us/library/bb934049(v=sql.120).aspx)
- Create a master key
- Create or obtain a certificate protected by the master key
- Create a database encryption key and protect it by the certificate
- Set the database to use encryption
- To restore the DB: (http://blogs.technet.com/b/josebda/archive/2009/04/01/sql-server-2008-transparent-data-encryption-tde.aspx)
- You must backup the certificate on the source system
- On the target you:
- Create a master key
- Create the certificate from the backup taken on the source
- Then restore the db
- Enable TDE following these steps: (http://msdn.microsoft.com/en-us/library/bb934049(v=sql.120).aspx)
- Recover data from a damaged DB (http://msdn.microsoft.com/en-us/library/ms187495.aspx)
- Restore full backup
- Restore differentials if needed – each
- Restore transaction logs (http://msdn.microsoft.com/en-us/library/ms177446.aspx)
- RESTORE DATABASE <database_name> WITH RECOVERY
- Restore to a point in time (http://msdn.microsoft.com/en-us/library/ms179451.aspx)
- Use option “WITH STOPAT = time” on last transaction log
- File group restore (http://msdn.microsoft.com/en-us/library/ms177425.aspx)
- Attempt to get tail of the log backup (http://msdn.microsoft.com/en-us/library/ms179314.aspx)
- Restore primary filegroup – RESTORE DATABASE FILEGROUP=’Primary’ FROM backup WITH PARTIAL, NORECOVERY
- Restore READ_WRITE_FILEGROUPS
- Restore differentials
- Restore logs
- Restore READ-ONLY filegroups
- Bring database online WITH RECOVERY
- Page level restore (http://msdn.microsoft.com/en-us/library/ms175168.aspx)
- Only supported for READ_WRITE_FILEGROUPS
- RESTORE DATABASE <database_name> PAGE = ‘ …. WITH NORECOVERY from full backup
- Apply differentials and log backups
- Take new log backup and restore it
- Bring db online
Implement and maintain indexes (http://msdn.microsoft.com/en-us/library/ms175049.aspx)
- Inspect physical characteristics of indexes and perform index maintenance
- Reorganize and Rebuild Indexes (http://msdn.microsoft.com/en-us/library/ms189858.aspx)
- Perform Index Operations Online (http://msdn.microsoft.com/en-us/library/ms177442.aspx)
- Excludes XML indexes
- Identify fragmented indexes (http://technet.microsoft.com/en-us/library/ms189858.aspx#Fragmentation)
- Query sys.dm_db_index_physical_stats looking at avg_fragmentation_in_percent
- Identify unused indexes
- Query sys.dm_db_index_usage_stats to compare updates vs. scans, lookups, and seeks (http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/)
- Implement indexes
- Defrag/rebuild indexes(http://technet.microsoft.com/en-us/library/ms189858.aspx)
- Set up a maintenance strategy for indexes and statistics(http://technet.microsoft.com/en-us/library/ms190981.aspx)
- Consider AUTO_UPDATE_STATISTICS_ASYNC updates for more predictable query times due to statistics updating
- Optimize indexes (full, filter)
- Create filtered indexes (http://msdn.microsoft.com/en-us/library/cc280372.aspx)
- Statistics (full, filter) force or fix queue (http://msdn.microsoft.com/en-us/library/ms190397.aspx)
- When to rebuild vs. reorg and index (http://technet.microsoft.com/en-us/library/ms189858.aspx)
- General rule above 30% REBUILD, between 5% and 29% REORGANIZE
- Full text indexes
- Column store indexes (http://msdn.microsoft.com/en-us/library/gg492088.aspx)
- Used in data warehousing situations where data doesn’t change often
Import and export data (http://msdn.microsoft.com/en-us/library/ms175937.aspx)
- Transfer data
- Bulk copy (http://msdn.microsoft.com/en-us/library/aa337544.aspx)
- Can use format files with bcp
- Bulk insert (http://msdn.microsoft.com/en-us/library/ms175915.aspx)
Preparation resources
- Back up and restore of SQL Server databases
- File restores (full recovery model)
- DBCC INDEXDEFRAG (Transact-SQL)
Click here to go back to main page for exam 70-458.