Clustering OR Shipping Log

What's the disadvantages of failover clustering over log shipping ?

Approximately how much it cost to set up the failover clustering system ?

We are planning use the log shipping to set up a standby server, but it required some down time and manual recovery, my boss asking if we can use failover clustering instead, so I have above questions in mind,

your help will be highly appreciated,
thanks a lot in advanceTo implement cluster (active-active) you have to have:
2 identical server from ms certified provider (to get support)
1 shared storage (2 servers are connected to 1 storage)
Enterprise edition of software(2X for windows and 2X for SQLserver)

Drawbacks for log shipping are:
1. You have to reconfigure clients in case of failure to hotstandby server
2. Not allways able to get latest tranactions
3. Hard to implement automatic failure detection and switching to safe host

Drawbacks for clustering:
expensive

HTH,
OBRP

Clustering on SQL Standard Edition?

I guess it is not possible. We can't afford the cost and at the same time downtime, I want to know if it is possible to simulate clustering using SQL Standard edition with minimal downtime. E.g., if I am running Windows 2000 Advanced Server in a cluster a
nd install SQL Server 1 on box1 and point to the data on the disk array and keep it running. When SQL server 1 crashes or stops functioning, can I start SQL Server 2 (on a diff box) and attach the databases on the disk array will it function smoothly. I g
uess it is a stupid question (it doesn't makes sense to me either), but if you have some insights on this please post them.
If you can count the cost of downtime then, depending on these costs to business, you may be able to justify to management the cost of moving to W2K Advanced Server or W2K3 Enterprise Edition and SQL2K Enterprise Edition. Introducing manual processes to
simulate High Availaible is a mistake. But you already knew that ;-)
|||Clustering is an Enterprise Edition feature only.
However, what you outline for a failover scenario will work exactly as you
expect it will. As long as the second SQL Server can gain access to the
data files, the SQL Server will start up cleanly with full access to your
data. The only thing you would have to manage is that your applications
have to be repointed to the new server.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I can completely understand the cost factor of implementing the cluster.
But the bigger question that needs to be asked is - does the downtime and
loss of productivity justify the savings or a workaround for NOT
implementing high availability clustering ? I guess, the mere fact that
your company is looking at clustering is that they value the high
availability aspect of running a database application and they value the
money lost in terms of lost productivity. From now onwards, it would be a
question of justifying the cost-benefit scenario to your BDMs.
If you try to manually point the database to the shared disk, its possible,
but why would you like to do that? There will be a lot of contingencies to
consider and plan for if we go the manual way.
I would sugges that you have a look at this link
MS SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
This is written for both Business and Technical folks and could give you
possible pointers on how you can achieve the functionality by justifying
the costs.
Hope this helps.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Clustered Index Update

In my estimated execution plan for a UPDATE it says I have
a 55% cost to do a "Clustered Index Update/Update". What
is odd is that I am not updating either column in the
PK/Clustered Index. Now I know this is the estimated
execution plan, but why does it say this? The real truth
will be told when I run the update statement, but I'm just
wondering about this mis-read of the execution.Can you post the update? Sounds interesting. Also, can you post the pre-run
plan and the post run plan?
Remember that any update to any column in the table requires an update to
the clustered index, since all columns are part of the index.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in message
news:126401c54102$af790090$a601280a@.phx.gbl...
> In my estimated execution plan for a UPDATE it says I have
> a 55% cost to do a "Clustered Index Update/Update". What
> is odd is that I am not updating either column in the
> PK/Clustered Index. Now I know this is the estimated
> execution plan, but why does it say this? The real truth
> will be told when I run the update statement, but I'm just
> wondering about this mis-read of the execution.|||There is not data presently so the statistics reflect
that, perhaps that could be the issue. But as you asked,
here is the resultset of SET SHOWPLAN_ALL. I exported it
to excel and then saved as CSV. You will have to import
and set the delimiter to a comma.
"UPDATE a SET IncntvRevWAncil =
b.totalQualRevOrg , IncntvRevWOAncil =
b.totalQualRevNew FROM dbo.CustomerProfileMonthly
a JOIN DB2.dbo.t_Detail b ON
a.CustNumber = b.CustNumber AND
a.ControlingDate = b.ControlingDate AND
a.ControlingDate = CAST('20040701' AS
DATETIME)" ,5,1,0,NULL,NULL,1,NULL,1,NULL,NULL,NULL
,3.81E-
02,NULL,NULL,UPDATE,0,NULL
" |--Clustered Index Update(OBJECT:([DB1].[dbo].
[CustomerProfileMonthly].[MerchantProfileMonthly_PK]), SET:
([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
[CustomerProfileMonthly].[IncntvRevWAncil]=
[Expr2860]))",5,2,1,Clustered Index Update,Update,"OBJECT:
([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK]), SET:
([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
[CustomerProfileMonthly].[IncntvRevWAncil]=
[Expr2860])",NULL,1,1.68E-02,0.000001,61,3.81E-
02,NULL,NULL,PLAN_ROW,0,1
" |--Compute Scalar(DEFINE:([Expr2860]=Convert
([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
([t_detail_2004_07].[totalQualRevNew])))",5,3,2,Compute
Scalar,Compute Scalar,"DEFINE:([Expr2860]=Convert
([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
([t_detail_2004_07].[totalQualRevNew]))","[Expr2860]
=Convert([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]
=Convert([t_detail_2004_07].
[totalQualRevNew])",1,0,0.0000001,53,2.13E-02,"[Bmk1000],
[Expr2860], [Expr2861]",NULL,PLAN_ROW,0,1
|--Top(ROWCOUNT est
0),5,4,3,Top,Top,NULL,NULL,1,0,0.0000001,53,2.13E-
02,"[Bmk1000], [t_detail_2004_07].[totalQualRevOrg],
[t_detail_2004_07].[totalQualRevNew]",NULL,PLAN_ROW,0,1
|--Sort(DISTINCT ORDER BY:([Bmk1000]
ASC)),5,5,4,Sort,Distinct Sort,DISTINCT ORDER BY:
([Bmk1000] ASC),NULL,1,1.13E-02,1.00E-
04,53,0.02130264,"[Bmk1000], [t_detail_2004_07].
[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]",NULL,PLAN_ROW,0,1
" |--Compute Scalar(DEFINE:
([t_detail_2004_07].[totalQualRevOrg]=[t_detail_2004_07].
[totalQualRevOrg], [t_detail_2004_07].[totalQualRevNew]=
[t_detail_2004_07].[totalQualRevNew]))",5,6,5,Compute
Scalar,Compute Scalar,"DEFINE:([t_detail_2004_07].
[totalQualRevOrg]=[t_detail_2004_07].[totalQualRevOrg],
[t_detail_2004_07].[totalQualRevNew]=[t_detail_2004_07].
[totalQualRevNew])","[t_detail_2004_07].[totalQualRevOrg]=
[t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]=[t_detail_2004_07].
[totalQualRevNew]",1,0,0.0000001,53,9.94E-03,"[Bmk1000],
[t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]",NULL,PLAN_ROW,0,1
" |--Nested Loops(Inner Join,
OUTER REFERENCES:([a].[CustNumber]))",5,7,6,Nested
Loops,Inner Join,OUTER REFERENCES:([a].
[CustNumber]),NULL,1,0,0.00001254,554,9.94E-03,"[Bmk1000],
[t_detail_2004_07].[totalQualRevNew], [t_detail_2004_07].
[totalQualRevOrg]",NULL,PLAN_ROW,0,1
" |--Clustered Index S
(OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
[ControlingDate]='Jul 1 2004 12:00AM') ORDERED
FORWARD)",5,8,7,Clustered Index S,Clustered Index
S,"OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
[ControlingDate]='Jul 1 2004 12:00AM') ORDERED
FORWARD","[Bmk1000], [a].[CustNumber]",1,3.20E-03,7.96E-
05,107,3.28E-03,"[Bmk1000], [a].
[CustNumber]",NULL,PLAN_ROW,0,1
" |--Clustered Index S
(OBJECT:([DB2].[dbo].[t_detail_2004_07].
[PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
[CustNumber]=[a].[CustNumber]) ORDERED
FORWARD)",5,9,7,Clustered Index S,Clustered Index
S,"OBJECT:([DB2].[dbo].[t_detail_2004_07].
[PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
[CustNumber]=[a].[CustNumber]) ORDERED
FORWARD","[t_detail_2004_07].[totalQualRevNew],
[t_detail_2004_07].[totalQualRevOrg]",1,3.20E-03,7.96E-
05,456,6.65E-03,"[t_detail_2004_07].[totalQualRevNew],
[t_detail_2004_07].[totalQualRevOrg]",NULL,PLAN_ROW,0,3
,,,,,,,,,,,,,,,,,

>--Original Message--
>Can you post the update? Sounds interesting. Also, can
you post the pre-run
>plan and the post run plan?
>Remember that any update to any column in the table
requires an update to
>the clustered index, since all columns are part of the
index.
>--
>----
--
>Louis Davidson - drsql@.hotmail.com
>SQL Server MVP
>Compass Technology Management - www.compass.net
>Pro SQL Server 2000 Database Design -
>http://www.apress.com/book/bookDisplay.html?bID=266
>Blog - http://spaces.msn.com/members/drsql/
>Note: Please reply to the newsgroups only unless you are
interested in
>consulting services. All other replies may be ignored :)
>"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:126401c54102$af790090$a601280a@.phx.gbl...
have
What
truth
just
>
>.
>|||I would guess that might be the thing. Since there is no data, there is
very little cost to do the other stuff, but I would hold off worry about
optimzing until you have data :) Seriously, as long as you are careful to
realize that your join criteria must be a 1-1 relationship between table A
and table B, it is probably fine.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3e01c5411d$25d96b70$a401280a@.phx.gbl...
> There is not data presently so the statistics reflect
> that, perhaps that could be the issue. But as you asked,
> here is the resultset of SET SHOWPLAN_ALL. I exported it
> to excel and then saved as CSV. You will have to import
> and set the delimiter to a comma.
>
> "UPDATE a SET IncntvRevWAncil =
> b.totalQualRevOrg , IncntvRevWOAncil =
> b.totalQualRevNew FROM dbo.CustomerProfileMonthly
> a JOIN DB2.dbo.t_Detail b ON
> a.CustNumber = b.CustNumber AND
> a.ControlingDate = b.ControlingDate AND
> a.ControlingDate = CAST('20040701' AS
> DATETIME)" ,5,1,0,NULL,NULL,1,NULL,1,NULL,NULL,NULL
,3.81E-
> 02,NULL,NULL,UPDATE,0,NULL
> " |--Clustered Index Update(OBJECT:([DB1].[dbo].
> [CustomerProfileMonthly].[MerchantProfileMonthly_PK]), SET:
> ([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
> [CustomerProfileMonthly].[IncntvRevWAncil]=
> [Expr2860]))",5,2,1,Clustered Index Update,Update,"OBJECT:
> ([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK]), SET:
> ([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
> [CustomerProfileMonthly].[IncntvRevWAncil]=
> [Expr2860])",NULL,1,1.68E-02,0.000001,61,3.81E-
> 02,NULL,NULL,PLAN_ROW,0,1
> " |--Compute Scalar(DEFINE:([Expr2860]=Convert
> ([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
> ([t_detail_2004_07].[totalQualRevNew])))",5,3,2,Compute
> Scalar,Compute Scalar,"DEFINE:([Expr2860]=Convert
> ([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
> ([t_detail_2004_07].[totalQualRevNew]))","[Expr2860]
> =Convert([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]
> =Convert([t_detail_2004_07].
> [totalQualRevNew])",1,0,0.0000001,53,2.13E-02,"[Bmk1000],
> [Expr2860], [Expr2861]",NULL,PLAN_ROW,0,1
> |--Top(ROWCOUNT est
> 0),5,4,3,Top,Top,NULL,NULL,1,0,0.0000001,53,2.13E-
> 02,"[Bmk1000], [t_detail_2004_07].[totalQualRevOrg],
> [t_detail_2004_07].[totalQualRevNew]",NULL,PLAN_ROW,0,1
> |--Sort(DISTINCT ORDER BY:([Bmk1000]
> ASC)),5,5,4,Sort,Distinct Sort,DISTINCT ORDER BY:
> ([Bmk1000] ASC),NULL,1,1.13E-02,1.00E-
> 04,53,0.02130264,"[Bmk1000], [t_detail_2004_07].
> [totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]",NULL,PLAN_ROW,0,1
> " |--Compute Scalar(DEFINE:
> ([t_detail_2004_07].[totalQualRevOrg]=[t_detail_2004_07].
> [totalQualRevOrg], [t_detail_2004_07].[totalQualRevNew]=
> [t_detail_2004_07].[totalQualRevNew]))",5,6,5,Compute
> Scalar,Compute Scalar,"DEFINE:([t_detail_2004_07].
> [totalQualRevOrg]=[t_detail_2004_07].[totalQualRevOrg],
> [t_detail_2004_07].[totalQualRevNew]=[t_detail_2004_07].
> [totalQualRevNew])","[t_detail_2004_07].[totalQualRevOrg]=
> [t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]=[t_detail_2004_07].
> [totalQualRevNew]",1,0,0.0000001,53,9.94E-03,"[Bmk1000],
> [t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]",NULL,PLAN_ROW,0,1
> " |--Nested Loops(Inner Join,
> OUTER REFERENCES:([a].[CustNumber]))",5,7,6,Nested
> Loops,Inner Join,OUTER REFERENCES:([a].
> [CustNumber]),NULL,1,0,0.00001254,554,9.94E-03,"[Bmk1000],
> [t_detail_2004_07].[totalQualRevNew], [t_detail_2004_07].
> [totalQualRevOrg]",NULL,PLAN_ROW,0,1
> " |--Clustered Index S
> (OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
> [ControlingDate]='Jul 1 2004 12:00AM') ORDERED
> FORWARD)",5,8,7,Clustered Index S,Clustered Index
> S,"OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
> [ControlingDate]='Jul 1 2004 12:00AM') ORDERED
> FORWARD","[Bmk1000], [a].[CustNumber]",1,3.20E-03,7.96E-
> 05,107,3.28E-03,"[Bmk1000], [a].
> [CustNumber]",NULL,PLAN_ROW,0,1
> " |--Clustered Index S
> (OBJECT:([DB2].[dbo].[t_detail_2004_07].
> [PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
> [CustNumber]=[a].[CustNumber]) ORDERED
> FORWARD)",5,9,7,Clustered Index S,Clustered Index
> S,"OBJECT:([DB2].[dbo].[t_detail_2004_07].
> [PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
> [CustNumber]=[a].[CustNumber]) ORDERED
> FORWARD","[t_detail_2004_07].[totalQualRevNew],
> [t_detail_2004_07].[totalQualRevOrg]",1,3.20E-03,7.96E-
> 05,456,6.65E-03,"[t_detail_2004_07].[totalQualRevNew],
> [t_detail_2004_07].[totalQualRevOrg]",NULL,PLAN_ROW,0,3
> ,,,,,,,,,,,,,,,,,
>
> you post the pre-run
> requires an update to
> index.
> --
> interested in
> message
> have
> What
> truth
> just

Clustered Index -minimize the cost of Page Splitting

Hi guys,
I hope this is the last post from me for a while. Your help in the last 2
ws has been appreciated. We have run into problems with a production
system that was deployed at a site with 4 times the data found at our other
sites and have just 2 more ws to optimize.
We have found that using clustered primary keys on Order and Invoice tables
has decreased the Read time dramatically. The system is highly transactional
and now I am concerned about inserts.
Each site has multiple internal Divisions. We have included the records for
all Divisions (by table type) in a single table, rather than creating
separate tables for each Division.. This results in a PK of (for example)
DIVID + INVNUM.
I am now expecting that we will be dealing with many page splits during peak
transactional periods because of the clustered index. In lieu of creating
separate Invoice tables for each Division (a maintenance nightmare) how can
we minimize the impact of page splits since new records will be inserted at
the end of each "division group" within the table.
I have set up a nightly maintenance plan that reorganizes Data and Index
pages. I currently have it set to allow 10% free space. It also shrinks the
database allowing 10% free space. Should the free space be increased to
handle a daily load of about 40 new invoices per Division and 10 Divisions?
Each record is about 3k in size with no variable size fields. Am I being
overly concerned about the performance effects of inserts with the Clustered
index?
Please take into consideration that this sytem is deployed at 30 smaller
sites already and there is a huge amount of code written to read and write
on the basis of DIVID + INVNUM. There are about 12 other tables that hold
accounting related info that get updated simultaneously with every write. Of
course we will consider a complete re-write (if necessary) for future
releases.
Any suggestions would be appreciated. Thanks to all.....Hi John
Your solution sounds like you may benefit from a partitioned view (see books
online for more).
John
"John Kotuby" wrote:

> Hi guys,
> I hope this is the last post from me for a while. Your help in the last 2
> ws has been appreciated. We have run into problems with a production
> system that was deployed at a site with 4 times the data found at our othe
r
> sites and have just 2 more ws to optimize.
> We have found that using clustered primary keys on Order and Invoice table
s
> has decreased the Read time dramatically. The system is highly transaction
al
> and now I am concerned about inserts.
> Each site has multiple internal Divisions. We have included the records fo
r
> all Divisions (by table type) in a single table, rather than creating
> separate tables for each Division.. This results in a PK of (for example)
> DIVID + INVNUM.
> I am now expecting that we will be dealing with many page splits during pe
ak
> transactional periods because of the clustered index. In lieu of creating
> separate Invoice tables for each Division (a maintenance nightmare) how ca
n
> we minimize the impact of page splits since new records will be inserted a
t
> the end of each "division group" within the table.
> I have set up a nightly maintenance plan that reorganizes Data and Index
> pages. I currently have it set to allow 10% free space. It also shrinks th
e
> database allowing 10% free space. Should the free space be increased to
> handle a daily load of about 40 new invoices per Division and 10 Divisions
?
> Each record is about 3k in size with no variable size fields. Am I being
> overly concerned about the performance effects of inserts with the Cluster
ed
> index?
> Please take into consideration that this sytem is deployed at 30 smaller
> sites already and there is a huge amount of code written to read and write
> on the basis of DIVID + INVNUM. There are about 12 other tables that hold
> accounting related info that get updated simultaneously with every write.
Of
> course we will consider a complete re-write (if necessary) for future
> releases.
> Any suggestions would be appreciated. Thanks to all.....
>
>|||Thanks John,
I will check it out
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FB376DEF-6846-43D5-B437-9FC0FAFE5F46@.microsoft.com...
> Hi John
> Your solution sounds like you may benefit from a partitioned view (see
> books
> online for more).
> John
> "John Kotuby" wrote:
>