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.
PASS Marathon GDPR in partnership with Redgate - This special edition of the PASS Marathon series will focus on the General Data Protection Regulation (GDPR). You’ll learn how the legislation impacts SQL Server data professionals around the globe and practical steps you can take to help ensure you’re ready for when GDPR enforcement takes effect on May 25th....(more)
SQL Server on Linux: CU4 – NewSequentialId() – Uuid - Prior to SQL Server 2017 CU4 for Linux (Linux only) the generation of a sequential UUID may not function as expected. SQL Server’s NewSequentialId() calls the Windows API UuidCreateSequential, which is limited in SQLPAL, prior to the CU 4 update.
Windowing without a partition by or an order by - If you’ve never worked with windowing functions they look something like this:
SELECT name, max(create_date) OVER
-- No this isn't meant to make sense
(PARTITION BY lower(left(name,1)) ORDER BY owner_sid)
The other day someone ......(more)
The Challenge Is on: Multi-Way Number Partitioning with T-SQL and SQL CLR - The challenge is known as multi-way number partitioning. Given a set of numbers S, representing quantities or weights, and a number of desired partitions, k, divide the quantities into k partitions such that the quantities are as evenly distributed among ......(more)
Outputting Status in Your T-SQL Code - Often, you have some code that is taking a long time, and you want to know how much time sections of the code is taking, and you likely want to know immediately as your code executes. In this blog I will talk about a couple of the techniques you can use to make this happen, either in a permanent manner, as well so in a message....(more)
Get the join cheat sheet! - Download and print this nifty little PDF with all of the INNER, LEFT, RIGHT, FULL and CROSS JOINs visualized! It’ll look great on your office wall or cubicle. Your coworkers and your interior decorator will love you for it.
How it works: For each join ......(more)
CROSS JOIN for fun - My son, who wishes to be known as “Pigeon” for the duration of this blog, was mixing the names of Hogwarts houses over a game of chess* with his brother: “ONE MILLION POINTS TO GRYFFINPUFF!” So I said, “Hey guys, I’m going to do a nerdy thing with my ......(more)
SQL Server Security
Recovering a TDE protected database without the Certificate - If you’ve been careful and done everything right when you’re setting up TDE then you shouldn’t run into this problem.
We all make mistakes though, and we’ve all been asked to deal with environments that haven’t been so carefully managed.
But what if ......(more)
Security news and thoughts
New Spectre/Meltdown Variants - Researchers have discovered new variants of Spectre and Meltdown. The software mitigations for Spectre and Meltdown seem to block these variants, although the eventual CPU fixes will have to be expanded to account for these new attacks. ...(more)
Cyber Hacks Cost Up to $109 Billion in 2016, U.S. Estimates - The report details the range of threats that U.S. entities face from actors, including corporations and countries such as Russia, China, Iran and North Korea.
Capture SQL Server Reported Errors using Extended Events - In my introduction to Extended Events blog, I mentioned that the xEvent is my favorite toy for performance troubleshooting. This blog may make you to start playing with it. In this tip, I will be discussing how you can find what caused SQL Server to ......(more)
Populating PowerShell module FunctionsToExport automatically - If you've written a PowerShell module you'll be familiar with the FunctionsToExport portion of the .psd1 module manifest that starts out like this:
This array defines what functions should be accessible from outside of the module, being almost everything ......(more)
How to: Build a SQL Server Dashboard with Power BI - A couple of years ago when Power BI Desktop was released I did a blog post on How to: Build a SQL Server Dashboard with Power BI Desktop. Today I will be presenting to the local MN SQL Server User Group on this very topic.
The example I will be doing ......(more)
Power BI Issue Fix: Import from Excel Workbook Contents; Password Protection Failure - One of the great features of Power BI Desktop is the ability to import your entire Power Pivot Excel model into it. This is an awesome feature, because you can import the entire model including tables, relationships, calculations, and hierarchies into ......(more)
Performance Tuning SQL Server
SQL Server Diagnostic Information Queries for February 2018 - This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I have developed a T-SQL script that you can use to check whether your instance of SQL Server has been patched to mitigate ......(more)
Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views - Scalar functions are the butt of everybody’s jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they’re in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, ......(more)
New SQLChallenge: Defuse the Deadlock - When I first created SQLWorkbooks, I distilled what it was all about down into one sentence: “Learn SQL Server by Solving Problems.”
This month, I introduce my first SQLChallenge, which distills that down into practice. The SQLChallenge features 23 minutes ......(more)
More Showplan enhancements – UDFs - As I mentioned on yesterday’s post, with the recent release of SQL Server 2017 CU3, we released yet more showplan enhancements: you can see other posts related to showplan enhancements here.
In this article I’ll talk about the second showplan improvement ......(more)
Microsoft to Devote $500 Million in Cash, Services to Support Startups - Participants in the Microsoft for Startups program will receive Azure cloud credits, marketing and sales support and a chance at getting funded.
SQL SERVER- Unable to Failover AlwaysOn Availability Group to Disaster Recovery Site - One of my clients took my assistance to deploy Always On availability group. After few months, they added a Disaster Recovery node to windows cluster and availability group also. Later, they contacted me that they are not able to move availability group ......(more)
DevOps and Continuous Delivery (CI/CD)
How to build a DevOps roadmap to kickstart your digital transformation journey - By harnessing the concept of agility to a methodology that enables constant software innovation, DevOps allows organizations to respond dynamically to changing market conditions and rising customer expectations....(more)
What’s new in SSMS 17.5: Data Discovery and Classification - This post is authored by Alan Yu, Program Manager, SQL Server.
We are excited to announce the release of SQL Server Management Studio (SSMS) 17.5!
Download SSMS 17.5 and review the Release Notes to get started.
SSMS 17.5 provides support for almost all ......(more)
dbachecks – Configuration Deep Dive - Today is the day that we have announced dbachecks a PowerShell module enabling you to validate your SQL Instances. You can read more about it here where you can learn how to install it and see some simple use cases
One of the things ......(more)
Database Design, Theory and Development
Uniqueifier details in SQL Server - This is a follow up post for https://blogs.msdn.microsoft.com/psssql/2018/02/16/uniqueifier-cons…ns-and-error-666/, for the ones that want to delve into the subject.
First a quick background on uniqueifiers...
A uniqueifier (or uniquifier as reported ......(more)
Indexing and Partitioning - Partitioning tables is a great tool to increase the manageability of your data. Being able to move large amounts of data in and out of a table quickly is incredibly helpful.
However, partitioning comes with a whole bunch of caveats and we need to be ......(more)
The Microsoft Team Data Science Process - This is part one of a series on launching a data science project.
This is the beginning of a series of posts around growing a data science project from the germ of an idea to its fruition as a stable oak. Before I get into the process, I want to start ......(more)
Data Governance vs. Data Architecture - While Data Architecture focuses on technology and infrastructure design, Data Governance encompasses the people, the process, the workflow, as well as the architecture needed to support governance. So, even though Data Architecture is critical to Data ......(more)
Free data privacy and protection livestream on February 28 - Redgate’s Data Platform MVPs, Steve Jones, Grant Fritchey and Kathi Kellenberger, will head up the speaker line-up of this Data Privacy and Protection -themed livestream. Many of you will be aware that the new GDPR legislation comes into effect in May and, in light of this, the agenda will include sessions to help you become best equipped to deal with the challenges GDPR brings to compliant database management....(more)
Conferences and Events
It’s events season – will you join us? - There are three major events coming up, and you’re invited!
This week Redgate are excited to be exhibiting as Gold sponsors at SQLBits 2018. SQLBits is THE largest SQL Server conference in Europe, and best of all this year it’s taking place just down ......(more)
Computing in the Cloud (Azure, Google , AWS)
How to Develop and Test Software with Azure VMs - In this post, I will discuss Azure DevTest Labs, a way to provide self-service for developers and testers with helpful automation but budge control for the business.
Lack of Control
When I started working with Microsoft Azure, I quickly learned ......(more)
Azure Functions and App Service Authentication - Azure App Service has a handy authentication integration that takes away the work of integrating with various identity providers (currently: Azure Active Directory, Facebook, Google, Twitter and Microsoft Accounts). Azure Functions are built on the same ......(more)
Your guide to Azure services for apps built with Xamarin - When talking about app development today, the cloud is almost always part of the conversation. While many developers have an idea of the benefits that cloud can offer them – scalability, ready-to-use functionality, and security, to name a few – it’s ......(more)
Bugs/Patches for SQL Server
Cumulative Update #4 for SQL Server 2017 RTM - The 4th 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)
Azure SQL Database
Migrating SQL Server databases from AWS EC2 to Azure SQL Database using the Azure Database Migration Service - Amazon Web Services EC2 is a well-known Infrastructure as a Service (IaaS) platform, and several organizations use EC2 to host various RDBMS platforms, including Microsoft SQL Server. Using an IaaS platform in the cloud provides certain advantages, such ......(more)
AI/Machine Learning/Cognitive Services
The Microsoft Artificial Intelligence Landscape – And What to use When - Artificial Intelligence (AI), at its broadest definition, is simply “a machine that can act using human-style reasoning or perception”. Of course, the technologies used to enable that definition are far from simple themselves. Artificial Intelligence ......(more)
Administration of SQL Server
Changing Data Types on Large Tables: The INT to BIGINT Conundrum - Changing a data type seems like a simple task unless the table is quite large and downtime must be kept to a minimum. Danny Kruge walks you through the process he created to change an INT to a BIGINT in a large table....(more)
.NET Related Articles
URL Matching in C# - Comparing URLs in C# code is a common task and seems simple. Camilo Reyes shows us that there are many pitfalls to avoid since people can come up with several ways to type the same URL. He then demonstrates how to solve several URL comparison problems....(more)
Run Visual Studio always as administrator in Windows 10 - If you plan on using Visual Studio with IIS it will need to run as an administrator. This is so Visual Studio can interact with IIS to set up the necessary virtual directories. If you don't run Visual Studio as an administrator then it won't even load ......(more)