The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I'm not a bit BI (Business Intelligence) user. I think the promise is great, and I was excited when SQL Server gained an OLAP component in SQL Server 7. There have been many changes over the years, but overall the investment in Analysis Services (SSAS) has tailed off in recent versions. In fact, there has been speculation that Microsoft will abandon SSAS in the future and remove it from the SQL Server platform. The few changes in 2014, more, but still nothing major in 2016, has had users worried about continuing to build applications (and their skills) on SSAS.

I'm confident that's not the case now. This past week Microsoft announced Azure Analysis Services at the PASS Summit. This is the evolution of SSAS on the SQL Server platform, with the ability to now move your tabular models into Azure and run them on an as needed basis. This means that you don't need to administer your own SSAS instance, and can connect to cloud and on-premise data sources for your data analysis needs. Since SQL Server has moved its codebase to primary development in Azure and a periodic release on-premise, this is a good sign that Analysis Services will continue to receive investment in the future.

There are restrictions. No multidimensional models, for one, and a few others. If you are interested in trying out the SSAS in Azure, make sure you understand that limitations. And since this is in preview and has been announced, keep up to date on the changes in the platform. As developers build in new features, I'd expect this platform to evolve and change at least quarterly, with new features and fewer restrictions over time.

I know others are excited, as I've already seen a few people blogging this week about their initial experiences. As with other services in Azure, however, I both like and dislike some things. It's great that the platform evolves and changes quickly, but I'd like to know which release of Azure Analysis Services I'm using. If for no other reason than I'd like some idea of when something changes. Not every evolution is helpful, and some will break systems, so knowing there has been a change or a new release can dramatically speed up troubleshooting. If the version of the Azure system changes, then I know to potentially look at Azure release notes rather than my own code. If it doesn't, then problems are more likely my own fault.

It hasn't seemed that SSAS has grown as much in use or popularity than I had expected 17 years ago, and I suspected Microsoft might give up, but that isn't the case. For now, we see that SSAS is alive and well, moving into the cloud and receiving development resources. Perhaps that will change, but I hope not. There are problem domains solved well by SSAS and the ability to use the technology as an on-demand platform, without adding additional administrative and hardware resources, might help grow adoption a bit. I still think the core understanding of SSAS and MDX escapes most people, but we have many more tools that can read data from SSAS instances and help users query data. That means we need fewer people to design and maintain SSAS instances, and we can easily create and destroy them as needed on Azure.

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.

Vendors/3rd Party Products

Redgate DLM Demo Webinar - Watch the recording of our latest DLM Demo webinar and learn how to improve your database change management process. See how Redgate DLM plugs into Git, Jenkins & Octopus Deploy so that you can automate your database changes alongside your application code....(more)

Tech News : Conferences and Events

SQL Cruise as a Speaker and Sponsor - Attending SQL Cruise is undoubtedly a unique experience - but what is it like as speaker or as a sponsor? As a seasoned SQL Cruise veteran, Grant Fritchey talks through the benefits. ...(more)

Tech News : Disaster Recovery

Hurricane Matthew May Cost Businesses $10B - For a storm that inflicted less damage than many had feared, Hurricane Matthew nevertheless impaired or destroyed more than 1 million structures, forced businesses from Florida to North Carolina to close and put thousands temporarily out of work. In ......(more)

Tech News : General Interest

PASS Summit Keynote 2 – Release 2 - Dr. David DeWitt is getting ready to take the stage to a huge ovation and is thrilled to be back at PASS Summit. Haha, bring back #BringBackRimma is a twitter tag that he has put in place….looking forward to hearing Dr. David DeWitt speak today….. Why ......(more)

PASS Summit Keynote 2 – Release 1 - Grant Fritchey taking the stage to talk about the financial health of PASS. 250k members , 170 countries, and 87% global representation – outstanding. 73% return into the community; focused on being a one global community 119 SQL Saturday Events held ......(more)

PASS Keynote Part 2 – Live Coverage - Good morning from PASS Summit in Seattle, Washington. Like yesterday I will be live blogging the event. The same format of the blog will apply with up to 5 releases every 15 minutes. Yesterday was a great first keynote, and from what it appears attendees ......(more)

PASS Summit Live Keynote – Release 5 - Julie Koesmarno, Senior Project Manager at Microsoft, takes the stage to talk about business analytics utilizing War and Peace. Tracing emotions of each key characters via a heat map…..pretty cool to see application of SQL Light language. Cognitive capabilities ......(more)

PASS Summit Live Keynote – Release 4 - Document DB: Blazing fast planet-scale NoSQL: Guaranteed Low Latency Elastically Scaling Storage Elastically Scaling Throughput No Impedance Mismatch Choice of Consistency Enterprise Level SLA Azure + DocumentDB with leading game technology. A game ......(more)

PASS Summit Live Keynote – Release 3 - Justin Silver, Scientist Pros, takes the stage…… Taking data science out of the lab settings and placing it into real world scenarios. Azure has tremendously been able to allow enhanced functionality into the realm of data science bridging the gap 100x ......(more)

PASS Summit Live Keynote -Release 2 - Rohan Kumar, General Manager of Microsoft, takes the stage hyping up SQL Server 2016. We keep hearing the word “Sharing” among databases. Intelligent Database: In-Memory Analytics In-Memory OLTP Wow, 100X faster analytics + 30x faster transactions Azure ......(more)

PASS Summit Live Keynote – Release 1 - Joseph Sirosh taking the stage by first telling us a story with over 400 million children in India. Only 50% of them attend school regularly. Consider the loss of potential that could have been a doctor. Millions more like these children; a world of ......(more)

SQL Server News

Announcing SQL Server Management Studio -16.5 Release - Today, we are pleased to announce the latest generally-available (GA) quality release of SQL Server Management Studio (SSMS) 16.5. This update features fixes for high DPI, reconnection and many issues! Check out the list of fixes below. Get it here: Download ......(more)

Top 5 Announcements at PASS Summit 2016 - This post is by Joseph Sirosh, Corporate Vice President of the Data Group at Microsoft. In June, we’ve announced the general availability of SQL Server 2016, the world’s fastest and most price-performant intelligence database for HTAP (Hybrid Transactional ......(more)

ODBC Driver 13.0 for SQL Server – Linux is now released - This post is authored by Meet Bhagdev. We are delighted to share the full release of the Microsoft ODBC Driver 13 for Linux – (Ubuntu, RedHat and SUSE). The new driver enables access to SQL Server, Azure SQL Database and Azure SQL DW from any C/C++ ......(more)

Upgrade with Data Migration Assistant 2.0 and Database Experimentation Assistant Preview - This week as part of Joseph Sirosh’s PASS Summit 2016 keynote, Microsoft announced general availability of Data Migration Assistant v2.0 and the first public preview for Database Experimentation Assistant. Both tools are part of Microsoft’s strategy ......(more)

Product Upgrades and Releases

Data Compare on Temporal Tables - I hadn't seen much talk on doing data comparisons on temporal tables, as they are a new feature. I went through the exercise to compare current to historical to see how Red Gate & Devart handled this. I'm a part of the Friends of Red Gate program, so ......(more)

Move Petabytes to Microsoft Azure Without Re-Architecting Applications - SoftNAS® today announced the newest release of SoftNAS Cloud® that utilizes the wide spectrum of data stores available from the Microsoft Azure cloud platform ranging from high performance SSD-based to Azure Cool Blob storage. Azure customers using SoftNAS ......(more)

Product Reviews

Using TRY..CATCH in SQL Prompt - I learned something new recently about SQL Prompt. I was working on documenting and experimenting with snippets and found a quicker way to write code. One of the things I think is great is to use the TRY..CATCH structure. I don’t do it enough, but I ......(more)

Press Releases

5 eBooks on Data Science and Analysis! - Below you can find 5 eBooks related to the Data Science and Data Analysis. The download can be done after buying the eBooks or under the "Free Minimum" formula. R Programming for Data Science This book brings the fundamentals of R programming to you, ......(more)

Microsoft News : General Interest

Microsoft's Surface Studio: The newest member of Microsoft's device family - Microsoft's Surface Studio is an all-in-one device with a hinge that lets the display itself serve as a desktop....(more)

Internet of Things

IoT Hack: Why Aren't We Holding Manufacturers Responsible? - Last Friday's daylong cascade of cyber attacks highlighted an issue that until now has largely been a discussion point on security-specific blogs and forums: The internet, and thus much of our modern way of life, is in a precarious state. DDoS attacks ......(more)

Hardware News

15 hours with the 13” MacBook Pro, and how Apple’s T1 bridges ARM and Intel - Andrew Cunningham Here’s the good thing: I walked away from Apple’s campus yesterday with a new 13-inch MacBook Pro. Here’s the less good thing: it’s not the one with the Touch Bar, the version that Apple actually spent most of its time talking about ......(more)

SSDs Kill The 15K HDD, Seagate Rolls Out Last Generation - The venerable 15K HDD is singing its swan song as Seagate introduces its sixth-generation Enterprise Performance 15K HDD v6 (otherwise known as the 15K.6), which will be the last mission-critical 15K HDD the company produces. ...(more)

Western Digital My Book 8TB and My Passport 4TB External HDDs Review - Western Digital has a number of direct-attached storage (DAS) products based on hard drives. They cater to different market segments. These DAS units usually employ a USB port to connect to the host (Thunderbolt is also popular in the high-end market). ...(more)

The Samsung 960 Pro (2TB) SSD Review - A year ago, Samsung brought their PCIe SSD technology to the retail market in the form of the Samsung 950 Pro, an NVMe M.2 SSD with Samsung's 3D V-NAND flash memory. The 950 Pro didn't appear out of nowhere—Samsung had shipped two generations of M.2 ......(more)

The Cooler Master Master Keys Pro L White Mechanical Keyboard Review - Cooler Master is a very familiar brand name amongst enthusiasts, with a virtually endless lineup of products ranging from PC cases and coolers to peripherals and accessories. In this review we are having a look at one of Cooler Master’s latest and most ......(more)

Samsung Introduces 8 GB LPDDR4-4266 Package for Mobile Devices - Samsung this week announced its first LPDDR4 memory chips made using its 10nm-class DRAM fabrication technology. The new DRAM ICs feature the industry’s highest density of 16 Gb, are rated to run at 4266 MT/s data rate, and open the door to more mobile ......(more)

Blogs : Administration

SQL SERVER – Configuring Startup Options for SQL Server Management Studio - Yesterday I started my day early to get to a gym. I know this was the first day and I had loads of work pending to be completed at work too. This got me busy and I started to drive my car, oblivious of the fact that I had to take a turn which I missed. ...(more)

SQL SERVER – Displaying SQL Agent Jobs Running at a Specific Time - Recently I was troubleshooting at a customer location something that looked trivial. When the customer approached me for a consulting requirement wherein they were saying their system was going unresponsive every day in the morning around a certain time. ...(more)

SQL SERVER – FIX Error 5120 – Database is in Read Only Mode After Attaching Files - Sometimes you need to know more about permissions on file, service account etc. to fix certain errors. In my recent visit to a customer location, one of the database developers came to me with a problem. He said that when he attaches MDF and LDF file ......(more)

First Responder Kit Update: sp_BlitzCache Catches Cursors, Scalar Functions, and More - Every time we take on a client for our SQL Critical Care®, the very first thing we do is run the First Responder Kit scripts: sp_Blitz to get a health check, sp_BlitzFirst to check their wait stats, sp_BlitzIndex to look at their indexes, and sp_BlitzCache ......(more)

Query Store – Part 2 - Configuration Settings - Introduction to Query Store covered here. Now for the second part on “How to Configure?”Query Store is configured at the database level. Right Click on the Database-> Properties ->QueryStore* Operation Mode: To turn the query store on set "Operation ......(more)

Blogs : Analysis Services / BI

Analyze DirectQuery requests using DAX Studio - This article describes how DAX Studio can analyze the SQL queries sent to a relational database by Power BI or Analysis Services Tabular in DirectQuery mode. The recent release of DAX Studio 2.5 introduced a new feature to analyze MDX and DAX queries ......(more)

First Thoughts On Azure Analysis Services - Hurray! At last we have proper cloud-based Analysis Services! The official blog post with the announcement is here: https://azure.microsoft.com/en-in/blog/introducing-azure-analysis-services-preview/ …the pricing details are here: https://azure.microsoft.com/en-us/pricing/details/analysis-services/ …and ......(more)

Deleting Cached Logins For Azure Analysis Services In SQL Server Management Studio - When Azure Analysis Services was announced I had to try it out right away. Of course I didn’t read the instructions properly so when I tried to log in to my Azure Analysis Services instance from SQL Server Management Studio, like an idiot I logged in ......(more)

Blogs : Azure SQL Database

Restoring An Azure SQL Database - Not too long ago, I had a chance to restore a database in Azure SQL Database, as someone had accidentally run an UPDATE statement without a WHERE clause. That happens to the best of us, and it’s one of many reasons why we keep database backups around. ...(more)

Blogs : Backup and Recovery

Backup to remote Azure URL failed due to throttling at VM level - There are multiple levels of throttling with Azure. You can get throttled at disk level, storage account level or VM/host level. These types of throttling can manifest into something that may not be that obvious. Recently we received a call from ......(more)

Blogs : Big Data

Big Data and Small Data: What’s the Difference? - Click here to learn more about author Sahil Miglani. The technology domain has witnessed a dramatic growth in the recent past. Methodologies have changed, new ways have been adopted by companies to accomplish these desired tasks. No wonder this change ......(more)

Blogs : Computing in the Cloud

Going PaaS? Think Google Cloud Platform for big data. - While a lot of companies still think of cloud in terms of infrastructure-as-a-service—virtualized, distributed hardware that brings agility and cuts costs—more and more are branching out to explore other “as-a-service” flavors. For enterprises that want ......(more)

Wildcard vs Regular Expressions – Lucene Query in Azure Search - Sometimes clever developers let you try out cloud services with very little work on your part. This is one of those times – get ready for “Playing with Azure Search: No Installation Required.” I recently saw a question about Azure Search on Twitter’s ......(more)

Blogs : Database Design, Theory and Development

Database Design Matters, RTO and Filegroups - RTO and RPO are two terms that must sound familiar for database professionals, but not everybody know that a good database design can dramatically improve our recovery time in case of disaster Usually it’s said the most important duty of DBA’s is to ......(more)

Blogs : DMO/SMO/Powershell

Convert User Friendly Retention to DateTime value with PowerShell - I think the title is fairly descriptive so let me put a little context around it for you. In my SQL Server environment we backup our databases to local disk. Unfortunately we don’t have unlimited storage for backups, which means we have to delete the ......(more)

Building a Better Get-SQLErrorLog - The Get-SQLErrorLog cmdlet is a nice addition to the SQL Server PowerShell module, but the tools team "mustn't be afraid to dream a little bigger, darling." ...(more)

PowerShell Editors - Editors are the bread and butter in the life of an individual that wants to write PowerShell, whether for serious development or just starting. You could also be ready to get involved in open source projects around PowerShell, ......(more)

Blogs : Events

Learn Advanced Query Tuning in Washington DC - Just about every database professional on Earth has the same goal: Make things faster. There are numerous ways to achieve that goal, but for many people it comes down to a series of hunches, guesses, and trying whatever seemed to work last time. What ......(more)

Notes on Designing an After Hours Event for the PASS Summit - This year we’re trying out two ideas on Thursday night, a networking dinner (of a sort) and game night. I wanted to share some ideas on how and why we put these together. We’ve long tried to encourage more after hours events with limited success. Of ......(more)

My Next Crazy Idea: A Free Online Conference - I wanna build something awesome where: Speakers submit session abstracts publicly online Vendors can be speakers, too (anybody can apply to talk about anything – your own open source project, paid project, Microsoft can apply, hardware vendors, whatever) Readers can ......(more)

SQL Saturday Raleigh - SQL Saturday Raleigh 2017 is official. The event will be Saturday, March 11th at William Peace University in Raleigh, North Carolina. SQL Saturdays are free, one-day training events for database administrators, database developers, and anybody in the ......(more)

Blogs : Integration Services/ETL

How-to load data fast into SQL Server 2016 - What will be the absolute fastest way to load data from a flatfile into a table within SQL Server 2016? A lot has changed since my initial post on this topic many years ago, ike the introduction of In-memory optimized tables and Updateable Columnstore ......(more)

Blogs : Machine Learning

New Artificial Intelligence Tool Could Save Lives and Healthcare Costs - by Angela Guess According to a recent press release, “Radiologists spend years acquiring and refining the skills needed to expertly interpret the subtle features found in medical images. A new software tool under development by Clearview Diagnostics ......(more)

Artificial Intelligence and the Future: Should We Worry About Our Data Jobs? - Speaking at the DATAVERSITY® Smart Data Online 2016 Conference, Adrian Bowles, industry analyst, recovering academic, and founder of STORM Insights, and Steve Ardire, Merchant of Light, both talked about the future of Artificial Intelligence (AI). ...(more)

Blogs : MDX/DAX

Nested Variables In DAX - Last week, at the SQL Server Days conference in Belgium, Kasper mentioned in his presentation that it was possible to define variables inside variables in DAX. So, for example, you could define a measure like so: MyMeasure = var Outer1 = ......(more)

Blogs : NOSQL

Integrating PolyBase with Cloudera using Active Directory Authentication - This article outlines the steps to use PolyBase in SQL 2016(including R-Services) with a Cloudera Cluster and setup authentication using Active Directory in both SQL 2016 and Cloudera. Prerequisites Cloudera Cluster Active Directory with Domain Controller SQL ......(more)

Blogs : Performance and Tuning

My 2016 - 2016 was a blast at work. For most of 2016, I worked with a small team of three called the Samurai team. The team name was taken from the wandering Samurai who wanders from village to village doing good where ever injustice was found. Our job was to ......(more)

Latch Coupling in SQL Server - (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) Years ago I blogged about Latches and why they are needed ......(more)

A Sub-Query Does Not Hurt Performance - The things you read on the internet, for example, “don’t use a sub-query because that hurts performance.” Truly? Where do people get these things? Let’s Test It I’ve written before about the concept of cargo cult data professionals. They see one issue, ......(more)

Blogs : PowerPivot/PowerQuery/PowerBI

Importing Excel Data into Power BI Desktop - For many organizations, Microsoft Excel remains a critical tool for recording, calculating, and sharing data, especially when it comes to number crunching and disseminating vital information. Those workbooks contain a great deal of important data, and ......(more)

Power BI Custom Visuals Class (Module 22 – Force-Directed Graph) - In this module you will learn how to use the Force-Directed Graph Power BI Custom Visual. The Force-Directed Graph allows you to display relationships between between your data in a fun and interactive way. Module 22 – Force-Directed Graph...(more)

First thoughts on Power BI on premises - The wait is over, the Reporting Services team finally released a preview for Power BI Desktop reports hosted on premises using Reporting Services! The preview itself is a VM hosted in Azure. You can read all about setting it up in the SSRS team blog ......(more)

Blogs : Professional Development

My How to Think Like the Engine Class is Now Free – and Open Source - You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. You’ve never had a formal database internals class, and you don’t ......(more)

Blogs : R Language

Introducing R-hub, the R package builder service - If you're developing a package for R to share with others — on CRAN, say — you'll want to make sure it works for others. That means testing it on various platforms (Windows, Mac, Linux, and all the versions thereof), and on various versions of R (current, ......(more)

ReporteRs: Manager-friendly Word or Powerpoint documents created with R - As much as we data scientists would like to work solely in R, there inevitably comes the time when our managers or customers want to see the fruits of our labours as a Word document or PowerPoint presentation. And not just any old document: it needs ......(more)

Blogs : Reporting Services

Technical Preview of Power BI reports in SQL Server Reporting Services now available - It’s finally here! Following our announcement Tuesday and subsequent session at PASS Summit 2016 this week, we’re pleased to announce the Technical Preview of Power BI reports in SQL Server Reporting Services is now available for you to try in the Azure ......(more)

Blogs : Software Development

Agile or Waterfall? - Waterfall project management is a serial approach to the phases of a project. Proponents of waterfall methodologies hold it’s best to plan: gather requirements, design the solution, develop it, test it, deploy and maintain it in discrete steps. Critics ......(more)

Static Data with Multiple Database Tenants - The loan origination system my team and I are responsible for have 20 tables marked as static data in Redgate's SQL Source Control. One example of static data is a tile type. The loan origination system has a dashboard made up of multiple tiles. There ......(more)

Starting a ReadyRoll Project - This is part 5 in the series named SQL Server and Continuous Integration. This post will cover the steps I follow to setup a ReadyRoll project. Topics covered so far in this series are: Git Visual Studio ReadyRoll Installation I’m using ReadyRoll in ......(more)

Blogs : SQL Server 2016

How bwin is using SQL Server 2016 In-Memory OLTP to achieve unprecedented performance and scale - bwin (part of GVC Holdings PLC) is one of Europe’s leading online betting brands, and is synonymous with sports. Having offices ......(more)

Blogs : T-SQL

Triggers: Threat or Menace? - The original SQL-86 standard did not have TRIGGERs. In fact it did not have much of anything! My favorite omission to the standard was that you could grant privileges to a user, but you could not revoke them. Talk to security officers about this and ......(more)

If You Read One Article About Split String in SQL SERVER 2016s Read this One #3 - In the Series of SQL SERVER 2016, this is another post. Before Jumping in detail just think if you have a comma or other separator string and if you have to split it by separator field then for such task in previous SQL SERVER versions either you will ......(more)

Fixing Untrusted Foreign Key or Check Constraint - Untrusted constraints can be found when you alter/drop foreign key relationships and then add them back without the proper syntax. If you are deploying data through several tables, you might want to disable foreign keys on those tables during the deployment ......(more)

Articles : Administration

How to Fix Ten SSDT Deployment Snags, With or Without ReadyRoll - Even a database development framework like Sql Server Data Tools (SSDT) doesn't get it right all the time, and there are ten deployment 'gotchas' in particular that can cause some head-scratching amongst developers to get right. From his unique perspective of creating a tool to make such deployments in SSDT less stressful, Dan Nolan discusses each pitfall and how to avoid it, whether you have ReadyRoll or not....(more)

Articles : Development

Triggers: Threat or Menace? - Triggers are generally over-used in SQL Server. They are only rarely necessary, can cause performance issues, and are tricky to maintain If you use them, it is best to keep them simple, and have only one operation per trigger. Joe Celko describes a feature of SQL that gets complicated fast....(more)