Team database development is hard. It throws up questions about who’s responsible for code, how to track changes, and how to share them. For application development, teams can overcome these problems with version control. Databases are different.
“Software development is a difficult and demanding discipline. It gets even more difficult when you bring teams of developers together on a single project.” SQL Server MVP Grant Fritchey
In the past, database version control has been seen as unfeasible. It’s still not a universal practice. But it brings the same benefits to development as version control for your application. It helps software teams to work better, faster, and more collaboratively. It lays the foundation for continuous integration and automated deployment. And, for a truly risk-free development environment, it’s a must.
SQL Source Control from Redgate is the first step to getting your database into version control and giving it the same treatment as your application code.
It integrates existing version control systems into SQL Server Management Studio, the standard environment for SQL Server development. This means there are minimal changes to the way you work. It’s designed to be simple and easy to use, so you see the benefits immediately.
In the rest of this article, I’ll walk you through how to set up database version control, and the sorts of workflows you might expect to adopt as a result. If you want to follow along, you can download the SQL Source Control installer from website.
SQL Source Control links your database to your version control system (VCS) – for example, Git, Team Foundation Server, or Subversion – inside SQL Server Management Studio. This means you can share changes without leaving your development environment.
Let’s assume you’ve already got a version control repository set up and you’re already committing application code to it. In this example, we’ll be using Git as our VCS, but SQL Source Control works with all other version control systems.
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.
In SQL Server Management Studio’s Object Explorer, select the database you want to link to source control.
In SQL Source Control, on the Setup tab, make sure Link to my source control system is selected and click Next:
On the Choose your source control system page, select Git and click Next.
The Link to Git page opens:
In the Folder field, specify a folder in an existing local Git repository where SQL Source Control will save SQL scripts, for example: C:\Repository\DatabaseFolder
If you’re the first person to link the database to source control, specify an empty folder. If someone has already linked this database to source control, specify the folder they used.
Ready to go?
Once you’ve provided repository details, click Link.
The database icon in the Object Explorer changes to indicate that your database is linked to source control:
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 changes tab. This tab lists, database changes that haven’t been committed to source control yet:
You may have some types of object you never want to commit to source control, for example, objects of a certain type (e.g. functions or views), or objects that belong to a particular schema. You can create a filter to exclude these objects so they never appear in the Commit changes or Get latest tabs.
To create a filter, right-click on the grid and select Edit filter rules.
To commit your database objects to source control, make sure all the objects are selected, write a commit message (e.g. “Initial commit”), and click Commit.
Pushing changes to a Git remote repository
To get changes into your remote repository, you need to push after you’ve committed them.
To do this, find the blue banner above the commit grid and click Push. This pushes all commits from the local repository to the remote repository.
Committing a change
Try making a simple change to the database, such as adding a column to a table.
After you make the change, go to the Commit changes tab. The change appears in the list of changes to commit:
In the lower pane, you can see the differences between the SQL creation scripts for the new object and for 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 and why it was made.
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:
When you click Get latest, the new database will be updated to match the other database.
Pulling changes from a Git remote repository
To get the latest changes from source control, you need to pull changes from the remote repository.
- On the Get latest tab, click Pull from remote repository. Any changes from the remote repository are pulled to your local repository.
- Click Apply changes to database. The changes from the local repository are deployed to the database.
So 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.
But most teams want to do some extra investigation to be sure they’re making a good investment.
To help your team decide, we’ve put together a guide full of free articles, eBooks, and videos, so 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
Or, we can show your team exactly how it would work in your environment. Our technical team is more than happy to talk about your needs, and we can arrange custom demos for you on change management, productivity, or anything else you’re interested in.
To speak to someone email SQLSourceControlQuestions@red-gate.com
Reference: Pinal Dave (https://blog.sqlauthority.com)