Solved

Archiving sql data to Amazon S3

Posted on 2014-10-14
1
208 Views
Last Modified: 2014-10-16
Hi

On Amazon cloud platform, we have sql server databases on ec2 instances with data storing on EBS volumes.

one of our database has highest writes and the database size is getting bigger, as we know the data storing on EBS volumes are bit expensive,

Our requirement is to maintain one to three months data on EBS storage and the data which is more than three months old should be archived to S3, which gives us a direct chance to look on S3 for data which is 3 months old.

Would that be possible to store the archived database files on S3?

If so how can we retrieve the archived data from S3 when needed?

if we go for partitioning tables on sql server, the data will be on EBS volume itself, but here we want to archive it to s3 and when ever user wants to check for the history they should able to retrive directly from s3.

Thanks in advance.
0
Comment
Question by:ccfcfc
1 Comment
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 40380361
"Would that be possible to store the archived database files on S3? "

Yes that should be possible assuming you can create a "Archive" database on the S3 storage from the same SQL Server or in other words that SQL Server could access both the EBS and S3 volumes.

"if we go for partitioning tables on sql server, the data will be on EBS volume itself, but here we want to archive it to s3 and when ever user wants to check for the history they should able to retrive directly from s3."

Assuming your database table structure is not changing too much/too often you could leave your active portion of the data in the SQL database(s) stored on the  EBS volume and "switch out" then (attach) "switch in" the partitioned/aged data to a secondary "Archive" database created on S# volumes attached to that same SQL (or different one if the existing can't connect to both EBS and S3 at the same time).

Partitioning is possible however before you start you need to consider that a SQL server table can be partitioned on only 1 column so ALL tables to be partitioned must have that (date/time in your case) column to be partitioned aside of a clustered index (not mandatory on the same column but should be included). Other thing to consider is that if you want to partition an existing large table(s) that may be a huge pain (and cost) considering data movements and in particular for 24/7 online systems.
As much complexity as this may seem to add I would do it with a new table where you could for instance create a view on the top of it to include (retrieve data from) the "Archived" database table as well.

http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/
http://sqlsaga.com/sql-server/how-to-partition-an-already-existing-table-in-sql-server/

http://msdn.microsoft.com/en-us/library/ms188730.aspx


Lot more at links below:
http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/
http://top-performance.blogspot.ca/2009/02/sql-server-partitioning-bad-good-and.html
http://www.brentozar.com/archive/2013/04/cloud-alternatives-to-microsoft-sql-server/
http://www.brentozar.com/sql/table-partitioning-resources/
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now