A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Big Data Analytics

How large is your analytics system? Do you have more than one machine for analytics? Do you have a cluster of machines that run Hadoop in a YARN cluster to analyze your data? Are there hundreds, or even thousands, of nodes that are being used regularly? Some of you might have what you consider to be a large system, but I bet it isn't as large as Microsoft's cluster.

They think they have the biggest YARN cluster, with over 50,000 nodes in a single cluster. This is used to process multiple exabytes of data from their various properties and systems. I certainly haven't heard of a system this large, and I really wonder what this costs to run. After all, I'd think a 50,000 node cluster has to be a significant cost, though perhaps in the grand scheme of Microsoft's $100 billion in revenue and $38 billion in expenses, even 100,000 machines can't really impact their numbers.

The cluster has essentially been running a private version of Azure Data Lake for years that their internal developers and analysts use to access a common pool of data. In fact, because of their scale needs and the desire to limit the copying of data between clusters, they have contributed back to the Apache Yarn project a number of fixes to help ensure the software can scale to tens of thousands of nodes. There is some discussion of how they've allowed YARN to grow to larger scales, and it's an interesting solution that essentially allows some overbooking of resources, knowing there are always some spare cycles available for processing data. It's a great test site for Azure Data Lake, and something that more of us might use in the future.

I doubt may of us would need to work on data sets that large, and I know I certainly wouldn't want to be responsible for that much of a data lake, I do think these are interesting problem domains that someone should look at. Certainly there are always large organizations and governments that have ever growing pools of data that will likely end up in a data lake of some sort. And who knows, perhaps, the definition of large will continue to grow to the point where 1,000 nodes in a cluster is considered "small", and it's what many of our businesses might implement in the future.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Database DevOps Demo Webinar

Learn how to automate your database deployments alongside your app code in this free demo webinar. Register now

Featured Contents

Generate QR Code® barcodes in an SSRS report with the QRCoder library

Stan Kulp from SQLServerCentral.com

QRCoder is an open source implementation of standards specification ISO/IEC 18004, which defines the requirements for two-dimensional QR Code symbols. It is a more complete implementation of the standard than the open source QRCode4CS library that was used in a previous article. More »

How to Linux for SQL Server DBAs — Part 2

Additional Articles from SimpleTalk

SQL Server DBAs are typically quite familiar with security and best practices in Windows but may not understand how security works in Linux. In this article, Kellyn Pot'Vin-Gorman demonstrates how to manage user accounts in Linux and why working while logged in as root is a bad idea. More »

Merging a Conflict with SQL Source Control and Beyond Compare

This article describes a simple example of what it’s like to merge a conflict in SQL Source Control. You will need to have installed Beyond Compare, one of the tools which integrates with SQL Source Control to resolve conflicts. More »

From the SQLServerCentral Blogs - Database Fundamentals #19: JOINS

Grant Fritchey from SQLServerCentral Blogs

The last Database Fundamentals post introduced the SELECT and FROM commands. We’re going to start using JOIN operations shortly, but... More »

From the SQLServerCentral Blogs - The Security Weak Link

Steve Jones from SQLServerCentral Blogs

I saw this cartoon, which I think is great: Data Security. It’s from John Klossner, and it perfectly shows that... More »

Question of the Day

Today's Question (by Steve Jones):

I need to create a schema for my developers to do some work under. I want to ensure the schema is owned by the dbo, in case I move to new projects. What command do I add to this script to replace the XXXX?

 CREATE SCHEMA Developers XXXX dbo

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Security.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Expert Scripting and Automation for SQL Server DBAs

Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have a database of baseball statistics. In the entire history of batting, I want to find out what percentage of players have hit 40 or more home runs in a season. I'm using this query, but I keep getting a 0 back.

 SELECT COUNT( CASE WHEN hr >= 40 THEN 1 END ) / COUNT(*) * 100 FROM dbo.Batting AS b ;

What should I do?

Answer: CASE one of the COUNT() statements to a decimal.

Explanation:

The problem with my query is that I am doing integer math. That returns an integer result. While I can change the 1 to a 1.0, the COUNT() function still returns an integer. If I change the 100, that helps, but the division from two COUNT() statements will return an integer, which in this case is a zero.

If I cast one of the COUNT() statements to a decimal, then the division will be floating point math.

Ref: Division - click here

If you're interested, from 1871 to 2017, 0.32% of batters have hit 40 or more home runs in a season.

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Development

A check constraint referencing another column - The first block of code is from another website. Here the CHECK constraint is supposed to be applied to the MedicamentRegulated column...

EBCDIC file is getting loaded with all special characters in my table - Hi, I have an EBCDIC file and I am trying to load it using SSIS. I created a flat file connection manager,...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

How to store output of SQL Server Stored Procedure in a .txt file - I have below SP I am printing the value of the variable in S.S.M.S. Instead, I want to store this...

SQL Server 2012 : SQL 2012 - General

???Ryerson?????|Ryerson???|Q?1010636782|Ryerson?????????????RU?????Ryerson University - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???OTTAWA?????|OTTAWA???|Q?1010636782|OTTAWA?????????????UO?????UNIVERSITY OF OTTAWA - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???Brock?????|Brock???|Q?1010636782|Brock?????????????BU?????Brock University - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???McMaster?????|McMaster???|Q?1010636782|McMaster???????????????MU?????McMaster University - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???Queen’s?????|Queen’s???|Q?1010636782|Queen’s????????????QU?????Queen’s University - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???Windsor?????|UW???|Q?1010636782|Windsor????????????UW?????University of Windsor - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???Guelph?????| Guelph???|Q?1010636782| Guelph?????????????UG?????University of Guelph - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???York?????|York???|Q?1010636782|York????????????YU?????York University - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???Carleton?????|Carleton???|Q?1010636782|Carleton?????????????CU?????Carleton University - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???Waterloo?????|Waterloo???|Q?1010636782|Waterloo?????????????UW?????University of Waterloo - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???UWO?????|UWO???|Q?1010636782|UWO??????????????UWO?????University of Western Ontario - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

???Toronto?????|Toronto???|Q?1010636782|Toronto?????????????U of T?????University of Toronto - ??????Q/?1010636782???????:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ?????????Q Q\??1010636782???:???,??????(????)??????+«?????&????»,???????,??????,?????,Offer,????,???? ,?????? ????????????????????????????????????????????,??,DHL?? F????:???.??.??.???.??.??.??. F???????????????????????,????,????,??????????????,????????????!??? #??

SQL Server 2012 : SQL Server 2012 - T-SQL

Find tables with records in multible DB's - Hi all! On my production SQL server, I have + 300 databases, whereof app. 50 has the same structure, and has a...

Yikes! SQL Master at Work. - I'm going through some scripts that my coworker wrote, for a good laugh. The HR guy calls him the "SQL...

Anyone see a problem with this? - I asked a developer to write a simple query against this table of mine. He looked at me and said...

HOW TO GET FIRST DATE - Dear all, I have data like: NAME SPONSOR_NAME DATE_OF_CALL A FI

SQL Server 2008 : SQL Server 2008 - General

dbms query cost merge-sort - Can anyone please explain the red marker line in above. I find it difficult to understand. This is given in my...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.