When creating a new JP1/Software Distribution database, you also need to include in the estimate the space required for devices other than software operation monitoring history database devices. For details, see 5.4.2 Estimating disk space required for Microsoft SQL Server in the manual Description and Planning Guide. Estimate as indicated below the space required monthly for the software operation monitoring history database devices and for the software operation monitoring history data partitions that hold one month of data. Here we use 10 for the number of programs for which operating time is acquired.

To change existing JP1/Software Distribution databases to data partitions, also estimate the size of the monthly software operation monitoring history data partition.

Space required for software operation monitoring history database devices

software-operation-monitoring-history-database-device-space-required (bytes)

= (1,861^#1 x number-of-software-operation-monitoring-history-information-items)^#2

+ (80 x number-of-acquired-operating-time-information-items)^#3

Software operation monitoring history data partition space required for one month

software-operation-monitoring-history-data-partition-space-required-for-one-month (bytes)

= (1,861 x number-of-software-operation-monitoring-history-information-items)^#2

= 1,861 x 10,000 x 2,000 x 20

= approximately 800 GB

#1

To set the maximum value for operation history information size, specify 1,861 (bytes). To specify a more average software operation monitoring history size, specify 543 (bytes).

#2

number-of-software-operation-monitoring-history-information-items

= number-of-client-machines-subject-to-software-operation-monitoring (10,000)

x number-of-operation-monitoring-history-items-acquired-per-client-daily (2,000)

x days-to-retain-operating-information (as secondary area, 10 days)

#3

number-of-acquired-operating-time-information-items

= 220 x number-of-clients x number-of-programs-subject-to-operating-time-acquisition (10)

= 1,861 x 10,000 x 2,000 x 10 + 80 x 220 x 1,000 x 10

= approximately 400 GB

Therefore, when creating a new JP1/Software Distribution database, create a 400-GB software operation monitoring history database device as a secondary area with Database Manager and create six 800-GB software operation monitoring history data partitions for six months of data using SQL Server Management Studio.

To change a JP1/Software Distribution database to a data partition, use the existing software operation monitoring history database device as a secondary area and create six 800-GB software operation monitoring history data partition for six months of data using SQL Server Management Studio.

Here, we assume the following disks are available for creating the secondary area.

• Disk 1
  1,000 GB, D: drive, specified as a secondary area, created by Database Manager^#
  
• Disk 2
  1,000 GB, E: drive, specified as an area for creating data partition for one month of data
  
• Disk 3
  1,000 GB, F: drive, specified as an area for creating data partition for one month of data
  
• Disk 4
  1,000 GB, G: drive, specified as an area for creating data partition for one month of data
  
• Disk 5
  1,000 GB, H: drive, specified as an area for creating data partition for one month of data
  
• Disk 6
  1,000 GB, I: drive, specified as an area for creating data partition for one month of data
  
• Disk 7
  1,000 GB, J: drive, specified as an area for creating data partition for one month of data
  

#

Used when creating a new database.

In this subsection, we prepare query scripts for creating the data partitions.

Table 7-15 Query scripts (for large user creating data partitions)

No.	Query script name	Description	Sample
1	DropTable.sql	Deletes an existing netmdm_monitoring_security table created by Database Manager. To create your own query script based on this sample, change the following item. Database name in USE statement	F.1
2	LargeAddFilegroup.sql	Adds a filegroup to be assigned a data partition. To create your own query script based on this sample, change the following items. Database name in USE statement Database name in ALTER DATABASE statement	F.15
3	LargeAddFile.sql	Adds an actual file to each drive and assigns it to a filegroup. To create your own query script based on this sample, change the following items. Database name in USE statement Database name in ALTER DATABASE statement FILENAME specification path in ALTER DATABASE statement SIZE specification size in ALTER DATABASE statement	F.16
4	LargeCreatePartition.sql	Creates a partition function and partition scheme. To create your own query script based on this sample, change the following items. Database name in USE statement yyyymmdd format value of CREATE PARTITION FUNCTION	F.17
5	CreateTable.sql	Creates a netmdm_monitoring_security table in the partition scheme. To create your own query script based on this sample, change the following item. Database name in USE statement	F.6

The data partitions shown in Tables 7-16 and 7-17 will be created when you execute the query scripts listed in Table 7-15.

Table 7-16 File and filegroups (for a large user creating data partitions)

No.	Logical name	File name	Initial size	Upper limit	Increase amount	Filegroup name
1	NETMDM_MONI_DEVICE	D:\Program Files\Hitachi\NETMDB\MSSQL\MONITOR.sdf	400,000 MB	UNLIMITED	10%	netmdm_moni_seg
2	NETMDM_DP_0001	E:\NETMDP\MONITOR_DP_0001.ndf	800 GB	UNLIMITED	10%	netmdm_moni_dp_0001
3	NETMDM_DP_0002	F:\NETMDP\MONITOR_DP_0002.ndf	800 GB	UNLIMITED	10%	netmdm_moni_dp_0002
4	NETMDM_DP_0003	G:\NETMDP\MONITOR_DP_0003.ndf	800 GB	UNLIMITED	10%	netmdm_moni_dp_0003
5	NETMDM_DP_0004	H:\NETMDP\MONITOR_DP_0004.ndf	800 GB	UNLIMITED	10%	netmdm_moni_dp_0004
6	NETMDM_DP_0005	I:\NETMDP\MONITOR_DP_0005.ndf	800 GB	UNLIMITED	10%	netmdm_moni_dp_0005
7	NETMDM_DP_0006	J:\NETMDP\MONITOR_DP_0006.ndf	800 GB	UNLIMITED	10%	netmdm_moni_dp_0006

Table 7-17 Partition function and partition scheme (for a large user creating data partitions)

Partition function name	Partition scheme name
netmdm_monitoring_security_pf	netmdm_monitoring_security_ps

Operation monitoring histories are stored by this partition function and partition scheme for each day for the event start times as indicated in the following table.

Table 7-18 Operation monitoring history that is stored (for large user creating data partitions)

No.	Filegroup name	Partition number	Operation monitoring history that is stored
1	netmdm_moni_seg	1	event-start-time < 01/01/2011
2	netmdm_moni_dp_0001	2	01/01/2011 event-start-time < 01/02/2011
3		3	01/02/2011 event-start-time < 01/03/2011
4		4	01/03/2011 event-start-time < 01/04/2011
5		5 to 29	One day stored in each respective partition number
6		30	01/29/2011 event-start-time < 01/30/2011
7		31	01/30/2011 event-start-time < 01/31/2011
8		32	01/31/2011 event-start-time < 02/01/2011
9	netmdm_moni_dp_0002	33	02/01/2011 event-start-time < 02/02/2011
10		34 to 59	One day stored in each respective partition number
11		60	02/28/2011 event-start-time < 03/01/2011
12	netmdm_moni_dp_0003	61	03/01/2011 event-start-time < 03/02/2011
13		62 to 90	One day stored in each respective partition number
14		91	03/31/2011 event-start-time < 04/01/2011
15	netmdm_moni_dp_0004	92	04/01/2011 event-start-time < 04/02/2011
16		93 to 120	One day stored in each respective partition number
17		121	04/30/2011 event-start-time < 05/01/2011
18	netmdm_moni_dp_0005	122	05/01/2011 event-start-time < 05/02/2011
19		123 to 151	One day stored in each respective partition number
20		152	05/31/2011 event-start-time < 06/01/2011
21	netmdm_moni_dp_0006	153	06/01/2011 event-start-time < 06/02/2011
22		154 to 181	One day stored in each respective partition number
23		182	06/30/2011 event-start-time

For information about how to create a new JP1/Software Distribution database, see 7.5.1 Creating a new database. When you create a new database, specify the initial size, upper limit, and increase amount from entry No. 1 of Table 7-16 in the software operation monitoring history database file.

For information about changing an existing JP1/Software Distribution database to a data partition, see 7.5.4 Upgrading the database.

Create data partitions by running the query scripts from Table 7-15 using SQL Server Management Studio.

To create a new JP1/Software Distribution data partition:

1. Execute DropTable.sql and delete the existing netmdm_monitoring_security table created by Database Manager.
2. Execute LargeAddFilegroup.sql to add the filegroup to which the data partition will be assigned.
3. Create a NETMDP folder on each drive.
4. Execute LargeAddFile.sql to add actual files on each drive and assign them to the filegroup.
5. Execute LargeCreatePartition.sql to create the partition function and partition scheme.
6. Execute CreateTable.sql to create the netmdm_monitoring_security table in the partition scheme.
7. Using SQL Server Management Studio, right-click the target database name NETMDM_SAMPLE to display the menu, choose Properties, and confirm that File and Filegroup are configured as specified. Also, right-click NETMDM_SAMPLE to open the menu, choose Storage, and confirm that netmdm_monitoring_security_ps (under Partition scheme) and netmdm_monitoring_security_pf (under Partition function) have been created.

To change an existing JP1/Software Distribution database into a data partition:

1. Stop Remote Install Server service.
2. Using the bcp utility, batch export the existing data from the netmdm_monitoring_security table.
3. Execute DropTable.sql and delete the netmdm_monitoring_security table.
4. Execute LargeAddFilegroup.sql to add the filegroup to which the data partition will be assigned.
5. Create a NETMDP folder on each drive.
6. Execute LargeAddFile.sql, add an actual file to the E drive, and assign it to the filegroup.
7. Execute LargeCreatePartition.sql to create the partition function and partition scheme.
8. Execute CreateTable.sql to create the netmdm_monitoring_security table in the partition scheme.
9. Using SQL Server Management Studio, right-click the target database name NETMDM_SAMPLE to display the menu, choose Properties, and confirm that File and Filegroup are configured as specified. Also right-click NETMDM_SAMPLE to open the menu, choose Storage, and confirm that netmdm_monitoring_security_ps (under Partition scheme) and netmdm_monitoring_security_pf (under Partition function) have been created.
10. Using the bcp utility, batch import the previously exported security data.

History starting on July 1, 2011 would also be stored in the partition created for storing history for June 30, 2011 (partition No. 182). To avoid this, you must add one or more data partitions before storing of history starting on July 1, 2011 begins. This example shows how to add new data partitions for storing an additional month of data.

Here, we assume the following disk is available for the new data partitions.

• Disk 8
  1,000 GB, K: drive, specified as the area for creating data partition for one month of data
  

In this subsection, query scripts for adding data partitions are provided.

Table 7-19 Query scripts (for a large user adding data partitions)

No.	Query script name	Description	Sample
1	LargeAddFilegroup2nd.sql	Adds a filegroup to be assigned a data partition. To create your own query script based on this sample, change the following items: Database name in USE statement Database name in ALTER DATABASE statement	F.18
2	LargeAddFiletoKdrive.sql	Adds an actual file to the K drive and assigns it to a filegroup. To create your own query script based on this sample, change the following items: Database name in USE statement Database name in ALTER DATABASE statement FILENAME specification path in ALTER DATABASE statement SIZE specification size in ALTER DATABASE statement	F.19
3	LargeAlterPartition.sql	Alters the function and partition scheme so that history from July 2011 will be stored daily in the added filegroup. To create your own query script based on this sample, change the following items: Database name in USE statement yyyymmdd format value of ALTER PARTITION FUNCTION	F.20

The data partitions shown in Table 7-20 will be added when you execute the query scripts shown above.

Table 7-20 File and filegroups (for a large user creating data partitions)

No.	Logical name	File name	Initial size	Upper limit	Increase amount	Filegroup name
1	NETMDM_DP_0007	K:\NETMDP\MONITOR_DP_0007.ndf	800 GB	UNLIMITED	10%	netmdm_moni_dp_0007

Operation monitoring histories are stored by changing this partition function and partition scheme each month for event start times as shown in the following table.

Table 7-21 Operation monitoring history that is stored (for large user creating data partitions)

No.	Filegroup name	Partition number	Operation monitoring history that is stored
1	netmdm_moni_dp_0006	153	06/01/2011 event-start-time < 06/02/2011
2		182	06/30/2011 event-start-time < 07/01/2011
3	netmdm_moni_dp_0007	183	07/01/2011 event-start-time < 07/02/2011
4		213	07/31/2011 event-start-time

Add data partitions by running the query scripts from Table 7-19 using SQL Server Management Studio.

To add a data partition:

1. Stop the Remote Install Server service.
2. Execute LargeAddFilegroup2nd.sql to add the filegroup to which the data partition will be assigned.
3. Create the folder K:\NETMDP.
4. Execute LargeAddFiletoKdrive.sql, add an actual file to the K drive, and assign it to the filegroup.
5. Execute LargeAlterPartition.sql to alter the partition function and partition scheme.^#
6. Using SQL Server Management Studio, right-click the target database name NETMDM_SAMPLE to display the menu, choose Properties, and confirm that File and Filegroup are configured as specified.

#

If history starting July 1, 2011 has already been stored, it may take some time to execute LargeAlterPartition.sql.

Here, since we do not have enough disk space for a new partition, we need to delete history up to January 2011 and reassign the partitions to store history for August 2011.

In this subsection, we prepare query scripts for reassigning data partitions.

Table 7-22 Query scripts (for a large user reassigning data partitions)

No.	Query script name	Description	Sample
1	CreateArchTable.sql	Creates a netmdm_monitoring_security_arch table for transferring deleted data. To create your own query script based on this sample, change the following item: Database name in USE statement	F.10
2	LargeSwitchPartition.sql	Switches history up to January 2011 from the netmdm_monitoring_security table to the netmdm_monitoring_security_arch table. To create your own query script based on this sample, change the following item: Database name in USE statement	F.21
3	DropArchTable.sql	Deletes the netmdm_monitoring_security_arch table. To create your own query script based on this sample, change the following item: Database name in USE statement	F.12
4	LargeMergeRange.sql	Alters the partition function so that history up to January 2011 will be stored in netmdm_moni_seg. To create your own query script based on this sample, change the following items: Database name in USE statement yyyymmdd format value of ALTER PARTITION FUNCTION	F.22
5	LargeAlterPartition2nd.sql	Alters the partition function and partition scheme so that August 2011 history will be stored in the existing filegroup from which history was deleted. To create your own query script based on this sample, change the following items: Database name in USE statement yyyymmdd format value of ALTER PARTITION FUNCTION	F.23

Operation monitoring history will be stored as shown in the following table when you execute the query scripts in Table 7-22, deleting old history, and altering the partition scheme and partition function.

Table 7-23 Operation monitoring history that is stored (for a large user reassigning data partitions)

No.	Filegroup name	Partition number	Operation monitoring history that is stored
1	netmdm_moni_seg	1	event-start-time < 01/01/2011
2	netmdm_moni_dp_0002	2	02/01/2011 event-start-time < 02/02/2011
3		3 to 28	One day stored in each respective partition number
4		29	02/28/2011 event-start-time < 03/01/2011
5	netmdm_moni_dp_0003	30	03/01/2011 event-start-time < 03/02/2011
6		31 to 59	One day stored in each respective partition number
7		60	03/31/2011 event-start-time < 04/01/2011
8	netmdm_moni_dp_0004	61	04/01/2011 event-start-time < 04/02/2011
9		62 to 89	One day stored in each respective partition number
10		90	04/30/2011 event-start-time < 05/01/2011
11	netmdm_moni_dp_0005	91	05/01/2011 event-start-time < 05/02/2011
12		92 to 120	One day stored in each respective partition number
13		121	05/31/2011 event-start-time < 06/01/2011
14	netmdm_moni_dp_0006	122	06/01/2011 event-start-time < 06/02/2011
15		123 to 150	One day stored in each respective partition number
16		151	06/30/2011 event-start-time < 07/01/2011
17	netmdm_moni_dp_0007	152	07/01/2011 event-start-time < 07/02/2011
18		153 to 181	One day stored in each respective partition number
19		182	07/31/2011 event-start-time < 08/01/2011
20	netmdm_moni_dp_0001	183	08/01/2011 event-start-time < 08/02/2011
21		184 to 212	One day stored in each respective partition number
22		213	08/31/2011 event-start-time

You can add data partitions by executing the query scripts shown in Table 7-22 using SQL Server Management Studio.

To re-assign a data partition:

1. Stop the Remote Install Server service.
2. Execute CreateArchTable.sql to create the netmdm_monitoring_security_arch table for transferring deleted data.^#
3. Execute LargeSwitchPartition.sql to transfer history up to January 2011 from the netmdm_monitoring_security table to the netmdm_monitoring_security_arch table.^#
4. Execute DropArchTable.sql to delete the netmdm_monitoring_security_arch table.^#
5. Execute LargeMergeRange.sql to alter the partition function so that history up to January 2011 is stored in netmdm_moni_seg.
6. Execute LargeAlterPartition2nd.sql to alter the partition function and partition scheme so that history for August 2011 will be stored in the existing filegroup from which history was deleted.

#

History can also be deleted using the dcmmonrst command, but deletion takes less time if it is transferred to the netmdm_monitoring_security_arch table and then deleted in table units.