Create and configure the FactoryLogix Database

ADMINISTRATOR

This topic explains how to create, configure, and format a FactoryLogix Database on a SQL server.

Important

These instructions assume that Microsoft SQL Server is already configured and the person performing this installation understands how to administer a SQL database server.

Before you begin, do the following:

• Verify all necessary Microsoft Windows accounts are created and granted the appropriate rights on the FactoryLogix Database Server (see Complete the pre-installation activities).

• Install the latest Microsoft service packs and security patches.

• Obtain the Aegis Software Products setup files and any available patches from Aegis Technical Support.

• Ensure the user name and password you use to connect to the FactoryLogix Database with FactoryLogix Server Management has the dbo role assigned (SQL Authentication) or is in the local administrator group (Windows authentication) of the SQL database server. All tables following installation must be owned by the dbo role.
  

Note

When you create the FactoryLogix Database, you will name it FactoryLogix and use the default database attributes. You should permit the database to grow automatically as needed. Putting size constraints on the database could cause problems later if the database reaches size limits.

After the FactoryLogix Database is created and permissions are set, you need to structure the database. The FactoryLogix Database Utilities tool uses scripted SQL statements to create the necessary tables, fields, and indexes in the new database. (You can access the tool from the FactoryLogix Server Management application.)

Create the FactoryLogix Database

1. Log onto SQL Server with a Windows account that has local administrative rights.

2. Open SQL Server Management Studio.

3. In the Object Explorer, expand the appropriate SQL Server computer/instance name, right-click Databases, then select New Database.

4. In the New Database dialog, enter the name FactoryLogix in the Database name field.
   
   

   

   
   

5. Drag to expand the dialog or use the horizontal scroll bar so you can view all available dialog fields.

6. Under Path, enter the path to be used for data and log files.

7. Under Autogrowth/Maxsize, select the Browse (...) button.

8. In the Change Autogrowth dialog, make sure the Enable Autogrowth check box is selected (default), then set File Growth to 10%.

9. Under Maximum File Size, select Unlimited, then select OK.
   
   

   

   
   

10. Select OK to return to the New Database dialog, then select Add to create the database.

Set up database security with Windows authentication

1. Open SQL Server Management Studio.

2. In the Object Explorer, expand the appropriate SQL Server Computer/Instance name, right-click Security, then select New > Login.

3. In the Login - New dialog, select Windows Authentication.

   
   

   

   
   

4. Specify the appropriate Windows domain logon account in the Login name text box.

5. Select FactoryLogix from the Default database drop-down, then select OK.

Set up database security with SQL authentication

1. Open SQL Server Management Studio.

2. In the Object Explorer, expand the appropriate SQL Server computer/instance name, right-click Security, then select New > Login.

3. In the Login - New dialog, select SQL Server Authentication.
   
   

   

   
   

4. Enter aegis for the Login name.

5. Enter a password in the Password field, then enter the password again in the Confirm password field.

6. Clear the Enforce password expiration check box.

7. Select FactoryLogix from the Default database drop-down, then select OK.

8. Select User Mapping under Select a page.
   
   

   

   
   

9. Under Users mapped to this login, select FactoryLogix.

10. Under Database role membership for: FactoryLogix, select db_datareader, db_datawriter, and db_ddladmin. (Public is selected by default and should remain selected.)
    

Note

If you are creating both a Windows account and a SQL account, ensure that the roles for each account are identical.

Structure the database

The following procedure creates all database tables, indexes, stored procedures, and triggers needed for the FactoryLogix system to function properly.

1. Log onto the server or a machine where FactoryLogix Server Management is already installed. (The login account should have administrative rights to the database server if you are using Windows authentication.)

2. Open FactoryLogix Server Management from the Windows Start menu.

3. Select the Database Connections button.
   
   

   

   
   

4. In the upper-right corner of the Database Connections window, select the Add new database connection button.
   
   

   

   
   

5. In the Add New Database Connection dialog, enter FactoryLogix in the Database Connection Name field.
   
   

   

   
   

6. Under Server Name, enter or select FactoryLogix SQL Server.

7. Under Log on to the server, select the applicable authentication type (Windows or SQL).

8. In the Select or enter a Database Name field, select FactoryLogix, then select OK.

9.  In the Database Connections window, select the FactoryLogix connection, select Set Default to make this the default database connection, then select Save.
   
   

   

   
   

10. Select the Home button at the top of the window, then select the Database Utilities button.

11. In the Database Utilities window, select FactoryLogix from the Select Target Database drop-down, then select Create New Target Database Schema.
    
    

    

    
    

12. When you see the message The Target FactoryLogix database was created successfully, select OK.
    

Note

If you are using Windows authentication, the FactoryLogix system will use the credentials of th euser who is logged into the system at the time that any dtabase connection is required. When using a specific user name and password (SQL authentication), you need to supply a user name and password in FactoryLogix Server Management. The user name will be used for every database connection.

Verify the database creation and structure

1. Open SQL Server Management Studio and connect to the server.

2. Open the versions table in the FactoryLogix database.
   
   The table should contain an entry that corresponds to the current major version of the software being installed. This is the last operation performed by the structuring procedure and indicates a properly-created and structured database.
   

Note

If errors are generated during database structuring or there are no entries in the versions table, contact Aegis Technical Support.

Grant permissions for the FactoryLogix stored procedures

1. Open SQL Server Management Studio.

2. In the Object Explorer, expand these folders: Databases > FactoryLogix > Programmability > Stored Procedures.

3. Right-click the DeleteECAD procedure, then select Properties.

4. In the Stored Procedure Properties - DeleteECAD dialog, select the Permissions page.

5. To grant permissions to a user, select Search.

6. Click the Browse (...) button to display the list of users or roles.

7. Select the previously-configured Aegis user to whom permissions should be granted, then select OK twice to return to the Stored Procedure Properties - DeleteECAD dialog.

8. On the Explicit Permissions tab, select the Execute check box in the Grant column, then select OK.
   
   

   

   
   

9. Repeat Steps 3-8 to set the same permission for each of these Stored Procedures: 
   

   • CheckoutBatchDataCollectionDocumentLink

   • CreateArchiveQueue

   • DeleteECAD

   • FinishBatchRouteTransaction

   • FinishRerouteProcessFlowTransactionStatuses

   • GenerateSerialNumberDefinition

   • StartBatchRouteStatus

   • StartBatchRouteTransaction

   • StartRerouteProcessFlowTransactionStatuses

Alternate method to create a FactoryLogix database

It is possible to create a FactoryLogix Database from a backup (*.bak file) of another FactoryLogix Database. An Aegis Applications Engineer or trainer may prepare a database ahead of time, create a backup, and migrate it to a customer site.

Configure SQL Server memory

By default, SQL Server tends to use as much RAM as is allowed by the system. On servers operating components in addition to SQL Server, we recommend configuring SQL to consume a fixed amount of RAM—usually one half of the total available in the system.

SQL Server maintenance plan

Proper database maintenance is very important. If you encounter database problems, you need to be able to recover the database as quickly as possible with minimum data loss. We recommend a good database maintenance plan to ensure that your transaction logs are properly maintained and don't consume all your available disk space. There are many ways that maintenance plans can be set up, but the goal is to create a simple plan that meets the objectives of backing up your database and properly maintaining transaction logs. For details, see Database backup and maintenance planning.