News & Events

Microsoft SQL Server – Partitioning and Compression

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.sql-server-2012

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.

 

Get in touch

For more information or to schedule a demo please contact us

Contact us

News

dsp drives up its share of the Database MSP Market with
record contract growth and 2nd acquisition

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
View All

Event

Applications on Oracle Database Appliance (ODA)

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
View All

Blog

Solving the UK's productivity puzzle - working harder or smarter?

How can your Manufacturing business benefit from constant production management by adopting data modernisation, digital transformation and the cloud?

Read more
View All