| Author |
Topic |
|
ddobbertin
Yak Posting Veteran
51 Posts |
 Posted - 2010-08-11 : 13:22:21
|
| I have a production database that I need to replicate (hope I'm using the correct term there) on another SQL instance. I need to replicate so that I can utilize the database for creating a backend for reporting and need the most accurate data possible but my company does not want me to hit the live tables. What is the best way for me to do this? I have full access to both servers and ideally would like to replicate as live as possible from production to offline. Thanks in advance! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-11 : 13:34:45
|
| you said its for reporting. then isn't it better to develop an OLAP system which suits your reporting needs based on live production tables or are you looking at real time data?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2010-08-11 : 14:12:26
|
| @ visahk16....basically I've been told I can copy the data but I cannot develop the web front end to hit the tables for users to query. Also the data isn't structured how it will be needed for the reports, I will have to massage it so moving it will probably be best so I can manipulate it a little bit to a final table.@ tkizer...thanks..I will look into transactional replication. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-11 : 14:27:52
|
| you're coming to my point @ddobbertin. then you should be designing a warehhouse with tables suiting your reporting needs which might need a bit of denormalising and then use them as source for your reports.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2010-08-12 : 13:44:34
|
| I understand.....my issue is that my company does not want us hitting the production tables for denormalising or any type of manipulation for fear of causing harm to the database or performance of the server the database runs on. They just want us to copy everythig out as it is and do any and all manipulation to create the reporting tables on another server. That is why I was thinking of replicating them out. Maybe I am using the incorrect terms? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-12 : 14:00:43
|
| replicating again means copying the data from main server onto the replicated server (publisher to subscriber). the frequency of copy will vary depending on type of replication. I really feel developing a DW would be better where you port data from production only once a day that too by overnight jobs which is outside busy hours.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-12 : 14:07:49
|
quote:
Originally posted by ddobbertin
I understand.....my issue is that my company does not want us hitting the production tables for denormalising or any type of manipulation for fear of causing harm to the database or performance of the server the database runs on. They just want us to copy everythig out as it is and do any and all manipulation to create the reporting tables on another server. That is why I was thinking of replicating them out. Maybe I am using the incorrect terms?
Both replication and data warehouse support those requirements.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2010-08-12 : 15:13:00
|
| Ok....then I guess my lack of understanding then would be why data wharehouse is the preferred method in this case? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Database Replication