Replication

Question

Replication

erics44

SSCertifiable

Points: 6311
More actions
September 5, 2012 at 7:07 am

#261568

What are the issues with using replication to move data between a live database and one used for reporting?
is this the best way to get real time data into a reporting database?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

erics44 (9/5/2012)
What are the issues with using replication to move data between a live database and one used for reporting?
is this the best way to get real time data into a reporting database?

There is no "best way". It depends on your situation.

For near real time you will need to use transactional replication and control access to prevent reporting data modification.

erics44 SSCertifiable Points: 6311 More actions · Answer 2

reporting data could change from time to time, why would you need to prevent this?

sorry i dont really understand exactly what replication is all about

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 3

Replication uses magazine/newspaper terminology.

Publisher = Source Server

Subscriber = Destination Server

Article = Table/Stored procedure/view

Publication = Group of articles

You would want to limit access to the subscriber as there is very little to prevent someone accessing the subscriber and changing/removing data. Its a rare occurrence but very possible that an application with R/W functionality is accidentally pointed at a reporting server. Its not to big an issue but can break reports and cause replication errors.

erics44 SSCertifiable Points: 6311 More actions · Answer 4

ahh i see

so you mean its good to prevent someone being able to go into the reporting server and changing data where as the data should be changed in the live server

another question, does replication have to be a direct copy of the live server?

so can you change it so when a transaction is updated in the live database the information is stored in the replicated database in a different way?

to try and clarify what i mean, you might have 2 tables in the live database (linked by a foreign key perhaps) that you want to store as a single table in the replicated database, so when the replication happens it brings through the foreign key from the other table too

crashdan SSCommitted Points: 1990 More actions · Answer 5

erics44 (9/5/2012)
ahh i see
so you mean its good to prevent someone being able to go into the reporting server and changing data where as the data should be changed in the live server
another question, does replication have to be a direct copy of the live server?
so can you change it so when a transaction is updated in the live database the information is stored in the replicated database in a different way?
to try and clarify what i mean, you might have 2 tables in the live database (linked by a foreign key perhaps) that you want to store as a single table in the replicated database, so when the replication happens it brings through the foreign key from the other table too

SqlServer will prevent you from updating replicated data. As to your other question about storing 2 tables in 1, replication will not handle that. you would have to write a process to do that on your own.

erics44 SSCertifiable Points: 6311 More actions · Answer 6

ok perhaps replication isnt what i need

it seems like more of a dev / back up / disaster recovery method

I am looking into ways of moving data into a reporting database and trying to keep the data real time

if i need to write procedures that will organise the data after the replication then this kind of defeats the object for me

i think 🙂

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 7

Not in the way you describe.

You can publish tables using a foreign key constraint type link using Merge replication but not to a single table. i.e. you can publish the primary table with a filter, call it WHERE active=1, and because of the publication links the related records to those filtered would accompany the row(s) in the primary table.

You "could" create a customized process to merge the data on the subscriber. Maybe a logical database with only cross database views would work for you to merge the data.

crashdan SSCommitted Points: 1990 More actions · Answer 8

erics44 (9/5/2012)
ok perhaps replication isnt what i need
it seems like more of a dev / back up / disaster recovery method
I am looking into ways of moving data into a reporting database and trying to keep the data real time
if i need to write procedures that will organise the data after the replication then this kind of defeats the object for me
i think 🙂

triggers aren't always the best idea, but might be something you want here (on insert/update/delete). you can use it to insert into another table that "flattens" the data you want out.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 9

crashdan (9/5/2012)
SqlServer will prevent you from updating replicated data.

Sorry. That is incorrect. It will not.

erics44 SSCertifiable Points: 6311 More actions · Answer 10

thanks for the replies

i obviously still do have options with replication, triggers and such as mentioned

one more question, if i am using all of these processes will the performance of the live database be affected much?

so if i have a trigger on the replicated database, I know how tempremental triggers can be, could a broken trigger on the replicated database affect the live in any way?

crashdan SSCommitted Points: 1990 More actions · Answer 11

MysteryJimbo (9/5/2012)
crashdan (9/5/2012)
SqlServer will prevent you from updating replicated data.
Sorry. That is incorrect. It will not.

I stand corrected. I looked into my permissions and only allow selects. Thank you for the correction

crashdan SSCommitted Points: 1990 More actions · Answer 12

erics44 (9/5/2012)
what do you think about my moving data to a reporting database issue?

It sounds like you are looking for 2 different things. one to move data to another database for reporting, and a second piece to flatten that data out to one table.

You can use replication for the first piece, but that second piece will require some coding.

erics44 SSCertifiable Points: 6311 More actions · Answer 13

crashdan (9/5/2012)
erics44 (9/5/2012)
what do you think about my moving data to a reporting database issue?
It sounds like you are looking for 2 different things. one to move data to another database for reporting, and a second piece to flatten that data out to one table.
You can use replication for the first piece, but that second piece will require some coding.

yep i think so

can a broken trigger say in the replicated database affect the live database at all?

so if for some reason the replication fails due to the trigger will that be all that fails?

crashdan SSCommitted Points: 1990 More actions · Answer 14

erics44 (9/5/2012)
crashdan (9/5/2012)
erics44 (9/5/2012)
what do you think about my moving data to a reporting database issue?
It sounds like you are looking for 2 different things. one to move data to another database for reporting, and a second piece to flatten that data out to one table.
You can use replication for the first piece, but that second piece will require some coding.
yep i think so
can a broken trigger say in the replicated database affect the live database at all?
so if for some reason the replication fails due to the trigger will that be all that fails?

depends on the type of replication you use. I would use transactional replication, the build from there. The only way it could affect the live data would be if the trigger were written to touch the live data.