The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I am somewhat surprised to see this story on a lack of a backup. Apparently the NYPD has a system where they track property and evidence. It's a big system, one that cost $25.5 million. This came up as a part of an investigation into cash forfeitures, when the department's lawyer said that requests for information couldn't be provided because the system has backup. At least, that's the first part fo the story.

This gets confusing because the system isn't designed to provide an audit report from the database, and apparently they can't query the database. It seems there are replicated backups to other systems, but the entire thing is live, so there's a danger that any misuse could disrupt the system and crash everything. The lawyers are even claiming web scraping might cause issues.

In a followup article, the vendor's description of the system mentions a DB2 database, but the NYPD lawyers have said the system isn't based on an DB2 database. It's possible the database was replaced, but stil, there has to be some database, right? Relational, NoSQL, flat files. There's some storage mechanism.

Ultimately, this seems like a legal trick, not based on any technical understandings. Webscraping would be similar to any other querying of the system. Accessing the underlying database shouldn't be hard, and certainly a backup must be maintained somewhere. If those aren't possible, then clearly there are multiple nodes. Take one offline and query that node. Most of us that work with applications know that the system isn't this fragile. While there may not be offsite tape or disk backups, the whole story seems fishy.

Most of us that work with databases know what backups are critical. In fact, I'd say that the most important thing we do is ensure there are backups avaliable in the event that we need to restore. Systems are fragile, and any particular piece can crash. Hardware fails and needs to be replaced. Power fluctuations or temperature changes can cause corruption. We always need backups.

At some point I'm sure this will be shown to be a bit of a scam and thre will be some backup taken of the system. This is regardless of whether there have been any backups taken before this point. It is possible that the IT staff or the maintenance vendor isn't doing their job, but having a lawyer stall or hide this fact might work for awhile.

Most of us won't have those circumstances. We might be able to lie about not taking backups, or losing some and be fine, but we might also be dismissed for cause. Don't put yourself in a bad situation. Make backups, make sure you can restore them, and make sure that system runs often. Let someone else decide what to tell clients, customers or judges if there are issues, but make sure you've performed your job as a data professional.

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.

Virtualization and Containers

Overcoming the Challenges of Microservices and Docker Containerisation - Since its introduction in 2013, Docker and containerization has grown in importance as a technology. This is primarily because of the growth of the popularity of agile methodology and microservices architecture. There are several examples in the industries ......(more)

T-SQL

SQL SERVER – Puzzle – Adding Two Dates Resulting Strange Results - This puzzle, I have discovered accidentally. In one of the recent project, I had to display two dates next to each other. Now, when I was doing this, I accidentally did a mistake while I was typing that out. Indeed, this puzzle about strange results ......(more)

Do Variables Exist? - I know, I know I swore I’d never talk about variables again. Then I ran into some funny business. Maybe it was quite as funny as the Temp Table Ghosts, but hey. What’s as funny as temp tables? It’s in your heaaaaaaaad If I run this, what do you think ......(more)

Updateable CTEs - CTEs are cool things. You can essentially create one or more in-line view(s) within your query. One thing that isn’t overly well known is that you can actually update the data within the CTE. No, I don’t mean using using the UPDATE statement with a CTE ......(more)

The Lighter Side

Volkswagen to take on Pikes Peak with a purpose-built electric racer - Enlarge / This is the first image of Volkswagen's purpose-built Pikes Peak electric hill climb car. (credit: Volkswagen) The annual Pikes Peak International Hill Climb is one of the oldest races in the world, having celebrated its 100th anniversary ......(more)

Use a second laptop as an extended monitor with Windows 10 wireless displays - James Clarke from the Windows team rolled into a meeting today with two Surfaces...but one had no keyboard. Then, without any ceremony, he proceeded to do this: Now, I consider myself a bit of a Windows Productivity Tips Gourmand, and while I was aware ......(more)

Testing Software

Where do you install tSQLt? - The question of where to install tSQLt is probably the most common question I get when I talk about unit testing T-SQL, so much so that I thought that it would be a good topic for a blog. I mention tSQLt in the title, but this covers all unit test code ......(more)

Where do you install tSQLt? - The question of where to install tSQLt is probably the most common question I get when I talk about unit testing T-SQL, so much so that I thought that it would be a good topic for a blog. I mention tSQLt in the title, but this covers all unit test code ......(more)

Tech News

One of the original coding schools must pay $375k over employment claims - Enlarge (credit: Flatiron School) In recent years, dozens of coding schools and boot camps have opened up, promising that they can teach students enough software programming that they can get a high-paying job in just a few months. Now one of the most ......(more)

How Blockchain Could Give Us a Smarter Energy Grid - Energy experts believe that blockchain technology can solve a maze of red tape and data management problems. ...(more)

SQL Server Security and Auditing

SQL Server Security Audits – A Confession - When clients need help, bugs require patching and production deadlines loom large, it’s easy to think that cutting a few corners is the “right thing” to do. I’ve done it, and I’m willing to bet that many of you have as well. Doing so puts the security ......(more)

SQL Server Security

SQL Server 2016 Express SP1 – Always Encrypted - This post is provided by Senior App Dev Manager, Chris Tjoumas who provides an overview of Always Encrypted with SQL Server 2016. Introduction In this post, I want to focus on the Always Encrypted security feature of SQL Server 2016 SP1, specifically ......(more)

Permissions required for developing with Temporal Tables - Temporal tables are one of those new (2016+) cool features that recently came across my desk. Basically, a temporal table is a combination of auditing columns (createdate) and a history table. Quick example using code from BOL. CREATE TABLE Department ......(more)

SQL Server on Linux

SQL Server on Linux – External Memory Pressure - In this blog post we’re going to explore how SQL Server on Linux responds to external memory pressure. On Windows based SQL Server systems we’ve become accustomed to the OS signaling to SQL Server that there’s a memory shortage. When signaled, SQL Server ......(more)

How to safeguard SQL Server on Linux from OOM-Killer - On a Windows based server, when all the available memory including the page file is consumed, the server's performance becomes sluggish and out of memory errors are logged to the event logs. On Linux systems, the behavior is slightly different. When ......(more)

How to safeguard SQL Server on Linux from OOM-Killer - On a Windows based server, when all the available memory including the page file is consumed, the server's performance becomes sluggish and out of memory errors are logged to the event logs. On Linux systems, the behavior is slightly different. When ......(more)

SQL Server News

Cumulative Update #8 for SQL Server 2014 SP2 - The 8th cumulative update release for SQL Server 2014 SP2 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)

Spatial Data

Simple SQL: Handling Location Datatypes - Where am I? One of the big problems in data processing is in defining locations precisely. We need, for example to know where people live and where organizations operate. You might want to send them mail or packages; it could be that you need some idea ......(more)

Security news and thoughts

Equifax, TransUnion Hit by Malicious JavaScript Security Risk - Tracking code for website performance data on an Equifax site allegedly had been injected with code that downloaded a fake Flash downloader onto user's systems. ...(more)

Security Flaws in Children's Smart Watches - The Norwegian Security Council has published a report detailing a series of security and privacy flaws in smart watches marketed to children. Press release. News article. This is the same group that found all those security and privacy vulnerabilities ......(more)

Questions about the Massive South African "Master Deeds" Data Breach Answered - Presently sponsored by: Build your own mock malware and test your stack. Stackhackr will tell you if your company is vulnerable. Built by Barkly.This week, I started looking into a large database backup file which turned out to contain the personal data ......(more)

Save Yourself from Devastating KRACK Wi-Fi Hack Attack - A serious security weakness discovered in the WPA2 wireless networking protocol likely affects every device that supports Wi-Fi, according to one of the researchers who discovered the vulnerability. The weakness could enable a so-called "KRACK" (Key ......(more)

Microsoft never disclosed 2013 hack of secret vulnerability database - Enlarge / Microsoft in Dublin, Ireland. (credit: Red Agenda / Flickr) Hackers broke into Microsoft's secret, internal bug-tracking database and stole information related to vulnerabilities that were exploited in later attacks. But the software developer ......(more)

Multiple WiFi Encryption Vulnerabilities Disclosed, Affecting Almost Everything - Today a collection of severe security vulnerabilities in the WPA2 encryption protocol for Wi-Fi are being disclosed, along with a proof of concept exploit. The weaknesses center around the process used for negotiating the encryption keys used by the ......(more)

Product Upgrades and Releases

Let Her Finish: Voices from the Data Platform - This year I had the pleasure of contributing a chapter to a book along with some very special and talented people. That book has now been released and is available on Amazon! Both a digital and print version are available. My chapter is about data viz ......(more)

Product Reviews and Articles

SQL Data Generator–Getting a value based on another column - This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today. One of the things that people often want ......(more)

Quick SQL Prompt Updates in a Pattern - I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try. We had a customer post a question today on ......(more)

PowerShell

Automating Everything with PowerShell – Getting Started #tsql2sday - I try to automate everything I can with PowerShell. Whether we’re talking SQL Server, WSUS, Active Directory, or any other product with active support in PowerShell, I try to write scripts to do everything. I believe in the old proviso that good engineers ......(more)

PowerPivot/PowerQuery/PowerBI

Searching a Field or Table in Power BI Visualization: Power BI Helper - Last week, we announced the very first version of Power BI Helper. Power BI Helper is a tool that helps clean up a Power BI solution. This week we have an updated version with a new interesting feature: Searching for a Field or Table in all visualizations ......(more)

SSRS APIs, M Queries, Power BI Desktop and more - Exploring The New SSRS 2017 API In Power BI (@technitrain) Bulk-extracting Power Query M-code from multiple pbix files in Power BI (@TheBIccountant) Automatic installs of Power BI Desktop (@CurbalEN) How administrators can manage the Power BI Desktop ......(more)

Filtering and comparing different time periods with Power BI - This article introduces a technique to filter and productively compare two time periods with Power BI. Time intelligence calculations are among the most required functionalities in any data model. Year-to-date, same period last year, comparison of different ......(more)

Power BI Custom Visuals Class (Module 73 – Heat Streams) - In this module you will learn how to use the Heat Streams Custom Visual. The Heat Streams visual allows you to visualize and compare a categorical value over time. Module 73 – Heat Streams Downloads Power BI Custom Visual – Heat Streams Dataset – ......(more)

Exploring The New SSRS 2017 API In Power BI - One of the new features in Reporting Services 2017 is the new REST API. The announcement is here: https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/10/02/sql-server-2017-reporting-services-now-generally-available/ And the online documentation for the ......(more)

Performance Tuning SQL Server

Locked Pages, Working Set, and Private Bytes - One of the great things about writing presentations is that it spurs you to “clean up” your definitions. When it comes to writing a slide about something, I ask myself, “Do I really know what that is?” I check my assumptions, and clarify how I think ......(more)

SQL SERVER – How to Find Stored Procedure Execution Count and Average Elapsed Time? - Every single time when I go for Comprehensive Database Performance Health Check, I get many different questions from the users. However, there are some questions which are very common and I have started to build a list of the such questions so I can ......(more)

Bug with STATISTICS TIME? - I love the STATISTICS IO and STATISTICS TIME commands. They are such a powerful and easy way to be able to measure improvements when performance tuning (Measuring SQL Query Performance). You have to be a little bit wary though, there are a few places ......(more)

Linked Servers and Stats - Linked Servers A linked server is a fabulous feature in SQL Server to help accomplish various data tasks between local and remote servers. There is a time and a place for the use of linked servers. Unfortunately, some are of the belief that a linked ......(more)

Performance implications of using multi-Statement TVFs with optional parameters - Authored by Arvind Shyamsundar (Microsoft) Credits: Prasad Wagle, Srinivasa Babu Valluri, Arun Jayapal, Ranga Bondada, Anand Joseph (members of the Sayint by Zen3 team) Reviewers: Rajesh Setlem, Joe Sack, Dimitri Furman, Denzil Ribeiro (Microsoft) This ......(more)

Microsoft News

Windows Server Servicing Model Changes - Microsoft has announced some changes to the release schedule and servicing model for Windows Server. The new Semi-Annual Channel is a twice-per-year feature update release with an 18 month servicing timeline (meaning that Mainstream support ends 18 months ......(more)

Hardware News

Samsung T5: Small, Light SSD Carries 1TB in Your Pocket - With most movies taking up anywhere from 4GB to 8GB of storage on average, do the math; you can place a lot of movies on this little device. ...(more)

The perils of RAID - Recently, I was asked to assist an organization with getting their data back for a SQL Server that had experienced physical hard drive failure. I asked a lot of questions about the configuration of the data layer, and it came to light that it was a fairly ......(more)

Intel Announces The Nervana Neural Network Processor - Intel announced the Nervana Neural Network Processor, a custom ASIC that should compete toe-to-toe against Google's TPU and Nvidia's latest GPU architectures with machine learning enhancements. ...(more)

Razer’s Blade Stealth 13.3” Laptop Updated With Quad-Core Intel Core i7-8550U CPU - Razer this week has announced that the company is upgrading its 13.3” Blade Stealth laptop with Intel’s new quad-core Core i7-8550U microprocessor, along with faster LPDDR3 memory. This marks the latest of several laptop vendors to capitalize on the ......(more)

Microsoft Surface Book 2: Specs, pricing, availability - Microsoft's new MacBook Pro competitor will start shipping new 13.5-inch and 15-inch Surface Book 2 high-performance laptops as of mid-November. Here's what to expect. ...(more)

WD May Have Given HDDs New Life for Big-Data Storage - WD unveiled new storageware that ostensibly enables ultra-high capacity hard disk drives (HDDs) to meet the future high-scale demands of big data. ...(more)

G.Skill Announces 32GB DDR4-4266MHz Trident Z RGB Memory Kits - Following the recent launch of its DDR4-4600MHz kit for Intel’s X299 HEDT platform, and its DDR4-4800 and DDR4-4333 lines of memory, G.Skill is launching yet another memory kit. ...(more)

Samsung's Next Gen NVMe SSDs Appear Online - The OEM version of Samsung's upcoming flagship NVMe SSD, 980, have appeared online. The PM981 NVMe SSD was designed for system builders using the same components as the retail models. The initial pricing looks good, but we don't expect high availability. ...(more)

Silicon Power Launches Bolt B80 External SSD: 500 MB/s, IP68, USB-C Interface - Silicon Power has announced its new lineup of stylish external SSDs. The SP Bolt B80 family of drives comes in a rugged UFO-shaped chassis, which is IP68 dust and waterproof. When it comes to performance, the Bolt B80 resembles other external SSDs that ......(more)

ETL/SSIS/ELT

Lift and shift your SSIS packages to run on Azure with Azure Data Factory v2 - So far, you would need to create ADF pipelines, activities etc. to implement ETL tasks on Azure. But If you are SQL Server Integration Services (SSIS) fun like me and want to use SSIS packages on Azure, you would need to run your SQL Server Integration ......(more)

DevOps and Continuous Delivery (CI/CD)

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts - Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on ......(more)

Data Warehousing

Rethinking data marts in the cloud - Become more agile with business intelligence and data analytics.Many of us are all too familiar with the traditional way enterprises operate when it comes to on-premises data warehousing and data marts: the enterprise data warehouse (EDW) is often the ......(more)

Data Science

Unintentional Data - Eric Hollingsworth describes data science as the cost of collecting data approaches zero: Thankfully not only have modern data analysis tools made data collection cheap and easy, they have made the process of exploratory data analysis cheaper and easier ......(more)

Data Privacy

Supreme Court Will Hear U.S.-Microsoft Email Battle - The Supreme Court agreed Monday to take on a major dispute over the government's authority to force American technology companies to hand over emails and other digital information sought in criminal probes but stored outside the U.S. The justices intervened ......(more)

Supreme Court to decide if US has right to data on world’s servers - Enlarge / Front row from left, US Supreme Court Associate Justice Ruth Bader Ginsburg, Associate Justice Anthony M. Kennedy, Chief Justice John G. Roberts, Associate Justice Clarence Thomas, and Associate Justice Stephen Breyer, back row from left, Associate ......(more)

Computing in the Cloud (Azure, Google , AWS)

Questions About Amazon Data Migration Service (AWS DMS) That You Were Too Shy to Ask - What is AWS DMS (database migration service)? I am planning to use DMS to migrate my data to the cloud. Is there anything in particular that I need to be concerned about? So I’m told that I need to be concerned with the optimum size of the Replication ......(more)

Career Growth

Scaling Mentorship - You may have had a mentor in the past. Often these are more senior/elder people who are further along in their career. The presumption usually is that if they are "ahead" of you that they likely have something profound to offer you in the areas of advice ......(more)

Applications are Open for our 2018 Scholarship Program - At this time of year, with the PASS Summit and SQL Intersections and Black Friday rapidly approaching, it’s so easy to get caught up in the rush of all the fun. Many of us are lucky enough to work at for-profit companies that pay well, send their employees ......(more)

Big Data

Microsoft Ignite Big Data Presentations - There were so many good presentations at Microsoft Ignite, all of which can be viewed on-demand. I wanted to list the big data related presentations that I found the most useful. It’s a lot of stuff to watch and with our busy schedules can be quite ......(more)

Backup and Recovery

Native database backup in Azure SQL Managed Instance - Azure SQL Managed Instance is a new fully managed PaaS offering in Azure cloud that will be publicly available in the near future. It will expose entire SQL Server Instance, support almost all features available in SQL Server 2017 and combine this with ......(more)

Judge shocked to learn NYPD’s evidence database has no backup - (credit: See-ming Lee) As part of an ongoing legal battle to get the New York City Police Department to track money police have grabbed in cash forfeitures, an attorney for the city told a Manhattan judge on October 17 that part of the reason the NYPD ......(more)

Configure Azure Storage for SQL Database backups - SQL Server 2012+ and Azure SQL Managed Instance support native BACKUP commands that can backup a database to Azure Blob Storage URL. Setting-up Azure Storage account might not be so easy as you think because there are some constraints. You can learn ......(more)

Using Backup-DbaDatabase for a Quick Backup - I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items. One ......(more)

Azure SQL Database

Connecting to Azure SQL Database Primary Endpoint – Failover Groups - When you have setup a Failover Group in Azure for your SQL Databases connecting to the R/W (Read / Write) endpoint via SSMS (SQL Server Management Studio) is pretty simple, if you remember one little thing, which will be the discussion point for this ......(more)

Azure SQL Data Warehouse and Data Lake

Simple database copies with the Azure Data Lake Database Export Wizard - Azure Data Lake Tools for Visual Studio now provides the ability to export all or part of a database associated with an ADL Analytics (ADLA) account into a database on a local machine. This capability is very useful when creating local environment for ......(more)

Analysis Services / BI on the MS Stack

DirectQuery in Analysis Services: Best practices, performance, use cases - DirectQuery is a feature of Analysis Services that transforms a Tabular model in a semantic layer on top of a relational database, transforming any MDX or DAX query in a real-time request to the underlying relational engine using the SQL language. This ......(more)

AI/Machine Learning/Cognitive Services

Using SQL Service Broker for asynchronous external script (R / Python) execution in OLTP systems - Authored by Arvind Shyamsundar (Microsoft) Credits: Prasad Wagle, Srinivasa Babu Valluri, Arun Jayapal, Ranga Bondada, Anand Joseph (members of the Sayint by Zen3 team) Reviewers: Nellie Gustafsson, Umachandar Jayachandran, Dimitri Furman (Microsoft) This ......(more)

Azure ML workbench- Installation-Part 1 - In Microsoft ignite 2017, Azure ML team announce new on-premises tools for doing machine learning. this tools much more comprehensive as it provides...(more)

You Could Become an AI Master Before You Know It. Here’s How. - Automating machine learning will make the technology more accessible to non–AI experts. ...(more)

Performing in-database predictions in SQL Server 2016 & 2017 - Starting from SQL Server 2016 Microsoft put a lot of effort into integrating various languages used in machine learning and statistical analysis inside their database engine. In SQL Server 2016 Microsoft introduced the integration of the R language and ......(more)

Microsoft, AWS Share Gluon Deep Learning Tool With AI Developers - Although rivals in the cloud, the technology giants find common ground in helping developers dive into the world of deep learning applications. ...(more)

1 Million predictions/sec with Machine Learning Server web service - Microsoft Machine Learning Server Operationalization allows users to publish their R/Python based models and scripts as "Web-Services" and consume them from a variety of client applications in a scalable, fast, secure and reliable way. In this blog, ......(more)

Administration of SQL Server

Implementing Buffer Pool Extension - Buffer Pool Extension was released in SQL 2014 so it's not new. It is also not advertised very much, but that's not for lack of usefulness. RAM is far faster than SSD and certainly arm based disks, but most databases are too big to cache into RAM. ......(more)

Query Store Best Practices for Mission Critical or Busy SQL Server - Query Store feature helping lot of SQL Admin in isolating top resource consuming queries quickly and also troubleshooting plan choice regressed scenario which is difficult to isolate or time consuming process. Now with powerful report and using few clicks ......(more)

Are your disks formatted with UseLargeFRS? - PureStorage has a pretty cool post that mentions the importance of formatting SQL Server disks with a 64KB clusters and the /L flag (also known as the UseLargeFRS switch on PowerShell's Format-Volume cmdlet). Why UseLargeFRS? It's to help avoid DBCC ......(more)

SQLskills SQL101: How can corruptions disappear? - As Kimberly blogged about earlier this year, 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 ......(more)

SQL SERVER – Configure Stored Procedure to Run at Server Startup – Simple Tutorial - One of the most popular questions I received during my SQL Server Performance Tuning Practical Workshop is how users can run sets of procedure when their SQL Server starts automatically. Today, let us see a simple tutorial to run stored procedure at ......(more)

Query Data Store Forced Plan behavior on AlwaysOn Readable Secondary - We are getting following questions repeatedly from many customers so thought of writing a quick blog to explain the behavior. Question: For example if you have Query Data Store (QDS) enabled for user database participating in Always On Availability Groups ......(more)

SQL Server: What will REPAIR do? - So I had a corruption issue and I was thinking about running repair but I wanted to know what would potentially get deleted. I was faced with the following message. Table error: Object ID 1189579276, index ID 1, partition ID 72057594046578688, alloc ......(more)

Database Fundamentals #14: Modifying Data Through SSMS - I’ve said it before, but I feel I should repeat myself. Using the SSMS GUI for data entry and data manipulation is not the preferred mechanism. T-SQL is the right way to manipulate the data in your database. For purposes of completion though, I will ......(more)

.NET Related Articles

Basics Of Elasticsearch In .NET - Ivan Cesar gives us a brief tutorial of the Elasticsearch .NET API: To be able to search something, we must store some data into ES. The term used is “indexing.” The term “mapping” is used for mapping our data in the database to objects which will be ......(more)