Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
Craig
In SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>
|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>
Showing posts with label clustered. Show all posts
Showing posts with label clustered. Show all posts
Tuesday, March 20, 2012
Changing a clustered index - impact on queries
Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
CraigIn SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>sql
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
CraigIn SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>sql
Changing a clustered index - impact on queries
Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
CraigIn SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
CraigIn SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>
Changes to clustered sql server
Hi
I need to change some settings on a clustered sql server 2000. I need to
use sp_configure to change the "set working set size" option and also
increase the memtoleave area using the -g flag.
How is this best accomplished in a cluster with 2 nodes?
- failover to node2, do the changes to node1 and restart the service?
- fail back to node1 and update node2 with a restart of service?
This way I will avoid downtime on the server.
Is this the way to do it?The settings follow the virtual server so once you stop and restart the
instance, you don't have to modify settings for any other nodes. Be sure
and make the startup flag changes using Enterprise Manager so it updates the
cluster properly. It is typically faster to move an instance to a new node
if you have a lot of AWE memory. Otherwise it is usually better to stop and
restart on the same node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E413E39AEgurbaohotmailcom@.129
.250.171.65...
> Hi
> I need to change some settings on a clustered sql server 2000. I need to
> use sp_configure to change the "set working set size" option and also
> increase the memtoleave area using the -g flag.
> How is this best accomplished in a cluster with 2 nodes?
> - failover to node2, do the changes to node1 and restart the service?
> - fail back to node1 and update node2 with a restart of service?
> This way I will avoid downtime on the server.
> Is this the way to do it?|||Thanks,
does this mean that e.g. the -g flag should not be added to the
imagepath key in the registry for the sql server service? Will
parameters to the service not be taken into account?
The reason I ask is that the cluster is set up this way today.
We don't use AWE, so I understand your advise as
- add the -g parameter in EM for the active instance
- restart the service
My goal is to minimize downtime.
Regards,
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
> The settings follow the virtual server so once you stop and restart
> the instance, you don't have to modify settings for any other nodes.
> Be sure and make the startup flag changes using Enterprise Manager so
> it updates the cluster properly. It is typically faster to move an
> instance to a new node if you have a lot of AWE memory. Otherwise it
> is usually better to stop and restart on the same node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E413E39AEgurbaohotmailcom@.129
.250.171.65...
>|||Generally on a cluster we are all trying to minimize downtime.
I am curious why you need more memtoleave. Are you running a lot of
third-party extended stored procedures? What specific symptoms are you
seeing? Before tweaking that parameter, I would open a case with PSS to try
and diagnose whatever the underlying problem really is.
EM is supposed to do the registry writes "under the covers" correctly for
clustered and non-clustered SQL instances.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E70926DD36gurbaohotmailcom@.12
9.250.171.68...
> Thanks,
> does this mean that e.g. the -g flag should not be added to the
> imagepath key in the registry for the sql server service? Will
> parameters to the service not be taken into account?
> The reason I ask is that the cluster is set up this way today.
> We don't use AWE, so I understand your advise as
> - add the -g parameter in EM for the active instance
> - restart the service
> My goal is to minimize downtime.
> Regards,
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>
>|||Hi,
We are seeing "WARNING: Failed to reserve contiguous memory ..."
messages in the errorlog. I've already been in contact with MS PSS and
they have advised us to increase the memtoleave area to see if this
resolves our problems.
This is not as a result of 3rdparty xps, but rather some "extreme"
queries that are submitted from time to time.
Thanks for your help.
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.12
9.250.171.68...
>
>|||Sorry for being "slow" here;
if I add the parameter (using EM) on the node owning the sql resource,
everything will be ok also when I failover and the sql server service
starts on the new node? EM took care of updating the registry also on
the second node?
If I add the parameter on the node _not_ owning the sql resource, EM
will update the registry on this node but not the other, so that I will
have to do the same on that after failover to this (phew)?
I have a feeling I'm being too complicated here, or is this stuff
complicated?
TIA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.12
9.250.171.68...
>|||Comments Inline
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9680AAAF4D1gurbaohotmailcom@.129.250.171.65...
> Sorry for being "slow" here;
I have no problem with you asking careful questions. NNTP postings are
cheap. Downtime gets expensive.
> if I add the parameter (using EM) on the node owning the sql resource,
> everything will be ok also when I failover and the sql server service
> starts on the new node? EM took care of updating the registry also on
> the second node?
Technically, EM updates the clustered registry keys and MSCS takes care of
copying them around where needed, but yes, that is essentially what happens.
> If I add the parameter on the node _not_ owning the sql resource, EM
> will update the registry on this node but not the other, so that I will
> have to do the same on that after failover to this (phew)?
>
Depends. If you manually hack the registry and/or startup parameters on a
non-owner node, it gets worse. Since that node doesn't own the resource
group, any changes made to the clustered keys/parameters get overwritten the
next time the resource group shifts to that node. EM actually has the SQL
Service write everything to the registry so it always happens on the correct
node.
> I have a feeling I'm being too complicated here, or is this stuff
> complicated?
Yes, it is complicated, but the fine programmers at Microsoft (cough, cough)
handle the complexity for you, at least in this case.
GNH
> TIA
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
>
>
I need to change some settings on a clustered sql server 2000. I need to
use sp_configure to change the "set working set size" option and also
increase the memtoleave area using the -g flag.
How is this best accomplished in a cluster with 2 nodes?
- failover to node2, do the changes to node1 and restart the service?
- fail back to node1 and update node2 with a restart of service?
This way I will avoid downtime on the server.
Is this the way to do it?The settings follow the virtual server so once you stop and restart the
instance, you don't have to modify settings for any other nodes. Be sure
and make the startup flag changes using Enterprise Manager so it updates the
cluster properly. It is typically faster to move an instance to a new node
if you have a lot of AWE memory. Otherwise it is usually better to stop and
restart on the same node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E413E39AEgurbaohotmailcom@.129
.250.171.65...
> Hi
> I need to change some settings on a clustered sql server 2000. I need to
> use sp_configure to change the "set working set size" option and also
> increase the memtoleave area using the -g flag.
> How is this best accomplished in a cluster with 2 nodes?
> - failover to node2, do the changes to node1 and restart the service?
> - fail back to node1 and update node2 with a restart of service?
> This way I will avoid downtime on the server.
> Is this the way to do it?|||Thanks,
does this mean that e.g. the -g flag should not be added to the
imagepath key in the registry for the sql server service? Will
parameters to the service not be taken into account?
The reason I ask is that the cluster is set up this way today.
We don't use AWE, so I understand your advise as
- add the -g parameter in EM for the active instance
- restart the service
My goal is to minimize downtime.
Regards,
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
> The settings follow the virtual server so once you stop and restart
> the instance, you don't have to modify settings for any other nodes.
> Be sure and make the startup flag changes using Enterprise Manager so
> it updates the cluster properly. It is typically faster to move an
> instance to a new node if you have a lot of AWE memory. Otherwise it
> is usually better to stop and restart on the same node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E413E39AEgurbaohotmailcom@.129
.250.171.65...
>|||Generally on a cluster we are all trying to minimize downtime.
I am curious why you need more memtoleave. Are you running a lot of
third-party extended stored procedures? What specific symptoms are you
seeing? Before tweaking that parameter, I would open a case with PSS to try
and diagnose whatever the underlying problem really is.
EM is supposed to do the registry writes "under the covers" correctly for
clustered and non-clustered SQL instances.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E70926DD36gurbaohotmailcom@.12
9.250.171.68...
> Thanks,
> does this mean that e.g. the -g flag should not be added to the
> imagepath key in the registry for the sql server service? Will
> parameters to the service not be taken into account?
> The reason I ask is that the cluster is set up this way today.
> We don't use AWE, so I understand your advise as
> - add the -g parameter in EM for the active instance
> - restart the service
> My goal is to minimize downtime.
> Regards,
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>
>|||Hi,
We are seeing "WARNING: Failed to reserve contiguous memory ..."
messages in the errorlog. I've already been in contact with MS PSS and
they have advised us to increase the memtoleave area to see if this
resolves our problems.
This is not as a result of 3rdparty xps, but rather some "extreme"
queries that are submitted from time to time.
Thanks for your help.
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.12
9.250.171.68...
>
>|||Sorry for being "slow" here;
if I add the parameter (using EM) on the node owning the sql resource,
everything will be ok also when I failover and the sql server service
starts on the new node? EM took care of updating the registry also on
the second node?
If I add the parameter on the node _not_ owning the sql resource, EM
will update the registry on this node but not the other, so that I will
have to do the same on that after failover to this (phew)?
I have a feeling I'm being too complicated here, or is this stuff
complicated?
TIA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.12
9.250.171.68...
>|||Comments Inline
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9680AAAF4D1gurbaohotmailcom@.129.250.171.65...
> Sorry for being "slow" here;
I have no problem with you asking careful questions. NNTP postings are
cheap. Downtime gets expensive.
> if I add the parameter (using EM) on the node owning the sql resource,
> everything will be ok also when I failover and the sql server service
> starts on the new node? EM took care of updating the registry also on
> the second node?
Technically, EM updates the clustered registry keys and MSCS takes care of
copying them around where needed, but yes, that is essentially what happens.
> If I add the parameter on the node _not_ owning the sql resource, EM
> will update the registry on this node but not the other, so that I will
> have to do the same on that after failover to this (phew)?
>
Depends. If you manually hack the registry and/or startup parameters on a
non-owner node, it gets worse. Since that node doesn't own the resource
group, any changes made to the clustered keys/parameters get overwritten the
next time the resource group shifts to that node. EM actually has the SQL
Service write everything to the registry so it always happens on the correct
node.
> I have a feeling I'm being too complicated here, or is this stuff
> complicated?
Yes, it is complicated, but the fine programmers at Microsoft (cough, cough)
handle the complexity for you, at least in this case.
GNH
> TIA
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
>
>
Changes to clustered sql server
Hi
I need to change some settings on a clustered sql server 2000. I need to
use sp_configure to change the "set working set size" option and also
increase the memtoleave area using the -g flag.
How is this best accomplished in a cluster with 2 nodes?
- failover to node2, do the changes to node1 and restart the service?
- fail back to node1 and update node2 with a restart of service?
This way I will avoid downtime on the server.
Is this the way to do it?The settings follow the virtual server so once you stop and restart the
instance, you don't have to modify settings for any other nodes. Be sure
and make the startup flag changes using Enterprise Manager so it updates the
cluster properly. It is typically faster to move an instance to a new node
if you have a lot of AWE memory. Otherwise it is usually better to stop and
restart on the same node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
> Hi
> I need to change some settings on a clustered sql server 2000. I need to
> use sp_configure to change the "set working set size" option and also
> increase the memtoleave area using the -g flag.
> How is this best accomplished in a cluster with 2 nodes?
> - failover to node2, do the changes to node1 and restart the service?
> - fail back to node1 and update node2 with a restart of service?
> This way I will avoid downtime on the server.
> Is this the way to do it?|||Thanks,
does this mean that e.g. the -g flag should not be added to the
imagepath key in the registry for the sql server service? Will
parameters to the service not be taken into account?
The reason I ask is that the cluster is set up this way today.
We don't use AWE, so I understand your advise as
- add the -g parameter in EM for the active instance
- restart the service
My goal is to minimize downtime.
Regards,
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
> The settings follow the virtual server so once you stop and restart
> the instance, you don't have to modify settings for any other nodes.
> Be sure and make the startup flag changes using Enterprise Manager so
> it updates the cluster properly. It is typically faster to move an
> instance to a new node if you have a lot of AWE memory. Otherwise it
> is usually better to stop and restart on the same node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I need
>> to use sp_configure to change the "set working set size" option and
>> also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the service?
>> - fail back to node1 and update node2 with a restart of service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>|||Generally on a cluster we are all trying to minimize downtime.
I am curious why you need more memtoleave. Are you running a lot of
third-party extended stored procedures? What specific symptoms are you
seeing? Before tweaking that parameter, I would open a case with PSS to try
and diagnose whatever the underlying problem really is.
EM is supposed to do the registry writes "under the covers" correctly for
clustered and non-clustered SQL instances.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
> Thanks,
> does this mean that e.g. the -g flag should not be added to the
> imagepath key in the registry for the sql server service? Will
> parameters to the service not be taken into account?
> The reason I ask is that the cluster is set up this way today.
> We don't use AWE, so I understand your advise as
> - add the -g parameter in EM for the active instance
> - restart the service
> My goal is to minimize downtime.
> Regards,
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager so
>> it updates the cluster properly. It is typically faster to move an
>> instance to a new node if you have a lot of AWE memory. Otherwise it
>> is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I need
>> to use sp_configure to change the "set working set size" option and
>> also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the service?
>> - fail back to node1 and update node2 with a restart of service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>|||Hi,
We are seeing "WARNING: Failed to reserve contiguous memory ..."
messages in the errorlog. I've already been in contact with MS PSS and
they have advised us to increase the memtoleave area to see if this
resolves our problems.
This is not as a result of 3rdparty xps, but rather some "extreme"
queries that are submitted from time to time.
Thanks for your help.
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>
>|||Sorry for being "slow" here;
if I add the parameter (using EM) on the node owning the sql resource,
everything will be ok also when I failover and the sql server service
starts on the new node? EM took care of updating the registry also on
the second node?
If I add the parameter on the node _not_ owning the sql resource, EM
will update the registry on this node but not the other, so that I will
have to do the same on that after failover to this (phew)?
I have a feeling I'm being too complicated here, or is this stuff
complicated?
TIA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>|||Comments Inline
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9680AAAF4D1gurbaohotmailcom@.129.250.171.65...
> Sorry for being "slow" here;
I have no problem with you asking careful questions. NNTP postings are
cheap. Downtime gets expensive.
> if I add the parameter (using EM) on the node owning the sql resource,
> everything will be ok also when I failover and the sql server service
> starts on the new node? EM took care of updating the registry also on
> the second node?
Technically, EM updates the clustered registry keys and MSCS takes care of
copying them around where needed, but yes, that is essentially what happens.
> If I add the parameter on the node _not_ owning the sql resource, EM
> will update the registry on this node but not the other, so that I will
> have to do the same on that after failover to this (phew)?
>
Depends. If you manually hack the registry and/or startup parameters on a
non-owner node, it gets worse. Since that node doesn't own the resource
group, any changes made to the clustered keys/parameters get overwritten the
next time the resource group shifts to that node. EM actually has the SQL
Service write everything to the registry so it always happens on the correct
node.
> I have a feeling I'm being too complicated here, or is this stuff
> complicated?
Yes, it is complicated, but the fine programmers at Microsoft (cough, cough)
handle the complexity for you, at least in this case.
GNH
> TIA
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
>> Generally on a cluster we are all trying to minimize downtime.
>> I am curious why you need more memtoleave. Are you running a lot of
>> third-party extended stored procedures? What specific symptoms are
>> you seeing? Before tweaking that parameter, I would open a case with
>> PSS to try and diagnose whatever the underlying problem really is.
>> EM is supposed to do the registry writes "under the covers" correctly
>> for clustered and non-clustered SQL instances.
>>
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>>
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>>
>
I need to change some settings on a clustered sql server 2000. I need to
use sp_configure to change the "set working set size" option and also
increase the memtoleave area using the -g flag.
How is this best accomplished in a cluster with 2 nodes?
- failover to node2, do the changes to node1 and restart the service?
- fail back to node1 and update node2 with a restart of service?
This way I will avoid downtime on the server.
Is this the way to do it?The settings follow the virtual server so once you stop and restart the
instance, you don't have to modify settings for any other nodes. Be sure
and make the startup flag changes using Enterprise Manager so it updates the
cluster properly. It is typically faster to move an instance to a new node
if you have a lot of AWE memory. Otherwise it is usually better to stop and
restart on the same node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
> Hi
> I need to change some settings on a clustered sql server 2000. I need to
> use sp_configure to change the "set working set size" option and also
> increase the memtoleave area using the -g flag.
> How is this best accomplished in a cluster with 2 nodes?
> - failover to node2, do the changes to node1 and restart the service?
> - fail back to node1 and update node2 with a restart of service?
> This way I will avoid downtime on the server.
> Is this the way to do it?|||Thanks,
does this mean that e.g. the -g flag should not be added to the
imagepath key in the registry for the sql server service? Will
parameters to the service not be taken into account?
The reason I ask is that the cluster is set up this way today.
We don't use AWE, so I understand your advise as
- add the -g parameter in EM for the active instance
- restart the service
My goal is to minimize downtime.
Regards,
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
> The settings follow the virtual server so once you stop and restart
> the instance, you don't have to modify settings for any other nodes.
> Be sure and make the startup flag changes using Enterprise Manager so
> it updates the cluster properly. It is typically faster to move an
> instance to a new node if you have a lot of AWE memory. Otherwise it
> is usually better to stop and restart on the same node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I need
>> to use sp_configure to change the "set working set size" option and
>> also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the service?
>> - fail back to node1 and update node2 with a restart of service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>|||Generally on a cluster we are all trying to minimize downtime.
I am curious why you need more memtoleave. Are you running a lot of
third-party extended stored procedures? What specific symptoms are you
seeing? Before tweaking that parameter, I would open a case with PSS to try
and diagnose whatever the underlying problem really is.
EM is supposed to do the registry writes "under the covers" correctly for
clustered and non-clustered SQL instances.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
> Thanks,
> does this mean that e.g. the -g flag should not be added to the
> imagepath key in the registry for the sql server service? Will
> parameters to the service not be taken into account?
> The reason I ask is that the cluster is set up this way today.
> We don't use AWE, so I understand your advise as
> - add the -g parameter in EM for the active instance
> - restart the service
> My goal is to minimize downtime.
> Regards,
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager so
>> it updates the cluster properly. It is typically faster to move an
>> instance to a new node if you have a lot of AWE memory. Otherwise it
>> is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I need
>> to use sp_configure to change the "set working set size" option and
>> also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the service?
>> - fail back to node1 and update node2 with a restart of service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>|||Hi,
We are seeing "WARNING: Failed to reserve contiguous memory ..."
messages in the errorlog. I've already been in contact with MS PSS and
they have advised us to increase the memtoleave area to see if this
resolves our problems.
This is not as a result of 3rdparty xps, but rather some "extreme"
queries that are submitted from time to time.
Thanks for your help.
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>
>|||Sorry for being "slow" here;
if I add the parameter (using EM) on the node owning the sql resource,
everything will be ok also when I failover and the sql server service
starts on the new node? EM took care of updating the registry also on
the second node?
If I add the parameter on the node _not_ owning the sql resource, EM
will update the registry on this node but not the other, so that I will
have to do the same on that after failover to this (phew)?
I have a feeling I'm being too complicated here, or is this stuff
complicated?
TIA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>|||Comments Inline
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9680AAAF4D1gurbaohotmailcom@.129.250.171.65...
> Sorry for being "slow" here;
I have no problem with you asking careful questions. NNTP postings are
cheap. Downtime gets expensive.
> if I add the parameter (using EM) on the node owning the sql resource,
> everything will be ok also when I failover and the sql server service
> starts on the new node? EM took care of updating the registry also on
> the second node?
Technically, EM updates the clustered registry keys and MSCS takes care of
copying them around where needed, but yes, that is essentially what happens.
> If I add the parameter on the node _not_ owning the sql resource, EM
> will update the registry on this node but not the other, so that I will
> have to do the same on that after failover to this (phew)?
>
Depends. If you manually hack the registry and/or startup parameters on a
non-owner node, it gets worse. Since that node doesn't own the resource
group, any changes made to the clustered keys/parameters get overwritten the
next time the resource group shifts to that node. EM actually has the SQL
Service write everything to the registry so it always happens on the correct
node.
> I have a feeling I'm being too complicated here, or is this stuff
> complicated?
Yes, it is complicated, but the fine programmers at Microsoft (cough, cough)
handle the complexity for you, at least in this case.
GNH
> TIA
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
>> Generally on a cluster we are all trying to minimize downtime.
>> I am curious why you need more memtoleave. Are you running a lot of
>> third-party extended stored procedures? What specific symptoms are
>> you seeing? Before tweaking that parameter, I would open a case with
>> PSS to try and diagnose whatever the underlying problem really is.
>> EM is supposed to do the registry writes "under the covers" correctly
>> for clustered and non-clustered SQL instances.
>>
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>>
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>>
>
Changes to clustered sql server
Hi
I need to change some settings on a clustered sql server 2000. I need to
use sp_configure to change the "set working set size" option and also
increase the memtoleave area using the -g flag.
How is this best accomplished in a cluster with 2 nodes?
- failover to node2, do the changes to node1 and restart the service?
- fail back to node1 and update node2 with a restart of service?
This way I will avoid downtime on the server.
Is this the way to do it?
The settings follow the virtual server so once you stop and restart the
instance, you don't have to modify settings for any other nodes. Be sure
and make the startup flag changes using Enterprise Manager so it updates the
cluster properly. It is typically faster to move an instance to a new node
if you have a lot of AWE memory. Otherwise it is usually better to stop and
restart on the same node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.6 5...
> Hi
> I need to change some settings on a clustered sql server 2000. I need to
> use sp_configure to change the "set working set size" option and also
> increase the memtoleave area using the -g flag.
> How is this best accomplished in a cluster with 2 nodes?
> - failover to node2, do the changes to node1 and restart the service?
> - fail back to node1 and update node2 with a restart of service?
> This way I will avoid downtime on the server.
> Is this the way to do it?
|||Thanks,
does this mean that e.g. the -g flag should not be added to the
imagepath key in the registry for the sql server service? Will
parameters to the service not be taken into account?
The reason I ask is that the cluster is set up this way today.
We don't use AWE, so I understand your advise as
- add the -g parameter in EM for the active instance
- restart the service
My goal is to minimize downtime.
Regards,
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
> The settings follow the virtual server so once you stop and restart
> the instance, you don't have to modify settings for any other nodes.
> Be sure and make the startup flag changes using Enterprise Manager so
> it updates the cluster properly. It is typically faster to move an
> instance to a new node if you have a lot of AWE memory. Otherwise it
> is usually better to stop and restart on the same node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.6 5...
>
|||Generally on a cluster we are all trying to minimize downtime.
I am curious why you need more memtoleave. Are you running a lot of
third-party extended stored procedures? What specific symptoms are you
seeing? Before tweaking that parameter, I would open a case with PSS to try
and diagnose whatever the underlying problem really is.
EM is supposed to do the registry writes "under the covers" correctly for
clustered and non-clustered SQL instances.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
> Thanks,
> does this mean that e.g. the -g flag should not be added to the
> imagepath key in the registry for the sql server service? Will
> parameters to the service not be taken into account?
> The reason I ask is that the cluster is set up this way today.
> We don't use AWE, so I understand your advise as
> - add the -g parameter in EM for the active instance
> - restart the service
> My goal is to minimize downtime.
> Regards,
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>
|||Hi,
We are seeing "WARNING: Failed to reserve contiguous memory ..."
messages in the errorlog. I've already been in contact with MS PSS and
they have advised us to increase the memtoleave area to see if this
resolves our problems.
This is not as a result of 3rdparty xps, but rather some "extreme"
queries that are submitted from time to time.
Thanks for your help.
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
>
>
|||Sorry for being "slow" here;
if I add the parameter (using EM) on the node owning the sql resource,
everything will be ok also when I failover and the sql server service
starts on the new node? EM took care of updating the registry also on
the second node?
If I add the parameter on the node _not_ owning the sql resource, EM
will update the registry on this node but not the other, so that I will
have to do the same on that after failover to this (phew)?
I have a feeling I'm being too complicated here, or is this stuff
complicated?
TIA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
>
|||Comments Inline
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9680AAAF4D1gurbaohotmailcom@.129.250.171.65 ...
> Sorry for being "slow" here;
I have no problem with you asking careful questions. NNTP postings are
cheap. Downtime gets expensive.
> if I add the parameter (using EM) on the node owning the sql resource,
> everything will be ok also when I failover and the sql server service
> starts on the new node? EM took care of updating the registry also on
> the second node?
Technically, EM updates the clustered registry keys and MSCS takes care of
copying them around where needed, but yes, that is essentially what happens.
> If I add the parameter on the node _not_ owning the sql resource, EM
> will update the registry on this node but not the other, so that I will
> have to do the same on that after failover to this (phew)?
>
Depends. If you manually hack the registry and/or startup parameters on a
non-owner node, it gets worse. Since that node doesn't own the resource
group, any changes made to the clustered keys/parameters get overwritten the
next time the resource group shifts to that node. EM actually has the SQL
Service write everything to the registry so it always happens on the correct
node.
> I have a feeling I'm being too complicated here, or is this stuff
> complicated?
Yes, it is complicated, but the fine programmers at Microsoft (cough, cough)
handle the complexity for you, at least in this case.
GNH
> TIA
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
>
I need to change some settings on a clustered sql server 2000. I need to
use sp_configure to change the "set working set size" option and also
increase the memtoleave area using the -g flag.
How is this best accomplished in a cluster with 2 nodes?
- failover to node2, do the changes to node1 and restart the service?
- fail back to node1 and update node2 with a restart of service?
This way I will avoid downtime on the server.
Is this the way to do it?
The settings follow the virtual server so once you stop and restart the
instance, you don't have to modify settings for any other nodes. Be sure
and make the startup flag changes using Enterprise Manager so it updates the
cluster properly. It is typically faster to move an instance to a new node
if you have a lot of AWE memory. Otherwise it is usually better to stop and
restart on the same node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.6 5...
> Hi
> I need to change some settings on a clustered sql server 2000. I need to
> use sp_configure to change the "set working set size" option and also
> increase the memtoleave area using the -g flag.
> How is this best accomplished in a cluster with 2 nodes?
> - failover to node2, do the changes to node1 and restart the service?
> - fail back to node1 and update node2 with a restart of service?
> This way I will avoid downtime on the server.
> Is this the way to do it?
|||Thanks,
does this mean that e.g. the -g flag should not be added to the
imagepath key in the registry for the sql server service? Will
parameters to the service not be taken into account?
The reason I ask is that the cluster is set up this way today.
We don't use AWE, so I understand your advise as
- add the -g parameter in EM for the active instance
- restart the service
My goal is to minimize downtime.
Regards,
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
> The settings follow the virtual server so once you stop and restart
> the instance, you don't have to modify settings for any other nodes.
> Be sure and make the startup flag changes using Enterprise Manager so
> it updates the cluster properly. It is typically faster to move an
> instance to a new node if you have a lot of AWE memory. Otherwise it
> is usually better to stop and restart on the same node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.6 5...
>
|||Generally on a cluster we are all trying to minimize downtime.
I am curious why you need more memtoleave. Are you running a lot of
third-party extended stored procedures? What specific symptoms are you
seeing? Before tweaking that parameter, I would open a case with PSS to try
and diagnose whatever the underlying problem really is.
EM is supposed to do the registry writes "under the covers" correctly for
clustered and non-clustered SQL instances.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
> Thanks,
> does this mean that e.g. the -g flag should not be added to the
> imagepath key in the registry for the sql server service? Will
> parameters to the service not be taken into account?
> The reason I ask is that the cluster is set up this way today.
> We don't use AWE, so I understand your advise as
> - add the -g parameter in EM for the active instance
> - restart the service
> My goal is to minimize downtime.
> Regards,
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>
|||Hi,
We are seeing "WARNING: Failed to reserve contiguous memory ..."
messages in the errorlog. I've already been in contact with MS PSS and
they have advised us to increase the memtoleave area to see if this
resolves our problems.
This is not as a result of 3rdparty xps, but rather some "extreme"
queries that are submitted from time to time.
Thanks for your help.
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
>
>
|||Sorry for being "slow" here;
if I add the parameter (using EM) on the node owning the sql resource,
everything will be ok also when I failover and the sql server service
starts on the new node? EM took care of updating the registry also on
the second node?
If I add the parameter on the node _not_ owning the sql resource, EM
will update the registry on this node but not the other, so that I will
have to do the same on that after failover to this (phew)?
I have a feeling I'm being too complicated here, or is this stuff
complicated?
TIA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
>
|||Comments Inline
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9680AAAF4D1gurbaohotmailcom@.129.250.171.65 ...
> Sorry for being "slow" here;
I have no problem with you asking careful questions. NNTP postings are
cheap. Downtime gets expensive.
> if I add the parameter (using EM) on the node owning the sql resource,
> everything will be ok also when I failover and the sql server service
> starts on the new node? EM took care of updating the registry also on
> the second node?
Technically, EM updates the clustered registry keys and MSCS takes care of
copying them around where needed, but yes, that is essentially what happens.
> If I add the parameter on the node _not_ owning the sql resource, EM
> will update the registry on this node but not the other, so that I will
> have to do the same on that after failover to this (phew)?
>
Depends. If you manually hack the registry and/or startup parameters on a
non-owner node, it gets worse. Since that node doesn't own the resource
group, any changes made to the clustered keys/parameters get overwritten the
next time the resource group shifts to that node. EM actually has the SQL
Service write everything to the registry so it always happens on the correct
node.
> I have a feeling I'm being too complicated here, or is this stuff
> complicated?
Yes, it is complicated, but the fine programmers at Microsoft (cough, cough)
handle the complexity for you, at least in this case.
GNH
> TIA
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
>
Sunday, February 19, 2012
Change server name in MSSQL cluster
Hi all, I've got PDC and BDC Windows NT 4.0 Servers (Enterprise edition) installed in the MS cluster. The clustered application is MS SQL server 7.0. Now I need to change names of both nodes - PDC and BDC Windows NT 4.0 Servers. Has anyone any experience with such operation ? Is it too risky ? Thanks a lot for any hint MichalChanging the server names will break your SQL servers.
I did this on a standalone (changed the server name) and SQL stopped working. The fix according to Microsoft was to reinstall SQL on the servers. When the installation starts it will see that all the components are already installed and then see the name resolution conflict and fix it.
This worked for the standalone but I dont' know about the cluster.|||Furthermore, you can not change the name of a PDC without reinstalling NT 4 server.
I did this on a standalone (changed the server name) and SQL stopped working. The fix according to Microsoft was to reinstall SQL on the servers. When the installation starts it will see that all the components are already installed and then see the name resolution conflict and fix it.
This worked for the standalone but I dont' know about the cluster.|||Furthermore, you can not change the name of a PDC without reinstalling NT 4 server.
Friday, February 10, 2012
Change non-clustered index to clustered
Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
Change non-clustered index to clustered
Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?
I think I found the answer, you do a create index with drop existing.
|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegro ups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>
|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?
I think I found the answer, you do a create index with drop existing.
|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegro ups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>
|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
Change non-clustered index to clustered
Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
Subscribe to:
Posts (Atom)