Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data archiving and replicating database

Posted on 2013-01-23
3
Medium Priority
?
198 Views
Last Modified: 2013-01-30
Hi,

I need advise with regards  to archiving old data from our prod database
since db size has increased
All tables in the database do not have a date column but they do relate to the tables
which have date columns
Users might require to access this old database for reporting or data analysis
once in a while, so it needs to exist on a remote server for them to access

Currently this prod database is getting replicated to a  reporting server.
So now once the data is archived we still would require to replicate both the archived and
the current prod data to the reporting server on a single db

Please suggest how this can be achieved??

Thanks
0
Comment
Question by:Sonali Patade
[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
  • 2
3 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38813295
For starters, does historic data change at all? If so, how far back? I ask because, if historic data is static, you don't need to worry about ongoing maintenance - once it's been replicated to the reporting server, you no longer need to keep it syncronized and you can delete it from the production server (or move it to the archive server you're referencing). Also, are you actually using SQL Replication, or are you using "Replication" to mean some kind of ongoing ETL process that moves older data to your reporting server?

If you're using SQL Replication, then both copies of the database (production and reporting) need to be exactly the same, data-wise. If they're the same, then why do you need to archive server? It seems like you'd just be maintaining a second copy of the data that's already on your reporting server.

If you're using some kind of ETL process, then you're free to delete data from your source server without worrying about the impact on the reporting server, so you can go forward with the Archive server you're talking about. You can set up a second ETL process (or extend the existing one) to also copy historic data to the archive server, and then delete it from your source server. As long as the records your working with have a date you can attach to them (through a join, it sounds like), then you can trim old data from your production server and move it to your archive.

Since the data has already been moved from production to the reporting server previously, you don't need to worry about syncing up anything between the archive and reporting servers.
0
 

Author Comment

by:Sonali Patade
ID: 38815546
Hi,

The historic data would not change but we need to archive this old data to archive server
as well replicate the old and the current data to the reporting server with some kind of
ETL strategy. Currently we are using transactional repication to replicate the data from
Prod server to the reporting server.
Do you think if this could be achieved using  merge replication between the production data and the archived data.

Also not all the tables on the prod server has date column in it , but the ones which do not
have date column are referencing the tables which do have date column, so can
we still archive the data from all tables.

Is their some kind of reference you could provide which explains such a solution.

Thanks
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 1500 total points
ID: 38816232
If you're doing custom ETL, then you can filter it however you want - if you have a date in your table, that's great, but you can filter by a date in another table that you can join to if you want to do that.

The problem with using standard transactional replication to get your data from the production to the reporting server is that, when you move the rows to the archive server and delete them from production, you'll also replicate that delete to the reporting server and remove them that server too. However, you can configure the subscription to not push delete statements to the subscribers:

http://www.sqlservercentral.com/articles/Replication/3202/

If you do that, then you're safe to delete data on your source database and not have it disappear from your reporting server (assuming deletes aren't a normal part of application activity that you actually require to be replicated for proper activity).
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

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