Initial setup:
Setting up MR in ME doesn’t necessarily have to be done in any particular order, but doing it in a specific order and validating it along the way makes things easier to troubleshoot should something go wrong. So we’re going to set it up in the order of management, then reporting.
Setting up Management:
- Make sure that there's an entry in dbo.ConnParameters for MRImport. If not, then copy one of the other columns and change the CollectionName and AppName columns to the correct settings. You’ll need to make sure there is an entry for each of the items above.
2. Run MRSettingsImport.exe SLA to do an initial import of all your current settings. We set it up to run by SLA because in case you have a large environment, you can break up the imports by order of importance. This also allows you to investigate any issues before moving on to other environments.
Now that the settings are imported you should check that everything came in ok. Check all of the dbo.IndexSettings* tables as well as the dbo.DBMaintVersion table to make sure that the MR versions were imported. This DBMaintVersion table is very useful in managing your environment because it’s kept up to date, so you’ll always know when servers aren’t on the latest version of MR.
3. If all the settings were imported, then it’s time to test a settings push. One of the best features of MR in ME is the ability to change your settings for a lot of your servers from a central location. So add a dummy row into the dbo.IndexMaintSettingsDB table and then manually run the Push job (MRPropertyPush). You should see the new row on the server.
INSERT INTO [dbo].[IndexSettingsDB](InstanceID, DBName, Exclude, ReindexGroupOrder, ReindexOrder, ReorgThreshold, RebuildThreshold, FILLFACTORopt, PadIndex, ONLINEopt, SortInTempDB, MAXDOPopt, DataCompression, GetRowCT, GetPostFragLevel, UpdateStatsOnDefrag, StatScanOption, IgnoreDupKey, StatsNoRecompute, AllowRowLocks, AllowPageLocks, WaitAtLowPriority, MaxDurationInMins, AbortAfterWait, PushToMinion, LogIndexPhysicalStats, IndexScanMode, DBPreCode, DBPostCode, TablePreCode, TablePostCode, LogProgress, LogRetDays, LogLoc, MinionTriggerPath, RecoveryModel, IncludeUsageDetails, StmtPrefix, StmtSuffix, RebuildHeap, Push, LastUpdate)
VALUES
(1, N'DummyDB', 0, 2, 0, 10, 20, 90, 'ON', NULL, NULL, NULL, NULL, 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 1, 60, NULL, NULL, NULL, 1, NULL, NULL, NULL, 1, N'2017-02-09T14:05:22.99' )
5. If the dummy row got pushed successfully, then you’re free to delete it using the same mechanism. The only trick with deleting rows out of the ME tables is that when you delete a row, you can’t set Push = 1 for that row, can you? So in this case, you need to delete the unwanted row, and then set Push = 1 for one of the other rows for that server. Remember when you set one row to push, it pushes all the rows for that InstanceID. So in this case, since you’ve deleted the row, then you set one of the others for that InstanceID to Push = 1, and all of the current rows for that InstanceID get pushed. When the push occurs, the Push job truncates the table on the client server and just imports everything from the ME table.
Setting up Reporting:
The reporting piece of MR consists of importing the reindex logs and configuring the reindex reporting thresholds. For starters, let's configure the log imports.Since it's likely you've had MR running in your environment as a standalone install, you've likely got plenty of data in your backup log tables that needs to be imported into ME. To do that, you're going to run the MRImportAllLogs.exe program. Open a PowerShell window and type ./MRImportAllLogs.exe SLA
6. If the logs imported without any errors then make sure you've got the number of rows you're expecting by inspecting the collector tables. You want to look at Collector.BackupFiles, Collector.BaclupLog, and Collector.BackupLogDetails. If everything checks out then you're ready to go to the next step.
7. Now it's time to configure ME to import the logs as the reindexes complete. This setup is really easy. Let's get started. The first thing you need to do is to pick a location for the trigger files to be dropped by each client server. This will be a file share, typically on the ME server, but it can be anywhere as long as the client servers and the ME server have access to it.
Officially speaking, every client server doesn't have to push its trigger files to the same location as the rest. You can spread it out across many servers if you like. However, spreading out your trigger files like that will make administration slightly more difficult, and will also potentially slow down the import process. You also need to make sure that all the SQL Agent accounts for all the client servers and read/write permissions to the share. Quite often the trigger share will be put onto the same drive as the backups as all client servers already have access to that location. Just make sure you don't put the trigger share locally on each server. This defeats the purpose of doing centralized collections, so put them on a network location. Once the trigger file share is setup, you may now setup the clients to save the trigger files to it.
Update the right columns in dbo.IndexMaintSettingsDB in ME.
UPDATE dbo.IndexMaintSettingsDB
SET PushToMinion = 1,
TriggerPath = '\\TriggerLocation\Share\', --Must end in a '\'.
Push = 1;'
Now run the Push job to save this new data to each client server. The trigger configuration has now been saved to each client server, and MR should now be creating trigger files as reindexes finish. You can check this by watching the trigger share folders, and as the reindexes finish you'll see files appear.
The last piece to making sure you're configured properly is configure your alert thresholds in the dbo.ReindexReport table.
Related Articles:
Troubleshooting Minion Backup collections in Minion Enterprise
Understanding the Minion Backup History Collection Process
Using Setup.BackupReportThreshold