August 11, 2009 at 11:11 pm
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:
August 16, 2009 at 5:28 am
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.
August 16, 2009 at 9:46 pm
"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
August 16, 2009 at 10:03 pm
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.
August 17, 2009 at 2:26 am
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."
August 17, 2009 at 2:48 am
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
August 17, 2009 at 3:53 am
August 18, 2009 at 7:40 am
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