These are the steps to set up an SQL server for the centralized databases.
The goal of this post is to provide the information needed to set up and configure a Centralized SQL Server to host your Cost and DESTINIData databases.
Very Important: A Company is responsible for providing its own version of Microsoft Standard SQL Server, if you already have an instance set up then we just restore the database in that instance or we can create a new instance. A Centralized Server can be set up on one of the following:
A. On-Prem Windows SQL Server
B. Azure Database environment (Serverless, Managed instance, Windows VM)
C. AWS SQL Server environment
The image below is how the on-premise or cloud base server should look with both the DESTINI Cost database and a DestiniData (jobs) database.
The DESTINI application does not need to be installed on the SQL server, just the database and SQL Server Management Studio where you can get the latest version from this link.
Setting up an on-premise server to host the Cost and DESTINIData Databases
On-Premise Minimum Server Requirements
- Operating System: Windows 2012+
- Processor: 2 GHz or Higher, 4 cores, 64-bit
- Memory: 32 GB+
- Disk Space: 256 GB+
- Database: MSSQL Standard 2016 or higher/w SQL Server Management Studio
- Additional System Requirements can be found at this link
This video goes through the entire process of restoring the databases, setting up and configuring the databases with permissions, and showing how to connect to those databases from within the Estimator Application.
Follow each task below to set up and configure the server properly:
- Setting up a SQL Instance
- Setting up the DESTINIData database
- Configuring the login credentials on the centralized server
1. Setting up a SQL Instance
The instructions in the link setting up a Becktechnology SQL Express Instance have the basic instructions to properly set up the instance. You can use your own naming convention for your organization, by following the steps.
SQL Express has limitations that can be found in the System Requirements article at this link.
If you already have a SQL Database instance make sure the full text for the search feature is enabled and we can just restore the databases and configure the security settings in step 3.
2. Downloading and restoring databases
The standard databases can be found at this link Commercial Databases. The five databases in that folder will need to be restored to the SQL Server ( Estimator Training Cost, Estimator Training Projects DB, Production Cost DB, Production DESTINIData DB, Sandbox Cost). The Production databases should contain your Company's name such as Becktech Production Cost DB, Becktech Production Estimates DB. Here is the link on how to restore the databases using SQL Server Management Studio.
These are Standard databases that may need to be verified or upgraded before the application can connect.
Cost databases upgrade can be found here - Cost Database upgrade/verification.
Projects database upgrade can be found here - Project database upgrade/verification.
3. Setting up and configuring database permissions
The next step after the database/s is restored is to create and configure the database/s login credentials on the Centralized Server. Click on the link to find the information to set up and configure Logins and permissions.
4. Firewall Rules
We have seen instances where the Firewall will need to be configured correctly to allow the Estimator to connect to the Central server.
UDP ports 1433 and 1434 are needed to allow for Named SQL Server Instances. If you are not using the standard SQL ports mentioned above, then you will need the configure the dynamic port.
Azure Centralized SQL Database setup
In Azure, we currently don’t support an Azure SQL database restored in Azure; only an MSSQL database restored on a VM in Azure. The reason for this is that some of the mechanisms we use for permission and enumerating the list of databases do not work on an Azure SQL database. However, we will incorporate the ability to use Azure SQL databases in the future. Click the link - How to set up databases in Azure for instructions.
Here are our recommended starting points to set up a Windows VM in Azure to host the databases.
AWS Centralized SQL Database setup
In Amazon Web Services (AWS) you can set up a Relational Database Server (RDS) in the cloud similar to an On-Premise server and set up an Instance of Microsoft SQL Server to host both Cost Database and DESTINIData. Here is documentation on how to set up AWS SQL Server.
One of our users documented the AWS steps in this article here that you can use as a guide.
Here is the starting point for SQL Database instance in AWS.
As the database increase, the size of the storage will need to be increased.
