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.

Professional SQL Server 2008 Internals and Troubleshooting

It is very easy to give a description of this book. It is a must, if you want to work with SQL Server as an administrator. Of course there are things that are not really necessary to know, but in general the book describes quite well, some of the mainissues when it comes to performanceproblems in a SQL Server: Memory and Locks.

Besides that there is a good deal of information about the storage engine and how it works, and we also look at the CPU.

On the downside, I think the book uses to much time talking about other approaches like SQL Nexus, and therefore it places it self between a good technical book and a book for administrators that does not want to be to technical.

It can be bought at amazon.com 

 

 

Pro SQL Server 2008 Mirroring

This book review is about the book “Pro SQL Server 2008 Mirroring“, and it is a nice little book that can be bought on Amazon or other online Book stores. It is 237 pages, and it is an expert book in Database mirroring on Microsoft SQL Server platform.

I recommend that you buy this book if you have setup a couple of installations (mirroring setups) and now you want to learn more about how mirroring works. There are 10 chapters, and each chapter cover an important topic when it comes to mirroring and as all other books, the first chapters are a general introduction, including the most common words that you need to learn.

One chapter I like in particular is chapter 3, that is called Planning and Best Practices. This is not a technical chapter. this is a chapter to understand that everything in a SQL server is not about using wizards and clicking Next- Next and Next. This chapter introduce you to the fact that Planning is everything!!!

It also have a list of Best Practices, and this is a nice list, BUT this is also an example of one of the downsides of the book. The layout is not very good. Why do you make a list that is difficult to read and prioritize, when you could create a checklist in prioritized order? I  would have done that.

Chapter 10, Planning for Failure, is also very interesting to read, and it sums up why you should use databasemirroring. You should use it because you are a good DBA, that plans for failure in a cost efficient way.

So… conclusion is, that if you want a good book on Database Mirroring, and you have some experience with that area, then the book is absolute worth buying. I think the price is around 50 USD.