SQL Server Performance

Merge replication with 40GB of data

Discussion in 'SQL Server 2005 Replication' started by ben5173, Aug 3, 2007.

  1. ben5173 New Member

    I would like to ask some question and get some opinion regarding my problems.
    Things that could not be change:
    1. SQL Server 2005 sitting in our server and SQL Server 2005 Compact Edition in our client PC.
    2. I understand that SQL CE only works with merge replication.
    Things that still can be change:
    2. 30 over tables/articles in one publication, and one of the table contains over 40millions of data (around 10GB).
    The problems:
    I tested out individual database as well as the rest of the database together in one publication Except the the HUGE table and everything was really smooth. When i added in the HUGE table, i cant generate snapshot. The snapshot agent just stopped on the server side. Even with the database alone in one publication, it is still the same outcome.
    The specification:
    1. Pentium 4HT with 1GB of RAM. 23GB of free hard disk space
    2. XEON 2.66Ghz with 4GB of RAM. 200GB of free hard disk space
    Both hardware system have failed. Is there any other way to work around this?
    Currently, i have been following the post below
    but i don't think it is for SQL CE as i could not find the 'Subscriber has the schema and data' options.
    Thanks in advance!!
    #Update: I posted on msdn sql ce replication forum, but still did not get any reply. Hope to get opinion on this soon! Thanks and Have a nice day too.
  2. satya Moderator

    Have you observed any errors or warnings during this behaviour on SQL server & SQLCE instances?
    Better to chase it up from the warnings to tackle the issue.
    Fyi on merge replication issues.
  3. ben5173 New Member

    nope, i did not know how to track any error during the progress. I have tested merge replication before with smaller database (2 Millions records per table). The final testing stage involve 40 millions (10GB) in one of the tables which caused the computer to locked up. Log file size keep on increasing in a rapid speed. There is no errors or warnings, the snapshot process running like normal but it is like forever (until PC hangs). When i delete 35millions of data from 40 millions (left only 5millions), it work fine.
    How to support larger database?
    Now, the only way to get through this (temporary), i created a empty table and created the snapshot. After creation of snapshot, i will insert the data 2 millions at a time to prevent the pc from hanging. The snapshot will be turn off after initial snapshot of empty tables. If snapshot is turn on, the 40 millions records table will make the PC hang again.
    Is there any impact on stopping the creation of subsequent snapshot? There is one forum where one of the MVPs says "It has no impact". Hope it is true.
    The true solution that i'm looking for is of course not the one i'm using. Hope can get some ideas on solving this.
    Thanks for replying~!
  4. satya Moderator

    Concurrency issues occur when merge replication of a delivery fails halfway through a process. This may require all the tables to be updated or none should be updated. SQL Server 2005 introduces the concept of logical records that enable the DBA define relationships between merge table articles.
    You can take help of replication monitor tool that will let monitor replications on a server. It functions independent of the SQL Server Management Studio and allows delegation of monitoring tasks to non-administrators. Since it is an independent application it can function in the passive mode without impacting the server. The Replication monitor helps identify performance issues before they attain criticality and endanger the stability of the applications. Acceptable performance thresholds for a publication can be defined. When the threshold is reached, alerts can be used to warn the user.
    Technet article on performance enhancement.
  5. ben5173 New Member

    Hi, Thanks for the reply. I'm still in the process of understanding of using the merge replication monitor. By the way, my merge replication process do not have any problems. It is the process during the creation of snapshots. The part when we need to start our snapshot agent. It will stops at certain % and go on forever.
    I still using the last method that i mention last week, which is create snapshot when tables are empty and shut off snapshot agent for future creation then inserting records batch by batch (2millions per batch). 40Millions per batch will lead to "infinite" process also. Tested with both system spec above.
    Thank you.
  6. ben5173 New Member

    I found this just now~!
    I will proceed to test on this to see whether that will solve my problems of or not.
    9mil and 40mil is a huge difference but the problems are actually what i'm facing.
    Currently still in the process of testing with the method i mention earlier.
    Hope to get a solution soon.
    Thanks and have a nice day.
  7. ben5173 New Member

    This is still not working.
    I need to open a seperate thread with more specific problem

  8. satya Moderator

    As specified you can take help of replication monitor tool that will let monitor replications on a server, also PROFILER & other reference of logs would help to see where is the problem .
  9. ben5173 New Member

    Thanks, will update the thread once i tried it out.
    Thank you.
  10. ben5173 New Member

    I did not seek microsoft help for this due to lack of time for this research. I had to move on with RDA which yields satisfying results for a huge tables like that. The merge replication is fast and *Helpful* unlike RDA. Initial Snapshot and Synchronization of client does not perform really well on huge tables (not database) for my case.
    Thank you guys!!

Share This Page