The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This past week was the PASS Summit in Seattle. It's an annual conference that attracts thousands of SQL Server and data platform professionals. I am lucky in that I usually get to attend, and this year was no different, although it was a short summit for me. There are some live blogs of the keynotes from Kendra for Day 1 and 2 if you want to catch up.

Microsoft was there with a large presence, and as always, they delivered a keynote and had plenty of demos that showcase new changes and enhancements for the data platform. In this case, center stage is really SQL Server 2019, though there were plenty of other items shown as well. I was surprised to see the Azure Managed Instance get so many mentions. I suspect this is an easy for for many companies to transition away from an expensive local data center, or to find ways to use less staff and continue to run a SQL Server outside of their existing infrastructure. I don't know if this is a good fit for most customers, but Microsoft certainly wants you to try it. The Business Critical edition (with business critical pricing) will release as generally available on December 1.

There were a few releases as well, which you might want to play around with in your lab. First, Azure Data Studio (ADS) got it's November release. I'm still not certain I love the tool, but Microsoft is working hard to improve it and add features. There aren't a lot of changes this month, but there are a few more extensions and a number of bug fixes. The paradigm for ADS is just a little off for me, and I'm not quite sure why. I find VS Code to work well for C#, Python, and PowerShell, so why is ADS off for me? Not sure, but let me know in the discussion if you like the tool.

We also have our second release of SQL Server 2019 with CTP 2.1 being announced. It's supposed to be available Friday for download, at least as a container, but we'll see. There aren't a lot of changes, but there are some. What's more impressive is Microsoft being able to release a second version a month after the first one. They hope to get to a monthly cadence, which I think is amazing for a large product like SQL Server, especially as Windows struggles with their cadence. 

There is one amazing new feature, which I think will really improve SQL Server performance for many systems: Scalar UDF Inlining. It's not a panacea, but it should dramatically improve the use of functions in many workloads. There are restrictions, and it's only SQL 2019, but I look forward to testing a few demos to see how well things perform with this enhancement to the query processor. You should give it a try as well, testing workloads before and enabling compatibility level 150. If you see improvement, maybe there's a good case to upgrade your instances that might be using lots of functions.

There are more announcements, especially in the BI area. We get some cool SSRS enhancements, and you might want to watch my friend, Patrick LeBlanc, demo the changes in Power BI. I love Power BI and I think this is going to be the de facto reporting tool for most organizations moving forward. Maybe it will even displace Excel for visuals.

There are a lot of moving parts in the Microsoft Data Platform right now, which may feel overwhelming to many of us. That's fine. We don't have to learn everything, but we can pick something that looks interesting and spend a few hours playing. You never know what you might get inspired to learn more about.

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.

Webinars

SQL in the City Streamed December 2018 - The theme for December 2018's SQL in the City Streamed is Your Journey to Compliant Database DevOps. The schedule includes sessions designed to broaden your skillset, support your ongoing learning, and keep you up-to-date with what’s new in the industry and at Redgate....(more)

Creating a data culture can transform risk calculation for insurers - James Boother from COEO will be joining us for a live video session to discuss the seven steps some organisations are already taking to predict future risks with even greater certainty and the opportunities Data Mastery presents insurers....(more)

able Business Agility Through Database DevOps - Founder and Chief Technologist at Nebbia Technology, Esteban Garcia, joins Redgate to discuss how DevOps helps technology teams to go faster and automate everything. As teams lower the cycle time between idea and production, how can organizations leverage these new capabilities to improve product delivery and quality?...(more)

Getting executive buy in for DevOps - 3 top tips - In this webinar, Microsoft MVP Kendra Little will discuss the value of DevOps from the perspectives of CEOs, CIOs/CTOs, and Managers. She will explore how the role of CIOs and CTOs are undergoing a major transformation, and how DevOps aligns with that transformation....(more)

Virtualization and Containers

Kubernetes For The Microsoft Data Platform Professional 101 - With the announcement of SQL Server 2019 big data clusters at Ignite, Kubernetes (often abbreviated to K8s) now stands front and center as part of Microsoft’s data platform vision. The obvious inference being that this is something that the Microsoft ......(more)

Why VMware Is Acquiring Heptio and Going All In for Kubernetes - NEWS ANALYSIS: VMware is positioning itself to be a leader in the next generation of the cloud, enabling multicloud with Kubernetes-based technology. ...(more)

Kubernetes' scheduling magic revealed - Understanding how the Kubernetes scheduler makes scheduling decisions is critical to ensure consistent performance and optimal resource utilization.Kubernetes is an industry-changing technology that allows massive scale and simplicity for the orchestration ......(more)

Vendors/3rd Party Products

Use SQL Server? We’d like to hear from you. - Redgate are running a survey to better understand trends in adoption of different technologies and platforms. Completing this survey will take just a few minutes. As a thank you for your time, they will be giving away one $100 Amazon gift card to one randomly selected participant who completes the survey by December 7, 2018. Take part here...(more)

SQL Clone V3: New features heighten visibility and manageability of clones and images - With a brand new dashboard, activity feed and search functionality there is a lot happening in the SQL Clone v3 release. Check out the full breakdown of the update and get onto the latest version. ...(more)

T-SQL

Uncovering Hidden Complexity - The other day, Erin Stellato asked a question on twitter about the value of nested SPs. Here’s how I weighed in: Hidden complexity has given me many problems in the past. SQL Server really really likes things simple and so it’s nice to be able to ......(more)

Explicitly Drop Temporary Tables Or Wait For Cleanup? - I was recently asked if we are going to see performance differences if we explicitly drop temporary tables. I couldn’t remember the specifics, but I said it actually didn’t matter. However, that answer has bugged me, so I set up a quick test. Explicitly ......(more)

SUM, AVG, and arithmetic overflow - You Shoulda Brought A Bigger Int Sometimes you run a query, and everything goes fine. For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error.SELECT u.Id, u.DisplayName, ......(more)

The Curse of Cursor Options - Red Skies At Night I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes ......(more)

Tools

Creating a Quick Dashboard Widget - I read Carlos Robles blog on creating an Azure Data Studio (ADS) insight widget and decided to try this for myself. I decided to try and get a list of object types and a count of each. Following instructions, here’s what I did. First, I wrote a query ......(more)

The Lighter Side

Nine things I learned from driving a supercar for three days - The email arrived unexpectedly. "I don't think we've ever met, but I'm the PR Manager for McLaren in North America," it began. My pulse quickened as I continued reading. "We've got a 570S Spider that is making ......(more)

The Tom's Hardware Community Gift Guides - Our personality-driven gift guides offer ideas and perfect picks for every person on your list, from the easiest to the pickiest! ...(more)

Dramatic footage of Soyuz accident shows rocket booster collision - Roscosmos On Thursday, Russian space officials held a news conference to lay out their findings into an October 11 accident that involved the launch of a Soyuz FG rocket and its spacecraft. The crew of NASA astronaut Nick Hague and Russian cosmonaut ......(more)

Testing Software

Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure - This article provides a walkthrough of database unit testing a stored procedure which contains a utility procedure within it. In this article, I am going to discuss a database unit testing scenario when a main stored procedure depends on a utility procedure ......(more)

Tech News

Supreme Court rejects industry challenge of 2015 net neutrality rules - Enlarge (credit: Getty Images | acilo) The US Supreme Court has declined to hear the broadband industry's challenge of Obama-era net neutrality rules. The Federal Communications Commission's 2015 order to impose net neutrality rules and strictly regulate ......(more)

SQL Server Security

SMO "leaks" SQL login passwords (in memory) and (some) SecureStrings in clear-text - Updated 2018-11-04: Expanded test code, renamed the title of the post so it's clearer. SMO (SQL Server Management Objects) are the .NET classes underpinning SSMS (SQL Server Management Studio) and all good PowerShell that interfaces with SQL Server. SMO ......(more)

Cannot Use the Special Principal – Back to Basics - I recently had a client call me out of the blue because he happened to be getting an error while trying to add a user to a database role. The error he was getting was “Cannot use the special principal ‘dbo’.” This error has probably cropped up on me ......(more)

SQL Server News

PASS Summit 2018 – Day 1 Keynote Announcements - Kendra at the blogger table with Bob Pusateri and Brent Ozar Good morning from Seattle, at the Summit for the Professional Association of SQL Server. I’m lucky enough to be sitting at the blogger table this morning, watching the keynote of announcements. I’m ......(more)

SQL Server 2014 Service Pack 3 is now Available!!! - The SQL Server team is excited to bring you SQL Server 2014 Service Pack 3 (SP3). SQL Server 2014 SP3 contains 25+ improvements centered around performance, scalability and diagnostics based on the feedback from customers and SQL community. These improvements ......(more)

Software Development

Setting up a simple Rest interface with SQL Server - We’re going to set up a web service for a SQL Server database using node js on a windows server. This is intended for a mobile application, but has a variety of other uses where an ODBC connection isn’t possible. This service is purely done as a demonstration ......(more)

Security news and thoughts

Introducing Scalar UDF Inlining - Last year SQL Server 2017 and Azure SQL Database introduced query processing improvements that adapt optimization strategies to your application workload’s runtime conditions. These improvements included: batch mode adaptive joins, batch mode memory ......(more)

Troy Hunt on Passwords - Troy Hunt has a good essay about why passwords are here to stay, despite all their security problems: This is why passwords aren't going anywhere in the foreseeable future and why [insert thing here] isn't going to kill them. No amount of focusing on ......(more)

Apple Patches Critical iOS, macOS and watchOS Flaws - DAILY VIDEO: Apple patches critical flaws in iOS 12.1 and macOS 10.14.1 updates, and a Visual Studio for Mac roadmap is released as Microsoft plans its future. ...(more)

Crucial and Samsung SSDs' Encryption Is Easily Bypassed - Dutch researchers found that modern SSDs come with flawed encryption schemes and implementations that could allow attackers to easily decrypt user's data. ...(more)

New data shows China has “taken the gloves off” in hacking attacks on US - Enlarge / Well, that whole thing clearly worked out well, didn't it? (credit: JASON LEE/AFP/Getty Images) Remember the good old days, when the US and China were supposedly working out new norms for the cybers, and China was going to stop all that hacking ......(more)

Intel CPUs fall to new hyperthreading exploit that pilfers crypto keys - Over the past 11 months, the processors running our computers, and in some cases phones, have succumbed to a host of attacks. Bearing names such as Meltdown and Spectre, BranchScope, TLBleed, and Foreshadow, the exploits threaten ......(more)

R Language

Working with US Census Data in R - If you need data about the American populace, there's no source more canonical than the US Census Bureau. The bureau publishes a wide range of public sets, and not just from the main Census conducted every 10 years: there are more than 100 additional ......(more)

Time series visualizations with wind turbine energy data in R - CategoriesVisualizing DataTagsData VisualisationR ProgrammingTime SeriesOne of the sectors with a huge demand for data science/analysis is the energy sector. A branch of this sector where demand is high is the green wind energy turbine sector. In this ......(more)

Management accounting and controlling in R - CategoriesData ManagementTagsData VisualisationTips & TricksIn this article you learn how to make visualizations & tables for management accounting & controlling in R. Read packages into R library First we need to read the packages into our R library. ...(more)

Explore Your Dataset in R - As person who works with data, one of the most exciting activities is to explore a fresh new dataset. You’re looking to understand what variables you have, how many records the data set contains, how many missing values, what is the variable structure, ......(more)

Python in SQL Server

Discover how SQL Server can use Python to access any NoSQL engine - .Many companies these days keep their data assets in multiple data stores.  Many companies that I have worked at have used other database systems alongside SQL Server, such as PostgreSQL instances, Redis, Elasticsearch or Couchbase. There are situations ......(more)

Product Upgrades and Releases

Python in Visual Studio Code – October 2018 Release - We are pleased to announce that the October 2018 release of the Python Extension for Visual Studio Code is now available. You can download the Python extension from the Marketplace, or install it directly from the extension gallery in Visual Studio Code. ...(more)

Azure Data Factory – Data Flow - I’m excited to announce that Azure Data Factory Data Flow is now in public preview and I’ll give you a look at it here. Data Flow is a new feature of Azure Data Factory (ADF) that allows you to develop graphical data transformation logic that can be ......(more)

The November release of Azure Data Studio is now available - We are excited to announce the November release of Azure Data Studio (formerly known as SQL Operations Studio) is now available. Download Azure Data Studioand review the Release Notes to get started. Note: If you are currently using the preview version, ......(more)

Announcing: Preview of Machine Learning Services with R support in Azure SQL Database - We are excited to announce that Machine Learning Services with support for R is now available for public preview on Azure SQL Database. Machine Learning Services has transformed SQL Server into a versatile machine learning platform. We are now bringing ......(more)

SQL Server Diagnostic Information Queries for November 2018 - This month, I have just made some more minor improvements to most of the query sets. I have a T-SQL script that you can use to check whether your instance of SQL Server has been patched to mitigate against the Spectre/Meltdown CPU vulnerability. This ......(more)

Azure Database for MariaDB in Preview - Microsoft has recently announced another Platform as a Service (PaaS) offering with the release of MariaDB in Preview in Azure. I’d like to tell you more about that offering and what are some of its advantages. First, a little history on MariaDB. MariaDB ......(more)

Azure ML Studio now supports R 3.4 - Azure ML Studio, the collaborative drag-and-drop data science workbench, now supports R 3.4 in the Execute R Script module. Now you can combine the built-in data manipulation and analysis modules of ML Studio with R scripts to accomplish other data tasks, ......(more)

Product Reviews and Articles

Retrospective Database Source Control with SQL Compare - You have tripped over a database, somewhere in the IT department, that is either not in source control, or not completely in source control. What do you do? The database may be in the middle of development, but it may even be in production. What I mean ......(more)

PowerShell

Converting SQL Server LSNs with PowerShell - If you’ve spent much time playing with SQL Server backups and restores then you’ll have discovered Log Sequence Numbers (LSNs), I’ve discussed before How SQL Server LSNs are generated, and went through how to manually convert a hexadecimal LSN to a numeric ......(more)

SMO leaks SQL login passwords and (some) SecureStrings in clear-text - Updated 2018-11-04: Expanded test code, renamed the title of the post so it's clearer. SMO (SQL Server Management Objects) are the .NET classes underpinning SSMS (SQL Server Management Studio) and all good PowerShell that interfaces with SQL Server. SMO ......(more)

Azure and Windows PowerShell: Using VM Extensions - In the third part of his series, Nicolas Prigent describes how to run post-deployment configuration and automation tasks on Azure Virtual Machines. Nicolas explains how to use Azure VM Extensions using the Azure PowerShell module to save time during the provisioning process....(more)

PowerPivot/PowerQuery/PowerBI

Power BI Dataflows, and Why You Should Care - Microsoft recently released a new feature in Power BI called “Dataflows”.  You may have seen it pop up in a recent release of Power BI Desktop under Home\Get Data\Power BI Dataflows (beta) as shown below. It is also visible when you log into PowerBI.com ......(more)

Showing actuals and forecasts in the same chart with Power BI - In budgeting reports, a common requirement is to show future sales forecast and actual sales volume on the same line chart. This article describes how to achieve this goal using DAX. The data model used for this example contains two tables: Sales and ......(more)

Calling The Power BI REST API From Microsoft Flow, Part 3: Finding All Data Sources Used In A Power BI Tenant - [Part 1 of this series can be found here and part 2 here] The problem with self-service BI is that you never quite know what your users are up to. For example, what data sources are they using? Are there hundreds of Excel files being used as data sources ......(more)

Power BI Learning Path – Free and Paid Resources - This week’s TSQL Tuesday challenge is on learning something other than SQL. I’ve written before about how to keep up with technology. When you are starting out with a technology, it’s just plain hard to get a lay of the land. So I thought I’d put together ......(more)

Performance Tuning SQL Server

Table Spool - Introduction The Table Spool operator is one of the four spool operators that SQL Server supports. It retains a copy of all data it reads in a worktable (in tempdb) and can then later return extra copies of these rows without having to call its child ......(more)

Three Easy SQL Server Performance Wins - As any veteran production DBA knows, you are often under significant pressure to diagnose and alleviate database performance issues as quickly as possible. Here are three things you might be able take advantage of, depending on your workload and infrastructure, ......(more)

Harvesting SQL Server optimizer stats detail from Query Plan XML: Part II SQL Server 2016 SP2++ - bSELECT plan_id, CONVERT(XML, query_plan) planXMLINTO ##plansFROM sys.query_store_plan;;with XMLNAMESPACES(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT plan_id, dbName, schemaName, tableName, statName, LastUpdate, SamplingPercent ......(more)

Obituaries

Naomi Williams Memorial Fund - Naomi Williams, a treasured member of our SQL Family passed away on Oct 20, 2018. A memorial fund is available to donate for her children....(more)

NoSQL

No, You Don’t Need a Blockchain - The hype around blockchain technology is reaching a fever pitch these days. Visit any tech conference and you’ll find more than a handful of vendors offering blockchain. This includes Microsoft, IBM, and AWS. Each of those companies offers a public blockchain ......(more)

Microsoft News

Windows 10 Activation Servers Hit A Snag, Deactivates Legitimate Installs of Windows - While we sit and wait for Microsoft to figure out what is wrong with Windows 10 1809 that was released and then pulled, the company is dealing with another issue this week. If you have suddenly found your install of Windows 10 to no longer be activated, ......(more)

MDX/DAX

Creating Calculated Columns Using DAX - DAX is Microsoft’s new(ish) language which allows you to return results from data stored using the xVelocity database engine, which, unlike for most databases, stores data in columns rather than rows. You can program in DAX within Power BI (Microsoft’s ......(more)

Hardware News

Asus Launches New ZenBook Laptops With Latest Whiskey Lake CPUs - Asus refreshed its ZenBook laptop lineup with Intel's 8th Gen Core processors, NanoEdge displays and other features to help the ultrabook continue to evolve. ...(more)

New Halo 15TB HDDs: Western Digital Unveils the Ultrastar DC HC620 - Western Digital this week introduced its new Ultrastar DC HC620 family of hard drives that consists of 14 TB and 15 TB models. The HDDs use shingled magnetic recording (SMR) technology and are aimed at applications that need a lot of storage space, but ......(more)

Lexar Launches 512 GB microSD Card with A2 App Performance Spec - Lexar this past week introduced its new family of A2-class microSD cards, the Lexar High-Performance 633x A2. Supporting the highest Application Performance class, the new microSD card is designed in particular for smartphones that need additional storage ......(more)

AMD Previews EPYC ‘Rome’ Processor: Up to 64 Zen 2 Cores - AMD on Tuesday formally announced its next-generation EPYC processor code-named Rome. The new server CPU will feature up to 64 cores featuring the Zen 2 microarchitecture, thus providing at least two times higher performance per socket than existing ......(more)

HA/DR/Always On/Clustering

SQL SERVER – Cluster Resource ‘AGName’ of type ‘SQL Server Availability Group’ in Clustered Role ‘AGName’ Failed - I never leave my customers alone when they are having an issue with something which I helped them. Typically, I help customers in creating POC and deploying AlwaysOn Availability Groups. Just the other day while doing the Comprehensive Database Performance ......(more)

Azure SQL DB Readable Secondary - I would like to share a little secret with you. Well, that is not exactly a secret – it has been publicly available since a good number of months in preview (6 months, I believe to be more precise), and I have been sharing the news on this feature in ......(more)

Graph Databases

SQL Graph, part II – The Edge Constraints - More than a year ago, I have started blogging about the Graph Databases at SQL Graph, part I, which besides a lot of drafts – never developed much further, but now with the announced features in SQL Server 2019 CTP 2.0 and Azure SQL Database, it was ......(more)

ETL/SSIS/ELT

DILM Tiers for the SSIS Enterprise - “How many Data Integration Lifecycle Management tiers (DILM tiers) do I need to deploy SSIS, Andy?” If I had a nickel for every time I am asked this question, I would have a lot of nickels. My answer is, “Four.” Why Four? I’m glad you asked. Before I ......(more)

Working with properties in ssisUnit - One of the ssisUnit commands is a PropertyCommand. It allows you to read or set a property of the task, the package or the project. As of the time of writing – you can’t test the properties of the precedence constraints or data flow elements (but you ......(more)

DevOps and Continuous Delivery (CI/CD)

Don’t just think database DevOps. Think compliant database DevOps. - The 2018 Accelerate State of DevOps Report from DORA specifically calls out database development as a key technical practice which can drive high performance in DevOps. It’s an interesting shift in our understanding of what should be included in DevOps ......(more)

2019 State of Database DevOps – Take the survey! - Database development practices are more integral than ever to DevOps success. How are we saving time, automating processes, deploying more frequently, all whilst keeping business critical data secure? Take part in the survey for an advanced copy of the research report, and a chance to win a $250 Amazon voucher. ...(more)

Database Design, Theory and Development

Understanding Conceptual vs. Data Modeling Part 4: Property-Entity Modeling - In Part 1  and Part 2  we explained that when the RDM (1969-70) and the E/RM (1976) were introduced, no clear distinction was made between an informal conceptual level as we now understand it, and a formal logical level. In 1980 Codd gave the first definition ......(more)

Data Privacy, Complianace, and GDPR

Users Stop Engaging With Brands After Data Breaches, Report Finds - Brands lose a lot more than just data when they suffer a data breach, according to Ping Identity. They also lose customer engagement and trust. ...(more)

Facebook to Begin Paying the Wages of Sin - NEWS ANALYSIS: The announcement by UK regulators that Facebook will be fined £500,000 is only a small beginning of what may be a very long period of misery for the social network. ...(more)

HIPAA and Database Administration – Part 2 - HIPAA was signed into law in the United States in 1996. In this article, Robert Sheldon discusses how the act affects the day-to-day responsibilities of database administrators....(more)

The Benefits of Adopting Compliant Database DevOps at PASS - The GDPR presented a challenge for PASS - a community of over 250,000 data professionals with almost 300 Local Groups around the world and 24 Virtual Groups. This whitepaper shows how PASS were able to introduce compliance by default with the new data privacy regulations by using Redgate's solutions to apply DevOps practices to the database. ...(more)

Conferences, Classes, and Events

SQL Saturday Slovenia 2018 - SQL Saturday Slovenia 2018 is coming back in town.  And it will be, as always, awesome. I prefer not to use superlatives, but the schedule speaks for it self. The event will take place on Saturday, 8th December 2018, at the Faculty of Computer and Information ......(more)

Computing in the Cloud (Azure, Google , AWS)

AzureR: R packages to control Azure services - by Hong Ooi, senior data scientist, Microsoft Azure This post is to announce a new family of packages we have developed as part of the CloudyR project for talking to Azure from R: AzureR. As background, some of you may remember the AzureSMR package, ......(more)

Amazon Redshift Unexpected Commit - If you are familiar with sqlhammer.com, then you know that I heavily specialize in the Microsoft Data Platforms. My experiences with Microsoft, and specifically the SQL Server products, established an expectation that explicit transactions would always ......(more)

Satya Nadella: The cloud is going to move underwater - Lowering Leona Philpot, Microsoft's first underwater serverpod, into the water. (credit: Microsoft) Microsoft CEO Satya Nadella says that underwater server farms are part of the company's plans for future data centers. Microsoft has been experimenting ......(more)

Premium blob storage - As a follow-up to my blog Azure Archive Blob Storage, Microsoft has released another storage tier called Azure Premium Blob Storage (announcement).  It is in private preview in US East 2, US Central and US West regions. This is a performance tier in ......(more)

Career Growth

Speakers wanted for Richmond (VA) SQL Server Users Group – 2019 - The Richmond SQL Server User Group, located in Richmond, VA, is looking for speakers for the 2019 calendar year. Due to limited bandwidth at our venue, we prefer in-person presenters. We currently have openings for the following dates (meetings start ......(more)

PASS Summit 2018 – Keynote Day 2 Commentary - Kendra and Malathi Mahadevan before the keynote It’s the second main-session day of the Summit for the Professional Association of SQL Server. I had a great breakfast this morning with Brent Ozar, along with Nathan and James from my first timer group. ...(more)

How do I ask for SQL Server help on Twitter? - For those of us who have been on twitter for a long time, we are familiar with the #sqlhelp hashtag. However, I was giving a presentation to a user group last night and came across a case where folks in the room didn’t know about the hashtag. I mentioned ......(more)

Data and AI Services training roundup October 2018 - To help you stay up to date on online training opportunities, were releasing a monthly list of the latest free Data and Artificial Intelligence (AI) sessions in one convenient post. SQL Server 2019 SQL Server 2019 brings innovative security and compliance ......(more)

How I deal with depression - Content warning: depression, suicide Matthew Roche recently blogged about his struggles with mental illness. I applaud his courage, because it’s easy to worry what people will think about you. More recently, a member of the SQL Community took her life, ......(more)

T-SQL Tuesday – Non-SQL Server Technologies - So, this month’s T-SQL Tuesday topic is to think about a non-SQL Server technology that we want to learn. For me, I’m going to pick machine learning. As a DBA, I’ve always looked at machine learning as a thing for the BI guys.  I’m a DBA after all why ......(more)

T-SQL Tuesday 108 – Non SQL Server Technologies - Nice to know I'm not an old dog just yet, I'm learning new tricks! ...(more)

Azure SQL Managed Instance

Azure SQL Managed Instance – Provisioning - Once you have created the prerequisites, you are ready to create your first Managed Instance.  As of now, Managed Instance is only available in the following subscription types: Pay-As-You-Go Enterprise Agreement Cloud Service Provider Information ......(more)

“Cannot OPEN BACKUP device” error seen when backing up Azure SQL Managed Instance to Azure Blob Storage - “Cannot OPEN BACKUP device” error seen when backing up Azure SQL Managed Instance to Azure Blob Storage.    We have been seeing a trend in customer support cases with the following error   When attempting to backup a database in an Azure SQL Managed ......(more)

Azure SQL Database

Change collation of existing Azure SQL database - When you are creating a new database you can change the collation in the portal Or else using TSQL (https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=azuresqldb-current)   However there is ......(more)

Azure SQL Database – Aborting Scale Request - Scaling up or down an Azure SQL Database is a very common task. Whilst common it is also very easy to do via the Azure portal or even PowerShell. When you scale a database please be aware that  it creates a … Continue reading ? ...(more)

Azure SQL Database Token-based authentication with PowerShell - How to connect to Azure SQL Database using token-based authentication in PowerShell native apps This guide assumes you already have a deployment of an Azure SQL Database, your PowerShell environment configured and you have an app registration for a native ......(more)

Understanding Dynamic Data Masking in Azure SQL DB - Understanding Dynamic Data Masking. When dynamic data masking is applied, the main objective is to limit the exposure of confidential information to users who do not have the necessary privileges to observe confidential information. Problem Summary. Once ......(more)

AI/Machine Learning/Cognitive Services

Ethics in Data Science and Artificial Intelligence - Microsoft redid its Data Science Track not too long ago and they added several courses and new progress points. One of those progress points is Apply Ethics and Law in Analytics and the course to cover this point is Microsoft DAT249x: Ethics and Law ......(more)

Image Processing- Invoice recording using Power App, Microsoft Flow and Cognitive Service- Part 1 - For a month I am looking to create a Microsoft App that user able to take a picture from an invoice then able to see the items in Power BI or store it in storage such as SQL server, excel and so forth. In this posts, first I will show how to create a ......(more)

LUIS: Notes from the Field of Natural Language Processing - I'm Anna Thomas, an Applied Data Scientist within Microsoft’s AI Engineering team. My focus the past two years has been in the “Applied AI” realm, which basically means integrating pre-built AI services into applications to make them smarter and more ......(more)

Administration of SQL Server

Creating Database Maintenance Plans - Using database maintenance plans, we can automate basic database administration activities. Maintenance plans are SQL Server integration service packages which are executed by SQL Server Agent job on configured schedule. Using maintenance plans, we can ......(more)

Enable Machine Learning Services on SQL Server - R Services (SQL Server 2016) or Machine Learning Services (2017 and 2019 CTP) provide you with the ability to perform data analysis from the database itself using T-SQL.You can learn a little more about what you can do in the SQL Server blog.On this ......(more)

Refresh SQL Modules - As is true in most facets of life, things tend to get stale and old. Sometimes this staleness can be visibly represented as the wrinkles on your face. Other times, as with SQL Server, it may mean that a stored procedure or view stops working. Unfortunately, ......(more)

Query Store Performance Overhead: What you need to know - “What is the performance overhead of enabling Query Store?” I get asked this question almost every time I present on a topic related to Query Store.  What people are really asking is “Does enabling Query Store affect the performance of my queries?”  ......(more)

Configuring Database Mail in SQL Server - SQL Server Database Mail has been introduced in SQL Server 2005. Database Mail is a component that can send emails using SQL Server Engine. Using Database Mail, an administrator or a developer can send query output to an end user. DBAs can configure ......(more)

Filtered Indexes 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.) Today I want to talk about a specific kind of index ......(more)

SQL SERVER – Unable to Start Service SQLSERVERAGENT on Server (mscorlib) - There are several reasons for the error mentioned in the blog title. In this blog, we would learn about one of the causes of error Unable to start service SQLSERVERAGENT on a server. When my client contacted me, they were in process of changing the hardware/drives ......(more)

SQL SERVER – Script level upgrade for database ‘master’ failed because upgrade step msdb110_upgrade.sql encountered error 926, state 1, severity 25 - I have helped many clients in upgrade failures via my On-Demand consulting and I am amazed to see various ways which can break SQL Server upgrade. In this blog we would learn about fixing error Script level upgrade for database ‘master’ failed because ......(more)

Failing to restore a database having ‘In-memory OLTP’ database - A few days ago, encountered a weird scenario where a 'In-memory OLTP' database had to be restored from its backup and it was failing to restore - precisely getting stuck in the Recovery state.  Checked the error logs and found the below log snippets ......(more)

Failed Installation of SQL Server Cumulative Updates Debugged - Background Our customer has recently experienced an issue during the installation of the latest cumulative updates in their SQL Server environment. Instances for one specific application ended in a crashed state. The update process was upgrading SQL ......(more)

Failed Installation of SQL Server Cumulative Updates Debugged - Background Our customer has recently experienced an issue during the installation of the latest cumulative updates in their SQL Server environment. Instances for one specific application ended in a crashed state. The update process was upgrading SQL ......(more)

Adaptive Query Processing in SQL Server 2017 - As new versions of SQL Server have been released, the Query Optimizer has undergone many improvements. Adaptive Query Processing, new in 2017, represents a new direction. This set of features provides some flexibility for varying join types, multi-statement table valued functions, and memory grants. In this article, Robert Sheldon explains the three features that make up Adaptive Query Processing...(more)