SQL Server 2012/2014 course, this certification shows your skills and also advancement insights in developing and keeping the next wave of mission-critical environments. Zabeel Institute is considered as the best training institute in Dubai for MCSA SQL Server 2012/2014 course.
Job role: Database Administrator
Required exams: 70-461, 70-462, 70-463
MCSA SQL Server 2012/2014 Course Certification details
Zabeel international institute of management technology Offer High Demand in IT Courses and technology management industry. We are one of the Top ranking training institute in Dubai .
Exam 70-462: Administering Microsoft SQL Server 2012/2014 Databases
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
This examination is planned for data source experts who perform installment, upkeep, and arrangement jobs. Other obligations include establishing database systems, ensuring those systems operate successfully, and on a regular basis saving, backing up, and also securing data from unapproved gain access to.
Part of the requirements for: MCSA: SQL Server 2012/2014
Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
This examination is primarily meant for ETL as well as information warehouse programmers who create Business Knowledge (BI) services, and also whose duties include data cleansing, and Essence Change Load as well as information storage facility application.
Part of the requirements for: MCSA: SQL Server 2012/2014 Course
MCSA SQL Server 2012/2014 Course Skills measured
None
MCSA SQL Server 2012/2014 Course content
Exam 70-461: Querying Microsoft SQL Server 2012/2014
Create database objects (20–25%)
Create and alter tables using T-SQL syntax (simple statements)
produce tables without utilizing the integrated in devices; ALTER; DECREASE; ALTER COLUMN; CREATE
Create and alter views (simple statements)
produce indexed views; create views without using the constructed in devices; CREATE, MODIFY, DECREASE.
Design views
Guarantee code non regression by maintaining consistent signature for procedure sights as well as feature (user interfaces); safety and security implications.
Create and modify constraints (simple statements)
Develop restraints on tables; specify restrictions; unique restrictions; default restraints; primary and also foreign essential restraints.
Create and alter DML triggers
Inserted as well as removed tables; embedded triggers; types of triggers; update functions; deal with multiple rows in a session; performance effects of triggers.
Work with data (25–30%)
Query data by using SELECT statements
use the ranking feature to select leading( X) rows for multiple classifications in a single query; compose and also perform inquiries effectively utilizing the new (SQL 2005/8- >) code things such as synonyms, as well as joins (except, converge); carry out logic which makes use of dynamic SQL and also system metadata; compose effective, technically complicated SQL inquiries, consisting of all types of joins versus the use of acquired tables; identify what code may or may not implement based upon the tables provided; offered a table with restrictions, determine which statement established would load a table; use and recognize different data gain access to modern technologies; situation versus isnull versus coalesce
Implement sub-queries
determine problematic components in query plans; pivot as well as unpivot; apply driver; cte statement; with statement
Implement data types
usage appropriate information; understand the usages and also limitations of each data type; influence of GUID (newid, newsequentialid) on database efficiency, when to utilize what data kind for columns
recognize XML data types and their schemas and also interop w/, restrictions and restrictions; execute XML schemas and also handling of XML information; XML information: just how to manage it in SQL Web server and also when as well as when not to utilize it, consisting of XML namespaces; import and also export XML; XML indexing
Modify data (20–25%)
Create and alter stored procedures (simple statements)
write a stored treatment to meet an offered collection of needs; branching reasoning; produce kept procedures and also other programmatic things; methods for establishing saved procedures; different kinds of storeproc result; produce kept treatment for data gain access to layer; program stored treatments, triggers, works with T-SQL
Modify data by using INSERT, UPDATE, and DELETE statements
Provided a collection of code with defaults, restrictions, and also causes, identify the outcome of a set of DDL; recognize which SQL statements are best to fix typical demands; usage outcome declaration
Combine datasets
difference in between UNION and also UNION all; instance versus is null versus coalesce; change information by utilizing MERGE declarations
Work with functions
Recognize deterministic, non-deterministic functions; scalar and also table values; apply integrated scalar features; produce and also change user-defined features (UDFs).
Troubleshoot and optimize (25–30%)
Optimize queries
comprehend statistics; check out query plans; plan guides; DMVs; hints; statistics IO; vibrant vs. parameterized questions; define the various join types (HASH, COMBINE, LOOPHOLE) and also describe the circumstances they would be made use of in
Manage transactions
mark a purchase; comprehend begin tran, devote, and also rollback; implicit vs specific deals; isolation degrees; scope and also sort of locks; Tran count
Evaluate the use of row-based operations vs. set-based operations
when to utilize cursors; impact of scalar UDFs; incorporate numerous DML procedures
Implement error handling
carry out try/catch/throw; usage collection based as opposed to row based logic; purchase administration
Exam 70-462: Administering Microsoft SQL Server 2012/2014 Databases
Install and configure (20–25%)
Plan installation
review installation demands; style the installation of SQL Server and also its elements (drives, solution accounts, and so on); plan scale-up vs. scale-out fundamentals; plan for capability, including if/when to diminish, expand, autogrow, as well as screen development; take care of the technologies that influence SQL style (for instance, solution broker, complete message, range out, etc.); design the storage for new data sources (drives, filegroups, partitioning); design data source framework; configure a SQL Web server standby data source for reporting purposes; Windows-level security and solution degree safety and security; Core setting installment; criteria a server before using it in a manufacturing setting (SQLIO, Tests on SQL Instance); select the right hardware.
Install SQL Server and related services
examination connection; enable as well as disable attributes; install SQL Web server data source engine and also SSIS (not SSRS as well as SSAS); configure an OS disk
Implement a migration strategy
restore vs detach/attach; migrate safety and security; move from a previous variation; move to new hardware; move systems as well as data from other sources
Configure additional SQL Server components
established and configure all SQL Server components (Engine, AS, RS as well as SharePoint integration) in a complicated and highly safe atmosphere; set up full-text indexing; SSIS security; filestream; file table
Manage SQL Server Agent
produce, maintain, and also screen tasks; administer jobs as well as informs; automate (arrangement, upkeep, surveillance) across numerous data sources as well as several circumstances; send to "Manage SQL Server Agent tasks"
Maintain instances and databases (15–20%)
Manage and configure databases
layout multiple file teams; data source configuration as well as standardization: autoclose, autoshrink, recovery models; take care of documents room, consisting of including brand-new filegroups and moving items from one filegroup to another; execute and also configure contained databases; information compression; configure TDE; separating; take care of log file growth; DBCC
Configure SQL Server instances
set up and systematize a database: autoclose, autoshrink, healing designs; set up default and named instances; configure SQL to use just specific CPUs (fondness masks, and so on); set up server degree setups; configure numerous databases/instance, numerous instances/server, virtualization; set up gathered instances consisting of MSDTC; memory allocation; database mail; configure SQL Server engine: memory, filffactor, sp_configure, default alternatives
Implement a SQL Server clustered instance
set up a collection; handle multiple instances on a cluster; established subnet clustering; recoup from a failed collection node
Manage SQL Server instances
mount an instance; manage interaction of instances; SQL spot monitoring; set up added instances; manage resource utilization by using Source Governor; cycle error logs
Optimize and troubleshoot (15–20%)
Identify and resolve concurrency problems
take a look at deadlocking concerns making use of the SQL server logs utilizing trace flags; style reporting data source infrastructure (duplicated data sources); monitor by means of DMV or various other MS product; detect blocking, live securing and deadlocking; identify waits; performance discovery with integrated in DMVs; know what affects efficiency; find and also if essential kill processes that are blocking or claiming all sources
Collect and analyze troubleshooting data
display utilizing Profiler; collect efficiency information by using System Monitor; gather trace information by utilizing SQL Web server Profiler; identify transactional duplication issues; determine and troubleshoot information access issues; gather efficiency metrics; identify prospective issues prior to the trigger service interruptions; identify performance troubles;, make use of XEvents as well as DMVs; develop signals on crucial server condition; display data and also server gain access to by developing audit as well as other controls; recognize IO vs. memory vs. CPU traffic jams; make use of the Data Collector tool
Audit SQL Server instances
apply a safety strategy for auditing as well as managing the instance; set up an audit; set up server audits; track that customized an object; screen elevated advantages along with unsolicited efforts to attach; policy-based management
Manage data (20–25%)
Configure and maintain a back-up strategy
take care of various backup designs, including point-in-time healing; secure consumer information even if backup media is shed; do backup/restore based upon correct methods including backup redundancy; recuperate from a damaged drive; manage a multi-TB data source; apply as well as evaluate a data source implementation as well as a backup technique (multiple declare individual data source and tempdb, dispersing data source files, backup/restore); back up a SQL Server setting; back up system databases
Restore databases
bring back a database protected with TDE; recover data from a damaged DB (numerous mistakes in DBCC checkdb); restore to a moment; data group restore; web page degree bring back
Implement and maintain indexes
evaluate physical attributes of indexes as well as carry out index maintenance; recognize fragmented indexes; recognize extra indexes; apply indexes; defrag/rebuild indexes; set up an upkeep method for indexes and statistics; optimize indexes (full, filter index); stats (full, filter) force or fix queue; when to reconstruct vs. reorg as well as index; full text indexes; column store indexes
configure server safety; secure the SQL Server using Windows Account/ SQL Web server accounts, server roles; produce log in accounts; manage accessibility to the server, SQL Web server circumstances, and data sources; develop as well as keep user-defined server roles; handle certificate logins
Manage database security
configure database protection; database degree, approvals; shield items from being changed; bookkeeping; file encryption
Manage users and database roles
produce accessibility to server/ data source with least opportunity; manage security roles for individuals and also administrators; create database user accounts; had login
Troubleshoot security
manage certifications and also tricks; endpoints
Implement high availability (5–10%)
Implement Always On
Carry out Always On accessibility teams; implement Always On failover clustering
Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014
Design and implement a data warehouse (10–15%)
Design and implement dimensions
style shared/conformed measurements; figure out if you require support for gradually changing measurements; determine characteristics; design power structures; determine whether you need celebrity or snow schema; determine the granularity of relationship with truth tables; identify the requirement for bookkeeping or family tree; establish tricks (company transactional or your very own data warehouse/surrogate keys); execute measurements; implement data family tree of a measurement table
Design and implement fact tables
style an information stockroom that sustains lots of to many connections; properly index a fact table; using columnstore indexes; separating; additive steps; semi additive procedures; non additive actions; carry out reality tables; identifying the loading approach for the reality tables; carry out data lineage of a truth table; design recap aggregation tables
Extract and transform data (20–25%)
Define connection managers
plan the arrangement of connection supervisors; plan level or job level connection manager; define a link string; parameterization of connection strings
Design data flow
define data resources and also destinations; differentiate blocking and non-blocking makeovers; use various techniques to pull out transformed data from data sources; determine ideal information circulation elements; identify the need for sustaining Slowly Transforming Dimensions (SCD); identify whether to utilize SQL Joins or SSIS lookup or combine sign up with improvements; set handling versus row by row processing; identify the suitable transform to use for a specific task; determine the need as well as technique for identification mapping and also deduplicating; blurry lookup, blurry organizing and also Information High quality Provider (DQS) makeover; figure out the requirement for custom data resources, locations, and changes; establish what to do with incorrect rows; figure out bookkeeping demands; trusted/authoritative data sources, consisting of stockroom metadata; extracting only transformed rows
Implement data flow
debug information flow; make use of the suitable data circulation parts; SQL/ SSIS data improvement; produce SSIS bundles that support slowly changing dimensions; use the lookup job in SSIS; map identifications making use of SSIS blurry lookup (advanced); specify a data source and also destination; usage data streams; various classifications of transformations; review, transform and also pack data; comprehend which changes to use to accomplish a specific business task; information improvement change; performance tune an SSIS dataflow; enhance Assimilation Solutions bundles for rate of execution; preserve information stability, consisting of great data circulation
Manage SSIS package execution
schedule package execution by using SQL Web server Agent; implement plans by utilizing DTEXEC; implement plans by utilizing SQL Web server Administration Workshop; carry out package execution; plan and layout plan implementation technique; usage PowerShell to implement manuscript; keep track of the execution using Administration Workshop; usage DTEXECUI; ETL reactivate capability
Implement script tasks in SSIS
determine if it is appropriate to make use of a manuscript task; extending the capacity of a control circulation; do a customized activity as required (not on every row) throughout a control flow
Load data (25–30%)
Design control flow
establish control circulation; figure out containers as well as tasks required; determine precedence restrictions; design an SSIS plan strategy with rollback, hosting and also purchase control; determine in between one bundle or numerous packages; establish event handlers; determine variables; identify criteria on plan as well as task degree; determine connection supervisors and also whether they are package or project degree; identify the demand for custom tasks; determine just how much details you need to log from a bundle; determine the demand for checkpoints; determine safety needs
Implement package logic by using SSIS variables and parameters
individual variables; variable range, data type; implement parameterization of residential or commercial properties utilizing variables; using variables in priority restrictions; referring to SSIS system variables; style vibrant SSIS plans; bundle arrangements (documents or SQL tables); expressions; plan and also job criteria; job level link supervisors; variables; carry out dynamic package behavior; configure bundles in SSIS for different environments, plan setups (xmlconfiguration documents, SQLServer table, pc registry access; moms and dad bundle variables, setting variable); parameters (package as well as job level); project link supervisors; residential property expressions (usage expressions for connection supervisors).
Implement control flow
checkpoints; debug control circulation; carry out the proper control flow task to fix a trouble; information profiling; usage sequence containers and loophole containers; handle purchases in SSIS plans; managing similarity; utilizing priority constraint to manage job execution sequence; creating plan design templates; making use of the implement package task.
Implement data load options
implement a full as well as step-by-step data lots technique; prepare for an incremental update of the relational Data Mart; prepare for lots into indexed tables; configure proper mass lots alternatives; choose an appropriate tons method (SSIS Location versus T-SQL) and also load separated tables.
Implement script components in SSIS
develop an SSIS package that deals with SCD Kind 2 modifications without utilizing the SCD element; collaborate with script part in SSIS; deciding when it is appropriate to make use of a script element versus an integrated in; resource, transformation, location part; usage instances: internet service source and also location, getting the error message.
Configure and deploy SSIS solutions (20–25%)
Troubleshoot data integration issues
efficiency problems; connection issues; implementation of a job or makeover failed; reasoning concerns; demonstrate awareness of the brand-new SSIS logging facilities; repair a failed bundle implementation to establish the origin of failing; troubleshoot SSIS package failure from an invalid datatype; execute break points; data audiences; profile information with different tools; set cleaning
Install and maintain SSIS components
software setup (IS, monitoring tools); advancement box as well as server; mount specifics for remote package implementation; planning for installation (32- versus 64-bit); upgrade; provisioning the accounts; developing the magazine
Implement auditing, logging, and event handling
audit bundle execution by using system variables; circulate events; usage log suppliers; log an SSIS execution; develop signaling and also notification devices; use Occasion Handlers in SSIS to track ETL occasions as well as mistakes; implement custom-made logging
Deploy SSIS solutions
create as well as configure an SSIS magazine; deploy SSIS bundles by using the release utility; release SSIS plans to SQL or documents system locations; confirm released bundles; release plans on multiple web servers; exactly how to set up personalized parts as well as jobs; release SSIS plans by utilizing DTUTIL
Configure SSIS security settings
SSIS catalog data source functions; bundle security degrees; safe Combination Providers plans that are released at the file system; secure Assimilation Providers parameters, arrangement
Build data quality solutions (15–20%)
Install and maintain data quality services
Setup prerequisites;. msi package; adding individuals to the DQ roles; identity evaluation, including data administration
Implement master data management solutions
mount Master Information Services (MDS); implement MDS; develop models, entities, power structures, collections, features; specify safety duties; import/export; subscriptions
Create a data quality project to clean data
profile Online Transaction Handling (OLTP) and also various other resource systems; information quality knowledge base management; produce data quality project; usage data top quality client; improve data top quality; identification mapping and also deduplicating; take care of history and also data quality; manage data quality/cleansing
