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
Solved

Archiving sql data to Amazon S3

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Concerto Cloud Services, a provider of fully managed private, public and hybrid cloud solutions, announced today it was named to the 20 Coolest Cloud Infrastructure Vendors Of The 2017 Cloud  (http://www.concertocloud.com/about/in-the-news/2017/02/0…
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
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.
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

838 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