Solved

Archiving sql data to Amazon S3

Posted on 2014-10-14
1
217 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 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