SQL Server & Inedo Products

ProGet, BuildMaster, and Otter use Microsoft SQL Server as a database backend. The Inedo Hub can install SQL Server Express for you, or you can use another SQL Server edition.

If you're not familiar with managing SQL Server, this guide will provide some high-level advice and guidance on setting up, maintaining, and using SQL server, including:

• Installing and Upgrading SQL Server
• Required Database Permissions
• How to Use Azure SQL Server
• SQL Server Management Tools
• Migrating your Database to a Different SQL Server
• Querying the Database
• Advanced Maintenance

SQL Server system requirements

Here are the hardware requirements for the latest version, SQL Server 2022 at a glance.

A more detailed breakdown can be found at SQL Server 2022: Hardware and software requirements.

Supported Versions of SQL Server

This includes the latest version of SQL Server, and others such as:

• SQL Server 2022
• SQL Server 2019
• SQL Server 2016 and 2017
• SQL Server on Linux

The following cloud-based servers are also supported. As these are cloud-based services, they have no system requirements.

• Azure SQL
• Amazon RDS

SQL Server Express

Installing your Inedo product, such as ProGet, through Inedo Hub will also install SQL Server Express, which has performed fine for nearly all organizations. It does have a 10GB limit, but this can be mitigated through data retention policies.

If your current version of SQL Server is already licensed, consider working with your DBA team to get the tool’s database installed and configured, so they can oversee and protect the data as with other databases.

Installation and Upgrading SQL Server

The Inedo Hub can install SQL Server Express for you when installing a product; this will use the default installation options to create an instanced named INEDO. You can also install SQL Sever Express yourself, or use a different edition on a dedicated SQL Server or cloud.

If you're using Linux, you can use Microsoft's SQL Server Linux container images; see our Docker Installation Guide to learn more.

How to Install SQL Server On Windows

Although the Inedo Hub can install SQL Server Express automatically, you may wish to install a SQL Server edition on your own. Microsoft's SQL Server Installation Guide has some pointers on how you can do this:

• From the Installation Wizard
• From the Command Prompt
• Using a configuration file
• Using SysPrep

Upgrading SQL Server

We recommend that you update fairly regularly, as Microsoft will cease support of it's older versions of SQL Server eventually. Refer to Microsoft's Upgrade SQL Server or use their SQL Sever Installation Wizard.

Required Database Permissions

The service account that runs the Inedo product (by default, NETWORK SERVICE) needs to have a login on the SQL Server and be granted the db_owner role. In 2024 versions and earlier, the «product-name»User_Role role was used instead.

When upgrading or installing an Inedo product, the user account running the Inedo Hub must have database owner (db_owner) permissions on the product's database. As a general best practice, this user account should be different than the service account.

If you install SQL Server Express using the Inedo Hub, then these permissions will already be configured.

Connecting Inedo tools to Azure SQL

If Azure SQL is being used as your SQL server, follow these steps to get everything connected:

1. Create an empty database on an Azure SQL Server. Typically these are named after our tools i.e. ProGet, BuildMaster, Otter, etc. However, any naming standards within your organization would take precedence.

2. Open the IP address of the machine where our software is being installed by updating the firewall rule of the server.

During installation the connection string for Azure SQL should be formatted as:

Server=tcp:{azure-server}.database.windows.net,1433; DataBase={AzureDBName}; Persist Security Info=False; User ID={username}; Password={password}; MultipleActiveResultSets=False; Encrypt=True;TrustServerCertificate=False; Connection Timeout=30;

SQL Server Management Tools

The following tools are available to you when managing and administrating SQL
Server, allowing you to manage and query SQL Server databases, and perform essential maintenance procedures such as database creation, backup and restoration.

osql.exe

osql.exe is a command-line tool that is used to write SQL statements and scripts when using Microsoft SQL Server. It is ideal for performing routine tasks quickly and efficiently, offering a flexible way to manage SQL Server instances. It comes pre-installed with SQL Server Express.

However, if you're an inexperienced with SQL, running queries and performing other procedures using this tool may be challenging. In that case, you may prefer SQL Server Management Studio, which provides a graphical user interface to make tasks easier.

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is a GUI-based IDE tool used for managing SQL Server databases. It offers a comprehensive and feature-rich environment that's useful if you prefer to perform adminstration through a graphical user interface.

SSMS can be more resource intensive than osql.exe, as well being more complex with it's list of features and functionality, so you may prefer osql.exe if you are confident as an SQL Server administrator.

While osql.exe comes installed when installing SQL Server, SMSS will need to be installed separately. For more information, refer to the SQL Server Management Studio page.

Migrating your Database to a Different SQL Server

When migrating the database of your Inedo product, such as ProGet, to a different SQL Server we recommend that you perform a full backup of the database and restoring it on the new server. After it is successfully restored, you will need to to edit the configuration file and change connection strings to point to the new server.

Querying The Database

We support querying databases but we do not offer support for writing to them. Tables, views, and stored procedures are closely linked with foreign keys and indexes. Should you add or change records this may lead to instability within the applications.

If you wish to gain a better understanding of the structure or seek other support, we welcome you to contact our engineers through our Support Page for any assistance or inquiries you may have.

Database Maintenance & Clean-up

Although you can get comprehensive information about the database using SQL Server Management Tools, you can find some information database, tables, and underlying data storage under Admin > Database Overview.

This page will warn about misconfiguration (such as a FULL Recovery Model) and provide a few tools to help clean-up unused, legacy data. We generally don't recommend using these tools unless directed by an Inedo engineer or you're familiar with what they're doing.

Index Maintenance

Indexes are used in databases to speed up SQL queries. However, over time, these indexes can become disorganized, which can slow down query performance.

To address this issue, you can use our DefragmentIndexesWithRowCount.sql script to identify identify indexes and reorganize them. This usually isn't necessary, but it may be helpful to address slow performance.

Troubleshooting

When loading ProGet, I see a page that states "Failed to Connect to the Database" or "Connection String is Missing"

This typically indicates that ProGet lost connection to the database or a connection string has not been set. To do this, verify your SQL Server instance (or container) is running and that you have the correct connection string specified in your product configuration file.