How can I install the DRAReporting NRC in a named SQL Instance? (NETIQKB72824)

How can I install the DRAReporting NRC in a named SQL Instance?

Can the DRAReporting DB, ACFConfiguration DB, SMCubedepot DB, and SSIS Jobs be installed in a named SQL Instance?

The NRC install will look to the MSDTS Configuration file (MsDtsSrvr.ini.xml) for the location in SQL to deploy the SSIS packages used by DRA Reporting. If you are going to use a named instance of SQL for the DRA Reporting, this XML file will need to be modified after

 

Here are the steps for modifying the XML file:

1. To locate the path for the XML file, open the registry locally on the SQL Server to host the DRA Reporting
2. For Windows 2003 / SQL 2005 Locate the KEY of: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile
3. The value for this key will show the location. The default value for Windows 2003 / SQL 2005 will be: C:\Program Files\Microsoft SQL Server\90\DTS\Bin\ MsDtsSrvr.ini.xml
4. Edit the XML file using a TEXT Editor, such as Notepad

• The default configuration of the file for the default instance of SQL Server will look like:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

• Now the if DRA Reporting will be installed on a SQL Server where there is ONLY a Named Instance and NO Default Instance, the file will need to be modified as listed below. ** Note for this example MY-SQL-2005 is the SQL Server name and DRAINSTANCE is the Named instance of SQL. **

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName> MY-SQL-2005\DRAINSTANCE </ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

 

• Now if you are using BOTH the DEFAULT Instance of SQL AND a Named Instance of SQL the file will need to look like the following:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB_NAMED</Name>
      <ServerName> MY-SQL-2005\DRAINSTANCE </ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

**Note the FIRST <Name> TAG shows the DEFAULT Instance (Known as . ) the SECOND <Name> Tag shows the NAMED Instance**

• After the XML file has been modified, restart the SQL Integration Services Service on the SQL Server. Once the service has restarted, run the NRC   Install as normal. Once the install has been completed, we will need to modify the SSIS Jobs. Here is how to modify:

1. Open SQL Management Studio
2. Connect to the Named Instance hosting the DRA Reporting DBs
3. Expand SQL Server Agent
4. Expand JOBS
5. Right Click on the (DRA) Data Pump Job, choose Properties
6. Highlight the Steps from the Left Hand side of the properties window
7. Click the Edit Step Button
8. Modify the Command to add the Named Instance tag from the XML file: "dtexec" /DTS "\MSDB_NAMED\(DRA) 001 Extract Data" /SERVER LOST-SQL /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V
9. Save the Changes
10. Repeat the same changes for the (DRA) Aggregations JOB: "dtexec" /DTS "\MSDB\(DRA) 002 Run Aggregation" /SERVER LOST-SQL /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V
11. Save the changes
12. Restart the SQL Integration Services Service

Now go back to DRA and configure the Management Reports Collector from the Reporting Services configuration. Once the DRA Log Archive Server has uploaded data to SQL, the SSIS jobs will run. After they have completed the NRC Will show trend data reports.

There are times when it may be necessary to use a named instance for the NRC Database installs. Also if DRA Reporting and Security Manager Reporting are going to share the same SQL server, each will need to use thier own isntance.

Formerly known as NETIQKB72824