Solved

Archiving sql data to Amazon S3

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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