Problems displaying this newsletter? View online.
Database Weekly
The Complete Weekly Roundup of SQL Server News
Hand-picked content to sharpen your professional edge
Editorial
 

Over-thinking Database Build Scripts

With SQL Server we tend to build databases, when necessary, from one or more build scripts. If making changes to existing versions of the database, we then script the required changes. Usually, a synchronization tool will create a script that can be tweaked to work; although occasionally it will require something more complicated, as when we change a group of tables that model a complex relationship. The alternative, a technique that seems especially popular with MySQL users, is to do builds and updates using a series of migration scripts, executed in the correct order. Occasionally, this can seem like another cultural divide, where 'migrations' developers argue loudly with the 'state' establishment, but in fact is merely a difference in emphasis.

I've always been on the 'essential' wing, when ascribing a value to the importance to source control. A working database developer sees the source of a database as an asset of the organisation that, hopefully, has value and so must enjoy the same custodianship as any intellectual assets. A developer who works primarily with migration scripts must still use source control: it isn't enough to store migration scripts without the source scripts. It is important to be able to track individual database objects so that one can see who in the team did what and when. You still need the object creation scripts in a migration-first development even if you just back-fill them into source control after a successful build.

A developer using a more traditional approach, working from the object scripts, can and usually will, create a migration script to make changes, and will test it out in staging until it works. Often, the auto-generated script will do the job unmodified. If you're wise, you will then enshrine that successful migration script in source control.

Whichever way you've done it, you end up with both the source, preferably at object-level, and the migration scripts to go from each published version to the next. Whatever way we do it, we end up using a migration script to update the production database. What, I've often puzzled, is the difference?

After asking around, it occurred to me that 'migration-first' and 'state-first' are just techniques that we all use at different development stages in the lifecycle of a database. We already have plenty of ways of working. After all, when we generate a first-cut database from an E-R diagram we are hardly going to claim a new development technique. When we test a data model against cruel reality, we will do a lot of fine-tuning of tables that have test data in them, using migration scripts to deal with the complexity of shuffling the data around. As a database matures, and more people work on it simultaneously, the unpicking of source control conflicts becomes intolerable and we settle for a state-based object-level way of working. Where is the dichotomy? Where is the conflict? 'Migrations-first' seems to be just another tool in our toolbox.

Phil Factor

Phil Factor

Join the debate, and respond to the editorial on the forums

 
The Weekly News
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.
Administration

Automate Gathering SQL Server Information and CPU Count

You're truing-up your SQL Server licenses and need a list containing CPU core information plus version, edition, etc. for each of your SQL Servers. Ideally you would like it ordered by server name in a nice neat .csv file. How can this be accomplished?

The End of SQL Server 2008 and 2008 R2 Extended Support

Many organisations still run their companies on SQL Server 2008 or 2008 R2. There are many reasons to upgrade, but the most pressing reason is that extended support is running out. In this article, Brian Kelley explains what this means, tells you about some of the features you’ll gain if you upgrade, and provides some options.

Detail Job History – Back to Basics

Sometimes, just knowing the frequency of the job failure is good enough. Sometimes, more information is helpful, when meeting with the business to discuss the validity of the job in question.

Database File Changes

Using Extended Events to capture database file size changes.

Checkpoint Tracking via Extended Events

Checkpoint tracking can be useful when you’re attempting to understand when automatic checkpoints are occurring. Extended Events in SQL Server offer a powerful way to track many events in SQL Server as they happen. This post shows how to setup an Extended Events session for tracking checkpoints.

Things to Think About When Your Databases Grow Quickly

Your data size grows >10% in a single week. When this happens, you want to stop for a second to ask yourself a few questions...

SQL Server 2019 CTP 3.0 - max number of transaction log writers increased

In SQL Server 2019, it seems the maximum number of transaction log writers has been increased. If the horsepower of 8 transaction log writers is needed, then you might also want to know which schedulers share CPU with the transaction log writer hidden schedulers, in order to avoid them with busiest/most important queries.

Azure DevOps

Azure DevOps - Build SSIS project (CI)

I have my SSIS project in Git (Azure Repos Git) and I want to build my project in Azure DevOps. How do I add Continuous Integration (CI) for my SSIS project?

Backup and Recovery

Getting Your SQL Server Backup Footprint

Recently, I needed to be able to determine the total size for all the most recent full backups for certain servers, where each back up had it’s own specific directory. John Morehouse provides a simple PowerShell script that does the trick.

Big Data

How to Setup SQL Server Big Data Cluster in Azure (AKS) – Part 4

Mohammad Darab takes a look at the dashboards we can use to monitor the health of your Big Data Cluster.

Data Privacy, Compliance, and GDPR

10 steps you can take to be compliant worldwide – free whitepaper

Learn how data protection legislation is changing around the world and the 10 steps database teams can take to ensure compliance and defend against data breaches.

Going Beyond GDPR to Protect Customer Data

GDPR's biggest impact may ultimately be its effect on overarching laws to protect customer data.

Data Science

AI, Machine Learning and Data Science Roundup: June 2019

A monthly roundup of news interesting blog posts, software announcements and data applications on Artificial Intelligence, Machine Learning and Data Science.

Category theory for data science: cautious optimism

John D Cook is cautiously optimistic that the authors of CQL, categorical query language. may be on to something useful.

DocumentDB/Key-Value/Graph/other NoSQL Databases

How to Design Schema for Your NoSQL Database?

One of the attractions to NoSQL for developers seems to be that it is "schema-less", which is not true. In fact, schema design is of utmost importance for NoSQL databases, because unlike an RDBMS, there is no one-stop schema solution for all use cases.

Public Preview of Shortest Path on SQL Server 2019

In SQL Server 2019, the graph database capabilities now include a Shortest Path function, which can be used to find a shortest path between two nodes in a graph.

ETL/SSIS/Azure Data Factory/Biml

Importing data from MSSQL is faster than I thought

It has not been an exciting job, but having PostgreSQL to pull data out of Microsoft SQL Server is a joy!

4 Ways to Move Data Into a Reporting Server

Brent Ozar lists the most common ways to move the data from production to a reporting server. As you move down the list, the solutions get harder to implement, but offer more benefits on the reporting side of the equation.

MDX/DAX

DAX 101: Using CONCATENATEX in measures

This article showcases the use of CONCATENATEX, a handy DAX function to return a list of values in a measure.

Performance Tuning SQL Server

Reading the Ring Buffer in Extended Events

The much-maligned ring buffer target in Extended Events offers a simple, reliable place to store transient events. While it’s pretty easy to use the “Live Data” view in SQL Server Management Studio to view events as they happen, it can be helpful to programmatically extract data from the ring buffer manually.

What the Arrow Sizes in Query Plans Really Mean

SSMS’s arrow size algorithm changed back in SQL Server Management Studio 17, but most folks never took notice. These days, it’s not based on rows read, columns read, total data size, or anything else about the data moving from one operator to the next. Brent Ozar explains what they really mean.

How Often does SQL Server look for Deadlocks

By default, SQL Server checks for a deadlock every 5 seconds. However, if it finds when, It will start checking more frequently, and you should investigate the cause.

Query Store Best Practices

Erin Stellato explains the things you must know before you enable Query Store. If you already have Query Store enabled and running, you can review her list to ensure you are following best practices.

Understanding Columnstore Indexes in SQL Server Part 1

ColumnStore indexes are very useful for data warehouse workloads and large tables. They can improve query performance by a factor of 10 in some cases, so knowing and understanding how they work is important if you work in an environment with larger scaled data.

PowerPivot/PowerQuery/PowerBI

Budget vs. Actual Model in Power BI: Calculations and DAX

As long as you slice and dice data in the level of granularity that both tables support, then you don’t need anything else. However, if you want to go to lower grain than what the table supports, then you would need more calculations, and DAX can handle that easily.

Conquering the Chaos with Data Governance for Power BI

The self-service ideal is to free up data to empower users to discover and use information in insightful ways, but of course there are legitimate reasons to control how people access, use, and share data. f you’re currently using Power BI and find yourself facing these very problems, what can you do to conquer the chaos?

Filters on Power BI Slicers – Wait, What?

There was a new feature released in the June 2019 version of Power BI Desktop that I really like and I think warrants some explanation and review. The new feature allows you to pre-filter a slicer so that it only contains a subset of values that would otherwise appear in that slicer.

Are you accidentally exposing confidential data with Microsoft Power BI?

Are you accidentally exposing confidential data with Microsoft Power BI? You may be, if you are misusing the Publish to Web functionality.

Quick Tip: Making self-documenting Power BI apps

Even though the new app navigation capabilities introduced last month to Power BI pare just a refinement of what the Power BI service has always had, they can still make your apps significantly better. Specifically, these new capabilities can be used to add documentation and training materials directly to the app experience.

Product Upgrades and Releases

SQL Server 2019 community technology preview 3.1 is now available

Including enhancements to the graph database, encrypted query processing, and big data cluster features.

Python

Python Pandas MultiIndex and reading data from SQL Server

Tomaz Kastrun explains why SQL Server and Python Pandas Indexes are two different worlds and should not be mixed.

SQL Server Security and Auditing

What about orphaned windows users?

A database user is orphaned when it does not have a matching SID record in the sys.server_principals table. Microsoft has provided a stored procedure sp_change_users_login to find and optionally fix orphaned database users. But it only works for SQL Server logins. In other words, it does not support windows users.

T-SQL

Understanding SQL Server Linked Servers

I see there is an option in SQL Server Management Studio (SSMS) for Linked Servers and I want to learn more about what they do and how they work.

Simplify Date Period Calculations in SQL Server

Aaron Bertrand attempts to help people simplify the ways in which they determine the beginning and end of basic periods, like the current week, the current month, and the current year.

Multiple Identity Inserts

If you're copying data between tables, such as to refresh development copies with data, remember that SQL Server only allows one table to have the IDENTITY_INSERT property enabled at a time within each session, so you'll have to enable identity inserts and copy each table’s data, one at a time.

Does SQL Server 2019 Help With Multiple Function Calls?

If your functions already run pretty quickly over a small number of rows, and the calling query doesn’t qualify for parallelism, you may not see a remarkable speedup. That’s fine, though, because UDF inlining has other benefits, as Erik Darling explains.

A Hidden Value Of Apply

While we await FROID (an extensible, language-agnostic framework for optimizing imperative functions in databases) in SQL Server 2019, if we want scalar UDFs to run faster, we can either tune the underlying query or have them run fewer times. Erik Darling shows a neat trick using APPLY, to achieve the latter.

Problems Caused by Use of the SQL_VARIANT Datatype

Phil Factor illustrates the 'quirks' of the SQL_VARIANT datatype and why it's only safe to store data this way if you explicitly convert it to its true type before you use it.

The Lighter Side

Brace Yourself for These 9 Database Horror Stories

Make yourself a nice cup of cocoa, gather around the campfire, and listen up for these terrible tales. Illustrations courtesy of Swear Trek (not safe for work.)

Vendors/3rd Party Products

Download your free copy of the 2019 State of SQL Server Monitoring Report

Over 800 SQL Server Professionals took part in the State of SQL Server Monitoring survey. Providing insights into how they monitor their SQL Server estates, the technologies they work with, what their biggest challenges are, and what the future trends for the industry are likely to be. For the detailed analysis of the responses, download your free copy of the report.

How to provision realistic and compliant data to development – free whitepaper

Compare common approaches to provisioning test data to database development teams, and assess how they stack up in terms of delivering realistic test data, managing bottlenecks, and meeting data privacy regulations.

The 2019 State of SQL Server Monitoring Report

Redgate's second annual State of SQL Server Monitoring survey provides new insights into how the world of SQL Server monitoring is adjusting and adapting to emerging challenges, such as compliance regulations and cloud technologies, while keeping on-top of the usual issues such as deployments, availability, and capacity. Download your copy here.

 
RSS FeedTwitter
This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -