transactional replication of many databases on a single sql instance

Question

transactional replication of many databases on a single sql instance

yxiong2

Valued Member

Points: 72
More actions
April 26, 2007 at 7:47 am

#98281

Folks,
I get a request of replicating (transactional) up to 200 live databases from a single sql instance (sql 2005 standard version) to a reporting server (another sql 2005 standard version). The size of each database is between 10MB to 2 GB.
Do you know it's possible to replicate that many databases on a single instance? How many databases can be replicated for one sql instance and how's the performance? Just using SQL Server replication to set them up or is there any 3rd party tool to do it?
Thanks for your help!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jason- SSCrazy Points: 2795 More actions · Answer 1

I'm not aware of any limit on the number of suscribers that can be added to an instance or publication. As for performance, there will be issues regarding performance with so many servers to send data to, but is more dependant upon the number of transactions than the size save the snapshot.

There are two things I would reccomend trying. First instead of one publication with multiple subscriptions, you can build multiple publications with fewer subscribers for each publication. This solves a performance issue with one agent doing so much.

The other option which can get confusing and hard to troubleshoot but disperses the workload, is to set up a replication chain. Let's say you have central server (CENTRAL001) and subscriber servers (SUBSCRIBERXXX where XXX is 001-200), build a publication on CENTRAL001 with subscibers SUBSCRIBER001 - SUBSCRIBER020, then build a publication on each of the first 20 subscribers that the other subscribers subscribe...

Hope this helps.

I'm interested to see what others come up with, I've used both of the above and each has it's share of ups and downs.

-

yxiong2 Valued Member Points: 72 More actions · Answer 2

Thanks Jason for your reply!

Replicate 200 DBs from one sql instance means I have to create 200 publications on this server,then create 200 subscriber from the reporting server. Even using separate multiple distributors, I think it may be very difficult to do it. The worker threads may be the problem due to too many agents has to be set up. The worker threads limit is 100 in SQL Server Agent for distribution jobs. Also there may be deadlock issue too:

http://support.microsoft.com/?kbid=246330

Just wondering if anyone has the experience of setting up many publications (such as 200) on one sql instance and replication is still working just fine.

Regards!

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Sorry I may be confused. However you are saying you need to replicate 200 dbs down to a single db on your reporting server right?

You can setup a single distributor but you will still need to setup the publication on each server. As well if you have tables using an identity column you may need to be careful because records can get hard to deal with if there are related tables you need to deal with in such that mutiples will cause you to be unable to resolve the related records. Most times you need to add a column to id the server and it will need to be added to the end of each table being replicated so you can identify relationships.

You might want to create denormalized views and replicate those to your reporting server like a datamart. That is in essence the very thing you want to do so research on Datamarts.