Hi
I have trouble with MSSQL2000 SP4 (without any hotfixes). During last two
weeks it start works anormally. After last optimalization (about few months
ago) it works good (fast, without blocks). Its buffer cache hit ratio was
about 99.7-99.8. Last day it starts work slow, there was many blocks and
dedlocks. There are no any queries, jobs and applications was added. Now
buffer cache hit ratio oscilate about 95-98. I try update statistics and
reindex some hard used tables, but there is no effect or effect is wery
short (after few hours problem return).
Mayby somene know what it could be?
Is it possible to estimate how each table (using DBCC SHOW_STATISTICS or
DBCC SHOWCONTIG or others) how the table affect on total buffer cache hit
ratio?
Marek
www.programowanieobiektowe.plChances are you are now scanning a table(s) where as before you were doing
seeks. You need to profile the queries to see which are the poorly
performing ones (ones with high duration and reads) and address why.
Andrew J. Kelly SQL MVP
"Marek Wierzbicki" <marek.wierzbickiiiii@.azymuttttt.pl> wrote in message
news:ed8rmq$1h4p$1@.news2.ipartners.pl...
> Hi
> I have trouble with MSSQL2000 SP4 (without any hotfixes). During last two
> weeks it start works anormally. After last optimalization (about few
> months ago) it works good (fast, without blocks). Its buffer cache hit
> ratio was about 99.7-99.8. Last day it starts work slow, there was many
> blocks and dedlocks. There are no any queries, jobs and applications was
> added. Now buffer cache hit ratio oscilate about 95-98. I try update
> statistics and reindex some hard used tables, but there is no effect or
> effect is wery short (after few hours problem return).
> Mayby somene know what it could be?
> Is it possible to estimate how each table (using DBCC SHOW_STATISTICS or
> DBCC SHOWCONTIG or others) how the table affect on total buffer cache hit
> ratio?
> Marek
> --
> www.programowanieobiektowe.pl|||> Chances are you are now scanning a table(s) where as before you were doing
> seeks. You need to profile the queries to see which are the poorly
> performing ones (ones with high duration and reads) and address why.
profiler didn't show any long time and expansive queries, but some simple
queries work about minute (not secounds like earlier)
Marek|||Then you need to find out why. Are there high disk queues? High CPU? Is
there blocking for those queries? You have to narrow it down so you can
start addressing the real issue. Maybe these will help:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Tempdb in 2005:
http://download.microsoft.com/downl...gWithTempDB.doc
Physical Database Storage:
http://download.microsoft.com/downl...
PhysDBStor.doc
Andrew J. Kelly SQL MVP
"Marek Wierzbicki" <marek.wierzbickiiiii@.azymuttttt.pl> wrote in message
news:edh8j6$4jj$1@.news2.ipartners.pl...
> profiler didn't show any long time and expansive queries, but some simple
> queries work about minute (not secounds like earlier)
>
> Marek
>|||> Then you need to find out why. Are there high disk queues? High CPU? Is
> there blocking for those queries? You have to narrow it down so you can
> start addressing the real issue. Maybe these will help:
I will try Your links. during hi trouble periods disk quees is over 50-60
Marek
Saturday, February 25, 2012
Buffer Cache Hit Ratio
Please advice.
I ran an OLTP Database on a SQL 2K on WIN 2K Advaced
Server in an Active/Passive Cluster configuration. I have
noticed the buffer cache hit ratio dropping down to about
1% sometimes below 1%. Waht can I do to ascertain the
Server needs more memory.
(i.e What memory is available to SQL ,what memory is SQL
requesting and what memory is given.)
I have also noticed the server running out of locks.
Please help urgent.Look at the SQL Server:Memory Manager:Target Server Memory(KB) counter in
Performance Monitor. That is the amount of memory SQL Server would like to
have. Also look at the SQL Server:Memory Manager:Target Server Memory(KB)
counter, which is the total memory SQL Server actually uses.
You can check in Enterprise Manager in the server properties if there are
any limits imposed on the amount of memeory SQL Server can use.
--
Jacco Schalkwijk
SQL Server MVP
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:07f901c3c47e$e30f25d0$a401280a@.phx.gbl...
> Please advice.
> I ran an OLTP Database on a SQL 2K on WIN 2K Advaced
> Server in an Active/Passive Cluster configuration. I have
> noticed the buffer cache hit ratio dropping down to about
> 1% sometimes below 1%. Waht can I do to ascertain the
> Server needs more memory.
> (i.e What memory is available to SQL ,what memory is SQL
> requesting and what memory is given.)
> I have also noticed the server running out of locks.
>
> Please help urgent.
I ran an OLTP Database on a SQL 2K on WIN 2K Advaced
Server in an Active/Passive Cluster configuration. I have
noticed the buffer cache hit ratio dropping down to about
1% sometimes below 1%. Waht can I do to ascertain the
Server needs more memory.
(i.e What memory is available to SQL ,what memory is SQL
requesting and what memory is given.)
I have also noticed the server running out of locks.
Please help urgent.Look at the SQL Server:Memory Manager:Target Server Memory(KB) counter in
Performance Monitor. That is the amount of memory SQL Server would like to
have. Also look at the SQL Server:Memory Manager:Target Server Memory(KB)
counter, which is the total memory SQL Server actually uses.
You can check in Enterprise Manager in the server properties if there are
any limits imposed on the amount of memeory SQL Server can use.
--
Jacco Schalkwijk
SQL Server MVP
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:07f901c3c47e$e30f25d0$a401280a@.phx.gbl...
> Please advice.
> I ran an OLTP Database on a SQL 2K on WIN 2K Advaced
> Server in an Active/Passive Cluster configuration. I have
> noticed the buffer cache hit ratio dropping down to about
> 1% sometimes below 1%. Waht can I do to ascertain the
> Server needs more memory.
> (i.e What memory is available to SQL ,what memory is SQL
> requesting and what memory is given.)
> I have also noticed the server running out of locks.
>
> Please help urgent.
Buffer Cache
hi,
How can we check buffer cache ratio of sql server..i don't want to go inside sql counters as i dont have direct access of sever..is it possible by some SP or command.
Regards
SunnyHi,
Execute the below command from Query Analyzer
DBCC PERFMON
Please refer BOL for more info in the above command.
Thanks
Hari
MCDBA
"Sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:ED4DADFD-C6E4-4D03-B2E4-6755EE1C5949@.microsoft.com...
> hi,
> How can we check buffer cache ratio of sql server..i don't want to go
inside sql counters as i dont have direct access of sever..is it possible by
some SP or command.
> Regards
> Sunny
How can we check buffer cache ratio of sql server..i don't want to go inside sql counters as i dont have direct access of sever..is it possible by some SP or command.
Regards
SunnyHi,
Execute the below command from Query Analyzer
DBCC PERFMON
Please refer BOL for more info in the above command.
Thanks
Hari
MCDBA
"Sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:ED4DADFD-C6E4-4D03-B2E4-6755EE1C5949@.microsoft.com...
> hi,
> How can we check buffer cache ratio of sql server..i don't want to go
inside sql counters as i dont have direct access of sever..is it possible by
some SP or command.
> Regards
> Sunny
Buffer Cache
hi,
How can we check buffer cache ratio of sql server..i don't want to go inside
sql counters as i dont have direct access of sever..is it possible by some
SP or command.
Regards
SunnyHi,
Execute the below command from Query Analyzer
DBCC PERFMON
Please refer BOL for more info in the above command.
Thanks
Hari
MCDBA
"Sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:ED4DADFD-C6E4-4D03-B2E4-6755EE1C5949@.microsoft.com...
> hi,
> How can we check buffer cache ratio of sql server..i don't want to go
inside sql counters as i dont have direct access of sever..is it possible by
some SP or command.
> Regards
> Sunny
How can we check buffer cache ratio of sql server..i don't want to go inside
sql counters as i dont have direct access of sever..is it possible by some
SP or command.
Regards
SunnyHi,
Execute the below command from Query Analyzer
DBCC PERFMON
Please refer BOL for more info in the above command.
Thanks
Hari
MCDBA
"Sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:ED4DADFD-C6E4-4D03-B2E4-6755EE1C5949@.microsoft.com...
> hi,
> How can we check buffer cache ratio of sql server..i don't want to go
inside sql counters as i dont have direct access of sever..is it possible by
some SP or command.
> Regards
> Sunny
Budget Clustering Standard Edition.
I am aware that the standard edition does not 'include'
fail over clustering.
I have read this newgroup on items containing the
budget / standard edition clustering.
I get the strong impression that 'budget clustering' is
strongly advised against.
WHY ?
Thanks for your attention,
ben brugman
Elaboration :
We want to have two options :
1. Standard edition.
2. Enterprise / San / Fail over.
Offcourse for high availability, you have to pay and
go for the second solution.
But to make the first solution as good as possible,
we are thinking in lines of the 'suggested' budget
fail over clustering.
So we plan :
Two servers with internal OS / SQL-server software.
Two Raid storage units which will be host based mirrored.
(So each file is stored four times).
Two locations, one for each server, storage unit.
If a part of the storage fails, there is plenty of redundancy.
But if a server fails we plan to do a fail over to the 'second'
machine, just reattaching the disks. (With MSA management
software).
We want the maximum amount of availability which can be
obtained with using the standard edition.
If that is not enough management can choose for the second
configuration, which will be more expensive.
We do not want to make the choice but supply the management
with enough 'numbers' and arguments to make a sollid choice on
the configuration.
1) Standard Edition is not supported
2) No Cluster resources will be created, thus adding tons of work during the
install
3) It is a violation of EULA
4) It will not failover properly
5) See number 1
Cheers,
Rod
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23kCvdN%23REHA.1340@.TK2MSFTNGP12.phx.gbl...
> I am aware that the standard edition does not 'include'
> fail over clustering.
> I have read this newgroup on items containing the
> budget / standard edition clustering.
> I get the strong impression that 'budget clustering' is
> strongly advised against.
> WHY ?
> Thanks for your attention,
> ben brugman
>
>
>
>
> Elaboration :
> We want to have two options :
> 1. Standard edition.
> 2. Enterprise / San / Fail over.
> Offcourse for high availability, you have to pay and
> go for the second solution.
> But to make the first solution as good as possible,
> we are thinking in lines of the 'suggested' budget
> fail over clustering.
> So we plan :
> Two servers with internal OS / SQL-server software.
> Two Raid storage units which will be host based mirrored.
> (So each file is stored four times).
> Two locations, one for each server, storage unit.
> If a part of the storage fails, there is plenty of redundancy.
> But if a server fails we plan to do a fail over to the 'second'
> machine, just reattaching the disks. (With MSA management
> software).
> We want the maximum amount of availability which can be
> obtained with using the standard edition.
> If that is not enough management can choose for the second
> configuration, which will be more expensive.
> We do not want to make the choice but supply the management
> with enough 'numbers' and arguments to make a sollid choice on
> the configuration.
>
>
|||Sorry could you explain what "budget clustering" is? Are you talking about 3rd party software?
Thanks
DaveK
http://www.sqlporn.co.uk
|||He is asking about a low-cost cluster. Using Standard Edition instead of
Enterprise.
Cheers,
Rod
"DaveK" <anonymous@.discussions.microsoft.com> wrote in message
news:31F9BABD-78FB-4174-B831-9E1B0C3D3093@.microsoft.com...
> Sorry could you explain what "budget clustering" is? Are you talking about
3rd party software?
> Thanks
> DaveK
> http://www.sqlporn.co.uk
|||First of all thanks for your time.
I would like to go over the points one at the time.
In my original mail I did (on purpose) elaborate, but
only at the end of the message.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:uonKad#REHA.3708@.TK2MSFTNGP10.phx.gbl...
> 1) Standard Edition is not supported
We do not intend to automatically fail over, but doing the fail over by
'hand',
in this group there are a lot of advices how to do this.
> 2) No Cluster resources will be created, thus adding tons of work during
the
> install
We need an extra machine and an extra installation of OS/SQL-server and
of course the setting up of the hardware and hardware management.
But I doubt if 'real' clustering has less work.
> 3) It is a violation of EULA
I do not see a violation of the EULA, with this set up.
(But then EULA's are so complex that allthough I do understand them,
this is not totaly 100 %).
> 4) It will not failover properly
I think by hand it wil. If this is not true please point out why.
> 5) See number 1
See number one.
As said we will do the switch manually, also switching the
application servers. (By newly resolving the name and reconnecting).
So the fail over is manually, but we do not need to restore and recover.
So there is less availability than for 'real' clustering, but more than
if a (backup) restore recover is done in case of a server failure.
Any new insights ?
ben brugman.
> Cheers,
> Rod
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:%23kCvdN%23REHA.1340@.TK2MSFTNGP12.phx.gbl...
>
|||"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:OskDg1#REHA.3660@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> He is asking about a low-cost cluster. Using Standard Edition instead of
> Enterprise.
> Cheers,
> Rod
> "DaveK" <anonymous@.discussions.microsoft.com> wrote in message
> news:31F9BABD-78FB-4174-B831-9E1B0C3D3093@.microsoft.com...
about
> 3rd party software?
>
I do not see the 'original message of DaveK, so I'll attach my anwser here.
By budget clustering, different people have different meanings.
What I did understand and mean by budget clustering is :
Using the standard edition SQL-server.
Setting up a second machine similar to the first machine.
If the first machine fails, attach the 'database' disks to the
second machine.
And start up SQL-server on the second machine.
Because this machine has a different name and ip address the
applications have to reconnect after a new name resolve.
(So this has to be changed as well).
Of course here we are missing a lot of the advantages of the
'real' clustering mechanism where 'everything' goed automatic.
(failure detection, switching over, name resolving and if the
application is cluster aware the reconnection).
But the advantage of this way creating high availability is that
you do not have to restore and recover a database in case of
a server failure.
So in my this 'might' give a higher availability then using the
backup and restore route.
Remarks :
Offcourse have you datastorage redundant. So that a storage failure
(disk or system) can be handled.
A server failure will be a rare event (using servers which have redundancy
in power supply, memory etc.). So during a server failure you might lose
some availability time.
But losing say an hour every two years because of a server failure, or
paying for the more expensive 'real fail over' capable system, is something
that has to be decided by the management.
Off course there will also be down time during regular upgrades of mainly
software and rearely hardware, but that can be planned.
Although we run a 7 x 24 hours shop, planned downtime is not to disruptive.
With my question, I hope to get some insight in how realistic this set up
is. I would like to form an opinion based on more than : "That won't work"
or "That is not supported" or "That is not allowed".
ben brugman
|||I think you want to look at creating a SQL Standby Server, which is
supported and very easy to do. Nothing to do with clustering, but neither
does your solution.
Cheers,
Rod
"ben brugman" <ben@.niethier.nl> wrote in message
news:c9k4ft$dt5$1@.reader10.wxs.nl...
> First of all thanks for your time.
> I would like to go over the points one at the time.
> In my original mail I did (on purpose) elaborate, but
> only at the end of the message.
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:uonKad#REHA.3708@.TK2MSFTNGP10.phx.gbl...
> We do not intend to automatically fail over, but doing the fail over by
> 'hand',
> in this group there are a lot of advices how to do this.
> the
> We need an extra machine and an extra installation of OS/SQL-server and
> of course the setting up of the hardware and hardware management.
> But I doubt if 'real' clustering has less work.
>
> I do not see a violation of the EULA, with this set up.
> (But then EULA's are so complex that allthough I do understand them,
> this is not totaly 100 %).
> I think by hand it wil. If this is not true please point out why.
>
> See number one.
> As said we will do the switch manually, also switching the
> application servers. (By newly resolving the name and reconnecting).
> So the fail over is manually, but we do not need to restore and recover.
> So there is less availability than for 'real' clustering, but more than
> if a (backup) restore recover is done in case of a server failure.
> Any new insights ?
> ben brugman.
>
>
|||Thanks,
ben
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:OIAIp1JSEHA.2936@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I think you want to look at creating a SQL Standby Server, which is
> supported and very easy to do. Nothing to do with clustering, but neither
> does your solution.
> Cheers,
> Rod
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:c9k4ft$dt5$1@.reader10.wxs.nl...
during
>
|||"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:OIAIp1JSEHA.2936@.TK2MSFTNGP10.phx.gbl...
> I think you want to look at creating a SQL Standby Server, which is
> supported and very easy to do. Nothing to do with clustering, but neither
> does your solution.
Did look up standby servers, but this is not what I meant.
In my mail I described that we didn't want to use the
backup / restore sequence, but just the switching of the
disks.
I have seen this mentioned as "budget clustering", but could
be wrong there. Sorry for that misunderstanding. Haven't got
a name / term for it.
ben
[vbcol=seagreen]
> Cheers,
> Rod
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:c9k4ft$dt5$1@.reader10.wxs.nl...
during
>
|||FYI, you can do a fully functional "budget cluster" using standard edition
of SQL/Windows using Legato's clustering product. Of course, it ain't free,
so that might blow your budget :-)
Regards,
John
"ben brugman" <ben@.niethier.nl> wrote in message
news:c9k58t$e84$1@.reader10.wxs.nl...
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:OskDg1#REHA.3660@.tk2msftngp13.phx.gbl...
> about
> I do not see the 'original message of DaveK, so I'll attach my anwser
here.
> By budget clustering, different people have different meanings.
> What I did understand and mean by budget clustering is :
> Using the standard edition SQL-server.
> Setting up a second machine similar to the first machine.
> If the first machine fails, attach the 'database' disks to the
> second machine.
> And start up SQL-server on the second machine.
> Because this machine has a different name and ip address the
> applications have to reconnect after a new name resolve.
> (So this has to be changed as well).
> Of course here we are missing a lot of the advantages of the
> 'real' clustering mechanism where 'everything' goed automatic.
> (failure detection, switching over, name resolving and if the
> application is cluster aware the reconnection).
> But the advantage of this way creating high availability is that
> you do not have to restore and recover a database in case of
> a server failure.
> So in my this 'might' give a higher availability then using the
> backup and restore route.
> Remarks :
> Offcourse have you datastorage redundant. So that a storage failure
> (disk or system) can be handled.
> A server failure will be a rare event (using servers which have redundancy
> in power supply, memory etc.). So during a server failure you might lose
> some availability time.
> But losing say an hour every two years because of a server failure, or
> paying for the more expensive 'real fail over' capable system, is
something
> that has to be decided by the management.
> Off course there will also be down time during regular upgrades of mainly
> software and rearely hardware, but that can be planned.
> Although we run a 7 x 24 hours shop, planned downtime is not to
disruptive.
> With my question, I hope to get some insight in how realistic this set up
> is. I would like to form an opinion based on more than : "That won't work"
> or "That is not supported" or "That is not allowed".
> ben brugman
>
fail over clustering.
I have read this newgroup on items containing the
budget / standard edition clustering.
I get the strong impression that 'budget clustering' is
strongly advised against.
WHY ?
Thanks for your attention,
ben brugman
Elaboration :
We want to have two options :
1. Standard edition.
2. Enterprise / San / Fail over.
Offcourse for high availability, you have to pay and
go for the second solution.
But to make the first solution as good as possible,
we are thinking in lines of the 'suggested' budget
fail over clustering.
So we plan :
Two servers with internal OS / SQL-server software.
Two Raid storage units which will be host based mirrored.
(So each file is stored four times).
Two locations, one for each server, storage unit.
If a part of the storage fails, there is plenty of redundancy.
But if a server fails we plan to do a fail over to the 'second'
machine, just reattaching the disks. (With MSA management
software).
We want the maximum amount of availability which can be
obtained with using the standard edition.
If that is not enough management can choose for the second
configuration, which will be more expensive.
We do not want to make the choice but supply the management
with enough 'numbers' and arguments to make a sollid choice on
the configuration.
1) Standard Edition is not supported
2) No Cluster resources will be created, thus adding tons of work during the
install
3) It is a violation of EULA
4) It will not failover properly
5) See number 1
Cheers,
Rod
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23kCvdN%23REHA.1340@.TK2MSFTNGP12.phx.gbl...
> I am aware that the standard edition does not 'include'
> fail over clustering.
> I have read this newgroup on items containing the
> budget / standard edition clustering.
> I get the strong impression that 'budget clustering' is
> strongly advised against.
> WHY ?
> Thanks for your attention,
> ben brugman
>
>
>
>
> Elaboration :
> We want to have two options :
> 1. Standard edition.
> 2. Enterprise / San / Fail over.
> Offcourse for high availability, you have to pay and
> go for the second solution.
> But to make the first solution as good as possible,
> we are thinking in lines of the 'suggested' budget
> fail over clustering.
> So we plan :
> Two servers with internal OS / SQL-server software.
> Two Raid storage units which will be host based mirrored.
> (So each file is stored four times).
> Two locations, one for each server, storage unit.
> If a part of the storage fails, there is plenty of redundancy.
> But if a server fails we plan to do a fail over to the 'second'
> machine, just reattaching the disks. (With MSA management
> software).
> We want the maximum amount of availability which can be
> obtained with using the standard edition.
> If that is not enough management can choose for the second
> configuration, which will be more expensive.
> We do not want to make the choice but supply the management
> with enough 'numbers' and arguments to make a sollid choice on
> the configuration.
>
>
|||Sorry could you explain what "budget clustering" is? Are you talking about 3rd party software?
Thanks
DaveK
http://www.sqlporn.co.uk
|||He is asking about a low-cost cluster. Using Standard Edition instead of
Enterprise.
Cheers,
Rod
"DaveK" <anonymous@.discussions.microsoft.com> wrote in message
news:31F9BABD-78FB-4174-B831-9E1B0C3D3093@.microsoft.com...
> Sorry could you explain what "budget clustering" is? Are you talking about
3rd party software?
> Thanks
> DaveK
> http://www.sqlporn.co.uk
|||First of all thanks for your time.
I would like to go over the points one at the time.
In my original mail I did (on purpose) elaborate, but
only at the end of the message.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:uonKad#REHA.3708@.TK2MSFTNGP10.phx.gbl...
> 1) Standard Edition is not supported
We do not intend to automatically fail over, but doing the fail over by
'hand',
in this group there are a lot of advices how to do this.
> 2) No Cluster resources will be created, thus adding tons of work during
the
> install
We need an extra machine and an extra installation of OS/SQL-server and
of course the setting up of the hardware and hardware management.
But I doubt if 'real' clustering has less work.
> 3) It is a violation of EULA
I do not see a violation of the EULA, with this set up.
(But then EULA's are so complex that allthough I do understand them,
this is not totaly 100 %).
> 4) It will not failover properly
I think by hand it wil. If this is not true please point out why.
> 5) See number 1
See number one.
As said we will do the switch manually, also switching the
application servers. (By newly resolving the name and reconnecting).
So the fail over is manually, but we do not need to restore and recover.
So there is less availability than for 'real' clustering, but more than
if a (backup) restore recover is done in case of a server failure.
Any new insights ?
ben brugman.
> Cheers,
> Rod
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:%23kCvdN%23REHA.1340@.TK2MSFTNGP12.phx.gbl...
>
|||"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:OskDg1#REHA.3660@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> He is asking about a low-cost cluster. Using Standard Edition instead of
> Enterprise.
> Cheers,
> Rod
> "DaveK" <anonymous@.discussions.microsoft.com> wrote in message
> news:31F9BABD-78FB-4174-B831-9E1B0C3D3093@.microsoft.com...
about
> 3rd party software?
>
I do not see the 'original message of DaveK, so I'll attach my anwser here.
By budget clustering, different people have different meanings.
What I did understand and mean by budget clustering is :
Using the standard edition SQL-server.
Setting up a second machine similar to the first machine.
If the first machine fails, attach the 'database' disks to the
second machine.
And start up SQL-server on the second machine.
Because this machine has a different name and ip address the
applications have to reconnect after a new name resolve.
(So this has to be changed as well).
Of course here we are missing a lot of the advantages of the
'real' clustering mechanism where 'everything' goed automatic.
(failure detection, switching over, name resolving and if the
application is cluster aware the reconnection).
But the advantage of this way creating high availability is that
you do not have to restore and recover a database in case of
a server failure.
So in my this 'might' give a higher availability then using the
backup and restore route.
Remarks :
Offcourse have you datastorage redundant. So that a storage failure
(disk or system) can be handled.
A server failure will be a rare event (using servers which have redundancy
in power supply, memory etc.). So during a server failure you might lose
some availability time.
But losing say an hour every two years because of a server failure, or
paying for the more expensive 'real fail over' capable system, is something
that has to be decided by the management.
Off course there will also be down time during regular upgrades of mainly
software and rearely hardware, but that can be planned.
Although we run a 7 x 24 hours shop, planned downtime is not to disruptive.
With my question, I hope to get some insight in how realistic this set up
is. I would like to form an opinion based on more than : "That won't work"
or "That is not supported" or "That is not allowed".
ben brugman
|||I think you want to look at creating a SQL Standby Server, which is
supported and very easy to do. Nothing to do with clustering, but neither
does your solution.
Cheers,
Rod
"ben brugman" <ben@.niethier.nl> wrote in message
news:c9k4ft$dt5$1@.reader10.wxs.nl...
> First of all thanks for your time.
> I would like to go over the points one at the time.
> In my original mail I did (on purpose) elaborate, but
> only at the end of the message.
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:uonKad#REHA.3708@.TK2MSFTNGP10.phx.gbl...
> We do not intend to automatically fail over, but doing the fail over by
> 'hand',
> in this group there are a lot of advices how to do this.
> the
> We need an extra machine and an extra installation of OS/SQL-server and
> of course the setting up of the hardware and hardware management.
> But I doubt if 'real' clustering has less work.
>
> I do not see a violation of the EULA, with this set up.
> (But then EULA's are so complex that allthough I do understand them,
> this is not totaly 100 %).
> I think by hand it wil. If this is not true please point out why.
>
> See number one.
> As said we will do the switch manually, also switching the
> application servers. (By newly resolving the name and reconnecting).
> So the fail over is manually, but we do not need to restore and recover.
> So there is less availability than for 'real' clustering, but more than
> if a (backup) restore recover is done in case of a server failure.
> Any new insights ?
> ben brugman.
>
>
|||Thanks,
ben
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:OIAIp1JSEHA.2936@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I think you want to look at creating a SQL Standby Server, which is
> supported and very easy to do. Nothing to do with clustering, but neither
> does your solution.
> Cheers,
> Rod
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:c9k4ft$dt5$1@.reader10.wxs.nl...
during
>
|||"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:OIAIp1JSEHA.2936@.TK2MSFTNGP10.phx.gbl...
> I think you want to look at creating a SQL Standby Server, which is
> supported and very easy to do. Nothing to do with clustering, but neither
> does your solution.
Did look up standby servers, but this is not what I meant.
In my mail I described that we didn't want to use the
backup / restore sequence, but just the switching of the
disks.
I have seen this mentioned as "budget clustering", but could
be wrong there. Sorry for that misunderstanding. Haven't got
a name / term for it.
ben
[vbcol=seagreen]
> Cheers,
> Rod
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:c9k4ft$dt5$1@.reader10.wxs.nl...
during
>
|||FYI, you can do a fully functional "budget cluster" using standard edition
of SQL/Windows using Legato's clustering product. Of course, it ain't free,
so that might blow your budget :-)
Regards,
John
"ben brugman" <ben@.niethier.nl> wrote in message
news:c9k58t$e84$1@.reader10.wxs.nl...
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:OskDg1#REHA.3660@.tk2msftngp13.phx.gbl...
> about
> I do not see the 'original message of DaveK, so I'll attach my anwser
here.
> By budget clustering, different people have different meanings.
> What I did understand and mean by budget clustering is :
> Using the standard edition SQL-server.
> Setting up a second machine similar to the first machine.
> If the first machine fails, attach the 'database' disks to the
> second machine.
> And start up SQL-server on the second machine.
> Because this machine has a different name and ip address the
> applications have to reconnect after a new name resolve.
> (So this has to be changed as well).
> Of course here we are missing a lot of the advantages of the
> 'real' clustering mechanism where 'everything' goed automatic.
> (failure detection, switching over, name resolving and if the
> application is cluster aware the reconnection).
> But the advantage of this way creating high availability is that
> you do not have to restore and recover a database in case of
> a server failure.
> So in my this 'might' give a higher availability then using the
> backup and restore route.
> Remarks :
> Offcourse have you datastorage redundant. So that a storage failure
> (disk or system) can be handled.
> A server failure will be a rare event (using servers which have redundancy
> in power supply, memory etc.). So during a server failure you might lose
> some availability time.
> But losing say an hour every two years because of a server failure, or
> paying for the more expensive 'real fail over' capable system, is
something
> that has to be decided by the management.
> Off course there will also be down time during regular upgrades of mainly
> software and rearely hardware, but that can be planned.
> Although we run a 7 x 24 hours shop, planned downtime is not to
disruptive.
> With my question, I hope to get some insight in how realistic this set up
> is. I would like to form an opinion based on more than : "That won't work"
> or "That is not supported" or "That is not allowed".
> ben brugman
>
buckets or quartile
Hi all,
Dreaded moment has come:)
I need to produce a report against MSAS 2005 with quartiles buckets, meaning that besides actual value of the measure, it needs to display following:
Value 1st 2nd 3rd 4th
X Min - Max Min - Max Min - Max Min - Max
-
80 85 - 95 75 - 85 55 - 75 12 - 55
where 1st is range of values of top 25% performing entities and so forth. It's supposed to be sliceable by any hierarchy in the cube.
I tried to use TopPercent 25%, but never could get it work in calculated member.
Any idea, comment, reference highly appreciated.
Thanks
An alternative is to create a "band dimension" and set the bandID in the fact table prior to loading. This will give you greater control if you are planning to use this type of banding in many queries.
eg. Band dimension structure
BandId StartBand EndBand Description
1 -99999 11 Less than 12
2 12 55 12 to 55
...
n 85 95 85 to 95
n+1 96 9999999 greater than 95
Can you use the Descritization properties on the attribute? Set it for 4 buckets, and change the label properties to have it display what you need.
-Kory
|||Let's clarify the problem a bit.
Suppose Value is Sales, which aggregates as Sum. Suppose for tuple (Geography.USA.Redmond, Time.Year.2005) the value of Sales is 80. How the value of 1st would be calculated? It is stated as "top 25% performing entities". What is "entity" and how the "performance" is calculated?
Suppose Geography dimension has the key attribute - Employee, which is the granularity attribute. Are employees entities?
Suppose in 2005 the employees of Redmond showed sales as: Emp1=10, Emp2=20, Emp3=30, Emp4=20. Total is 80.
How do we calculate 1st value?
|||Extendong your example with employees in particular geography top 25% performing employees (Emp1=10, Emp2=20, Emp3=30, Emp4=26, Emp5=15, Emp6 = 22, Emp7=35, Emp8=14) will be Emp3 & Emp7. The report will look like this:
Redmond 30 37
22 26
15 20
10 14
Employees are entities and performance is highest value of the sale in particular geography.
Thank you.
|||is it some kind of Add-on for AS? How it can be installed and used?
Thank you
|||It is kind of strange that with your last example the numbers for the employees you provided did not sum to 80, like you showed with your very first example. Along with the 1stValue - 85-95 being more than Value - 80 in the first example. I am thinking about the additional rules not yet explained.
Let's move up in the Geography hierarchy a bit. Consider tuple (Geography.USA, Time.Year.2005) (let's not have states, since in my first example i showed Geography.USA.Redmond). Will you need calculation of those 4 values for this tuple also? Will it be something like scanning aggregated sales in cities, bucketizing them and reporting?
What i am trying to get clarified is - do you need just one static report (is "report" an MDX query or report for MS Reporting Services?), where you have predefined 2 levels of the same hierarchy, one measure and for each member of the first hierarchy we calculate buckets from the values of the measure when the member is drilled down to the second level?
|||
the measure is not additive.
The report will calculate quartiles for the cities only.
For some reason, TopPercent did not work properly, so I could not create calculated members or named set. So far I have calculated quartile values for each city and store them in a cube as member properties.
We are using ReportPortal on SPS, because there are many other hierarchies, which users might want filter or multi-select, so dynamic reporting is preferred.
I would like to thank you all for help and suggestions. Strange, but TopPercent still does not work. May be MSFT folks can shed some light on its usage.
Thank you very much again
|||You can use it in SSAS if you have Excel installed on the server.Bubble Chart
I would like to show static values on the Y-Axis of a bubble chart, say High,
Medium and Low. When I select the descriptive text in the dataset the
bubbles are not plotted correctly whilst if I retrieve the numeric
equivalent the bubbles are plotted the right way, but the Y-axis will show
numbers and not text.
Any help would be greatly appreciated.I too would like to do this and have not found any help so far. Has anyone
been successful in this? Or does anyone know of good documentation and
specific examples for the bubble chart?
"Adrian Theuma" wrote:
> I would like to show static values on the Y-Axis of a bubble chart, say High,
> Medium and Low. When I select the descriptive text in the dataset the
> bubbles are not plotted correctly whilst if I retrieve the numeric
> equivalent the bubbles are plotted the right way, but the Y-axis will show
> numbers and not text.
> Any help would be greatly appreciated.
>
Medium and Low. When I select the descriptive text in the dataset the
bubbles are not plotted correctly whilst if I retrieve the numeric
equivalent the bubbles are plotted the right way, but the Y-axis will show
numbers and not text.
Any help would be greatly appreciated.I too would like to do this and have not found any help so far. Has anyone
been successful in this? Or does anyone know of good documentation and
specific examples for the bubble chart?
"Adrian Theuma" wrote:
> I would like to show static values on the Y-Axis of a bubble chart, say High,
> Medium and Low. When I select the descriptive text in the dataset the
> bubbles are not plotted correctly whilst if I retrieve the numeric
> equivalent the bubbles are plotted the right way, but the Y-axis will show
> numbers and not text.
> Any help would be greatly appreciated.
>
Subscribe to:
Posts (Atom)