Need Datawarehouse

I currently have one server acting as a publisher and distributor. Merge
replication is setup where our shoppes replicate their sales data to the
central server once a week. The home office would like to run consolidated
sales reports on the data. My thought is to keep them off the publisher
database and create a new reporting database. I can then setup the
reporting database as another subscriber. The users can then run reports
against the reporting database. This means my one server will be a
publisher/distributor and subscriber. Is this too much for one server to
handle?
Tina,
it really depends on the overall load. You can make life slightly easier
having a remote distributor, but in your case I'd advise log-shipping to
another server for reporting requirements. This way the report data creation
has no real impact on the production server, as logs are normally created
anyway, rather than having another merge agent active all the time.
HTH,
Paul Ibison
|||Nope. I'd suggest doing what I outlined for several other customers. Setup
a transactional publication against that data on your publisher and then put
a subscriber on another machine where you users can run reports.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Hi Mike,
At this point, I'm limited to just this one server so I need to make the
sure I make the right decisions. There will only be two users running
reports or ad-hoc queries during the day. The shoppes synchronize after
hours between 9:00pm - 2:00am so I don't anticipate the merge agents
struggling with queries for resources.
I appreciate your help!
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:enjxwTvLEHA.556@.tk2msftngp13.phx.gbl...
> Nope. I'd suggest doing what I outlined for several other customers.
Setup
> a transactional publication against that data on your publisher and then
put
> a subscriber on another machine where you users can run reports.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||As noted in my reply to Mike, I'm limited to just one server so I need to
make the best of what I have. Since it's a new deployment it's hard for
me to determine the exact load. A year or so down the road I'll have over
250 subscribers and then the whole ball game changes.
Within the next two months I'm anticipating about 35 subscribers on board.
I appreciate your help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23xspWMvLEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Tina,
> it really depends on the overall load. You can make life slightly easier
> having a remote distributor, but in your case I'd advise log-shipping to
> another server for reporting requirements. This way the report data
creation
> has no real impact on the production server, as logs are normally created
> anyway, rather than having another merge agent active all the time.
> HTH,
> Paul Ibison
>
|||OK, but remember this, everything is hard coded. If you run out of
resources on the publisher and need to move the distributor to another
machine, you will have to completely remove replication from ALL machines
and completely redeploy from scratch. Doing that with 1 - 2 machines is bad
enough, trying to do it with 250 machines when you have an environment
deployed that everyone is using is an entirely different matter altogether.
Not saying it can't be done, but replication is one thing you don't do last
minute planning on and survive to tell about it.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Your point is well taken. Thanks Mike!
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:uCSnfHCMEHA.1192@.TK2MSFTNGP11.phx.gbl...
> OK, but remember this, everything is hard coded. If you run out of
> resources on the publisher and need to move the distributor to another
> machine, you will have to completely remove replication from ALL machines
> and completely redeploy from scratch. Doing that with 1 - 2 machines is
bad
> enough, trying to do it with 250 machines when you have an environment
> deployed that everyone is using is an entirely different matter
altogether.
> Not saying it can't be done, but replication is one thing you don't do
last
> minute planning on and survive to tell about it.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>