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

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?

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.

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.

Using Extended Events to capture database file size changes.

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.

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...

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.

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?

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.

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

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.

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

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

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

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.

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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?

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? You may be, if you are misusing the Publish to Web functionality.

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.)

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.

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.

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.