将数据从公共生产服务器归档到内部存档服务器
【腾讯云】亏本大甩卖,服务器4核16G 1年370元(带宽12M,系统盘120GB SSD盘,月流量2000GB)!!!!!!
云产品 配置 价格
服务器 1核2G,带宽5M,系统盘50GB SSD盘,月流量500GB 38元/年
MySQL 1核1G 19元/年
服务器 16核32G,带宽18M,系统盘250GB SSD盘,月流量5000GB 1197元/年
点我进入腾讯云,查看更多详情

We have a web-application where - amongst other things - an detailed archive of log-data from embedded devices is kept. We want to keep this detailed data for internal analysis, but this data is not needed on the public accessible production server for longer than three months. The web-application only inserts new data, there are no updates or deletes on the detailed data.

So we want to build an archive system which gets regularly (e.g. daily) a copy of the new data from the production server (including the last three months) and then all data older then three months is removed from the production server.

Unfortunately sometimes some of our embedded devices are offline for some time (days to weeks). In this case it could happen that new data is added to the system for an already copied/archived time.

On the public production server we are using SQL Server 2008 Standard Edition, on the internal archive system we want to use SQL Server 2008 R2 Enterprise Edition to benefit from partitioning and compression for the archive.

At the moment I am considering the following approaches:

  1. Daily replication of data from production to archive system. When old data on production is deleted this should not be replicated to the archive system. I found an option to ignore delete operations on the replication target.

  2. If there was not the requirement to also have the current data on the archive system I regularly would move all data older than a specific time to the archive system. But I have to synchronize the current data and move all old data.

    Maybe I have to use a mixed solution: Replication for synchronizing the current data between archive and production system and moving of old data to an archive table on the archive system (while replicating the deletes for the archived data from the current table to the production system).

What do you think? Do you have some recommendations or know some best practices regarding such problems? Is this topic covered elsewhere (e.g. books)?

Thank you very much in advance.

PS: I am not sure if this question should be asked here or on serverfault. Please move it if my decision posting it here was wrong. Thanks.

#0

About how many tables are we talking that need to be archived?

If it's only one or a few tables, you could just run some SQL regularly via SQL Server Agent.

Something like this (very simplified):

-- copy to archive database
insert into ArchiveServer.dbo.ArchiveTable (Column1, Column2, ...)
select Column1, Column2, ...
from ProductionTableOnThisServer
where DateColumn < dateadd(m, -3, getdate())

-- delete in production database
delete from ProductionTableOnThisServer
where DateColumn < dateadd(m, -3, getdate())

In order for this example to work, it needs to be run on the production server and the archive server must be configured as a linked server in the production server.

Of course this little example is far from perfect (or even production ready!).
This was just to express the basic idea.

In the real world, you probably also want to:

  • wrap both queries together in a transaction so the real data doesn't get deleted if the archiving didn't work for some reason
  • insert only the rows which are really new and update those which have changed
  • and so on...

#1

One thing you could do is have your web app insert in both DBs simultaneously. This way archive is not dependent on prod db. at all.

You may also consider partitioning the prod. DB so that removing data from prod DB is easier. (But since your prod DB is standard edition, that's not an option.) In which case, you may have to delete rows from prod DB. To do the deletes, dont do the entire delete in one transaction. You may want to break it down into batches.

Finally, watch your tlog growth closely while doing the deletes. It can grow big pretty fast.

推荐文章

checkbox列表项签出未签入代码隐藏

checkbox列表项签出未签入代码隐藏

推荐文章

如何实施对象创建的某些方法?

如何实施对象创建的某些方法?

推荐文章

图形布局中断

图形布局中断

推荐文章

jQuery-如何更改Chrome和Safari的对象

jQuery-如何更改Chrome和Safari的对象

推荐文章

调用服务器端方法的锚定按钮

调用服务器端方法的锚定按钮

推荐文章

MVC3 telerik网格中的服务器端寻呼

MVC3 telerik网格中的服务器端寻呼

推荐文章

当在for循环中使用DOM创建行时,如何获得rowIndex

当在for循环中使用DOM创建行时,如何获得rowIndex

推荐文章

如何跟踪软件打开的所有文件?

如何跟踪软件打开的所有文件?

推荐文章

Flash Builder 4.5(移动)-推出数据库更新

Flash Builder 4.5(移动)-推出数据库更新

推荐文章

如何将电子邮件发送到多个电子邮件帐户?

如何将电子邮件发送到多个电子邮件帐户?

推荐文章

接管JSF中的日志处理

接管JSF中的日志处理

推荐文章

如何在运行时从泛型类型定义和运行时类型参数构建Java类型对象?

如何在运行时从泛型类型定义和运行时类型参数构建Java类型对象?

推荐文章

使用闭包编译器时,导出库方法的最佳方法是什么?

使用闭包编译器时,导出库方法的最佳方法是什么?

推荐文章

nslookup:**找不到服务器名称

nslookup:**找不到服务器名称

推荐文章

使用SearchBarDisplay时,如何更改“取消”按钮的名称

使用SearchBarDisplay时,如何更改“取消”按钮的名称

推荐文章

微调器在微调器上滚动所选项目

微调器在微调器上滚动所选项目