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. SQL is not provided by On Center Software, it is a separate product available from Microsoft.
See CLS - Using SQL with Classic Products 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. Microsoft Access® databases are the default database-type used by On-Screen Takeoff and require no additional software to create or use.
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.
We provide these instructions as a resource for our customers, but you are solely responsible for implementation, maintenance, troubleshooting, and configuration of SQL Servers and databases. On Center Software, Inc. does not provide technical support for SQL server setups, assist in the administration, maintenance, configuration, or troubleshooting of an SQL server performance, network connectivity, or any other issues directly related to SQL Server. For assistance with managing your SQL Server installation, please contact the vendor from whom you purchased SQL Server or contact Microsoft directly.
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.
- The SQL database is created from within On-Screen Takeoff - 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 are listed in Using SQL Server to Share Databases in Classic Products
- 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!
Only users with appropriate permission (SQL Server
Administrators and
dbo's) should attempt to create or upgrade SQL databases. Attempting to modify an SQL
database if you do not have sufficient SQL rights may render the
database unusable.
Creating a New SQL Database
Step 1 - Install On-Screen Takeoff (on Your Workstation)
If you haven't already, you need to install On-Screen Takeoff on your computer.
You do not need to install On-Screen Takeoff on the server where SQL is installed, although for future database upgrades and routine maintenance, it's not a bad idea.
On-Screen
Takeoff is not a Client-Server application - the program cannot be used in this manner and must be installed on
each user's workstation.
Step 2 - Open Your Current Microsoft Access Database
Open the existing Access (*.mdb) database to which you will be synchronizing your new SQL database (to bring over your Master information such as Styles/Sets, Condition Types, Job Statuses, Employees, etc.).
It's a good idea to run a Compact and Repair on the Access database before continuing, see Database Maintenance - Compact and Repair for details.
It is recommended that you synchronize only one
database to any other
database. Synchronizing multiple databases into a single
database can cause duplicated entries.
Step 3 – Create the SQL Database
Ensure your product is licensed before proceeding.
In On-Screen Takeoff 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). Again, On Center does not know this information, you will have to check with your SQL Administrator.
Enter a Name for the new Database in the Database field
Database names must not contain special characters such as the following symbols: \ / : * ? ’ ; < >; only standard alpha-numeric English characters should be used. (Anything that is not an English letter or number should be avoided when naming databases or projects.)
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), 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. Securing your database is covered later in Related Articles.
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. Do not mix Imperial and Metric Bids within the same database, especially if you are using OST and QB interactively. (In On-Screen Takeoff, you can store Imperial and Metric Projects/Bids in the same database, but we recommend against doing so - it is better to create separate Imperial and Metric database.)
Click OK
The database is created and listed on the Bids Tab (any existing, open Microsoft Access (*.mdb) databases will be visible also).
There are no Auto-Backup or Auto-Compress options for SQL databases because backing up must be performed on the SQL Server, using software specifically designed for those tasks (Microsoft SQL Server Management Studio). Your SQL Administrator is responsible for backing-up and "Shrinking" all SQL databases on a regular basis in accordance with good business practices. Regularly running the Compact and Repair routine from On-Screen
Takeoff, described in the Maintenance article that follows, is required to keep your
database in tip-top shape.
Step 4 – Synchronize SQL Database With Access Database (optional)
See Database Maintenance - Synchronizing Databases for information on "synchronizing" databases.
Synchronizing transfers Master Database Tables information from one database to another, but your Bids and database preferences are not copied during sync'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 on the Bids Tab (both databases must be open...).
Select the Bids you want to copy over, and then just drag-n-drop them from your old database to your new database. See Copying and Pasting Bids from One Database to Another for details.
Verify the Bids are correct and match the originals, then go back and delete the originals (if you want) 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.
If you leave the Access (*.mdb)
database visible, you risk your end users accidentally opening the outdated Microsoft Access
database and creating Bids in the wrong
database or modifying archival Bids. Also, leaving the Access databases available increases the risks of 'cross-connecting' more than one OST
database to a single QB
database and vice versa.
Each user must type the name of the server exactly the same when they "Find"/Open an SQL
database, case matters. We recommend using all lower-case letters, just to be consistent. When users type the server name differently, they will cause connection issues for interactive bids.
Creating a Microsoft Access Database