The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I ran across a post this week from Joe Chang on server sizing. It's short and talks about some of the issues that you might consider when looking at a single socket v multi-socket systems. What first caught my eye, however, was the opening sentence: "Standardizing on 2 and 4 sockets systems for servers has been an established practice going back to 1996".

I think that's been my experience overall. For most of my early career, we often did purchase 2 or 4 socket systems. A few times I had 8 sockets, but hardware costs and licensing were high. When VMs became the preferred method of building servers, we tended to just ask for 2 or 4 vCPUs. If the system ran slow, we just doubled the vCPUs.

Time has moved one and hardware has advanced at incredible rates. These days licensing by core has really changed the way that I look at hardware. I couldn't tell you how many sockets servers have, as I'd likely just ask for a VM with x CPUs (and lots of RAM) allocated. Whether the system was 1, 2, 4, 8, or more sockets wouldn't be a consideration. In fact, I've somewhat given up on trying to track which CPUs have what cores and what the best choice is. I take the simplistic view of a core is a core and the hardware geeks will figure out how to get those into my VM.

I wonder how many of you actually worry about the hardware in your system beyond gross layout? Do you dig into tracking which CPUs need to be inside a physical box, the type of RAM layout, the drives (beyond size and count)? Or are you like me. Your system is JBOC (just a bunch of cores), JBOR (just a bunch of RAM), and JBOD. If things run slow, after fixing code, I usually JWM (just want more of something).

Certainly there is a need for someone to pay attention to hardware details, perhaps if for no other reason than to ensure that price/performance is being considered and there are spare parts available. For me, as a data professional, I've tended to just look at the performance needed from a system and ask for that. Even in my last position as a DBA, my concern was the SQL Server process and how it worked, limiting my hardware concerns to knowing the CPU count, RAM size, and number of disks. I've abstracted away hardware for the most part, focusing on a higher level of the system.

For those times when I do care about hardware, such as when laptop shopping, a simple query of a few colleagues or a tweet nets me enough information to make a choice. After all, that's what friends are for.

Steve Jones from SQLServerCentral.com

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

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

T-SQL

SQL101: Avoiding Mistakes on a Production Database Server - As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there ......(more)

Injecting JSON objects into existing JSON text with JSON_MODIFY function - JSON_MODIFY function that is available in SQL Server 2016+ enables you to modify existing text formatted as JSON and add or update existing values. One common problem that happens is inserting unexpected value while trying to update existing JSON text ......(more)

The Lighter Side

How to set up a 10" Touchscreen LCD for Raspberry Pi - I'm a big fan of the SunFounder tech kits (https://www.sunfounder.com), and my kids and I have built several Raspberry Pi projects with their module/sensor kits. This holiday vacation we have two project we're doing, that coincidentally use SunFounder ......(more)

A Budget Home Theater & PC Setup: 4K, HDR, UHD Blu-ray, and More - The days of bulky home-theater PCs (HTPCs) with built-in tuners, optical disc drives, and integrated media storage capabilities are long gone. In 2017, advancements in the media / home theater space (including the rise in popularity of OTT streaming ......(more)

Tech News

How do you change the most important law in Internet history? Carefully - (credit: robby-T) Congress has spent much of the past year grappling with a heartbreakingly difficult issue: victims who are prohibited by Section 230 of the Communications Decency Act from suing the websites where they were sex-trafficked. Unfortunately, ......(more)

Library of Congress will no longer save every tweet - Enlarge (credit: Michael Nagle / Bloomberg / Getty Images News) Yesterday, the US Library of Congress announced a change to its social media archiving policy. In 2010, the LOC had been given a complete archive of every tweet sent to that point; it has ......(more)

SQL Server Security and Auditing

Audit Domain Group and User Permissions - No matter how simple the task or how versed we are with doing a security audit, it seems like we can always stand to learn just a little bit more. No matter how many times we hand an audit report over to the auditor, there is always “just one” more report ......(more)

SQL Server Security

Extended Events Permissions - A common question that I am asked about Extended Events relates to what kind of permissions are required in order to use this awesome feature. In this article, I am going to compare and contrast the permissions required for Profiler as well as Extended ......(more)

What is impersonation for? - I was doing some research on impersonation the other day, and among other things, I ran across a forum question Use of IMPERSONATE permissions in SQL Server? I started reading the answer, and it seemed like a pretty good answer, well laid out, decent ......(more)

SQL Server News

SQL Server 2014 SP2 CU9 - On December 18, 2017, Microsoft released SQL Server 2014 SP2 CU9, which is Build 12.05563.0. By my count, this CU has seven public hotfixes, nearly all of which are for the SQL Engine of SQL performance. Since SQL Server 2014 SP1 and earlier are no ......(more)

Security news and thoughts

How Can Companies Balance Between Too Much Control and Too Much Access to Data? - Click to learn more about video blogger Andrew Brust. The Big Data & Brews video blog series continues with host Andrew Brust, Senior Director of Market Strategy and Intelligence at Datameer. The series touches on hot topics within the business of Big ......(more)

Fixing Data Breaches Part 5: Penalties - Presently sponsored by: Netsparker - dead accurate web application security scanning solution - Scan websites for SQL Injection, XSS & other vulnerabilitiesIn the first 4 parts of "Fixing Data Breaches", I highlighted education, data ownership and minimisation, ......(more)

Acoustical Attacks against Hard Drives - Interesting destructive attack: "Acoustic Denial of Service Attacks on HDDs": Abstract: Among storage components, hard disk drives (HDDs) have become the most commonly-used type of non-volatile storage due to their recent technological advances, including, ......(more)

Post-Quantum Algorithms - NIST has organized a competition for public-key algorithms secure against a quantum computer. It recently published all of its Round 1 submissions. (Details of the NIST efforts are here. A timeline for the new algorithms is here.) ...(more)

PowerShell

Different methods to write PowerShell output to a SQL table - PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Sankey Chart: a case to review your Budget books - (2017-Dec-29) Sankey diagram (chart), named after Matthew H Riall Sankey, is a good visualization tool for information flow within a system. It can also identify main players (contributors) to different stages of this flow, where those stages can be ......(more)

Design Concepts For Better Power BI Reports Part 4: The Squint Test - Data visualization should be iterative. You should get a good initial draft put together and then check to make sure it meets your success criteria. Then check the design to ensure it it effectively conveys information in a manner that is easy for your ......(more)

Power BI Custom Visuals Class (Module 83 – ChartAccent – LineChart) - In this module you will learn how to use the ChartAccent LineChart Custom Visual. This visual is a custom line chart that allows you to annotate individual data points, data series and ranges. Module 83 – ChartAccent – LineChart Downloads Power BI ......(more)

Checklist for Finalizing a Data Model in Power BI Desktop - My colleague and friend Meagan Longoria has been giving me a few “gentle nudges” to update an old post about this topic:    This post is a checklist of suggested items to be completed in your Power BI data model, within Power BI Desktop, before you ......(more)

Performance Tuning SQL Server

Do Disabled Indexes Affect Missing Index Recommendations? - I’m so glad you asked! Let’s take a look. Open up the Stack Overflow database, turn on actual execution plans, and run a query that will cause SQL Server to beg and plead for an index:SELECT * FROM dbo.Users WHERE DisplayName = 'Hovercraft Full Of Eels'; And ......(more)

Getting perfmon data into SQL Server, Part II - This week we will retake my last’s post topic to finish that job off and get in and out our Perfmon data to and from our SQL Server. This week we will retake my last’s post topic and finished that job off and get our Perfmon in and out data of our SQL ......(more)

Hardware News

Intel Outs Unannounced 1TB BGA SSD, 64L 512Gb NAND Die - Intel joins Samsung and Toshiba with a 1TB BGA NVMe SSD that will come to market "next year." ...(more)

HA/DR/Always On/Clustering

SQL SERVER – Msg 32055 – sp_refresh_log_shipping_monitor – There Was an Error Configuring the Remote Monitor Server - Since I started consulting, I never had “Monday Blues” because I don’t get any weekends. I am always ready to help without time boundaries. Consulting gives me exposure to real customer issue, learn more and blog more. This also help me to see what customers ......(more)

ETL/SSIS/ELT

Upgrading SSIS projects, part I - In the previous post, I wrote about migrating SSISDB database. When we migrate the database the packages still have the version of the source SSIS catalog. When you start the execution of the migrated package, you get the information like “The package was migrated from version 6 to version 8. It must be saved to retain migration changes.”...(more)

DevOps and Continuous Delivery (CI/CD)

DevOps for Data Science – Automated Testing - I have a series of posts on DevOps for Data Science where I am covering a set of concepts for a DevOps “Maturity Model” – a list of things you can do, in order, that will set you on the path for implementing DevOps in Data Science. In this article, I'll ......(more)

Data Warehousing

Is the traditional data warehouse dead? - There have been a number of enhancements to Hadoop recently when it comes to fast interactive querying with such products as Hive LLAP and Spark SQL which are being used over slower interactive querying options such as Tez/Yarn and batch processing options ......(more)

Conferences and Events

Precon at SQLKonferenz 2018 - One of my favourite conferences (and so far I was incredibly lucky to have spoken at every edition) is SQLKonferenz, which is a middle-range sized conference, located in Darmstadt, Germany. Sized at 400-500 people, it is not the biggest conference in ......(more)

DBA Fundamentals at SQLSaturday Cincinnati - I will be teaching my all day “DBA Fundamentals” pre-con at SQL Saturday Cincinnati on March 16, 2018.   There is a $125 fee for this class, which includes lunch.  Possibly a cool DBA t-shirt as well… This class is targeted at pretty much everyone that ......(more)

Career Growth

Female DBAs Make Less Money. Why? - The 2018 Data Professional Salary Survey is open now, and you can look at the live responses as they come in. (To get that in Excel, click File, Download.) As of this writing (Dec 23), there’s already over 2,500 responses, so I’m going to take a look ......(more)

My Learning Goals for 2018 - A couple of weeks ago, the SQL Server community had their last T-SQL Tuesday of the year – 97th since 2009 – hosted by Mala (b|t). T-SQL Tuesday, a brainchild of Adam Machanic (b|t), is a monthly blog party in which … Continue reading ? The post My Learning ......(more)

Backup and Recovery

Restoring a Copy Only Backup–#SQLNewBlogger - There was a question posted recently at SQLServerCentral about whether a copy only backup could be restore with a transaction log backup from a database. I was positive this could, but decided I needed to repro and test for someone as there wasn’t a ......(more)

Azure SQL Database

Azure SQL Versus SQL Server In An Azure VM - This article will compare and contrast the two options for deploying SQL Server in Microsoft Azure, Azure SQL (SQL-as-a-service) and SQL Server running in a virtual machine.     SQL Server in a Virtual Machine In my experience, when customers (developers ......(more)

AI/Machine Learning/Cognitive Services

Uncovering hidden patterns through machine learning - Lessons from FizzBuzz for Apache MXNet.When data scientist Joel Grus wrote an article on using machine learning to solve the "fizzbuzz" problem last year, most people saw it as an exercise in comedy, perhaps with a warning about the inappropriate use ......(more)

Administration of SQL Server

SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use - People generally ask me; how do I manage to find a blog topic for every day blogging? And my answer is – “Connect yourself to the ground, try to help people with the basics, have open eyes & ears and promise yourself to learn one new thing every day”. ...(more)

What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE? - You read a lot of advice that says you shouldn’t shrink databases, but…why? To demonstrate, I’m going to: Create a database Create a ~1GB table with 5M rows in it Put a clustered index on it, and check its fragmentation Look at my database’s empty space Shrink ......(more)

SQL SERVER – Linked Server Error: TCP Provider: No Connection Could be Made Because the Target Machine Actively Refused It - While trying to create linked server, sometimes there are errors. One of the error is below which is related to TCP Provider. The linked server has been created but failed a connection test. Do you want to keep the linked server? TCP Provider: No ......(more)

Rethink Server Sizing - Standardizing on 2 and 4 sockets systems for servers has been an established practice going back to 1996. There were very good reasons for this, but it was so long ago that they have been forgotten. Yet the practice continues unquestioned, almost as ......(more)

How to troubleshoot database mail? - If after creating a profile and account you send a test email and it doesn’t work try this: Check the database mail log if it is empty: “SELECT * FROM msdb.dbo.sysmail_event_log;” Check mail status is started: “EXEC msdb.dbo.sysmail_help_status_sp;”  Check mail ......(more)

SQL SERVER – Error: 17300 – SQL Server Was Unable to Run a New System Task - One of my clients was performing load tests on timesheet application. They noticed that application jobs are failing with below error message about running a new system task. TimesheetSaveFailed (24014) – A severe error occurred on the current command. ...(more)

Best New(ish) SSMS Feature - We all probably use SQL Server Management Studio (SSMS) on a very frequent basis. Just for giggles, lets just say that means we use it at least once a day. I think it is safe to say, we have all been wanting to see something cool released for SSMS for ......(more)

SQL Server Automatic Tuning and sys.dm_db_tuning_recommendations - In Azure SQL Database for quite some time and now available in SQL Server 2017, Microsoft has put a lot of the knowledge they’ve gleaned from running more databases that any of the rest of us ever will to work with Automatic Tuning. Automatic Tuning The ......(more)

Message in a Bottle of XE - One of the age old features that most probably take for granted in SQL Server happens to be the error messages. What? How can a message be a feature, right? Well, I concede. Maybe calling it a feature may be too much. Then again, consider it for just ......(more)