July 11, 2008 at 4:56 am
A and B are 2 Physical Servers having both Sql Server 2005 instances clustered(active/active).
Can I have the same database, say db_cluster , available on both nodes (A and B) of cluster??such that oltp run on B and reports run on A on the same database.???i.e. db_cluster would be read-only on A.
Is it feasible or rather achievable through clustering???
July 11, 2008 at 5:16 am
Hi,
Clustering is one of the high availability solution. It is not meant for reports. If you want to generate reports on another server try other methods like transactional replication etc..
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 11, 2008 at 5:21 am
Thats good.
But can i have above scenario in conjunction with clustering??
July 11, 2008 at 10:03 am
In principle, you can. You just need to install your database on the nodes. But they will affect each other since they are on the same cluster.
July 11, 2008 at 10:21 am
How current do the reports need to be? Database mirroring and snapshots (if you have enterprise) can help here. Or replication to a second server.
July 11, 2008 at 12:48 pm
Steve Jones - Editor (7/11/2008)
How current do the reports need to be? Database mirroring and snapshots (if you have enterprise) can help here. Or replication to a second server.
Since this was published in the SQL Server 7,2000 forum, I am going to assume the OP is not on SQL Server 2005 yet. Database mirroring and snapshots are only available on SQL Server 2005 and up (Enterprise Edition only for Snapshots - I am fairly certain you can mirror from standard).
So, the other options are log shipping and replication. Both options can get you near real-time reporting availability.
Other options, regardless of version of SQL Server would be SAN snapshots. Review what is available to you from your SAN vendor. I can give you two examples:
Netapp
We have a database where we use SnapManager to create a snap-clone and present the cloned database to a secondary node. This process is run every couple of hours and does not require any downtime on the primary node.
HP EVA:
We have a very large datawarehouse database that is cloned and presented to a secondary node. This process requires the database to be shut down on the primary node to create the snap clone because the EVA tools are not integrated into SQL Server.
Just a couple of ideas...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2008 at 10:04 pm
Hey thanks Jeffery
Actually in my case reports should be online i.e. no time lag.
So can in that scenario the above methods work???
July 14, 2008 at 3:25 pm
No, sorry - those options will not get you real time (or even near real time). The closest you are going to get to real-time is transactional replication.
I have not set that up before and really don't know all that is needed, so I can't really help with getting it set up. If you run into problems, I am sure someone here can help you out.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2008 at 10:56 pm
Ohkay.......
Transactional replication will obviously the last or may be mandatory option left.
Anywys, nw plan is something like....
A and B are clustered, seperate machines, active/active.
Db A -->replication to Db B(on server B).
OLTP on A and Reports on B.
Whenever failover occurs, both databases goes to the active node...
I suppose its more realistic , isn't it????
Waiting for responses.......
July 15, 2008 at 9:22 am
pardeep (7/14/2008)
Ohkay.......Transactional replication will obviously the last or may be mandatory option left.
Anywys, nw plan is something like....
A and B are clustered, seperate machines, active/active.
Db A -->replication to Db B(on server B).
OLTP on A and Reports on B.
Whenever failover occurs, both databases goes to the active node...
I suppose its more realistic , isn't it????
Waiting for responses.......
That will work, as long as you have enough resources available to run both instances on a single node. However, you still have to be able to get that second instance for reporting - which is going to use one of the outlined technologies above.
Are you really sure you need real-time reporting for all data in the database? Can you perform the required real time reports from the live system without impacting the production system? What I mean by this is - if these real time reports are properly built to only look at recent data they should perform well with little or no blocking.
If so, that would allow you the flexibility of providing a lag reporting system where you can then look to the other possibilities above. Ideally, you would want to upgrade your system to 2005/2008 and implement database mirroring and snapshots - at least, that is what I would do.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 12:35 pm
Isn't Active/Active clustering not available in Windows2003 ? (Not sure about 2008).
Also, I seem to recall db mirroring won't help, as the mirror db is "in recovery" and inaccessible until fail-over.
I think you're left with log shipping.
Honor Super Omnia-
Jason Miller
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply