July 27, 2012 at 11:49 am
I got a question. A server X has database with tables which use no compression.
I want to replicate it using transactional replication on server Z.
I would like to have the database tables on server Z compressed using pare or row compression. can that be done? or compression must be enabled on both side in order for replication to work?
Thanks
July 27, 2012 at 10:57 pm
As per my current understanding there is no limitation, you are free to choose the compression option on the subscriber.
This is because data compression is transparent to an application \ consumers of data. A database connection is not aware of how the data is saved internally , it only cares about how it gets it and Subscriber in a replication paradigm can be compared to a application pulling data from publisher.
I mean it is much like a 4GL ( like 4th generation language, you specify what you want and not care about what code is being run internally to get it) )
A 4GL may run a 3 GL (Like VC++) when you execute a query but we don't have to know about it to get the results that we are looking for.
My reasoning is not based on any related literature, therefore I can be proved wrong. ( would be more than happy to be corrected if that is indeed the case)
July 29, 2012 at 4:23 am
First data compression works only with Enterprise edition.So make sure your edition is Enterprise.
Second compression is a property which does get replicated in replication.So if your table is compressed on the publisher then compression would be applied to the subscriber.But for that to work you subscriber also needs to be enterprise edition else the compression wont work on subscriber.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 29, 2012 at 6:39 am
Yes got EE on both but i want to use compression only on the subscriber. Is that possible?
July 29, 2012 at 10:28 am
Theoretically yes it should work.
But for some reason if you have to re-run the snapshot agent and re-initialize the subscriber then I believe you will need to reset the compression settings on the subscriber.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 30, 2012 at 3:39 am
Sachin Nandanwar (7/29/2012)
Theoretically yes it should work.But for some reason if you have to re-run the snapshot agent and re-initialize the subscriber then I believe you will need to reset the compression settings on the subscriber.
It will work. I'd recomend doing this doing a quiet period or stopping the distribution agent while this being enabled due to locking.
Depending on how big the table is this may take some time.
July 30, 2012 at 4:04 am
What i would like to do is to copy the database on the subscriber. I will copy just tge structure with no data.
Enable compression on the tables i want.
And kick off replication.
Will it work right? The data replicated will be compressed.
July 30, 2012 at 6:36 am
sugnu iu (7/30/2012)
What i would like to do is to copy the database on the subscriber. I will copy just tge structure with no data.Enable compression on the tables i want.
And kick off replication.
Will it work right? The data replicated will be compressed.
Yes it would work provided you know what you're doing.
How will you get the data there?
If you intend on using a snapshot, you will need to ensure the default action is set to delete/truncate/none so that the table is not dropped and recreated (wiping out compression)
July 30, 2012 at 8:46 am
Exactly.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply