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

Using SQL Server to Share Databases in Classic Products - OST QB DPC

Reference Number: AA-00221 Views: 9619 Created: 01/13/2016 01:27 pm Last Updated: 01/22/2019 01:18 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.

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?

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.

   

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.

Software

On Center Software's current release products work with the following versions of SQL Server.  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.

Microsoft SQL Server Versions

  • Microsoft SQL Server 2014 (Full or Express)
  • Microsoft SQL Server 2012 (including R2) (Full or Express)
  • Microsoft SQL Server 2008 R2 (Full or Express)

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

Note
On Center Software's products are not tested using Azure® SQL databases. While this solution, in theory, may work, at this time, On Center has not implemented this environment for testing. If you are interested in leveraging Azure SQL databases, please submit a Feature Request so we can track how many clients are interested. See Suggesting a Feature, Improvement, or Change to a Product.

Ensure you have adequate back-up 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 OST/QB/DPC, 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).

Naming Database

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.

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

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 may 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 access to specific databases if they are not the SysAdmin.  In the screenshot below, this user would be able to access/open the checked database only.

Performance

Most user would never know the 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).

Enabling Connections

The number one reason end users cannot connect to a network installation of SQL Server is that the Network Protocols for "TCP/IP" and "Named Pipes" have not been enabled after installing SQL Server.

On the server where SQL Server is installed, launch the SQL Server Configuration Manager

Under "SQL Server Network Configuration", open "Protocols for <the appropriate server>"

  • Right-click on "Named Pipes" and select "Enable"
  • Right-click on "TCP/IP" and select "Enable"

Users must also be "mapped" to databases to which they are to have access, see the above section on Users/Logins for more information.

Start the SQL Browser Service

If you are using SQL Express, you will have to start the "SQL Browser" Service.  This can be done from the SQL Configuration Manager or from Windows Services applet...


Windows Services


SQL Server Configuration Manager

Firewall Settings

Before your users will be able to communicate with the SQL, you will likely have to configure the firewall on your SQL server to allow incoming connection.  There are too many firewall providers for On Center to provide technical support for them, but there are two things you can check:

  • Make sure the SQL Server software (sqlserver.exe and sqlbrowser.exe) processes are excepted in your firewall (for incoming and outgoing connections).
  • Make sure you allow incoming and outgoing communication over any ports that those two processes use (excepting them should do this for you...).

Contact your firewall provider for information on performing these steps.  For details about any other firewall configuration required to deploy SQL Server, contact Microsoft.

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.

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

 

What's Wrong With This Article?

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