SQL Server & Inedo Products

ProGet, BuildMaster, and Otter require Microsoft SQL Server. 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 «product-name»User_Role role in the products database.

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 inqueries you may have.

Advanced Maintenance

When administering a database, you made need to perform advanced maintenance. For example:

• When upgrading to a new version of the database
• When the database is responding slowly or not at all
• When data is corrupt
• When addressing vulnerabilities or other security concerns.

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 a SQL script such as this to identify indexes that need to be reorganized:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

This will return a set of results like this:

From this output, you can see that:

• the "PK__PlanVersions" index has the highest average fragmentation percentage (87.2%), which may indicate that it is in need of maintenance to improve query performance.
• The "PK__BuildPromotions" and "PK__Builds" indexes both have low page counts, which may suggest that they are small and efficiently optimized, but also may not be necessary due to their limited size.

To address potential issues like the ones above, you can use SQL Server Management Studio to address them.

Under Tables > select table > Indexes > select index > Right-click > select Reorganize. See the Remove fragmentation using SQL Server Management Studio to learn more.