Sign In | Feedback | Donate | Search
Katie and Emil Logo
Vote Yes! I like it! [+0]  |  Vote no! Dislike? [-0]

Below are "skills measured" for 70-462 Administering Microsoft SQL Server 2012 Databases and for now I will include my usually short tutorials (as I'm not DBA) and hopefully that will help me to pass the exam and get MCSE in BI!! Later on I will improve the tutorials and add more.

Later on I intend on adding some sample questions so you can test your knowledge and fill in gaps and that hopefully will help you to get more correct answers to the actual questions on the exam.

If you have links and materials that cover the subject; let me know and I will put them here until.

Install and Configure SQL Server (19%)
  • Plan installation.
    • This objective may include but is not limited to:
    • evaluate installation requirements;  SQLServer2012 Evaluate Installation Requirements - This articles provides summary of SQL Server 2012 editions, features, hardware and software requirements and provides links to Microsoft sites that contain all the details. 
      design the installation of SQL Server and its components (including drives and service accounts);  
      plan scale up vs. scale out basics;

      What is Scalability? (Microsoft)

      Scaling Out and Up (Microsoft)

      plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth;  
      manage the technologies that influence SQL architecture (including service broker, full text, and scale out);  
      design the storage for new databases (drives, filegroups, partitioning);  
      design database infrastructure;  
      configure a SQL Server standby database for reporting purposes;  
      Windows-level security and service level security;  
      Core mode installation;  
      benchmark a server before using it in a production environment (SQLIO, Tests on SQL Instance);  
      choose the right hardware  
  • Install SQL Server and related services.
    • This objective may include but is not limited to:
    • test connectivity; find links
      enable and disable features;  
      install SQL Server database engine and SSIS (not SSRS and SSAS);  
      configure an operating system disk  
  • Implement a migration strategy.
    • This objective may include but is not limited to:
    • restore vs. detach/attach; Backup Restore vs Detach Attach (Video)
      migrate security;  
      migrate from a previous version;  
      migrate to new hardware;  
      migrate systems and data from other sources  
  • Configure additional SQL Server components.
    • This objective may include but is not limited to:
    • set up and configure Analysis Services (AS), Reporting Services (RS), and SharePoint integration in a complex and highly secure environment; find links 
      configure full-text indexing;  
      SQL Server Integration Services (SSIS) security;  
      filestream;  
      filetable

      NOTE:

      CREATE TABLE TableName as FileTable; 

  • Manage SQL Server Agent.
    • This objective may include but is not limited to:
    • create, maintain, and monitor jobs; find links
      administer jobs and alerts;  
      automate (setup, maintenance, monitoring) across multiple databases and multiple instances; NOTE: Ma
      send to "Manage SQL Server Agent jobs"  
Maintain Instances and Databases (17%)
  • Manage and configure databases.
    • This objective may include but is not limited to:
    • design multiple file groups; find links
      database configuration and standardization: autoclose, autoshrink, recovery models;

      NOTES:

      • auto shrink-Should be disabled to avoid fragmentation
      • auto-close - should be disabled to avoid performance issues in medium and heavy load scenarios
      • Recovery models - Full, Simple and Bulk logged
      manage file space, including adding new filegroups and moving objects from one filegroup to another;  
      implement and configure contained databases;

      CREATE DATABASE partially_contained_db CONTAINMENT = PARTIAL;

      CREATE USER contained_user WITH PASSWORD = 'Pa$$w0rd';

      CREATE USER [contoso\ contained_user_a];

       

       

      data compression;

      Row and Page Level compression. Very beneficial in most data warehouse databases (I can confirm that!). Page has bigger compression (lower I/O usage). Row has smaller compression but it requires less CPU usage. Good when you need compression but with smaller impact on CPU.

      SQL Example below.

      ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION = ROW)

      Compression can be used on tables, indexes and partitions.

      SP syntax to check savings

      EXEC sp_estimate_data_compression_savings

      'schema_name', 
      'object_name', 
      index_id,
      partition_number, 
      'data_compression'

       

      configure Transparent Data Encryption (TDE);

      Transparent Data Encyption Steps - We show steps you need to take to enable TDE on a database.

       

      partitioning;

      Editions: Table and Index pPartitions can be only used with Developer and Enterprise Edition.

      Limits: Up to 15000 partitions on x64 version and up to 1000 on x86 version.

       

      manage log file growth;

      Control the Growth of a Transaction Log File (Microsoft)

      Database Console Commands (DBCC)

      Training Kit (Microsoft book) has Exam tip that says to pay extra attention to:

      DBCC CHECKDB

      Full list is available here http://msdn.microsoft.com/en-us/library/ms188796.aspx

      [worth knowing. not explicitly part of requirements]

      Start SQL Server with Minimal Configuration (Microsoft)

      [worth knowing. not explicitly part of requirements]

      Database File Initializatiom (Microsoft)

  • Configure SQL Server instances.
    • This objective may include but is not limited to:
    • configure and standardize a database including autoclose, autoshrink, recovery models; find links
      install default and named instances; NOTE: Instance name can be maximum 16 characters
      configure SQL to use only certain CPUs (for example, affinity masks);  
      configure server level settings;  
      configure many databases/instance, many instances/server, virtualization;  
      configure clustered instances including Microsoft Distributed Transaction Coordinator (MSDTC);  
      memory allocation;  
      database mail;  
      configure SQL Server engine including memory, filffactor, sp_configure, and default options

      NOTE:

      sys.sp_configure is used to change server properties like:

      EXEC sys.sp_configure 'max server memory', 4096;

      EXEC sys.sp_configure 'min server memory', 1024;

      EXEC sp_configure filestream_access_level, 2;

      • 0 = Disabled
      • 1 = T-SQL Only
      • 2 = T-SQL and Win32

      EXEC sp_configure 'contained database authentication', 1;

       

      EXEC sp_configure "common criteria compliance enabled", 1;

       

      For a list of server properties see http://msdn.microsoft.com/en-us/library/ms189631.aspx

       

      Check also Reconfigure vs reconfigure with overwrite

  • Implement a SQL Server clustered instance.
    • This objective may include but is not limited to:
    • install a cluster; find links
      manage multiple instances on a cluster;  
      set up subnet clustering;  
      recover from a failed cluster node  
  • Manage SQL Server instances.
    • This objective may include but is not limited to:
    • install an instance; find links
      manage interaction of instances;  
      SQL patch management;  
      install additional instances;  
      manage resource utilization by using Resource Governor;  
      cycle error logs

      NOTE: EXEC sp_cycle_errorlog; - used to cycle error log without database engine restart

      EXEC sp_cycle_agent_errorlog - used to cycle agent error log

Optimize and Troubleshoot SQL Server (14%)
  • Identify and resolve concurrency problems.
    • This objective may include but is not limited to:
    • examine deadlocking issues using the SQL server logs using trace flags; find links
      design reporting database infrastructure (replicated databases);  
      monitor via Dynamic Management Views (DMV) or other Microsoft product;

      sys.dm_db_uncontained_entities - shows entities that cross the boundary of a database, in other words you cannot use contained database if this DMV returns results.

       

      diagnose blocking, live locking, and deadlocking;  
      diagnose waits;  
      performance detection with built-in DMVs;  
      locate and if necessary kill processes that are blocking or claiming all resources  
  • Collect and analyze troubleshooting data.
    • This objective may include but is not limited to:
    • monitor using Profiler, collect performance data by using System Monitor, collect trace data by using SQL Server Profiler, identify transactional replication problems; find links 
      identify and troubleshoot data access problems;  
      gather performance metrics;  
      identify potential problems before they cause service interruptions;  
      identify performance problems, use Extended Events (XEvents) and DMVs;  
      create alerts on critical server condition;  
      monitor data and server access by creating audit and other controls;  
      identify IO vs. memory vs. CPU bottlenecks;  
      use the Data Collector tool  
  • Audit SQL Server instances.
Manage Data (20%)
  • Configure and maintain a backup strategy.
    • This objective may include but is not limited to:
    • manage different backup models, including point in time recovery; find links
      protect customer data even if backup media is lost;  
      perform backup/restore based on proper strategies including backup redundancy;  
      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  
  • Restore databases.
    • This objective may include but is not limited to:
    • restore a database secured with TDE; find links
      recover data from a damaged DB;  
      restore to a point in time;  
      file group restore;  
      page level restore  
  • Implement and maintain indexes.
    • This objective may include but is not limited to:
    • inspect physical characteristics of indexes and perform index maintenance; find links
      identify fragmented indexes;  
      identify unused indexes;  
      implement indexes;  
      defrag/rebuild indexes;  
      set up a maintenance strategy for indexes and statistics;  
      optimize indexes (full, filter);  
      statistics (full, filter) force or fix queue;  
      when to rebuild vs. reorg and index;

      avg fragmentation >5% and <=30% then reorganize

      >30% then rebuild

      http://technet.microsoft.com/en-us/library/ms189858.aspx

      full text indexes; Maximum number of full text indexes on a table is 1
      column store indexes  
  • Import and export data.
    • This objective may include but is not limited to:
    • transfer data; find links
      bulk copy;  
      bulk insert  
Implement Security (18%)
  • Manage logins and server roles.
    • This objective may include but is not limited to:
    • configure server security; find links
      secure the SQL Server using Windows Account/SQL Server accounts, server roles;  
      create log in accounts;

      CREATE LOGIN "Domain\Account" FROM WINDOWS;

       

      CREATE LOGIN sql_login WITH PASSWORD = 'Pa$$w0rd';

      CREATE

       

      ALTER LOGIN sql_user_a DISABLE;

       

      manage access to the server, SQL Server instance, and databases;

      sys.server_principals - shows login info on server level

      sys.server_role_members - shows members is different server roles using role principal id and member principal id

      sp_helpsrvrole

      sp_helpsrvrolemember

      sp_srvrolepermission

       

      select IS_SRVROLEMEMBER('sysadmin','LoginName')

       

      ALTER SERVER ROLE serveradmin ADD MEMBER "Login";

      create and maintain user-defined server roles;

      CREATE SERVER ROLE UserDefinedServerRoleName;

       

      GRANT ALTER ANY LOGIN TO UserDefinedServerRoleName;

       

      manage certificate logins

      CERTIFICATE Cert_Emil WITH SUBJECT = 'Emil certificate in master database'

      CREATE LOGIN Emil FROM CERTIFICATE Cert_Emil;

       

      CREATE ASYMMETRIC KEY user_ak WITH ALGORITHM = RSA_2048;

      CREATE LOGIN user_ak FROM ASYMMETRIC KEY user_ak;

  • Manage database permissions.
    • This objective may include but is not limited to:
    • configure database security; find links
      database level permissions;  
      protect objects from being modified  
  • Manage users and database roles.
    • This objective may include but is not limited to:
    • create access to server/database with least privilege; find links
      manage security roles for users and administrators;

      EXEC sp_addrolemember 'db_datawriter', "sql_user";

      CREATE ROLE TableCreator AUTHORIZATION "sql_user";

      GRANT CREATE TABLE TO TableCreator;

      create database user accounts;

      CREATE USER "SQL_User" FOR LOGIN "SQL_User";

       

      contained logins  
  • Troubleshoot security.
    • This objective may include but is not limited to:
    • manage certificates and keys; find links
      endpoints  
Implement High Availability (12%)
  • Implement AlwaysOn.
    • This objective may include but is not limited to:
    • implement a mirroring solution using AlwaysOn; find links
      failover  
  • Implement database mirroring.
    • This objective may include but is not limited to:
    • set up mirroring; find links
      monitor the performance of database mirroring  
      [Not officially a requirement but it is worth knowing] Mirrored Database - Upgrading (Microsoft)
  • Implement replication.
    • This objective may include but is not limited to:
    • troubleshoot replication problems; find links
      identify appropriate replication strategy  
         

Take care 

Emil

Mini Learning Map

Upsss... Something went wrong and Google Ads on our website did not display.

Google ads sponsor FREE content on our website and without them it would not exist.

How to fix the problem?
  1. Check if the Google Search display any ads by searching the term Business Intelligence
    • If ads don't display, try different search terms and if there no ads then there might be a problem with your browser. If you made changes to your browser behaviour then undoing them will help.
    • If ads display then, refresh our page one more time, if the problem is still there than please contact us.
  2. Become 'No Ads Learner' Member or if you are already one Sign In.
Close
Take care
Katie & Emil
Well done! It seems Google Ads display properly now on our website.

Access our website's FREE content for FREE!