---
title: "SQL Server & Inedo Products"
order: 13
---

ProGet, BuildMaster, and Otter require [Microsoft SQL Server](https://www.microsoft.com/en-us/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](#installing-and-upgrading-sql-server)
* [Required Database Permissions](#required-database-permissions)
* [How to Use Azure SQL Server](#how-to-use-azure-sql-server)
* [SQL Server Management Tools](#sql-server-management-tools)
* [Migrating your Database to a Different SQL Server](#migrating-your-database-to-a-different-sql-server)
* [Querying the Database](#querying-the-database)
* [Advanced Maintenance](#advanced-maintenance)


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

| Component | Requirement |
| --- | --- |
| Storage | A minimum of 6 GB of available hard drive space |
| Memory | **Minimum:** Express: 512 MB, Other editions: 1 GB<br>**Recommended:** Express: 1 GB, Other editions: 4 GB |
| Processor Speed | **Minimum:** x64 Processor: 1.4 GHz<br>**Recommended:** 2.0 GHz or faster |
| Processor Type | x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support |

A more detailed breakdown can be found at [SQL Server 2022: Hardware and software requirements](https://learn.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-2022).

### Supported Versions of SQL Server
:::(Info)
As long as the SQL Server edition hasn't reached End-of-Life (see [Microsoft's SQL Server Support Policy](https://learn.microsoft.com/en-us/troubleshoot/sql/general/support-policy-sql-server)), your Inedo product will work with it.
:::

This includes the latest version of SQL Server, and others such as:
* [SQL Server 2022](https://learn.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-2022?view=sql-server-ver16)
* [SQL Server 2019](https://learn.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-2019?view=sql-server-ver16)
* [SQL Server 2016 and 2017](https://learn.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-ver16)
* [SQL Server on Linux](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup?view=sql-server-ver16#system2)

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

* [Azure SQL](https://azure.microsoft.com/)
* [Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.SQLServer.html)

### 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](https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker); see our [Docker Installation Guide](/docs/installation/linux/docker-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](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server?view=sql-server-ver16#individual-component-installation) has some pointers on how you can do this:

* [From the Installation Wizard](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-installation-wizard-setup?view=sql-server-ver16)
* [From the Command Prompt](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver16)
* [Using a configuration file](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-a-configuration-file?view=sql-server-ver16)
* [Using SysPrep](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-sysprep?view=sql-server-ver16)

### Upgrading SQL Server
:::(Warning)
SQL Server will not be automatically updated, nor will be upgraded via the Inedo Hub.  
:::

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](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server?view=sql-server-ver16) or use their [SQL Sever Installation Wizard](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server-using-the-installation-wizard-setup?source=recommendations&view=sql-server-ver16).

## 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.

:::(Info) (🔒 Locked out of SQL Server?)
Even though your an administrator on the Windows server, you may find yourself unable upgrade an Inedo product or  connect to SQL Server Express. Check out Microsoft's guide on how to [Connect to SQL Server when system administrators are locked out](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out) to remediate this.
:::

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](https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-create-server-level-portal-quickstart?view=azuresql).

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](https://learn.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms) 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](/docs/installation/sql-server/installation-database-backup) and restoring it on the new server. After it is successfully restored, you will need to to edit the [configuration file](/docs/installation/configuration-files) and change connection strings to point to the new server.

:::(Info) (Migrating to Azure?)
See our guide on [Migrating an On-Premise ProGet Installation to Azure](/docs/proget/installation/proget-migrate-instance-to-azure) for guidance on how to migrate the database.
:::

## 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](https://inedo.com/support) 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:

| Schema Table | Index | Avg Frag % | Page Count |
| --- | --- | --- | --- |
| dbo	- PlanVersions | PK__PlanVersions	| 87.2	| 47 |
| dbo	- BuildPromotions | PK__BuildPromotions | 50.1 | 2
| dbo	- Builds | PK__Builds | 32.3 | 2

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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16) to learn more.