SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Snapshot replication for large database


Snapshot replication for large database

Author
Message
ShekharNaidu
ShekharNaidu
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 70
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

ps.
ps.
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7133 Visits: 3668
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
Sarab_SQLGeek
Sarab_SQLGeek
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1082 Visits: 558
"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 Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Anam Verma
Anam Verma
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 1318
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.
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7465 Visits: 2250
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."
Sarab_SQLGeek
Sarab_SQLGeek
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1082 Visits: 558
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 Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
huyenptt126
huyenptt126
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 0
hi
thanks so much for your share


logiciel emailing
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32161 Visits: 8672
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search