Using SQL Server to Share Databases in On-Screen Takeoff and Quick Bid - OST QB DPC

AA-00221

Notes
On Center Software, Inc. provides this information as a resource for our customers but the processes detailed in this article require some advanced computer/networking skills for which On Center Software, Inc. does not provide technical assistance including SQL Server setup, administration, maintenance, configuration, or troubleshooting.

When implementing Microsoft SQL Server for use with On-Screen Takeoff® and Quick Bid®, On Center Software recommends consulting with a qualified IT professional to assist with determining requirements for installing and configuring SQL Server and optimizing the installation for your particular environment/needs. Share this article with whoever will be setting up and maintaining your SQL Server/Databases.

For specific information regarding System Requirements, installation guidelines, technical support, and any other concerns about SQL Server, please contact Microsoft directly. Remember that minimum System Requirements are just that - the bare necessities and will likely not provide acceptable performance. On Center Software products perform many read-writes per second and one of the best ways to ensure acceptable performance is to significantly exceed System Requirements for the server on which you will be installing SQL Server.

Azure-SQL and SQL-Server are not the same thing - Azure-SQL is not supported at this time, however SQL-Server running on an Azure Virtual Machine is supported.

Check Related Articles if your users are having problems connecting to the SQL Server.

Warning
Do not use SQL databases with Digital Production Control®. Before flipping a bid to DPC Mode, copy both the OST and QB bids into local, Access databases, relink them, then proceed. See Related Articles for more information.

Why Use SQL Databases?

There are two major reasons for using Microsoft SQL databases versus Access databases:

  • Your organization wants estimators to share a common database (there are several reasons why from seeing other peoples' bids to sharing a common set of Condition Templates or Items).
  • You or your organization wants to store database(s) in a network location (usually to make sharing easier).

Sharing databases and making them available to more than one person are great ideas to leverage On-Screen Takeoff and Quick Bid in your organization. The default Access databases do not support these goals, however, and you must use SQL if you are sharing databases (allowing more than one person to access a database at any one time), storing a database in a network location (on a network drive or server), or both.

This is a rather lengthy article, please take your time to read it carefully before proceeding.

What's the Difference between Access and SQL?

Microsoft Access is the default database-type when using OST or QB. There is nothing extra that needs to be installed and Access-type databases work fine for most clients. However, Microsoft Access is suitable only for a single user, storing the files on his or her local computer.

Topic Microsoft Access Database MS SQL Server Database
Setup Access databases require no additional software or setup. Everything you need is included with the software and Windows operating system. Microsoft SQL Server is a separate application not provided nor supported by On Center Software and may require additional cash investments (expense).
Networking Access databases are designed to be stored locally, not on a network. Using a network-stored Access database will cause problems at some point - On Center cannot restore databases if they become corrupted. SQL Server is typically installed on a network server. End Users access databases through the product. SQL can be supported over WAN connections as well as LAN.
Limits Access databases can be used by one user at a time. Sharing an Access database will cause your database to become damaged, and On Center is not able to fix a damaged database.
Databases must remain under 40MB to remain stable, this means you may be creating databases very often, depending on your bidding volume.		 SQL is designed for multi-user environments (shared-access). By design, SQL supports many concurrent users as long as your server hardware is up to the task.
Based on a particular environment, performance may be affected by adding concurrent users, but typically, end users will not notice any performance degradation.
Creating a new database each year is a good idea still - just to make it easier to navigate the Bids List for the users.
Stability and Performance Microsoft Access is a great solution for a single user who stores his or her database on their local hard drive. Access databases lose stability when pressed into harder service and risk being left in an unusable state if not closed properly.
Microsoft does not recommend Access for networked, shared, or other high-stress or high-transaction applications.
Daily/Weekly maintenance is always required.		 SQL is a robust database server that can accommodate the large number of transactions generated by On-Screen Takeoff and Quick Bid. Because the database is designed to be shared and/or networked, it is very stable and requires less maintenance. although routine maintenance is still required, just less often than with Access databases.
There is a limit, of course to how well any database is going to perform based on any particular environment and/or business practices.
Security You can set up security through OST/QB/DPC to limit what users can do (Access and SQL allow same program-based security "roles").
Each database can have different security settings and employee lists.
Logins are not tied to a users’ Windows profile login (although, they can be in Quick Bid).		 In addition to the program security you can set up on each database, SQL requires users to enter a username and password to connect to the SQL Server, this can be tied to their Windows profile, of course, depending on how you set up your SQL Server. On Center Software recommends using Windows Authentication when setting up an SQL database, that way, the users’ Windows security is transferred to the database and only valid users can access the database and the security is centralized.
Backups Database backups are handled by the application per settings stored on the local hard drive. You can set your database to automatically backup at set intervals or manually backup as you see fit. You can select the location for your backups as well, we recommend a network drive or cloud drive for backups. To backup an Access database, the program simply makes a copy of the database file (*.mdb) and puts it in the "Backups" folder. The SQL administrator sets up the backup and maintenance routines as dictated by Business practices. SQL is a transaction-based database and it has far more options for backing up and restoring databases.

Microsoft SQL Server

On Center Software's current release products work with SQL Server versions shown below. See the products' Information and Downloads pages for the current version by clicking on a product name above.

You can use the Express (free) version of Microsoft SQL if you are sharing a database between two or three estimators. If you are sharing with a larger staff, you will want to invest in the full version of SQL Server.

Virtualized MS SQL Server

In theory, it does not matter what type of server on which you are installing Microsoft SQL Server - whether the machine on which the software (MS SQL Server) is installed is a physical server, located within your building/complex, or a virtual server stored within your organization or in the cloud, as long as you have a dedicated and robust connection to the machine on which SQL Server is running, the programs should function fine. On-Screen Takeoff and Quick Bid are sensitive to database connections, though, so you must ensure they do not lose this connection, even for a moment, or the program may stop responding or you may lock yourself out of a project or database.

As of the date this article was last updated, we have not formally tested the products using a database stored in a cloud-virtualized server.

On Center Software's products are not tested using Azure® SQL databases (which is different than Microsoft SQL Server installed on virtual machine hosted by Azure). The products are not able to connect to cloud database at this time. If you are interested in leveraging Azure SQL databases, please submit a Feature Request so we can track your interest.

See Suggesting a Feature, Improvement, or Change to a Product.

Microsoft SQL Server Versions

  • Microsoft SQL Server 2019 (OST 3.98.05.14 and newer, only)

Check with Microsoft for System Requirements for running SQL Server, visit https://www.microsoft.com/en-US/sql-server/.

Note

We have not completed testing Quick Bid 4.99 on SQL 2019, but there are no technical reasons it wouldn't work. We completed a full test of SQL 2019 and On-Screen Takeoff 3.98.05.14 successfully.

Newer versions of Microsoft Server (operating system) come configured to block/drop incoming "ICMP" requests that are necessary for our software to connect to a SQL Server. See the following Microsoft technical article for details: https://docs.microsoft.com/en-us/windows/security/threat-protection/windows-firewall/create-an-inbound-icmp-rule.

Ensure you have adequate back-ups and maintenance plans for your SQL Server. On Center Software products cannot backup SQL databases - this is performed by your network administrator using SQL Server Administration tools.

You will need Microsoft SQL Server Management Studio to perform any database maintenance or configuration changes. This product is available from and supported by Microsoft.

Warning

If you are using an older version of On-Screen Takeoff or Quick Bid, before you upgrade your On Center Software product(s), you must upgrade your SQL Server to a supported version (below) before installing the product upgrades. If you try to upgrade a database to a newer version of OST or QB that is no longer compatible with your version of SQL Server, your database will become unusable.

Hardware

You will need to work with a qualified IT Professional to determine your hardware needs.

We have found that a minimum of 24GB of RAM on the server running SQL is a must (Windows and SQL Server use up a lot of system resources, there really is not "too much" when it comes to hardware).

As always, the more "duties" you give a Server, the more hardware you will have to throw its way. Saving a few dollars on hardware is unwise because it can cause performance issues for anyone using that server (not just On Center's products).

Performance

Most users should not notice a difference between using an SQL database versus an Access database, as long as you take the time to set up your SQL Server properly.

If your end users report performance issues, the server on which SQL Server is running may need additional RAM installed (or an upgrade to the processor or hard drive), the internal network may need to be optimized, or the databases (and/or log files) may require routine maintenance (shrinking and/or Compact/Repairing). On Center Software applications perform many read/writes to the database every second and require a fast network connection to provide acceptable performance. Just running Windows and SQL on a server, not even using them activately, can quickly consume 20, 30, even 40 GB of RAM. Invest as much as you can when setting up your servers and test before rolling out any SQL Migration.

Once implemented, it is essential to monitor maintenance and performance to ensure the Microsoft SQL Server is operating at peak performance. If your IT professional has any questions regarding accessing a SQL database with our software, they should contact On Center Software Technical Support at 866-689-5687. For questions about SQL Server itself (including installation, configuration, troubleshooting, optimization, and maintenance), contact Microsoft directly. On Center Software is unable to provide support for the installation, configuration, or maintenance of Microsoft SQL Server (it is not our product, it is Microsoft's). If you need assistance, please contact Microsoft or a Certified SQL Server Professional for assistance. It is important to monitor System Resource usage on your SQL Server - if the SQL Server uses an inordinate amount of RAM, the SQL Server Service probably needs to be restarted or the server itself may need to be rebooted (off hours, of course).

Users Accounts/Logins/Security Roles

Users must connect to your SQL Server to access databases on that server. When you set up your SQL Server, you will need to decide whether you are going to use Windows logins or setup SQL-specific logins for your users. On Center Software recommends using Windows Authentication for logging into the server because this makes it easier for your end users as they will not have to remember a separate SQL Server login.

Warning
Users must access the SQL Server/databases with unique (individual) logins - do not use a shared login as this will cause problems with the applications' bid-locking mechanism. Never allow users to use the built-in Administrator accounts.

Users must be assigned the following Server Roles:

To create databases:

  • sysadmin
  • public

To upgrade existing database to a new version:

  • dbowner
  • public (selected by default)

If a user with insufficient rights attempts to upgrade an SQL database, they will cause the database to become unusable or unstable.

To use (open/work in) a database:

  • db_datareader
  • db_datawriter
  • public (selected by default)

These roles must be applied to each database to which a user is granted access in the "User Mapping" screen.

User Mapping

You must grant each user (who is not a SysAdmin) access to specific databases.

To adjust Mapping, open a User's profile and click "Mapping" in the left-side panel.

Place a checkmark in the right-side panel for each database he or she can open/edit.

Moving your SQL databases to a different SQL Server

Please refer to the following Microsoft Support articles for assistance with moving your SQL Server database from one server to another.

Article ID: 314546 - How to move databases between computers that are running SQL Server

Article ID: 224071 - How to use Detach and Attach functions to move SQL Server databases

These links open to Microsoft's Web site - SQL is their program and it is their privilege and responsibility to provide technical assistance with its use. On Center Software cannot provide technical support for Microsoft's products any more than they can provide technical support for our products.

Please contact Microsoft or a Certified SQL Server Professional for assistance.

Where should you store image files?

Using SQL-Server implies you want to share a database and allow all your users to see and access each others' bids. To that end, when setting up an SQL Server, it's important to designate a shared space on your network were all images files should be stored.