Trening za pripremu certifikata
Microsoft Certified Technology
Specialist (MCTS): SQL Server 2005
Pregled
Ovaj trening je priprema za certifikacijski ispit „Exam 70‐431: TS: Microsoft SQL Server 2005 ‐ Implementation and Maintenance“. Polaganjem ispita stječete preduvjete za certifikat „Microsoft Certified Technology Specialist (MCTS): SQL Server 2005“.Cilj edukacije
Cilj ovog treninga je upoznavanje MS SQL Server 2005 RDBMS platforme i administracijskih zadataka vezanih uz kreiranje, održavanje i optimizaciju baza podataka. Naučit ćete osnovne i napredne tehnike rada sa objektima MS SQL Server 2005 baza podataka.Trajanje edukacije
1 mjesecMetoda isporuke
Blended learning ‐ samostalno e‐učenje (70%) ‐ online laboratorij (10%) ‐ uživo sesije sa mentorom (10%) ‐ online sesije sa mentorom (10%)Program edukacije
Tjedan Aktivnost Tip aktivnosti Trajanje (sati)
1 Uvodno predavanje Uživo sa mentorom 3 1 Installing SQL Server 2005 Samostalno e‐učenje 3 1 Configuring SQL Server 2005 Samostalno e‐učenje 3 2 Getting Started with Databases and Transact‐SQL in Microsoft® SQL Server™ Samostalno e‐učenje 2
2005 2 Performing Basic Queries in Microsoft® SQL Server™ 2005 Samostalno e‐učenje 2 2 Hands‐on LAB Online sa mentorom 3 3 Creating and Implementing Database Objects in SQL Server 2005 Samostalno e‐učenje 3 3 Retrieving Data and Running Queries in SQL Server 2005 Samostalno e‐učenje 2 4 Grouping and Summarizing Data in Microsoft® SQL Server™ 2005 Samostalno e‐učenje 2 4 Joining Data from Multiple Tables in Microsoft® SQL Server™ 2005 Samostalno e‐učenje 2 4 Hands‐on LAB Online sa mentorom 3 5 SQL Server 2005 Messaging and Querying Tools Samostalno e‐učenje 3 5 Managing and Maintaining SQL Server 2005 Databases Samostalno e‐učenje 2 6 Working with Subqueries in Microsoft® SQL Server™ 2005 Samostalno e‐učenje 2 6 Modifying Data in Microsoft® SQL Server™ 2005 Samostalno e‐učenje 2 6 Hands‐on LAB Online sa mentorom 3 7 SQL Server 2005 Database Performance and Optimization Samostalno e‐učenje 2 7 Monitoring and Diagnosing Database Problems in SQL Server 2005 Samostalno e‐učenje 3 8 Querying Metadata, XML, and Full‐Text Indexes in Microsoft® SQL Server™ 2005 Samostalno e‐učenje 2 8 Using Programming Objects for Data Retrieval in Microsoft® SQL Server™ 2005 Samostalno e‐učenje 2 8 Hands‐on LAB Online sa mentorom 3
9 SQL Server 2005 High Availability and Disaster Recovery Samostalno e‐učenje 3 9 SQL Server 2005 Backup and Restores Samostalno e‐učenje 2 10 Using Advanced Querying Techniques in Microsoft® SQL Server™ 2005 Samostalno e‐učenje 2 10 Hands‐on LAB Online sa mentorom 3 11 70‐431 TS: Microsoft SQL Server 2005 ‐ Implementation and Maintenance Samostalno e‐učenje 4 12 Završno predavanje Uživo sa mentorom 3
Curriculum
Installing SQL Server 2005 (234620) Overview/Description In this course you learn how to install SQL Server 2005 and upgrade to SQL Server 2005 from previous versions Expected Duration (hours) 3.0 Objectives • recognize how SQL Server 2005 can help improve enterprise data management, developer productivity, and business intelligence. • differentiate between the components and services provided by SQL Server 2005. • distinguish between SQL Server 2005 management tools. • identify the requirements and considerations for installing SQL Server 2005. • configure a SQL Server 2005 installation in a given scenario. • install SQL Server 2005. • select the appropriate SQL Server 2005 upgrade path for a given scenario. • perform the steps to upgrade to SQL Server 2005. • upgrade to SQL Server 2005 Configuring SQL Server 2005 (234633) Overview/Description In this course you learn how to configure a SQL Server instance and create a database, configure logins, users, and schemas for a database, and set up a linked server configuration Expected Duration (hours) 3.0Objectives • use SQL Server Surface Area Configuration to configure a SQL Server 2005 installation. • create a SQL Server database in a given scenario. • create a database in SQL Server 2005. • recognize the security functionality of the SQL Server 2005 Database Engine. • create logins and grant permissions in a given scenario. • create logins, users, and schemas in SQL Server 2005. • recognize the security mechanisms provided by encryption, the TRUSTWORTHY database property, and execution context in SQL Server 2005. • recognize how SQL Server 2005 can access data from OLE DB data sources on remote servers using a linked server configuration. • use SQL Server Management Studio to configure a linked server in a given scenario. • configure a linked server using SQL Server Management Studio. Creating and Implementing Database Objects in SQL Server 2005 (234647) Overview/Description In this course you learn how to create and implement key database objects in SQL Server 2005 Expected Duration (hours) 3.2 Objectives • create a database table in SQL Server 2005, given a scenario. • create a database view in a given scenario. • create a database table and a database view. • identify recommended guidelines for designing indexes. • create a database index in a given scenario. • create an online index in SQL Server 2005. • create functions and triggers in a given scenario. • create stored procedures in a given scenario. • manage stored procedures in a given scenario. • create and implement a stored procedure. Retrieving Data and Running Queries in SQL Server 2005 (234661) Overview/Description In this course you learn how to perform relational data and XML data management tasks, and work with assemblies and transactions in SQL Server 2005 Expected Duration (hours) 3.0 Objectives • retrieve data from a database in a given scenario. • modify data from a relational database in a given scenario.
• use queries to retrieve and modify data in a SQL Server database. • retrieve, insert, and convert XML data in SQL Server 2005. • create and secure an HTTP endpoint in a given scenario. • work with XML data in SQL Server 2005. • create and modify an assembly in a given scenario. • control SQL Server transactions in a given scenario. • manage transactions in SQL Server 2005. SQL Server 2005 Messaging and Querying Tools (234686) Overview/Description In this course you learn how to implement Service Broker components and use full‐text search in SQL Server 2005 Expected Duration (hours) 1.9 Objectives • create Service Broker components in a given scenario. • create a Service Broker service. • recognize the routing, networking, and security features provided by Security Broker. • identify the features of Full‐Text Searches and recognize considerations for implementing them. • create and troubleshoot full‐text queries in a given scenario. • use full‐text search to query SQL Server 2005. Managing and Maintaining SQL Server 2005 Databases (234704) Overview/Description In this course you learn how to manage and maintain SQL Server 2005 databases Expected Duration (hours) 3.2 Objectives • export bulk data in SQL Server 2005 in a given scenario. • import bulk data into SQL Server 2005 in a given scenario. • import and export bulk data in SQL Server 2005. • create and manage jobs using SQL Server Agent. • configure alerts using SQL Server Agent in a given scenario. • configure operators using SQL Server Agent in a given scenario. • perform management and monitoring tasks using the SQL Server Agent service. • perform database management tasks in SQL Server 2005, given a scenario. • perform the steps required to shrink a database in a given scenario. • check and maintain database integrity in SQL Server 2005. SQL Server 2005 Database Performance and Optimization (234718)
Overview/Description In this course you learn how to monitor and optimize database performance using SQL Server utilities Expected Duration (hours) 2.6 Objectives • configure SQL Server Profiler Trace files in a given scenario. • configure System Monitor to monitor SQL Server performance in a given scenario. • use SQL Server Profiler and System Monitor to monitor an instance of SQL Server 2005. • recognize the features and functionality of Database Engine Tuning Advisor. • use Database Engine Tuning Advisor to tune a SQL Server database in a given scenario. • tune a SQL Server database using Database Engine Tuning Advisor. • retrieve SQL Server state information using dynamic management views (DMVs) in a given scenario. • query dynamic management views and functions. Monitoring and Diagnosing Database Problems in SQL Server 2005 (234730) Overview/Description In this course you learn how to monitor and resolve database blocks, and connect to the server using a dedicated administrator connection (DAC) Expected Duration (hours) 1.3 Objectives • recognize how locking is used to secure data integrity in a SQL Server database. • detect and end deadlocks and blocking connections in a given scenario. • detect and terminate a transaction lock. • connect to the server using the dedicated administrator connection (DAC) in a given scenario. • connect to a non‐responsive database using the dedicated administrator connection (DAC). SQL Server 2005 High Availability and Disaster Recovery (234771) Overview/Description In this course you learn how to implement database mirroring, log shipping, database snapshots, and data replication in SQL Server 2005 Expected Duration (hours) 3.0 Objectives • recognize the features and functionality of database mirroring. • configure database mirroring in a given scenario.
• configure database mirroring in SQL Server 2005. • configure and monitor log shipping in a given scenario. • configure log shipping options in SQL Server 2005. • configure database snapshots in a given scenario. • configure data replication in a given scenario. • manage replication in SQL Server 2005, given a scenario. • set up data replication in SQL Server 2005. SQL Server 2005 Backup and Restores (234927) Overview/Description In this course you learn how to back up and restore a database in SQL Server 2005 Expected Duration (hours) 1.7 Objectives • determine the appropriate recovery model and backup strategy for a given scenario. • perform database backups in a given scenario. • back up a SQL Server database. • restore a SQL Server backup in a given scenario. • move a SQL Server database in a given scenario. • perform a database restore in SQL Server 2005. Course 4326: Getting Started with Databases and Transact‐SQL in Microsoft® SQL Server™ 2005 Overview/Description In this course, you analyze how client/server architecture works, and examine the various database and business tasks that you can perform by using the components of Microsoft SQL Server 2005. You also query SQL Server 2005 databases by using T‐SQL querying tools. In addition, this course explains the basics of the T‐SQL language, its syntax, and how to manage T‐SQL scripts Expected Duration (hours) 2.0 Objectives • Describe the architecture and components of SQL Server 2005. • Describe the structure of a SQL Server database. • Explain the basics of the SQL language. • Describe the syntax elements of T‐SQL. • Explain how to manage T‐SQL scripts. • Use T‐SQL querying tools to query SQL Server 2005 databases. Course 4327: Performing Basic Queries in Microsoft® SQL Server™ 2005
Overview/Description In this course, you perform basic queries in Microsoft SQL Server 2005. You use Transact‐SQL (T‐SQL) statements such as SELECT to write queries, filter data, and format result sets. In addition, this course covers the performance considerations that affect data retrieval and the use of the Query Optimizer. Expected Duration (hours) 2.0 Objectives • Retrieve data by using the SELECT statement. • Filter data by using different search conditions. • Explain how to work with NULL values. • Format result sets. • Describe the performance considerations that affect data retrieval. Course 4328: Grouping and Summarizing Data in Microsoft® SQL Server™ 2005 Overview/Description In this course, you group and summarize data when generating reports in Microsoft SQL Server 2005. You rank group data and create cross‐tabulation queries. You also use aggregate functions including functions native to SQL Server, functions that use NULL values, and custom aggregate functions using the .NET Framework. In addition, this course covers the GROUP BY, HAVING, COMPUTE, PIVOT, UNPIVOT, ROLLUP, and CUBE clauses and functions such as SUM, AVG, COUNT, MAX, MIN, GROUPING, RANK, DENSE_RANK, NTILE, and ROW_NUMBER Expected Duration (hours) 2.0 Objectives • Summarize data by using aggregate functions. • Summarize grouped data by using the GROUP BY and COMPUTE clauses. • Rank grouped data. • Create cross‐tabulation queries by using the PIVOT and UNPIVOT clauses. Course 4329: Joining Data from Multiple Tables in Microsoft® SQL Server™ 2005 Overview/Description In this course, you retrieve data from multiple tables by using Transact‐SQL (T‐SQL) in Microsoft SQL Server 2005. You apply the inner join, outer join, cross join, and self join to retrieve the desired result set. You also use the APPLY operator to join using a user‐defined function. In addition, you examine the various techniques provided by SQL Server 2005 to combine and limit result sets such as UNION, EXCEPT, INTERCEPT, TOP, and TABLESAMPLE. Expected Duration (hours) 2.0
Objectives • Query multiple tables by using joins. • Apply joins for typical reporting needs. • Combine and limit result sets. Course 4330: Working with Subqueries in Microsoft® SQL Server™ 2005 Overview/Description In this course, you write subqueries and correlated subqueries by using Transact‐SQL (T‐SQL) in Microsoft SQL Server 2005. You determine the best option among subqueries, joins, and temporary tables to get your desired result. You refine subqueries by using the ANY, ALL, and SOME operators. In addition, this course explains common table expressions (CTEs) to write recursive queries. Expected Duration (hours) 2.0 Objectives • Write basic subqueries. • Write correlated subqueries. • Compare subqueries with joins and temporary tables. • Use common table expressions in queries. Course 4331: Modifying Data in Microsoft® SQL Server™ 2005 Overview/Description In this course, you modify data in tables using Transact‐SQL (T‐SQL) in Microsoft SQL Server 2005. You insert, delete, and update single or multiple rows of data in the tables. You use statements such as INSERT, DELETE, UPDATE, INSERT…SELECT, SELECT INTO, and TRUNCATE TABLE statements. In addition, you examine implicit and explicit transactions, the importance of transaction isolation levels, and how to manage transactions. Expected Duration (hours) 2.0 Objectives • Explain how SQL Server implements transactions. • Insert data into tables. • Delete data from tables. • Update data in tables. Course 4332: Querying Metadata, XML, and Full‐Text Indexes in Microsoft® SQL Server™ 2005 Overview/Description In this course, you query metadata, XML data, and full‐text indexes by using Transact‐SQL (T‐SQL) in Microsoft SQL Server 2005. You query metadata by using SQL Server views and commands. You
query XML data by using OpenXML and XQuery. You also query relational data combined with XML data. In addition, you analyze the purpose and process of querying full‐text indexes. Expected Duration (hours) 2.0 Objectives • Query metadata. • Describe the functionality of XML. • Query XML data. • Describe the functionality of full‐text indexes. • Query full‐text indexes. Course 4333: Using Programming Objects for Data Retrieval in Microsoft® SQL Server™ 2005 Overview/Description In this course, you retrieve data by using the various programming objects provided by Microsoft SQL Server 2005. You encapsulate expressions with user‐defined functions and SQL Server views. You also utilize stored procedures, and triggers to encapsulate T‐SQL logic. In addition, you perform distributed queries and learn how SQL Server works with heterogeneous data such as databases, spreadsheets, and other servers. Expected Duration (hours) 2.0 Objectives • Encapsulate expressions by using user‐defined functions. • Encapsulate queries by using views. • Explain how stored procedures encapsulate T‐SQL logic. • Write distributed queries. Course 4334: Using Advanced Querying Techniques in Microsoft® SQL Server™ 2005 Overview/Description In this course, you apply advanced querying techniques in Microsoft SQL Server 2005. You follow best practices to query complex data involving data/time and multinational data. You query complex table structures such as data stored in hierarchies and self‐referencing tables. You also analyze query execution and optimize query performance. In addition, you use Microsoft Visual SourceSafe for managing query source files. Expected Duration (hours) 2.0 Objectives • Explain the recommendations for querying complex data.
• Query complex table structures. • Write efficient queries. • Use various techniques when working with complex queries. • Maintain query files. 70‐431 TS: Microsoft SQL Server 2005 ‐ Implementation and Maintenance (TP70431) Overview/Description Generally taken near the end of a program of certification‐orientated study, the 70‐431 TS: Microsoft SQL Server 2005 ‐ Implementation and Maintenance TestPrep enables the learner to test their knowledge in a simulated certification testing environment. Learners can take TestPrep in two different modes: Study and Certification. Study mode is designed to maximize learning by providing feedback, while Certification mode is designed to mimic a certification exam.
