Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rcombis
Starting Member
3 Posts |
 Posted - 2012-05-01 : 22:28:23
|
| I have an inherited an process/application that takes data from an OLTP DB and moves (and slightly transforms) it into a reporting DB (which happens to reside on the same server). (The OLTP is a basic Order Processing DB)The problem is that an SSIS package does this move/transform. It deletes all the data in the tables and recreates them every hour. As this db has grown the process is now causing all kinds of havoc. Obviously there is a better way to do this.Replication itself won't solve this issue because the tables are slightly transformed and are a subset of the original table based on a condition in another table.So for example the Order table in the reporting DB will only contain customers who the client designates and that field is in a table called company. So a simple join gets me this data. But the replicated data can not be filtered.Now the other thing is that an external application which I have no control over is using this reporting.Orders table (as well as other tables from reporting). So basically I have to recreate the reporting DB exactly how it is just get data into it more efficiently.My first thought on this was to replicate the Orders table to the Reporting database and rename it as Orders_Repl. Then create a view called Orders that transforms the data as needed and also is a subset of that data. I think this will work but wondering what my other options?From a performance standpoint I have a few questions:1. Say I replicate the data and create the view as above. Seeing as these 2 DBs (reporting and prod are on the same server) what is the difference performance wise if I just create the view from the prod database in the reporting database (i.e. skipping the replication altogether)? (Would indexing either view help?)I guess a more general question is if I have 2 databases on the same server, what are the performance benefits from segregating this data, as in a reporting app? (If there were different servers it would be make sense). Thanks,Rob |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-05-02 : 12:34:36
|
| I think your SSIS packages need to be modified to NOT delete and repopulate all the data. At the very least, I hope it's truncating and not deleting. Anyway, this is seldom a good idea.Replication can indeed be filtered. Not sure why you think it can't.It's a good idea t have the reporting on a seperate instance, but if this isn't possible, it should still be in its own database. Don't create reporting views over the OLTP tables. You don't want reporting to ever block OLTP.Without knowing anything about your system, I'm going to say that fixing the SSIS package(s) is the right approach. |
 |
|
|
rcombis
Starting Member
3 Posts |
Posted - 2012-05-02 : 13:19:43
|
| Thanks for the comments.The problem with filtering the replicated data is that the data is filtered based on a condition that resides in another table.A simple explanation. There are 2 tables Orders and Company. The Company table has a field that tells if the data is to be included in the Reporting system. So for transactional replication, I don't think I can filter the replication based on this condition. Am I wrong?The Orders table frequently gets updated, inserted to and deleted from.I am creating the views right now (Which I will be able to use against the reporting DB or the OLTP DB). I hear you about not creating reporting views over the OLTP Data. So maybe I should just replicate all the data (hourly schedule) to the reporting Database and then just run the views from the reporting DB that mimic the table structure of the existing Report DB?The reporting DB is not used that much, could I tell the query not to lock the table if I go direct against the OLTP.The SSIS package does TRUNCATE. I am not sure fixing this package is the best route. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-05-02 : 13:47:47
|
| Assuming you have primary keys on all the tables that need to be copied over, transactional replication (push) would work fine, with views over the subscriber (as you described above).Eventually, it would be nice to build a true reporting environment. |
|
|
|
rcombis
Starting Member
3 Posts |
Posted - 2012-05-02 : 17:04:55
|
| What would be the better way to do this for a true reporting environment? My thinking would be to put the reporting environment on a separate server. Replicate the data over to that and run all the reporting queries/views from this.Anything else that you would suggest? |
|
|
|
SQL Basic
Starting Member
8 Posts |
Posted - 2012-05-31 : 06:04:08
|
| Its depend what your case. You can follow what your actual condition. |
|
|
|
|
|
|
|
|
Replication or Not?