Solved

Archiving sql data to Amazon S3

Posted on 2014-10-14
1
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 40

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Is your phone running out of space to hold pictures?  This article will show you quick tips on how to solve this problem.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

739 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