SQL Server Source Control Basics

by Robert Sheldon, Rob Richardson, Tony Davis

Few software developers would build an application without using source control, but its adoption for databases has been slower. Yet without source control to maintain the scripts necessary to create our database objects, load lookup data, and take other actions, we cannot guarantee a reliable and repeatable database deployment process, let alone coordinate database upgrades with changes to the application. We also run the risk that our "ad hoc" database patching will cause inconsistencies and data loss. Source control can and should play a key role in the database development and deployment process, and this book will show you exactly how to get started. It provides 'just enough' detail about the core components of a source control system and how to incorporate that system into the database development and deployment processes, Database Source Control architecture - what to include, how to structure the components Collaborative editing - teamwork on a database project, while minimizing change conflicts and data loss. Change auditing - what changed between versions and who changed it? Branching -work independently on separate features and control what to deploy and when Merging - what happens if one user changes a column name while another updates its data type? A merge operation lets the team decide the correct outcome Building and Deploying databases - building new databases and upgrading existing ones from source control Every chapter follows the same "half-theory, half practice" template, so you learn the concepts then see how they work.

308 pages, Paperback

First published June 11, 2014

Reviews

[Nathan Albright · Rating 5 out of 5]

When I went recently to the monthly Oregon SQL meeting [1] I happened to win this book in the raffle, and I found it to be an immensely worthwhile book even if it is for those who are more proficient in SQL than I am [2]. At any rate, even if I do not consider myself particularly proficient in the techniques discussed in this book, most notably the use of source control as a way of aiding the quality of database functionality as well as the development of skill in how to successfully manage the challenges of development and error fixes in one's projects, I figured that having read this excellent and practical book that I ought to at least make its contents better known to those readers who may profit from the expertise of the authors to an even greater degree than I was able to. This book is, above all, practical, and it happens to be straightforward as well, and, perhaps unbeknownst to the authors of the book, the techniques used in this book and the insights the authors provide are of use beyond areas of technology to larger questions of version control and textual analysis, which may be of interest even to readers who are not particularly technological in their focus and interests.

In about three hundred pages of material, the authors give a systematic demonstration, full of code examples and diagrams as well as helpful explanations, of various aspects of the basics of source and version control in SQL Server over the course of eight chapters. The authors begin with a brief history of source control and an introduction to the material and the authors' approach to using hands-on-examples before giving the first chapter on source control concepts, including the central repository, the working folder used by each developer, versioning and the process of collaborative editing, as well as branching and merging, which receive a great deal of discussion later. The second chapter discusses the repository in greater detail, comparing and contrasting centralized versus distributed repositories and giving the tradeoffs of each approach, discussing the task of versioning, and introducing source control using Subversion as one's source control solution, abbreviated as SVN throughout the text. The third chapter discusses the need to plan for database source control through integrated development environments, and also discusses challenges, compares the disconnected versus online development approaches, as well as shared versus dedicated database development, and discusses matters of source control architecture, giving detailed examples using a sample bookstore database, an example that is continued throughout the book. The fourth chapter looks at editing and versioning, looking at code modules and tables as well as the mechanisms of version numbering and deltas and diffs, pointing out the solution of reverting to previous versions when things go wrong. The fifth chapter discusses branches and tags, looking at various strategies of dealing with branching and the need to frequently commit changes to avoid pain in the merging process later on, as well as the use of tags and branches in Subversion. The sixth chapter talks about merges, and how it works in general as well as specifically in subversion. The seventh chapter discusses the process of manually deploying databases from source control, starting with pitfalls in the process like version drift, a lack of testing, and having no rollback plan, and then moves on to discussing how one builds and deploys databases and giving extensive coverage to how this works in the example problem involving the bookstore database. The eigth and final chapter discusses automating database versioning and deployment from source control by the use of CREATE scripts, giving more examples for the use of these techniques in the bookstore database, and also discussing database versioning and development with continuous integration before closing with a brief summary.

Among the main insights of a book like this there are a few that are worth mentioning. Some of these insights come are relevant to the technical discussion of SQL server and database management that are the subject of this text, namely the need to have an active QA team that rigorously tests databases in development early and often, as well as the need to frequently commit changes and avoid the silo effect where people work in a black hole of silence without communicating their changes and their actions to other people involved in the same projects. This lack of testing and lack of communication is, moreover, something that is widely and generally relevant. In looking at the way that version drift occurs, I was struck in particular by its relevance to matters of biblical and other textual criticism in that over time there is a great deal of version drift when it comes to texts in production and use even though there is supposed to be no development once a text has been completed. Nevertheless, there is version drift because glosses become added and embedded in the text, because of both garbling and attempts at harmonization and because of the imperfections of the copying and transmission of texts over time, especially among isolated groups or populations that are often in silos and engaged in different branches of the same overall texts. This is an insight the authors, or others, may want to explore in greater detail, but although this work is mainly of interest to the technical employees who are engaged in the practice of handling databases and working on technical design projects in SQL Server, there is clearly relevance far beyond this field in the concepts and issues discussed in this book.

[1] https://edgeinducedcohesion.wordpress...

[2] See, for example:

https://edgeinducedcohesion.wordpress...

https://edgeinducedcohesion.wordpress...

https://edgeinducedcohesion.wordpress...

https://edgeinducedcohesion.wordpress...

https://edgeinducedcohesion.wordpress...

https://edgeinducedcohesion.wordpress...

https://edgeinducedcohesion.wordpress...

https://edgeinducedcohesion.wordpress...