On Center Home | MyOnCenter Portal | REQUEST A DEMO | CONTACT | SALES: 1-866-627-6246
 
 
Advanced
How to Use Search Effectively
Login


Tools Add
Table of Contents

Best Practices and Requirements When Using SQL Server - OST QB DPC

Reference Number: AA-02919 Views: 1177 Created: 02/27/2019 01:26 pm Last Updated: 02/27/2019 01:28 pm 0 Rating/ Voters
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.

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

When you create SQL databases from On-Screen Takeoff or Quick Bid, by default the "Recovery Method" is 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.

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

Discussion
  • There is no feedback for this article.
Info kBase Article Feedback

If you need Technical Support or want to submit a Feature Request, click one of the buttons below, do not use this form.

 
request support Request Support submit feature request Feature Request

 

How Does This Article Not Measure Up?

Your full name: Your Email: Your feedback on this article:

 

Something Wrong with this Article? Let us Know! Copyright 2018 - On Center Software, Inc. All Rights Reserved.
Be sure to rate this article 5 stars if you found it helpful!.