January 23, 2008 at 11:38 am
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
January 23, 2008 at 2:27 pm
hi,
its soo helpful for me. so simple . really appreaciatable.
thx
sreejith
MCAD
January 23, 2008 at 2:30 pm
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
January 23, 2008 at 2:39 pm
hi GAJ ,
sure. am eagerly waiting for ur next post.
thx
sreejith
MCAD
January 23, 2008 at 6:35 pm
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
Change is inevitable... Change for the better is not.
January 23, 2008 at 6:39 pm
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
January 23, 2008 at 7:08 pm
Good point.
that was another option we pursued but it did require down time (albeit short).
GAJ
Gregory A Jackson MBA, CSM
January 23, 2008 at 7:11 pm
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
February 28, 2008 at 1:23 pm
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.
September 4, 2008 at 6:42 am
Gregory, how did you create your staging tables without impacting your OLTP?
September 4, 2008 at 7:21 am
good 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.
GAJ
Gregory A Jackson MBA, CSM
January 29, 2009 at 12:21 pm
On 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?
January 29, 2009 at 12:23 pm
Hadn't considered it but it certainly is a good idea
Gregory A Jackson MBA, CSM
August 7, 2009 at 8:09 am
We'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! 🙂
August 7, 2009 at 10:01 am
Thanks Rowland,
I agree.
I'd love to hear more discussion on this topic.
Never an easy one to handle....
cheers,
Greg J
Gregory A Jackson MBA, CSM
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply