Database Code Analysis

Database code analysis will reduce the number of 'code smells' that creep into your database builds. It will alert the team to mistakes or omissions, such as missing indexes, that are likely to cause performance problems in production. It will allow the Governance and Operations team visibility into production readiness of the code, warning them of security loopholes and vulnerabilities. William Brewer describes the two technical approaches to database code analysis, static and dynamic, and suggests some tools that can help you get started.… Read more

Working From Home

The idea that one can do nothing useful as a developer unless you are in an open plan office is rather retro. Although we all get that knowing wink when we announce that we are “Working from Home”, the truth is more complicated, I think. Some people are more productive when they work from home, … Read more

Visual Studio 2017 and Swagger: Building and Documenting Web APIs

The OpenAPI ‘Swagger’ Specification defines a protocol that allows applications to discover, and generate documentation of methods, parameters and models of REST APIs, This provides a way for any software to identify the features of a REST API. It does for REST APIs what WSDL (Web Service Description Language) did for web services. Visual Studio now provides it to support proxy creation for REST APIs, as Dennes Torres explains.… Read more

Monitoring Azure Cloud and Hybrid Environments

Cloud-based services and applications must still be monitored just like the on-premise ones. You still need most of your data center activities that ensure that your planning, budgeting, security and service-level obligations are met wherever the data and services are actually hosted. There is much to be said for an integrated approach to providing a unified view of entire application workloads on-premise. hybrid and cloud using the same tools wherever possible. … Read more

SQL Server R Services: The Basics

It is possible to do a great deal with R within SQL Server, but it is best to start by doing analysis in R on numeric data from SQL Server and returning the results to SQL Server. There is great value to be gained even with this basic foundation. Robert Sheldon is on hand to give you a kick start with the first in his series on beginning with R in SQL Server.… Read more

To Fly, To Serve, To Fry Your Servers

So, the story goes that an Ops engineer walked into a data center with the necessary pass, a cheery wave and a ‘good morning’. Shortly afterwards, he made history. At around 8.30AM, British Airway’s entire communications systems went down at the height of the May holiday, forcing them to cancel flights from the UK’s two … Read more

Working with Windows Containers and Docker: Into your Stride

So far, in this series, Nicolas has shown how to get simple container instances up and running with just some basic background information. Now we need to understand the differences between Linux containers, Windows Server containers and Hyper-V containers. We can then define, create and run multi-container Docker applications, and port existing Windows Container VMs to Docker.… Read more

Control the Controller in ASP.NET MVC

Because of its obvious importance in coordinating the MVC application, we tend to be wary of being too adventurous with it, but there are some interesting features that can sometimes be exploited to provide for less common requirements. The controller in ASP.NET MVC core, for example, can be a plain-old C# class (POCO), making it easier to create file-based content on the fly. The DefaultControllerFactory class can be changed, and you can override the GetControllerType method with a custom factory to give you localized routes and URLs. Dino explores some of the interesting internals of ASP.NET MVC controllers… Read more

How to Automatically Create and Refresh Development and Test Databases 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 is big. Phil illustrates a solution by taking a set of Redgate tools to show how they can be used together, via PowerShell, to build a database from object-level source, stock it with data, document it, and then provision any number of test and development servers with the database build, taking care to save any DDL changes to the existing copies of the database.… Read more

Developing Metadata Design Patterns in BIML

You may have already experienced ETL Hell, where you have a large number of similar of SSIS tasks, and a small change, such as an alteration to the network topology, means that they all need to be altered with the correct connection details. Perhaps you should consider creating design patterns for all the standard components of integration tasks in BIML, and generating the SSIS packages from these? Amarendra walks you through the process.… Read more

Query Store and Parameterization Problems

The query store gives us a novel way of identifying those queries that are causing performance problems when they are parameterized by SQL Server for reuse. Although it is relatively simple to ensure that certain troublesome queries avoid the problem, it is laborious to identify these queries. Additionally, Query Store gives us the means to fix the problem for groups of queries by means of plan guides without changing the DDL at all. Dennes Torres explains the details… Read more

The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects

Technical debt is a real problem in database development, where corners have been cut in the rush to keep to dates. The result may work but the problems are in the details: such things as inconsistent naming of objects, or of defining columns; sloppy use of data types, archaic syntax or obsolete system functions. With databases, technical debt is even harder to pay back. Robert Sheldon explains how and why you can get it right first time instead.… Read more

WannaCry Over Spilled Data

The WannaCry ransomware attack has highlighted a serious problem. If there is negligence in your IT strategy, you are increasingly risking the functioning of your organisation, and the privacy of your customers. If you are being careless with data you don’t own, and of which you are legally only the custodian, if you are storing … Read more

SQL Server User-Defined Functions

User-Defined Functions (UDFs) are an essential part of the database developers' armoury. They are extraordinarily versatile, but just because you can even use scalar UDFs in WHERE clauses, computed columns and check constraints doesn't mean that you should. Multi-statement UDFs come at a cost and it is good to understand all the restrictions and potential drawbacks. Phil Factor gives an overview of User-defined functions: their virtues, vices and their syntax.… Read more

Simple SQL: Attribute Splitting

If the design of a relational database is wrong, no amount of clever DML SQL will make it work well. Dr. Codd’s Information Principle is that you have, inside the entity tables, the columns that model the attributes of that entity. The columns contain scalar values. Tables that model relationships can have attributes, but they must have references to entities in the schema. You split those attributes at your peril. Joe Celko explains the basics.… Read more

Getting What You Need From Azure Storage Disks

If you need persistent data disks for Azure IaaS VMs that are supported on both Windows and Linux then you will be interested in Azure Storage Disks. These can increase the storage capacity of your VMs by up to a terabyte per disk, and they not only allow several availability options, but also offer a range of performance in terms of I/O throughput and latency. With right configuration, you can create as much of the right sort of storage as you need.… Read more

High Performance PowerShell with LINQ

PowerShell is a scripting language, and like all scripting languages it struggles to perform well with rapid iterative processes such as aggregation. It isn't well-known that PowerShell can use LINQ for many of those tasks which would otherwise use iteration, though somewhat awkwardly. However, some of the speed improvements you can get are startling. To get you well started, Michael explains every LINQ function , gives you example code in C#, The PowerShell way of getting the result, and finally Powershell's use of LINQ. This article could change the way you use PowerShell.… Read more