How many lines of TSQL codes and Window Functions you need to come up with this?

With R, it takes about 2 commands:

> order <- as.matrix(sqlFetch(ch,"SalesDetails")
> summary(order)

To give you an idea, I prepared the data set into a view like this:

CREATE VIEW SalesDetails
AS
SELECT
 soh.OrderDate AS [Date],
 soh.SalesOrderID,
 ppc.Name AS Category,
 pps.Name AS Subcat,
 pp.Name as Product,
 SUM(sd.OrderQty) AS Qty,
 SUM(sd.LineTotal) AS LineTotal
FROM Sales.SalesPerson sp
 INNER JOIN Sales.SalesOrderHeader AS soh
 ON sp.BusinessEntityID = soh.SalesPersonID
 INNER JOIN Sales.SalesOrderDetail AS sd
 ON sd.SalesOrderID = soh.SalesOrderID
 INNER JOIN Production.Product AS pp
 ON sd.ProductID = pp.ProductID
 INNER JOIN Production.ProductSubcategory AS pps
 ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
 INNER JOIN Production.ProductCategory AS ppc
 ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderID, ppc.name, pps.Name, pp.Name,
 soh.SalesPersonID

And, that’s it. I just fed that view to the R engine and it summarized the dataset just by using those two commands.

This is not the perfect dataset to test with R.

I’d love to dig deeper into R because of its powerful analytical features.

What is R? “R is a language and environment for statistical computing and graphics.” If you’re into statistics, linear/non-linear modelling, or simply want to try another tool to analyze your data warehouse, give R a shot.

If you want to start digging with R, Ted Malone ( b | t ) has a nice introduction to using R with SQL Server 2012. Get more information about R on the R Project website.

I’ve been looking for better ways of learning and improving my SQL Server skills. Blogs, BOL, webcasts, and training videos are great learning aids. Having the right tools and method makes a lot of difference. Building a SQL Server VM Playground to put the lessons in practice is a vital part of the learning process.

In The 4-Hour Chef,Timothy Ferriss suggests a recipe for learning any skill, which is pretty much applicable to learning SQL Server:

Deconstruction – What are the minimal learnable units, the LEGO blocks, I should be starting with?

Selection – Which 20% of the blocks should I focus on for 80% or more of the outcome I want?

Sequencing - In what order should I learn the blocks?

Stakes – How do I set up stakes to create real consequences and guarantee I follow the program?

“Where do I start?” is always the first question we ask when we want to learn something new. And the answer to that is the fundamentals – the building blocks.

The same is true with SQL Server. You want to learn how to be great at Performance Tuning? Learn the Database Structure and Index Internals. You want to master Disaster Recovery? Learn the Data Storage and Backup Internals. It’s always going back to the basics.

With a huge system such as SQL Server, learning just the basics alone can still be overwhelming. You need to deconstruct SQL Server into “minimal learnable units”. You have to select and line up what you want to learn in a sequence that aligns to your learning goals.

Sometimes you just know what you want and how to get it. You want to become better at skill x, and you know that getting there would take y and z.

But you can raise the bar one notch.

You can study and learn on your own but wouldn’t it be great if someone can give you guidance and direction – someone that will not only show you the ropes but will also help you find the most effective way of achieving your desired outcome.

Get a mentor who’ll bring out the best in you.

I am currently under the mentorship of Edwin Sarmiento (b | t). He’s doing a great deal of work in helping the SQL Sever Community around the world (check his MVP Profile). This is my first experience having a mentor in learning and building my SQL Server skills. I should have done this sooner.

Wikipedia defines Mentorship this way:

“Mentorship is a personal developmental relationship in which a more experienced or more knowledgeable person helps to guide a less experienced or less knowledgeable person. However, true mentoring is more than just answering occasional questions or providing ad hoc help. It is about an ongoing relationship of learning, dialog, and challenge.”

There are three things to take away from that definition:

Learning – Since your mentor is someone who has “been there, done that”, he exactly knows how to deconstruct the challenges you’re facing based on your strength and weaknesses. A relationship between you and your mentor must therefore be established prior to your mentoring sessions.

Dialog – A line of communication should always be open between you. A good mentor is someone who provides an open channel through which he makes himself accessible at any reasonable time. A constant dialog must be in place to communicate what’s needed on both side.

Challenge – We become better by overcoming challenges. A great mentor provides challenges that are tailored to test our aptitude. Not only that, challenges keep the process interesting. On the mentor’s side, the real challenge for him is not only to help us get closer to our goals but also to make us see our Purpose.

Do you have a mentor?

There are things that I’m thankful for this year. Yes, I also got a lot of setbacks but that wouldn’t make me less thankful.

I’m thankful for my family as always. No words can ever describe how thankful I am for the gift of family.

I’m also thankful for my #SQLFamily. Thank you for the gift of friendship. Thank you all for sharing your knowledge. But, mostly, thanks for the friendship.

And now, the book.

I have signed a contract with a publisher to write a book on SQL Server 2012 (dev side). I’ll provide more details as they become available.

That number at the title of this post? 978-1-84968-508-5 – yes, that’s the ISBN.

Thank you for the opportunity to write a book.

No related posts.

Developers will love this latest improvement, specially those that have SQL Server Express installed on their laptops or workstations but still work with full versions of SQL Server on their network.

In the past you have to have at least the Developer Version of SQL Server if you also wanted to have the complete features of SQL Server Management Studio.

Thanks to SQL Server 2012 SP1:

SSMS Complete in Express

More info on What’s New in SQL Server 2012 SP1 here

No related posts.

I’m nowhere near where I needed to be. I am not digging enough. I’m not learning enough. Learning opportunities are not landing on my lap.

I have to go out on my own and learn SQL Server on my own time and resources.

This blog was never meant to become an outlet of my frustrations about not getting to that next level. I’m supposed to be focusing on acquiring real-world skills, and not whining about why I am not hitting my career target.

Today, there is no more next level.

I need to just stop looking for that next level and just focus on learning and acquiring new skills.

I’ve been learning – by reading blogs, watching videos, and subscribing to hardcore online training. But I don’t have the kind of job that would allow me to practice.

I remember what Sean McCown (Blog | @MidnightDBA) said in the DBA Roadmap seminar: “If you want to become a SQL Server DBA, there’s no excuse for not knowing stuff about SQL Server (not the exact words he said, but it’s something to that effect).”

I am renewing my commitment to learning more about SQL Server. No more excuses. No more drama.

Just action.

The Tool: Lenovo ThinkPad W530

SQL Server needs some horsepower to run, some juices, and some muscles. The ThinkPad W530 fits the bill. Glenn Berry (Blog | @GlennAlanBerry) lists the W530 as one of the mobile workstations fit for testing SQL Server 2012.

The Intel Core i7-3720QM Ivy Bridge processor in the W530 allows me to practice most of SQL Server features that demand some computing power. I will dig into Clustering, Replication, and all other High Availability features.

Of course, one of the primary reasons why I decided to shell out some money on a good machine is SQL Server 2012. I can now build SQL Server 2012 AlwaysOn Availability Groups on my own lab!

The 16GB DDR3 RAM that I have in this machine is the amount of memory that will allow me to build multiple Virtual Machines. I am looking at having 5-7 Virtual Machines to form my personal SQL Server Sandbox. More on this in the upcoming posts.

I have more wishlist for my ThinkPad W530 – SSD’s on the Primary Bay and Ultrabay, Maximizing the 32GB RAM capacity, and a better Wireless Network Card. I just don’t have the extra dollars to make those upgrade now.

Focus More On Hardcore Training

I have had Pluralsight for a month now. I was on slow pace in the first month. Having this ThinkPad W530 on my hand builds momentum. I should now take advantage of this momentum and continue to fuel my passion for SQL Server.

I should now switch to doing-mode – practicing more and more on my skills.

Laying The Foundation

I’m changing my mindset from now on. I should totally forget about “getting to the next level.” If an opportunity comes my way, I’ll be glad to take it. But that is not my focus now.

I should start laying the foundation of my career as a SQL Server Professional. That sounds like I’m starting from square one. If that’s what it takes, then Yes. I’m starting all over. And I am making sure that I have the right, strong foundation this time.

In the real world, that translates to acquiring the Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 Certification. The exam guides for 70-461, 70-462, and 70-463 are great study guides. So I will be using them as outline for my personal training.

Learning continues.

Related posts:

1. Getting to the next level in your SQL Server career

Jen McCown (Blog | Twitter) wrote about an interesting topic on the different levels of database professionals. In her article, Jen identified the three levels as Junior, Mid-Level, and High-Level.

I’m not repeating here her descriptions of the different levels, so go and read her article (link above) and, hopefully, you come back here to read the rest of this post.

There is a clear distinction as to who the “Junior” database professional is. This level is generally a stage where one focuses on exploration – just knowing what is there to know.

What’s a Mid-Level? High-Level? Can we use the number of “years of experience” to distinguish one from the other? What’s the gauge? How to measure?

It depends.

It depends on the level of knowledge, on “what you know” vs “what you should know” at a particular level. For example, if you are a Mid-Level SQL Server DBA, you should know X, Y, and Z.

I think everyone agrees on that. What everyone does not agree on are the specific types of knowledge one is supposed to posses at a particular level.

The comments on Jen’s article have their own merits. Everyone has a point.

But what the article and the comments all boil down to is one’s willingness to learn.

If you want to move to the next level you should study – you should learn to learn.

That is the most important lesson in that story.

Learning Is Reading

Technical books are great resources if you want to learn. You don’t have to read a lot. You only need to read just enough. Just enough.

Blogs are great resources too.

Learning Is Watching

Watch training videos and webcasts. Make this a habit – one video a week, depending on the level and quality of content, is a good way to start.

Or, better yet, subscribe to an online technical training. A year-long subscription is better choice compared to, say, ones with shorter term. Spend at least 30 minutes a day on this.

Learning Is Doing

Reading and watching is not just enough. You have to practice. Want to learn how to install SQL Server 2012? Read. Watch. But you cannot really learn how to install it until you actually install it.

Learning Is Connecting

Learning is a communal act. No one learns alone. Yes, you can “study” on your own by reading a book but you have to ask questions. You have to verify facts.

The community is there to answer your questions. You have to go out and reach out. The SQL Server Community has a great #SQLFamily. Be part of that.

Related posts:

1. Are you ready?
2. The evolving role of the database professional
3. On the road less traveled: a DBA Roadmap review

This month’s T-SQL Tuesday is hosted by Aaron Nelson (@SQLvariant | Blog). The topic is about logging. Many will talk about their logging strategies with their SQL Server infrastructure. Many will show us ways they do their day-to-day logging. And many will offer tips and tricks to expert logging.

According to Aaron, we’re not constrained to talking about logging within just SQL Server. I am not the right person to talk about SQL Server logging; so, that rule encouraged me to participate in this month’s T-SQL Tuesday (my first).

I am tempted to write about all of the logging stuff I am doing in my personal life. If we take “logging” to mean making lists, tracking processes, and trailing work flows, my list will be in infinite loop.

Let me share how I track, or log, the blogs I want to read, or have read but want to keep for reference, in my quest to gain and improve my SQL Server DBA skills. I know I am far down the scale and reading is one of the best tools I can afford to catch up.

Chrome Bookmark

I have a main folder called “SQL Server” to hold all blog links related to SQL Server. These links are categorized into different topics with each topic placed in their own sub-folder. My folder structure looks something like this:

SQL Server
> SQL Fundamentals
> SQL Dev
>> SSRS
>> SSIS
> Backup & Recovery
> High Availability
>> Replication
>> Mirroring
>> Log Shipping
>> Failover Clustering
> Security
> Performance Tuning
> Monitoring
> Troubleshooting
> SQL Tutorials
> SQL 2012
>> AlwaysOn Availability Group
> SQL Azure
> SQL Tools

It’s not a perfect system but it works for what I need.

Twitter Favorites

I also track the interesting links I found on Twitter by marking them as Favorite. If the link is something that I can use now or in the future, it goes to my Chrome Bookmark. More often than not, I “favorite” something to just read it later

Old-school Notebook

I am on a mission. I am on a quest for that elusive SQL Server DBA job I have been longing to have. Aside from blog reading, I purchased a handful of SQL Server 2008/2012 paperbacks and I have a list of books I still need to buy! I have some SQL Server books on my Kindle, too. For me taking notes is better than highlighting sentences. Yes, I agree that searching through pages in physical notebooks is difficult but I am comfortable with that. Hey, I’m a Moleskine fan!

Hacker News

I have become a Hacker News (HN) fan only last year. It’s my source of daily geek news. Where do you think I’m finding those interesting (well, some of them are) links I am posting on my Twitter stream? Want to know latest movement in the tech world? Yes, go to HN. My upvoted links are stored in my “saved stories” and all the links I submitted are in my, well, “submission”.

I promise that I will post about real SQL Server logging next time.

No related posts.

I have talked about wanting a change. I thought I am prepared to embrace that change when it finally comes.

I have waited for the right opportunity. And, yes, I finally met the right opportunity.

I wish this is a success story.

If there is one blog post I remember most from Thomas LaRock (@SQLRockstar | Blog), it’s his post about timing and opportunity. In “Timing Is Everything“, Thomas said:

Timing is everything, no matter what you are doing. You need to be able to prepare yourself so that at the time the opportunity presents itself you are ready. If you do, some people will look at you and say “wow, how did you get so lucky?” Others, like myself, will say “wow, how can I prepare myself to be ready when the time comes?”

How one prepares himself for an opportunity?

No excuses

The inadequacy of your present job is not an excuse for not knowing the things you aspire for in a job. There is just no excuse for your lack of knowledge. If you think you’ve worked hard enough, work even harder.

Dig in

If you are like me, one of the biggest challenge of learning advance SQL Server knowledge is the lack of an environment that supports your development. If I can’t find help from within, I must find some other means to learn the things I need to learn.

Knowing and not knowing

It’s not enough that you are aware of what you don’t know. You must act to find the answers. It might be ok to say “I don’t know” to a question in an interview but communicate how willing you are to work to know the answer.

Learn scenarios

It’s not enough that you know the fundamental and the technical know-how’s. Focus your learning effort on scenarios – on solving problems. Equip yourself with knowledge that will enable you to navigate through providing solutions in different situation.

Identify the heart of the job

SQL Server is a management tool. It’s like a hammer to a carpenter. Unlike a hammer, SQL Server has different features that address specific needs. Majority of those needs have something to do with monitoring, performance tuning, and troubleshooting – the need for servers to be running all the time. You cannot possibly learn all the features of SQL Server overnight. But, if you are like me who’s still struggling to find that one ideal SQL Server DBA job, you’d want to focus on monitoring, performance tuning, and troubleshooting.

Know the trinity of a SQL Server DBA job

I just said it. I am not repeating this for you. I am repeating this for myself. Three things will get you through a SQL Server DBA job interview:

• Monitoring - Knowledge beyond SQL Server Error Log and Agent Error Log. Act of omissions on the SQL Server level won’t show as errors (obviously). How to monitor things that are not readily visible in the eye? Proactive Monitoring! Identify the tools and features available to you.
• Performance Tuning – Perfmon, DTA, DMV’s, and SP_Who2 are too common for an answer. Describe scenarios and identify the tools that you might use depending on the situation.
• Troubleshooting – There are different points of failure you need to get yourself familiarized. These are some of them: User Access, Application Access, Security, Replication errors, diagnostics , etc. Basically you’re looking at a scenario which is something like “This stops running, how to fix it?”.

Yes, indeed, timing is everything. Finding that ideal SQL Server job is not about waiting for the right opportunity to knock at your door.

Yes, I failed that interview but the failure of not getting the job did not cripple me. It might have discouraged me a bit but it only strengthened me as a person.

That failed interview equipped me with a failure that does not weaken. It showed me the beacon of where I should be heading. It gave me directions to follow.

The failure does not matter now. I learned. That’s all that matters.

When opportunity knocks at your door, how ready are you to answer the call?

No related posts.

It’s one thing to be able to backup a database, it’s another thing to be able to restore it into an operational database in case of failure. We can run a simple TSQL to check the validity of a backup file. We can do something like this:

 RESTORE VERIFYONLY FROM DISK = 'C:\TestDB.bak'

It’s easy, right? You don’t have to have some ninja skills to run that statement. Run the verification, and if SQL Server validates the backup, your job here is done. Yes to that, only if you can rely on VERIFYONLY.

Valid doesn’t mean you can restore it

The fact is you can’t rely on VERIFYONLY for real backup verification. It doesn’t guarantee that you can RESTORE the backup although it might report your database backup as VALID. If it reports validity, it’s only guaranteeing that it can READ the backup file, but not necessarily for restoration. “Validity” in this case is a test for whether the backup file is readable or not.

Let’s run an example.

For this example let’s borrow Paul Randal’s corrupt database backup. Let’s pick “Sample 2008 R2 Fatal Corruption Databases”. And that is because I am running a SQL Server 2008 R2 on my laptop.

Let’s verify the CorruptDemoFatalCorruption1.bak backup file and see if SQL Server validates it:

RESTORE VERIFYONLY FROM DISK = 'C:\BackupTest\CorruptDemoFatalCorruption1.bak'

This is what SQL Server found:

(For the sake of this example, let’s just ignore the fact that you should be monitoring the health of your databases.)

The best you could do to make sure that you can restore your backup is to actually restore it in a test server. I’m sure you’re going to have some fun doing that on hundred’s of databases.

Third-party backup and restore tool

This is where third-party backup and restore tools can easily help. Let’s take a look at the latest version of Red Gate’s SQL Backup Pro (v.7).

Since we’re talking about validity of a backup for restoration, let’s take a look particularly at one of the new features of SQL Backup Pro v.7 - Automated Backup Verification and Restore. I’ll provide a couple of links at the end of this post if you want to check the demo of the new capabilities of the application. Let’s zoom in to the verification of a backup.

Let’s restore the backup CorruptDemoFatalCorruption1.bak. To check for the data and log files needed for restoration, just do RESTORE FILELISTONLY first.

RESTORE FILELISTONLY
FROM DISK = 'C:\BackupTest\CorruptDemoFatalCorruption1.bak'

Then restore:

RESTORE DATABASE CorruptDemoFatalCorruption1
FROM DISK = 'C:\BackupTest\CorruptDemoFatalCorruption1.bak'
WITH MOVE 'DemoFatalCorruption1'
       TO 'C:\BackupTest\RestoreTest\Corruption1.mdf',
MOVE 'DemoFatalCorruption1_log'
       TO 'C:\BackupTest\RestoreTest\Corruption1.ldf'

We should get a message similar to this:

Let’s cut to the chase and go straight to the verification process. Like I said, I’ll provide links at the end of this post if you want to check all the features of SQL Backup Pro v7.

For this demonstration, I’ve already created a backup of CorruptDemoFatalCorruption1 in advance using SQL Backup Pro. Suppose we’re not aware of the health  status of the database (we know that it’s a corrupt database).

Now that we have the backup, the next thing we want to do is verify that backup and make sure that we can restore it. We can do the verification by creating a Schedule Restore Job and running it. At step 4 of 7 of the process, we specify verification options for the restored database as shown below:

When run, SQL Backup Pro v7 provides the Outcome status of the restore. In our case, we know that our database restore will fail, and SQL Backup Pro accurately tells us that:

Of course, you can check where the Database Consistency Check failed:

Grant Fritchey (Blog | @GFritchey) have produced the following resources to demo the new features of SQL Backup Pro v7:

SQL Backup Pro for the Accidental DBA (article)

Introduction To SQL Backup Pro v7 (video)

No related posts.

The story of why I came up with this blog is no secret to the few readers I have here. In fact, I have written about it in my inaugural post. Although I wrapped it in rather obscure way, you probably get what I meant by the words I’ve written.

I’ve gone into overdrive, absorbing any amount of information I can process into my brain. I am really working hard for my ultimate career goals. I seek wisdom from anywhere and any source – human or otherwise.

Our usual step when seeking for advice is to search the internet for relevant topics. There is no shortage of what we can get if we know where to find them.

The internet has a vast, rich body of information that is readily available for our instant gratification.

But I feel that the goals I want to achieve are too important for me to rely on the John and Jane Doe’s that dominate my Google search.

(Of course, I’m making exception for the likes of Brent Ozar, Thomas LaRock, Brad McGehee, etc. who never fail to offer great pieces of advice on becoming an exceptional DBA.)

I am not saying that I cannot put these free information to good use – I can, in more ways than one.

There’s more ‘why’ to that.

I feel that if I invest some of my hard earned money to my goals I can probably get more value than just by consuming free information on my spare time.

I need to invest in myself. I need a concrete way to get my body and soul commit to the journey!

Thus, I paid to get the DBA Roadmap. A thing like this is the least I can do for my career. Some are spending out of pocket upwards of $5,000 dollars each year towards their career (training, conferences, etc).

The DBA Roadmap Seminar is a business venture by the SQL Server couple Sean and Jen McCown (@MidnightDBA), or popularly known as the Midnight DBA’s. The recorded seminar costs $99.00 as of this writing.

I’m not sure if it’s just me, but I don’t like the seminar’s banner question, “How do I become a DBA with no experience?“

That can easily put off anyone who has experience with SQL Server (including myself). That’s the reason why I hesitated to purchase the seminar package on the onset.

But I agree that the primary target audience of the seminar is individuals who hope to break through the DBAhood. Sean, himself, was a French cook before he became a DBA. The banner question makes the seminar appear to be “exclusive” for this type of audience.

I am not a newbie to SQL Server. I’ve been an accidental DBA for an information and data management company before. I’ve done some gigs on SQL Server development. And I’ve migrated a big financial reporting system from DTS 2000 to SSIS 2008.

The seminar has 7 tracks, including the Introduction and Conclusion. Except for those two, you can listen to any tracks in any order you want (not in sequence).

The main tracks are: How To Study, What To Study, Resumes, Job Hunting, and Interviews. Each track comes with a bonus Companion Guide that gives you a breakdown of what has been discussed on the track. The guide also provides additional links to useful sites and resources. The tracks are described on the DBA Roadmap site.

You’re probably thinking if these tracks can actually offer you new things. It’s true that these topics are often discussed. But do you think the McCown’s will ask you for money just to rehearse things you already know? Yes, I know, right? There was hesitation at first on my part because of the way the seminar was being marketed (newbie’s seminar). But I found out it’s exactly what I needed.

Although a newbie to SQL Server can reap the most benefit from this seminar, I don’t see why a current SQL Server pro can’t learn and pick up resume and interview skills from it.

There’s a reason why I titled this post “the road less traveled”. And you’ll find out why if and when you purchase the seminar yourself.

For 99 bucks, the seminar is a steal. Listening to 5 hours of solid, meaningful material is like attending a whole day of seminar. Plus, you can play the tracks over and over again, as I have, if you cannot digest the content on one pass.

If you are a SQL Server DBA with 2-3 years worth of experience or even a mid-level SQL pro looking to advance his career and thinking that he could use an advice or two, you owe it to yourself to purchase this seminar.

No related posts.