The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

There was a day when you didn’t have many decisions to make about a new SQL Server instance. You had to choose from a few editions and two licensing models: per proc or server cal. It wasn’t long ago that SQL Server would almost always be installed on a physical server on premises, and the DBA had full control and responsibility over the operating system, storage, processors and RAM. Those days are long gone.

Today, most SQL Server instances are virtualized. That seemed to happen overnight about seven or eight years ago. You don’t even have to host the VMs yourself. There are plenty of hosting companies willing to do it for you, and you can create VMs on Azure or Amazon.

Eventually, Microsoft made a database-as-a-service offering available on Azure, which is now called Azure SQL Database. The biggest benefit when it was originally introduced was that you could have a database up and running in minutes with no need for hardware or even a virtual machine. The bad news, back then, anyway, was that the maximum database size was small and quite a bit of functionality was missing. Again, things have changed, and Azure SQL Database will generally have new features in place well before they appear in traditional SQL Server instances. The maximum database size in the Azure SQL Database Premium service tier is four terabytes at the time of this writing.

About the same time that Microsoft introduced the database-as-a-service, they also came out with Parallel Data Warehouse (PDW). PDW was an appliance meant for multi-terabyte data warehouses running on premises. Just getting started with PDW cost a couple of million dollars or more for the hardware and SQL Server licenses. Today, PDW is renamed Microsoft Analytics Platform System (APS) and also supports querying data stored on Hadoop by way of PolyBase. Don’t have a few million sitting around to buy the appliance? You can be up and running in the cloud with the Azure SQL Data Warehouse, the latest data warehouse offering, in minutes without that initial investment in hardware. While you must pay for storage costs continuously, you pay for compute by the hour only when running.

For years, SQL Server ran only on Windows. SQL Server 2017 can run on Linux and in Docker containers. You can connect to the instance with SSMS or with the new SQL Operations Studio. Either way, you may want to dust off those Bash scripting skills...

Don’t want the responsibility of VM, but your app isn’t compatible with Azure SQL Database? Microsoft recently announced Azure SQL Managed Instances (in preview). You get almost 100% compatibility with traditional SQL Server instances but without dealing with patching, backups, high-availability, and disaster recovery. Amazon’s Relational Database Service (RDS) will also allow you to run a managed instance of SQL Server.

Keeping up with all the ways that you can run SQL Server can be overwhelming. My advice is to figure out what you want to accomplish, and which options will meet your goals. You have a lot of choices!

Kathi Kellenberger 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

Take the SQL Estate Data Mapping Survey and help shape Redgate’s products - Help Redgate to learn more about how SQL Server teams keep on top of sensitive data. If you're planning, or are already implementing, an information audit or data-mapping exercise for your SQL Server estate, please complete their short survey....(more)

Investigating problems with ad-hoc queries using SQL Monitor - There is nothing wrong with the principle of using ad-hoc queries; one can use them occasionally and perfectly legitimately. However, when ad-hoc queries run as part of a processes that does database operations iteratively, row-by-agonizing-row, they can be one of the most unremitting ways of sapping the performance of a SQL Server instance....(more)

Beyond Formatting: Improving SQL Code using SQL Prompt Actions - This article looks at how to use the SQL Prompt actions that you can apply as part of the Format SQL command. These actions are designed to help improve the overall quality of your SQL code, in various subtle but meaningful ways, such as qualifying object names, standardizing the use of aliases, adding semicolons to the end of statements, and removing unnecessary square brackets around object names...(more)

5 Minutes with Fritchey - For quick information on various aspects of the Microsoft Data Platform and Redgate software, look no further than Grant Fritchey's Youtube channel. In addition to all the technical content he also editorializes on various aspects of data and all things IT....(more)

T-SQL

SQL Server Graph Databases – Part 2: Querying Data in a Graph Database - SQL Server Graph Databases – Part 2: Querying Data in a Graph Database...(more)

New Poster & Wallpaper on User Defined Functions in SQL Server (UDFs) - I added a new wallpaper and poster this week… I had so much fun with the new SQLChallenge on functions that I made a poster and desktop wallpaper on user defined functions (UDFs). It’s UFO themed. Because acronyms. Grab this poster for free in three ......(more)

Tech News

Facebook CEO Vows to Alert All Whose Data May've Been Exposed - Mark Zuckerberg, breaking a media silence on the latest imbroglio to engulf the social media company, vowed to inform each of his two billion-plus users if there’s a chance their personal data was compromised. ...(more)

Dropbox IPO: 'Central Nervous System for Other Platforms' Angles for Cloud Dominance - How the stock will play out is more a question for investors than for those who are watching the storage space or seeking the most efficient cloud-based collaboration service. ...(more)

SQL Server on Linux

Mount SQL Server DMVs and Custom Queries Using DBFS on Linux - With SQL Server now supported on Linux, traditional SQL Server DBAs will need to learn how to work with Linux, and Linux administrators will need to learn how to work with SQL Server. Fortunately, there are several tools available to assist. In this article Prashanth Jayaram describes how to work with the DMV tool which treats queries as files. ...(more)

Security news and thoughts

Data Exploited, Regulators Wary, Shares Slump: Facebook Update - Facebook Inc.’s mounting Cambridge Analytica data crisis continues to roil markets and draw the ire of politicians on both sides of the Atlantic. Chief Executive Officer Mark Zuckerberg, silent so far, is under increasing pressure to testify before Congress ......(more)

Cryptocurrency Miners Set Sights on Docker - Security researchers have found that cryptocurrency mining malware can infect Docker, using clever methods designed to avoid detection. ...(more)

Replication

Azure SQL DB Managed Instances: Transactional Replication - I knew Brent and Erik wouldn’t touch replication, so I figured I’d give it a whirl. My good, old friend replication I have a love-hate relationship with replication. Mostly hate due to latency and errors, but it does serve its purpose. Before Availability ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI updates, CDS-A, DAX, Power Query and more… - Tour of the Power BI Solution Advisor (@paul_turley) Capturing Power BI queries using DAX Studio (@marcorus) Improving The Performance Of Aggregation After A Merge In Power BI And Excel Power Query/Get&Transform (@cwebb_bi) Power BI Report Server Update ......(more)

Getting Started with Power BI Desktop in 5 steps - Getting started with Power BI Desktop is easy! Adam looks at 5 steps to get you up and running quickly with Power BI Desktop. From downloading, to getting data, and then building your report visuals. Getting started with Power BI Desktop https://docs.microsoft.com/power-bi/guided-learning/gettingdata#step-2 ......(more)

Performance Tuning SQL Server

Webinar on Adaptive Query Processing for Data Platform Geeks - My session on Adaptive Query Processing has been finding significant interest in Data Platform events :) Delivered 2 webinars for Data Platform Geeks community, one of them at American time and other with Indian time. Indian time webinar gave me massive ......(more)

SQL Performance – Using Query Hint OPTION (FAST N) - It is fun working with SQL/Application developer where you exchange a lot of technical thoughts to find the root of the issues. Recently, one of the developers optimized the OLTP system stored procedure by putting the query hint OPTION (FAST ‘N’). But, ......(more)

NoSQL

Data Modeling and NoSQL - "To the question How relevant is data modeling in the world of NoSQL? I give the following answer. The main purpose of data modeling is to understand the business, some application domain, some users world. The model becomes a representation of that ......(more)

Microsoft News

Microsoft Details Windows Machine Learning for Gaming - In a day filled with all sorts of game development-related API and framework news, Microsoft also has an AI-related announcement for the day. Parallel to today’s DirectX Raytracing announcement – but not strictly a DirectX technology – Microsoft is also ......(more)

Microsoft delivers first test build of Windows Server 2019 - Microsoft is releasing to Insider testers its first test build of Windows Server 2019, due later this year, which will be its next Long-Term Servicing Channel release of its OS. ...(more)

HA/DR/Always On/Clustering

TempDB Filling Up On Secondary Replicas - Have you ever had an issue where TempDB was filling up on your secondary replicas? Do those secondaries happen to be read only replicas? When I have an issue with tempdb filling up the first thing that I usually do is try to figure out exactly what ......(more)

Data Privacy and GDPR

Query Monitoring and the GDPR - I’ve been reading the General Data Protection Regulation (GDPR) and discussing the ramifications of the beginning of enforcement with lots of people. The implications of it all are fascinating. The real serious issues remain primarily a business problem, ......(more)

Data Retention: An Inexact Art - A myriad of laws regulate data retention and generally compete with each other. William Brewer provides an introduction to the principles, but gives good advice -- leave the details to the specialists....(more)

Computing in the Cloud (Azure, Google , AWS)

Recover Data in SQL Server AWS RDS with the Help of BACPAC - With Amazon RDS, it’s easy to have a SQL Server running in minutes. There are some limitations, though. Seth Delconte explains a workaround to overcome one of the limitations: the inability to restore a second copy of a database to recover missing data....(more)

Azure vs. AWS Data Services Comparison - Both Microsoft Azure and Amazon Web Services offer a lot of data services. So many services that it can be hard to comprehend how the compare without a scorecard. So, that’s what I did here, I put together a quick image to help you make sense of all ......(more)

Career Growth

How to Engage Your Audience during Online Deliveries - Interested in upping your online presentation game? Looking for some tips to engage your audience on Skype calls? Read this post by Premier Developer Consultant Daisy Chaussee to learn how. Today’s modern, virtual world relies on the ability to deliver ......(more)

Azure SQL Database

SQL Vulnerability Assessment for Azure SQL Database - Back in September 2017 Microsoft announced a new security feature for Azure SQL Database called the SQL Vulnerability Assessment (VA). It is currently in preview mode where it has the ability to find, you can guess, security based vulnerabilities for ......(more)

Understanding Azure SQL Database- Introduction - Introduction:- In this first article of the series Understanding Azure SQL Database in which I am going to publish in the coming weeks to understand Azure SQL Database concepts. After this series, you will have the good understanding of the following ......(more)

AI/Machine Learning/Cognitive Services

R and Docker - If you regularly have to deal with specific versions of R, or different package combinations, or getting R set up to work with other databases or applications then, well, it can be a pain. You could dedicate a special machine for each configuration you ......(more)

What the Hottest AWS Cloud Services Tell Us About Enterprise Adoption - As organizations explore AI, machine learning, and containers, they are entrusting more sophisticated workloads to AWS cloud services, Cloudability finds. ...(more)

Administration of SQL Server

SQLskills SQL101: Is the recovery interval guaranteed? - One of the concepts I find people misunderstand frequently is the recovery interval, either for the server as a whole or the per-database setting that was introduced in SQL Server 2012 for indirect checkpoints. There are two misconceptions here: The recovery interval equals how often a checkpoint will occur SQL Server guarantees the recovery interval (i.e. crash recovery for the database will only take the amount of time specified in the recovery interval) ...(more)

Generate a SQL Script to run page-compression on all SQL Tables that exceed a certain saving rate threshold - This Script is reusing a script from Eli Leiba published here: https://www.mssqltips.com/sqlservertip/2381/sql-server-data-compression-storage-savings-for-all-tables/ and extends it to also generate the compression-script where the saving-rate exceeds ......(more)

Cumulative Update #5 for SQL Server 2017 RTM - The 5th cumulative update release for SQL Server 2017 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates. To learn more about the release or servicing ......(more)

Deadlock when creating a database – A Query Store bug - Earlier tonight, I sat down to install Reporting Services on my laptop. I haven’t played with SSRS 2017 yet, so I figured I’d poke around some. Much to my surprise, the configuration failed when creating the ReportServer database, reporting a deadlock. ...(more)

.NET Related Articles

Load JSON into SQL Server Using a Stored Procedure and C# - This post will demonstrate how to load JSON into SQL Server using a stored procedure and C#. This technique is possible with the new JSON functions starting in SQL Server 2016, and is nice for the following reasons:...(more)

Securing Angular-Based Chrome Extensions Using Azure AD and ASP.NET CORE - Azure Active Directory is a service that provides an authorization layer for different kinds of web applications easily. It is possible to implement it with many programming languages. Jakub Kaczmarek describes the development of an authorization procedure used in an Angular-based Chrome extension project....(more)

Procedural Generation with Unity and C# - Have you ever wondered how game developers create their worlds? In this article, Lance demonstrates the basics of creating a map using a procedure in C#....(more)