Creating a Microsoft SQL Server Database

Microsoft SQL Server is a robust server application that can provide a multi-user environment with enhanced security, performance and stability. If you need to share a database with more than 3 concurrent users, SQL is the way to go! 

 

This document details how to setup and create an SQL database for use with On-Screen Takeoff software. The steps are basic and easy to follow, however On Center Software, Inc. insists 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. On Center Software, Inc. provides these instructions as a resource for our customers but the processes detailed in this article are to be run at the customer's own risk. On Center Software, Inc. does not provide technical support for SQL server setups or assist in the administration, maintenance, configuration or troubleshooting of an SQL server or network. 

 

This document assumes that Microsoft SQL Server™ is already installed on a server running Microsoft™ Server 2003™, and the server meets the minimum system requirements for SQL Server™. If SQL is not currently installed, we recommend that a certified IT professional assist with the SQL setup and configuration.

Brief Overview

The SQL database is created from within On-Screen Takeoff. Windows Authentication should be used if possible.

 

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

• 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 password. Each user should have a unique SQL sign on whether Windows or SQL Authentication is used.

 

Step 1 - Install On-Screen Takeoff at the workstation

Do not install On-Screen Takeoff on the server - On-Screen Takeoff is not designed to be run over a network. The On-Screen Takeoff application must be installed on the workstation on which the following steps are completed.

Step 2 - Open all current Microsoft Access databases

Open any existing Access (*.mdb) database to which the new SQL database is to be synchronized.

Step 3 – Create the SQL Database

1. Start On-Screen Takeoff

2. Once application opens, click File > New > Database

3. Select Microsoft SQL Server and click

4. The Database Properties (SQL Server) dialog opens

    

   

    

5. Type the name of the SQL Server in the SQL Server field (On Center Software is unable to provide this information - contact your Network Administrator if you do not know it)

6. Select appropriate server authentication under Connect using

7. Type in the Login Name and Password (if using SQL Authentication)

8. Enter a name for the new Database in the Database field - database names cannot contain \ / : * ? ’r; < > | ; symbols

9. Requires login box is not active until after you create the database.  If you want to make the database password protected, proceed with the steps to create it, add appropriate estimators to the database and then come back to Database Properties and check the box for this feature

10. In the Measure mode field, select either Imperial or Metric as the system of measurement to use - see Units of Measurement for more information

11. Click OK - the database will now be available and listed on the Bid tab

     

Existing Microsoft Access (*.mdb) databases should be visible also.

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

Please follow these steps in Synchronizing Databases in order to copy the Master Tables from a previous database to this new SQL database.

Step 5 – Transfer Bids into SQL Database (optional)

To transfer bids from Access database to the SQL database, both databases must be visible on the Bids tab.

 

1. Expand the existing Access database (Source) to see bids it contains

2. Drag and drop the bid(s) from the Access database to the SQL database and release the mouse button

3. Click YES on the confirmation box (or Yes to All)

4. The bids are copied to the new database.

 

Step 6 – Archive Microsoft Access Database (optional)

Move the Access (*.mdb) database to a folder or CD that is not shared and not visible to user. This is an archive of previous work and should only be accessed for those purposes.

 

 

Synchronize a new database with an existing database 

Set database to Require Logins 

Set Database Options and Defaults