So this is the third of of the four SQL Server 2012 beta exams that I am taking.  I have blogged about my experiences with the other exams and shared the study notes that I have used.  This post is no different.  For more information no the other exams I have taken to date, see the links below. 

Querying Microsoft SQL Server 2012 (71-461)
Designing Database Solutions for Microsoft SQL Server 2012 (71-465)

The exam was pretty good.  I have nit picky issues with the way some of the questions were worded but overall I felt the test did a good job of covering the material. 

My study notes listed below are based off of the Microsoft Training Catalog.  You will not that I did not covey everything listed in the catalog.  I didn't have time to get it all listed out. 

Install and Configure SQL Server (19%)
   * Plan installation
      * Evaluate Installation Requirements
      * Drive best practices
         * Disk configuration primer: http://www.mssqltips.com/sqlservertip/1328/hard-drive-configurations-for-sql-server/
      * Service Accounts
         * These should be Managed Service Accounts
   * Install SQL Server and related services
   * Implement a migration strategy
   * Configure additional SQL Server Components
      * Configure full-text indexing
         * allows for full-text queries against character based data
         * More information about configuring full-text indexing can be found here: http://msdn.microsoft.com/en-us/library/cc879306.aspx
      * Configure FILESTREAM
         * This can be done through the GUI or through TSQL and the sp_configure command
         * More information on FILESTREAM can be found here: http://msdn.microsoft.com/en-us/library/cc645923.aspx
      * Configure FileTable
         * FILESTREAM must be enabled to use FileTable
         * Enable Non-Transaction Access at the Database Level
         * Specify a directory for FileTables at the Database Level
         * More information on FileTable: http://msdn.microsoft.com/en-us/library/ff929068(v=sql.110).aspx
   * Manage SQL Server Agent
      * Create, maintain, and monitor jobs
         * More information on creating jobs: http://msdn.microsoft.com/en-us/library/ms190268.aspx
      * Monitor Jobs
         * Job activity can be viewed in the Job Activity Monitor
         * Job activity can also be queried by running the sp_help_jobactivity
         * More information can be found here: http://msdn.microsoft.com/en-us/library/ms187449.aspx
      * Administer jobs and alerts
         * An alert  is an automated response to an event
         * Alerts can respond to
            * SQL Server events
            * Performance conditions
            * Windows Management Instrumentation (WMI) events
            * More information on alerts can be found here: http://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

Maintain Instances and Database (17%)
   * Manage and configure databases
      * Filegroups
         * named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.
         * Log files are never part of a file group
         * More information regarding Filegroups can be found here: http://msdn.microsoft.com/en-us/library/ms179316.aspx
      * Database configuration
         * Five categories
            * Auto Options
            * Cursor Options
            * Recovery Options
            * SQL options
            * State options
         * More information on Database configuration options can be found here:http://msdn.microsoft.com/en-us/library/aa933082(v=sql.80).aspx
      * Transparent Data Encryption (TDE)
         * real-time I/O encryption and decryption of the data and log files
         * It does not provide encryption across communication channels
         * Encryption is performed at the page level.
         * More information regarding TDE can be found here: http://msdn.microsoft.com/en-us/library/bb934049.aspx
      * Database Console Commands DBCC
         * More information can be found here: http://msdn.microsoft.com/en-us/library/ms188796.aspx
      * Manage Log file growth
         * Information regarding managing the size of Transaction Log files can be found here: http://msdn.microsoft.com/en-us/library/ms365418.aspx
   * Configure SQL Server Instances
   * Implement a SQL Server clustered instance
      * More Information can be found here: http://msdn.microsoft.com/en-us/library/ms179530.aspx

Optimize and Troubleshoot SQL Server (14%)
   * Identify and resolve concurrency problems
      * Deadlock Trace Flags
         * Profiler Trace Flags 1204 and 1222 return information that is captured in the error log
         * More information regarding Deadlocks can be found here: http://msdn.microsoft.com/en-us/library/ms178104.aspx
      * Reporting database infrastructure (replicated databases)
         * Transactional replication is typically the form used for data warehousing and reporting
         * More information can be found here: http://technet.microsoft.com/en-us/library/ms151198.aspx
      * Monitor via Dynamic Management Views
         * DMVs and DM Functions return server state information
         * More information regarding DMVs can be found here: http://technet.microsoft.com/en-us/library/ms188754.aspx
      * Diagnose Blocking
         * Information regarding blocking can be found here: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/14/blocking-blockers-and-other-b-words-a-month-of-activity-monitoring-part-14-of-30.aspx
   * Collect and analyze troubleshooting data
      * Monitor using profiler
         * Profiler manages traces and allows for the replay of trace results
         * More Information on Profiler can be found here: http://msdn.microsoft.com/en-us/library/ms181091.aspx

Permalink | Leave a comment  »