By placing under source control everything we need to describe any version of a database, we make it much easier to achieve consistent database builds and releases, to find out who made which changes and why, and to access all database support materials. Matthew Skelton explains how to make sure your version control system fully supports all phases of the database lifecycle, from governance, development, delivery and through to operations.… Read more
With database deployments, not all script-based processes are equal. Some use change scripts in a free-and-easy way, and some, which are normally called 'migrations-based approaches', have more discipline around them. In this article, Redgate Product Manager Elizabeth Ayer covers 'migrations', and shows some of the benefits that have come with new tooling which is specifically designed to assist the change script processes.… Read more
Few databases are self-contained. They take data from other sources, and publish them to downstream consumers of data. These ETL processes tend to grow in an unplanned organic way and so tand to cause trouble both in production and in deployment. Database Lifecycle Management systems allow all the teams to come together to ensure that ETL systems meet all requirements.… Read more
It should be simple to upgrade a database to a new version. It certainly can be, but if you need to preserve the existing data and you have made changes to the design to the tables then it can get complicated. If you are deploying changes to a heavily-used OLTP system on which an organization depends, then you need to understand, and be familiar with, the issues that can effect a database migration. Matthew Skelton explains the basic approaches.… Read more
Any database development project will be hard to manage without a system for reporting bugs in the code, anomalies and incidents from live environments, and for keeping track of new features. Matthew Skelton discusses the critical role of an integrated issue tracking system in database lifecycle management.… Read more
The purpose of a database build is simple: prove that what you have in version control can successfully create a working database. And yet many teams struggle with unreliable and untested database build processes that slow down deployments and prevent the delivery of new functionality. Grant Fritchey explains how to achieve an automated and reliable database build that is only as complex as the database system it needs to create.… Read more
When you use AdventureWorks as a practice database, have you ever looked at the code and thought 'what idiot did this', or 'what did the DBAs think when they saw that?' Subconsciously, you occasionally forget it isn't real and 'fill in the back-story'. The SQL Release Team at Redgate did the same with their own practice database, and imagined a cast of characters wrestling with the difficulties of deploying it.… Read more
Continuous integration (CI) is becoming more and more common in application development. It ensures code and related resources are integrated regularly and tested by an automated build system, and highlights problems early in the development process. But what about database development? Can the same advantages of CI be applied to production databases? Where do you start? How do you tackle it? Sjors Takes relates his experience.… Read more
Hosted TFS, now called Visual Studio Online (VSO), has a new way of writing build processes called Build vNext. Agent tasks are the building blocks of processes and you can supplement the built-in ones with custom build tasks defined in JSON that use targets written in node.js or PowerShell. Jason Crease shows how to develop custom build tasks for building, testing, publishing and synchronizing databases.… Read more
The scale of change in the insurance and financial markets is such that there is little time for the application or database developer to sit back and work out ways of improving the delivery process. Over time, however, it is possible to improve the process so that individual heroics are required less and less as release and deployment become more managed and predictable. It can be messy and error-prone at times but the long-term benefits make the struggle worthwhile.… Read more
For an experienced database developer, the idea of doing non-breaking database deployments while the database is still online holds respect but only a little fear. If your test procedures are good, you attend to the detail, and the application interfaces are properly managed, it can be achieved. Ed Elliott explains some of the issues to tackle.… Read more
Once you're clear about what data the developers and testers need to be able to be effective, you have several alternatives approaches to providing them exactly the data that will give you rock solid database tests that will allow deployments with far less risk. Tim goes through some practical demos to show you how you can automatically keep your testing environments up to date, and thereby ship more robust databases.… Read more
Behaviour Driven Development is not always clearly understood, and the term is particularly unfamiliar in database circles. Seb Rose introduces us to the fundamentals of BDD, and make some suggestions for how it might be relevant to database development.… Read more
Constructing a test environment for your databases can be a difficult task at the best of times. Once you've actually acquired the hardware needed and architected the environment, you still have to arrange and securely transport the data. And with the rising demand for fast feedback and continuously integrated processes, having all of this automated and operating at speed is a challenge all of its own.… Read more
So you're keen to take the first steps to the continuous integration of your database. You have to start by getting your database into source control. You can then begin to automate your build processes in order to generate and test a database. By doing this regularly, you'll be much better prepared for the deployment process because you'll have solved integration issues when they happen, and facilitated the broader testing process.… Read more
Continuous Integration and automatic builds are fantastic tools for software teams, but only if developers take responsibility for their code. Karsten Kempe explains how to use Team Foundation Server to drive better continuous integration, and walks through a simple (open source) tool he built to make nightly builds more transparent, and more valuable.… Read more
Test-Driven Development is a practice that can bring many benefits, including better design, and less-buggy code, but is it relevant to database development, where the process of development tends to me much more interactive, and the culture more test-oriented? Greg reviews the support for TDD for Databases, and suggests that it is worth giving it a try for the range of advantages it can bring to team-working.… Read more
We’ll be looking at a typical database ‘migration’ script which uses an unusual technique to migrate existing ‘de-normalised’ data into a more correct form. So, the book-distribution business that uses the PUBS database has gradually grown organically, and has slipped into ‘de-normalisation’ habits. What’s this? A new column with a list of tags or ‘types’ … Read more
There is a popular design for a database that requires a built-in audit-trail of amendments and additions, where data is never deleted, but merely superseded by a later version. Whilst this is conceptually simple, it has always made for complicated SQL for reporting the latest version of data. Alex joins the debate on the best way of doing this with an example using an indexed view and the filtered index.… Read more
Simple-Talk generally doesn't re-publish anything from another site, but Troy's popular blog post on the Ten Commandments of Source Control was too good to miss. Here is Troy's updated version in the light of the readers' comments made when it was first published.… Read more