SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication


Replication

Author
Message
erics44
erics44
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 739
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?
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5385 Visits: 15346
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
erics44
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 739
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
MysteryJimbo
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5385 Visits: 15346
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
erics44
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 739
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
crashdan
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1030 Visits: 757
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
erics44
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 739
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 Smile
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5385 Visits: 15346
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
crashdan
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1030 Visits: 757
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 Smile


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
MysteryJimbo
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5385 Visits: 15346
crashdan (9/5/2012)


SqlServer will prevent you from updating replicated data.


Sorry. That is incorrect. It will not.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search