Efficient database search and reporting, such as for enterprise customers having large and/or numerous files
View Patent ↗This application discloses a server for handling data reporting requests in a system that also comprises storage managers, primary storage devices, and secondary storage devices connected over one or more networks. The server receives, from each storage manager, a copy of data associated with the storage manager, and stores the received copies in one or more local databases. The server builds offline one or more indices for part or all of the received copies to improve query processing against the one or more local databases. Next, the server receives a request over a network from one of the storage managers or a standalone console, which received the request from a user for a report of data associated with the storage managers. The server produces a data report in response to the request, using the one or more indices and without impacting performance of the storage managers.
1. A reporting system comprising:
one or more processors; and
one or more non-transitory, computer-readable memory storing computer-executable instructions that, when executed by the one or more processors, configure the system to perform a plurality of operations comprising:
receiving, from one or more storage managers, a first copy of live management database data associated with its corresponding storage manager, wherein each of the one or more storage managers is associated with a corresponding management database that stores the corresponding first copy of live management database data;
storing the first copy of the live management database data as an offline copy of management database data into one or more local databases of a reporting server;
prior to a data reporting request, building one or more indices for the one or more local databases based on selected one or more tables from the one or more local databases according to one or more criteria, wherein:
each table of the selected one or more tables satisfies the one or more criteria, and,
a first criterion of the one or more criteria is based on a relative characteristic for a corresponding table of the selected one or more tables, wherein the relative characteristic is based on a specific timeframe of usage for the corresponding table;
receiving, by the reporting server, the data reporting request for a report of metadata associated with the one or more storage managers, wherein the data reporting request includes an indication of whether a search corresponding to the data reporting request is to be performed against a particular subset of the one or more local databases of the reporting server or all the local databases of the reporting server;
producing, by the reporting server, a report based on processing the data reporting request using the one or more indices, wherein the one or more local databases is searched based on the indication included within the data reporting request;
updating the one or more indices based on the one or more criteria, wherein updating comprises removing at least one index from the one or more indices where at least one table corresponding to the at least one index fails to satisfy at least one criterion from the one or more criteria; and
sending, by the reporting server, the data report to a requesting user.
2. The reporting system of claim 1 , wherein the plurality of operations further comprises:
selecting one or more tables from the one or more local databases for indexing based on the one or more tables satisfying one or more criteria selected from the group comprising:
the one or more tables being a largest table in the one or more local databases; and
the one or more tables being most frequently used within the one or more local databases.
3. The reporting system of claim 1 , wherein the plurality of operations further comprises:
determining whether a temporary location different from the one or more local databases includes the first copy of the live management database data; and wherein:
storing the first copy of the live management database data as the offline copy of management database data is based on a determination that the temporary location includes the first copy of the live management database data.
4. The reporting system of claim 1 , wherein the updating occurs according to at least one of:
a regular schedule;
a local database update; and
based on a certain amount of data having been changed in the one or more tables of the one or more local databases.
5. The reporting system of claim 1 , wherein the plurality of operations further comprises:
removing, by the reporting server, the one or more indices based on analyzing queries submitted against the one or more management databases of the one or more storage managers.
6. The reporting system of claim 1 , wherein the plurality of operations further comprises:
storing a second copy of live management data at a temporary location different from the one or more local databases of the reporting server; and
selectively incorporating the second copy of live management data stored at the temporary location into the one or more local databases by overwriting the data stored in the one or more local databases of the reporting server with the second copy of live management data.
7. The reporting system of claim 1 , wherein the plurality of operations further comprises:
storing a second copy of live management data at a temporary location different from the one or more local databases of the reporting server; and
selectively incorporating the second copy of live management data stored at the temporary location into the one or more local databases by updating the data stored in the one or more local databases of the reporting server based on the second copy of live management data.
8. A method comprising:
receiving, from one or more storage managers, a first copy of live management database data associated with its corresponding storage manager, wherein each of the one or more storage managers is associated with a corresponding management database that stores the corresponding first copy of live management database data;
storing the first copy of the live management database data as an offline copy of management database data into one or more local databases of a reporting server;
prior to a data reporting request, building one or more indices for the one or more local databases based on selected one or more tables from the one or more local databases according to one or more criteria, wherein:
each table of the selected one or more tables satisfies the one or more criteria, and,
a first criterion of the one or more criteria is based on a relative characteristic for a corresponding table of the selected one or more tables, wherein the relative characteristic is based on a specific timeframe of usage for the corresponding table;
receiving, by the reporting server, the data reporting request for a report of metadata associated with the one or more storage managers, wherein the data reporting request includes an indication of whether a search corresponding to the data reporting request is to be performed against a particular subset of the one or more local databases of the reporting server or all the local databases of the reporting server;
updating the one or more indices based on the one or more criteria, wherein updating comprises removing at least one index from the one or more indices where at least one table corresponding to the at least one index fails to satisfy at least one criterion from the one or more criteria;
producing, by the reporting server, a report based on processing the data reporting request using the one or more indices, wherein the one or more local databases is searched based on the indication included within the data reporting request; and
sending, by the reporting server, the data report to a requesting user.
9. The method of claim 8 , further comprising:
selecting one or more tables from the one or more local databases for indexing based on the one or more tables satisfying one or more criteria selected from the group comprising:
the one or more tables being a largest table in the one or more local databases; and
the one or more tables being most frequently used within the one or more local databases.
10. The method of claim 8 , further comprising:
determining whether a temporary location different from the one or more local databases includes the first copy of the live management database data; and wherein:
storing the first copy of the live management database data as the offline copy of management database data is based on a determination that the temporary location includes the first copy of the live management database data.
11. The method of claim 8 , wherein the updating occurs according to at least one of:
a regular schedule;
a local database update; and
based on a certain amount of data having been changed in the one or more tables of the one or more local databases.
12. The method of claim 8 , further comprising:
removing, by the reporting server, the one or more indices based on analyzing queries submitted against the one or more management databases of the one or more storage managers.
13. The method of claim 8 , further comprising:
storing a second copy of live management data at a temporary location different from the one or more local databases of the reporting server; and
selectively incorporating the second copy of live management data stored at the temporary location into the one or more local databases by overwriting the data stored in the one or more local databases of the reporting server with the second copy of live management data.
14. The method of claim 8 , further comprising:
storing a second copy of live management data at a temporary location different from the one or more local databases of the reporting server; and
selectively incorporating the second copy of live management data stored at the temporary location into the one or more local databases by updating the data stored in the one or more local databases of the reporting server based on the second copy of live management data.
15. A non-transitory, computer-readable memory storing computer-executable instructions that, when executed by one or more processors, configure a system to perform a plurality of operations comprising:
receiving, from one or more storage managers, a first copy of live management database data associated with its corresponding storage manager, wherein each of the one or more storage managers is associated with a corresponding management database that stores the corresponding first copy of live management database data;
storing the first copy of the live management database data as an offline copy of management database data into one or more local databases of a reporting server;
prior to a data reporting request, building one or more indices for the one or more local databases based on selected one or more tables from the one or more local databases according to one or more criteria, wherein:
each table of the selected one or more tables satisfies the one or more criteria, and,
a first criterion of the one or more criteria involves is based on a relative characteristic for a corresponding table of the selected one or more tables, wherein the relative characteristic is based on a specific timeframe of usage for the corresponding table;
receiving, by the reporting server, the data reporting request for a report of metadata associated with the one or more storage managers, wherein the data reporting request includes an indication of whether a search corresponding to the data reporting request is to be performed against a particular subset of the one or more local databases of the reporting server or all the local databases of the reporting server;
producing, by the reporting server, a report based on processing the data reporting request using the one or more indices, wherein the one or more local databases is searched based on the indication included within the data reporting request;
updating the one or more indices based on the one or more criteria, wherein updating comprises removing at least one index from the one or more indices where at least one table corresponding to the at least one index fails to satisfy at least one criterion from the one or more criteria; and
sending, by the reporting server, the data report to a requesting user.
16. The non-transitory, computer-readable memory of claim 15 , wherein the plurality of operations further comprises:
selecting one or more tables from the one or more local databases for indexing based on the one or more tables satisfying one or more criteria selected from the group comprising:
the one or more tables being a largest table in the one or more local databases; and
the one or more tables being most frequently used within the one or more local databases.
17. The non-transitory, computer-readable memory of claim 15 , wherein the plurality of operations further comprises:
determining whether a temporary location different from the one or more local databases includes the first copy of the live management database data; and wherein:
storing the first copy of the live management database data as the offline copy of management database data is based on a determination that the temporary location includes the first copy of the live management database data.
18. The non-transitory, computer-readable memory of claim 15 , wherein the updating occurs according to at least one of:
a regular schedule;
a local database update; and
based on a certain amount of data having been changed in the one or more tables of the one or more local databases.
19. The non-transitory, computer-readable memory of claim 15 , wherein the plurality of operations further comprises:
storing a second copy of live management data at a temporary location different from the one or more local databases of the reporting server; and
selectively incorporating the second copy of live management data stored at the temporary location into the one or more local databases by overwriting the data stored in the one or more local databases of the reporting server with the second copy of live management data.
20. The non-transitory, computer-readable memory of claim 15 , wherein the plurality of operations further comprises:
storing a second copy of live management data at a temporary location different from the one or more local databases of the reporting server; and
selectively incorporating the second copy of live management data stored at the temporary location into the one or more local databases by updating the data stored in the one or more local databases of the reporting server based on the second copy of live management data.