Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Snapshot replication for large database Expand / Collapse
Author
Message
Posted Tuesday, August 11, 2009 11:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 8, 2010 10:45 PM
Points: 122, 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. "





Thanks ,

Shekhar

Post #769110
Posted Sunday, August 16, 2009 5:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:26 PM
Points: 2,242, Visits: 3,645
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
Post #771598
Posted Sunday, August 16, 2009 9:46 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
"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
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #771681
Posted Sunday, August 16, 2009 10:03 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:52 PM
Points: 528, Visits: 1,258
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.
Post #771684
Posted Monday, August 17, 2009 2:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #771754
Posted Monday, August 17, 2009 2:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
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
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #771757
Posted Monday, August 17, 2009 3:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 17, 2009 3:37 AM
Points: 1, Visits: 0
hi
thanks so much for your share


logiciel emailing
Post #771768
Posted Tuesday, August 18, 2009 7:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 4,352, Visits: 6,168
I think there are better ways to 'sync' VLDBs than snapshot replication.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #772701
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse