Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Replication Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 7:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:36 AM
Points: 286, Visits: 572
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?
Post #1354500
Posted Wednesday, September 5, 2012 7:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.
Post #1354506
Posted Wednesday, September 5, 2012 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:36 AM
Points: 286, Visits: 572
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
Post #1354513
Posted Wednesday, September 5, 2012 7:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.
Post #1354527
Posted Wednesday, September 5, 2012 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:36 AM
Points: 286, Visits: 572
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
Post #1354539
Posted Wednesday, September 5, 2012 8:05 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 PM
Points: 782, Visits: 730
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.
Post #1354555
Posted Wednesday, September 5, 2012 8:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:36 AM
Points: 286, Visits: 572
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 :)
Post #1354563
Posted Wednesday, September 5, 2012 8:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.
Post #1354564
Posted Wednesday, September 5, 2012 8:13 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 PM
Points: 782, Visits: 730
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.
Post #1354565
Posted Wednesday, September 5, 2012 8:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
crashdan (9/5/2012)


SqlServer will prevent you from updating replicated data.


Sorry. That is incorrect. It will not.
Post #1354566
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse