changing the default port on a clustered 2005 server.

I am unclear as to which Ip address to change the standard port on. When setting up a cluster you have multiple IP address's including the nic that is used for the heartbeat. I have not found any documention on how to change both nodes to listen on a port other than 1433..

Any help would be greatly appreciated.

Set the one titled "IPAll" (you may need to scroll to the bottom of the list). The clustered SQL Server instance will apply it to the actual IP addresses of the cluster.

changing the default port on a clustered 2005 server.

I am unclear as to which Ip address to change the standard port on. When setting up a cluster you have multiple IP address's including the nic that is used for the heartbeat. I have not found any documention on how to change both nodes to listen on a port other than 1433..

Any help would be greatly appreciated.

Set the one titled "IPAll" (you may need to scroll to the bottom of the list). The clustered SQL Server instance will apply it to the actual IP addresses of the cluster.

Changing the clustered index on a table - tips?

Hi,
SQL Server 2000
I've got a table that doesn't change during the day but has inserts
overnight. currently 4 million rows in the table and I would say no
more than 5000 rows being added nightly. Db server is totally
overloaded and sometimes queries on this table take a long time.
Queries usually filter on a client_id and a input_date range. Input
date range is not normally very selective. There can be up to about
100k rows per client_id. There are normally additional group by
clauses or further filters but they are not applied every time. Also
frequently used is a bit flag called billed. billed = 0 is very
selective (0-5%), billed = 1 is not (>95%). The clustering is
currently on the identity PK so is completely wasted - only benefit is
that the fill factor can be set to 90 as allrows are currently added
to the end of the table.
I want to change the clustering index to help improve performance of
queries. My initial thought is:
(client_id, input_date). Does this seem remotely sensible? Should it
be the other way round?
Given that I would always expect client_id to be used in a query,
would putting a non-clustered index on (billed) achieve anything? Or
on (client_id, billed)?
The suggested clustered index seems to work quite well and although
not making much difference to CPU time seems to cut logical reads by
about 40x. I think it will have more effect on Live which is much
busier and much less powerful than the dev server (yes I know that's
daft!)
I think this will increase fragmentation on the table. Should I be
worried about this - I intend to rebuild all indexes with a fillfactor
of 70? My plan would be to follow the following:
DROP all existing indexes and constraints
Create new clustered index with fillfactor of 70
recreate nonclustered indexes and constraints with fillfactor of 70
Is it okay to build a clustered index on a populated table or is it
better to create a new table, add clustered index and then populate,
then rename?
Many thanks.
Cheers,
James
Jim,
I have done similar(composite nonunique CI's for ranges) with good results
but it sounds like this is a big deal so I would test as much as possible. I
would also consider your NCI's during testing. Does adding a column or two
to an NCI cover more queries? 70% on the fillfactor sounds low to me if you
are just adding 5k rows\day. How often are you reindexing?
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
> Hi,
> SQL Server 2000
> I've got a table that doesn't change during the day but has inserts
> overnight. currently 4 million rows in the table and I would say no
> more than 5000 rows being added nightly. Db server is totally
> overloaded and sometimes queries on this table take a long time.
> Queries usually filter on a client_id and a input_date range. Input
> date range is not normally very selective. There can be up to about
> 100k rows per client_id. There are normally additional group by
> clauses or further filters but they are not applied every time. Also
> frequently used is a bit flag called billed. billed = 0 is very
> selective (0-5%), billed = 1 is not (>95%). The clustering is
> currently on the identity PK so is completely wasted - only benefit is
> that the fill factor can be set to 90 as allrows are currently added
> to the end of the table.
> I want to change the clustering index to help improve performance of
> queries. My initial thought is:
> (client_id, input_date). Does this seem remotely sensible? Should it
> be the other way round?
> Given that I would always expect client_id to be used in a query,
> would putting a non-clustered index on (billed) achieve anything? Or
> on (client_id, billed)?
> The suggested clustered index seems to work quite well and although
> not making much difference to CPU time seems to cut logical reads by
> about 40x. I think it will have more effect on Live which is much
> busier and much less powerful than the dev server (yes I know that's
> daft!)
> I think this will increase fragmentation on the table. Should I be
> worried about this - I intend to rebuild all indexes with a fillfactor
> of 70? My plan would be to follow the following:
> DROP all existing indexes and constraints
> Create new clustered index with fillfactor of 70
> recreate nonclustered indexes and constraints with fillfactor of 70
> Is it okay to build a clustered index on a populated table or is it
> better to create a new table, add clustered index and then populate,
> then rename?
> Many thanks.
> Cheers,
> James
|||If the database is in Simple or Bulk Logged mode the Creation and rebuilding
of the indexes can be minimally logged and faster as a result. Just be sure
to switch back to Full and take a full backup when done.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:64479e9a-191e-4445-b52d-4a9a89ef8a13@.i29g2000prf.googlegroups.com...
On Feb 27, 2:03 am, "jason" <jason-r3m...@.statisticsio.com> wrote:
> Jim,
> I have done similar(composite nonunique CI's for ranges) with good results
> but it sounds like this is a big deal so I would test as much as possible.
> I
> would also consider your NCI's during testing. Does adding a column or two
> to an NCI cover more queries? 70% on the fillfactor sounds low to me if
> you
> are just adding 5k rows\day. How often are you reindexing?
> --
> Jason Massie
> www:http://statisticsio.com
> rss:http://feeds.feedburner.com/statisticsio
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Hi Jason,
I checked the maintenance plans and they get rebuilt every Sunday with
a fixed free space percentage of 10% - so fillfactor is irrelevant.
Anyway following on from your comments I'll specify fillfactor as 90.
I've had a play with the indexes and the suggested clustered index
works extremely well. The nonclustered one I suggested on billed or
client_id, billed is never actually used as it always favours the
clustered index seek with a WHERE clause on the billed column.
Thanks for your help.
Just one more question - the database is fully logged. Does it make
any difference to speed/transaction log size whether you recluster
the index in situ or in a new replacement table?
James

Changing the clustered index on a table - tips?

Hi,
SQL Server 2000
I've got a table that doesn't change during the day but has inserts
overnight. currently 4 million rows in the table and I would say no
more than 5000 rows being added nightly. Db server is totally
overloaded and sometimes queries on this table take a long time.
Queries usually filter on a client_id and a input_date range. Input
date range is not normally very selective. There can be up to about
100k rows per client_id. There are normally additional group by
clauses or further filters but they are not applied every time. Also
frequently used is a bit flag called billed. billed = 0 is very
selective (0-5%), billed = 1 is not (>95%). The clustering is
currently on the identity PK so is completely wasted - only benefit is
that the fill factor can be set to 90 as allrows are currently added
to the end of the table.
I want to change the clustering index to help improve performance of
queries. My initial thought is:
(client_id, input_date). Does this seem remotely sensible? Should it
be the other way round?
Given that I would always expect client_id to be used in a query,
would putting a non-clustered index on (billed) achieve anything? Or
on (client_id, billed)?
The suggested clustered index seems to work quite well and although
not making much difference to CPU time seems to cut logical reads by
about 40x. I think it will have more effect on Live which is much
busier and much less powerful than the dev server (yes I know that's
daft!)
I think this will increase fragmentation on the table. Should I be
worried about this - I intend to rebuild all indexes with a fillfactor
of 70? My plan would be to follow the following:
DROP all existing indexes and constraints
Create new clustered index with fillfactor of 70
recreate nonclustered indexes and constraints with fillfactor of 70
Is it okay to build a clustered index on a populated table or is it
better to create a new table, add clustered index and then populate,
then rename?
Many thanks.
Cheers,
JamesJim,
I have done similar(composite nonunique CI's for ranges) with good results
but it sounds like this is a big deal so I would test as much as possible. I
would also consider your NCI's during testing. Does adding a column or two
to an NCI cover more queries? 70% on the fillfactor sounds low to me if you
are just adding 5k rows\day. How often are you reindexing?
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
> Hi,
> SQL Server 2000
> I've got a table that doesn't change during the day but has inserts
> overnight. currently 4 million rows in the table and I would say no
> more than 5000 rows being added nightly. Db server is totally
> overloaded and sometimes queries on this table take a long time.
> Queries usually filter on a client_id and a input_date range. Input
> date range is not normally very selective. There can be up to about
> 100k rows per client_id. There are normally additional group by
> clauses or further filters but they are not applied every time. Also
> frequently used is a bit flag called billed. billed = 0 is very
> selective (0-5%), billed = 1 is not (>95%). The clustering is
> currently on the identity PK so is completely wasted - only benefit is
> that the fill factor can be set to 90 as allrows are currently added
> to the end of the table.
> I want to change the clustering index to help improve performance of
> queries. My initial thought is:
> (client_id, input_date). Does this seem remotely sensible? Should it
> be the other way round?
> Given that I would always expect client_id to be used in a query,
> would putting a non-clustered index on (billed) achieve anything? Or
> on (client_id, billed)?
> The suggested clustered index seems to work quite well and although
> not making much difference to CPU time seems to cut logical reads by
> about 40x. I think it will have more effect on Live which is much
> busier and much less powerful than the dev server (yes I know that's
> daft!)
> I think this will increase fragmentation on the table. Should I be
> worried about this - I intend to rebuild all indexes with a fillfactor
> of 70? My plan would be to follow the following:
> DROP all existing indexes and constraints
> Create new clustered index with fillfactor of 70
> recreate nonclustered indexes and constraints with fillfactor of 70
> Is it okay to build a clustered index on a populated table or is it
> better to create a new table, add clustered index and then populate,
> then rename?
> Many thanks.
> Cheers,
> James|||On Feb 27, 2:03=A0am, "jason" <jason-r3m...@.statisticsio.com> wrote:
> Jim,
> I have done similar(composite nonunique CI's for ranges) with good results=
> but it sounds like this is a big deal so I would test as much as possible.= I
> would also consider your NCI's during testing. Does adding a column or two=
> to an NCI cover more queries? 70% on the fillfactor sounds low to me if yo=u
> are just adding 5k rows\day. How often are you reindexing?
> --
> Jason Massie
> www:http://statisticsio.com
> rss:http://feeds.feedburner.com/statisticsio
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
>
> > Hi,
> > SQL Server 2000
> > I've got a table that doesn't change during the day but has inserts
> > overnight. currently 4 million rows in the table and I would say no
> > more than 5000 rows being added nightly. Db server is totally
> > overloaded and sometimes queries on this table take a long time.
> > Queries usually filter on a client_id and a input_date range. Input
> > date range is not normally very selective. There can be up to about
> > 100k rows per client_id. There are normally additional group by
> > clauses or further filters but they are not applied every time. Also
> > frequently used is a bit flag =A0called billed. billed =3D 0 is very
> > selective (0-5%), billed =3D 1 is not (>95%). The clustering is
> > currently on the identity PK so is completely wasted - only benefit is
> > that the fill factor can be set to 90 as allrows are currently added
> > to the end of the table.
> > I want to change the clustering index to help improve performance of
> > queries. My initial thought is:
> > (client_id, input_date). Does this seem remotely sensible? Should it
> > be the other way round?
> > Given that I would always expect client_id to be used in a query,
> > would putting a non-clustered index on (billed) achieve anything? Or
> > on (client_id, billed)?
> > The suggested clustered index seems to work quite well and although
> > not making much difference to CPU time seems to cut logical reads by
> > about 40x. I think it will have more effect on Live which is much
> > busier and much less powerful than the dev server (yes I know that's
> > daft!)
> > I think this will increase fragmentation on the table. Should I be
> > worried about this - I intend to rebuild all indexes with a fillfactor
> > of 70? My plan would be to follow the following:
> > DROP all existing indexes and constraints
> > Create new clustered index with fillfactor of 70
> > recreate nonclustered indexes and constraints with fillfactor of 70
> > Is it okay to build a clustered index on a populated table or is it
> > better to create a new table, add clustered index and then populate,
> > then rename?
> > Many thanks.
> > Cheers,
> > James- Hide quoted text -
> - Show quoted text -
Hi Jason,
I checked the maintenance plans and they get rebuilt every Sunday with
a fixed free space percentage of 10% - so fillfactor is irrelevant.
Anyway following on from your comments I'll specify fillfactor as 90.
I've had a play with the indexes and the suggested clustered index
works extremely well. The nonclustered one I suggested on billed or
client_id, billed is never actually used as it always favours the
clustered index seek with a WHERE clause on the billed column.
Thanks for your help.
Just one more question - the database is fully logged. Does it make
any difference to speed/transaction log size whether you recluster
the index in situ or in a new replacement table?
James|||If the database is in Simple or Bulk Logged mode the Creation and rebuilding
of the indexes can be minimally logged and faster as a result. Just be sure
to switch back to Full and take a full backup when done.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:64479e9a-191e-4445-b52d-4a9a89ef8a13@.i29g2000prf.googlegroups.com...
On Feb 27, 2:03 am, "jason" <jason-r3m...@.statisticsio.com> wrote:
> Jim,
> I have done similar(composite nonunique CI's for ranges) with good results
> but it sounds like this is a big deal so I would test as much as possible.
> I
> would also consider your NCI's during testing. Does adding a column or two
> to an NCI cover more queries? 70% on the fillfactor sounds low to me if
> you
> are just adding 5k rows\day. How often are you reindexing?
> --
> Jason Massie
> www:http://statisticsio.com
> rss:http://feeds.feedburner.com/statisticsio
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
>
> > Hi,
> > SQL Server 2000
> > I've got a table that doesn't change during the day but has inserts
> > overnight. currently 4 million rows in the table and I would say no
> > more than 5000 rows being added nightly. Db server is totally
> > overloaded and sometimes queries on this table take a long time.
> > Queries usually filter on a client_id and a input_date range. Input
> > date range is not normally very selective. There can be up to about
> > 100k rows per client_id. There are normally additional group by
> > clauses or further filters but they are not applied every time. Also
> > frequently used is a bit flag called billed. billed = 0 is very
> > selective (0-5%), billed = 1 is not (>95%). The clustering is
> > currently on the identity PK so is completely wasted - only benefit is
> > that the fill factor can be set to 90 as allrows are currently added
> > to the end of the table.
> > I want to change the clustering index to help improve performance of
> > queries. My initial thought is:
> > (client_id, input_date). Does this seem remotely sensible? Should it
> > be the other way round?
> > Given that I would always expect client_id to be used in a query,
> > would putting a non-clustered index on (billed) achieve anything? Or
> > on (client_id, billed)?
> > The suggested clustered index seems to work quite well and although
> > not making much difference to CPU time seems to cut logical reads by
> > about 40x. I think it will have more effect on Live which is much
> > busier and much less powerful than the dev server (yes I know that's
> > daft!)
> > I think this will increase fragmentation on the table. Should I be
> > worried about this - I intend to rebuild all indexes with a fillfactor
> > of 70? My plan would be to follow the following:
> > DROP all existing indexes and constraints
> > Create new clustered index with fillfactor of 70
> > recreate nonclustered indexes and constraints with fillfactor of 70
> > Is it okay to build a clustered index on a populated table or is it
> > better to create a new table, add clustered index and then populate,
> > then rename?
> > Many thanks.
> > Cheers,
> > James- Hide quoted text -
> - Show quoted text -
Hi Jason,
I checked the maintenance plans and they get rebuilt every Sunday with
a fixed free space percentage of 10% - so fillfactor is irrelevant.
Anyway following on from your comments I'll specify fillfactor as 90.
I've had a play with the indexes and the suggested clustered index
works extremely well. The nonclustered one I suggested on billed or
client_id, billed is never actually used as it always favours the
clustered index seek with a WHERE clause on the billed column.
Thanks for your help.
Just one more question - the database is fully logged. Does it make
any difference to speed/transaction log size whether you recluster
the index in situ or in a new replacement table?
James|||On Feb 27, 1:48=A0pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> If the database is in Simple or Bulk Logged mode the Creation and rebuildi=ng
> of the indexes can be minimally logged and faster as a result. Just be sur=e
> to switch back to Full and take a full backup when done.
> --
> Andrew J. Kelly =A0 =A0SQL MVP
> Solid Quality Mentors
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:64479e9a-191e-4445-b52d-4a9a89ef8a13@.i29g2000prf.googlegroups.com...
> On Feb 27, 2:03 am, "jason" <jason-r3m...@.statisticsio.com> wrote:
>
>
> > Jim,
> > I have done similar(composite nonunique CI's for ranges) with good resul=ts
> > but it sounds like this is a big deal so I would test as much as possibl=e.
> > I
> > would also consider your NCI's during testing. Does adding a column or t=wo
> > to an NCI cover more queries? 70% on the fillfactor sounds low to me if
> > you
> > are just adding 5k rows\day. How often are you reindexing?
> > --
> > Jason Massie
> > www:http://statisticsio.com
> > rss:http://feeds.feedburner.com/statisticsio
> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...=
> > > Hi,
> > > SQL Server 2000
> > > I've got a table that doesn't change during the day but has inserts
> > > overnight. currently 4 million rows in the table and I would say no
> > > more than 5000 rows being added nightly. Db server is totally
> > > overloaded and sometimes queries on this table take a long time.
> > > Queries usually filter on a client_id and a input_date range. Input
> > > date range is not normally very selective. There can be up to about
> > > 100k rows per client_id. There are normally additional group by
> > > clauses or further filters but they are not applied every time. Also
> > > frequently used is a bit flag called billed. billed =3D 0 is very
> > > selective (0-5%), billed =3D 1 is not (>95%). The clustering is
> > > currently on the identity PK so is completely wasted - only benefit is=
> > > that the fill factor can be set to 90 as allrows are currently added
> > > to the end of the table.
> > > I want to change the clustering index to help improve performance of
> > > queries. My initial thought is:
> > > (client_id, input_date). Does this seem remotely sensible? Should it
> > > be the other way round?
> > > Given that I would always expect client_id to be used in a query,
> > > would putting a non-clustered index on (billed) achieve anything? Or
> > > on (client_id, billed)?
> > > The suggested clustered index seems to work quite well and although
> > > not making much difference to CPU time seems to cut logical reads by
> > > about 40x. I think it will have more effect on Live which is much
> > > busier and much less powerful than the dev server (yes I know that's
> > > daft!)
> > > I think this will increase fragmentation on the table. Should I be
> > > worried about this - I intend to rebuild all indexes with a fillfactor=
> > > of 70? My plan would be to follow the following:
> > > DROP all existing indexes and constraints
> > > Create new clustered index with fillfactor of 70
> > > recreate nonclustered indexes and constraints with fillfactor of 70
> > > Is it okay to build a clustered index on a populated table or is it
> > > better to create a new table, add clustered index and then populate,
> > > then rename?
> > > Many thanks.
> > > Cheers,
> > > James- Hide quoted text -
> > - Show quoted text -
> Hi Jason,
> I checked the maintenance plans and they get rebuilt every Sunday with
> a fixed free space percentage of 10% - so fillfactor is irrelevant.
> Anyway following on from your comments I'll specify fillfactor as 90.
> I've had a play with the indexes and the suggested clustered index
> works extremely well. The nonclustered one I suggested on billed =A0or
> client_id, billed is never actually used as it always favours the
> clustered index seek with a WHERE clause on the billed column.
> Thanks for your help.
> Just one more question - the database is fully logged. Does it make
> any difference =A0to speed/transaction log size whether you recluster
> the index in situ or in a new replacement table?
> James- Hide quoted text -
> - Show quoted text -
Thanks. I'll do that.
What happens if a transaction log backup runs before the next full
backup? Will it fall over or just be corrupt? If it falls over, does
it bring the db down?
In other words, can I just leave the maintenance plans running knowing
that a couple of log backups will fail or do I need to either disable
them or do an immediate manual full backup? We're set up for half
hourly log backups. I'll probably make the change just after 6pm with
users off the system and the nightly full backup is at 9pm. Noone will
be using the db in the meantime.
James|||<<What happens if a transaction log backup runs before the next full
backup?>>
It depends on version and details. If the db is in simple mode when you execute the BACKUP LOG
command, you get an error message; regardless of version.
If db was in full and you did log backups and you then put it to simple and then backup to full and
you now do a log backup, then:
In 2000, the log backup is produced, with a lame message (not error) that it isn't usable.
In 2005, you get an error message.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:19208e61-4e35-450e-8da2-d36208ad7cbe@.n75g2000hsh.googlegroups.com...
On Feb 27, 1:48 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> If the database is in Simple or Bulk Logged mode the Creation and rebuilding
> of the indexes can be minimally logged and faster as a result. Just be sure
> to switch back to Full and take a full backup when done.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:64479e9a-191e-4445-b52d-4a9a89ef8a13@.i29g2000prf.googlegroups.com...
> On Feb 27, 2:03 am, "jason" <jason-r3m...@.statisticsio.com> wrote:
>
>
> > Jim,
> > I have done similar(composite nonunique CI's for ranges) with good results
> > but it sounds like this is a big deal so I would test as much as possible.
> > I
> > would also consider your NCI's during testing. Does adding a column or two
> > to an NCI cover more queries? 70% on the fillfactor sounds low to me if
> > you
> > are just adding 5k rows\day. How often are you reindexing?
> > --
> > Jason Massie
> > www:http://statisticsio.com
> > rss:http://feeds.feedburner.com/statisticsio
> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
> > > Hi,
> > > SQL Server 2000
> > > I've got a table that doesn't change during the day but has inserts
> > > overnight. currently 4 million rows in the table and I would say no
> > > more than 5000 rows being added nightly. Db server is totally
> > > overloaded and sometimes queries on this table take a long time.
> > > Queries usually filter on a client_id and a input_date range. Input
> > > date range is not normally very selective. There can be up to about
> > > 100k rows per client_id. There are normally additional group by
> > > clauses or further filters but they are not applied every time. Also
> > > frequently used is a bit flag called billed. billed = 0 is very
> > > selective (0-5%), billed = 1 is not (>95%). The clustering is
> > > currently on the identity PK so is completely wasted - only benefit is
> > > that the fill factor can be set to 90 as allrows are currently added
> > > to the end of the table.
> > > I want to change the clustering index to help improve performance of
> > > queries. My initial thought is:
> > > (client_id, input_date). Does this seem remotely sensible? Should it
> > > be the other way round?
> > > Given that I would always expect client_id to be used in a query,
> > > would putting a non-clustered index on (billed) achieve anything? Or
> > > on (client_id, billed)?
> > > The suggested clustered index seems to work quite well and although
> > > not making much difference to CPU time seems to cut logical reads by
> > > about 40x. I think it will have more effect on Live which is much
> > > busier and much less powerful than the dev server (yes I know that's
> > > daft!)
> > > I think this will increase fragmentation on the table. Should I be
> > > worried about this - I intend to rebuild all indexes with a fillfactor
> > > of 70? My plan would be to follow the following:
> > > DROP all existing indexes and constraints
> > > Create new clustered index with fillfactor of 70
> > > recreate nonclustered indexes and constraints with fillfactor of 70
> > > Is it okay to build a clustered index on a populated table or is it
> > > better to create a new table, add clustered index and then populate,
> > > then rename?
> > > Many thanks.
> > > Cheers,
> > > James- Hide quoted text -
> > - Show quoted text -
> Hi Jason,
> I checked the maintenance plans and they get rebuilt every Sunday with
> a fixed free space percentage of 10% - so fillfactor is irrelevant.
> Anyway following on from your comments I'll specify fillfactor as 90.
> I've had a play with the indexes and the suggested clustered index
> works extremely well. The nonclustered one I suggested on billed or
> client_id, billed is never actually used as it always favours the
> clustered index seek with a WHERE clause on the billed column.
> Thanks for your help.
> Just one more question - the database is fully logged. Does it make
> any difference to speed/transaction log size whether you recluster
> the index in situ or in a new replacement table?
> James- Hide quoted text -
> - Show quoted text -
Thanks. I'll do that.
What happens if a transaction log backup runs before the next full
backup? Will it fall over or just be corrupt? If it falls over, does
it bring the db down?
In other words, can I just leave the maintenance plans running knowing
that a couple of log backups will fail or do I need to either disable
them or do an immediate manual full backup? We're set up for half
hourly log backups. I'll probably make the change just after 6pm with
users off the system and the nightly full backup is at 9pm. Noone will
be using the db in the meantime.
James

Changing Primary Key from nonclustered to clustered

Is it possible to modify a PrimaryKey Constraint from nonclustered to
clustered without dropping and redefining the Primary Key?
I'm 99% sure that I will need to drop and redefine but just thought that I
would ask since it is a major hassle to drop and redefine the Foreign Keys
that reference this Primary Key
Thanks in advance.
Nope, you have to drop and redefine it. You can use Enterprise Manager to
generate a script though. Make the change in the table designer and the Save
Script button is the third from the left.
Jacco Schalkwijk
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ex%23E5FkUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> Is it possible to modify a PrimaryKey Constraint from nonclustered to
> clustered without dropping and redefining the Primary Key?
> I'm 99% sure that I will need to drop and redefine but just thought that I
> would ask since it is a major hassle to drop and redefine the Foreign Keys
> that reference this Primary Key
> Thanks in advance.
>

Changing Primary Key from nonclustered to clustered

Is it possible to modify a PrimaryKey Constraint from nonclustered to
clustered without dropping and redefining the Primary Key?
I'm 99% sure that I will need to drop and redefine but just thought that I
would ask since it is a major hassle to drop and redefine the Foreign Keys
that reference this Primary Key
Thanks in advance.Nope, you have to drop and redefine it. You can use Enterprise Manager to
generate a script though. Make the change in the table designer and the Save
Script button is the third from the left.
Jacco Schalkwijk
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ex%23E5FkUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> Is it possible to modify a PrimaryKey Constraint from nonclustered to
> clustered without dropping and redefining the Primary Key?
> I'm 99% sure that I will need to drop and redefine but just thought that I
> would ask since it is a major hassle to drop and redefine the Foreign Keys
> that reference this Primary Key
> Thanks in advance.
>

Changing Non-Clustered Index to Clustered

Hi, I want to change a non-clustered index to a clustered index (on the PK).
But when I try doing this through EM, I get "Cannot convert a clustered
index to a nonclustered index using the DROP_EXISTING option". When I
remove the DROP EXISTING option, I still get an error. I should mention
that there a lot of other non clustered indexes on this table.
How can I change this to use a clustered index?
Thanks.Remove the foreign keys that refers to this table, drop the primary key,
re-create it as a non-clustered index and add back the foreign key. Sorry,
but those are the steps... :-(
Also, make sure no user is in the db as you will be without FK's for a
while.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SQL" <nospam@.asdfadsf.com> wrote in message
news:e3TVU217DHA.1640@.TK2MSFTNGP11.phx.gbl...
> Hi, I want to change a non-clustered index to a clustered index (on the
PK).
> But when I try doing this through EM, I get "Cannot convert a clustered
> index to a nonclustered index using the DROP_EXISTING option". When I
> remove the DROP EXISTING option, I still get an error. I should mention
> that there a lot of other non clustered indexes on this table.
> How can I change this to use a clustered index?
> Thanks.
>|||Probably the simplest way is to do this in Enterprise Manager. You can
make the change and save it, or make the change and view/save the script
it produces. You can then see all the steps that are required in your
particular case.
HTH,
Gert-Jan
SQL wrote:
> Hi, I want to change a non-clustered index to a clustered index (on the PK).
> But when I try doing this through EM, I get "Cannot convert a clustered
> index to a nonclustered index using the DROP_EXISTING option". When I
> remove the DROP EXISTING option, I still get an error. I should mention
> that there a lot of other non clustered indexes on this table.
> How can I change this to use a clustered index?
> Thanks.

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
NancyYes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.c..._parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:

> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used wa
s
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything el
se
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
Nancy
Yes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.co...parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:

> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used was
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything else
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
NancyYes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.com/rd_sql_server_startup_parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:
> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used was
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything else
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change location
s
of your drives in a clustered environment?
Any ideas?Same as in a non-clustered environment.
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:

> Same as in a non-clustered environment.
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only all
ow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>
>|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change locations
of your drives in a clustered environment?
Any ideas?
Same as in a non-clustered environment.
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>
|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:

> Same as in a non-clustered environment.
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only allow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>
>
|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change locations
of your drives in a clustered environment?
Any ideas?Same as in a non-clustered environment. :)
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:
> Same as in a non-clustered environment. :)
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only allow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
> >I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> > SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> > the
> > location of the data, now that the virtual server is functional I want to
> > change the location of the log files to a different resource of the
> > cluster
> > (different drive) but it is not letting me do so. Hoe do you change
> > locations
> > of your drives in a clustered environment?
> >
> > Any ideas?
> >
>
>|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing Data Folders In a clustered environment

All,
I need to move the OLAP data folder across disks. The problem is that we
are running on a cluster environment, and I cannot see how to change the Dat
a
Folder setting on the general tab of the properties dialog, and its greyed
out.
Is it simply a matter of changing this on the Cluster resources to point at
the new data folder or do I have to move anything else?
TIA
JasonThat and moving the data.
As this is a production system, and the whole point of a cluster is
high-availability, I recommend that you open up a PSS case and work with
them directly to get the right sequences of events. It would be easy to mess
things up and have the whole AS service go down.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Lees" <JasonLees@.discussions.microsoft.com> wrote in message
news:E373CF5F-A126-49B5-ACFA-E4FBF57A95AA@.microsoft.com...
> All,
> I need to move the OLAP data folder across disks. The problem is that we
> are running on a cluster environment, and I cannot see how to change the
Data
> Folder setting on the general tab of the properties dialog, and its greyed
> out.
> Is it simply a matter of changing this on the Cluster resources to point
at
> the new data folder or do I have to move anything else?
> TIA
> Jason
>
>

Changing Data Folders In a clustered environment

All,
I need to move the OLAP data folder across disks. The problem is that we
are running on a cluster environment, and I cannot see how to change the Data
Folder setting on the general tab of the properties dialog, and its greyed
out.
Is it simply a matter of changing this on the Cluster resources to point at
the new data folder or do I have to move anything else?
TIA
Jason
That and moving the data.
As this is a production system, and the whole point of a cluster is
high-availability, I recommend that you open up a PSS case and work with
them directly to get the right sequences of events. It would be easy to mess
things up and have the whole AS service go down.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Lees" <JasonLees@.discussions.microsoft.com> wrote in message
news:E373CF5F-A126-49B5-ACFA-E4FBF57A95AA@.microsoft.com...
> All,
> I need to move the OLAP data folder across disks. The problem is that we
> are running on a cluster environment, and I cannot see how to change the
Data
> Folder setting on the general tab of the properties dialog, and its greyed
> out.
> Is it simply a matter of changing this on the Cluster resources to point
at
> the new data folder or do I have to move anything else?
> TIA
> Jason
>
>