Troubleshooting
Problem
Customer is hosting their Controller database using Microsoft SQL Server. Customer would like to:
- maximise performance
- make regular backups
- keep the database in good condition (regular trimming of transaction logs).
How can the customer maintain their SQL databases, in order to maximise performance/speed and minimise system downtime?
Symptom
If regular maintenance is not performed on the Controller database, then the following can occur:
- Poor performance, caused by out-of-date database statistics
- Poor performance, caused by out-of-date (fragmented) database indexes
- SQL server downtime caused by running out of hard drive space
- Data loss caused by lack of backups.
Cause
By default, Microsoft SQL only performs minimal maintenance on its databases.
- Unless your I.T. department's SQL administrator ("DBA") runs a scheduled (regular) task to perform maintenance on your databases, performance problems and downtime/data-loss can occur.
Environment
Customer's Controller database hosted on Microsoft SQL server.
Resolving The Problem
Consult your I.T. department's database server administrator (DBA) to ensure that your database(s) are regularly optimised.
However, as a simple starting point for experienced DBAs (or as an overall guide for inexperienced Microsoft SQL administrators) IBM Support recommends that customers create a SQL database maintenance plan to automatically/regularly perform some tasks (in the background).
Example:
For most customers (read attached PDF document in full for exceptions), they should aim to:
(1) Reorganise and re-index the database
(2) Update the database 'statistics'
(3) Backup databases
(4) Backup transaction logs
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optionally (especially if the SQL server has small amount of disk space), they might also want to:
(5) Truncate transaction logs
However, the instructions in this Technote do not cover automating that step.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Steps:
The instructions below are based on Microsoft SQL 2014, but (in general) the process is very similar for SQL 2012, 2008 or 2005
* PART ONE - Daily routines *
1. Logon to the SQL server as a Windows administrator
2. Launch 'SQL Server Management Studio'
3. Expand 'Management'
4. Right-click on 'Maintenance Plans' and choose 'Maintenance Plan Wizard':
5. Inside the wizard, choose settings appropriate to you, for example:
In other words, it will look similar to:
6. Click OK
7. Tick (select) the following 4 maintenance tasks:
8. Click Next
9. Accept the default task order
10. Click Next
11. Unless you have specific requirements (for example you only want to perform the task on some databases) choose 'All user databases':
In other words, our maintenance plan will affect *all* user (non-system) databases (not just the main Controller database).
Keep the other settings as the default values ("All existing statistics", "Full Scan").
12. Click Next
13. Choose 'All user databases':
14. Choose a sensible location for the backups
If required, tick/enable 'Verify backup integrity':
The author’s personal preferences are shown above.
15. Choose similar settings for your Transaction Log backups
16. Finally, we shall automate the deleting of the ‘old’ *.BAK (data) and *.trn (transaction log) backups by choosing the following options:
VITAL: You must remember to ensure that your backup (.BAK) files are archived to a long-term secure storage.
TESTING: It is recommended that you periodically check (ideally via an automated alert) that your SQL server’s disk does not get full (over time), caused by the size of the Controller databases growing over time.
=====================================================
* PART TWO - Weekly routines *
17. Right-click on 'Maintenance Plans' and choose 'Maintenance Plan Wizard':
18. Inside the wizard, choose settings appropriate to you, for example:
In other words, it will look similar to:
19. Click OK
20. Tick (select) the following 2 maintenance tasks:
21. Click Next
22. Accept the default task order
23. Click Next
24. Unless you have specific requirements (for example you only want to perform the task on some databases) choose 'All user databases':
In other words, our maintenance plan will affect *all* user (non-system) databases (not just the main Controller database).
Keep the other settings as the default values ("All existing statistics", "Full Scan").
25. Click Next
26. Choose 'All user databases':
27. Choose 'All user databases':
====================================================
* PART THREE - Testing *
28. For the jobs to run, you must make sure that the Windows service 'SQLSERVERAGENT' is set to Automatic and 'Running':
29. You can manually test your routines (whenever you like), by manually right-clicking on the plan, and choosing 'Execute':
30. Finally, it is recommended that customers periodically (e.g. weekly or at least monthly) double-check that their plans are working OK for all databases.
VITAL: Customers are urged not to simply trust that their backups are working OK. They need to periodically perform a 'test' restore, just to be sure!
In particular, it is 100% recommended that periodic checks are made that the backup (.BAK) files are:
- Your DBA should be experienced in database optimise techniques, and should also be able to tune those techniques to work best in your specific environment.
- For more in-depth advice on third-party (non-IBM) Microsoft SQL maintenance routines, see separate third-party articles such as the MS TechNet article (linked at the end of this Technote).
However, as a simple starting point for experienced DBAs (or as an overall guide for inexperienced Microsoft SQL administrators) IBM Support recommends that customers create a SQL database maintenance plan to automatically/regularly perform some tasks (in the background).
Example:
For most customers (read attached PDF document in full for exceptions), they should aim to:
(1) Reorganise and re-index the database
- Typically once a week
- For example, schedule to run every Sunday morning at 2am
(2) Update the database 'statistics'
- Every 24 hours
- For example, schedule every evening at 12am (midnight)
(3) Backup databases
- At least every 24 hours
- For example, schedule every evening at 12am (midnight)
(4) Backup transaction logs
- At least every 24 hours
- For example, schedule every evening at 12am (midnight)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optionally (especially if the SQL server has small amount of disk space), they might also want to:
(5) Truncate transaction logs
- Every 24 hours
- For example, schedule every evening at 12am (midnight)
However, the instructions in this Technote do not cover automating that step.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Steps:
The instructions below are based on Microsoft SQL 2014, but (in general) the process is very similar for SQL 2012, 2008 or 2005
- TIP: For reference purposes, the attached document "05. Proven Practice - Creating a SQL 2008 R2 maintenance plan for Controller 10.1.1.pdf" are the instructions/printscreens based on SQL 2008 R2. However, again these exact same instructions will work OK for SQL 2005 and 2008 R1.
* PART ONE - Daily routines *
1. Logon to the SQL server as a Windows administrator
2. Launch 'SQL Server Management Studio'
3. Expand 'Management'
4. Right-click on 'Maintenance Plans' and choose 'Maintenance Plan Wizard':
5. Inside the wizard, choose settings appropriate to you, for example:
- Name: All User DB Backup and update statistics MaintenancePlan
- Description: Every night - backs up all non-system SQL databases and then updates their statistics
- Schedule: Every night at 12am
In other words, it will look similar to:
6. Click OK
7. Tick (select) the following 4 maintenance tasks:
- Update Statistics
- Back Up Database (Full)
- Back Up Database (Transaction Log)
- Maintenance Cleanup Task
8. Click Next
9. Accept the default task order
10. Click Next
11. Unless you have specific requirements (for example you only want to perform the task on some databases) choose 'All user databases':
In other words, our maintenance plan will affect *all* user (non-system) databases (not just the main Controller database).
- This is helpful if you (a) create new databases in the future (b) rename existing databases.
Keep the other settings as the default values ("All existing statistics", "Full Scan").
12. Click Next
13. Choose 'All user databases':
14. Choose a sensible location for the backups
- Make sure that there is sufficient disk space for now and your future needs!
- For example:
If required, tick/enable 'Verify backup integrity':
The author’s personal preferences are shown above.
- During the backup section of the wizard, typically you would create a new folder on a disk with a large amount of free disk space.
- Using the extension “bak” is by convention
- For safety’s sake, choose to “Verify backup integrity”
- Although you could set backup compression here, it is probably easier to simply “Use the default server setting” (see later).
15. Choose similar settings for your Transaction Log backups
- However, you may only be able to choose some of your databases, for example:
16. Finally, we shall automate the deleting of the ‘old’ *.BAK (data) and *.trn (transaction log) backups by choosing the following options:
VITAL: You must remember to ensure that your backup (.BAK) files are archived to a long-term secure storage.
- The concept is that you will have a separate tool (for example a third-party backup agent such as ArcServe/Veritas) which backs up these .BAK & .TRN files to a different (for example tape) destination. That separate tool's job (which runs each night) archives the backup (.BAK and .TRN) files, eventually to an offsite location
- In the above example, all the local backups (stored on the SQL server's hard drive) will be deleted after 2 days (to conserve hard drive space). If the backup file is required after this time/date, then it will need to be recovered from your offsite archival routines.
- Tick “Include first-level subfolders”
- and change the file extension to: *.*
TESTING: It is recommended that you periodically check (ideally via an automated alert) that your SQL server’s disk does not get full (over time), caused by the size of the Controller databases growing over time.
=====================================================
* PART TWO - Weekly routines *
17. Right-click on 'Maintenance Plans' and choose 'Maintenance Plan Wizard':
18. Inside the wizard, choose settings appropriate to you, for example:
- Name: All User DB Weekly Full Optimisation MaintenancePlan
- Description: Check database integrity and Rebuild indexes every Sunday morning
- Schedule: Choose a sensible time (e.g. 2 hours after the backups have started) to give time for other jobs to finish
In other words, it will look similar to:
19. Click OK
20. Tick (select) the following 2 maintenance tasks:
- Check Database Integrity
- Rebuild Index
- [NOTE: Rebuilding the indexes can consume a lot of transaction log disk space, so it is suggested that:
- this task is only performed once a week
- and on a day when other users are unlikely to use Controller to run an index rebuild from inside the Controller GUI 'database optimise' screen]
21. Click Next
22. Accept the default task order
23. Click Next
24. Unless you have specific requirements (for example you only want to perform the task on some databases) choose 'All user databases':
In other words, our maintenance plan will affect *all* user (non-system) databases (not just the main Controller database).
- This is helpful if you (a) create new databases in the future (b) rename existing databases.
Keep the other settings as the default values ("All existing statistics", "Full Scan").
25. Click Next
26. Choose 'All user databases':
27. Choose 'All user databases':
====================================================
* PART THREE - Testing *
28. For the jobs to run, you must make sure that the Windows service 'SQLSERVERAGENT' is set to Automatic and 'Running':
29. You can manually test your routines (whenever you like), by manually right-clicking on the plan, and choosing 'Execute':
30. Finally, it is recommended that customers periodically (e.g. weekly or at least monthly) double-check that their plans are working OK for all databases.
VITAL: Customers are urged not to simply trust that their backups are working OK. They need to periodically perform a 'test' restore, just to be sure!
In particular, it is 100% recommended that periodic checks are made that the backup (.BAK) files are:
- Being created successfully
- Being archived to backup tape successfully
- Can be restored successfully from tape
Related Information
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"ARM Category":[{"code":"a8m50000000KzMEAA0","label":"Optimize Database"}],"ARM Case Number":"TS003957598","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
31 July 2020
UID
swg21396973