Need Help (replication)

Dear All,
I have 2 DB Server, OLTP Server and Report Server.
I want to replicate some tables from OLTP Server to Report Server.
The distributor Database is on Report Server bcos I don't to slow down
the performance of OLTP Server.
But every time the replication is running, the error (see attachement)
always occur on OLTP Server.
Pls somebody give my some suggestion.
Thanks
Robert LieRobert,
Are there triggers on the tables being replicated? If so, do the triggers
need to be executed again on the subscriber? Not sure if this is the cause
of your error but it might be related.
HTH
Jerry
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:u2VvQxMxFHA.736@.tk2msftngp13.phx.gbl...
> Dear All,
> I have 2 DB Server, OLTP Server and Report Server.
> I want to replicate some tables from OLTP Server to Report Server.
> The distributor Database is on Report Server bcos I don't to slow down
> the performance of OLTP Server.
> But every time the replication is running, the error (see attachement)
> always occur on OLTP Server.
> Pls somebody give my some suggestion.
> Thanks
> Robert Lie
>
----
--|||Since I need to use IE to use these forums, I cant see the attached error
file. Please post it.
--
TIA,
ChrisR
"Robert Lie" wrote:

> Dear All,
> I have 2 DB Server, OLTP Server and Report Server.
> I want to replicate some tables from OLTP Server to Report Server.
> The distributor Database is on Report Server bcos I don't to slow down
> the performance of OLTP Server.
> But every time the replication is running, the error (see attachement)
> always occur on OLTP Server.
> Pls somebody give my some suggestion.
> Thanks
> Robert Lie
>|||Here's the error:
Run-time error '-2147217900 (80040e14)'
Maximum soted procedure, function, trigger, or view nesting level
exceeded (limit32)
ChrisR wrote:
> Since I need to use IE to use these forums, I cant see the attached error
> file. Please post it.|||Hai Jerry,
You're right, some tables being replicated have triggers on them.
The problem is I need the triggers to be executed on OTLP server since
the triggers are needed to maintain data integrity of applications that
run on OLTP server.
Do you have alternatives solution on top of that?
Thanks
Robert Lie
Jerry Spivey wrote:
> Robert,
> Are there triggers on the tables being replicated? If so, do the triggers
> need to be executed again on the subscriber? Not sure if this is the caus
e
> of your error but it might be related.
> HTH
> Jerry
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:u2VvQxMxFHA.736@.tk2msftngp13.phx.gbl...
>
>
> ----
--
>
>
>|||True but are replicating the triggers as well or creating them on the
subscriber(s)? Something to look into.
HTH
Jerry
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:eWomXVXxFHA.2620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hai Jerry,
> You're right, some tables being replicated have triggers on them.
> The problem is I need the triggers to be executed on OTLP server since the
> triggers are needed to maintain data integrity of applications that run on
> OLTP server.
> Do you have alternatives solution on top of that?
> Thanks
> Robert Lie
> Jerry Spivey wrote:|||Do you mean I'm replicating the triggers to subcriber server (Report
Server) as well or not?
No, I'm not replicating the triggers to subcriber.
Which part do you worry about?
Thanks
Robert Lie
Jerry Spivey wrote:
> True but are replicating the triggers as well or creating them on the
> subscriber(s)? Something to look into.
> HTH
> Jerry
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:eWomXVXxFHA.2620@.TK2MSFTNGP09.phx.gbl...
>
>|||Robert,
I was looking at the possible cause of your issue being related to the fact
that there might be triggers on the subscriber tables that could possibly be
causing this issue. Are there triggers on the subscribing tables
(sp_helptrigger tablename at subscriber)? Are they required? As a test
what if you removed them (one at a time with a retest)? Agan..I am not
saying this is definitely the cause but it might be.
HTH
Jerry
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:OZjVHYYxFHA.2620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Do you mean I'm replicating the triggers to subcriber server (Report
> Server) as well or not?
> No, I'm not replicating the triggers to subcriber.
> Which part do you worry about?
> Thanks
> Robert Lie
>
> Jerry Spivey wrote:

Need Help (replication)

Dear All,
I have 2 DB Server, OLTP Server and Report Server.
I want to replicate some tables from OLTP Server to Report Server.
The distributor Database is on Report Server bcos I don't to slow down
the performance of OLTP Server.
But every time the replication is running, the error (see attachement)
always occur on OLTP Server.
Pls somebody give my some suggestion.
Thanks
Robert Lie
Robert,
Are there triggers on the tables being replicated? If so, do the triggers
need to be executed again on the subscriber? Not sure if this is the cause
of your error but it might be related.
HTH
Jerry
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:u2VvQxMxFHA.736@.tk2msftngp13.phx.gbl...
> Dear All,
> I have 2 DB Server, OLTP Server and Report Server.
> I want to replicate some tables from OLTP Server to Report Server.
> The distributor Database is on Report Server bcos I don't to slow down
> the performance of OLTP Server.
> But every time the replication is running, the error (see attachement)
> always occur on OLTP Server.
> Pls somebody give my some suggestion.
> Thanks
> Robert Lie
>

|||Since I need to use IE to use these forums, I cant see the attached error
file. Please post it.
TIA,
ChrisR
"Robert Lie" wrote:

> Dear All,
> I have 2 DB Server, OLTP Server and Report Server.
> I want to replicate some tables from OLTP Server to Report Server.
> The distributor Database is on Report Server bcos I don't to slow down
> the performance of OLTP Server.
> But every time the replication is running, the error (see attachement)
> always occur on OLTP Server.
> Pls somebody give my some suggestion.
> Thanks
> Robert Lie
>
|||Here's the error:
Run-time error '-2147217900 (80040e14)'
Maximum soted procedure, function, trigger, or view nesting level
exceeded (limit32)
ChrisR wrote:
> Since I need to use IE to use these forums, I cant see the attached error
> file. Please post it.
|||Hai Jerry,
You're right, some tables being replicated have triggers on them.
The problem is I need the triggers to be executed on OTLP server since
the triggers are needed to maintain data integrity of applications that
run on OLTP server.
Do you have alternatives solution on top of that?
Thanks
Robert Lie
Jerry Spivey wrote:
> Robert,
> Are there triggers on the tables being replicated? If so, do the triggers
> need to be executed again on the subscriber? Not sure if this is the cause
> of your error but it might be related.
> HTH
> Jerry
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:u2VvQxMxFHA.736@.tk2msftngp13.phx.gbl...
>
>
> ----
>
>
>
|||True but are replicating the triggers as well or creating them on the
subscriber(s)? Something to look into.
HTH
Jerry
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:eWomXVXxFHA.2620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hai Jerry,
> You're right, some tables being replicated have triggers on them.
> The problem is I need the triggers to be executed on OTLP server since the
> triggers are needed to maintain data integrity of applications that run on
> OLTP server.
> Do you have alternatives solution on top of that?
> Thanks
> Robert Lie
> Jerry Spivey wrote:
|||Do you mean I'm replicating the triggers to subcriber server (Report
Server) as well or not?
No, I'm not replicating the triggers to subcriber.
Which part do you worry about?
Thanks
Robert Lie
Jerry Spivey wrote:
> True but are replicating the triggers as well or creating them on the
> subscriber(s)? Something to look into.
> HTH
> Jerry
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:eWomXVXxFHA.2620@.TK2MSFTNGP09.phx.gbl...
>
>
|||Robert,
I was looking at the possible cause of your issue being related to the fact
that there might be triggers on the subscriber tables that could possibly be
causing this issue. Are there triggers on the subscribing tables
(sp_helptrigger tablename at subscriber)? Are they required? As a test
what if you removed them (one at a time with a retest)? Agan..I am not
saying this is definitely the cause but it might be.
HTH
Jerry
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:OZjVHYYxFHA.2620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Do you mean I'm replicating the triggers to subcriber server (Report
> Server) as well or not?
> No, I'm not replicating the triggers to subcriber.
> Which part do you worry about?
> Thanks
> Robert Lie
>
> Jerry Spivey wrote:

Need help

Hi,
We have publisher and remote distributor with lot of transactional and
merge publications. (SQL2K SP3)
Recently we migrated our publisher to a new machine.
Today I renamed my old publisher server and tried to disable subscribers on
it (because everything works on a new publisher already).
When I did that via EM, all my replication agents on a distributor failed
(probably the old publisher wrote somethig to the distributor).
Then I marked (enabled) all subscribers back on that server (old publisher).
After that all merge replications became good again. Buh the transactional
ones don't work.
I get an error "The remote procedure call failed and did not execute"
Need help.
Thanks
Hi,
How did you rename the old publihser from EM? I'm not familiar with that capability.
-Matt
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Need for Distributor server in existing Replication Setup

We have a setup with one Publisher Server and 15 Subscribers with merge replication configured for all subscribers. The current Database size is approximately 3 GB expected to grow to 10 GB in next 1 year. We wanted to know what benefits would we incur if we add an additional seperate distributor server (hardware box). Also, what is the approx. size of a database where a seperate distributor server (hardware box) is recommended.

Thanks in advanceRead up on replication in SQL Books Online (provided with SQL Server). It covers a lot of the trade-offs in detail. Without knowing a LOT more about your configuration, plans, etc. I don't know how to give you a simple answer.

Typically, when you have one publisher with only 15 subscribers, the load isn't too heavy unless the publisher is stressed. The primary advantage comes from improved security, better network performance, and better managability. As your system grows (more data and more subscribers), then the performance benefits will start to come into play.

Again, without a lot more understanding of what you are doing now and what you plan to do in the near future, I really can't give you a straightforward answer.

-PatP|||Sometimes you make me crack up, I swear. ..."only 15 subscribers"... You'll see an immediate benefit even if you have just 1 subscriber...man, how many of those have you set up?|||Nice to know that you find me good for something!

I've set up about six production replication systems, a few hundred training systems, and I'm not really sure how many test systems. As I said in my first post, without knowing a lot more about what the poster wants and what they've actually got, any observation I can offer is only in very general terms.

Based on my experience, if you set up at least one NIC for replication on a relatively beefy box, it can handle a reasonable user load along with the replication... If you try to skimp on bandwidth, RAM, or processing power, you can certainly swamp any box.

It seems obvious to me that having a separate distributor is better than having one box do both, so on that point we certainly agree. I guess the question is where do you see the performance "knee" due to the additional load imposed by distribution. Without knowing a lot more, I don't see how you can offer much insight there.

-PatP|||Hey, I just felt that having 15 subscribers is enough to predict that a dedicated distributor is well due, that's all ;)

Need Feedback on Trans. Replication w/ Remote Distributor

Greetings:
I have been asked to set up replication between two SQL servers on our
network. Though I am primarily a network security engineer, and would
consider myself just above a novice SQL Admin, replication is definitely new
territory for me, and I was hoping I could get some help and feedback on
what I think I am trying to accomplish...
The scenario: We have a SQL server that currently serves as the backend
database for a web-based application. It consists of several databases (on
one instance) with no clustering nor data redudancy other than the RAID
structures currently housing the data. A second server has been purchased
with much more processing power, that they wish to use as a failover device,
but do not wish to make it the PRODUCTION box until/unless the current box
fails. They just want a copy of the data to be duplicated to another device
as close to real-time as possible (and without clustering).
So, because of the power of the new box compared to the old one, I had
decided based on my initial research, to set up Transactional Replication
between the two boxes, and defining the new server (Server B) as the Remote
Distributor and Subscriber with the original production server (Server A) as
only a Publisher. I also intended/hoped to use pull subscriptions. The
intent of all these decisions being to absolutely minimize the additional
overhead on the original production box. What I have not been able to find
is any documentation on how to configure an alternate snapshot location when
using a remote distributor that is the sole subscriber. Can this be done?
The reason I need to use alternate snapshots (I believe) is because when I
do implement this on the production servers, they exist in a DMZ zone that
not only has no domain, but also does not have any NetBIOS nor windows mgmt
protocols enabled (except Terminal Services for Remote Admin). There is NO
Windows SMB file sharing, so I need snapshots to be distributed to the
subscriber via FTP ... but as I said, the subscriber IS the distributor.
Can this be done? Did I inadvertently make my first replication project too
complex? Did I overlook something as to how it can be done? Basically I am
stuck at the point where I have definied my first susbscription, did NOT
create an initial snapshot yet, and am trying to figure out how to configure
the 'Snapshot Location' properties on the Publisher so that it will deploy
the snapshot to a location that the Distributor/Subscriber can access via
FTP, BEFORE running the snapshot agent for the first time.
And yes, this is all on a test environment using Virtual PC's at the moment.
My apologies for the length of this message, but I know how much it helps to
have as much detail up front as possible. And Thank you in advance for any
feedback or recommendations.
Keith C. Jakobs, MCP
[FYI... Exec mgmt has INSISTED that all System files, program files, SQL
data and transaction logs are configured on the SAME RAID-5 partition on 4
physical disks with a 5th hot spare drive... I've tried to convince them to
allocate dedicated log drives and pull swap files off the RAID-5, but they
are not interested in deploying multiple physical disk structures on a
single server]
First, don't apologize for the post length. I strongly prefer a detailed
post with relevant information to a "my server don't work, help, TIA" post.
Second, Transactional replication will give you a read-only copy of the
data, but not of the complete database schema. All objects don't get
replicated and stuff like Identity columns, referential integrity, unique
constraints, views, and stored procedures just won't work the way you think
it will. The short answer is that without a LOT of extra work, you can't
use a subscriber in place of a publisher as a DR strategy.
If you don't use any file copy protocols other than FTP, how do you get
backups off of the host computer? There is such a thing as too much
security.
I would use Log Shipping to handle the creating and maintaining a warm
standby server. There are some scripts in the SQL 2000 Resource Kit as well
as various web sites that you can adapt for your own use.
I realize this isn't answering the question you asked, but it may be the
answer you really need.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
news:OIcAYyM2FHA.4008@.tk2msftngp13.phx.gbl...
> Greetings:
> I have been asked to set up replication between two SQL servers on our
> network. Though I am primarily a network security engineer, and would
> consider myself just above a novice SQL Admin, replication is definitely
> new
> territory for me, and I was hoping I could get some help and feedback on
> what I think I am trying to accomplish...
> The scenario: We have a SQL server that currently serves as the backend
> database for a web-based application. It consists of several databases
> (on
> one instance) with no clustering nor data redudancy other than the RAID
> structures currently housing the data. A second server has been purchased
> with much more processing power, that they wish to use as a failover
> device,
> but do not wish to make it the PRODUCTION box until/unless the current box
> fails. They just want a copy of the data to be duplicated to another
> device
> as close to real-time as possible (and without clustering).
> So, because of the power of the new box compared to the old one, I had
> decided based on my initial research, to set up Transactional Replication
> between the two boxes, and defining the new server (Server B) as the
> Remote
> Distributor and Subscriber with the original production server (Server A)
> as
> only a Publisher. I also intended/hoped to use pull subscriptions. The
> intent of all these decisions being to absolutely minimize the additional
> overhead on the original production box. What I have not been able to
> find
> is any documentation on how to configure an alternate snapshot location
> when
> using a remote distributor that is the sole subscriber. Can this be done?
> The reason I need to use alternate snapshots (I believe) is because when I
> do implement this on the production servers, they exist in a DMZ zone that
> not only has no domain, but also does not have any NetBIOS nor windows
> mgmt
> protocols enabled (except Terminal Services for Remote Admin). There is
> NO
> Windows SMB file sharing, so I need snapshots to be distributed to the
> subscriber via FTP ... but as I said, the subscriber IS the distributor.
> Can this be done? Did I inadvertently make my first replication project
> too
> complex? Did I overlook something as to how it can be done? Basically I
> am
> stuck at the point where I have definied my first susbscription, did NOT
> create an initial snapshot yet, and am trying to figure out how to
> configure
> the 'Snapshot Location' properties on the Publisher so that it will deploy
> the snapshot to a location that the Distributor/Subscriber can access via
> FTP, BEFORE running the snapshot agent for the first time.
> And yes, this is all on a test environment using Virtual PC's at the
> moment.
> My apologies for the length of this message, but I know how much it helps
> to
> have as much detail up front as possible. And Thank you in advance for
> any
> feedback or recommendations.
> Keith C. Jakobs, MCP
>
> [FYI... Exec mgmt has INSISTED that all System files, program files, SQL
> data and transaction logs are configured on the SAME RAID-5 partition on 4
> physical disks with a 5th hot spare drive... I've tried to convince them
> to
> allocate dedicated log drives and pull swap files off the RAID-5, but they
> are not interested in deploying multiple physical disk structures on a
> single server]
>
|||Hi Geoff,
Thank you so much... yes, this is probably exactly the kind of information
I was in need of. I knew I was missing something about all of this
replication business!!!
I will start reading into Log Shipping, hoping the BOL will give me a good
foundation. I have only vaguely heard the term before, so I will need to
get myself up to speed in that arena. I'll save my questions until I have
at least done my own preliminary studying. But I do need to ask if that can
be done over FTP protocols?
As for backups, we use Veritas NetBackup and have uniquely specified ports
for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt has
also decided that all backups go through our 100Mbit firewall... that's
why they want a standby DB in the DMZ. I would rather a dedicated mgmt
network on secondary NICs, but I dont get to make the final say.... go
figure. ;-)
However, nowadays, I'm beginning to wonder if there is such a thing as too
much security... I've been finding the tighter the better - you know...
single function and hard-coded applications are making more and more sense.
Plus they keep passing more and more laws that you may be better off erring
on the side of excess. Just my 2 cents on the security end.
Thanks again Geoff! Your feedback was very much appropriate and
appreciated!!!
Keith C. Jakobs, MCP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> First, don't apologize for the post length. I strongly prefer a detailed
> post with relevant information to a "my server don't work, help, TIA"
post.
> Second, Transactional replication will give you a read-only copy of the
> data, but not of the complete database schema. All objects don't get
> replicated and stuff like Identity columns, referential integrity, unique
> constraints, views, and stored procedures just won't work the way you
think
> it will. The short answer is that without a LOT of extra work, you can't
> use a subscriber in place of a publisher as a DR strategy.
> If you don't use any file copy protocols other than FTP, how do you get
> backups off of the host computer? There is such a thing as too much
> security.
> I would use Log Shipping to handle the creating and maintaining a warm
> standby server. There are some scripts in the SQL 2000 Resource Kit as
well[vbcol=seagreen]
> as various web sites that you can adapt for your own use.
> I realize this isn't answering the question you asked, but it may be the
> answer you really need.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
> news:OIcAYyM2FHA.4008@.tk2msftngp13.phx.gbl...
purchased[vbcol=seagreen]
box[vbcol=seagreen]
Replication[vbcol=seagreen]
A)[vbcol=seagreen]
additional[vbcol=seagreen]
done?[vbcol=seagreen]
I[vbcol=seagreen]
that[vbcol=seagreen]
distributor.[vbcol=seagreen]
I[vbcol=seagreen]
deploy[vbcol=seagreen]
via[vbcol=seagreen]
helps[vbcol=seagreen]
4[vbcol=seagreen]
they
>
|||Built-in log shipping works over SMB protocols. I suppose you could build
something over HTTP/FTP protocols for the file transfers, but I wouldn't
want to write it. I do recommend a second, closed SQL management/backup
network but as you said, we don't always get the final say on such
decisions. BOL describes the built-in stuff for Log Shipping in Enterprise
Edition. The SQL 2000 resource kit has some more information on roll your
own log shipping. You can always Google the term and get many results from
various web sites.
I agree that tighter security is a good thing, but arbitrarily saying that
all communications have to go through X is not security, it is
simplification for network managers.
If you are migrating to SQL 2005, there is a new type of replication called
Peer-to-Peer that may meet your needs. Here is teh latest version of
SQL2005 BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
news:uXZNXRY2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
> Thank you so much... yes, this is probably exactly the kind of
> information
> I was in need of. I knew I was missing something about all of this
> replication business!!!
> I will start reading into Log Shipping, hoping the BOL will give me a good
> foundation. I have only vaguely heard the term before, so I will need to
> get myself up to speed in that arena. I'll save my questions until I have
> at least done my own preliminary studying. But I do need to ask if that
> can
> be done over FTP protocols?
> As for backups, we use Veritas NetBackup and have uniquely specified ports
> for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt
> has
> also decided that all backups go through our 100Mbit firewall...
> that's
> why they want a standby DB in the DMZ. I would rather a dedicated mgmt
> network on secondary NICs, but I dont get to make the final say.... go
> figure. ;-)
> However, nowadays, I'm beginning to wonder if there is such a thing as too
> much security... I've been finding the tighter the better - you know...
> single function and hard-coded applications are making more and more
> sense.
> Plus they keep passing more and more laws that you may be better off
> erring
> on the side of excess. Just my 2 cents on the security end.
> Thanks again Geoff! Your feedback was very much appropriate and
> appreciated!!!
> Keith C. Jakobs, MCP
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> post.
> think
> well
> purchased
> box
> Replication
> A)
> additional
> done?
> I
> that
> distributor.
> I
> deploy
> via
> helps
> 4
> they
>
|||Here is the link.
http://www.microsoft.com/downloads/d...displaylang=en
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
news:uXZNXRY2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
> Thank you so much... yes, this is probably exactly the kind of
> information
> I was in need of. I knew I was missing something about all of this
> replication business!!!
> I will start reading into Log Shipping, hoping the BOL will give me a good
> foundation. I have only vaguely heard the term before, so I will need to
> get myself up to speed in that arena. I'll save my questions until I have
> at least done my own preliminary studying. But I do need to ask if that
> can
> be done over FTP protocols?
> As for backups, we use Veritas NetBackup and have uniquely specified ports
> for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt
> has
> also decided that all backups go through our 100Mbit firewall...
> that's
> why they want a standby DB in the DMZ. I would rather a dedicated mgmt
> network on secondary NICs, but I dont get to make the final say.... go
> figure. ;-)
> However, nowadays, I'm beginning to wonder if there is such a thing as too
> much security... I've been finding the tighter the better - you know...
> single function and hard-coded applications are making more and more
> sense.
> Plus they keep passing more and more laws that you may be better off
> erring
> on the side of excess. Just my 2 cents on the security end.
> Thanks again Geoff! Your feedback was very much appropriate and
> appreciated!!!
> Keith C. Jakobs, MCP
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> post.
> think
> well
> purchased
> box
> Replication
> A)
> additional
> done?
> I
> that
> distributor.
> I
> deploy
> via
> helps
> 4
> they
>

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
>