InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 01:19:00 GMT20http://www.sqlservercentral.com/Forums/Topic445276-143-1.aspx[quote][b]GregoryAJackson (10/6/2010)[/b][hr]Good Morning....thanks for the comment.You could use Trnxn-Replication to copy the data to your staging server and then SSIS to ETL the data from the staging server to you reporting DB.Mirroring could work too, depending on exact requirements and how often you wanteed to back up your transaction logs.Hope this helps...GAJ[/quote]Im leaning more towards replication, because of the low latency. Log shipping would not work because of the latency. Not sure about the mirror (and snap) approach, I need to do this to 700GBs worth of data.Wed, 06 Oct 2010 07:59:30 GMTgrahamchttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxGood Morning....thanks for the comment.You could use Trnxn-Replication to copy the data to your staging server and then SSIS to ETL the data from the staging server to you reporting DB.Mirroring could work too, depending on exact requirements and how often you wanteed to back up your transaction logs.Hope this helps...GAJWed, 06 Oct 2010 07:44:01 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxThread resurrection time :cool:Great article... this has been an issue at every place I have worked! Anyone got experience with the larger DB side of things? Our PROD server is 700GBs. Requirements would be very little (if any) "lag" between the PROD and REPORTING. What would the ideal solution be (no real budget constraints)?My thoughts are pretty much leaning towards transactional replication. Are there other options I should be considering?Wed, 06 Oct 2010 03:37:55 GMTgrahamchttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxYeah It would depend on the requirements for the reporting environment (mostly the lag time that is acceptable).LogShipping would be a great solution in most cases.gajThu, 08 Oct 2009 13:13:18 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxGreat Post! Any thoughts on the best architecture for creating a standby instance for reporting? Our prod database is over 800GB. Reporting requirements are during business hours (8:00-5:00).Log Shipping? Thank you!Thu, 08 Oct 2009 12:35:57 GMTSequelDBAhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxlolyeah. I usually leave em at home.thanks againGAJFri, 07 Aug 2009 11:30:05 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxNeat gun collection too :) I'd bet those surly developer types don't give you a lot of crap :w00t:Fri, 07 Aug 2009 11:13:34 GMTRowland Goslinghttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxThanks Rowland,I agree.I'd love to hear more discussion on this topic.Never an easy one to handle....cheers,Greg JFri, 07 Aug 2009 10:01:28 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxWe've all been there: This is a classic problem and really could use more discussion. Thank you for taking the time to write this article and sharing your solution. Nice job! :-)Fri, 07 Aug 2009 08:09:38 GMTRowland Goslinghttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxHadn't considered it but it certainly is a good ideaThu, 29 Jan 2009 12:23:14 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxOn using ReportingDB1 and ReportingDB2, did you consider appliances like F5 Big-IP using iConnect so that your reporting layer would always point to a single virtual IP and ETL to a different Virtual IP and the swap happening without the knowledge of the codes on either side?Thu, 29 Jan 2009 12:21:58 GMTdarabafhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxgood question...that was exactly why we went with mirroring.SQL Server Mirroring automatically copies the transaction logs from your source to the target.the logs are backed up as part of normal OLTP operations and the copying of the logs from the OLTP server to a "Staging" Server has zero impact to the OLTP.Once the logs are copied to the staging server, the staging server does the work to move the data (ETL) to the reporting system.This is how we do it without impacting the OLTP.The article shows a picture and provides the details.If you have more questions, please feel free to contact me directly. Contact info is provided in my profile.GAJThu, 04 Sep 2008 07:21:08 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxGregory, how did you create your staging tables without impacting your OLTP?Thu, 04 Sep 2008 06:42:26 GMTsteitelbaumhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxWe have nearly an identical situation and I had mirroring successfully setup for 32 database ranging from 10-40GB with a couple of them having a very high number of transactions per day. We are running in a Windows Cluster environment with 3 nodes so I setup Asynchronous mirroring because we don't have the requirement of purging the OLTP databases so we can always rebuilt the reporting databases if needed. The mirroring solution worked perfectly to move the data in near real-time to a separate reporting database server. All was running just fine until we had to do some maintenance on one of the cluster servers. We failed ClusterA over to ClusterB and rather than taking the usual 3-7 minutes for the SQL instance to come back up and recover the databases it took 9 hours.The only reason that we have been able to come up with is that when you failover it shuts down the virtual sql instance on ClusterA and starts it up on ClusterB causing the mirroring to freak out and think that the source and destination are out of synch which caused a complete resynch of the mirror destination which caused the source to hang in recovering mode until it was completed.Is anyone running mirroing in a clustered environment and have you experience anything similiar to what I described above? I really like the mirroring solution and would like to use it again but am afraid of having another 9 hour recovery.Thu, 28 Feb 2008 13:23:52 GMTTerry Sharphttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxwell....technically the reporting DBs never get smoked. they just get set to "Loading"even while a DB is "loading" it's still usable. the client performance will just suffer as indexes are dropped and recreated, tables are locked, etc etc etc. the users will still get their data or they'll timeout.the connections to the DB are always created quickly and dropped quickly via the middle tier.nobody is ever allowed to connect directly.Wed, 23 Jan 2008 19:11:09 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxGood point.that was another option we pursued but it did require down time (albeit short).GAJWed, 23 Jan 2008 19:08:40 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxWhat if the reporting server were being used by someone who is running a Win32 client application, for example, that is keeping a live connection to the database. When the swap of database occurs, I imagine that the client would loose its connection and any client that couldn't handle that gracefully would be unable to run in this environment. Is that truly the case or is there someway of avoiding this?Thanks,EricWed, 23 Jan 2008 18:39:45 GMTEric Stimpsonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxIt was, indeed, a good post!Just an FYI... I'm not sure how to do it myself, but the hardware boys in our shop found out that the EMC Clarion is capable of making "clones" on the fly... requires a little bit (seriously... just a little bit) of programming in, I think, Perl, but we snap half a tera-byte from one server to our reporting server in less than 15 minutes. It does currently require an outage but it would fit your "synonym" server swaps just fine and I'm sure it would to 50g in a snap.Wed, 23 Jan 2008 18:35:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxhi GAJ ,sure. am eagerly waiting for ur next post.thxsreejithMCADWed, 23 Jan 2008 14:39:00 GMTSreejithsqlhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxSreejith,thanks for the post.Glad you enjoyed the article.I'll be posting another article in the next couple of weeks.keep your eyes out for it....:)GAJWed, 23 Jan 2008 14:30:43 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxhi,its soo helpful for me. so simple . really appreaciatable.thxsreejithMCADWed, 23 Jan 2008 14:27:07 GMTSreejithsqlhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxYeah I totally agree. the staging server could be very minimal as long as the data itself was on the SAN.Ideally, I'd like the two reporting DBs to be on their own box as well.but not in the cards until we can prove the business case.currently this is holding it's own with the current hardware configuration (actually doing quite well).To be honest, we didnt even have the reporting server in the budget.we had to beg borrow and steal to get it.since reports were timing out in prod, we had a captive audience....you know how it goes. We probably wont get another machine or two until someone is bleeding again.Wed, 23 Jan 2008 11:38:22 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxSince your budget does not include another server, I am curious how much another server would cost you.If the staging area was a separate server, then could it be a less expensive server in the $2,000 to $3,000 range?Also, one of the SQL Server licenses could be Standard with minimal CALs, so wouldn't that get you into an easy to justify range?Wed, 23 Jan 2008 11:21:57 GMTDavid Machanickhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxnot hugecurrently the OLTP side is ~50GB and the Reporting DB is probably ~60GB or so.growing pretty quickly though this is a very new system.Our next phase in this project will be to regularly purge the OLTP data so that it never has more than about 30 dyas of data in it.gajTue, 22 Jan 2008 14:19:22 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxGregory,I'm curious... What size (in gig or tera-bytes) is your OLTP and Reporting databases?Tue, 22 Jan 2008 13:51:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxour transactional system is used by thousands of Auto Dealers all across the country and in Canada.the reporting system is mostly used by tech support (not a huge number of users)I have not actually time compared a sproc outside of SSIS vs the same sproc within SSIS but I dont really think there would be a huge difference.the logic in our ETL is so complex that trying to do the ETL with simple SQL would have been way too difficult.Performance is currently not giving us issues.GAJTue, 22 Jan 2008 10:47:48 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxThanks for the article Gregory - just the kind of thing I subscribe to Server Central to read.You've also generated a fair number of discussion topics I see. I was curious about you having to use 2 reporting databases due to the load generated by your ETL queries. Just from a personal perspective these kind of queries are the one's you just leave once they work, which means they're the one's that are pulling redundant fields out of the DB, and are prone to more than a few SELECT *'s etc etc. Just for reference I've had a lot of joy (I say joy, I mean mind numbing boredom, of course ;)) using cursors for ETL work - especially when denormalising, I've obtained performance improvements in the 1000s of percent doing this. I realise you've done some tuning work, but what I was most curious about was whether you've tried timing a stored procedure against an SSIS package doing the same job??? Also, how many users are using the system? Tue, 22 Jan 2008 09:38:42 GMTRichard Gardner-291039http://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxThe performance of SSIS has not been an issue for us in any way. In fact, the ability of SSIS to modify the batch sizes and the ability to use bulk copy within SSIS has helped immensely. We call SSIS from the command line and the performance has been fine.SSIS does have some bugs in it still but most of them have been fixed in SP2.The mirror and snapshot space requirements also are not that much of an issue. We'd have the additional space consumed using any of the other methods of transferring the data anyway (Replication, log shipping, etc). The snapshot of the mirror does not "Double" the space requirement as I thought it would. If you research the snapshot option a little you'll see what I mean.In all, we are happy with our design\approach.We might modify some of our ETL SSIS packages to optimize the way they are implemented, but other than that, we have no current plans to change our approach.hope this helps,GAJTue, 22 Jan 2008 06:49:22 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspx[quote][b]GregoryAJackson (1/21/2008)[/b][hr]Don,I dont understand your post regard the negatives to SSIS and regarding blocking....(I think I might disagree with you but I want to make sure before I reply in that regard...)Please expand here....ThanksGAJ[/quote]I second the request for more detail on the SSIS concerns.We are also building a model, and several of the same concepts apply. We have gone the replication route because we have a clearly defined set of reports, currently, that have a signifcantly smaller set of data than the OLTP, and this implementation (even thought I disagreed with it), is focussing on only supplying the data required for these reports. So, our articles are vastly smaller than the whole OLTP databases. I'd be interested to know the impact (additional space requirement), your "Mirror and Snapshot" design added. Also, as implied by databases, we have several source databases - some of them new world, where we are able to require Created and LastUpdate datetime columns, and other legacy databases where we don't have that remit. This has led to our current 'KeyLookup CDC' design (we want to get SQL 2008,. and look into using hte CDC functionality, but that will be down the line).Another advantage of our replicated databases, is that they can be indexed for the data accessing that will happen there, so we can index to improve our ETL process.SSIS (via several packages, run in parallel via an 'orchestration package' is our current design - this is still a work in progress - 3-4 months away from live.Tue, 22 Jan 2008 06:29:54 GMTRegan Galbraithhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxgood points....Partitioning is a really great tool but for my case (and likely in most cases) it doesnt solve all the problems associated with using your transactional system for reporting needs.When using a transactional systme for reporting needs, you really cant Optimize for OLTP or for OLAP.In a Reporting system, you'll likely want more denormalized schemas, more indexes, different fill factor settings, etc etc etc.Certainly Partitioning "could" help alleviate some of the disk io contention but I dont believe it would really get you what you wanted in most scenarios.GAJMon, 21 Jan 2008 19:02:58 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxCan you [tymberwyld] provide more details on DevExpress. Is this from devexpress.com? Which product?Mon, 21 Jan 2008 18:45:35 GMTEric Stimpsonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxHere's a question. How does correctly partitioning your tables compare? For example, if there were a partitioning schedule (say once a week for intensive tables, once a month for others, and eventually per year partitions). It's my understanding (although I have yet to put it in practice) that these partitions can be in different FileGroups which can then be in different disk resources. So, it could potentially keep reporting apps using the same production database but there wouldn't be as much contention because reports would be using different FileGroups than the live OLTP data. I've read that Jobs can be created to manage this as well. Does anyone have experience with this?Also, I've used Crystal (7 - 11.5) in the past. If I never have to use it again that will be great! I redesigned our entire Healthcare reporting to use DevExpress reporting where the User's could basically design their own reports (you're always going to have to make sure you have proper indexes regarless if a user makes a report or you). Anyway, they would layout the report real-time using the Grid for sorting, grouping, and filtering and then we'd store that "schema" as Xml in the database. Really nice design, fast and we never had to design 150+ reports for each client ever again. I think there were 5 Crystal reports left after we re-wrote everything (and it only took 2 months to rewrite the reports).Mon, 21 Jan 2008 18:38:11 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxRpt1 and Rpt2 are not aware of each other.They are never synched with each other. Each of them are individually and seperately synched with the Prod OLTP system.basically we have a table that tracks the last updated date for each db. Then our synch scripts run and catch each db up with changes since they were last updated....hope this makes a little more sense.GAJMon, 21 Jan 2008 18:36:17 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxI had started to write a somewhat similar article just last week. Although my concerns and constraints are somewhat different, and final solution completely different, I enjoyed your article. Here's how my situation differs from yours: * Ad hoc reporting must not cause locks for OLTP transactions. * Live data for reporting. There can be no delay between transactions and reporting. * No improvement in report performance necessary, no budget for new hardware.So the main issue we are correcting is that Office (SQL Query), Access, and Crystal all connect to SQL Server and have data isolation levels at "read committed" rather than the much more OLTP friendly "read uncommitted." As a result, it takes a table lock to return a table of data...So our solution has been to create another database which is a schema copy of the OLTP database, where every view and table from the OLTP database is a view of the original table in the "reporting" database. Refreshing the schema has been automated so that any table or view change on the OLTP database (which are frequent as our live system is still under constant development) are refreshed in the reporting database on a scheduled basis (2x per day).We've seen a dramatic improvement in performance due to the fact that the reporting system no longer generates locks and we can now continue to offer allow ad hoc queries from the system users, allowing our business to remain nimble and freeing the development team from a never-ending list of new data requests. This is admittedly more of a brute force solution then a highly engineered data-warehouse could provide, but I think it offers many of the advantages of more expensive solutions, without the cost of massive software re-engineering or severely limiting access to the data.Mon, 21 Jan 2008 18:24:00 GMTEric Stimpsonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxHow do you synchronise Reporting1 and Reporting2? If Reporting1 is toggled as the 'loading' DB, and therefore Reporting2 is the 'live' DB, then at some point (after the load is finished?) Reporting2 will need the transactions just loaded into Reporting1; it isn't clear (to me anyway :)) just how and when that occurs.Mon, 21 Jan 2008 17:40:10 GMTDavid Cook-169090http://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxthe reason for our production reporting issues were MANY1) lack of proper indexes2) poorly written sprocs3) way too many joins4) too much logic in the report sprocs5) poorly designed DB6) blocking7) IO subsystem not configured correctly 8) Huge use of User defined functions in the reports forcing non-set based queries9) Reports were using dynamica sql to create the correct sorting and grouping options10) etc etc etcnot only have we moved the reports into their own little world to shield the OLTP system from their potential damage, we have also greatly optimized all the reports and we have way more indexes in the Reporting DBs.Furthermore, our ETL Jobs pre-calculate many of the aggregated values and the Reporting DB is denormalized to a great extent.it's a complete redesign literally apples to orangesGAJMon, 21 Jan 2008 16:17:11 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxdid you find the root cause of why production server take more than 5 minutes and the reporting server take less than 1? caused by CPU contention? or the "read uncommit" solved the locking issue? if it's the later one, then the performance issue can be solved by transation level turn on "read uncommit".sometime report just take too long by joining tables, when table grow, the cardition join will get worse and worse, have to think about de-normalization and BI/OLAP solution at that point.just a thought.jiuluMon, 21 Jan 2008 16:10:42 GMTsunjiuluhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxyeah weve thought about that.1) all of our Reporting sprocs have isolation level set to read uncommitted so that they dont block. We are no worried about irrepeatable reads, etc.2) worst case scenario, a user that accidentally got navigated to server that had the rug pulled out from under them, they would get an error and when they retry, they'd be directed to the other reporting db.3) none of our reports take more then a minute or so to execute. that was a major goal of ours with the reporting system in the first place. Our reports originally were timing out in production all over the place even after 5-10 minutes.....Great questionsGAJMon, 21 Jan 2008 15:58:18 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxI am wondering how to handle concurrency of data loading and reporting...I know you've designed the round robin to switch loading data for every 15 minutes, what if the report last more than 15 minutes to generate, or let's say, just a second before the server1 get it's turn to refresh data, and there is a user report request come in and take 10 second to generate the report, then server 1 has to handle the concurrency of loading data and generating report...that will lead to sqlserver locking mechnism and isolation level. what's your thought about it?jiuluMon, 21 Jan 2008 15:50:12 GMTsunjiuluhttp://www.sqlservercentral.com/Forums/Topic445276-143-1.aspxgreat question....I"m actually thinking of writing a follow up article on the CodeGen'd sprocs.so we basically have an xml file that lists all reports by name and it lists the parameters for the report, the select(s) for the report, the where clause, the sort and the group bywe then read this xml file and for each report node in the xml we run the report data through a code gen template using Nant.you should be able to read up on the Use of Nant and CodeGen Templates to get a start. I wish I could just send you our xml and our template file but that might get me in a little hot water here......please feel free to email me if you have more questions (my email is listed in my profile page).cheers,GAJMon, 21 Jan 2008 15:16:39 GMTGregoryAJackson