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]

MS SQL 2012 – Offset and fetch

(You must have the database adventureworks2012 installed. Download it from codeplex.com)

This is one of the new cool features of Microsoft SQL Server 2012, and if you are a developer, then you will love.

Earlier when you wanted to create e.g. a website where you wanted to have a grid where you wanted paging, then you had to rely on either the .net components or complex SQL if you wanted to show e.g. 50 rows pr. page and your table had e.g. 2000 rows.

With the new Offset and fetch, you can create readable SQL and easy to understand SQL (even for all the .net guys out there).

So lets see a simple example. You want to retrieve 50 rows from the Person table in the Person Scheme. You want to order by lastname, firstname and middlename and you want to retrieve row 51 to 100.

   1: select BusinessEntityID, coalesce(Firstname+' '+MiddleName+' '+LastName, Firstname+' '+LastName,FirstName) as name

   2: from person.person

   3: order by LastName, Firstname, middlename

   4: offset 50 rows fetch next 50 rows only;

Line 4 does the trick for you, and basically you say:

 

  1. I want you to start at offset 50 (0 i also a offset, so offset 50 will be record 51).
  2. You want to fecth the next 50 rows.

If you want to read the full documentation, then go to books online and read it there.

But what about performance? Well, if we look at two important things, then we can look at the execution plan and the page reads. The executionplan looks like this

image

 

As you can see, then we do an index scan, Compute Scalar (that is of no interest here, because that has just something to do with the Coalesce that I uise) and a Top.

From the executionplan I can see that the cost is 0,0037976.

If we look at the pages read, then it looks like this

image

 

As you can see, then there are 1 scan and 3 logical reads.

What if we did it the old fashion way? Then the code would look something like this (an example):

   1: with yourCTE as

   2: (

   3:     select row_number() over (order by LastName, Firstname, middlename) as rowNumber

   4:             , BusinessEntityID, coalesce(Firstname+' '+MiddleName+' '+LastName, Firstname+' '+LastName,FirstName) as name

   5:     from Person.Person

   6: )

   7: select *

   8: from yourCTE

   9: where Rownumber between 51 and 100

  10: order by rownumber;

 

As you can see, then we uses a common table expression, and the code is much more complex. For a SQL Guy, it is easy to read, but why make it difficult, when we can make it simple? Let’s look at the executionplan for this query:

image

It’s much more complex, and the cost is a little bit higher: 0,0038956.

Reads are the same.

Conclusion: I would recommend you to use the new offset and fetch instead of common table expressions; it’s easier and in many cases performance would be better. If performance is the key issue, then make sure that you look into all alternatives AND that you measure instead of guessing. In some situations it might be better to load the complete dataset into the application and then do the filtering within the application code. It all depends.

Practical Troubleshooting The database Engine

This book is an oldie, but a goodie, and I finally managed to get through all the pages. I hate my self for not having done that for many years ago.

 

This book is a technical book, and it covers a lot of serious aspects related to troubleshooting; from DBCC to Memory preassure; from IO problems to CPU problems.

 

The best thing is that it mentions a lot of Dynamic management views that are still very relevant.

 

So. If you a skilled SQL person, and you want to learn more about Troubleshooting, then this is a must have book.

Buy it on Amazone.com.