The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

The GDPR is being enforced as of yesterday. It's been a law for two years, but fines are now going to be assessed for violations. If you're like me, you've been getting a lot of different types of privacy policy updates, new opt-in requests in email, some notices in email with the burden on you to opt-out, and more. A few people joked about how many they've been getting, and certainly I've seen no shortage of updates. I've even seem some updates to services that don't allow access to content, such as YouTube, without clicking some accept button.

It's interesting to see the various approaches being taken. Last week in London, Redgate held a SQL Privacy Summit and I was honored to host a panel discussion from various industry experts. They had different takes on the GDPR, though most of these people were pro-GDPR, happy that some proper data handling was being enforced. That's the attitude that many DBAs in know in the EU, as they now have some legal reasoning why we should implement better data handling and security practices.

However, I've also seen that there are different interpretations of how to deal with data. Do you need to ask all customers to opt-in? Can you continue to use data in development and test environments? Can you process data as you already have if you disclose what you're doing? Is the burden on the company or the data subject? I'm sure we'll see various decisions and rulings from regulatory authorities across the next year as data subjects complain and companies try to do the minimum level of work.

The idea of data being somewhat co-owned by a business and an individual is fascinating I see both sides, and I certainly would like to have some rights over data about me. I definitely think my address, my date of birth, and more should be secured and companies that use my data should have some liability if it's disclosed. I'm not sure about rights over how it's used, but that's certainly a discussion that's coming.

I've already seen one organization file suit over access to data, because they're being forced to consent to handling that they disagree with. That is going to be something I watch carefully. Can a company change their terms arbitrarily, ask me to consent in a take-it-or-leave-it fashion, and withhold access to data? Do I own my messages and data stored in services? Is it co-owned?

Like it or not, the GDPR is forcing us to have some discussions and debates about digital information, which is good.

We're mostly ready at Redgate, and certainly continuing to do work. Ultimately, our reading of the GDPR (with some backing from auditors), is that we don't have to be perfect today, but we need to be making an effort and be able to prove that we are doing so. So on a day after the GDPR went into enforcement, what are you doing? Do you think you're ready?

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

How to unpack the ROI of Database DevOps - DevOps is becoming mainstream, and the results are impressive, but the database is commonly a bottleneck. Join Tom Austin to learn about the increasing list of compelling reasons to extend DevOps practices to databases, and how to approach measuring the impact this will have for YOUR organization. ...(more)

Virtualization and Containers

Deploying Azure Container Instances - In a previous post I went through how to Push an image to the Azure Container Registry Now let’s look at using that image to create an Azure Container Image instance. Azure Container Instances (ACI) are Microsoft’s serverless container technology. They ......(more)

Vendors/3rd Party Products

Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement - SQL Server’s TOP clause in a SELECT statement is very useful and intuitive, but it allows you to leave out the associated ORDER BY clause that clarifies what you had in mind: TOP by what aspect? Phil Factor explains how to find code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement ...(more)

T-SQL

You know everything about GROUP BY. Do you? - SQL is a stout language and SQL Server has so many features that it’s impossible to be an expert in everything. I see a lot of people gravitate towards performance; but, that’s not the best place to focus. If you don’t know the fundamentals of the language ......(more)

How to Group By “Nothing” in SQL - The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by “nothing” in SQL. E.g. when querying the Sakila database: SELECT count(*) FROM film GROUP BY () This will yield: count ......(more)

Convert a Positive Number To a Negative In SQL With a Spot of Bitwise Logic - Now this is something that I’ve seen asked a few times and it’s always a question that gets a fair number of different answers. How do we go about converting a positive number to a negative, or the other way around? I’ll give you my solution and it ......(more)

Converting JSON to SQL Server CREATE TABLE Statements - Watch this week’s episode on YouTube. Tedious, repetitive tasks are the bane of any lazy programmer. I know, because I am one. One such repetitive task that I find comparable to counting grains of rice is building database layouts from JSON data sources. While ......(more)

How to pass data between procedures using temp tables. - In this post I will show how in some cases we can split a long procedure to a small and manageable parts using a simple technique of passing data in temp tables ...(more)

Dates and Times in SQL Server: more functions you should never use - Previously we looked at four built-in functions to get the current date and time in SQL Server and Azure SQL Database using Transact-SQL (T-SQL). We identified that out of the options provided, SYSUTCDATETIME() is the recommended method because it relies ......(more)

How to Extract last name or first name(s) from a combined field - Extracting pieces of information from a string can be a daunting task. This is an example of a REVERSE technique. The post How to Extract last name or first name(s) from a combined field appeared first on SQLTreeo. ...(more)

What is Deferred Name Resolution, and How Can It Go Wrong? - I got a question recently about why a particular bit of code was failing when running. My first thought: oh, that’s probably related to… whatsitcalled. You know, whatsitcalled, one of those features you notice just often enough so that it’s name doesn’t ......(more)

Tools

Mssql-cli Command-Line Query Tool - A recent announcement on the release of several SQL Server tools has raised expectations across various groups. Product requirements and business are almost always a trade-off, and striking the right balance in a product in terms of the toolset is a ......(more)

Removing servers from the SSMS connection dialog (Day 36) - One of the things that you will no doubt end up experiencing as a DBA is that you will see new SQL Server instances come into existence. Going right along with this is that you will see old instances die off. However, they don’t just disappear from the ......(more)

The Lighter Side

America, your offshore wind is coming: 1.2GW in contracts awarded - European offshore wind farms have made big US projects possible. Massachusetts and Rhode Island both awarded major offshore wind contracts on Wednesday, underscoring the increasing economic viability of a kind of renewable energy that has been long ......(more)

In absence of fog, the images from a SpaceX launch Tuesday are stunning - SpaceX Most of us associate Southern California with Hollywood, beaches, and sunny weather. However, with relatively cold waters offshore and typically higher pressures over the Pacific Ocean, there is essentially a competition between air rising from ......(more)

Tech News

Americans Are More Worried That AI Will Hurt Jobs Than Help Them - A Brookings Institution survey offers fresh fodder for the debate, showing opinion is heavily tilted against AI at a time companies are increasingly looking for ways to include it in everything they do. ...(more)

SQL Server Security

Tips for Securing SQL Server Database from All Sort of Risks - Securing SQL Server Database is a challenging task as there are number of external and internal risks or parameters affecting the Server and its databases. In this section, we have discussed about all the concepts and the precautions that can be taken ......(more)

SQL Server Database Security and Source Control - Security in SQL Server, at its core, is a pretty simple topic (not in actually configuring and getting it right, but the mechanics of how security works.) There are server and database principals, and you can grant, take away, and deny rights to these ......(more)

SQL: Assign permissions to the table type not directly to the object name - When you use table types in SQL Server, you need to assign permissions on them, and usually EXECUTE permissions. On one of my mailing lists, I saw a posting about how the user was trying to assign permissions to a table type (for use as a table-valued ......(more)

The trials and tribulations of REVERTing from impersonation - Impersonation is a highly useful tool in your toolbox. With the EXECUTE AS clause you can allow your stored procedure to do things that the user doesn’t have permission to. EXECUTE AS LOGIN/USER gives you the ability to pretend you are someone else test/view ......(more)

SQL Server News

Cumulative Update #7 for SQL Server 2017 RTM - The 7th 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)

Turbo boost data loads from Spark using SQL Spark connector - Reviewed by: Dimitri Furman, Xiaochen Wu Apache Spark is a distributed processing framework commonly found in big data environments. Spark is often used to transform, manipulate, and aggregate data. This data often lands in a database serving layer like ......(more)

Security news and thoughts

Amazon confirms that Echo device secretly shared user’s private audio - Enlarge (credit: Jeff Dunn) Amazon confirmed an Echo owner's privacy-sensitive allegation on Thursday, after Seattle CBS affiliate KIRO-7 reported that an Echo device in Oregon sent private audio to someone on a user's contact list without permission. "Unplug ......(more)

Intel Set to Patch Two New Meltdown, Spectre Vulnerabilities - Two new variants of the Meltdown and Spectre CPU vulnerabilities have been disclosed by Google and Microsoft. ...(more)

Comcast bug made it shockingly easy to steal customers’ Wi-Fi passwords - Enlarge (credit: Comcast) A security hole in a Comcast service-activation website allowed anyone to obtain a customer's Wi-Fi network name and password by entering the customer's account number and a partial street address, ZDNet reported yesterday. The ......(more)

Accessing Cell Phone Location Information - The New York Times is reporting about a company called Securus Technologies that gives police the ability to track cell phone locations without a warrant: The service can find the whereabouts of almost any cellphone in the country within seconds. It ......(more)

Critical PGP Vulnerability - EFF is reporting that a critical vulnerability has been discovered in PGP and S/MIME. No details have been published yet, but one of the researchers wrote: We'll publish critical vulnerabilities in PGP/GPG and S/MIME email encryption on 2018-05-15 07:00 ......(more)

Reporting Services

SQL Server Stress Testing Using OStress – RML Utilities - As a DBA, implementing a new database consists of a variety of aspects like correct table structure, tuning the stored procedures, creating right indexes, and many more. But all of these items, the stress testing and performance benchmarking play one ......(more)

Replication

Transactional Replication Enhancement in SQL Server 2017 CU6 – Distribution database in AlwaysOn AG - At the event of SQLSaturday Dallas 2018, it was a great honor to present this new feature that allows you to put Distribution database in AlwaysOn AG. The slide deck for the session can be downloaded at http://www.sqlsaturday.com/SessionDownload.aspx?suid=20757. ...(more)

R Language

Time-dependent ROC for Survival Prediction Models in R - Use of receiver operator curves (ROC) for binary outcome logistic regression is well known. However, the outcome of interest in epidemiological studies are often time-to-event outcomes. Using time-dependent ROC that changes over time may give a fuller ......(more)

Python in SQL Server

Hands-on Tutorial on Python Data Processing Library Pandas – Part 1 - Pandas is a Python language package, which is used for data processing. This is a very common basic programming library when we use Python language for machine learning programming. This article is an introductory tutorial to it. Pandas provide fast, ......(more)

Zomato Web Scraping with BeautifulSoup in Python - The Data Science projects start with the collection of data. The data can be collected from the database, internet/online and offline mode. These days most of the information is available online and in order to extract that information Data Engineers/Data ......(more)

Adding Python Packages to SQL Server 2017 - SQL Server 2017 allows for the use of Python scripts called as an external script. SQL Server comes with some Python packages by default. Today I wanted to talk about adding Python packages to SQL Server 2017. To get started with Python in SQL Server ......(more)

Product Upgrades and Releases

Octopus May Release 2018.5 - This month, we shipped the next generation of our Azure support. Octopus 2018.5 bundles the latest Azure SDK and Powershell Modules, introduces new deployment targets for Azure Web Apps, Cloud Services and Service Fabric Clusters, plus adds the ability to dynamically manage them in Octopus using PowerShell Cmdlets....(more)

What’s new in VSTS Sprint 134 Update - The Sprint 134 Update of Visual Studio Team Services (VSTS) has rolled out to all accounts. In this Update we continue to increase the breadth of services offered in Azure DevOps Projects to enable you to get started quickly. The (newly renamed) Azure ......(more)

Product Reviews and Articles

Book Review: SQL Server 2017 Developer's Guide - This book’s title is misleading, it actually describes the salient new and enhanced features in both SQL Server 2016 and 2017. In other respects, how does it fare?...(more)

PowerShell

Finding Parameters that do not match Column Names - You think this will take me hours? Ha! Think again. ...(more)

SQL Dialog Box for PowerShell - I had a need the other day to build a PowerShell script to automate some tasks for installing a solution I built. I've always believed that its best to limit how much untested data entry a user has to do. Hence I wanted a dialog box for SQL connections ......(more)

Azure PowerShell – List Data Center Locations - Occasionally I will come across the need to use PowerShell for my day to day activities. One such need came up this week when I was preparing to do training with a customer. Part of that meant creating 6 data science VMs. So we updated the software on ......(more)

dbachecks – Save the results to a database for historical reporting - I gave a presentation at SQL Day in Poland last week on dbachecks and one of the questions I got asked was will you write a command to put the results of the checks into a database for historical reporting. The answer is no and here is the reasoning. ...(more)

Learning Module Scope the Hard Way - When you set a variable and it gets ignored >:( ...(more)

Create Azure VMs with PowerShell Part 2 - The ability to quickly spin up a virtual machine is one of the great things about Azure. In this article, Robert Cain describes the steps to set up a VM using PowerShell using his PSAzure module....(more)

dbachecks – Improved Descriptions - With the latest release of dbachecks we have added a new check for testing that foreign keys and constraints are trusted thanks to Cláudio Silva b | t To get the latest release you will need to runUpdate-Module dbachecksYou should do this regularly as ......(more)

PowerPivot/PowerQuery/PowerBI

SQL, M or Dax? – part 2 - This is a post about a post about a post. Thanks to those of you who are entering comments in the original May 12 post titled SQL, M or DAX? This is a popular topic. And thanks to Adam Saxton for mentioning this post in his Guy in A Cube Weekly Roundup....(more)

Import a Power BI Custom Visual - All you need to import a Custom Visual from the Power BI App Store is a Microsoft account and one Power BI report. You can import the custom visual into Power BI on the web by visiting https://powerbi.microsoft.com/en-us/ and clicking “Sign In” Note: ......(more)

More Details On Creating Tables In Power BI/Power Query M Code Using #table() - About two years ago I wrote a blog post describing how the #table M function can be used to generate tables, but in that post I only covered the functionality I used regularly – namely using #table with a list of column names or a table type in the first ......(more)

Power BI Custom Visuals Class (Module 104 – User List by CloudScope) - In this module you will learn how to use the User List by CloudScope. The User List by CloudScope is designed for showing a list of data presented in the format of a Twitter profile. Module 104 – User List by CloudScope Downloads Power BI Custom Visual ......(more)

Migrate off of Power BI Workspace Collections - In this video, Adam looks at how to migrate off of Power BI Workspace Collection to Power BI Embedded. Power BI Workspace collections will be shut down on June 30, 2018. You don't have much time before your reports and visuals will stop working if you ......(more)

Power BI with Maps: Choose Your Destination - (2018-May-20) A childhood dream to travel around the world fueled by reading Gulliver's Travels stories and Robinson Crusoe attempts to survive on a deserted island. Those books were filled with geographical description and map locations. I think I always ......(more)

Create Custom Visual with R and JSON- Part3 - In this post, I am going to show you how we can create most comprehensive Custom visual. In the last two posts published in July 2017 (post1 and Post 2). Some notes before I am going to complete the last two posts. 1- We able to declare some custom input ......(more)

Power BI updates, DAX, Dashboards and more… (May 17, 2018) - DAX Measure Dependencies in SSAS Tabular and Power BI (@biinsightnz) SQL, M or DAX? (@Paul_Turley) Announcing Dashboard Theming in the Power BI Service Power BI Service and Mobile April Feature Summary Power BI Desktop May Feature Summary BONUS ITEMS: ......(more)

Performance Tuning SQL Server

The Importance of Baselines - As a consultant working with SQL Server, many times I’m asked to look at a server that seems like it’s having performance issues. While performing triage on the server, I ask certain questions, such as: what is your normal CPU utilization, what are your ......(more)

SQLskills SQL101: Why do some wait types need to be ignored? - SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious ......(more)

Thoughts on Query Performance with TDE enabled - Microsoft state that enabling TDE (Transparent Data Encryption) usually has a performance overhead of 2-4%. That doesn’t sound like very much, and personally I wouldn’t let it bother me if I want to make sure my data is encrypted at rest. However, you ......(more)

Trivial Plans, Simple Parameterization and Check Constraints - Trivial Plans Are Weird They’re the optimizer’s “Yes, Dear”. The thing is, sometimes “Yes, Dear” can miss some important details. Cost based decisions about operators Potential index improvements Contradiction detection for check constraints Simple ......(more)

How to Reduce the CPU Overhead of Dynamic SQL - Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string. For frequently executed queries, “EXEC sp_executesql @sql”is a good choice but at the risk of encountering parameter sniffing issues. For ......(more)

A View Will Not Make Your Query Faster - Twice recently, one on a blog post, and one in a forum post, I’ve seen people state, unequivocally, without reservation or hint of a caveat, that, “Oh, just put that query into a view. It will run faster.” To quote the kids these days… Time for a rant. But ......(more)

How to Create Deadlocks and Troubleshoot Them - Watch in awe and wonder as I create a deadlock, then use sp_BlitzLock after the fact to show you which queries and tables were involved: Here are the scripts to run in the left hand window:CREATE TABLE dbo.Lefty (Numbers INT PRIMARY KEY CLUSTERED); INSERT ......(more)

Grouping dates without blocking operators - It’s not entirely uncommon to want to group by a computed expression in an aggregation query. The trouble is, whenever you group by a computed expression, SQL Server considers the ordering of the data to be lost, and this will turn your buttery-smooth ......(more)

Find Query with Implicit Conversion in the Plan Cache - In my previous blog, I discussed about “Which are the queries using a particular index or table?“. Today’s post, I am going to show how you can find all queries with Implicit Conversion in SQL Server. What is an implicit conversion? Implicit conversions ......(more)

When Good Query Plans Go Bad - Today's story starts again with a page escalation:--We are seeing very high CPU usage on DistServer01. Could you please take a look and do a health check on this server? Please let us know if there is anything we can do to help.--I connnected to DistServer01 ......(more)

Tactics to Find Outdated Statistics in SQL Server - Statistics in SQL Server are used to identify the type of plan that needs to be executed for a query. SQL Server is pretty good in estimating the statistics. But, there are some circumstances in which it does not, in that case, a better understanding ......(more)

Microsoft News

Azure Stack on-premises (and how it’ll blow your mind) - Azure Stack Summary Today I watched Ultan Kinahan (from Microsoft Global Black Belt Consulting team) presentation on Azure Stack. Short summary – it blew my mind. It reminded how powerful Microsoft really is. And how advanced things are. And that really ......(more)

Microsoft: Our chatbots can converse like humans, too - Microsoft is touting the 'full duplex' conversational capabilities of its Chinese chatbot, Xiaoice, while promising similar functionality will come soon to its Zo.ai bot. ...(more)

OpenSSH is now Part of Windows! - Today is a big day! The OpenSSH client version 7.6p1 is now part of the Windows 10 operating system! Microsoft released Windows 10 Update 1803 and included in that release is the OpenSSH client, which is installed as part of the update. That’s right ......(more)

MDX/DAX

Debug DAX using Variables in Power BI - In this video, Marco Russo, from SQL BI, joins Patrick to look at how you can debug DAX using variables in Power BI. Marco helps to break down a DAX measure and show how you can see the results as you build it up to find where the problem is. SQL BI ......(more)

Time intelligence issues in DAX for fiscal years starting in March - If you have a fiscal year starting in March, you can have a problem using the time intelligence functions in DAX. For example, by using February 28, the fiscal year starts on February 29th in leap years. DATESYTD ( 'Date'[Date], "02-28" ) By using ......(more)

A New Approach To Handling SSRS Multi-Valued Parameters in DAX Queries - Back in 2012 I wrote a blog post explaining how to handle multi-value parameters in DAX queries in Reporting Services reports. The approach I took back then was basically: Generate a string containing a pipe-delimited list of all the parameter values ......(more)

Fun with DAX – Compressing Numeric Sequences to Text - A recent post on the Power BI community website asked if it was possible to compress a group of numbers into text that described the sequential ranges contained within the numbers. This might be a group of values such as 1, 2, 3, 4, 7, 8, 9, 12, 13: ......(more)

Hardware News

Acer Unveils Ultralight 15.6-Inch Swift 5 Laptop: Less than 2.2 Lbs - Acer announced one of the company's thinnest and lightest 15.6-inch class laptops at its annual Next@Acer event. The laptop packs Intel’s latest Core CPU and can work for a day on a single charge. Meanwhile, it weighs less than 1 kilogram (2.2 pounds) ......(more)

Is There Really a Difference Between Enterprise and Consumer Solid State Disk Drives? - Why not install cheap consumer-grade solid state disk drives into enterprise-class hardware? Let us count the ways. ...(more)

Surface Book 2: Six Months Later - When the Surface Book 2 was announced late last year, I had high hopes that this was going to be among my favorite laptops, ever. All Microsoft had to do was take the original Book and address the few issues with the hardware and voila, a hero device ......(more)

Dell XPS 15 2-in-1 review: Meet the child of Intel and AMD’s unholy union - Samuel Axon This new Dell XPS 15 2-in-1 is the first convertible in the XPS 15 line, but that’s not the most interesting thing about it. Since 2010, Dell’s XPS 15 has been a reliable, 15-inch performance workhorse and a light gaming option for users ......(more)

GIGABYTE Launches Its First SSDs: Phison PS3110 S10 with Toshiba’s 3D TLC - Despite growing demand for SSDs in the recent years, the four leading makers of motherboards have largely stayed out of this market, despite the significant leverage they normally have with commodity products. However after all of this time, GIGABYTE ......(more)

HP Envy x360: 13-inch with Ryzen, 15-inch with Ryzen or Core 8th Gen - In another announcement today out of the HP camp, the company has shared with us some updates to the Envy x360 line of convertibles. The Envy x360 13 is all AMD based system with a Ryzen based APU options, while the Envy x360 15 has both Intel Core 8th Gen ......(more)

HP 13-inch Elite x2 1013 2-in-1 PC: Quad-Core, Thunderbolt 3 - HP has announced its third-generation Elite x2 2-in-1 hybrid PCs for enterprise and government clients. The new Elite x2 1013 systems received faster quad-core processors along with a larger display while retaining approximate weight and dimensions when ......(more)

HA/DR/Always On/Clustering

SQL SERVER – Install Error: Microsoft Cluster Service (MSCS) Cluster Verification Errors – Part 1 - In recent past, I was assisting a client in installing SQL Server clustered instance in a Windows Cluster. There were many errors encountered and I learned a lot from this experience. In this blog, we would learn about Microsoft Cluster Service (MSCS) ......(more)

SQL SERVER – Parallel Redo on AlwaysOn Secondary – DIRTY_PAGE_TABLE_LOCK - As most of you might know that my expert area in SQL Server in performance tuning. But, I also deal with almost every issue related to SQL Server engine. Many times, it so happens then clients call me for performance tuning and while fixing, we see some ......(more)

The Future of Disaster Recovery and IT - Click to learn more about author Chris Colotti. As we enter hurricane season, more than 10% of Puerto Rico is still without power more than six months after Hurricane Maria slammed against its shores. Some might ask how that is even possible in an age ......(more)

SQL SERVER – Unable to Bring SQL Cluster Online – Reason: The Account is Disabled - This was one of an interesting issue reported by my client. They informed that after some hardening by the security team, they are not able to bring SQL resource online in the cluster. In this blog, we would learn about how to fix the issue where SQL ......(more)

ETL/SSIS/ELT

Handling JSON in SSIS - I believe the first time I ever learned JSON was a thing in the data world was when I attended SQLSaturday Washington DC in December 2012, but I never encountered it in any client projects. My clients relied solely on relational data sources for analytics, ......(more)

DevOps and Continuous Delivery (CI/CD)

The 2018 State of DevOps Survey. Don’t just wait for it. Be a part of it. - The annual State of DevOps Survey from DevOps Research and Assessment (DORA) has become a valued part of the IT information landscape. Each year, it identifies the issues that matter the most to IT professionals, and unlocks new findings to help improve ......(more)

How DevOps can automate the repayment of technical debt - One of the common things we find in enterprise organizations looking to move to a DevOps model is high levels of technical debt. To be more accurate, they are caught in a vicious cycle of technical debt to the point that trying to ship anything in a ......(more)

Database Design, Theory and Development

Back to basics: What is a HEAP table? - Understanding the fundamentals is key for success, with everything you do. These days SQL Server has expanded into much more than just a database; but, products that ingest data for analysis still need to get the data from a source, such as an OLTP SQL ......(more)

Good collection of Articles on Design Patterns - Observer vs Pub-Sub pattern ...(more)

Relationships and the RDM Part 2: Integrity Constraints - Note: This is the second of a three-part rewrite of two earlier posts, to bring them into line with McGoveran's formalization and re-interpretation of Codd's true RDM[1]. For the in-depth treatment see [2,3].(Continued from Part 1) In Part 1 I referred ......(more)

Data Visualisation

5 Tips for Building Better Data Visualizations - The best tool in the analyst’s toolbox is a convincing chart, a visual that will illustrate an insight clearly. Here are there are a few simple tips that can go a long way toward optimizing the story a chart is telling. ...(more)

Data Privacy and GDPR

Journey to GDPR compliance - May 25 is an important day for data protection in the EU and elsewhere. Alison Howard explains how Microsoft has prepared for May 25 and beyond.Continue reading Journey to GDPR compliance. ...(more)

Data Privacy Protection: A Modest Proposal - I’m arguing for opt-in-only in any relationships between individuals and other individuals or groups when it comes to PII. It's as simple--and modest (and not in the satirical way)--as that. ...(more)

Conferences, Classes, and Events

One Week to Music City Tech - Next week is Music City Tech, a three day conference consisting of Coding, Agile, and Data tracks, takes place in Nashville on May 31-June 2. There are a lot of great sessions on the schedule, covering a lot of software development topics. I’ll be delivering ......(more)

SQL Saturday Iowa City Precon June 22 - I’m extremely proud to announce that I have been selected to present an all-new preconference training session on Friday, June 22, ahead of the SQL Saturday in Iowa City, Iowa. This all-day training class, entitled “Level Up Your Cloud Infrastructure ......(more)

Free Session! DBA vs Deadlock: How to Out-Index a Deadly Blocking Scenario - I’ll be doing a free online webcast with the fine folks at Quest Software this June. Join us to practice solving and preventing deadlocks in SQL Server. DBA vs Deadlock: How to Out-Index a Deadly Blocking Scenario June 14, 2018 8:30 AM – 9:30 AM PST Deadlocks ......(more)

SQLpassion Online Trainings in June 2018 - (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 announce my SQLpassion Online Training ......(more)

Computing in the Cloud (Azure, Google , AWS)

Microsoft Azure – Azure Mobile App is available now - Hello friends, Please check the below link for my new post on Microsoft Azure Mobile App. https://social.technet.microsoft.com/wiki/contents/articles/51525.microsoft-azure-mobile-app-is-available-now.aspx Advertisements ...(more)

Career Growth

Why PASS? - In case this is your first time hearing of it, PASS is the single largest community of Microsoft Data Platform professionals on the planet. It consists of local groups, virtual groups, multiple online events, SQLSaturday, and, to pay for it all, PASS ......(more)

Blogging and when to keep your big mouth shut - I’ve been blogging for a while now. I’ve thought about blogging quite a bit. I’ve even done quite a few blogs about blogging. I’ve talked about blogging problems, and I’ve talked about deciding what to blog about. I even have my own list of blogging ......(more)

Backup and Recovery

Backup Linux/windows SQL Server databases using PowerShell and Windows task scheduler - This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines. Here’s an outline of what this article is all about: Introduction Technical specifications How to load SQL Server modules ......(more)

Discuss smart database backup in SQL Server 2017 - So far, we’ve discussed several phases of backup that starts with planning, creating, strategizing and implementing. In this article, we are going to see how database administrators can define the strategy to improve backup performance and efficiently ......(more)

Data Recovery : Page level restore in SQL Server - In this article, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key ......(more)

Azure SQL Managed Instance

Updating Azure SQL Managed Instance properties using ARM templates - Azure SQL Managed Instance is fully managed SQL Server Database Engine hosted in Azure cloud. Azure SQL Managed Instance allows you to easily change instance settings such as changing number of cores assigned to the instance, adding more storage, etc. ...(more)

Azure SQL Managed Instances – Pros and Cons - Here are few notes I took at SQLSaturday Atlanta about Azure SQL Managed Instances Today there are two managed db options in Azure: Azure SQL and Azure Managed SQL Instance Azure SQL database Pros: – Simple, easy – most things are managed like backups, ......(more)

Creating Azure SQL Managed Instance using ARM templates - Azure API enables you to create Azure SQL Managed Instance using ARM templates. These are JSON objects that contain definition of resources that should be created. You can send these objects to the Azure REST API to automate creation of Azure SQL Managed ......(more)

Azure SQL Database

Choosing Azure SQL Purchase Model and Service Tier - When we are beginning to think of migrating our on-premises databases to Azure SQL, we have to decide on a proper purchase model, a service tier, and a performance level. Before starting the Azure SQL migration process, we have to find logical and provable answers to the following questions:...(more)

Inside Azure SQL Database: Security Features - Security shouldn’t be something you pay extra for; it should be something you can access, easily, at any time. ...(more)

Azure SQL Data Warehouse and Data Lake

R In Azure Data Lake- Part1- Sample Data - Azure Data Lake store is one of the components in Microsoft cloud, that helps the developers, data scientists and analyst to store any data size, shape, and speed. Azure Data Lake is optimized for processing large amounts of data; it provides parallel ......(more)

Azure CosmosDB

Cosmos DB for the Data Professional - Cosmos DB is one of the fastest growing Azure services in 2018. As its popularity grows, data professionals are faced with a changing reality in the world of data. Data is no longer contained in relational databases as general rule. We saw the start ......(more)

Analysis Services / BI on the MS Stack

Delete SSAS Tabular Partitions with C# - This post shows how to delete SSAS Tabular partitions with C#. This is an improvement over a PowerShell script I previously wrote about, Delete SSAS Partitions with TMSL and PowerShell, because it is more dynamic and doesn’t require a manually-authored ......(more)

AI/Machine Learning/Cognitive Services

Artificial Intelligence Takes on Large-Scale Database Management - Artificial Intelligence (AI) is having a clear impact on databases. Among the major players, Microsoft is applying AI to continuously monitor Azure SQL database workload patterns and apply automatic tuning when it recognizes opportunities to improve ......(more)

YOLO on Azure Deep Learning Virtual Machine (DLVM – Linux) - In a previous post I covered setting up YOLO on an Azure DLVM. In this I'll cover setting up YOLO on a Linux DSVM/DLVM using Docker. First spin up a new Deep Learning Virtual Machine on Linux - this is already setup with Nvidia GPU CUDA drivers and ......(more)

Administration of SQL Server

DBCC Clonedatabase and Query Store - Cloning things is all the rage these days…..even Storm Troopers. Anyway, by now, you probably have seen documentation on Query Store and DBCC Clonedatabase. Query Store, introduced in SQL Server 2016, is considered the “black box” or “flight recorder” ......(more)

Virtual Log Files: 200 or 1000? - Last week I had the privilege of reviewing possibly the best SQL Server production environment I’ve seen in Canada. During the follow-up meeting, the senior DBA and I had a discussion about Virtual Log Files (VLFs), disagreeing on the maximum number ......(more)

SQL SERVER – Setup / Installation Error – Updating Permission Setting for File ResumeKeyFilter.Store Failed - My client contacted me for a quick assistance in a setup trouble. She informed me that it’s a brand-new install and its failing at the end. She was installing SQL Server 2016 on Windows Server 2016. Let us learn about how to fix Installation Error. Below ......(more)

Azure SQL Database / SQL Server – Paused Index Rebuild - I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code: CREATE NONCLUSTERED INDEX [dbo.NCI_Time] ON [dbo].[Audit] ([UserId]) INCLUDE ([DefID],[ShopID]) Msg 10637, ......(more)

SQL SERVER – FIX: Msg 22004 – The Log File is Not Using Unicode Format - Recently, I was playing with some settings in SQL Server Agent and encountered an interesting issue. In this blog we would learn about how to fix error Msg 22004 – The log file is not using Unicode format. When I tried to open SQL Server Agent Log via ......(more)

SQL SERVER – Databases Going to Recovery Pending State Randomly - One of my famous offerings is Comprehensive Database Performance Health Check where we always go over various SQL Server and database level properties. Lots of people do not know that there are quite a few SQL Server and database level settings which ......(more)

SQLskills SQL101: The Importance of Maintaining SQL Server - SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious ......(more)

What are the system databases? - I get to help with technical interviews every now and again, and one of the questions we always ask is “What are the system databases, and what are they for.” It’s a nice easy softball of a question. Or at least so I always thought until I started using ......(more)

.NET Related Articles

Razor Redux - Microsoft has introduced a new version of Razor with ASP.NET Core 2. Tom Fischer explains why it’s worth taking another look at this methodology, especially those who discounted it before. He walks you through creating a simple application, highlighting the benefits or Razor....(more)

Extending the Unity Editor with C# - The next step in learning how to create games in C# using Unity is to add extensions to get custom functionality. Lance Talbert shows you how to create an extension that changes the properties of many objects at once....(more)

Sharing is Caring: Using Memory Mapped Files in .NET - Sharing and reusing large objects between components and services can save time and computing resources. Tom Fischer explains how to take advantage of the Memory Mapped Files feature of .NET to help boost performance....(more)