SQL Server Implementations With Inedo Tools

KB#1167: Last Updated January 7, 2021

Recommended SQL Edition

All Inedo software merely requires you to have at SQL Server 2008 or newer. We keep our tools flexible so that you can keep the version best suited for your systems.

Here are some minimum system requirements for SQL versions that we support.

SQL Server 2016
SQL Server 2014
SQL Server 2017 for Linux
SQL Azure – We support this version and since it is cloud-based no system requirements are needed.

Note: SQL Server Express is free and can be installed as an Inedo instance during the installation process. SQL Server express has performed fine for nearly all organizations, and the 10GB limit can be mitigated through retention policies. However, this is best suited for evaluation purposes only. If you already have sql server licensed you should consider working with your DBA team to get our tool’s database installed and configured, so they can oversee and protect the data as with other databases.

When to upgrade SQL server

This should depend on current business needs. Our tools do not demand specific versions and should only prompt an upgrade if clients version is below our minimum specified version.

Managing Index Fragmentation

Index maintenance jobs can successfully cleanup indexes and can be scheduled. However, this affects all indexes in a database even the indexes that are not in need of this type of action.

An option we suggest is that you REINDEX/REORGANIZE Indexes that reach certain fragmentation thresholds. You can use this SQL script to help you identity the indexes that may be causing some SQL slowness:


        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

Now that you have pinpointed where your potential problems are you can address them with SQL Server Management Studio, under Tables > select table > Indexes > select index > Right-click > select Reorganize. See the Remove fragmentation using SQL Server Management Studio to learn more.

SQL Server Database backups

In typical scenarios we recommend a full back-up every 24 hours with a differential every 6 hours. However, your backup schedule will be dictated by several factors. Two of the most important are your data’s criticality, and how busy the database is, which can be thought of as the number of transactions per hour/ day. Consider these predefined sample schedules as examples:

Lazy – Full backup every 168 hours (weekly), Differential every 24 hours: This is ideal for a database with just a few transactions per week, or one whose importance is not mission-critical. The loss of a few hours of data can be tolerated or easily recreated. A real-life example would be a HR/ payroll system for a very small company – very infrequent changes in personnel changes and easy ‘recreatibility’ results in little need for frequent backups.
Daily simple – Full backup every 24 hours: In this type of backup you simply take a daily full database backup. This is usable in situations where transactions are more frequent than ‘Lazy’ above, but transactions are still recreatable from manual records or other sources.
Daily 4 times – Full backup every 24 hours, Differential every 6 hours: this is suitable for databases that incorporate several more changes during a normal working day, and are thus much busier than ‘Daily simple’ databases.
Thorough – Full backup every 24 hours, Differential every 4 hours, Transaction log every 1 hour: now we are into the realms of databases whose loss can be considered catastrophic or at the very least serious hindrances to business operations. Such databases may host applications writing several dozen or hundred transactions per hour, so even waiting the 4 or so hours between differential backups means a potential loss of a significant chunk of the day’s transactions. Additionally, there is likely no manual recording of transactions, so trying to recreate lost transactions would be a major pain – hence the need for transaction log backups.

SQL Server Azure Backups

SQL Database automatically creates database backups that are kept between 7 and 35 days, and uses Azure read-access geo-redundant storage (RA-GRS) to ensure that they are preserved even if the data center is unavailable. These backups are created automatically and at no additional charge.

Connect Our tools to SQL Azure

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

Database Migrations

If you are going to be migrating your Inedo software database to a different SQL Server we recommend that you perform a full backup the database and restore it on your new server. After it is successfully restored you will need to edit the configuration file and change your connection strings to point to the new server.

Querying The Database

We support querying our databases but we do not offer support for writing to them. Our tables, views, and stored procedures are closely linked with foreign keys and indexes. Adding or changing records may lead to instability within the applications.

Support