On Center Home | MyOnCenter Portal | Start an On-Screen Takeoff Trial | Request a Quick Bid Demo | Contact Us | SALES: 1-866-627-6246
   
 
Learn more about Searching
Looking for help for one of ConstructConnect's Other products? Login


Tools
Table of Contents

Best Practices and Requirements When Using SQL Server

Views: 6555 Last Updated: 07/14/2023 10:06 am 0 Rating/ Voters
Be sure to rate this article 5 Stars if you find it helpful!
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.

See Using SQL Server to Share Databases in Classic Products for more details and requirements.

Best Practices and Requirements

Database Naming Conventions

Until a database is opened, On-Screen Takeoff/Quick Bid have no way of knowing if it is an OST/QB database - so it is important that your Organization names its databases logically.

For example, a logical name for an On-Screen Takeoff database would be "OST2015" or "OST Projects 2015". Including the year is a good idea because even if you use SQL, it is a good idea to create a new database at least once a year, just to make it easier to keep track of Bids/Projects. Be sure users understand which database(s) they should be using and if necessary, "drop" those that are no longer supposed to be used so users do not get confused.

Recovery Model (Recommend Setting to "Simple")

When you create SQL databases from On-Screen Takeoff or Quick Bid, by default the "Recovery Method" may be set to "Full". This means that SQL logs every transaction and keeps that log indefinitely (until you manually clear it). The benefit of "Full" logging is that you can restore the database to more specific points rather than only to set backups. Over time, however (a very short time if there are multiple users), SQL can create a log file so large that SQL spends the majority of its time and resources managing that log instead of being able to service databases.

One way to prevent this is to use the Simple Recovery Model and then make more frequent backups of your database.

The Simple Recovery Model provides the simplest form of backup and restore. Backing up the database is easier because the transaction log is quite small; however, as there are no log backups, a database can be restored only to the end of the most recent backup of the database itself (no transactional backups). If a failure occurs, updates that are made after the most recent backup of the data are lost. So, you will want to create more frequent database backups, daily or twice daily is recommended.

To verify and/or change the Recovery Model of your database, follow the steps below (you will need Microsoft SQL Server Management Studio to perform this operation, your version may differ slightly).

Launch Microsoft SQL Server Management Studio (Management Studio is a free utility provided by Microsoft that allows you to manage and maintain SQL databases using a GUI, it is not necessary if you are comfortable using a command prompt - this is entirely outside our Support, however.

In the Database Properties window select Options and verify the Recovery model is set to Simple - if it is not, use the drop-down list and modify it

If your company cannot set the Recovery Model to "Simple" due to IT practices or requirements, you must regularly and manually shrink the log file to keep its size below 100MB. Shrink is a utility accessed from within SQL Server Management Studio - please see your SQL documentation for details on using Shrink.

Collation Settings

Warning
Incorrect Collation settings will prevent On-Screen Takeoff and Quick Bid from being able to create Projects and save data properly. Do not set Collation settings to anything that forces Upper Case.

During SQL Server Setup, your "Collation" settings are selected based on the Windows system locale (Regional Settings) of the computer where SQL Server is being installed.

On Center's products are designed to use the English (United States) Regional and Language Options. Other Regional settings can be used but may require modification to their default settings. The default English-language (US) collation is "SQL_Latin1_General".

To verify and/or change the Collation settings of your database, follow the steps below (you will need Microsoft SQL Server Management Studio to perform this operation, your version may differ slightly).

Launch Microsoft SQL Server Management Studio - the example below shows the Express edition which is a free download from Microsoft, you will see a screen similar to the one below

Under Databases, right-click the database that needs adjusting and select Properties.

In the Database Properties window select Options, and verify that Collation is set to the proper Regional and Language option for your computer/locale - if it is not, use the drop-down list and modify it

"SQL_Latin1_General_CP1_CI_AS" is the default setting for English-based regional settings. If the incorrect Collation type is specified, certain font families will not display correctly when using the Annotation tools in On-Screen Takeoff. For example, if you are using Greek based font, your Collation should be set to Greek_CI_AS, otherwise, the Greek font will display as incorrect characters. For additional information on Regional and Language options contact Microsoft directly.

Where should we 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.

  1. This network location must be mapped as a lettered drive for all users (your IT department can do this automatically via login scripting).
  2. The drive letter must be the same for all users.
  3. When downloading plans, store them in this network location. (You can adjust your On-Screen Takeoff Folders assignment to point to this network "drive" automatically, see OST - 19.06 Program Options - Folders (Specifying Where the Program Saves Files) for details.)
  4. When creating a project and adding plans to it, pull them from this network "drive". (See OST - 03.04 Filling out the Cover Sheet - Step 2: Adding Plans to a Bid Using the Plan Organizer for more information.)
  5. Ensure that the Bid Wizard is disabled (the Bid Wizard copies image files to the local machine, this will cause issues in a shared environment). (See OST - 01.04 Using and Disabling the Quick Start Wizards for details.)

Rules for the Interaction between Quick Bid and On-Screen Takeoff

This information is published in the Quick Bid User Guides, but it is important enough to include here.

DO'S

  • Use the latest versions of both On-Screen Takeoff and Quick Bid to ensure correct operation. Download the current shipping versions at www.oncenter.com/support.
  • The SQL Server name and database names (both programs) must be spelled identically for all users or one user will break the interactivity for another user.
  • When working with On-Screen Takeoff and Quick Bid, only one On-Screen Takeoff database should ever be linked to a Quick Bid database and vice versa, even if you are using SQL database. Several Master tables are synchronized between the programs' databases and can become corrupt if databases are connected in a multi:one or multi:multi environment.

Warning
To clarify: only bids from a single On-Screen Takeoff database should be connected to a single Quick Bid database.

  • Only one estimator should work on an Interactive Bid at any one time - whether in On-Screen Takeoff or Quick Bid to avoid bid-locking errors. One estimator can perform the takeoff and another estimate the Bid just not at the same time (the user who opens the Bid in OST tries to put a lock on the project in Quick Bid and vice versa).
  • Create new Bids, Alternates, and Change Orders in On-Screen Takeoff.
  • Create, Modify and Delete Conditions, Bid Areas, and Typical Areas in On-Screen Takeoff. Once a job in On-Screen Takeoff is linked with Quick Bid,  the ability to add/delete Conditions or change Condition quantities, or add/change/delete Bid Areas and Typical Areas is disabled in Quick Bid.
  • Update Condition quantities in On-Screen Takeoff - On-Screen Takeoff passes this information to Quick Bid.
  • Add Material and Labor to Conditions in Quick Bid.
  • Add Equipment, Subs, Markups/Indirect Expenses, and all other costs in Quick Bid.
  • Use the "Job No." field to customize the number for your project (not the "Bid No.").

DON'TS

At On Center, we avoid saying "Don't" as much as possible - but the following points are critical.

  • Never link more than one OST database to any QB database (or vice versa). As mentioned above, Interactivity should be a 1:1 relationship, for example, all Bids in the On-Screen Takeoff database "Takeoff 2012" should be connected to only one Quick Bid database, let us say this database is called "Estimates 2012". No bids in the  "Estimates 2012" database should ever be connected to an On-Screen Takeoff Bid residing in any other database other than 'Takeoff 2012". This becomes a little tricky when you are switching from one database to another, say at the end of a year. When the time comes, disconnect all bids you are going to copy to new databases before copying, then use Quick Bid to reconnect and restore connectivity. (This is all covered in the Quick Bid User Guides).
  • Never share Microsoft Access databases between users. Never connect a shared SQL database to a local Access database.
  • Never open an Interactive Bid (in either program) if one of the programs is in "No-License" mode or not installed, this will cause the interactive connection to partially break causing issues later on.
  • Don't change the Bid Number after the Bid is created. There is functionality that reduces the chance that two Bids will be numbered the same when creating bids when multiple users are sharing an SQL database. This technology does not prevent two users from renumbering their Bids at the same time which can cause problems with both Bids (use the Job No. field if you must enter a custom number).


Product documentation (user guides) describes functionality in the latest version of each major release and may not match the functionality in the version you are using. Please check the Product Information and Downloads pages by clicking one of the product buttons above.

Something Wrong with this Article? Let us Know! Copyright 2023 - On Center Software, Inc. by ConstructConnect - All Rights Reserved.