You, being the “Microsoft guy” (or gal) in your organization, have been saddled with the task of keeping the BI system’s back-end database properly maintained. You’re not necessarily a SQL Server expert, and you’re definitely not a BI expert. You’re a “reluctant DBA,” and you just need to know enough about SQL Server BI performance to keep the thing running smoothly.
Here’s what you need to know:
Don’t mess with the database design, and definitely don’t mess with the indexes that have been created on the database. BI systems are incredibly finicky about their database schema, and performance is heavily dependent upon the indexes.
Do maintain the indexes. If you can, ask the folks who created the system how often those indexes should be rebuilt. If the BI system is routinely loaded with data (a very common approach), then you’ll often want to at least reorganize the indexes after every major data load. That can take a long time on large data warehouses, so be sure to plan the reorganization as part of a scheduled maintenance plan. Some BI systems are set up to do this themselves; ask the people who implemented it if that’s the case with your system.
Don’t load other tasks onto the database server that drives the BI system. SQL Server BI performance is often calculated with the assumption that the BI system will be the only thing using the server, and adding workload will affect that performance.
Do keep Windows and SQL Server properly patched, just as you would for any machine.
Don’t upgrade SQL Server versions without first ensuring that you won’t be causing any incompatibility issues. A new version of SQL Server will rarely produce any kind of immediate gain unless the BI system can take advantage of specific new features, so there’s rarely a pressing need to upgrade SQL Server. It’s very common, in fact, for organizations to be running several versions of SQL Server, since applications are almost always paired to the specific version they were written for.
Do find out if you need to back up the BI system’s databases. The answer is usually yes, of course, but not always. Some BI systems get data directly from production systems, meaning the BI system may not actually contain copies of that data. Understanding if, and, more important, how often, backups are needed is very important. Also make sure you understand what kinds of backups are necessary. For example, some BI databases are built to run without transaction logs, making more-frequent full data backups advisable.
You can get the answers to most of your “what do I need to maintain” questions from whoever set up the BI system, and it’s a good idea to document those answers for posterity.
ABOUT THE AUTHOR
Don Jones is a senior partner and principal technologist at strategic consulting firm Concentrated Technology. Contact him via www.ConcentratedTech.com.