Snapshot replication for large database

  • Hi

    is snapshot replication is supported for large databases??? can anyone please help me out in this regard..

    as i am confused with the statements

    "Snapshot replication has a lower continuous overhead on the Publisher than transactional replication, because incremental changes are not tracked. However, if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication. "

    :w00t:


    Thanks ,

    Shekhar

  • The statement doesnt say it's not supported. It mentions the amount of recources utilized in BCPing out data from source and then BCPing in to the target is huge for large databases.



    Pradeep Singh

  • "Snapshot replication has a lower continuous overhead on the Publisher than transactional replication, because incremental changes are not tracked. "

    In Snapshot replication it creates a snapshot at once and apply on the subscriber where as in Transaction replication the agent has to keep running on a certain regular interval to keep replicating the changes to subscriber.

    Now obviously if you are running one service let's say for 20 minutes in a day and second service is keep on running after every 2 minutes(just example), the overhead of the second one would be much higher on publisher.

    However, if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot.

    Now lets come back to your scenario which is

    large databases

    see if your database is very large then obviously creating snapshot and replicating it to subscriber will also take many resources.

    Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication.

    decision: If your subscriber(s) can work on the data which is one day older obviously you can safely choose either snapshot replication or transactional replication.

    If the database size is small you can also afford to create snapshot 3 or 4 times a day but if the size of your database is that big obviosly creating that much bulky snapshot and replicating it to all users would be not a good practice, Moreover if the database size is big we can safely assume that all the data is not changing everynow and then, comparatively a small portion of the data is changing so why to replicate the same bulky data again and again? instead we can use transactional replication the trade-of will be we can save a lot of resources in this case if we use transactional replication.

    Regards,
    Sarabpreet Singh 😎
    Sarabpreet.com
    SQLChamp.com
    Twitter: @Sarab_SQLGeek

  • If the database size is small you can also afford to create snapshot 3 or 4 times a day but if the size of your database is that big obviosly creating that much bulky snapshot and replicating it to all users would be not a good practice.

    agreed on above statement. Another option could be to use LOGSHIPPING with STANDY READ ONLY option on destination server. You can set the interval time as per business requirements.

  • Yes it's possible to snapshot large database but your hardware should be sufficient enough to support it. There will be load on Distributor server as Snapshot and Distributor Agents are running on the server.

    Also another optin is Database Snapshot which is introduced in SQL Server 2005 onwards.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Also another optin is Database Snapshot which is introduced in SQL Server 2005 onwards.

    What solution you are talking abt with DB Snapshot?

    Regards,
    Sarabpreet Singh 😎
    Sarabpreet.com
    SQLChamp.com
    Twitter: @Sarab_SQLGeek

  • hi

    thanks so much for your share

    logiciel emailing[/url]

  • I think there are better ways to 'sync' VLDBs than snapshot replication.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 8 posts - 1 through 7 (of 7 total)

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