کد:
http://itprosecure.com/blogs/fcs_administration/archive/2009/06/02/forefront-client-security-modifying-the-dts-package-database-variables-for-increased-data-storage-on-a-forefront-client-security-sp1-server.aspx

Modifying Database and Environment Variables to Support the DTS Package for Proper Data Storage on a Forefront Client Security SP1 Server


The Forefront Client Security SP1 Environment in a Single Server Topology on Windows 2008 (as well as when Forefront Client Security SP1 is installed on multiple Servers) consists of a number of separate components working together to provide Services. These components consist of the following:

  1. Windows 2008 x86 Edition (Win2k8)
  2. Internet Information Services 7 (IIS 7)
  3. SQL 2005 SP2 (SQL2k5 SP2) supporting the Database, Integration Services and Reporting Services
  4. Windows Server Update Services 3.0 SP1 (WSUS 3.0 SP1)
  5. Group Policy Management Console SP1 (GPMC SP1)
  6. Forefront Client Security SP1 (FCS SP1) including Microsoft Operations Manager 2005 (MOM2k5) for Alerting and Reporting

Since FCS SP1 requires distribution of the MOM2k5 Agent to each Target Workstation and/or Target Server we find a variety of Alert, Security, Health and Availability Data moving from the Target (running the MOM2k5 Agent and FCS SP1 Agent) to the MOM2k5 Database for presentation through the FCS SP1 Administrative Console. MOM2k5 uses a paradigm where this 'Inbound Data' from the MOM2k5 Agent (on the Target) is stored in the 'OnePoint' Database and nightly (1 AM is the Default) moved from the 'OnePoint' Database into the 'SystemCenterReporting' Data Warehouse. This 'movement' from one Database to another occurs through a Scheduled Task called 'SystemCenterDTSPackageTask'. Occasionally, even when all the configuration settings are correct for this Scheduled Task the Data does not properly move from 'OnePoint' to 'SystemCenterReporting'. Separately I have Blogged about how to Troubleshoot the DTS Package. Take a look at this entry for reference material and a background about the DTS Package Scheduled Task (running as the 'MOM.Datawarehousing.PackageGenerator.exe' with defined Switch Parameters).
In this Blog entry I wanted to review several important configuration modifications that might be of value when working towards ensuring Data moves properly between the two Data Repositories mentioned above. Here's a Summary of the Actions I follow in this Blog entry:

  1. Analyze the DTS Package Scheduled Task to confirm the problem.
  2. Modify the Database Size for the 1) 'SystemCenterReporting' Database, and 2) the 'TempDB' Database from the Default Values.
  3. Manually run the DTS Package Scheduled Task using the '/latency:3 (3 denotes '3 Days'), then '/latency:2', '/latency:1' and finally without the '/latency' Switch Parameter. This moves the data in small incremental batches from the 'OnePoint' Database to the 'SystemCenterReporting' Database.
  4. Modify the 'Remote Query Timeout Value' to permit appropriate time for the Scheduled Task to complete successfully.

If you have ever asked the following questions, this Blog entry may be a handy reference:

  • I have some data in my '14 Day History Graph' in the Forefront Client Security SP1 Console, then all the sudden it disappeared. What happened?
  • I am receiving a MOM2k5 Alert with terms in it like '1_SC_Inner_DTS_Package' for my Forefront Client Security SP1 configuration and I need to know how to resolve this Alert?
  • Why is 'DataTransformationServices' failing with an 'Event ID 81' on my Forefront Client Security SP1 configuration?
  • Why am I receiving an 'Stop Error Code: 8000405' for my 'DataTransformationServices' Scheduled Task on my Forefront Client Security SP1 configuration?



Figure 1 - I login to the Forefront Client Security SP1 Single Server Topology on Windows 2008.



Figure 2 - Upon opening the FCS SP1 Console I note the '14 Day History' Graph missing data values for several days. I begin to investigate.



Figure 3 - An examination of the 'MOM2k5 Operators Console' shows an Alert with a 'Repeat Count of 6'. Analysis of this Alert indicates data not moving from the 'OnePoint' Database (short term storage) to the 'SystemCenterReporting' Database (longer term storage). I will begin next to investigate the Scheduled Task that moves this Data on a Nightly basis.



Figure 4 - Next I open the Task Scheduler Library to view the Scheduled Task for the DTS Package. The 'Last Run Result' indicates an odd Hex Code. I then examine the 'History' of this Scheduled Task and its output.



Figure 5 - The 'Administrative Events' Filter in Windows 2008 Event Logging shows 'Warnings and Errors' from a variety of Event Logs. The 'Event ID 81' indicates problems with the 'DataTransformationServices' Scheduled Task completing correctly.



Figure 6 - Further examination of the 'Event Properties' displays the detail the 'DataTransformationServices' Data Source yields when offeirng an Error. I will 'Copy' the Error Output to a Text File for easier examination.



Figure 7 - The 'Copy and Paste' into a Text File allows easier reading and search (using 'Edit/Find').



Figure 8 - After a quick scan of the Text File Error Output I can see the detail for which Table Insert failed. The MOM2k5 Documentation on the Microsoft TechNet Web Site (and various Blog entries) yield facts that this 'Insert' fails for a variety of reasons. The resolution requires trying several of the remedies until the problem is solved. Next I will try several approaches to solving the problem.
In Summary, this 'Insert Error' may occur because of (among these, others):

  • Insufficient Database Space Available for the 'SystemCenterReporting' Database.
  • Insufficient Database Space Available for the 'TempDB' Database.
  • Insufficient Processing Time for Remote Queries (even though this occurs on the same Server and not from a Remote Server).



Figure 9 - I initially examine the size (in MB) of the 1) 'SystemCenterReporting' Database (which is actually 2 files - RepData.mdf and RepLog.ldf), and 2) the 'TempDB' Database (again, two files - Tempdb.mdf and templog.ldf).



Figure 10 - I login to SQL Management Studio and invoke the 'Properties' for the 'SystemCenterReporting' Database. Note the Default File Sizes (1 GB and 512 MB respectively).



Figure 11 - I move the Database File Sizes to 4 GB for the RepData.mdf File and 1 GB for the RepLog.ldf File. This should insure proper Space Availability within the Database.



Figure 12 - Next I focus on the 'TempDB' Files. Note the Default Size of 8 MB for the tempdb.mdf File and 1 MB for the templog.ldf File.



Figure 13 - I move the File Size to 100 MB for the tempdb.mdf File and 25 MB for the templog.ldf File.



Figure 14 - When I subsequently rerun the 'dir *.mdf *.ldf /s' Command the values for each of the Database and Log Files appears as configured.



Figure 15 - Next, I begin to invoke the 'MOM.Datawarehousing.DTSPackageGeneratore.exe' Command with the appropriate Switch Parameters as found in the Scheduled Task. Upon initially running this Command with just the '/latency' Switch Parameter I recieve an Output Error (Figure 16 below). This indicates either 1) too much data is being moved, 2) not enough 'processing time' is available before the defined timeout, or 3) some other issue with the Database. As a result I then begin to issue the '/latency:X' parameters one day at a time. The MOM2k5 Alert in 'Figure 3' above denotes this Scheduled Task has not run in an automated fashion in 6 Days - so, I begin with '/latency:6' then decrement to '/latency:5', '/latency:4' through to the point where the '/latency' parameter is removed.
Note: I remove the '/silent' Switch Parameter and add the '/latency:X' where X is the defined interval in Days that the Data should be referenced from as a starting point.



Figure 16 - The initial run with just the '/latency' Switch Parameter. Note the Output Error in Figure 17 that follows.



Figure 17 - I still receive the Output Error. This means I must issue the '/latency:X' beginning with the Maximum Days since the Error (from Figure 3 above it is 6 days) while decrementing to 5, 4, 3, 2, 1 and no '/latency' Switch Parameter.



Figure 18 - Another review of the MOM2k5 Alert Output now shows a 'Repeat Count of 7' since running this command another time with just the '/latency' Switch Parameter. Before I run the decrementing 'latency:X' countdown I need to increase the 'Remote Query Timeout' value to a higher value (Default is 600 Seconds). Before I complete this Task I will validate the Scheduled Task Results to be sure nothing else has been overlooked.



Figure 19 - All is fine with the Scheduled Task. I will move ahead with the Remote Query Timeout Value modification.



Figure 20 - Invoking SQL Management Studio I then select 'Properties' for the Server.



Figure 21 - The 'Properties' for the Server are selected followed by 'Connections'. Here is the modifiable value for 'Remote Query Timeout (in Seconds)'.



Figure 22 - I modify the 'Remoe Query Timeout' fromt the Default Value of '600 Seconds' to '1200 Seconds'. This should improve the processing time permitted for Queries against all Databases.



Figure 23 - Another attempt to run the DTS Package Scheduled Task from the Command Line without the '/latency:X' Switch Parameter.



Figure 24 - Based on this Output Error I decide to increase the Query Timeout Value even further before beginning the 'latency:X' decrementing countdown.



Figure 25 - I change the 'Query Timeout Value' to '3600 Seconds' from '1200 Seconds' (the originally modified value).



Figure 26 - I begin issuing the DTS Package Command using the Switch Parameters including the decrementing '/latency'X' countdown (beginning here at 6 Days, then 5 Days, etc.). The Scheduled Task should now have 1) time to execute (due to the Query Timeout Value change) and 2) small amounts of data (due to the 'latency:X' countdown).



Figure 27 - Here is the input using the 'latency:3' Switch Parameter to show the progress.



Figure 28 - Finally I run the Scheduled Task from the Command Line using no 'latency:X' Switch Parameter. If all is well the data should be successfully moving from the 'OnePoint' Database to the 'SystemCenterReporting' Database without error.



Figure 29 - When I move to the FCS SP1 Administrative Console and 'Refresh' the Console I immediately note data on the 14 Day History Graph previously not present.



Figure 30 - The Programmatic Proof for success in this exercise is noting the 6 Each 'Information' Event Values with an 'Event ID 80' (not 'Event ID 81' which denotes Failure). The data is now being properly transferred from the 'OnePoint' Database (Short Term Storage) into the 'SystemCenterReporting' Database (Longer Term Storage)!


If you'd like to 'Learn Advanced IT' - check out our new website exchangesummit.net! Use coupon code 'ITPS-777' for $100 off (through 9/1/2009) the Forefront Client Security SP1 Single Server Topology on Windows 2008. Detailed Course Description -15 hours of video training. Free video content as well!

Summary: In this Blog entry I review several configuration parameters for the Database and Operating System Environment on a Forefront Client Security SP1 Single Server Topology on Windows 2008 that allow proper data storage for both Short Term Data and Long Term Data transferred by the Nightly Data Transformation Services Scheduled Task



موضوعات مشابه: