ChromeIS Pvt. Ltd. is proud to be a Microsoft Partner!

Microsoft SQL Server

Be the Champ !

Introduction:

Microsoft SQL Server offers a robust architecture for enterprise data management, developer productivity and business intelligence. This course provides the knowledge and practical skills needed to maximize the benefits of SQL Server. You gain a solid ground on which to build essential SQL Server development and administrative skills, including creating databases, generating Transact-SQL statements, implementing security and reporting through reporting services.
This training program aims to provide the participants proven, innovative, and technical skills needed to manage database systems in effective ways. SQL Server 2012 offers a robust architecture for enterprise data management and business intelligence. ChromeIS SQL Server training program aim to provide essentials skills for SQL Server development and administration, including creating databases, generating Transact-SQL statements, implementing security and reporting through reporting services. After this course an attendee will be able to:

  • Install and Configure MS SQL Server, Understand Database Concepts & Normalization
  • Understanding Data Warehousing & Data Mining and Introduction To Business Intelligence And Reporting
  • Designing, Implement and Manage Databases, Transaction Logs & Database Snapshots
  • Create Tables, UDTs, Triggers, Views, Stored Procedure & Functions
  • Implement Constraints, Triggers, Indexes & User Defined Data Types, Full-Text search
  • Working With Data Using Transact-SQL And Management Studio
  • Managing XML data in SQL Server, Implementing HTTP Endpoints, Service Broker Components
  • Implement login and database security, manage permissions, Configuring Linked Servers & Import/Exporting Data using Linked Servers
  • Implementing Database Mirroring & Log Shipping
  • Manage Complex Multi-Step Jobs with SQL Server Agent
  • Design Transactions that Maximize Concurrency and Minimize Contention
  • Manage XML Data & Implement HTTP End-Point
  • Implementing High Availability and Disaster Recovery, Performance Tuning, Monitoring
  • Maintain Databases (Backup, Restore & Replication)
  • Analyze and Cure Performance Problems using SQL Server’s Tools
Contents at a Glance

The training course has been divided into 3 parts; please refer to each section below to view details.

Part 1: Querying Microsoft SQL Server 2012

  1. Create Database Objects (24%)
    1. Create and alter tables using T-SQL syntax (simple statements)
      Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE
    2. Create and alter views (simple statements)
      Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP
    3. Design views
      Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications
    4. Create and modify constraints (simple statements)
      Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints
    5. Create and alter DML triggers
      Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers
  2. Working with data (27%)
    1. Query data by using SELECT statements
      Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce
    2. Implement sub-queries
      Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement
    3. Implement data types
      Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns
    4. Implement aggregate queries
      New analytic functions; grouping sets; spatial aggregates; apply ranking functions
    5. Query and manage XML data
      Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing.
  3. Modify data (24%)
    1. Create and alter stored procedures (simple statements)
      Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL
    2. Modify data by using INSERT, UPDATE, and DELETE statements
      Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement
    3. Combine datasets
      Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements
    4. Work with functions
      Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)
  4. Troubleshoot and optimize (25%)
    1. Optimize queries
      Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in
    2. Manage transactions
      Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount
    3. Evaluate the use of row-based operations vs. set-based operations
      When to use cursors; impact of scalar UDFs; combine multiple DML operations
    4. Implement error handling
      Implement try/catch/throw; use set based rather than row based logic; transaction management

 

 

Part 2: Administering Microsoft SQL Server 2012 Databases

  1. Install and Configure (19%)
    1. Plan installation
      Evaluate installation requirements; design the installation of SQL Server and its components (drives, service accounts, etc.); plan scale-up vs. scale-out basics; plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth; manage the technologies that influence SQL architecture (for example, service broker, full text, scale out, etc.); 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
    2. Install SQL Server and related services
      Test connectivity; enable and disable features; install SQL Server database engine and SSIS (not SSRS and SSAS); configure an OS disk
    3. Implement a migration strategy
      Restore vs detach/attach; migrate security; migrate from a previous version; migrate to new hardware; migrate systems and data from other sources
    4. Configure additional SQL Server components
      Set up and configure all SQL Server components (Engine, AS, RS and SharePoint integration) in a complex and highly secure environment; configure full-text indexing; SSIS security; filestream; filetable
    5. Manage SQL Server Agent
      Create, maintain, and monitor jobs; administer jobs and alerts; automate (setup, maintenance, monitoring) across multiple databases and multiple instances; send to “Manage SQL Server Agent jobs”
  2. Maintain instances and databases (17%)
    1. Manage and configure databases
      Design multiple file groups; database configuration and standardization: autoclose, autoshrink, recovery models; manage file space, including adding new filegroups and moving objects from one filegroup to another; implement and configure contained databases; data compression; configure TDE; partitioning; manage log file growth; DBCC
    2. Configure SQL Server instances
      Configure and standardize a database: autoclose, autoshrink, recovery models; install default and named instances; configure SQL to use only certain CPUs (affinity masks, etc.); configure server level settings; configure many databases/instance, many instances/server, virtualization; configure clustered instances including MSDTC; memory allocation; database mail; configure SQL Server engine: memory, filffactor, sp_configure, default options.
    3. Implement a SQL Server clustered instance
      Install a cluster; manage multiple instances on a cluster; set up subnet clustering; recover from a failed cluster node.
    4. Manage SQL Server instances
      Install an instance; manage interaction of instances; SQL patch management; install additional instances; manage resource utilization by using Resource Governor; cycle error logs.
  3. Optimize and Troubleshoot (14%)
    1. Identify and resolve concurrency problems
      Examine deadlocking issues using the SQL server logs using trace flags; design reporting database infrastructure (replicated databases); monitor via DMV or other MS product; diagnose blocking, live locking and deadlocking; diagnose waits; performance detection with built in DMVs; know what affects performance; locate and if necessary kill processes that are blocking or claiming all resources
    2. Collect and analyze troubleshooting data
      Monitor using Profiler; collect performance data by using System Monitor; collect trace data by using SQL Server Profiler; identify transactional replication problems; identify and troubleshoot data access problems; gather performance metrics; identify potential problems before they cause service interruptions; identify performance problems;, use 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
    3. Audit SQL Server instances
      Implement a security strategy for auditing and controlling the instance; configure an audit; configure server audits; track who modified an object; monitor elevated privileges as well as unsolicited attempts to connect; policy-based management.
  4. Manage Data (19%)
    1. Configure and maintain a back-up strategy
      Manage different backup models, including point-in-time recovery; 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-TB 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.
    2. Restore databases
      Restore a database secured with TDE; recover data from a damaged DB (several errors in DBCC checkdb); restore to a point in time; file group restore; page level restore
    3. Implement and maintain indexes
      Inspect physical characteristics of indexes and perform index maintenance; identify fragmented indexes; identify unused indexes; implement indexes; defrag/rebuild indexes; set up a maintenance strategy for indexes and statistics; optimize indexes (full, filter index); statistics (full, filter) force or fix queue; when to rebuild vs. reorg and index; full text indexes; column store indexes.
    4. Import and export data
      Transfer data; bulk copy; bulk insert.
  5. Implement Security (18%)
    1. Manage logins and server roles
      Configure server security; secure the SQL Server using Windows Account / SQL Server accounts, server roles; create log in accounts; manage access to the server, SQL Server instance, and databases; create and maintain user-defined server roles; manage certificate logins
    2. Manage database permissions
      Configure database security; database level, permissions; protect objects from being modified
    3. Manage users and database roles
      Create access to server / database with least privilege; manage security roles for users and administrators; create database user accounts; contained logins
    4. Troubleshoot security
      Manage certificates and keys; endpoints
  6. Implement High Availability (12%)
    1. Implement AlwaysOn
      Implement a mirroring solution using AlwaysOn; failover
    2. Implement database mirroring
      Set up mirroring; monitor the performance of database mirroring
      Troubleshoot replication problems; identify appropriate replication strategy

 

Part 3: Implementing a Data Warehouse with Microsoft SQL Server 2012

  1. Design and Implement a data warehouse (11%)
    1. Design and implement dimensions
      Design shared/conformed dimensions; determine if you need support for slowly changing dimensions; determine attributes; design hierarchies; determine whether you need star or snowflake schema; determine the granularity of relationship with fact tables; determine the need for auditing or lineage; determine keys (business transactional or your own data warehouse/surrogate keys); implement dimensions; implement data lineage of a dimension table
    2. Design and implement fact tables
      Design a data warehouse that supports many to many relationships; appropriately index a fact table; using columnstore indexes; partitioning; additive measures; semi additive measures; non additive measures; implement fact tables; determining the loading method for the fact tables; implement data lineage of a fact table; design summary aggregation tables
  2. Extract and transform data (23%)
    1. Define connection managers
      Plan the configuration of connection managers; package level or project level connection manager; define a connection string; parameterization of connection strings
    2. Design data flow
      Define data sources and destinations; distinguish blocking and non-blocking transformations; use different methods to pull out changed data from data sources; determine appropriate data flow components; determine the need for supporting Slowly Changing Dimensions (SCD); determine whether to use SQL Joins or SSIS lookup or merge join transformations; batch processing versus row by row processing; determine the appropriate transform to use for a specific task; determine the need and method for identity mapping and deduplicating; fuzzy lookup, fuzzy grouping and Data Quality Services (DQS) transformation; determine the need for text mining; determine the need for custom data sources, destinations, and transforms; determine what to do with erroneous rows; determine auditing needs; determine sampling needs for data mining (advanced); trusted/authoritative data sources, including warehouse metadata.
    3. Implement data flow
      Debug data flow; use the appropriate data flow components; SQL / SSIS data transformation; create SSIS packages that support slowly changing dimensions; use the lookup task in SSIS; map identities using SSIS fuzzy lookup (advanced); specify a data source and destination; use data flows; different categories of transformations; read, transform and load data; understand which transforms to use to accomplish a specific business task; data correction transformation; performance tune an SSIS dataflow; optimize Integration Services packages for speed of execution; maintain data integrity, including good data flow.
    4. Manage SSIS package execution
      Schedule package execution by using SQL Server Agent; execute packages by using DTEXEC; execute packages by using SQL Server Management Studio; implement package execution; plan and design package execution strategy; use PowerShell to execute script; monitor the execution using Manangement Studio; use DTEXECUI; ETL restartability.
    5. Implement script tasks in SSIS
      Determine if it is appropriate to use a script task; extending the capability of a control flow; perform a custom action as needed (not on every row) during a control flow
  3. Load data (27%)
    1. Design control flow
      Determine control flow; determine containers and tasks needed; determine precedence constraints; design an SSIS package strategy with rollback, staging and transaction control; decide between one package or multiple packages; determine event handlers; determine variables; determine parameters on package and project level; determine connection managers and whether they are package or project level; determine the need for custom tasks; determine how much information you need to log from a package; determine the need for checkpoints; determine security needs.
    2. Implement package logic by using SSIS variables and parameters
      User variables; variable scope, data type; implement parameterization of properties using variables; using variables in precedence constraints; referring to SSIS system variables; design dynamic SSIS packages; package configurations (file or SQL tables); expressions; package and project parameters; project level connection managers; variables; implement dynamic package behavior; configure packages in SSIS for different environments, package configurations (xmlconfiguration file, SQLServer table, registry entry; parent package variables, environment variable); parameters (package and project level); project connection managers; property expressions (use expressions for connection managers)
    3. Implement control flow
      Checkpoints; debug control flow; implement the appropriate control flow task to solve a problem; data profiling; use sequence containers and loop containers; manage transactions in SSIS packages; managing parallelism; using precedence constraint to control task execution sequence; creating package templates; using the execute package task
    4. Implement data load options
      Implement a full and incremental data load strategy; plan for an incremental update of the relational Data Mart.
    5. Implement script components in SSIS
      Create an SSIS package that handles SCD Type 2 changes without using the SCD component; work with script component in SSIS; deciding when it is appropriate to use a script component versus a built in; source, transformation, destination component; use cases: web service source and destination, getting the error message
  4. Configure and deploy SSIS solution (24%)
    1. Troubleshoot data integration issues
      Performance issues; connectivity issues; execution of a task or tranformation failed; logic issues; demonstrate awareness of the new SSIS logging infrastructure; troubleshoot a failed package execution to determine the root cause of failure; troubleshoot SSIS package failure from an invalid datatype; implement break points; data viewers; profile data with different tools; batch cleanup
    2. Install and maintain SSIS components
      Software installation (IS, management tools); development box and server; install specifics for remote package execution; planning for installation (32- versus 64-bit); upgrade; provisioning the accounts; creating the catalog
    3. Implement auditing, logging, and event handling
      Audit package execution by using system variables; propagate events; use log providers; log an SSIS execution; create alerting and notification mechanisms; use Event Handlers in SSIS to track ETL events and errors; implement custom logging
    4. Deploy SSIS solutions
      Create and configure an SSIS catalog; deploy SSIS packages by using the deployment utility; deploy SSIS packages to SQL or file system locations; validate deployed packages; deploy packages on multiple servers; how to install custom components and tasks; deploy SSIS packages by using DTUTIL
    5. Configure SSIS security settings
      SSIS catalog database roles; package protection levels; secure Integration Services packages that are deployed at the file system; secure Integration Services parameters, configuration
  5. Build data quality solutions (15%)
    1. Install and maintain data quality services
      Installation prerequisites; .msi package; adding users to the DQ roles; identity analysis, including data governance
    2. Implement master data management solutions
      Install Master Data Services (MDS); implement MDS; create models, entities, hierarchies, collections, attributes; define security roles; import/export; subscriptions
    3. Create a data quality project to clean data
      Profile Online Transaction Processing (OLTP) and other source systems; data quality knowledge base management; create data quality project; use data quality client; improve data quality; identity mapping and deduplicating; handle history and data quality; manage data quality/cleansing.
Course Project
Participants will develop a commercial level distributed database with help of their trainer. They will also plan & implement its replication on multiple servers as well as backup & restoration of the database.
Training Philosophy
Around 24 contact hours
Training contents have been developed by Industry professionals
Detailed discussions and experience sharing. Case studies from across industries
Interactive sessions, promoting inquisitive audience
Self assessment assignments
Certifications and Reward
ChromeIS Certificate of Attendance of subject training
ChromeIS Certified SQL Server Professional

70-461: Querying Microsoft SQL Server 2012
70-462: Administering a Microsoft SQL Server 2012 Database
70-463: Implementing Data Warehouses with Microsoft SQL Server 2012
After passing above three exams, a successful candidate will become Microsoft Certified Solutions Associate (MCSA)

MCITP: Database Developer SQL Server 2012 (ChromeIS Certificate to pass)
MCITP: Database Administrator SQL Server 2012 (ChromeIS Certificate to pass)

Program Fee and Discount

Please contact us for course fee details.
Call 0300-9498855 or drop email to trainings @ chromeis.com

10% discount on a group of at least 2 participants
25% discount on group of 4 participant with early registration
50% discount for Candidates seeking revision

Public Material: Download latest version of slides is not available.

 

2913 Total Views 6 Views Today