We've recently migrated to the ESRI platform and are still trying to get all the bugs worked out and everything running properly. When we initially set up our databases in SQL Server 2012, we did not define a maintenance plan. We are at a point now where we need to be maintaining and backing up our databases routinely and I was hoping to automate that process.
I've done some reading on the web and it seems that we need to be performing some maintenance items along the lines of compressing the databases, rebuilding indexes, updating statistics, etc. SQL Server allows me to configure a maintenance plan that will perform these tasks, however, all of the examples show doing these tasks through ArcCatalog. My question is are these tasks better handled in ArcCatalog or in SQL Server? Also, if handled in SQL Server, are there any details/tricks/tips I need to be aware of?
Below is a list of maintenance plan options in SQL Server 2012 for reference:
Check Database Integrity, Shrink Database, Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, Execute SQL Server Agent Job, Back Up Database (Full), Back Up Database (Differential), Back Up Database (Transaction Log), Maintenance Cleanup Task
Thanks in advance!
While the Script is a good resource to check, you should read about the concepts to get a better idea.
Example: The compress operation for the Enterprise Geodatabase is not the same as traditional Compress\Shrink (in case of SQL Server).
So my suggestion would be to read about these first, compare it with the SQL Server maintenance tasks and then decide how you want to get these done.
Here are some links for reference: