I want to know if there is a way to achieve the following with SQL Server 2012 Enterprise Edition?
I have Table A: on Server 1 with say 10 columns.
Table A:
| col1 | col2 | col3 | coln | col10 |
on Server 2, I need a live copy of Table A in Table B on Server 2. However I only actually need 3 of the columns replicated, as that's all I use.
Table B:
| col1 | col3 | col10 |
I mentioned "live" above, By this I mean that when I insert a new value or update a new value in Table A that change will be propagated as soon as it can to Table B.
Table B is a read only table. And Table A gets data written to it. So A will be the publisher and B the subscriber.
How can I achieve this sort of setup? In other words do I need to use some sort of replication? or can I achieve this with Always ON High Availability.
Best Answer
You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick users out temporarily as you restore new logs. If log shipping the entire database is a concern, then you could instead use a trigger to populate another database on the same instance - which only has this table and the required columns populated - and then log ship that database to the other instance:
I have a blog post on making log shipping work much more like a set of readable secondaries:L
You can also look at these posts for an approach I've used to do something a lot like replication but with more control, less impact, and none of the troubleshooting nightmares:
I also talk about some ways to provide read-only copies of the data for reporting at the end of this post: