I was recently commissioned to complete an archiving project for one of our customers. The customer did not have a SQL Server DBA resource in house to develop the solution, and would need it automated with very little or no administrative intervention. The solution is hosted on a SQL Server 2012 Enterprise instance.
The solution required 1 month of data stored in the live application database, and 1 week of data stored inside the archive database. The aim is to manage the storage requirements for the application database servers. The new application has only been launched for a few months, and has approximately grown in 60GB of data.
The partitioning of the live database focused on the tables that incurred the huge growth in data, since the launch of the application. The datetime column in the target tables were used to define the partitioning function. The partition function was defined to run up to December 2019, which contained 72 partitions; this is sufficiently under the 15,000 partition limitation for SQL Server 2012.
The automated process to archive from the live target tables to the archive tables and the archive table clean-up process ran once a month. Depending on the time of the month, there could be up two months of data in the live database. The automated archiving and clean-up process ran under two SQL Agent jobs. The archive tables used Page level compression, to further manage the storage requirements of the server.
The business benefits from this solution included:
• Seamless automated archiving process,requiring no action from IT
• Storage cost savings using compression on the archive tables, the savings were calculated by using the ‘sp_estimate_data_compression_savings’ stored procedure
The next blog in this series will discuss the technical workflow of designing and implementing partitioning and compression.
For more information or to schedule a demo please contact usContact us
DSP firmly established itself as one of the UK’s fastest growing proactive Database MSPs during 2016, signing £2.1m of new contracts and making its second acquisition, the Oracle DBA support division of IT Services provider ITSB.Read more
A main goal... how to future proof your applications environment through Oracle while becoming more efficient in terms of costs and productivity.
As well as the option of developing the fundamental steps you need to take to get ...Read more
In the age of big data, public cloud, private cloud it’s easy to be pushed into constantly thinking about the future... What should you optimise though and how can you be sure it’ll make a difference? Would knowing it could give you a competitive edge be worth considering?Read more