Bhuvnesh (2/3/2013)
AccDba (2/3/2013)
I will create a new database and put these 45 tables keep actual transactional database small and slim. So its like having a copy of the same tables but this copies will be used for reporting only from a different database.How will you uodate the tables in NEw database from main database. ? Batch process ? Partitoning ?
I am thinking about transaction replication where the publisher, distributor and subscriber are located on the same database server instance.
AccDba (2/3/2013)
the issue here is when I run a purge job (maintenance) on a nightly basis on the transactional tables how would i prevent the impact of not getting the data deleted from the reporting tables.Where do you run this job , on Main(source) database ?
The purge/delete job is run on the source database which the publisher database. But I came to know that we have a fix for this as mentioned in the below url:
http://www.mssqltips.com/sqlservertip/2520/options-to-not-replicate-sql-server-delete-commands/
But can you tell me if there is a way that I can restrict the updates specific only to a column? Lets say if updates occur on source table(publisher database) on column a, column b and column c then I dont want any thing to get updated in the destination database tables (subscriber database). But if update occurs on column d on source table(publisher database) then I want it to get updated in destination table too (subscriber database). Is this possible?
Thanks Again