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 depends on the way the repository is specified and a protocol used to access the repository.

Description

For this article, the following supported protocols will be described: file, HTTP and HTTPS. Also, various authentication methods will be included, such as the Subversion authentication as well as Windows Basic and Integrated authentication.

To link a database to a Subversion repository, follow the steps through the Source control setup window 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 setup window to link a database to a Subversion 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:

The Connection tab when linking a database to the Subversion repository

When Subversion is selected in the first step of the Source control setup window, the System login step (the final step) is as follows:

The System login tab when linking a database to the Subversion repository

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, the TortoiseSVN will be used, which is 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:

Creating a local Subversion repository over TortoiseSVN

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

Folder architecture for the Subversion loacl repository

If the available Subversion client supports the CLI, a repository can be initialized through the command line as well. 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 a repository is initialized, a database can be linked using the file protocol. An example of the System login step is as follows:

The System login tab when lining a database to Subversion repository using the File protocol

Quick tip icon

Quick tip:

In a 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:443/svn/TestSVN SVN_WorkingCopy

Username and Password

The Username and password are not mandatory when working with the local repository. 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.

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), click the Browse for folder button in the Project field and the Choose project dialog will open:

The Choose projet window when linking a database to Subversion repository

HTTP and HTTPS

Besides the file protocol, a Subversion repository can be accessed over HTTP or HTTPS protocols. As an example, VisualSVN will be used 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 an 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 VisualSVN Server is suggested to use default ports 80 or 443 for HTTP and HTTPS protocols

    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 8080, 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> – the 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:80/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:

The repository path when linking a database to the Subversion repository using HTTP(S) protocol

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 authentication, which requires a username and password for each user, despite 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 (HTTPS 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 the provider offers to the user

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

URL on the Subversion cloud repository

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

The System login tab when linking a database to cloud Subversion repository using HTTP(S) protocol

In this specific case, email is not used as a username for the cloud Subversion service.

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

A: Yes, this is supported, but not recommended due to 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 folder structure. In the 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.