linkedin

Home - Courses - - postTitle –

 

MCSA SQL Server 2012/2014 Course in Dubai

Overview of MCSA SQL Server 2012/2014 Course

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.

Job role: Database Administrator

Required exams: 70-461, 70-462, 70-463

MCSA SQL Server 2012/2014 Course Certification details

Exam 70-461: Querying Microsoft SQL Server 2012/2014

Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)

This test is intended for SQL Web server database managers, system designers, and also programmers with 2 or even more years of experience, who are seeking to verify their abilities as well as expertise in creating inquiries.

Part of the requirements for: MCSA: SQL Server 2012/2014 Course

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

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

Implement aggregate queries

  • brand-new analytic features; organizing sets; spatial aggregates; apply ranking functions

Query and manage XML data

  • 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

Import and export data

  • transfer data; bulk copy; bulk insert

Implement security (15–20%)

Manage logins and server roles

  • 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

Implement replication

  • troubleshoot replication issues; determine suitable replication approach

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

To know more on MCSA SQL Server 2012/2014 course, click here.

To know more about other courses in IT Academy, click here.

whatsaapnow
Quick Enquiry

    situs daftar slot online