SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Reporting System Architecture


A Reporting System Architecture

Author
Message
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 506
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
Sreejithsql
Sreejithsql
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 134
hi,
its soo helpful for me. so simple . really appreaciatable.

thx
sreejith
MCAD
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 506
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....

Smile

GAJ

Gregory A Jackson MBA, CSM
Sreejithsql
Sreejithsql
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 134
hi GAJ ,
sure. am eagerly waiting for ur next post.

thx
sreejith
MCAD
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88090 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric Stimpson
Eric Stimpson
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 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
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 506
Good point.

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



GAJ

Gregory A Jackson MBA, CSM
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 506
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
Terry Sharp
Terry Sharp
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
steitelbaum
steitelbaum
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 81
Gregory, how did you create your staging tables without impacting your OLTP?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search