|
|
This appendix describes how you set up SQL databases as a repository of network statistics to be used in reports.
This appendix contains the following sections specific to your environment:
This appendix contains the following sections specific to your environment:
The TrafficDirector application can use network statistics to create long-term trend reports with the Trend Reporter application. To do so, you must set up one or more SQL servers to log to a SQL database.
An embedded SQL database is bundled with the TrafficDirector application that you can use on Windows NT and Solaris platforms. The software that runs on the Windows NT Server platform also lets you use the Microsoft Windows SQL Server as a standalone SQL server database. You have many options for configuring the database.
When you ran the installation procedure, you chose the type of SQL database you will use---embedded SQL or Microsoft SQL Server.
The TrafficDirector application supports a distributed database architecture so you can track statistics from multiple sites. You can define multiple network management stations to monitor your network locally, then collect the information from these stations into one centralized SQL database.
By setting up the logging mechanism to perform most agent polling locally, you can avoid excessive traffic to and from the central SQL database.
You can also configure your environment so each local site generates reports based on the data gathered locally, or have the central site gather the reporting information from each remote site and compile reports globally.
To properly set up the SQL server environment, you must perform these major tasks:
(a) Install and configure the SQL Server database software.
(b) Identify the centralized logging server to the TrafficDirector application. You use the Configure SQL Servers application to do this. See the "Configuring SQL Servers for Reporting" chapter in Using the Campus TrafficDirector Application for detailed information.
(c) Define any read-only servers from which to obtain statistics for the logging server. You use the Configure SQL Servers application to do this. See the "Configuring SQL Servers for Reporting" chapter in Using the Campus TrafficDirector Application for detailed information.
(d) Identify the desired aging and utilization parameters to determine how long to maintain historical data in the SQL database. You use the Configure Rollup application to do this. See the section, "Logging and Aging Data for Reporting" in the "Configuring SQL Servers for Reporting" chapter in Using the Campus TrafficDirector Application for detailed information.
(e) Associate the SQL servers with the appropriate agent definition. You use the Configuration Manager application to do this. See the "Using Configuration Manager" chapter in Using the Campus TrafficDirector Application for detailed information.
(a) Define any read-only servers from which to obtain statistics for the logging server. You use the Configure SQL Servers application to do this. See the "Configuring SQL Servers for Reporting" chapter in Using the Campus TrafficDirector Application for detailed information.
(b) Identify the desired aging and utilization parameters to determine how long to maintain historical data in the SQL database. You use the Configure Rollup application to do this. See the section, "Logging and Aging Data for Reporting" in the "Configuring SQL Servers for Reporting" chapter in Using the Campus TrafficDirector Application for detailed information.
(c) Associate the SQL servers with the appropriate agent definition. You use the Configuration Manager application to do this. See the "Using Configuration Manager" chapter in Using the Campus TrafficDirector Application for detailed information.
The TrafficDirector application on a Windows NT Server platform supports the configuration of a separate SQL server database using Microsoft SQL Server V7.0 or 6.5.
Use the procedures in the following sections as they pertain to your environment:
To set up the SQL database for use by the Trend Reporter application, you must complete the following tasks in the order listed.
1. Setting SQL Server Network Options
2. Setting SQL Client Network Options
5. Setting Permissions for Database Access
7. Establishing Permission to Access Tables (Windows NT Platforms Only)
8. Defining SQL Servers to the TrafficDirector Application (All Platforms)
To enable logging to the TrafficDirector SQL database, you must set the SQL Server network options on the system where the SQL Server is installed. To do so, follow these steps:
Step 1 From the Windows Programs menu, select Microsoft SQL Server 7.0.
The Microsoft SQL Server window is displayed.
Step 2 Start the Server Network Utility application.
From the General tab, at least three network library selections should be present:
Step 3 If all three selections are listed, click OK.
Step 4 If TCP/IP is not listed, do the following:
(a) Click Add.
(b) In the Add Network Library Configuration window, click TCP/IP.
(c) The server name and computer names should be filled in with the system name. If they are not complete, enter this information manually.
(d) The port number field should contain 1433; if not, enter 1433.
Step 5 Click OK to exit from the Add Network Library Configuration window.
Step 6 In the SQL Server Network Utility window, click OK to save the changes.
Step 7 Exit from Microsoft SQL Server.
To establish proper network communication, you must set the SQL client network options on the system on which the SQL Server is installed. To do so, follow these steps:
Step 1 From the Windows Programs menu, select Microsoft SQL Server 7.0.
The Microsoft SQL Server window is displayed.
Step 2 Click the Client Network Utility option.
Step 3 From the General tab, change the default network library to TCP/IP.
Step 4 Click OK.
You must create a database to establish a location where information collected by the TrafficDirector application is stored for use with the Trend Reporter application. To do so, follow these steps:
Step 1 From the Windows Programs menu, select Microsoft SQL Server 7.0.
Step 2 Start the Enterprise Manager application.
Step 3 Expand the navigation tree until the Databases folder is displayed under the correct SQL server.
Step 4 Highlight the Databases folder.
Step 5 Select Actions > New Database.
Step 6 In the Database Properties window, click the General tab.
Step 7 In the Name field, enter NSTREND_DB.
Step 8 Set database and transaction log default sizes by following these steps:
(a) From the Database tab, click the Space Allocated field.
(b) Enter a new size for the database.
(c) Click the Transaction Log tab.
(d) Click the Space Allocated field.
(e) Enter a new size for the transaction log.
Step 9 Click OK.
To access the NSTREND_DB database, you must create a SQL user. This information is the same as what you used when you set up the database with the TrafficDirector Configure Servers application.
To create a database user, follow these steps:
Step 1 From the SQL Server Enterprise Manager window, expand icons until the Security folder is displayed.
Step 2 Expand the Security folder.
Step 3 Highlight Logins.
Step 4 Select Actions > New Login.
Step 5 At the SQL Server Login Properties---New Login window, click the General tab.
Step 6 In the Name field, enter the user name of the SQL user account.
Step 7 Click the SQL Server authentication radio button.
Step 8 In the Password field, enter a password for the SQL user account.
Step 9 In the defaults section of the General tab, go to the database pulldown menu.
Step 10 Highlight NSTREND_DB.
Step 11 Click the Database Access tab.
Step 12 From the Database Access tab, click inside the Permit box to the left of NSTREND_DB.
Step 13 Click OK.
Step 14 When prompted, confirm the password by reentering it.
Step 15 Click OK.
To allow the TrafficDirector application to create tables, insert new data, and delete old data, the SQL user account must have full permission to NSTREND_DB. To set database permissions, follow these steps:
Step 1 From the SQL Server Enterprise Manager window, open the Databases folder.
Step 2 Highlight NSTREND_DB.
Step 3 Select Action > Properties.
Step 4 Click the Options tab.
Step 5 In the settings section, check Truncate Log on Checkpoint.
Step 6 Click the Permissions tab.
Step 7 For the SQL Server user created, check all of the permissions boxes. (If necessary, use the scroll bar to show all of the boxes.)
Step 8 Click OK.
Step 9 Close the SQL Server Enterprise Manager window.
If the TrafficDirector software and the SQL Server software are not installed on the same system, you should install the client connectivity portion of the SQL Server Standard edition on the system on which the TrafficDirector application is installed.
The steps you follow depend on whether the system on which you are installing the software has the auto-run feature enabled.
To install the client software, follow these steps.
Step 1 Place the SQL Server CD in the CD-ROM drive of the system on which you installed the TrafficDirector application.
Step 2 Browse the contents of the CD.
Step 3 Double click autorun.exe.
Step 4 At the Microsoft SQL Server window, click Install SQL Server 7.0 Components.
Step 5 At the Install SQL Server 7.0 Components window, click the Database Server Standard Edition icon.
Step 6 At the Select Install Method window, click the Local Install radio button.
Step 7 Click Next.
A message is displayed to indicate that only Client components are available for installation.
Step 8 Click OK.
Step 9 At the Welcome window, click Next.
Step 10 At the Software License Agreement window, click Yes.
Step 11 If necessary, at the User Information window, enter user information.
Step 12 Click Next.
Step 13 At the Setup window, in the fields provided, enter the CD-key.
Step 14 Click OK.
Another setup window is displayed, showing Product ID information.
Step 15 Click OK.
Step 16 At the Setup Type window, accept the default installation directory by clicking Next, or browse to specify a different directory.
Step 17 Click Next.
Step 18 In the Select Components window, deselect all options except for Client Connectivity.
Step 19 Click Next.
Step 20 In the Start Copying Files window, click Next.
Files are copied to the directory you specified in Step 16.
Step 21 In the Setup Complete window, click Finish.
Step 22 Close all open windows.
Step 23 Remove the CD-ROM from the drive.
Step 24 Restart your computer.
Step 25 When your computer restarts, continue with the section, "Establishing Permission to Access Tables (Windows NT Platforms Only)."
To set up the SQL database for use by the Trend Reporter application, you must complete the following tasks in the order listed.
1. Gathering Information Prior to Setup
2. Installing SQL Server Software
3. Setting SQL Server Network Options
4. Setting SQL Client Network Options
6. Creating a Database Device on SQL Server
10. Establishing Permission to Access Tables (Windows NT Platforms Only)
11. Defining SQL Servers to the TrafficDirector Application (All Platforms)
Before installing the Microsoft SQL Server software, use this worksheet to record information specific to your installation.
| Item Description | Location |
|---|---|
NT server name on which the SQL Server is installed (maximum eight characters) |
|
Database device name | NETSQL |
Database device size |
|
Database name | NSTREND_DB |
Database size |
|
Users group name | NETSQLUSERS |
Administrator's login name |
|
Administrator's login password |
|
IP address of NT server |
|
You must install the SQL Server software on a system that also supports Windows NT Server. Install the software using the detailed instructions provided with the software.
To enable logging to the TrafficDirector SQL database, you must set the SQL Server network options on the system where the SQL Server is installed. To do so, follow these steps:
Step 1 From the Windows Programs menu, select Microsoft SQL Server.
The Microsoft SQL Server window is displayed.
Step 2 Select the SQL Setup option.
The Welcome window is displayed.
Step 3 Click Continue.
The SQL Server already installed message is displayed.
Step 4 Click Continue.
The Microsoft SQL Server options window is displayed.
Step 5 Click Change Network Support.
Step 6 Click Continue.
The Select Network Protocols window is displayed.
Step 7 Deselect Named pipes.
Step 8 Select TCP/IP sockets.
Step 9 Click OK.
The TCP/IP Socket Number window is displayed with port number 1433 selected.
Step 10 Click Continue.
The Microsoft SQL Server window is displayed.
Step 11 Click the Exit to Windows NT icon.
To enable proper network communication, you must set the SQL Client network options on the system on which the SQL Server is installed. To do so, follow these steps:
Step 1 From the Windows Programs menu, select Microsoft SQL Server.
The Microsoft SQL Server window is displayed.
Step 2 Click the SQL Client Configuration Utility option.
The SQL Server Client Configuration Utility window is displayed.
Step 3 Click the Net Library tab.
The default information for the net library is displayed.
Step 4 If necessary, change the Default Network from Named Pipes to TCP/IP sockets.
Step 5 Click the Advanced tab.
The Advanced Tab window is displayed.
Step 6 Within the Client Configuration section, do all of the following:
(a) In the Server field, enter the name of the NT Server where the SQL server is installed, or select the server name from the list box.
(b) In the DLL Name field, select TCP/IP Sockets from the list box.
(c) Click Locate.
The Locate Libraries window is displayed.
(d) Select dbmssocn.dll.
(e) Click Done.
The SQL Client Configuration Utility window is displayed.
Step 7 In the Advanced Client Options section, click Add/Modify.
The SQL Server Client Configuration Utility window (with the server name and associated DLL connection string) is displayed.
Step 8 Click Done to close the window.
After you set the SQL Server and Client network options, you must register the SQL Server so the system administrator can perform necessary maintenance. To do so, follow these steps:
Step 1 From the Windows Programs menu, select Microsoft SQL Server.
The Microsoft SQL Server window is displayed.
Step 2 Select the SQL Enterprise Manager option.
The Server Manager window is displayed within the Microsoft SQL Enterprise Manager window.
Step 3 From the menu bar on the Microsoft SQL Enterprise Manager window, select Server > Register Server.
The Register Server window is displayed.
Step 4 In the Server field, enter the name of the NT Server previously installed or select the server name from the list box.
Step 5 Verify the following:
Step 6 Click the register box.
Unable to connect. . .Server doesn't exist, click No in the window, then perform these tasks in the sequence listed:
If you are still unable to register the server, even after stopping and restarting the processes, perform the following tasks in the sequence listed:
Step 7 Close the Register Server window.
To designate space on the SQL Server for use by the database, you create a database device. To do so, follow these steps:
Step 1 In the Microsoft SQL Enterprise Manager Server Manager window, select the name of the SQL Server you registered.
Step 2 From the Microsoft SQL Enterprise Manager menu bar, select
Manage > Database Devices.
The Manage Database Devices window is displayed.
Step 3 Click the New Device icon in the upper left corner of the window to create a new database device.
The New Database Device window is displayed.
Step 4 In the name field, enter NETSQL.
Step 5 In the Size (MB) field, enter the database device size.
Step 6 Click Create Now.
The SQL Server Enterprise Manager message window is displayed.
Step 7 Click OK.
The Manage Database Devices window is displayed with the NETSQL device created displaying a bar graph for the device size established.
Step 8 Close the Manage Database Devices window.
Step 9 Close the Server Manager window.
You create a database to establish a location where information collected by the Trafficdirector application is stored for use with the Trend Reporter application. To do so, follow these steps:
Step 1 From the Microsoft SQL Enterprise Manager window menu bar, select
Manage > Databases.
The Manage Databases window is displayed.
Step 2 Click the New Database icon in the upper left corner of the window to create a new database.
The New Database window is displayed.
Step 3 Do the following:
(a) In the name field, enter NSTREND_DB.
(b) In the data device field, select NETSQL.
(c) Make the database the same size used when you created a database device on the SQL Server.
(d) Make sure (none) is displayed in the Log Device field.
Step 4 Click Create Now.
The Manage Databases window is displayed with a bar graph for the database size established.
Step 5 Close the Manage Databases window.
A user group is created so that all users of the SQL Server database can be controlled and identified. To do this, follow these steps:
Step 1 From the Server Manager window, in the Databases folder, highlight NSTREND_DB.
Step 2 From the SQL Enterprise Manager window menu bar, select
Manage > Groups.
The Manage Groups window is displayed.
Step 3 Create a user group called NETSQLUSERS by entering the following text in the group field; this group will be associated with NSTREND_DB.
NETSQLUSERS
Step 4 Click Add.
Step 5 Close the Manage Groups window.
Step 6 From the SQL Enterprise Manager window menu bar, select Manage > Logins.
The Manage Login window is displayed.
Step 7 Do the following:
(a) In the Login Name field, enter the name of the individual responsible for performing the administrative functions related to logging.
(b) In the Password field, enter the appropriate password.
Step 8 Referring to the Database Access section of the Manage Logins window, do the following:
(a) In the NSTREND_DB row, check the Permit box.
(b) Under the group heading, select NETSQLUSERS.
(c) Click Add.
(d) Confirm the password.
(e) Click OK.
(f) Close the Manage Logins window.
Step 9 Close the Server Manager window.
Step 10 From the Microsoft SQL Enterprise Manager window menu bar, do the following:
(a) Select Manage > Databases.
(b) Select NSTREND_DB.
(c) Click the Edit database icon in the upper left corner of the window.
The Edit Database window is displayed.
Step 11 Select the Options tab.
Step 12 Click Truncate Log on Checkpoint.
Step 13 Select the Permissions tab.
Step 14 Give both NETSQLUSERS and the Administrator's Name that is displayed all permissions.
Step 15 Click OK.
Step 16 Close the Manage Databases window.
Step 17 Close the Microsoft SQL Enterprise Manager window.
If the system on which the SQL Server software is installed is a different system than where the TrafficDirector application is installed, you must install the MSSQL client software on the system where the TrafficDirector application is installed. To do so, follow these steps:
Step 1 Place the SQL Server CD in the CD-ROM drive of the system on which you installed the TrafficDirector application.
Step 2 Browse the CD to locate the i386 directory.
Step 3 Select this directory and run setup.exe.
Step 4 Enter the following information:
Step 5 From the Windows Programs menu, select Microsoft SQL Server.
The Microsoft SQL Server Options window is displayed.
Step 6 Click the SQL Client Configuration Utility option.
The SQL Server Client Configuration Utility window is displayed.
Step 7 Click the Net Library tab.
The default information for the Net Library is displayed.
Step 8 If necessary, change the default network from Named Pipes to TCP/IP sockets.
Step 9 Click the Advanced tab.
The Advanced Tab window is displayed.
Step 10 In the Client Configuration section, perform all of these tasks:
(a) In the Server field, enter the name of the Windows NT Server system on which the SQL Server is installed, or select the server name from the list box.
(b) In the DLL Name field, select TCP/IP Sockets from the list box.
(c) Click Locate.
The Locate Libraries window is displayed.
(d) Select dbmssocn.dll.
(e) Click Done.
The SQL Client Configuration Utility window is displayed.
Step 11 In the Advanced Client Options section, click Add/Modify.
The SQL Server Client Configuration Utility window, with the server name and the associated DLL connection string, is displayed.
Step 12 Click Done to close the window.
Step 13 Under the Setup options, select Utilities.
An ISQL/w icon is displayed.
Step 14 From the Server program group, click the ISQL/w icon.
Step 15 Enter the following information:
Step 16 Select connect.
Step 17 The SQL query box is displayed.
Step 18 Minimize this display.
Step 19 Continue with the next section, "Establishing Permission to Access Tables (Windows NT Platforms Only)."
Before the TrafficDirector application can access the various databases on the SQL Server, you must enable the proper permissions in the SQL Server software.
To establish permissions, follow these steps:
Step 1 From the Windows Programs menu, select Microsoft SQL Server.
The Microsoft SQL Server window is displayed.
Step 2 Select the SQL Enterprise Manager option.
The Server Manager window is displayed.
Step 3 Click the plus-sign (+) symbol to the left of the SQL server.
The folders within the SQL Server directory are displayed.
Step 4 Click the plus-sign (+) symbol to the left of the Databases folder.
The names of existing databases are displayed.
Step 5 Highlight the NSTREND_DB database.
Step 6 From the menu bar, select Object > Permissions.
The Permissions window is displayed.
Step 7 Highlight the first name listed.
Step 8 Click Grant All.
Step 9 Click Set.
The Configure Servers application lets you define the address and access information that the TrafficDirector application requires to either write to or read from an SQL database.
Although each copy of the TrafficDirector software writes only to a single SQL server, you can use this application to configure the TrafficDirector software to read from multiple SQL servers. You use Configure Servers to do the following:
If you installed the Microsoft SQL Server, use the Configure SQL Servers application to define the SQL server as the logging server. To do so, follow these steps:
Step 1 Click the Config Servers icon in the Admin level of the TrafficDirector software.
Step 2 Select Edit > New from the menu bar.
The Add Server Entry window is displayed.
Step 3 Enter the following information:
Step 4 Click OK to accept the server configuration and add the SQL Server to the TrafficDirector configuration.
Step 5 Close the Configure SQL Servers window.
Although the TrafficDirector application can only write report data to one SQL Server as the logging server, the distributed reporting feature enables the TrafficDirector application to read data from multiple SQL servers for use in generating trend reports.
To configure any read-only SQL servers to read data and generate reports from (on either Windows NT or Solaris platforms), follow these steps:
Step 1 Click the Config Servers icon in the Admin level of the TrafficDirector application.
The Configure SQL Servers window is displayed.
Step 2 Select Edit > New from the menu bar.
The Add Server Entry window is displayed.
Step 3 Enter the following information in the Add Server Entry window:
Step 4 Click OK to accept the server configuration and add the SQL server to the TrafficDirector configuration files.
After you define the SQL server as part of the TrafficDirector installation process, and utilization and aging parameters have been established for logging, you must associate the appropriate server with each agent installed in the TrafficDirector application.
To associate the server with each agent, follow these steps:
Step 1 From the TrafficDirector main window, click the Admin button.
Step 2 Click the Config Manager icon.
Step 3 Select an agent.
Step 4 Click Edit.
Step 5 Click the SQL Server list box.
Step 6 Select the appropriate server for the agent.
Step 7 Click OK twice.
The correct SQL server definition has been assigned to the agent.
Step 8 If necessary, repeat Steps 3 through 7 for each agent.
Before the TrafficDirector application can access the various databases on the SQL Server, you must enable the proper permissions in the SQL Server software.
To establish permissions, follow these steps:
Step 1 From the Windows Programs menu, select Microsoft SQL Server.
The Microsoft SQL Server window is displayed.
Step 2 Select the SQL Enterprise Manager option.
The Server Manager window is displayed.
Step 3 Click the plus-sign (+) symbol to the left of the SQL server.
The folders within the SQL Server directory are displayed.
Step 4 Click the plus-sign (+) symbol to the left of the Databases folder.
The names of existing databases are displayed.
Step 5 Highlight the NSTREND_DB database.
Step 6 From the menu bar, select Object > Permissions.
The Permissions window is displayed.
Step 7 Highlight the first name listed.
Step 8 Click Grant All.
Step 9 Click Set.
Step 10 Repeat this process for each table name listed.
Step 11 Click the By User tab.
All permissions that have been set are displayed in the Object Permissions window.
Step 12 Verify that all permissions are properly set for each table name by reviewing the contents of the display.
Step 13 Close the Object Permissions window.
Step 14 Close the Microsoft SQL Enterprise Manager window.
There may be times when you want to edit a SQL server definition or delete a definition that is no longer needed.
In these cases, you can either edit or delete the SQL server definitions as outlined in the following sections:
To edit a server definition, follow these steps:
Step 1 Click the Config Servers icon in the Admin level of the TrafficDirector application.
Step 2 Select the SQL server entry that you want to edit.
Step 3 Select Edit > Edit from the menu bar.
The Edit Server Entry window is displayed.
Step 4 Change any of the parameters for the selected SQL server entry.
Step 5 Click OK to accept the server definition and add the SQL server to the TrafficDirector configuration files with the new changes.
To delete a SQL server definition, follow these steps:
Step 1 Click the Config Servers icon in the Admin level of the TrafficDirector application.
Step 2 Select the SQL server entry you want to delete.
Step 3 Select Edit > Delete from the menu bar.
The TrafficDirector software deletes the SQL server configuration and removes it from the Configure SQL Servers main window.
Posted: Fri Oct 29 17:19:33 PDT 1999
Copyright 1989-1999©Cisco Systems Inc.