I'm looking for some tips on the best practises for retaining data to be used for producing reports showing data up to 2-3 years old and then some?
Would the best option be to extend the raw and historical data fields in the data_enging probe? Would there be much of a performance impact on the database server (MS-SQL 2008 R2) and UMP?
Unfortunately there are a few dozen factors that will intrude on any "rule of thumb" you might come up with for data retention.
Performance will be affected by how often you defragment the indices, and how much data (by volume) is stored for queries.
I have worked on a system that had a 5 YEAR retention, but this was more that they didn't have any autopruning on their data_engine rather than design. When they got up to 2 TB of data in the database, they figured they were storing too much.
Another performance hit will be your physical disk IOPS rating. Is it on a shared drive? Is it on a VM Host?
I have also worked in a very large environment that stored 1.8TB, but that only covered 90 days of data. We worked on a plan for pruning how much QOS they were tracking (in this case EVERY QOS metric on the interface_traffic probe for EVERY port).
With Historic and Raw data tables, the difference is set in data_engine. Raw is every single data point. Historic is run every 24 hours by default and the data_engine slices the day up into 24 and averages the amount in each slice and stores that as the historic data. Unfortunately it was never implemented in Nimsoft, so there is nothing that actually queries or uses that data. You can create your own reports from it, though, using iReports or Crystal Reports.
What I have done on other SQL servers, but not for Nimsoft was to backup the database at year end, restore it to a new name (ex. NimsoftSLM2012). This way you can query this database with iReports or CR without affecting current performance of NimsoftSLM. You'll just have to have enough storage to store as many years as you'd like.
The database server is sitting in a VM environment, in a shared SAN storage environment with fast tiering when required.