请教sql server DB 大侠 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

请教sql server DB 大侠

请教sql server DB 大侠# Database - 数据库

m*y2007-01-23 08:01

1 楼

要是我有些production db在好几个sql server上, 现在要把这些db create 一个
offline的db copy用于别的reporting. 第一次我可以restore backup files, 然后根
据reporting需要作些index调整什么的，　然后我想要create nightly job to do
incremental data load from the production dbs to offline dbs，不做schema
changes.　问题是我怎么才能作incremental load? 怎么来track changes on
production db 做synchronization? 我知道有些utility象sql data compare可以一个
一个db　compare, 但是我想要的是schedule一个job同时能做好多个db的incremental
data load. Any scripts or reference or advice is appreciated.

a*t2007-01-23 08:01

2 楼

probably Log Shipping?

incremental

【在 m******y 的大作中提到】

: 要是我有些production db在好几个sql server上, 现在要把这些db create 一个
: offline的db copy用于别的reporting. 第一次我可以restore backup files, 然后根
: 据reporting需要作些index调整什么的，　然后我想要create nightly job to do
: incremental data load from the production dbs to offline dbs，不做schema
: changes.　问题是我怎么才能作incremental load? 怎么来track changes on
: production db 做synchronization? 我知道有些utility象sql data compare可以一个
: 一个db　compare, 但是我想要的是schedule一个job同时能做好多个db的incremental
: data load. Any scripts or reference or advice is appreciated.

x*e2007-01-23 08:01

3 楼

search for DB replication for the second question.
for the first one, I recommend you create a seperate DB for DB warehousing
or other usages.
It all depends on what kind of Production DBs you got.
I assume they are OLTP, not OLAP.
It also depends on what kind of reporting and how fast you want that done.
How big the DBs are, how powerful your production servers and the offline
servers are, etc.
Anyway, my point is your question is too large to give a simple solution.

incremental

【在 m******y 的大作中提到】

L*e2007-01-23 08:01

4 楼

log shipping没戏，俩办法
1. replication
2. script
第二个自己定制，怎么搞都可以.

后根
一个
Nights or Counter-Strike, Work or BBS, Car or Motorcycle, Computer or woman.

【在 a*******t 的大作中提到】

: probably Log Shipping?
:
: incremental

m*y2007-01-23 08:01

5 楼

production DB is OLTP. We do want a OLAP to do the reporting. But because of
time constraints, we will only starts with OLTP dbs copying to offline
servers insteading of doing all reporting on OLTP dbs right now.
It's easy to get the backup of OLTP dbs and restore them on the offline
servers, but it seems not very efficient. If we want to make changes on
offilne server dbs like tuning index better for reporting, it has to be made
every time we restore them from production dbs. So I'm wondering h

L*e2007-01-23 08:01

6 楼

script估计你们更些才了，还是把repl搞定吧，architecture怎么不行了？

of
made
can
that?
as

【在 m******y 的大作中提到】

: production DB is OLTP. We do want a OLAP to do the reporting. But because of
: time constraints, we will only starts with OLTP dbs copying to offline
: servers insteading of doing all reporting on OLTP dbs right now.
: It's easy to get the backup of OLTP dbs and restore them on the offline
: servers, but it seems not very efficient. If we want to make changes on
: offilne server dbs like tuning index better for reporting, it has to be made
: every time we restore them from production dbs. So I'm wondering h

x*e2007-01-23 08:01

7 楼

If your DB is only used to record inserting data and seldom do delete or
update.
Then, you can record update/insert date as a seperate column.
Use "select into" to do incremetal load and
if your DB is big, you will have to think about table partition.
For OLAP reporting, do you need the data warehouse and mart at the same
server
or you need a data warehousing server and a mart server?
different approach will lead to different design.
restore backups is not a quite efficient way.
If you have to d

【在 m******y 的大作中提到】

m*y2007-01-23 08:01

8 楼

Thanks guys. Since we have daily backups, I restore db first instead of
incremental load since it seems much easier. Later we will come up a data
warehouse schema to completely offline the reporting from production dbs.