Introduction to Red Gate’s SQL Source Control

It’s unthinkable for modern application developers to work without source control. The benefits it brings to software development are so well and so long understood that even lone hobbyist developers will tend to employ a source control system. Database source control was for some time seen as unfeasible, and it is not yet common practice everywhere.

However, Now that database source control is not merely possible but easy, SQL Developers, Business Intelligence developers, and DBAs are beginning to understand its benefits too; how it helps software teams to work better, faster, and more collaboratively. How does this translate into business benefits?

Related Sponsored Content

SQL Source Control is going to be a huge productivity booster for our customers. For those that aren’t currently version controlling their databases, this tool gives them absolutely no excuse. It’s so easy! For those rolling their own solution or manually dumping and tracking their schema, this product will save them a ton of time. Point, click, go.

- Rob Sobers, Head of Professional Services, Fog Creek

How source control helps your business

If you’re not familiar with source control (also known as Version Control), then you’re in luck – there is a huge repository of material available on InfoQ to introduce you to the idea. If you’re already aware of what source control is and how it works, then hopefully you’re also aware of the business benefits of having your software teams using it:

• Management visibility - Source control maintains detailed change histories, and can often be associated with issue tracking systems. For example, SQL Source Control lets you associate database tasks with Microsoft’s Team Foundation Server “work items”.
• Backup, recover and rollback- Your databases and data warehouses are where much – if not all – of your critical business information lives. Having a history of how it’s changed, and making it easy for the teams maintaining it to work efficiently is as important as your backup and recovery strategy.
• Auditing and compliance - Regulatory compliance has an impact throughout an organization, from finance departments and CIOs to individual developers and database administrators. For example, compliance auditors will require DBAs to account for all changes to a database, and detail all those with access to it.

Overview of SQL Source Control

The change tracking provided by source control is the first step to getting your database ready for compliance, and an essential step in maintaining a robust audit trail and managing risk. If you already have a version control system in place, then it’s easy to integrate your database into the same system using SQL Source Control.

In addition to integrating with Microsoft Team Foundation Server, Red Gate partners with leading source control providers (such as Fog Creek, SourceGrea, Collabnet, and Perforce) to ensure you can get the best out of your version control system.

Efficiency and scalability

Genuinely scalable growth is about doing more without spending more, which is where efficient tools and processes come in. Source control dramatically simplifies change management, enabling developers to work faster, making it particularly important for geographically distributed teams. Although DBAs, database developers and application developers are often in different locations, they are fundamentally part of the same team. At the very least, enabling them to work on the same platform using tools like SQL Source Control will simplify deployments, making complex processes more automatable and repeatable.

Cost of ownership

Cost of ownership naturally varies between source control systems, but what doesn’t vary is the fact that the easier a system is to use, the quicker it is to learn, to implement, and so to return value.

For database source control, SQL Source Control integrates existing source control systems with the standard development environment, and doesn’t require that application or database developers change the way they work. It’s designed to be simple and easy to use. This means it is easy to adopt, with no additional infrastructure requirements, so the cost of ownership stays low.

Walkthrough

Put simply, SQL Source Control links your database to your source control system, so you can commit changes without leaving SQL Server Management Studio. Let’s briefly walk through how to set up database source control, and what kind of workflows you might expect your team to adopt as a result.

I’ll assume you’ve already got a source control repository set up, and your application developers are already committing their code into it.

Linking a database to source control

To get started, you need to link your database to your source control repository. If you don’t want to try SQL Source Control with one of your own databases yet, you can create a dummy database full of objects using this script.

Select a database in the Object Explorer. In SQL Source Control, in the Setup tab, click Link this database to source control:

(Click on the image to enlarge it)

Linking your database to source control in the Setup tab

In the Link database to source control dialog box, select your source control system and provide details for the source control repository you want to link the database to. For more information, including examples, see Linking a database to source control in the SQL Source Control documentation.

Choosing a development model

When you link a database, you tell SQL Source Control which development model your team uses:

• Dedicated
  • Each developer works on their own copy of the database
• Shared
  • Developers work on a single database

SQL Source Control changes its behavior slightly to suit the model your team uses, but the key point to notice here is that the tool is specifically designed to fit around your team’s existing workflows, rather than require them to adopt new ones.

Ready to go?

Once you’ve provided repository details and selected a development model, click Link. The database icon in the Object Explorer changes to indicate that it’s linked to source control:

A database that’s been linked to source control in the Object Explorer

Making your first commit

Once you’ve linked your database to source control, you’re ready to make your first commit (also known as the initial commit). This gets a copy of your database into source control. To make the initial commit, go to the Commit tab. This tab lists database changes that haven’t been committed to source control yet:

(Click on the image to enlarge it)

Committing database objects to source control for the first time in the Commit changes tab

You may have some types of object you never want to commit to source control, for example, objects of a certain type (eg functions or views), or objects that belong to a particular schema. You can create a filter to exclude certain objects so they never appear in the Commit or Get latest tabs.

To create a filter, right-click on the grid and select Edit filter rules. For more information, see Using filters to exclude objects in the SQL Source Control documentation. Make sure all the objects are selected, write a commit message (eg “Initial commit”), and click Commit.

Committing a change

Try making a simple change to the database, such as adding a column to a table. After you make the change, when you go to the Commit tab, the change appears in the list of changes to commit:

(Click on the image to enlarge it)

See which changes you haven’t yet committed to source control, with line-by-line diffs, in the Commit Changes tab

In the lower pane, you can see the differences between the SQL creation scripts for the new object and the object in source control. To commit the change, write a commit message and click Commit. Commit messages are useful when getting changes or reviewing history, so your team can quickly understand what’s in each change.

Getting a change

Let’s check out the database from source control. Link an empty database to the same repository you linked your database to. Afterwards, when you go to the Get latest tab, you’ll see your database changes waiting to be retrieved from source control:

(Click on the image to enlarge it)

Getting database changes in the Get latest tab

When you click Get latest, the new database will be updated to match the other database.

Setting SQL Source Control options

You can configure SQL Source Control to suit your development process:

• Filter changes to objects you never want to add to source control
• Change how SQL Source Control understands database changes
• Link static data to source control

Sharing option changes with your team

When you edit an option, it appears in the Commit tab as a change to commit:

(Click on the image to enlarge it)

Sharing a SQL Source Control option with your team, in the Commit changes tab

After you commit, other people on your team can get your option change in the Get latest tab. This makes sure everyone works with the same options.

What’s next?

Nice work – now you’ve covered all the basics of SQL Source Control. So what next? If your team is on board with the idea of database source control, then it’s just a case of setting everyone up with the tool, and pointing everyone towards the same repository.

However, most teams want to do some extra investigation to be sure they’re making a good investment. To help your team decide, we put together a guide full of free articles, eBooks, and webinars, so that you can:

• Show your DBA how source control keeps production data safe
• Help anyone who hasn’t used source control before to learn the fundamentals
• Explain the advantages that database source control brings to a business:
  • Time and money savings
  • Strong regulatory compliance
  • Fast, reliable delivery of new products to customers

Conclusions

Implementing source control for databases helps developers, DBAs, and Business Intelligence professionals work more efficiently, which ultimately saves money and expands their potential to do high quality work. Database source control hasn’t been possible for a long time. Because problems with no feasible solution are readily regarded as a sunk cost, the disparity with application development and the inefficiency caused is often overlooked.

Now database source control is simple to implement, it is no longer necessary to accept that compromise. Database development can enjoy the same benefits as application code, and businesses can start saving money, and both database and application developers can adopt the same, efficient workflows.

About the Author

David Atkinson has worked on tools for SQL Server since 2005, beginning with (and recently returning to) product management for SQL comparison and deployment tools. David’s been focused on maturing SQL Source Control and bringing it to market, and he’s on a mission to bring the benefits of continuous integration and other Agile practices to database development teams. If you’ve ever wondered why relational database changes are hard, it's because he’s not finished yet. Bug him on twitter and he’ll try harder.