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 «««23456»»»

A Reporting System Architecture Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2008 11:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
Yeah 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.


Gregory A Jackson MBA, CSM
Post #446568
Posted Wednesday, January 23, 2008 2:27 PM


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: Saturday, August 25, 2012 9:48 PM
Points: 777, Visits: 134
hi,
its soo helpful for me. so simple . really appreaciatable.

thx
sreejith
MCAD
Post #446646
Posted Wednesday, January 23, 2008 2:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
Sreejith,

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....

:)

GAJ


Gregory A Jackson MBA, CSM
Post #446651
Posted Wednesday, January 23, 2008 2:39 PM


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: Saturday, August 25, 2012 9:48 PM
Points: 777, Visits: 134
hi GAJ ,
sure. am eagerly waiting for ur next post.

thx
sreejith
MCAD
Post #446658
Posted Wednesday, January 23, 2008 6:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
It 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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #446711
Posted Wednesday, January 23, 2008 6:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 1:07 PM
Points: 59, Visits: 115
What 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,
Eric
Post #446712
Posted Wednesday, January 23, 2008 7:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
Good point.

that was another option we pursued but it did require down time (albeit short).



GAJ


Gregory A Jackson MBA, CSM
Post #446717
Posted Wednesday, January 23, 2008 7:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
well....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.


Gregory A Jackson MBA, CSM
Post #446720
Posted Thursday, February 28, 2008 1:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 15, 2009 7:09 AM
Points: 3, Visits: 45
We 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.
Post #461973
Posted Thursday, September 4, 2008 6:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:58 PM
Points: 16, Visits: 81
Gregory, how did you create your staging tables without impacting your OLTP?
Post #563744
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse