Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts

Monday, March 26, 2012

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 ;)

Friday, March 23, 2012

need direction on SSB replacing existing technology

Hello, I have an existing suite of applications in my company where remote laptop users enter data into a local database instance. There are several different applications with several different databases (some users run application A and C, others run A and B and D, etc). All of this data needs to make its way into both their local databases, and to the front office database. In addition, some of the data goes out of the front office to other end users (supervisors get data from people under them).

Currently, we get the data back and forth by creating text files full of SQL statements that are run on either the end user servers or the main server, and we zip and ftp these SQL Script files back and forth.

I am looking into the benefits of replacing this process with a Service Broker application, but I'm still too much of a newbie to understand how this might work. What should my messages contain? Should they contain the same SQL statements that are currently in the script files, and then a SSB process would grab the SQL and run it? Or should the message types be more specific to each application?

any pointers to articles that go beyond "here's what SSB is" would be appreciated.

matt tagliaferri

Why aren't you considering SQL Server Replication?|||I've used replication for other tasks - I've found it to be a bit difficult to manage and troubleshoot. Ansynchonous messaging seems to most closely match our existing method of sending the data around (scripts via ftp).

matt tag
|||

Implementing replication on top of Service Broker will require a lot of effort in terms of building the plumbing to read/write data, marshalling/unmarshalling, managing schemas, monitoring, configuration, etc. I'm not sure if it actually helps reduce the problem.

Service Broker is useful when you truely want to build loosely-coupled apps. That is apps that are not bound w.r.t. availability, time, schemas, administrative control, identities, scalability, etc. But it is more general purpose than replication and therefore requires more effort.