SQL Server Support for Advance Steel

Advance Steel fully supports all editions of SQL Server. This functionality enables workflows that require sharing Advance Steel databases with additional control.

You can configure Advance Steel to have databases centralized and administered in any edition of SQL Server. Advance Steel no longer needs the database files shared across the network and basic functionality is assured with per-user databases permissions.

In addition to SQL Server Express, Advance Steel supports full SQL Server as the centralized database source so that you can use Enterprise SQL Server solutions and not have to share database files across the network.

You can use Advance Steel with full SQL Server for sharing and administering the databases. This includes restricting some users from modifying Advance Steel databases.

You can customize Advance Steel databases using the Management Tools, granted you have db_datawriter and db_owner rights on said databases.

The SQL Server that serves the Advance Steel databases can be used both with integrated security as well as non-domain username and password. The SQL Server can be either SQL Server Express edition, Standard editions or Enterprise edition.

Use the following Microsoft documentation to start configuring your SQL Server:

After configuring the SQL Server instance, the next step is to copy the Advance Steel databases on the server and attach them to the configured instance.

When configuring the Advance Steel databases on an SQL Server instance, give the databases the names of the database files copied from the installation without path and without extension. For example:

Advance Steel requires db_owner rights for most of its databases to be customized, db_datawriter rights are not sufficient. The following functionalities require in addition to read access both db_datawriter and db_owner rights, each on the mentioned databases:

Drawing Style Manager - editing drawing styles requires the AstorAddin database
Drawing Processes Manager - editing drawing processes requires the AstorAddin database
Prefix settings - requires the AstorAddin database
Drawing Suite Manager - requires the AstorAddin database
Add user sections - creating user profiles requires the AstorProfiles database
Import-export scenarios that write in GTCmapping - requires the GTCMapping
Connection libraries - to save to connection library, it requires the AstorRules database
Quick Connect - to configure, it requires the AstorAddin database
Necessary views - to configure it requires the AstorAddin database
BOM editor - to edit and create bill of materials templates, it requires the AstorAddin database
Insert label - to modify manual label, it requires the AstorAddin database
Detail explode settings - requires the AstorAddin database
Add custom profiles (other than user profiles), save or delete compound and tapered beams requires the AstorProfiles database
Migration Tool requires db_datawriter and db_owner rights to the following databases: AstorAddin, AstorDetailsBase, AstorGratings, AstorProfiles, AstorRules, AstorSettings, GTCMapping
Management Tools for full customization functionalities requires db_datawriter and db_owner rights to the following databases: AstorAddin, AstorBase, AstorDetailsBase, AstorGratings, AstorProfiles, AstorSettings, GTCMapping

The database rights need to be specified per database and per user. They can be specified for an entire user group, it is not necessary to apply the settings for every individual user.

Note: Forgetting to give a user db_owner rights to one Advance Steel database will often cause the Advance Steel functionalities that require write access to that database to behave as if that particular user has read-only access even if the user has write access to that database.

Important:

Not all Advance Steel databases are shareable. The MTPrompts.mdf database is not shareable, it should not be configured on the SQL Server. This database will be local on each machine using SQL LocalDB.
Before any users with read-only permissions start using Advance Steel configured for the SQL Server it is required that a user with write permissions and configured for the SQL Server opens Advance Steel and Management Tools once. It is recommended this be performed as part of the setup process.
When migrating settings from a previous Advance Steel installation it is recommended to perform this only once, locally on a machine with the desired customized databases and the new, target Advance Steel version. This should be done before configuring the SQL Server and before starting to use Advance Steel properly with the SQL Server.

Note: The Advance Steel database AstorAddin.mdf is migrated by copying the .mdf file. The correct way to migrate the AstorAddin.mdf database from an SQL Server is to:

Copy the database AstorAddin.mdf file from the server into an installation of Advance Steel.
Note: This requires temporarily stopping the SQL Service.
Overwrite the local AstorAddin.mdf file with the one from the server.
Perform database migration from this installation of Advance Steel.

The final step is to configure Advance Steel on user machines to use the SQL Server instance.

On user machines, the DatabaseConfiguration.xml file should be modified to specify the SQL Server and the instance name instead of the LocalDB instance.

Another important change to the DatabaseConfiguration.xml file is to change the "AttachDbFileName" specifier to the "Database" specifier for all databases served by the SQL Server.

Below is an example of a connection string for an SQL Server:

For a description of the connection string syntax see Connection String Syntax and for connection string descriptions see SQL Server Connection Strings

Recommendations

Advance Steel 2024 officially supports SQL Server Express 2019 on user machines. It is recommended to use the database file format for SQL Server 2019 editions.
Note: Attaching a database file of an older version to an SQL Server instance of a newer version automatically upgrades the file format of the database file.
Although a newer version of SQL Server can be configured, please note that a downgrade of the database file format is not possible and the databases will not be usable on machines that have an older local SQL Server client installed. This includes fresh installations of Advance Steel 2024.
In order to use a newer version of SQL Server it is required to install the same newer version of SQL Server Express/ LocalDB on all user machines. It is also required to ensure the default LocalDB instance is created with the correct, newer version of SQL Server on all user machines.
You will not be able to share databases with other users who have older versions of SQL Server and/ or SQL LocalDB on user machines.
If you are using the same server to serve databases in parallel for multiple Advance Steel major versions that support SQL Server, it is recommended to keep them separated by SQL Server instances. For example, have one SQL Server instance for Advance Steel 2024 named AdvanceSteel2024.

Known Limitations

The behavior with read-only rights is not always smooth, sometimes the operation will show as having been completed and the changes have taken effect only to display the previous state or setting. For example, when editing a drawing style, although an error will be logged in database errors and in the command line, the drawing styles manager will erroneously show the addition having taken place. Selecting a different style and coming back to the recently edited drawing style - or closing and opening the drawing styles manager will correctly show that the addition has not been performed.
Management Tools will display the tables as editable even when having read-only access. Changing the values in a read-only table will fail displaying an error.
In bad configurations or when the SQL Server is not reachable, Advance Steel takes several minutes to start.
Note: In bad configurations involving only the instance name or the names of databases, Advance Steel will fail much faster.
The DbConnDiagnosticsCli tool will not treat connections to SQL Server.