Microsoft SQL Server is a robust server application that provides a multi-user environment with enhanced security, performance, and stability. If you need to share your database or store it on a network, you must use SQL-type databases (you cannot use Access-type databases). Microsoft SQL Server is a separate product available from Microsoft, it is not distributed by ConstructConnect.

See Related Articles for important information related to using Microsoft SQL with Classic products.

See the previous article for instructions on setting up an Access-type (default) database if you are not sharing your database or storing it in a network location.

This document details how to setup and create an SQL database. The steps are basic and easy to follow, however On Center Software, Inc. recommends that only IT professionals with knowledge of the server environment and with appropriate expertise in server configuration and administration perform these actions. In some cases, a server may have been configured in a way that would require additional steps not detailed in this process.

This document assumes your server is running a supported version of Microsoft SQL Server™ that meets (preferably exceeds) the system requirements for SQL Server™. If SQL is not currently installed, we recommend that a certified IT professional assist with SQL Server installation and configuration.

Before proceeding, please the Related article for up-to-date information on using Microsoft SQL with On Center's products. On Center Software only tests and certifies certain versions of SQL Server for each version of the software - what worked in the past may no longer be supported.

Quick Facts about SQL Databases

• The SQL database is created from within Quick Bid - not from the SQL Management Console.
• There are several editions of SQL Server available from Microsoft - you need to determine the edition (Free, Full, Enterprise, etc.) that meets your particular needs. On Center recommends you contact a qualified IT Professional to discuss your needs based on the number of users, your network environment, how you bid jobs, and other software that may be running on your server.
• The versions of SQL Server with which On Center's products can be used
• There are two ways to authenticate to (log into, attach to...) a SQL Server:
• Windows Authentication passes the user's Windows logon and password to the SQL Server to make it easier for the users to use the SQL Server database (this is the recommended authentication method)
• SQL Server Authentication requires the SQL Admin create SQL Logins for all users and manage these user IDs and passwords separate via SQL Server Management Studio. On Center provides no assistance with setting up or maintaining user names and passwords. Each user should have a unique SQL sign on whether Windows or SQL Authentication is used.
• Once a new SQL database is created you can synchronize it with an existing Access-type database and copy Bids to it (see below for more information). Your database will be ready to use in minutes!

Creating a New SQL Database

Step 1 - Install Quick Bid on the workstation

It is not necessary to install Quick Bid on the server where SQL is installed, although for future database maintenance, you may want to (you will need to license it, at least to create and upgrade databases).

Step 2 - Open current Microsoft Access databases

Open the existing Access (*.mdb) database to which you will be synchronizing your new SQL database (to bring over your Master information such as Items, Assemblies, Estimators, etc.).

It's a good idea to run a Compact and Repair on the Access database before continuing - see the Related Article for details.

Step 3 – Create the SQL Database

Ensure your product is licensed before proceeding.

Click File > New > Database

Select "Microsoft SQL Server Database", the Database Properties (SQL Server) dialog opens

Type the name of the SQL Server in the SQL Server field (On Center Software is unable to provide this information - contact the SQL or Network Administrator for assistance). It is important that all users enter this Server Name exactly the same if you are using OST and QB interactively (On Center recommends entering the Server name in all lower-case letters, for consistency).

When using a "Named Instance" type in the name of the server followed by the instance, for example: "sqlserver\instance"

Select appropriate server authentication under Connect using (if you are using SQL Authentication, type in the Login Name and Password provided by your SQL Server Administrator).

Enter a Name for the new Database in the Database field

The "Requires login" option is only available after the database is created. To enable program-level security (require a password to open the database in OST/QB), follow the steps in this article to create the database, then add appropriate estimators to the database, and then come back to Database Properties and check the box for this feature. See the articles that follow for more information on securing your database using program-level security.

In the Measure mode field, select either Imperial or Metric as the system of measurement to use. All UOM boxes in the application are based on this setting.

Click OK

The database is created and listed on the Bids Tab (any existing, open Microsoft Access (*.mdb) databases will be visible also).

Step 4 – Synchronize SQL Database With Access Database (optional)

See the articles that follow for information on "synchronizing" databases. Synchronizing transfers Master Database Tables information from one database to another, no Bids or database preferences are copied during synch'ing - you will do that in Step 5.

Step 5 – Copy Bids from Access To SQL Database (optional)

To copy Bids from Access database to the SQL database, both databases must be visible in the Bid Navigator. Select the Bids you want to copy over, and then just drag-n-drop them from your old database to your new database. Verify the Bids are correct and match the originals, then go back and delete the originals to avoid any confusion.

Step 6 – Archive Your Microsoft Access Databases

Move the Access (*.mdb) database to a folder, CD, or Flash Drive that is not shared nor visible to your end-user(s) (you can also move them to a "ZIP" archive). Consider this an archive of previous work which should be accessed only for review of 'old' information. You want to prevent your users from using these old databases by accident.

Creating a Microsoft Access Database

Database Maintenance - Upgrading Databases