Exam 70-462 Chapter 10 Lesson 1: Indexes

Here is a short video about indexes in Microsoft SQL server 2012.

[youtube https://www.youtube.com/watch?v=Ckm2rjIwOKk]

Exam 70-462 Chapter 11, Lesson 3: Restore

Here is a short video about restore for the 70-462 exam. My suggestion is that you try all the things, so that you learn them.

 

[youtube https://www.youtube.com/watch?v=ev4BM7QQA9Q]

Exam 70-462–Chapter 11, Lesson 2: Backup

Here is a short video about backups and the required skills that you must aquire to take the exam 70-462.

[youtube https://www.youtube.com/watch?v=XARnU-ShzIE]

Exam 70-462–Chapter 11, Lesson 1: SQL Server Agent

The SQL Server Agent is the scheduling tool for Microsoft SQL Server 2012, and you must at minimum learn what a job and an alert is. If you don’t know that, and if you can’t setup a simple job and an alert, then you will not pass the exam.

When you have learned the basics of alerts and jobs, then you must learn

  1. How to setup the database mail option in the SQL Server Agent.
  2. Operators
  3. Notifications  and schedules on jobs
  4. Setting up a proxy and use it in a job step.
  5. Multi instance management.

You must also understand the impact of the account you have setup the SQL Server Agent Service with.

I forgot to mention one thing in the demo, and that is if you are not a member of the sysadmin, then you must be member of a specific role in the MSDB database:

 

  1. SQLAgentUserRole
  2. SQLAgentReaderRole
  3. SQLAgentOperatorRole

 

[youtube https://www.youtube.com/watch?v=x-x2kzIPpZ0]

Exam 70-462–Chapter 7, Lesson 2: Replication

Below is a video that describes and show the different types of replication that you need to know, when you want to try the Exam 70-462. To get full benefit from this blog you must have the book for the Training Kit 70-462 (ISBN: 978-0-7356-6607-8).

Some of the keypoints to the exam is:

  1. Understand the replication architecture (page 200)
  2. Understand the replication types (snapshot, transactional, peer-to-peer, Merge). Page 302 and forward
  3. Understand the replication monitor (page 315)

I also think it is important to understand what an agent is (at least the snapshot and log reader agent) and that all agents just are implemented by using SQL Server Agent jobs.

So.. Here is the video that hopefully helps you understanding replication

 

[youtube https://www.youtube.com/watch?v=VNRvjh0HXPA]

Bookreview – 70-462 Administering Microsoft SQL Server 2012 Databases

This is the book for Microsoft SQL 2012 exam 70-462, and compared to the books for Microsoft sql server 2008 and earlier, then I think that Microsoft has done a great job on this one. The book is divided into 12 chapters, and the first one is the traditional “How do we install” and what versions has what functionalities, how can I upgrade from one SQL server to another and so on. When it comes to Examination it is good to know, but In real life, I think it of no use.

Chapter 2+3 is about configuration, First about the instance configuration and then how to configure components. I would wish that more dba’s would read these chapters, because there are a lot of small tricks that could improve SQL Server installations. E.g. is there a small chapter about fill-factor and a lot more about e.g. filegroups/files and recovery models. Really good to know, eventhough you can google a lot of things these days.

Chapter 4 is about migrationg, importing and export, and what I would focus on, if I should learn from this chapter, would be all about the bulk operations, and I think that I would try to get more knowledge if I could find the time. It is really interesting.

Chapter 5+6 is about security, and eventhough security is not as funny as e.g. performancetuning, then these chapters are a very good startingpoint to understand security in a SQL Server. In 2012 we also have the possibility to have user-defined serveroles. That is really a new and great thing. I could also recommend to read about auditing in chapter 6.

Chapter 7+8 is about high availability and replication. Please note that I think it is wrong to call replication a high availability feature. I don’t think it is, but anyway, AlwaysOn (a new term in Sql 2012) is introduced.

Chpater 9+10 is about performance, troubleshooting and concurrency, and you should definately read these two chapters. For me it is the most important thing when i comes to SQL Servers.

Chapter 11 is about the SQL Server Agent, backups and restores, and I have seen so many wrong configured Backups, that it is a wonder that I only know one situation where a customer has lost data. READ IT!!!!!!! UNDESTAND IT!!!!! PRACTICE IT!!!

Chapter 12 is code studies, and before taking the exam, you should really dig into this.

So, the 100 million dollar question: Can you pass the exam if you understand this book? NO! I think you can use it as a very very good beginning (better than earlier versions), but you must also practice and also use the tests that are on the CD that follows the Book.

If I should rate this book, then I would give it 3 out of 5 stars.

I want to pass 70-461 – Querying Microsoft SQL Server 2012

Hi,

I just want to share some of the thoughts that I have about how you in the most easy way can Pass exam. 70-461.

Basically, the easiest way is to learn SQL, but it is my opinion that you should have been using TSQL and the SQL server for at least 3-5 years, if you want to be sure to pass the exam.

The Exam is split into the following topics.

  1. Create Database Objects (24%)
  2. Work with Data (27%)
  3. Modify Data (24%)
  4. Troubleshoot & Optimize (25%)

At present (on 2012-10-22) there is no trainingmaterial from Microsoft, so you must learn a lot of stuff your self. That stuff includes (but is not 100% perfect):

  1. What is the syntax to
    1. Create/drop/alter a table
    2. Create/alter/drop  a filetable
    3. Create /alter/drop a view
    4. Create an indexed view.
    5. What types of functions are there.
    6. How to extract data to XML format (What is the difference between e.g. FOR XML AUTO, RAW, PATH)
    7. How do you insert, update, delete records.
    8. Understand different types of constraint (Unique, Foreign key e.g.).
    9. How SELECT works, and how to use Joins (at least inner, Outer and Cross joins), APPLY.
    10. How to Insert, Update and Delete data.
    11. How MERGE is working.
  2. Dynamic management views you must underStand:
    1. Views to tell you about missing indexes.
    2. Views to tell you about index usage, including physical info.
  3. Other things that you might need to know:
    1. How collation works.
    2. New features in SQL 2012 in relation to querying.
    3. General understanding of different types of objects (e.g. views, tables, Stored Procedures, Functions) and when to use what.
    4. Data types (especially the new ones).
    5. How Transactions works (also what the difference between implicit and explict transactions are).
    6. How Executionplans works
    7. Table hints
    8. Query hints

And there are much more. I would recommend also to read the studyplan at microsoft.

So how would you prepare. Well, I would start downloading the Adventureworks databases from CODEPLEX.COM.
When they are installed (at least just choose one of them, then I would do the following:

  1. Search Books online to read about the topics.
  2. Open SQL Server Management Studio (or other tools if they are required) and then start to code.
  3. If any doubts then try searching www.google.com (but be carefull. there is a lot of crap out there).

When you have learned a new topic, then you can move on to the next topic. However!!!!! What about the level of knowledge?

Well, that is always the question. Often Microsoft has this “Free shot” campaign, and if that is the situation (or if you have to much money), then try to take the exam. If you pass then you are fantastic. If you fail, you are still fantastic, but then you have learned something about the level of knowledge.

AND!!!!! At some point www.measureup.com and www.selftestsoftware.com will have some preparation questions ready, and Microsoft will send out the training material, and then you can use that for your preparations.

Good Luck (and please call me if you need any specific advises).

Søren

 

1) Print out a checklist (that could e.g. be mine, eventhough there are more topics to be covered)

 

70-433 – 000 – Introduction

Welcome to this series, where I will try to cover some of the most interesting and problematic topics in relation to this Microsoft Exam. The exam is a exam at the technology specialist exam, but to be honest, then it is an exam the divide people into two groups, where a lot of people tend to find the exam easy, and other people basically never pass.

With the proper preparation, then it should be quite easy to pass, but it requires a lot of hours of hard work.

this introduction is split into three parts:

1) Prerequisites

2) What is required for this exam

3) supplementary reading / material

4) Video material

A lot of the material for this course can used to pass the exam for both SQL server 2008R2 and SQL Server 2008.

PREREQUISITES

To be able to follow this blog, then you should have downloaded the adventureworks from codeplex.

then you should buy the book MCTS Self pased training exam 70-433. This is the core book for the exam, but let me be 100% honest. It will not make sure that you pass the exam.

WHAT IS REQUIRED FOR THIS EXAM

You can read Microsoft Official studyplan by clicking here, but you don’t get any idea about the level of what you are expected to learn. The level is quite technical, but not impossible.

To understand this exam, then we must start understanding why we have an SQL-server, and basically we use it to store data and to retrieve them again. We store information about a customer and we retrieve it again. It sounds simple, and it is. at some point then some guys figured out that we could store things in tables (Imagine a EXCEL-sheet) and that is the basic structure of a microsoft SQL Server.

Later on then someone figured out that it would be nice to store things in multiple tables and to normalize a database. The relational database was born.

Since that, the world has become a much more complex place to live, and that also goes for the Microsoft SQL server. Now we have XML-data, we want to be able to search like you do on Bing or google, and we want to do it faster and more effient.

During this course we will see how the relational database is created and how techniques to fullfill the requirements of today are used.

The book covers 9 chapters, and they are:

Chapter Description
1 – Data Retrieval This is were we begin, and this is were we start to understand how to retrieve data from a relational database. the most important words are:

  1. Select – From – Where – Order by
  2. Inner joins – Outer Joins – Cross Joins – Self Join
  3. Aggregation (Avg, Sum, count and so on) – Group by – Rollup – cube – grouping set – Having
  4. Union, Union All, Except, Intersect, Apply
  5. Built-in Scalar Functions
2 – Modifying data If you only know how to select, then it will be impossible to get data into the database, so you also need to know how to manipulate data. the most important words/techniques are:

  1. Insert, Update, Delete, Truncate Table
  2. Output clause, Merge
  3. Transactions, Locking, Transaction Isolation Levels
3 – Tables, data types, Integrity Now you can retrieve data, you can insert,update and delete data, but what if you want to create your own database, then you must be able to create databases and tables. In this module we look at:

  1. Datatypes. what datatypes are there, and why is it important to know the difference.
  2. How to create tables
  3. Integrity (Primary key, unique, Foreign key, check, default)
4 – Additional query techniques  Basically you now know everything to be a databasedeveloper, but if you want to be a good one, then you should continue with this chapter and the following once:

  1. CTE (common Table Expressions), Recursion
  2. Subqueries, running aggregates, Correlated and noncorrelated subqueries
  3. Rank functions (Row_number, rank, Dense_rank, NTILE)
5 – Stored Procedures, functions, triggers, views Not all SQL Statements are run on an Ad-Hoc basis, and the SQL Server is also build to use different techniques to perform better and do different tasks, and therefore this chapter is essential:

  1. Stored procedures, variables, parameters, return codes, Control Flow, Error messages, Error handling, execution context, compilation and recompilation.
  2. Types of User-defined functions (scalar, multi-line table function, inline table functin)
  3. DML-Triggers (only the triggers that are fired after insert, update and delete)
  4. DDL-triggers (only the triggers that are fired after a create, alter or drop)
  5. Logon-triggers
  6. Views
6 – Query Performance
  1. Understanding query costs
  2. Understanding pages
  3. Execution order
  4. Execution plans
  5. How to determine where to put in a lot of time 🙂
  6. Indexes (clustered, nonclustered, covering, filtered indexes)
  7. Tools to automatical index optimization
  8. Indexed views
  9. Partitioning
7 – XML, SQLCLR, Filestream
  1. Understanding CLR (Common Language Runtime).
  2. Understanding XML
  3. Transform  relational data to XML
  4. transform XML to relational data
  5. Query XML data
  6. Manage XML data
  7. Understanding Filestream
8 – Spatials, Full-Text Search, service broker In this chapter you learn how to work with

  1. Spatials (geography, geometry)
  2. Use the fulltext indexing
  3. Use Service Broker
9 – Manageability features This is a fluffy chapter, but there are four important things to learn:

  1. Databasemail
  2. Power shell
  3. Track data changes
  4. SQL Server Management Objects

This is the topics we will cover during the modules that I will upload over the next 30 days.

Course at SQL Skills in Seattle – Day 3-4

2 more days of the course has gone, and this will be a very short post, because, it is tough.. very tough. Long days and then work/practise after the course.

But anyway it is wort all the time.

Wednesday was about partitioning and you would think that partitioning is just something enterprisebusiness are using. You are wrong.

Partitioning can be done in a lot of different ways, and it can improve dramatically.

BUT the best part until now is about the statistics. I have never earlier thought about how statistics works in a microsoft SQL Server 2008, but now I know in debt.

I will post my last blog tomorrow and then I will head back home to Denmark.

Søren