Development / Release Challenges

Question

Background

1 x Dev SQL Server
1 x UAT SQL Server
1 x Prod SQL Server

Developers use SSMS to view SQL Server objects and code and make changes directly to these objects in SQL Server itself.

Challenge

We have multiple developers potentially making changes to the same database object (let’s say a stored procedure or a view). The challenge arises from different bits of work happening on the same object where the delivery timescales for release for each of the bits of work are different. This means we end up with someone having completed their changes on the dev object, but releasing the changes into the next environment along may fail as the view (for example) contain may another developer’s changes too, and those changes themselves may require other objects. The business may not be expecting that other’s developer’s work to be released anyway, as there may be days/weeks of effort still to put into it before release. But that doesn't help the developer who's ready to go into the next environment.

How do we get round that?

How should each developer have started off, before they started making changes, to avoid dependency issues when releasing?

How can a developer “jump the queue” and release their bits of work, equally without scuppering anyone starting off their particular change too.

Answer 1

1 source control

Any source control system GIT/TFS to manage your code and control changes

2 branching/release strategy

Git Flow! F.e. main branch with current working source code (main, develop whatever you call it), each developer works on his own feature branch, after he done his work he test it by deploying on DEV environment and running tests. After that it could be merged into release branch that will go live on PROD. Also you need to consider merge vs rebase strategy (some link).

3 and some SCRUM

The most basic: 2 weeks for sprint, after end of the sprint you create new release branch and deploy it on UAT for testing. During next sprint release is tested on UAT, developers work on tasks from new sprint. Deploy tested release on PROD, developers have there 3rd sprint and UAT is ready for new release to be deployed. And so on.

4 more then one DEV environments

Based on the number of developers you need more DEV environments.

Answer 2

This is not a perfect answer, nor is it the only potential answer - but it's a good start. It's based on my experience within a relatively small shop, where tasks are re-prioritised frequently and changes required after testing etc.

Firstly - it's about process. You need to make sure you have a decent process and people follow it. Software etc can help, but it won't stop people making process errors. There are a lot of products out there to help with this, but I find making small steps is often a good start.

In our shop, we use Git source control for managing codes and releases. These script the entire database structure and views/etc, and are used to manage any changes to those scripts.

In general, we have a 'release' branch, then 'feature' branches for updates we're working on, and 'hotfix' branches for when we do changes to live on the fly (e.g., fixes etc).

When working on a specific branch, you check out that branch and work on it. Any change to the database has to go into an appropriate branch.

When ready to go live, you merge the feature/hotfix branches into that release branch when they're released. This way the 'release' branch always exactly matches what is on the production database.

For software, we use Redgate Source Control integratated with SSMS, but there are definitely others available (e.g., ApexSQL Source Control). You can also do it manually, but I wouldn't suggest it.

You don't have to, but you can also use a git GUI (e.g., SourceTree) to manage your branching and merging etc.

There are additional software products that can help to manage releases/etc (including scripting etc) but the source control aspect should be the biggest help with the main issue (being able to work on different things and helping ensure no clashes).

Regarding Git and how to use it (or SVN etc) - if you haven't used them before, they're a bit weird and take some getting used to. We had a few re-starts with a few different processes before we came up with an approach we liked. It will also take some time to run into the different issues that can arise - so you cannot expect this to just fix it out of the box.