SQL 2005 Replication Performance: More Than 3000 Subscribers
Jun 7, 2007
Hi All,
We are developing a system which will have to support more than 3000
subscribers. We will have to support both Transactional replication
and Merge replication.
I checked the following document about SQL 2005 replication <http://
www.microsoft.com/technet/prodtechnol/sql/2005/mergrepl.mspx>. The
document does not clearly specify what is the maximum number of
subscribers supported without a significant performance degradation.
The questions i have are:
1. Given the fact that there will be more than 3000 subscribers, there
will be more than 500-1000 subscribers trying to replicate at the same
time. Will be there be a performance degradtion in such a scenario
2. Has anyone used SQL Server 2005 in a scenario involving more than
3000 subscribers?
3. Will it be better if we develop our own system to perform
replication activity instead of relying on SQL Server 2005?
- Ngm
Mail me atnarasimha (DOT) gm (AT) gmail (DOT) com )
View 6 Replies
ADVERTISEMENT
Oct 24, 2007
I posted a more detailed technical question a couple of weeks ago
with Subject = "Compact Edition 2005 Subscribers - Merge Agent failed after detecting that retention-based cleanup has deleted metadata"
But I got no replies.
Now my question is simpler: Is SQL Server 2005 Merge Replication reliable with Compact Edition subscribers on WindowsXP tablets?
We deleted all users' local databases a couple of weeks ago, and republished two publications with retention = 60 days.
Now we have at least one user who can't sync with the server, and replication monitor shows the error below.
But if she e-mails her sdf file to a support person, they can successfully sync her database.
We're sure that it's not a permissions issue, which is a normal suspicion when one user can perform a task that another can not.
It doesn't seem like a retention problem, because she's well inside the retention period.
She can successfully sync with the publication with upload/download tablers, but not with the publication with download only tables. Both publications were created on the same day, by the same DBA, with nearly identical properties.
This problem user has operated in the past with no problem, and she's configured identically to all of the others. It just seems like random flakyness with merge replication for Compact Edition subscribers.
I feel stupid saying that, because it sounds like I believe in a magical ghost in the machine. So my question is - IS MERGE REPLICATION A LITTLE BIT FLAKY FOR COMPACT EDITION SUBSCRIBERS ON XP MACHINES?
We've applied SP2 to the server, but no subsequent HotFixes.
We're trying to sell users and management on developing a larger occasionally connected system with the same replication scheme, so we need to know if it deserves our confidence.
Error messages:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
Get help: http://help/MSSQL_REPL-2147199402
View 2 Replies
View Related
Nov 9, 2015
Is it possible to replicate data from one publishers to a multiple subscribers in transaction replication? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 10 articles from A to B but only 1 from A to C. In other words I want to use the same publisher but select one article from that publisher instead of all 10 to Server C/database C.
If it is possible what will be the drawbacks of such implementation? Will performance be a hit?
View 5 Replies
View Related
Jan 25, 2007
Hi,
I am Using Transactional Replication with Updatable Subscriptions. Is there any limitations in the number of subscribers (Servers) used for this type of replication?
I have a scenario of configuring this replication in 60 Subscribers(Servers). And the replication should be in the Continuous running mode. Will Transactional Replication with Updatable Subscriptions work in this scenario??? Or is it meant to work for less than 10 subscribers?
Please reply asap.
Thanks in advance.
Regards,
Swapna.B.
View 3 Replies
View Related
Jan 8, 2004
When I try to open the subscribers tab in the publisher and distributor properties in SQL Enterprise Manager I get the following error:
SQL Enterprise Manager could not retrieve the list of registered subscribers.
Error 8152: String or binary data will be truncated
Anyone who has experienced similar problems or has a solution?
Best Regards
MF
View 1 Replies
View Related
Sep 21, 2006
Hi:
Is there a limit on the number of subscribers for Transactional replication?. My question is how many maximum number of subscribers can we have in transactional replication?. How do I retrieve this information throug T-SQL (any DMVs or system tables?). Please let me know.
I am looking to know for both SQL 2005 as well as SQL 2000 and also if anyone can tell me if anything has changed in SQL 2005 for transactional replication that would be great.
Can anyone please let me know?.
Thank you
AK
View 3 Replies
View Related
May 13, 2006
Hi ,
I am trying to create Replication Topology (Merge Replication) like below.
Subscriber1 --> Publisher <-- Subscriber2.
I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.
Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.
Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.
I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.
Can anyone help ?
Thanks in advance
View 4 Replies
View Related
Dec 21, 2005
Hi,
I read the BOL on how the publisher will had out identity ranges to subscribers, but it was not clear if this was also the case for anonymous subscribers. Will merge replication with identity columns work with anonymous subscribers that sync via HTTPS?
Thanks,
Darrell Young
View 1 Replies
View Related
Oct 22, 2015
Replicate ddl setting is not working if multiple subscribers are used...
View 2 Replies
View Related
Jan 4, 2007
I need to merge replicate data to two different types of subscribers:
Clients subscribers which will have a very small percentage of the data from the central database. The data on these machines will be managed using dynamic filtering on host_name()
Server subscribers which will manage a copy of all the data from the central database
There will be far fewer server subscribers than client subscribers.
As I see it I have two options for the configuration
1) Use two separate merge publications €“ one which is filtered and one which isn€™t
2) Use a single merge publication and setup the filtering so that the server subscribers receive all the rows
Which option is likely to lead to better performance?
With option 1) there would be 2 complete sets of replication metadata which need to be maintained €“ so I am tending towards option 2. Are there any disadvantages in using a dynamic filter to return a very large number of rows?
View 6 Replies
View Related
Aug 21, 2007
Sql Server 2000
I am looking for bidirectional transactional replication using updatable subscribers (queued or immediate) . Is it possible to replicate the image data from the updatable subscribers to the publisher. I understood that the Image data can't be replicated to the publisher from the updatable subscriber. I am not using the WRITETEXT or UPDATETEXT. I am using just INSERT and UPDATE for image data type transactions.
Any thoughts on this is greatly appreciated.
Thanks in advance
View 3 Replies
View Related
Sep 27, 2007
I have just upgraded from 2000 to 2005 and my transactional replication is running very slow, I already have a latency of 10 mins and its getting worse. I'm just using the default agent profile, is there anything I need to change?
Help please.
View 19 Replies
View Related
Feb 25, 2008
Wondering if anyone has any experience with SQL Server Express Edition (SSEXP). We're looking at a mobile sales force type model, so a local database on a laptop with no real time network connection. So the users would collect data locally, then connect up to the network every few days to replicate the data to a central server.
So questions.. Has anyone tried anything similar? How stable/mature is SSEXP? Any other thoughts, alternatives or gotchas anyone can think of?
Thanks for the input.
View 1 Replies
View Related
Sep 21, 2007
Hello,
We previously having two servers A and B. Server A is used for updation of data and the data then replicated to server B. Server B is used for
Server A :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2000 SP 2
Server Z :
purpose : used for Reporting
SQL Server version : SQL Server 2000 SP 2
We were doing Transactional replication from Server A to Server B.
Last month we have broght another server (Server B) with same hardware configuration but having SQL SERVER 2005 installed. This is to speed up our database update process. We have moved some of the database on this new server so that we can achieve our deadlines.
Server B :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2005
I have set up the transactional replication from Server B to Server Z and replication works fine.
However, the issue is after it is started replicating from this new server (Server B) performance of all the queries reduced a lot.(making my life harder)
I didnt expected this as our reporting server is still SQL server 2000.
I have restored the backup of database which was replicated from server A (sql server 2000) and compared execution plan for one of our common query (which is used in most of the reports and which is now taking longer time to provide results)
I found that database which is replicated from Server B (Sql server 2005) is having primary keys. which was not present in the database which replicated from server A(Sql server 2000).
I have then removed the primary key and make the indexes same as previous copy of database(which was replicated from server A) But still the query takes long time.
Execution plan now shows "Table Spool" which was not present in previous copy of database.
Almost every query for this database is taking longer time now.
Can someone suggest me what is wrong and what should I need to fix.
Am I going on the right direction?
View 14 Replies
View Related
Nov 18, 2005
I implemented a merge replication publication on a SQL Server 2005
View 6 Replies
View Related
Jun 18, 2007
As I am begininng to believe that is the only edition I am going to be able to run on my Vista Utlimate os.
Standard does not install server components.
I believe SQL Express was working as installed with Visual Studio 2005, but I need the use the tools that SQL Express does not contain.
Thanks,
_Greg
View 3 Replies
View Related
Apr 12, 2008
I am writing a sql table, which is used to record a webpage's clicked number at different times.
The table structure is sth like:
click_number_1, time_1, click_number_2, time_2, ... ..., click_number_3000, time_3000
Cos there are too many columns, I feel it's impossible to use CREATE TABLE to add them by hand. I wonder if sql has statements like "for" statement in c++ to add those columns in a looping way.
Thanks!
View 9 Replies
View Related
Jul 6, 2006
Hi there,
I am
currently planning an ASP.NET and MS SQL project to distribute customer codes
to my customers. All the codes will be stored in the database, and then when a
customer registers, the next available code is linked to their account. Does
this sound like a feasible solution? The number of codes (ie. rows) could be
anywhere from 3,000 to 100,000 – but will only contain a couple of fields.
Also, it is
a separate company that is producing the codes (we’re offering them as an
incentive) and I was wondering the best way for them to get the codes to me, so
I can easily import them into MS SQL? CSV / Excel ??
This is the
main problem I need help with.
Thank you
all for your continued support, and all the work you kind guys do here!
Nathair
View 1 Replies
View Related
May 2, 2006
I can't seem to get SQL Server to run on my 64 bit box running Windows XP Pro 64-bit. Is there a fix for this? I really need SQL Server.
View 5 Replies
View Related
Apr 25, 2008
im sure there must be function for this but i cantfind it anywhere
essentially i want to do a Large insert on a table around 3000 records
which links to a table using 3000+ non sequential IDS
the content will be the same but bar the ids
roughly along the lines of
Code Snippet
insert into comment
values
('Some comment',date,commentypeid, non sequential List of IDs)
i could do this fairly simply in c# using a List<int>
Code Snippet
foreach (int ID in IDS)
{
insert into comment
values
('Some comment',datetime.now,1234, ID)
}
surely there is a a function
Thanks in advance
Jake
View 2 Replies
View Related
Nov 9, 2005
I have to copy a large (3000) amount of different tables from a Oracle machine into an
SQLServer machine.
I am able to do this using a (VB) script.
I use now several methods:
1) INSERT INTO TABLE1 SELECT * FROM SID1..DB.TABLE1 (SID1 is a linked server)
2) INSERT INTO TABLE1 SELECT * FROM OPENQUERY(SID1,'SELECT * FROM DB.TABLE1')
3) Also used OPENROWSET method (similar to 2)
For small tables this is fine, however for BIG tables (15M Rows/150Cols) the methods above are too slow.
If I compare the same copy action with a simple DTS, the DTS is 3 times faster.
Also, the DTS seems to bulk copy the data directly into the desired database while the
mentioned methods first fill the tempdb, then the transaction log of the desired database and
then finally the desired table (need very much extra space on your filesystem).
The total size of data is about 300GB.
Can anyone supply me with a simple example how to copy data from an Oracle table into a
SQLServer table in script (or SQL) that is as fast as the DTS and not filling my logfiles??
I read the bcp (which I use for import/export files) and bulk insert commands, but
I do not understand how to use them in this question.
View 14 Replies
View Related
Jun 25, 2006
I'm having a tough time finding any good resources on Sql Server Replication Performance. Are there any benchmarks / state of any kind? How well does replication scale out?
In my scenario, I have one central publisher and several large tables, all with hundreds of millions of records. Every day I may insert/update millions of records in the publisher, and then I need to replicate the changes (in a few hours at most) onto a pool of subscribers, while they remain online.
Is the replication story robust enough to handle a situation like this?
View 1 Replies
View Related
Oct 26, 1999
Can anyone suggest hwo can I improve the performance of the replication process and make it faster.
Pran
View 1 Replies
View Related
Mar 9, 2004
Hi all,
I am keen to hear peoples perspectives on how much additional load Transactional Replication will have on a server.
Obviously this will depend greatly on the level of transactions in the database, but a general indication would be great (eg 10% increase in overheads).
I am thinking of encorperating this into a new server structure which we are going to be setting up and am unsure as to whether to make the primary server BOTH the publisher and distributor; or make the secondary server the distributor to reduce the load on the primary to only being the publisher.
Basically the secondary server will simply be a 'hot swap' of the primary - so i/o load on the secondary is not going to be an issue.
There may be 2 primary's (if that makes sense) replicating to the hot swap so that if either primary is dropped the hot swap could take over either servers load/responsibilities - not sure if this will make a difference on where to put the roles?
Anyone have any thoughts on this?
Thanks in advance.
Cheers
View 6 Replies
View Related
Aug 24, 2006
Hello,could you please advice on how to measure replication performancein Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?I've got two servers with databases installed and configured,I prepared set of data using DBGEN from TPC and I already imported theminto databases.Also, I configured the replication.Now I have to do a test with a few kind of replications methodimplemented in these RDMBS, but I don't know which tool or reports or"v$iews" should I use to measure replication performance.The replication is configured only between the same RDBMS, I meanOracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.Most of applications are great for checking performance of local DB,not for replicated/distributed.I've found description of CA Unicenter Database Performance Managementfor distributed RDMBS, and I think it could be the right one, but Ican't find any demo or trial version :(Could you please advice any place to download it, or other application,script, description, just whatever.Perhaps just any other idea how to check the replication mechanismefficiency ?Regards,Mark
View 5 Replies
View Related
Jul 20, 2005
I'm seearching for information regarding database replicationperformance. We need to compare the performance of replication for SQLServer and Oracle and it is urgent! Anyone who can describe theperformance bottlenecks for each database when performing replication,or can point me to a white paper or webpage.
View 1 Replies
View Related
Apr 18, 2006
Hello,
I am trying some replication sample.I create a table with thousands of records in the publisher side. as I create a subscription(the subscription database is on a remote machine),
the whole table is created on the remote database.
I wanted to measure the performence as:
1. how much time was taken in filling the whole table in the subscriber side?
2. If i insert some 10000 records on publisher side, I want to measure, how much time was taken in inserting the same records on the subscriber?
How do I measure this ? Can I use some Log reader stuff.
thanks in advance
View 3 Replies
View Related
May 15, 2007
Hello to all,
I have a performance question: we have a cluster with 2 SQL instances on 1 node (another instance is on another node, but no link with my current problem!). Let's call them C1SQL1 and C1SQL2.
This node is a Hyperthreaded Xeon 2.8Ghz with 1 gig of memory.
These 2 instances are using transactionnal replication and are configured as the distributor and publisher. C1SQL1 is not using much power, it's a small replication with around 10 agents. C1SQL2 is a bit heavier, with around 100 distribution agents. C1SQL2 has around 50 subscribers in 12 publications, but not all subscribers are used in each publications.
Once in a while, this cluster node impacts our production environment (since it's also a production server) and we're wondering if performance wise, it's really not powerfull enough to be the distributor?
I've isolated C1SQL2 on it's own logical CPU, and in idle mode, the replication workload (history, checking if new transactions are made) peaks at around 15-50% each 4-10 seconds.
Can I have any input on this?
Thanks!
View 1 Replies
View Related
Feb 25, 2004
Hi
I have sucessfully set up transactional replication, allowing the subscriber to update the publisher. All works well for a while, but after a couple of weeks or so it fails, but always for a different reason !
My question is, is there anything that can be done to help replication stay healthy. I had thought of doing regular backups of the database and the transaction log, and then truncating the transaction log.
Any advice, or links to other troubleshooting resource much appreciated.
View 1 Replies
View Related
Dec 14, 2006
Hi all.
Any assistance would be greatly appreciated.
We recently created transactional replication to hopefully improve performance issues we were expereincing. The replication is between 2 SQL Servers (2000), and since we have introduced the replication, the performance has degraded considerably.
I will try and explain the scenario.
We have a primary db that our internal users use and we also have the newly replicated db that our website and another application use. The users are complaining that the website and the internal application is extremely slow and I was just wondering if it is possible to do an Index Tuning on both the primary db and replicated db based on trace files so as to create new indexes or would this have an impact on the replication?
Thanks in advance.
View 4 Replies
View Related
Dec 18, 2006
Hello,
I have been experiencing some difficulties with poorly performing synchronizations using replication from SQL Server 2005 to SQL Server Mobile running on Windows Mobile 5 devices. Currently there are two main databases (each client will only use one of them), the 1st one has around 500,000 rows, and the 2nd has about 1,200,000 rows. The initial synchronization for the 1st database takes around 45 minutes, and for the 2nd, around 2.5 hours. This is quite long, but we have comforted our clients by saying that this is a one time delay, and that further synchronizations will be much quicker. Well, synchronizing the data after this is usually quite speedy, however, things get bad rather quickly when the number of changes increases.
In normal cases, the client will have at most a few thousand changes and all is well, the synchronization will typically be under a few minutes - no big deal. Once in a while though, there are a substantial number of changes to the database (from the SQL Server 2005 side), perhaps around 50,000 changes. When this happens, the synchronization process doesn't seem to ever finish (I've left it over the weekend and come back to find it still synchronizing). For the record, there seems to be a level at which the database will finish synchronizing, but be agonizingly slow - around 10,000 to 20,000 records will finish eventually (but take a few hours, at which point it's faster to just blow away the database and start again from scratch). This is obviously not acceptable, and I need to find a way to resolve this. Does anyone have any thoughts?
While on this topic, why does this synchronization process take so long anyways? The snapshot creation (even for the database with millions of rows) finishes in a couple minutes, and the actual transfer of data shouldn't take more than a few minutes. The device can't possibly be storing the database content in memory (the SDF file ends up being between 40MB and 100MB), but when I watch network activity, there tends to be an initial busy period, then a periodic and fairly small spike every few seconds until the process completes, so the connection isn't being saturated at all.
At this point, I am almost considering breaking the nice database design I have and creating combined logical records to see if reducing the number of rows may help. I'd really prefer not to have to go this route though, so if anyone has any suggestions, I'd really appreciate some feedback.
Thanks,
Adrien.
View 6 Replies
View Related
