The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

If you hadn't heard about it, VFEMail may be dead. At least, that's what the founder was thinking in this article. A malicious hacking incident took place last week, and though they're back up and running, who knows if customers will stick by them, or maybe sue them out of existence. I wouldn't be surprised as a large number of their infrastructure servers were wiped out by reformatting servers. These included mail servers, backup servers, and SQL Servers.

That's quite an attack, and whether this was directed at the company or some individuals, a large number of people might have lost their mailboxes and previously stored mail that was in IMAP storage. This is the type of issue that is most likely an annoyance for individuals, but it would be potentially catastrophic for businesses. Imagine your small business hosted with them and all your mailboxes were lost with customer communications and who knows what else. Perhaps you could recover data or keep the business going, but it might be an issue.

Could this happen with a cloud provider like Azure O365, Google Apps or AWS? Possibly, and while I'm sure they have backups, I'm not sure how reliable those might be for the average individual or small business. This makes me worry slightly as I depend on GMail and wouldn't even try to backup to few 100GBs of mail I have. I'm not even sure how to do it, though I don't really keep anything in there that's really important. In any case, I'd suspect that connecting and somehow wiping out Gmail servers, along with backups, would be very difficult.

This does make me think about a few customers I know that use online storage for backups. They assume that they will always have either a primary server or the online backup server/share/bucket/container and can download data. The problem is that online systems that connect to the primary can be accessed. If an attacker were to access one, they potentially could access the second.

The world seems to be moving towards more online storage, or in the case of cloud vendors, a reliance on snapshots. That might be good enough for cloud vendors, but it's certainly not for any on-premise system. It's likely that an attacker, possibly with insider help, would wipe out backups first, then primary systems. I'd always want some sort of disconnected offline backup of data, especially database servers. I have seen Murphy's Law strike two systems at once, so an air gap between copies of data just feels prudent.

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

Don’t just think DevOps. Think Compliant Database DevOps! - How can the promise of releasing changes to the database faster and easier be balanced with the need to keep data safe and remain compliant with legislation? Join this webinar to discover how the answer lies in in going one step further than database DevOps and thinking about Compliant Database DevOps....(more)

The importance of monitoring your Azure SQL Database - Monitoring Azure SQL Database is still a necessary part of understanding how your system is behaving and ensures that you have the information needed to make necessary decisions about your databases in a timely and accurate manner. ...(more)

The 2019 State of Database DevOps results, live with Donovan Brown! - Donovan Brown, Principal DevOps Manager at Microsoft, joins Steve Jones, Microsoft Data Platform MVP to discuss the latest in all things Database DevOps. They offer a closer look at the key findings in the 2019 State of Database DevOps Report, and investigate the growing importance of the database in successful DevOps and IT performance....(more)

Virtualization and Containers

Configure Kubernetes with minikube - We have discussed about Docker Swarm to know how to use Docker as a Load balance. Today we will discuss about Kubernetes – Wiki: “Kubernetes is an open-source container orchestration system for automating application deployment, scaling, and management. ...(more)

Vendors/3rd Party Products

The configuration of your SQL Server estate - Redgate would like to know more about the role that configuration plays in the health of your SQL Server estate. Fill out their short online survey and be in with a chance to win a $100 gift card....(more)

T-SQL

Are All Delimited-String Parsers Created Equal? - Overview In short, no. However, for many years there was no alternative to the T-SQL option, and even when there were other viable options, it was unclear whether it was worth changing existing code to take advantage of the new technologies. With the ......(more)

How to Calculate a Cumulative Percentage in SQL - A fun report to write is to calculate a cumulative percentage. For example, when querying the Sakila database, we might want to calculate the percentage of our total revenue at any given date. The result might look like this: Notice the beautifully ......(more)

Closest Match, Part 3 - In Closest Match, Part 1, I covered a T-SQL challenge that was provided to me by Karen Ly, a Jr. Fixed Income Analyst at RBC. The challenge involved two tables — T1 and T2, each with a key column (keycol), a value (val), and some other columns (represented ......(more)

The Performance of Window Aggregates Revisited with SQL Server 2019 - In 2005 and 2012, Microsoft introduced a number of windowing functions in SQL Server, like my favourite function LAG. These functions perform well, but, in my opinion, the main benefit is making complicated queries easier to write. I’ve been fascinated ......(more)

Parsing HTML in SQL Server - Recently I was asked how to parse text out of an HTML fragment stored in SQL Server. Over the next few seconds my brain processed the following ideas: SQL Server is not meant for parsing HTML. Parse the data with something else.T-SQL does have functions ......(more)

Efficiently Update Data from OLTP to OLAP - OLTP to OLAP: Acronym Time! Online Transaction Processing, or OLTP, serves data to clients for line-of-business applications, enabling core business functionality. Online Analytical Processing, or OLAP, consists of analyzing OLTP data with the intent ......(more)

The Value of Calendar Tables, Part 2: Simplify Queries - In the last post, I showed how to create a calendar table. In this post, we’ll look at a few places where calendar tables can simplify your queries significantly. Finding Valid Dates First up, let’s do a little bit of SQL Saturday Raleigh prep (notice ......(more)

Using Temporary Procedures - I’ve often read in forums how people have special utility databases with all their stored procedures and functions for working on the databases on the server. It is great because you don’t want your utilities intruding into the actual databases that ......(more)

Partitioning 1: Non-partition aligned queries - Here’s my take on partitioning. I’ll be focusing on getting queries to perform on partitioned tables, and not on partition maintenance, which can be its own headache. This is the first part in a series I’m planning to write, so this post may not answer ......(more)

Tools

SQL Server Bulk Insert – Part 1 - According to Wikipedia, ”A Bulk insert is a process or method provided by a database management system to load multiple rows of data into a database table.” If we adjust this explanation in accordance with the BULK INSERT statement, bulk insert allows ......(more)

6 SSMS features that deserve some attention - SSMS new versions are full of very interesting features. With SQL Server 2019 arriving, it’s normal that our focus is captured by the new features in the database engine, however, SSMS has also a lot to offer. Let’s highlight some very interesting features ......(more)

Tech News

Pulling the Plug on Legacy Technology: What You Need to Know - Click to learn more about author Leon Adato. Being careful and steady can carry IT professionals through a number of rough spots, but eventually, even the best technology gets old and jammed up with digital duct tape—patches, workarounds, and inefficiencies. ...(more)

SQL Server Security

In-depth Exploration of Row Level Security - Introduction Organizations are becoming more and more concerned about how to reduce the cost of licensing database solutions using consolidation. Some consolidation can be achieved in SQL Server simply by taking advantage of the existing one-to-many ......(more)

“SSPI handshake failed” \ “The login is from an untrusted domain” errors - I’ve recently encountered an issue that was difficult to resolve and I didn’t find the particular cause that was troubling us documented elsewhere on the web so thought I’d record it here. The issue was with a service account connecting to SQL Server ......(more)

Lesson Learned #72: Is my database still encrypted (TDE) - A quick lesson learned from the field when dealing with TDE (Transparent Data Encryption). After running something like ALTER DATABASE [AdventureWorks] SET ENCRYPTION OFF One quick spot where people usually will look for information if a database is ......(more)

SQL Server News

SQL Server 2019 New Features - Microsoft’s leading database is an essential tool, with in-cloud and on-premises versions providing incredible storage and analytic tools. It’s additionally turned into an essential application for data scientists, giving a structure to building and ......(more)

Software Development

What learning and development means at Redgate - At Redgate, encouraging personal development in our teams is fundamental to building amazing products. As well as developing new skills for employees to apply to their current work, personal development has a huge impact on job satisfaction. Drive by ......(more)

Security news and thoughts

USB Cable with Embedded Wi-Fi Controller - It's only a prototype, but this USB cable has an embedded Wi-Fi controller. Whoever controls that Wi-Fi connection can remotely execute commands on the attached computer. ...(more)

“Catastrophic” hack on email provider destroys almost two decades of data - VFEmail says data for virtually all US users is gone for good....(more)

R Language

Multilevel Modelling in R: Analysing Vendor Data - CategoriesRegression ModelsTagsLinear Mixed ModelLinear RegressionR ProgrammingOne of the main limitations of regression analysis is when one needs to examine changes in data across several categories. This problem can be resolved by using a multilevel ......(more)

Product Upgrades and Releases

The February release of Azure Data Studio is now available - Were excited to announce the February 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 youre currently using the preview version, ......(more)

Microsoft to launch Visual Studio 2019 on April 2 - Microsoft is planning a day-long launch event for Visual Studio 2019, the latest release of its developer platform, on April 2. ...(more)

H2O.ai Advances Leading Data Science and Machine Learning Platforms - A new press release reports, “H2O.ai, the open source leader in AI and ML, today announced new and innovative capabilities for its data science and machine learning platforms, H2O, AutoML and H2O Driverless AI, to address the critical scalability and ......(more)

Talend Introduces Fast, Frictionless Data Loading for Microsoft Azure - A new press release reports, “Talend, a global leader in cloud data integration software, today announced that Stitch Data Loader now supports Microsoft Azure’s SQL Data Warehouse, providing one of the easiest ways for users to get data into a fast, ......(more)

Product Reviews and Articles

[Video] How to Capture Baselines with sp_BlitzFirst - Everybody tells you to capture baselines – but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources? All ......(more)

Adding Objects to our SCA Project - In my last post, I started out with a new SCA project, getting a connection to my database up and running. Now, let’s start the new project by adding some objects. Adding Objects I’m going to start with a couple objects to make this simple, but we’ll ......(more)

How to create and refresh development and test databases automatically, using SQL Clone and SQL Toolbelt - In order to be able to deliver database changes more quickly, there are several tasks that must be automated. It can be a daunting job to ensure that the whole team has the latest database build, when there is a proliferation of copies, and the database ......(more)

New Free Videos on SQL Prompt, SQL Compare, and SQL Doc with Redgate’s SQL Change Automation - I’m excited to have just clicked ‘publish’ on four new videos in the brand new Evangelist Tutorials playlist on Redgate’s YouTube channel. These videos step through setting up and working with a project in one of my favorite Redgate tools: SQL Change ......(more)

Course Reviews: Writing Functions in R - This is a review of Hadley and Charlotte Wickham’s DataCamp course entitled Writing Functions in R. Wickham and Wickham (borther and sister) do a great job of introducing some of the concepts of functional programming in R. The first chapter is a refresher ......(more)

Using the SQL Monitor Estate Pages - As a DBA responsible for the availability, performance, security and overall health of a set of SQL Servers, you have many plates to keep spinning. You’ll need to respond quickly to avert or resolve query performance and resource contention problems. ...(more)

A Database Developer’s Guide to SQL Clone - With a database, many people can view the data at the same time. If someone makes a change and commits it, then the change is visible not only for that connection, but all the others as well. Everyone sees the effects of that change; that is what databases ......(more)

Pro SQL Server Linux–Moving to Linux - As part of my learning goals for 2018, I wanted to work through various books. This is part of my series on Pro SQL Server on Linux from Bob Ward. It’s not until Chapter 10 that we get back to Linux specifics. Most of the chapters in the middle are general ......(more)

PowerPivot/PowerQuery/PowerBI

Violin Plots in Power BI - In case you aren’t familiar, I would like to introduce you to the violin plot. A violin plot is a nifty chart that shows both distribution and density of data. It’s essentially a box plot with a density plot on each side. Box plots are a common way ......(more)

Disable free signups in the Power BI service - Learn how you can block free signups for the Power BI service. Free signups are also what are referred to as viral signups. This action can affect more than just Power BI, so be careful. The post Disable free signups in the Power BI service appeared ......(more)

Power BI – Fixing Dates from Sharepoint Lists (converting UTC Dates to Local Dates) - Originally posted on: http://coolcat.de/archive/2019/02/01/power-bi-ndash-fixing-dates-from-sharepoint-lists-converting-utc.aspxSo the problem below was in relation to working with dates from a Sharepoint 2013 List, but the same technique should be applicable ......(more)

Splitting Text By Character Transition In Power BI And Excel Power Query - The February 2019 release of Power BI Desktop includes some new functionality not mentioned in the summary blog post, functionality that is already in the latest Excel 365 builds (thanks Ed Hansberry for pointing this out) and will be extremely useful. ...(more)

Power BI Monthly Digest – February 2019 - In this month’s Power BI Digest with my friend Manuel Quintana [Blog | Twitter] and I will again guide you through some of our favorite Power BI updates this month. In our February 2019 edition we highlighted the following features: Improved Filter ......(more)

Linked Entities and Computed Entities; Dataflows in Power BI Part 4 - In previous articles, I explained what is the Dataflow and where to use it, I also explained how to create a dataflow, and what is the common data model. In this article, I’m explaining one of the differences between Dataflow and the Power Query in Power ......(more)

Power BI: Hiding future dates for calculations in DAX - Marco Russo joins Patrick to discuss how to hide future dates for calculations in DAX. Marco walks through Power BI Desktop with some examples that work and don't work. SQLBI Article: Hiding future dates for calculations in DAX The post Power BI: Hiding ......(more)

Power BI Features Reporting - Matt here. The blog article this week was written by Neville de Sousa. Neville shared with me a Power BI report that shows all the Power BI features released since July 2015. Neville will explain the background and his report. Neville: Keeping track ......(more)

Power BI – Fixing Dates from Sharepoint Lists (converting UTC Dates to Local Dates) - Originally posted on: http://ferventcoder.com/archive/2019/02/01/power-bi-ndash-fixing-dates-from-sharepoint-lists-converting-utc.aspxSo the problem below was in relation to working with dates from a Sharepoint 2013 List, but the same technique should ......(more)

Finding Power Query Query Execution Times In Power BI And Excel Using SQL Server Profiler - Working out how long a Power Query M query takes to run – the first step to troubleshooting refresh performance problems in Power BI or Excel – is something I have blogged about several times (see here, here and here for example). However, there are ......(more)

How To Use Power BI Embedded For Your Customers - Recently I had a need to build a Power BI report for a client. This client has a multi-tenant database and their own custom web app. They want to show each client their data without showing any other clients’ data. They also don’t want to require their ......(more)

Power BI Workspaces, Gateways, Export to PDF and more… (February 11, 2019) - Thanks for watching this week's Power BI news roundup! Last weeks roundup: https://guyinacu.be/roundup126a 2 Minute Tuesday: https://guyinacu.be/tenant Patrick's tech video: https://guyinacu.be/streamingdataset Adam's tech video: https://guyinacu.be/adddomain ......(more)

Performance Tuning SQL Server

Query Plan Operators That Hide Work - In A Row? When you’re reading query plans, you can be faced with an overwhelming amount of information, and some of it is only circumstantially helpful. Sometimes when I’m explaining query plans to people, I feel like a mechanic (not a Machanic) who ......(more)

Dynamic Data Masking in the execution plan - I think Dynamic Data Masking is pretty cool. The idea is basically to provide a mask for certain users when they might see protected data. The documentation on this feature is actually pretty deep, it’s worth a look. I just want to show you how you can ......(more)

Microsoft News

Microsoft begins work on its 2020 Windows releases in new preview - Enlarge / Windows is now perpetually under construction. (credit: David Holt) Microsoft has published a new preview release of Windows 10, build 18836, to participants of the "Skip Ahead" group. But it's not quite the preview that they were expecting ......(more)

Microsoft shaking up how Windows feature updates are rolled out—again - Enlarge (credit: Microsoft) Customers using Windows Update for Business will lose some ability to delay the deployment of each new Windows feature release once version 1903 goes live. When Microsoft first started delivering Windows 10 "as a Service" ......(more)

Microsoft patches 0-day vulnerabilities in IE and Exchange - Enlarge (credit: Getty Images | Justin Sullivan) Microsoft’s Patch Tuesday this month had higher-than-usual stakes with fixes for a zero-day Internet Explorer vulnerability under active exploit and an Exchange Server flaw that was disclosed last month ......(more)

Hardware News

Plugable Releases USB-C Enclosure for NVMe SSDs - Plugable released a spring-loaded, tool-free USB-C enclosure for NVMe SSDs to appease those who need quick access to lots of storage on the go. ...(more)

State of the Union: Seagate's HAMR Hard Drives, Dual-Actuator Mach2, and 24 TB HDDs on Track - Seagate this week reiterated that the company is on track to launch two crucially important technologies later this calendar year. Firstly, the company plans to start ramping up its 16 TB hard drives featuring heat-assisted magnetic recording (HAMR) ......(more)

The AMD Ryzen 5 2500X and Ryzen 3 2300X CPU Review - Despite AMDs resurgence to kick it with the high end of mainstream processors, the biggest volume sales occur more in the mid-range where the parts are often competitively priced. In the segment, AMD currently has the Ryzen 5 2600 and the 2400G at retail, ......(more)

HA/DR/Always On/Clustering

SQL Server Script To Check Your Backup RPO Status - I recently wanted a script to tell me that for every database on a given server What levels of backups I have How many files would need to be restored to get to the most recent backup state. The size of all the files I’d need to restore How up to date ......(more)

Performance on readable replicas in Availability Groups - I apologize in advance if I mess up the terminology. I’ve worked with a moderate read workload on a readable replica and I wanted to share some of the things I’ve seen. First, how in sync is your replica? To make an Availability Group replica readable, ......(more)

ETL/SSIS/Azure Data Factory

Azure Content Spotlight – Azure Data Factory - Welcome to another Azure Content Spotlight! These articles are used to highlight items in Azure that could be more visible to the Azure community. This week our content spotlight is about Azure Data Factory. From my experience as a Cloud Architect, moving, ......(more)

Deeper integration and new connector for SSIS in ADF - Expanding our efforts to integrate SQL Server Integration Services (SSIS) deeper into Azure Data Factory (ADF), we have recently improved the configurability of Execute SSIS Package activities in ADF pipelines. With the enhanced “Settings” tab and ......(more)

SSIS Project Connection Manager Naming Error - I am sometimes asked, “Andy, how do I start a technical blog?” I demonstrate one way here – a way that has served me well with search engines. 1. Describe how the error occurred.2. Copy the error message, post it as text. 3. Describe the solution. I ......(more)

Andy Answers: Format a Flat File Name - My Twitter DMs are open @AndyLeonard. Ask me a question about SSIS, ADF, or Biml. I may answer it with a blog post, a video, or both. Today’s Andy Answer is an answer to an SSIS question received via @AndyLeonard direct message: Using SSDT, I have 3 ......(more)

Using Batch Scripts, and SQLCMD to Write Out a Database’s Data. - This article is about using the DOS Batch script facility of the Windows command line, together with SQLCMD to write the contents of each table in a database to the local filesystem. It shows how to use temporary stored procedures to advantage. Just ......(more)

Quick tip: Speeding up deletes from SSIS execution log - If you have SQL Server Integration Services installed on your server, and you left the default configurations a table named sysssislog is created on MSDB database, it contains logging entries for packages executed on that instance.If you are not careful ......(more)

No Pause on the Azure Data Factory - Using only what you need in Azure is a crucial part of optimizing your environment in the cloud. You find that as attractive as Azure is for the masses, making this change to make sure what you do use is optimal will make it downright irresistible. Many ......(more)

PolyBase Revealed: Changing Dates With RegEx - This week’s post has to do with handling dates in files. One of the data sets I use in demos for PolyBase Revealed is an open data set of Raleigh, North Carolina police incidents. This data set is nice because it’s enough rows not to be a trivial example, ......(more)

Start Azure-SSIS, Execute Package, Stop Azure-SSIS - Last August, I had a blast at the Data Platform Summit 2018 in Bengaluru, India. One highlight of the trip was meeting and conversing with the Azure Data Factory SSIS Integration Runtime Product/Program Manager, Sandy Winarko. Sandy and I swapped coder ......(more)

Developing pipelines in Azure Data Factory using Template gallery - (2019-Feb-08) I often see demos of Azure Data Factory (ADF) development techniques with a modest set of activity tasks and not very complex logic for a data transformation process. Knowing more about ADF capability may generate additional interest to ......(more)

Creating custom solution templates in Azure Data Factory - (2019-Feb-11) Azure Data Factory (ADF) provides you with a framework for creating data transformation solutions in the Microsoft cloud environment. Recently introduced Template Gallery for ADF pipelines can speed up this development process and provide ......(more)

DevOps and Continuous Delivery (CI/CD)

PASS SQL Saturday Wellington - Hamish Watson presents "Creating a Database Deployment Pipeline Using DevOps Processes" Pre-Con - PASS SQL Saturday Wellington, NZ - In this all day training session you will learn the importance of getting both your application and database code into version control, how various tools can be used for automated continuous integration processes that will then feed into an automated release & deployment process for your database....(more)

Mean Time to Identify Failure - While managing a team of 40 ETL developers, I wanted to track lots of metrics. Some of the things I wanted to track were technical, like SSIS package execution times. Some metrics were people-centric. Andy’s First Rule of Statistics states: You can ......(more)

A Glimpse into the Future of Modern Data Architectures with DataOps at its Core - Click to learn more about author Itamar Ankorion. Enterprises are building out modern Data Architectures to accommodate new analytic requirements, larger data volumes and take advantage of newer technologies such as cloud, data lakes and data streaming. ...(more)

Database Design, Theory and Development

Understanding Domains and Attributes - Note: This is a rewrite of one section of an older post (page thereof now links here), to bring it into line with McGoveran's formalization, re-interpretation, and extension of Codd's RDM[1]. The rewrite of the other part will be posted next.“I don't ......(more)

Master Data vs. MDM - Click to learn more about video blogger Scott Taylor. The Data Rants video blog series begins with host Scott Taylor “The Data Whisperer.” The series covers some of the most prominent questions in Data Management such as Master Data, the difference ......(more)

Data Warehousing

Data Warehousing Tip #8 – Make it better - In this article I’ll discuss making your data warehouse better. I’ll admit that this is a pretty obvious aspiration, but data warehouses evolve. If they are going to be a success and have some sort of longevity they have to. The focus of this article ......(more)

Three Of The Most Common Data Problems Plaguing Organizations Today - Click to learn more about author Mark Marinelli. Think about your day-to-day life. How much of it revolves around technology? We live in a world of constant connectivity. Data is at the very core of how we go about business: As a consumer, you expect ......(more)

Data Privacy, Complianace, and GDPR

Back to Basics; Masking Address Data - Protecting Personable Identifiable Information is central to the compliance of numerous regulations which your organization may be subject to. In this article Grant Fritchey starts with the basics of obfuscating address data with the data masking technology in SQL Provision....(more)

Protecting Credit Card Data when Testing - Privacy regulations, ranging from GDPR to HIPAA, among many others, put strict compliance requirements on the storage and use of personal and sensitive data. In this article Grant Fritchey explains how the data masking technology in SQL Provision can help you protect credit card data while maintaining characteristics and distribution of the real thing. ...(more)

Conferences, Classes, and Events

Come See Me In Boston On May 10th! - So Where In Boston Are You From? Weymouth? I’ll be presenting for NESQL at the User Group on the 9th, and for a full day of training on May 10th, delivering material from my sold out SQLBits session. This isn’t your typical training session with the ......(more)

A Bit Of A Contest For SQLBits - On Twitter (More unfortunate words were never spoken) I decided to offer a free three hour block of time to whomever named the cadre of under-performing queries we’re going to be looking at as part of my SQLBits precon. This is fair. I have no idea ......(more)

Career Growth

Practical Web Scraping–Getting Started - As part of my learning goals for 2018, I wanted to work through various books. This is part of my work with Python. After going through a few first chapters, I decided to start my February learning with Practice Web Scraping for Data Science, which ......(more)

T-SQL Tuesday #111 – Why I blog - This month’s T-SQL Tuesday is hosted by Andy Leonard at andyleonard.blog . Andy asks us, what is your “Why?” So what is my Why? Well, I want to talk about a few things I do. My current job and why Currently I work primarily as a performance focused ......(more)

What is your why?: T-SQL Tuesday #111 - This month Andy Leonard (b/t) wants to know why we do what we do. Funnily enough, this is something I think about on a fairly regular basis. Like most people, I have a lot of reasons for doing things, but for me, most of them come down to one thing. I ......(more)

Why I Do What I Do #tsql2sday - I didn’t originally want to go back into IT after 15 years in the career field. It was 1999 and I had just left active duty with the US Air Force. My designation was a Communications-Computer Systems Officer but I had served as a hybrid IT support/project ......(more)

What is my why?–T-SQL Tuesday #111 - This is an interesting question from Andy Leonard for T-SQL Tuesday: What Is Your “Why”? He is this month’s host and asks us to talk about our motivation, our passion. The question is: Why do you do what you do? Why I Do What I Do? I have had a great ......(more)

What is your why? – T-SQL Tuesday #111 - Would you believe that it is already the second Tuesday in February? Wow, this month is sure going fast. But being the second Tuesday of any month means just one thing – It’s time for the SQL blogging universe to come out in force and post their blog ......(more)

There’s got to be a better way - For T-SQL Tuesday #111, Andy Leonard asks “What is your why? Why do you do what you do?” Like Andy, I didn’t take a straight route into being a DBA. I actually trained as a teacher, and did a variety of temp jobs – largely in call centres – before I ......(more)

T-SQL Tuesday #111 - Why Do You Do What You Do? - This month's T-SQL Tuesday is hosted by Andy Leonard (blog/@AndyLeonard) and his topic was this:That’s the question this month: Why do you do what you do?For me this was the easiest T-SQL Tuesday I have ever seen. Some of you may consider this a cop-out, ......(more)

T-SQL Tuesday #111 – What is My “Why?” - This month for T-SQL Tuesday we are asked to describe our “Why”. I have a couple of Whys not related to my day job, but we will start with my day job. Since I was six years old, I wanted to be a computer programmer because I thought video games on the ......(more)

Passion, Challenges, and SQL - TSQL Tuesday The second Tuesday of the month comes to us a little early this month. That means it is time again for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes ......(more)

T-SQL Tuesday #111 – What is your why? - For this T-SQL Tuesday, Andy Leonard asks us, “What is your why?”, why do you do what you do? Many of the answers from others are heartfelt and admirable. Mine are not. Most fall under the category of either dumb or dumb luck. Starcraft and Scholastic My ......(more)

T-SQL Tuesday #111: Why, tell me why - Time flies. It feels like the new year has just started, and yet we’re already at the second T-SQL Tuesday of the year. Our host this February is Andy Leonard (b|t), and his assigned topic is, simply: Why? Or rather: What is your why? What motivates ......(more)

Azure SQL Database

How fast can a $5,436/mo Azure SQL DB Hyperscale load data? - A client asked, “How quickly could we spin up a full copy of our database in the new Azure SQL DB Hyperscale?” Their database size wasn’t too far off from the 340GB Stack Overflow database, so I decided to migrate that to Hyperscale to see how the experience ......(more)

How fast can a $21,468/mo Azure SQL DB load data? - In my last post, I explored how fast a $5,436/mo Azure SQL DB Hyperscale could load data. I’d had a client who was curious about spinning up their dev environment up there to see how query plans might look different. Well, as long as I was running this ......(more)

Azure SQL Database and Transaction Log - Checking out the transaction log in Azure SQL Database. If you are curious like me, you will want to know about what your transaction log is doing in the cloud. The following queries have been tested and run okay within … Continue reading ? ...(more)

How to get Azure SQL database size - There are multiple ways to achieve this and there are also some storage types you should be aware There is a good doc about this at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management Understanding the following storage ......(more)

Analysis Services / BI on the MS Stack

How Many Data Gateways Does My Azure BI Architecture Need? - It’s not always obvious when you need a data gateway in Azure, and not all gateways are labeled as such. So I thought I would walk through various applications that act as a data gateway and discuss when, where, and how many are needed. Note: I’m ignoring ......(more)

AI/Machine Learning/Cognitive Services

Machine Learning – Building a Pet Detector in 30 minutes using Tensorflow - Ever wondered what breed that dog or cat is? In this show, you'll see us train, optimize and deploy a deep learning model using Azure Notebooks, Azure Machine Learning Service, and Visual Studio Code using Python. We use transfer learning to retrain ......(more)

Making Machine Learning Datasets Unbiased - Click to learn more about authors Dmitry Pozdnyakov and Olga Ezzheva Machine Learning (ML), a subset of a broader Artificial Intelligence (AI) field, is finding its way into more and more areas of application. From smarter shopping recommendations to ......(more)

Administration of SQL Server

SQL SERVER – Update Table Statistics in Parallel with FULLSCAN - SQL Server Performance Tuning is a much simpler job if you know exactly where to look for and how to tune the configurations. Here is the real world scenario which I encountered during the recent Comprehensive Database Performance Health Check. During ......(more)

Should you run SSAS/SSIS/SSRS on the SQL Server? - When you’re building a new SQL Server, you’re going to see a few intriguing checkboxes during setup. It’s all free, right? Check everything! The services are all free, right? You can just check the boxes for Machine Learning Services, R, Python, Data ......(more)

DBA Myths: Can a differential backup get bigger than the full backup? - The thing about DBA Myths is that they are generally widespread and widely believed. At least I believed this one until I posted What’s a differential Backup? and Randolph West (b/t) pointed out that my belief that differential backups can’t get larger ......(more)

Partitioning 4: Finding partitioning in DMVs and Object Explorer - Happy Friday! Today’s post will be light on the performance side and instead, I’m going to talk about how to take a look at your server’s partitioning. First, partitioning in SQL Server Management Studio Object Explorer, that thing on the left side of ......(more)

SQL SERVER – New Parallel Operation Cannot be Started Due to Too Many Parallel Operations Executing at this Time - One of my clients contacted me via my On Demand (50 Minutes) offering and they were seeing below error message in ERRORLOG – New parallel operation cannot be started due to too many parallel operations executing at this time. Here is the complete message ......(more)

Partitioning 2: Getting and measuring partition elimination - In Partitioning 1, my query searched through all 1-12 partitions. Let’s see if we can reduce that number. Adding the partitioning key to your query Since I created the index in part 1 on the Posts_Partition_Scheme, the index was split up among the partitions. ...(more)

Proposed SQL Server defaults: cost threshold for parallelism - A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ......(more)

Automatic Deletion of Forgotten Transactions in MS SQL Server - Introduction It is often the case when an MS SQL Server transaction is being forgotten by the initiator. The best example would be the following: a script is executed in SSMS which, via the ‘begin tran’ instruction, starts a transaction and an error ......(more)

SQL SERVER – Enabling Older Legacy Cardinality Estimation - Yesterday, I had a very interesting situation, one of our Comprehensive Database Performance Health Check client immediately called me on Skype with his entire server going continuously stopping due to a very toxic poison wait type of Resource Semaphore. ...(more)

SQL SERVER – Cannot Drop the Assembly ‘ISSERVER’, Because it Does not Exist or You do not Have Permission - As they say – There are more ways than one to skin a cat. In this blog we will find another method to fix an error which you might see during SQL upgrades – Cannot drop the assembly ‘ISSERVER’, because it does not exist or you do not have permission. Here ......(more)

When your ‘Best Practices’ are only your ‘Best guess’ with regards to SQL Server configuration - SQL Server configuration plays a vital role in the health of your SQL Estate. Your needs may vary depending on the purpose of the server, or its role within a business, but a healthy configuration ensures availability and performance for your customers. We’ve ......(more)

SQL Server Worker Thread Default Calculation - SQL Server 2017 introduced a small change to SQL Server’s default worker thread calculation, accounting for smaller environments. When running on smaller environments SQL Server reduces the worker target. For an X64 installation, using the sp_configure ......(more)

Query Store and a READ_ONLY Database - What happens in Query Store when the database itself is READ_ONLY? Yeah, I don’t know. Let’s find out. READ_ONLY The only way to find out how this works is to test it. So, let’s write some code: Executing that resulted in a small glitch in the Matrix: ......(more)

Shredding XML Data From Extended Events - Querying the data of an Extended Events session has never been easy. My XEvent sessions typically store event data in a target file, which means using sys.fn_xe_file_target_read_file. To get something of value, you need to shred the event data XML. ......(more)