<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Replication
19 posts, Page 1 of 2
1
2
»»
Replication
Rate Topic
Display Mode
Topic Options
Author
Message
erics44
erics44
Posted Wednesday, September 05, 2012 7:07 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 20, 2013 7:27 AM
Points: 266,
Visits: 512
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
MysteryJimbo
MysteryJimbo
Posted Wednesday, September 05, 2012 7:12 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:28 AM
Points: 1,125,
Visits: 14,511
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
erics44
erics44
Posted Wednesday, September 05, 2012 7:17 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 20, 2013 7:27 AM
Points: 266,
Visits: 512
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
MysteryJimbo
MysteryJimbo
Posted Wednesday, September 05, 2012 7:31 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:28 AM
Points: 1,125,
Visits: 14,511
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
erics44
erics44
Posted Wednesday, September 05, 2012 7:39 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 20, 2013 7:27 AM
Points: 266,
Visits: 512
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
crashdan
crashdan
Posted Wednesday, September 05, 2012 8:05 AM
Right there with Babe
Group: General Forum Members
Last Login: Thursday, May 23, 2013 9:24 AM
Points: 770,
Visits: 682
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
erics44
erics44
Posted Wednesday, September 05, 2012 8:10 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 20, 2013 7:27 AM
Points: 266,
Visits: 512
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
MysteryJimbo
MysteryJimbo
Posted Wednesday, September 05, 2012 8:12 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:28 AM
Points: 1,125,
Visits: 14,511
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
crashdan
crashdan
Posted Wednesday, September 05, 2012 8:13 AM
Right there with Babe
Group: General Forum Members
Last Login: Thursday, May 23, 2013 9:24 AM
Points: 770,
Visits: 682
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
MysteryJimbo
MysteryJimbo
Posted Wednesday, September 05, 2012 8:13 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:28 AM
Points: 1,125,
Visits: 14,511
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 »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
