Environment
Directory & Resource Administrator 8.5
Directory & Resource Administrator 8.5 SP1
Directory & Resource Administrator 8.6
Directory & Resource Administrator 8.6 SP1
Situation
Can the DRAReporting DB, ACFConfiguration DB, SMCubedepot DB, and SSIS Jobs be installed in a named SQL Instance?
Resolution
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:
- To locate the path for the XML file, open the registry locally on the SQL Server to host the DRA Reporting
- For Windows 2003 / SQL 2005 Locate the KEY of: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile
- 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
- 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:
- Open SQL Management Studio
- Connect to the Named Instance hosting the DRA Reporting DBs
- Expand SQL Server Agent
- Expand JOBS
- Right Click on the (DRA) Data Pump Job, choose Properties
- Highlight the Steps from the Left Hand side of the properties window
- Click the Edit Step Button
- 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
- Save the Changes
- 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
- Save the changes
- 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.