How to link a SQL Server database to a Subversion repository

Applies to
ApexSQL Source Control

Summary
This article describes how to link a database to a Subversion repository. We’ll explain several situations related to the linking process that depend on the way the repository is specified and a protocol used to access the repository.

Description

For the purpose of this article, we’ll describe all supported protocols: file, HTTP and HTTPS. In addition, various authentication methods will be included such as Subversion authentication as well as Windows Basic and Integrated authentication.

In order to link a database to a SVN repository, follow the steps through the Source control wizard explained in the How to link a database to source control article as the most steps are common, used in the same way across all supported version control systems. The accent in this article will be on the specific settings and information that should be passed to the Source control wizard in order to link a database to a SVN repository.

The first important point is to make sure that the Subversion item is selected in the Source control system drop down list in the Connection type step:

When Subversion is selected in the first step of the wizard, the System login step (the final step of the wizard) is as follows:

Subversion on a local file system (file protocol)

To explain how to link a database to a Subversion repository hosted by the local file system, we’ll use TortoiseSVN, a free Subversion client that integrates into Windows as a shell component. This means that a folder on a local file system can be created and in a few clicks initialized as a Subversion repository.

Quick tip icon

Quick tip:

ApexSQL Source control works in the same way with a local Subversion repository initialized with any other Subversion client.

Let’s create an empty folder in the local file system called SVNTestRepo. To initialize it as a Subversion repository, open the folder, right click and choose the Create repository here option:

This creates the necessary folder structure and initializes the folder as a local Subversion repository:

If the available Subversion client supports the CLI, repository can be initialized through the command line. To achieve this, make the directory where the svn.exe file is as a current directory and run the following command:

svnadmin create –fs-type fsfs c:\SVNTestRepo

Quick tip icon

Quick tip:

svn.exe will be installed as a part of the Subversion client installation.

The result of the above command is equivalent to what is achieved using the Create repository here option from the Windows context menu, previously described.

When the repository is initialized, a database can be linked using the file protocol. An example for the System login step is as follows:

Quick tip icon

Quick tip:

In the similar way as the repository is initialized, a remote repository can be checked out (cloned) to a local file system and a local path to the folder can be used as a Subversion repository.

The following command will create a working copy of a Subversion repository on a local file system:

svn checkout http://hostmachine:8080/svn/TestSVN SVN_WorkingCopy

Username and Password

Username and password are not required when working with the local repository. In order to avoid specifying username and password, it is mandatory to specify the file:\\\ part in the Repository field before pasting a local path to the folder initialized as a Subversion repository.

As soon as this is done, Username and password fields become greyed out with a caption in the Username field: Username and password are not mandatory.

Quick tip icon

Quick tip:

If for any reason, a folder initialized as a Subversion repository requires authentication such as a network drive that is password protected, or a Windows Basic authentication is set (that requires the user to provide username and password), this can be achieved by simply providing a username and password before providing the file:\\\ part in the Repository field.

Project

In the Project field, the user should specify a separate folder inside the repository where each database will be linked. To manage projects inside the repository (create and delete), use the Browse button and the Choose project dialog:

HTTP and HTTPS

Besides the file protocol Subversion repository can be accessed over HTTP or HTTPS protocols. As an example we’ll use VisualSVN as a Subversion server that can be installed on a local Windows machine. The following is a format of the Subversion repository URL:

<protocol>://<host>:<port>/<URL_prefix>/<repository_name>

The explanation for each highlighted segment is listed as follows:

  • <protocol> – Subversion can be used with http or https protocols
Quick tip icon

Quick tip:

In order to have HTTPS repository URL, a secure (TLS/SSL) connection must be enabled on the Subversion server.

  • <SVN_server_name> – host machine, NetBIOS, or DNS name
  • <port> – by default 8080 is used
Quick tip icon

Quick tip:

If the default port is in use by any other resource, it can be changed to any free (available) port. It is often changed to port 80, 81, 443 or 8443.

  • <URL prefix> – by default it is svn, but this can be changed to any other term, depending on the user needs, or it can be omitted
Quick tip icon

Quick tip:

ApexSQL Source Control works in the same way, no matter if the default prefix is used, if it is changed or omitted.

  • <repository_name> – name of the Subversion repository

When a new repository is created (for instance, we have created a repository called TestSVN on a machine called dev01), the following will be provided as a Subversion repository URL:

http://dev01:8080/svn/TestSVN/

To link a database to the above created Subversion repository, its URL needs to be provided in the Repository field under the System login step:

Depending on the authentication settings (Subversion or Windows authentication) a username and password may or may not be required.

A Subversion server can have its own authentication which requires a username and password for each user, despite of Windows users. Such authentication has its advantages, but in most cases it is not recommended as it requires additional (separate) user management on a Subversion server side plus regular Windows user management. Apart from that, a Subversion server can authenticate users with Windows Basic and Integrated authentication. Basic authentication requires that the user specifies Windows user credentials when linking a database, while the Integrated authentication does not require such information.

Quick tip icon

Quick tip:

When the Basic authentication is set, it is strongly recommended that secure connection is used (HTTP instead of HTTP) as credentials are sent over the network without being encrypted previously.

Accessing online Subversion repositories

Similar to any other Source control system cloud service, when creating a Subversion repository in the cloud, credentials used to log-in to a cloud portal are about to be used in the System login step as credentials for a Subversion repository unless other methods are explicitly specified by the Subversion cloud service.

Quick tip icon

Quick tip:

Subversion repository management depends on the resources and options provided by the Subversion repository cloud hosting provider. This means that before creating an account, it is recommended to research through available documentation and online resources for all the benefits and resources that provider offers to the user.

For instance, if a Subversion repository is created on Assembla.com the following URL will be available for the user:

This can be used as a Repository path in the System login step of the Source control wizard along with the Assembla.com login credentials as a username and password:

In this specific case email is not used as a username for the cloud Subversion service. However, in order to login to Assembla.com, a username is required and not an email.

Q: Is it possible to use the Basic authentication over HTTP protocol?

A: Yes, this is supported, but not recommended due to a security reasons.

Q: Can I link a database to a Subversion repository hosted on a machine in the same domain as the one I’m using to access the repository, with Windows authentication?

A: Yes, if the machine where the repository is hosted is in the same domain as a machine where SQL Server management Studio is installed, the user will be able to link a database using Windows credentials.

Q: Do I need a separate project for each database that I want to link to Subversion?

A: It is strongly recommended that each database has a separate project on a Subversion repository, as ApexSQL Source Control creates its own folder structure. In case of versioning the same database across multiple environments where a database from each environment is identical (or needs to be in sync) with the same database from another environment, a single project can be used.

Q: How can I change the password for linking a database, in case it is changed for the specific user?

A: Simply by unlinking a database and linking it back to the same repository using the new password.

Q: Can I relink a database without losing any data if the protocol or authentication is changed?

A: Yes, this is possible to achieve, as the authentication and credentials are irrelevant as long as the same repository is accessed.