SQL Server Certification - Microsoft SQL CertificationThe Knowledge Academy
En Cardiff (Gales)
£ 1.495 - ($ 29.166)
- Cardiff (Gales)
¿Qué aprendés en este curso?
The Knowledge Academy Designing Solutions for SQL Server (M20465) 3 day course covers the following topics:
- Delegates are responsible for the creation of plans and designs for database structure, storage, objects and servers. They create the plan for the environment in which the database solution runs.
Who should attend?
- This exam is intended for database professionals who design and build database solutions in the organisation.
- Design for business requirements
- Business to data translations, identify which SQL Server components to use to support business requirements, design a normalisation area, de-normalise technically (versus by remodelling) by using SQL Server features (materialisation via indexed views and more)
- Design SQL Server instances
- Identify hardware for new instances; design CPU affinity; design clustered instances using Microsoft Distributed Transaction Control (MSDTC); define instance memory allocation; design installation strategies, including sysprep, slipstream and SMB file server; define cross db ownership chaining
- Design backup and recovery
- Design a backup strategy based on business needs, including differential, file, log, striped and Microsoft Azure Blob Storage Service; design a database snapshot strategy; design appropriate recovery models; design a system database backup strategy; recover Tail-Log backups
- Design a database model
- Design a logical schema; design a data access and data layer architecture; design a database schema; design a security architecture; design a cross-server instance database model, including linked servers, security, providers, distributed transactions, distributed partitioned views and Service Broker
- Design tables
- Design tables appropriately, including physical tables, temp tables, temp table variables, common table expressions, commonstore indexes, user-defined table types, FILESTREAM, FILETABLE and In-Memory OLTP; design views and table valued functions; design a compression strategy, including row and page; select an appropriate data type; design computed columns
- Design T-SQL stored procedures
- Create stored procedures; design a data-access strategy using stored procedures; design appropriate stored procedure parameters, including input, output and Table Valued; design error handling; design an In-Memory OLTP strategy for stored procedures
- Design a management automation strategy
- Create a data archiving solution; design automation and auditing, including jobs, alerts, operators, SSIS, CDC, auditing, DDL triggers and Windows PowerShell; automate across multiple databases and instances; design data batch processing: design a database load test; deploy to different environments, including development, staging and production
- Design for transactions
- Manage transactions, including time, savepoint and mark; design for implicit and explicit transactions; ensure data integrity by using transactions; design error handling for transactions, including TRY, CATCH and THROW
- Design instance-level security configurations
- Implement separation of duties using different login roles; choose an authentication type, including logon triggers, regulatory requirements and certificates; implement data encryption, including database master key and configuration; implement Data Description Language (DDL) triggers; define a secure service account
- Design a maintenance strategy for database servers
- Design maintenance plans; design index maintenance, including rebuild, defragmentation, statistics, online rebuilds, offline rebuilds and thresholds; maintain physical and logical consistency (DBCC); manage database files, including LDF, MDF, In-Memory OLTP and rubbish collection; define a retention policy
- Design a solution to monitor performance and concurrency
- Identify performance monitor counters; monitor for performance and bottlenecks, including Wait Stats; design a query monitoring and review strategy; monitor for missing statistics
- Design a monitoring solution at the instance level
- Design auditing strategies, including Extended Events, Event traces, SQL Audit, Profiler-scheduled or event-based maintenance, Performance Monitor and DMV usage; set up file and table growth monitoring; collect performance indicators and counters; create jobs to monitor server health; audit using Windows Logs