Jonathan Gardner

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

Design Database Structure (29%)

• Design for business requirements
• Design physical database and object placement
• Log files and data should be isolated at the physical disk level
• Logical vs. physical design: http://sqlserverpedia.com/wiki/Physical_vs._Logical_Design
• Filegroups
• Database objects and files can be grouped together in file groups for allocation and administration purposes.
• This is for data files only.  Log files are managed separately.  
• No file can be a member of more than one file group.
• Two Types
• Primary
• User Defined
• More information regarding file groups can be found here: http://msdn.microsoft.com/en-us/library/ms179316.aspx
• Design a table and index partitioning strategy
• Partitioning can have manageability and performance benefits
• More information re: Table Partitioning: http://msdn.microsoft.com/en-us/library/ms190787.aspx
• Partitioned Indexes
• Aligned Index
• Nonaligned Index
• More information regarding Partitioned Indexes: http://msdn.microsoft.com/en-us/library/ms190787.aspx
• Design a migration, consolidation, and upgrade strategy
• Upgrade with minimal downtime
• If Mirroring or clustering is in play this can be achieved by doing rolling upgrades
• Contained Databases
• This is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
• Metadata that describes the database is maintained inside the database and not in the master database
• All metadata are defined using the same collation
• User authentication can be performed by the database
• More information on contained database: http://msdn.microsoft.com/en-us/library/ff929071.aspx
• Contained databases are extremely useful with AlwaysOn for moving databases and users.
• Design SQL Server Instance
• Affinity Masks
• The Affinity Mask will change how SQL Server accesses CPUs.
• More information regarding Affinity Mask http://msdn.microsoft.com/en-us/library/ms187104.aspx
• The IO Affinity mask is a targeted optimization
• More information on the IO Affinity mask can be found here: http://blogs.msdn.com/b/psssql/archive/2010/11/19/how-it-works-io-affinity-mask-should-i-use-it.aspx
• Clustering
• SQL Server clustering allows multiple physical servers to host a single SQL Server Instance.  
• It leverages Windows Clustering technologies
• Microsoft Distributed Transaction Control
• Coordinates transactions that update two or more transaction-protected resources
• More information regarding regarding Distributed Transaction Coordinator: http://technet.microsoft.com/en-us/library/cc759136(v=ws.10).aspx
• SQL Server memory allocation
• http://msdn.microsoft.com/en-us/library/ms178067.aspx
• Design backup and recovery
• More information regarding Backups and Recovery: http://msdn.microsoft.com/en-us/library/ms187048.aspx

Design Database and Database Objectives (32%)

• Design a database model
• Design a logical schema
• Design a normalized database
• Design data access and data layer architecture
• Relational Model
• Normalized data model
• Database schema
• EAV modeling
• Star-Schema
• Collation
• These are the rules that govern the use of characters for a language or alphabet.
• ANSI NULLS
• This changes the way that NULLs behave
• Based off of the ANSI SQL-92 standard
• More information regarding NULLs can be found here: http://msdn.microsoft.com/en-us/library/ms187048.aspx
• QUOTED IDENTIFIER
• Causes SQL Server to follow the ISO rules regarding quotation mark delimiting
• More information can be found here: http://msdn.microsoft.com/en-us/library/ms174393.aspx
• Design Tables
• Common Table Expressions
• Temporary result set
• Confined within a single query
• More information regarding Common Table Expressions can be found here: http://msdn.microsoft.com/en-us/library/ms190766.aspx
• Transactions
• Sequence of operations performed as a single logical unit of work
• Primer on Transactions can be found here: http://www.sqlteam.com/article/introduction-to-transactions
• Views
• Essentially a virtual table
• If the view is indexed it will become a stored set of data
• Information regarding designing views can be found here: http://msdn.microsoft.com/en-us/library/ms189918.aspx
• Temporary Tables vs. Table Variables
• Transaction logs are not recorded for table variables
• Procedures with temporary tables cannot be pre-compiled
• Primer here: http://www.sql-server-performance.com/2007/temp-tables-vs-variables/ (Note, this is reflective of SQL Server 2000 and 2005)
• Filestream
• This is a way to store unstructured data in the database but store it separate from the structured data.  This means SQL Server can us the NTFS file system to store the files. 
• Files are stored using varbinary(max).
• While these files are stored on the NTFS file system they should not be accessed from the file system unless the OpenSqlFilestream API is used.
• More information on Filestream can be found here: http://technet.microsoft.com/en-us/library/bb933993.aspx
• FileTable
• This is built on top of Filestream technology
• Allows windows file manipulation of SQL Server unstructured data
• Allows SQL Server functionality including full-text search and semantic search to be used and access via SQL Server management tools.
• More Information regarding FileTables can be found here: http://msdn.microsoft.com/en-us/library/ff929144(v=sql.110).aspx
• Semantic Engine
• Video on the Microsoft Semantic Engine from the SQL Server team: http://www.microsoftpdc.com/2009/SVR32
• “The Semantic Engine is a technology that enables us to mine unstructured data and query it using tools that are already familiar to SQL Server professionals. It extracts and indexes pertinent information from data sources that are presented to it through data adapters. Adapters may exist for documents, audio, video, web pages and a plethora of other unstructured or partially-structured data sources” via: http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/22/microsoft-semantic-engine-uncovered-the-harnessing-of-unstructured-data.aspx
• Sequences
• SEQUENCE is similar to IDENTITY but it is not bound to a specific table.  Much of the difference is in the way the Metadata is stored for the numbers.
• SEQUENCE can be used during a stored procedure.
• Values can be appended to the front of a sequence.
• More information can be found here: http://technet.microsoft.com/en-us/magazine/hh407114.aspx
• Data Compression
• Can be better for IO but will use CPU overhead
• Compression is an Enterprise edition only feature
• More Information on Data Compression can be found here: http://msdn.microsoft.com/en-us/library/cc280449.aspx
• Design for concurrency
• Design TSQL stored procedures
• Design a management automation strategy
• Design for implicit and explicit transactions
• TRY…CATCH
• This is a way to implement error handling inside of TSQL. 
• When an error condition is detected inside of the TRY block, control is passed to the CATCH block for error processing.
• More information on TRY…CATCH can be found here: http://msdn.microsoft.com/en-us/library/ms179296.aspx
• COMMIT TRANSACTION
• Marks the end of a successful transaction
• Makes all data modification performed since the start of the transaction permanent
• More information on COMMIT TRANSACTION can be found here: http://msdn.microsoft.com/en-us/library/ms190295.aspx
• THROW
• Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct
• If TRY…CATCH is not available the session is ended.
• THROW is a new feature in SQL Server 2012
• It allows for error handling without the need to use the RAISERROR statement that requires the use of the sys.messages table.
• THROW error numbers need to be higher than 50000 to avoid replicating system errors.
• More inofmraiton on THROW can be found here: http://msdn.microsoft.com/en-us/library/ee677615.aspx
• More examples of THROW can be found here: http://cm-bloggers.blogspot.com/2011/02/using-new-throw-keyword-in-sql-server.html

Design Database Security (15%)

• Design an application strategy to support security
• Design security
• Schemas and schema security
• SQL Server vs. Integrated Authentication
• Integrated authentication leverages the Windows principal token in the operating system.
• This uses Kerberos security protocol
• Complexity requirements and account management is in a central location
• SQL Server Authentication cannot use Kerberos security protocol
• Is good for mixed environments that don't support windows auth.
• More information regarding SQL Server authentication can be found here: http://msdn.microsoft.com/en-us/library/ms144284.aspx
• Permissions and mirroring issues
• If both SQL servers in a mirroring scenario do not run under the same service account the service account for each server will need to be manually added to all the servers in the scenario.
• More information regarding logins and mirroring can be found here: http://technet.microsoft.com/en-us/library/ms366346.aspx
• Encryption

I have just finished the first of the Microsoft SQL Server 2012 Beta exams, Querying Microsoft SQL Server 2012 (71-461).  I took them for a couple of different reasons, I feel that certifications are important and it was an opportunity for me to upgrade my current certifications.  

My thoughts after finishing the exam, You don't want me to be your developer.  It was a good exam by I know my strengths are in database administration and not development.  There were some questions that were unclear, and some that I flat didn't know the answer of but they seemed positioned simlar to the tests from last year.  

If you are intersted in taking these exams you can register for them for free here.

I have mentioned before that use Evernote for a host of things.  Studying for these exams has been no different.  I have created a notebook that has my links to study notes.  I am going to post them here but I will share the notebook that will update as I update them.  You can see the notebook here.

The notes below and in my notebook are based off of the study guide posted in the Microsoft Training Catalog.

This exam is intended for SQL Server database administrators, implementers, system engineers, and developers with two or more years of experience who are seeking to prove their skills and knowledge in writing queries. Primary responsibilities may include but are not limited to:

Create database objects (24%)

• Create and alter tables using T-SQL syntax (simple statements)
  • Create and alter tables
    • Tables store data in the database
    • The CREATE TABLE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms174979.aspx
    • After a table is created you can change many of the options through the ALTER TABLE command.  
    • The ALTER TABLE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms190273.aspx
• Create and alter views (simple statements)
  • Vies are essentially virtual tables.  This virtual table is defined by a query.
  • Views have a max of 1024 columns
  • The CREATE VIEW TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187956.aspx
  • The ALTER VIEW TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms173846(v=sql.110).asp
• Design Views
• Create and modify contraints
  • Constraints define the way the Database Engine enforces the integrity of a database. 
  • Constraints are preferred to using Triggers, Rules, and Defaults
  • Keys are constraints
  • Constraint TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189862.aspx
• Create and alter DML Triggers
  • INSTEAD OF are executed in place of the usual triggering action. 
  • AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE
  • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms190267.aspx
  • CREATE TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189799.aspx
  • Triggers are a way to enforce business rules and data integrity.
  • It is a special type of stored procedure that automatically takes effect.
  • There are three types: DML, DDL and logon
  • DDL triggers are invoked when a data definition language event takes place.
  • DML triggers are invoked when when a data manipulation language event takes place in the database.  This includes INSERT, UPDATE or DELETE statements that modify data.    
  • Logon triggers fire stored procedures in response to a LOGON event.
  • They can cascade changes through related tables in a database.  Constraints can do this more efficiently.
  • The CREATE TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189799.aspx
  • The ALTER TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms176072.aspx

Work with Data (27%)

• Query data by using SELECT statements
  • Ranking Functions
    • Ranking functions return a ranking value for each row in a partition
    • The Ranking Function syntax for RANK can be found here: http://msdn.microsoft.com/en-us/library/ms189798.aspx
  • Join Basics
    • A good primer can be found on Pinal Dave's blog: http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/
  • Except and Intersect
    • Returns distinct values by comparing the results of two queries
    • EXCEPT retunes and distinct values from the left query that are not also found in the right query.
    • INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand
    • Rules
      • The number and order of the columns must be the same in all queries
      • The data types must be compatible
    • Two NULL values are considered equal
    • More information can be found here: http://msdn.microsoft.com/en-us/library/ms188055.aspx
  • Dynamic SQL
    • Dynamic SQL is the term used when the SQL code is generated by a program before it is executed.  
  • CASE
    • Evaluates the list of conditions and returns one of multiple possible result expressions
    • The CASE statement can be found here: http://msdn.microsoft.com/en-us/library/ms181765.aspx
  • ISNULL
    • Replaces NULL with the specified replacement value
    • ISNULL is not a replacement for IS NULL. 
    • ISNULL TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms184325.aspx
  • COALESCE
    • Returns the first non null expression among its arguments
    • Expressions involving COALESCE with non-null parameters is considered to be NULL
    • The COALESCE statement can be found here: http://msdn.microsoft.com/en-us/library/ms190349.aspx

• Implement sub-queries
  • A subquery can be used anywhere an expression is allowed
  • A subquery will return a single value
  • More information can be found here: http://msdn.microsoft.com/en-us/library/aa213252(v=sql.80).aspx

• Implement data types
  • A data type is an attribute that specifies the type of data that the object can hold.  
  • Data types can be created through the CREATE TYPE command or through .NET Framework
  • More information on Data Types can be found here: http://msdn.microsoft.com/en-us/library/ms187752.aspx
  • GUIDs can cause performance issues when they are used as the clustered index key.  More information can be found from Glenn Berry here: http://www.sqlservercentral.com/blogs/glennberry/2010/03/22/why-uniqueidentifier-is-a-bad-choice-for-a-clustered-index-in-sql-server/
  • GUIDs are also a larger data type than necessary.

• Implement aggregate queries
  • Aggregate functions return a single value summarizing a given data set.  All aggregate functions are deterministic.  
  • Examples: COUNT, MAX, MIN, AVG, SUM, etc
  • More information on aggregate functions can be found here: http://sqlserverpedia.com/wiki/Built-in_Functions_-_Aggregate_Functions

• Query and manage XML data
  • SQL Server 2012 has changed the Data Type Conversion by the XML value Method Has Changed.  More information regarding this change can be found here: http://technet.microsoft.com/en-us/library/ms143359(v=sql.110).aspx
  • TSQL supports a subset of the XQuery language that is used for querying the XML data type.

Modify Data (24%)

• Create and alter stored procedures
  • Stored procedures accept input parameters return values
  • They contain programming statements that perform operations in the database
  • The CREATE PROCEDURE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187926.aspx
• Modify data by using INSERT, UPDATE, and DELETE statement
• Combine Datasets
  • Datasets can be combined by UNION, EXCEPT, and INTERSECT
  • ORDER BY is only allowed at the end of the statement and cannot be used within the individual queries
  • GROUP BY and HAVING clauses can be used only within individual queries not on the final result set.
  • The FOR BROWSE clause cannot be used.
  • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms191523.aspx
• Work with functions
  • Apply built-in scalar functions
    • Provided by SQL Server.
    • Cannot be modified
    • Access system information without accessing the system tables
    • Used to perform common tasks like SUM, GETDATE, or IDENTITY
    • MSDN Library Reference:http://msdn.microsoft.com/en-us/library/ms177499.aspx
  • Apply ranking functions
    • Retuns a ranking value for each row in a partition
    • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms189798.aspx

Troubleshoot and Optimize Queries (25%)

• Optimize queries
  • Statistics
    • The query optimizer uses statistics to create query plans that improve query performance. 
    • They contain statistical information about the distribution of the values in one or more columns of a table or indexed view
    • More information regarding statistics can be found here: http://msdn.microsoft.com/en-us/library/ms190397.aspx
  • Query Plans
    • SQL Server 2012 includes additional information over 2008R2.  
    • Additions to the SELECT Statement
      • MemoryGrantInfo is now included
      • OptimizedHardwareDependentProperties is now included
      • Warnings are now included to show things that will effect plans
      • Some more information can be found here: http://www.sqlservercentral.com/blogs/scarydba/2011/12/12/changes-to-sql-server-2012-execution-plans/
    • Plan Guide information can be found here: http://technet.microsoft.com/en-us/library/ms190417(v=sql.110).aspx
  • DMVs
    • DMV information can be found here: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/18/sql-server-2012-changes-to-system-objects-in-rc0.aspx
  • Hints 
    • options or strategies for enforcement by the query processor. 
    • Join Hints
      • LOOP, HASH, MERGE, REMOTE
      • More information on Join Hints can be found here: http://msdn.microsoft.com/en-us/library/ms173815.aspx
    • Query Hints
      • More Information on Query Hints can be found here: http://msdn.microsoft.com/en-us/library/ms181714.aspx
    • Table Hints
      • Table Hints override the default behavior of the query
      • More information on table hints can be found here: http://msdn.microsoft.com/en-us/library/ms187373.aspx
    • Statistics IO
      • Setting STATISTICS IO is ON the query statistics are displayed when it is run
      • Statistics will show information regarding physical and logical reads.
      • More information can be found here: http://msdn.microsoft.com/en-us/library/ms184361(v=sql.110).aspx
    • Parameterized Queries
      • TSQL that accepts parameters
• Manage transactions
• Evaluate the use of row-based operations vs. set-based operations
• Implement error handling

Create and alter indexes

• Index allows specific data to be located quicker.
• Indexes are on-disk structure associated with a table or view.
• There are two types of indexes: Clustered and Nonclustered
  • Clustered
    • Sort and store the data rows in the table. 
    • Like 'The Highlander', there can be only one
  • Nonclustered
    • Contains key values and a pointer to the data roy that contains the key
    • Can have more than one.
• Indexes, if properly designed, can reduce disk I/O and improve system performance by reducing the use of system resources. 
• CREATE INDEX TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms188783.aspx

Create and alter stored procedures

• Stored procedures accept input parameters return values
• They contain programming statements that perform operations in the database
• The CREATE PROCEDURE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187926.aspx

Create and alter user-defined functions (UDFs)

• These functions are used to bundle code for reuse within SQL Server.  
• TSQL Errors cause the function to stop.
• Benefits of UDFs
  • Allow modular programming
  • Allow faster execution
  • Can reduce network traffic
• The CREATE FUNCTION TSQL can be found here:http://msdn.microsoft.com/en-us/library/ms191320(v=sql.110).aspx