My Review of Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012

Originally submitted at O'Reilly

Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012

Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012

Good resource for 70-463 Exam

By Jaype from Kenosha, WI on 2/10/2013

 

5out of 5

Pros: Well-written, Accurate, Helpful examples, Easy to understand

Best Uses: Intermediate, Certification Resource, Student

Describe Yourself: Sql Database Administrato

I've passed the 70-448 exam, BI for SQL 2008. The 70-463 exam concentrates mostly on the SSIS part of the Microsoft BI stack. This MS Press Training Kit covers all of the enhancements made to SSIS in 2012. It also covers the design of a data warehouse. There's a lot here.

The authors did a good job in matching the books sections to the exam objectives. I thought the examples were clear and easy to follow. The practice exam, included on the CD, will come in handy when I'm closer to exam time.

I think this book will be enough for me to pass the 70-463 exam. But if you have no SSIS background you may want to also use some other resources.

(legalese)

Moving to new home

I'm moving SQL Servings to a new site, at http://www.pertell.com/sqlservings. See you there.

SQL and Relational Theory Master Class by C.J. Date

I recently had a chance to review C.J. Date's video, Relational Theory Master Class. This is an excellent resource and I highly recommend it for anyone who wishes to better understand the theory behind relational database design. But be prepared; this is not a beginner’s class. You should have a basic understanding of SQL and database design before viewing this video. And while the content is expensive at $299 I feel it’s worth it. It’s still cheaper than attending a similar seminar in person, and you can review it whenever you like.

The video is a companion to Date’s book, SQL and Relational Theory, but it’s not required to read or purchase the book too. There’s 16 hours of video content, and I feel the class does a better job of describing relational theory. One of the features of the video I like is that it’s not static; that is, the camera focus is not always on the speaker or a slide deck. Moving between the speaker, the audience, and the content keeps the viewer’s interest. And you can hear the questions asked; it’s almost like you’re attending the class in person.    

The content is excellent. It’s not centered on any specific platform. The methods you learn here help you to better understand why your table joins work the way they do, regardless if your database is Oracle or SQL Server. Once you better understand relation theory your code will make more sense. 

You can find SQL and Relational Theory Master Class at O'Reilly Books

What I learned today: No backup or restore with the DAC

For everything I teach, I learn something in return.

I began to train a new junior level DBA today. He’s someone that works with us already as a developer but he’s shown an interest in learning how to be a DBA so I’m beginning to teach him what I know. To start off we began by discussing his daily tasks, then we started to talk about backup and recovery, topics I gave him as an assignment last week. For this week’s homework I told him to read up on the Dedicated Administrator Connection (DAC) and to get familiar with SQLCMD. I mentioned how the DAC was useful in situations where the server was under a strain and you need a way to connect to troubleshoot the problem. Then I said you can use it for backups and recoveries.

I should have quit when I was ahead.

As my new colleague correctly pointed out, you can’t use the DAC for backups or recovery. If you try, you’ll get the following error:

Msg 3637, Level 16, State 3, Line 1
A parallel operation cannot be started from a DAC connection.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

And BOL can’t make it any clearer:

SQL Server prohibits running parallel queries or commands with the DAC. For example, error 3637 is generated if you execute either of the following statements with the DAC:

• RESTORE
• BACKUP

(Ref: http://technet.microsoft.com/en-us/library/ms189595.aspx)

For some reason I’d forgotten that. It was something that I had read a long time ago and forgot since I don’t use the DAC very often, never in production other than to confirm that it was set up through the firewall. I had assumed that backing up a database in an emergency would be one of the reasons I’d use the DAC. Luckily I was reminded before a situation arose where I relied on it.

So I actually learned two things today. I was reminded about the restriction of the DAC, and I learned to be more careful about what I tell others when it comes to SQL, not to assume facts and to do my own homework. I hope I remember both lessons equally. 

 

 

 

Using the IGNORE_DUP_KEY index option

This is a quick demo I put together for a talk on indexes earlier. It shows the behavior of the IGNORE_DUP_KEY option when you create a unique index.
First, create a small sample table. I used tempdb but feel free to use whatever test database you want. Then create a unique clustered index on the field1 column.

CREATE TABLE Table1
(
    field1 int        NOT NULL,
    field2 char(2)    NOT NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX cl_Table_1 ON Table1 
(
    field1 ASC
)
GO

Next insert a few records into the table. I’m inserting records in two separate blocks to trap the intentional duplicate key error.

BEGIN TRY
    INSERT INTO Table1 VALUES(1, 'aa')
    INSERT INTO Table1 VALUES(2, 'ab')
    INSERT INTO Table1 VALUES(3, 'bb')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

BEGIN TRY
    INSERT INTO Table1 VALUES(4, 'cc')
    INSERT INTO Table1 VALUES(3, 'ac')
    INSERT INTO Table1 VALUES(5, 'dd')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

SELECT field1, field2 FROM Table1

You won’t get any errors in the first block because all three values are unique as defined by the key. The second block will generate the duplicate key error when it tries to insert the value 3 into field1. At this point the error is trapped and value 5 doesn’t get inserted. Viewing the values shows the 4 records.

field1	fiels2
1	aa
2	ab
3	bb
4	cc

Now lets alter the index to ignore duplicate key errors. When you use this option any error is ignored and execution of the batch continues. Only the duplicate record is not inserted. We’ll insert three more records and again trap any errors.

ALTER INDEX cl_Table_1 ON Table1 SET ( IGNORE_DUP_KEY  = ON );
GO

BEGIN TRY
    INSERT INTO Table1 VALUES(6, 'zz')
    INSERT INTO Table1 VALUES(2, 'dd')
    INSERT INTO Table1 VALUES(7, 'cc')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

This time no error is generated. The duplicate value of 2 is ignored and execution continues, meaning that the next record of 7 is also inserted. Viewing the values shows this.

field1	field2
1	aa
2	ab
3	bb
4	cc
6	zz
7	cc

Does your company owe you training?

Recently I read an interesting post by Steve Jones (Blog | Twitter) on SQL Central about Personal Investments, how you invest in your career. It’s also something I’ve been thinking about lately. I agree with Steve that I’m responsible for my own education. But I feel that companies should be, to some extent, as well.

I fall into the “accidental dba” category. When a senior member of our team left I was assigned to work on maintaining and monitoring our databases, something no-one really did before that. It fell to me because I had the most knowledge about SQL Server, as opposed to just writing T-SQL statements. I learned what I could by going to all the free events I could find. I joined my local SQL User Group and attended as many meetings as possible. I bought and read many excellent SQL books and blogs. I took any free or cheap training resource that was available. Eventually I passed a number of Microsoft certification exams. I’m proud of what I’ve achieved on my own, and I will continue to further my technical education.

There’s a cost to my training, even if it’s free. There’s the hours of attending conferences and viewing webcasts. There’s the price of purchasing books, and the time it takes to read them. There’s even travel related costs, such as attending a SQL Saturday event in another state. I pay the cost gladly, and I’ve already seen the benefits.

But at what point should a company start providing training?

Now I don’t believe your company should always pay for your education. As Steve said in his post, you should make your own investment in your career, showing your managers the value of your training. You’re the one responsible for keeping your skills up to date. But it should be a partnership. Your company also needs to make an investment. They have as much to gain as you do.

This isn’t just to motivate an employee, giving you a nice job perk. Frankly, if you’re not self-motivated to get the basic skills needed to do your job maybe you should start looking for another position. But if your job suddenly requires specialized knowledge they should help you attain that knowledge.          

Let’s say, for instance, that your company has decided to use clustering for high availability. And since you’re the SQL ‘expert” they’ve come to you to set it up. You’ve read up on clustering, and you know in theory how it works. But you’ve never worked in a clustering environment, let alone design the architecture involved. Shouldn’t your company help you learn what you need to know to succeed? After all, they have more at stake in this case than you do.

I just feel that companies should also contribute to your technical education. I’m interested in how other’s feel about it.         

What I learned today – CHECKDB and database snapshots

A few days ago I was sitting in my local coffee shop, getting a little light reading done. At work we’re about to move from the Standard Edition of SQL 2005 to the Enterprise Edition and I wanted to learn more about some features of database snapshots. Snapshots are basically just a read-only copy of a database taken at a specific time. Anyway I was reading Chapter 3 – Database and Database Files in the excellent book Microsoft SQL Server 2008 Internals. The discussion turned to space allocated for the snapshots. That’s when I read that DBCC CHECKDB actually runs against a snapshot of the database.

That caught me by surprise. Until then I thought that the CHECKDB ran against the database itself.

Now I thought I was doing OK with protecting our databases. I faithfully run CHECKDB weekly against all databases and check for any errors. I set the CHECKSUM option and verify backups. I occasionally restore databases on a standby server to make sure there’s no problems with the backups. And I monitor database and drive space pretty closely.

But I was flying blind. I have no idea how near I came to having CHECKDB fail because of a lack of space. So I started digging a little deeper into CHECKDB. And I was able to learn a little bit more of how CHECKDB works with snapshots.

For anything concerning CHECKDB, your first resource should be Paul Randal (Blog | Twitter). Read his excellent series on CHECKDB From Every Angle to get a good understanding of what’s going on. And he also wrote Chapter 11 – DBCC Internals in Microsoft SQL Server 2008 Internals, a great book you should read anyway.   

CHECKDB needs a consistent view of the database to work with. In SQL 7 it acquired table locks. In SQL 2000 it analyzed the transaction log. Beginning with SQL 2005 and going forward it creates a hidden snapshot on the same volume as the database – you have no control over where it’s placed. If you’re running CHECKDB at the same time that your server has a heavy workload the snapshot can run out of space and you’ll get an error showing that CHECKDB didn’t complete. If that happens you have a few options;

• Create your own database snapshot on a drive that has enough space and run CHECKDB against that snapshot. CHECKDB will know that its running against a snapshot and won’t create another one.
• Start the SQL service in single user mode.
• Put the database in read-only mode. 

Once CHECKDB has finished it will delete the snapshot if it created one.

But there are a few gotcha’s with CHECKDB creating the snapshot. First, it has to be created on an NTFS drive. Second, you can’t use the WITH TABLOCK hint. And third, you can’t run against tempdb; but why would you want to?.  

I’m sure I’m over simplifying. I still need to finish Chapter 11.

One other thing I’m wondering. If CHECKDB can create a snapshot database in SQL 2005 regardless of the edition, can the Standard Edition create one with a hidden flag?