How to save time linking to multiple databases by sharing mappings to other team members

Applies to
ApexSQL Source Control

Summary
Putting multiple databases under source control can take a long time for one user, let alone all the members of a team. Having a solution that involves one user linking databases from a single machine, allowing the rest of the team to “import” these settings would save a lot of time.

The team must use a shared SQL Server instance which hosts all the databases that need to be linked. The solution requires a separate SQL Server Management Studio instance, installed on each local machine.

All users must have unique credentials for accessing the repository, and only one source control system (i.e. Git) must be used. This will not work in cases when databases from a shared server are linked to different source control system repositories where users have different credentials.

Description

To accomplish the task:

  1. One user links all databases from a local machine
  2. The rest of the team uses these connection settings to link the databases to their respective local clients
Quick tip icon

Quick tip:

It is mandatory that at least one user links all databases, so the rest of the team can use the connection information.

The first step is covered in the article that explains how to link a database to source control.

After the first user links all databases from a local machine, the complete settings can be transferred to any other instance of SQL Server Management Studio on other user’s machines, which has ApexSQL Source Control installed.

For the purpose of this article we have used 5 sample databases, from DEV01 – DEV05 linked to the same Team Foundation Server repository, each one in a different project:

After linking the last database, all the information should be saved in the Options.xml file located, by default, in C:\Users\<current_user>\AppData\Local\ApexSQL\ApexSQLSourceControl

The Options.xml file must be shared across the team to be available for importing on a local machine of each team member.

Import connection for the shared databases

Quick tip icon

Quick tip:

A requirement for importing connections is that all users connect in the same way to the shared SQL server instance as the first user did. For example, if SQL Server name is DevServer, or if it is specified over the IP address, all developers must specify a name when connecting through local SQL Server Management Studio.

After starting SQL Server Management Studio locally and connecting to the shared SQL server, each developer should perform the following steps:

  1. From the add-in main menu, select the Options item:

  2. Under the Connection tab, browse the external options file:

  3. Navigate to the location where the shared Options.xml file is stored, highlight it and click the Open button:

  4. A list of all databases linked in the shared model appears:

  5. At this point, all databases that are linked in the shared model on the same repository should be selected:

  6. Specify source control credentials for the repository where the selected databases are linked, and click the Link button:

  7. Confirmation message appears, informing the user that all selected shared databases are linked:

  8. By following the instructions from the message, SQL Server Management Studio must be restarted, in order for the new changes to take effect

    Quick tip icon

    Quick tip:

    It is a necessary step to restart SQL Server Management Studio after importing shared connection information from the add-in options.

  9. After restarting SQL Server Management Studio and connecting to the shared SQL Server instance, databases will appear as linked

Q: What if I have databases linked in the shared model on another repository where I need different credentials?

A: In that case the process of importing connections should be repeated in order to select the appropriate set of database and provide source control credentials for linking to another repository

Q: What if different users connected to the same SQL Server in a different way?

A: To import shared connections identical server naming is required across the team.