Billing Database and Log File Configuration

Billing Database and Log File management and monitoring

Billing Database, DB Log File and SQL Server management has to be incorporated into the IT Operations and Monitoring process of the individual companies. As the product process's lots of data, the DB size keeps growing and a poorly configured Database may grow its log size and occupy lots of disk space than necessary. Data is the integral part of the Usage Collection and Billing, so proper planning of the configuration, BCDR and monitoring is essential for a Enterprise grade production deployment.

SQL Server Configuration:

It is important to have very good configuration with support for a good Disk IOPS for the SQL Server machine hosting Billing DB.

Placing Billing DB and Log files on a SSD will improve performance and processing throughput tremendously or use RAID based drive configuration with good amount of h/w caching.

Note: Billing DB Log file size grows really large over time. To contain the log file size growth set the DB recovery mode to Simple using the following SQL statements. (Consult with your DBA for any organizational policies related to backups before doing these on production database instances and adjust as appropriate)

USE [CloudAssertBillingDb]

ALTER DATABASE [CloudAssertBillingDb] SET RECOVERY SIMPLE

DBCC SHRINKDATABASE ('CloudAssertBillingDb', 0);

DBCC SHRINKFILE('CloudAssertBillingDb', 200);

CHECKPOINT

CHECKPOINT

You can check file sizes using the following statements:

SELECT file_id, name, type_desc, physical_name, size, max_size FROM sys.database_files ;

Database Backups

It is important to setup periodic backup for the Billing database. Frequency ultimately depends on each organizations policies and SLAs, but at least once a day backup is recommended.