Load balancing with Sql Server 2005 in Active/Active mode

This is in regards to the MS SQL

Server 2005 Cluster testing.

I have the set-up in

Active/Passive configuration.

We decided upon moving to the Active/Active

configuration, did some research on the internet for the additional features

which we get with this configuration and what could be the benefits of this over

the Active/Passive configuration. We have a query regarding the Active/Active

configuration. Please find below the details on the

same:

The Current Setup in Active/Passive

configuration has the following details:

1)

There are two nodes “node 1” and

“node 2” setup on Virtual Server 2005.

2)

A cluster has been set up using

“node 1” and “node 2”.

3)

A SQL server default instance is

installed on “node 1”. While SQL server 2005 was installed, it created a

resource SQL server IP.

To connect to this SQL server

instance, our application uses SQL server IP created in step 3. (Please note

that this is the only IP through which we are able to connect to SQL cluster).

The fail-over features are working fine in this configuration. If “node 1” goes

down then the SQL server instance runs in “node 2”. This is taken care of by

cluster and is transparent to our application.

However, there is no provision for

load balancing as only one node is active at a

time.

After some research, we came across

Active/Active configuration which is supposed to support load

balancing.

We understand that in this

configuration, Step 1and 2 are similar to the Active/Passive configuration. The

only difference is in step 3 where an instance of SQL server is installed on

each node, thus providing two active nodes at a time. The failover works just

like in Active/Passive configuration.

As per the above information, the

Active/Active configuration seems to be similar to two SQL server instances

running independently.There will be two seperate databases and on failure of one instance other instance wont be able to cater to the requests designed for first instance, Thus providing no extra benefits from

cluster.

We require the information on how to

take benefits of the load balancing features in this configuration.

SQL Server 2005 doesn't support load-balancing in the way that you are describing, the main problem being that only one instance of SQL Server can access a database's data and log files at a time.

Have a look at these links for more info as to how load balancing can be achieved:

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1127807,00.html

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1133488,00.html

Chris

|||As mentioned, MS SQL Server does not provide a way to do "active/active" cluster.

What you have installed would be a "high availablity" cluster, ie if one goes down, the other picks up.

If you need load balancing, you install 2 unique SQL installations and run "transactional replication" between them to make the databases the same. Then use a 3rd load balancing device to balance between them, problaby using "sticky sessions".

If you need both, high availabilty and load balancing, you install TWO 2 node clusters (4 servers + load balancing device) with transactional replication between cluster 1 and cluster 2.

I always recommend getting bigger/faster/better hardware and using 1 cluster of 2 servers.

Load balancing with SQl server 2005 cluster in active/active configuration

This is in regards to the MS SQL

Server 2005 Cluster testing.

I have the set-up in

Active/Passive configuration.

We decided upon moving to the Active/Active

configuration, did some research on the internet for the additional features

which we get with this configuration and what could be the benefits of this over

the Active/Passive configuration. We have a query regarding the Active/Active

configuration. Please find below the details on the

same:

The Current Setup in Active/Passive

configuration has the following details:

1)

There are two nodes “node 1” and

“node 2” setup on Virtual Server 2005.

2)

A cluster has been set up using

“node 1” and “node 2”.

3)

A SQL server default instance is

installed on “node 1”. While SQL server 2005 was installed, it created a

resource SQL server IP.

To connect to this SQL server

instance, our application uses SQL server IP created in step 3. (Please note

that this is the only IP through which we are able to connect to SQL cluster).

The fail-over features are working fine in this configuration. If “node 1” goes

down then the SQL server instance runs in “node 2”. This is taken care of by

cluster and is transparent to our application.

However, there is no provision for

load balancing as only one node is active at a

time.

After some research, we came across

Active/Active configuration which is supposed to support load

balancing.

We understand that in this

configuration, Step 1and 2 are similar to the Active/Passive configuration. The

only difference is in step 3 where an instance of SQL server is installed on

each node, thus providing two active nodes at a time. The failover works just

like in Active/Passive configuration.

As

per the above information, the Active/Active configuration seems to be

similar to two SQL server instances running independently.There will be

two seperate databases and on failure of one instance other instance

wont be able to cater to the requests designed for first instance, Thus

providing no extra benefits from cluster.

We require the information on how to

take benefits of the load balancing features in this configuration.

Hi geetu...

MSCS (Microsoft Cluster Services) is not a load-balancing product, it is simply a high-availability solution, period...load balancing does not come in to play at all with clustering a Sql database...the term Active/Active seems to imply that this would be the case, which is why we typically try to refer to them as Multi-instance clusters now instead of the Active/Active label. Active/Active in the MSCS world basically means that you have 2 independent Sql Server instances running on 2 cluster nodes - these instances are independent of each other in all respects, obviously unless you link them in some manner with custom business logic, replication, etc. Think of them for all intents and purposes as 2 seperate instances running on seperate servers at all times (as that's what they really are)...the only difference being that in case of a physical node failure (or service failure on a node), the instance will be moved to and hosted on a second physical server.

I'd be curious to know what research you came across that implied load-balancing as a feature with MSCS so we can try and get it corrected, or possibly clarify what position the author was taking.

To support load-balancing, or scaling out in a Sql Server environment you have a couple of different options, depending on your edition, environment, version of Sql, etc. Take a look at the following articles for a start:

http://msdn2.microsoft.com/en-us/library/aa479364.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

HTH

|||

Hi Geetu,

Basically, Active/Active Cluster is two Active/Passive Clusters.

As mentioned by Chad, they are completely independent of each other.

HTH

Jag

|||

I hope you got more information from Chad's reply here and as suggested you might need another solution, cluster will not provide the load balancing.

These 2 links shoudl give you information in this regard:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog04.mspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0513.mspx

http://www.sql-server-performance.com/dk_massive_scalability.asp

|||

It's pretty clear what geetu is trying to get at. Here is your answer geetu:

1. Instead of having the 2nd node idle until something happens to node1, you can split the databases between the 2 nodes.

2. While this is not the definition of classic Load Balancing, it does help balance the load between the two nodes while they are both up.

We have several customers who are configured like this and it works well.

The downside? The “size” of each of the 2 servers in terms of CPU, memory etc must be enough to handle all the databases that are normally served from the 2 nodes in case one node fails.

While this is the same requirement in the active/passive configuration, there is a danger that over time you will keeps adding load to both nodes independently and at time of failure, the failover node will die as well because of a sudden overwhelming load.

Sometimes when smarty MSFT employees respond, they should be more respectful to the person who is posting a question, instead of being arrogant.

sql

Load balancing with SQl server 2005 cluster in active/active configuration

This is in regards to the MS SQL Server 2005 Cluster testing.

I have the set-up in Active/Passive configuration.

We decided upon moving to the Active/Active configuration, did some research on the internet for the additional features which we get with this configuration and what could be the benefits of this over the Active/Passive configuration. We have a query regarding the Active/Active configuration. Please find below the details on the same:

The Current Setup in Active/Passive configuration has the following details:

1) There are two nodes “node 1” and “node 2” setup on Virtual Server 2005.

2) A cluster has been set up using “node 1” and “node 2”.

3) A SQL server default instance is installed on “node 1”. While SQL server 2005 was installed, it created a resource SQL server IP.

To connect to this SQL server instance, our application uses SQL server IP created in step 3. (Please note that this is the only IP through which we are able to connect to SQL cluster). The fail-over features are working fine in this configuration. If “node 1” goes down then the SQL server instance runs in “node 2”. This is taken care of by cluster and is transparent to our application.

However, there is no provision for load balancing as only one node is active at a time.

After some research, we came across Active/Active configuration which is supposed to support load balancing.

We understand that in this configuration, Step 1and 2 are similar to the Active/Passive configuration. The only difference is in step 3 where an instance of SQL server is installed on each node, thus providing two active nodes at a time. The failover works just like in Active/Passive configuration.

As per the above information, the Active/Active configuration seems to be similar to two SQL server instances running independently.There will be two seperate databases and on failure of one instance other instance wont be able to cater to the requests designed for first instance, Thus providing no extra benefits from cluster.

We require the information on how to take benefits of the load balancing features in this configuration.

Hi geetu...

MSCS (Microsoft Cluster Services) is not a load-balancing product, it is simply a high-availability solution, period...load balancing does not come in to play at all with clustering a Sql database...the term Active/Active seems to imply that this would be the case, which is why we typically try to refer to them as Multi-instance clusters now instead of the Active/Active label. Active/Active in the MSCS world basically means that you have 2 independent Sql Server instances running on 2 cluster nodes - these instances are independent of each other in all respects, obviously unless you link them in some manner with custom business logic, replication, etc. Think of them for all intents and purposes as 2 seperate instances running on seperate servers at all times (as that's what they really are)...the only difference being that in case of a physical node failure (or service failure on a node), the instance will be moved to and hosted on a second physical server.

I'd be curious to know what research you came across that implied load-balancing as a feature with MSCS so we can try and get it corrected, or possibly clarify what position the author was taking.

To support load-balancing, or scaling out in a Sql Server environment you have a couple of different options, depending on your edition, environment, version of Sql, etc. Take a look at the following articles for a start:

http://msdn2.microsoft.com/en-us/library/aa479364.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

HTH

|||

Hi Geetu,

Basically, Active/Active Cluster is two Active/Passive Clusters.

As mentioned by Chad, they are completely independent of each other.

HTH

Jag

|||

I hope you got more information from Chad's reply here and as suggested you might need another solution, cluster will not provide the load balancing.

These 2 links shoudl give you information in this regard:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog04.mspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0513.mspx

http://www.sql-server-performance.com/dk_massive_scalability.asp

|||

It's pretty clear what geetu is trying to get at. Here is your answer geetu:

1. Instead of having the 2nd node idle until something happens to node1, you can split the databases between the 2 nodes.

2. While this is not the definition of classic Load Balancing, it does help balance the load between the two nodes while they are both up.

We have several customers who are configured like this and it works well.

The downside? The “size” of each of the 2 servers in terms of CPU, memory etc must be enough to handle all the databases that are normally served from the 2 nodes in case one node fails.

While this is the same requirement in the active/passive configuration, there is a danger that over time you will keeps adding load to both nodes independently and at time of failure, the failover node will die as well because of a sudden overwhelming load.

Sometimes when smarty MSFT employees respond, they should be more respectful to the person who is posting a question, instead of being arrogant.

Load Balancing

need advice for below scenario

currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.

incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.

with regards

alby peter

The same options are available that would be available for a non-clustered system. Perhaps you can split your data into two separate sets on two separate instances and use distributed partitioned views (DPVs) or perhaps Peer-to-peer transactional replication to work with the data on each instance. Perhaps data dependent routing (DDR) can be used to have your client connect to the instance that likely has the data you want, and linked servers and DPVs can be used to access data that's on the other instance (or split across the two instances).

Don

|||

Dear Don,

thanks for the response.

i read about the above solutions is this type of real time replication in practice for real time servers. in peer to peer transactional replication how many servers could be idle for the best result.

thanks in advance

alby

|||

Replication does have some amount of latency, and though it might only be sub-second, you do need to evaluate your real-time requirements. DPVs access the actual data in its original location so there's no latency there.

I'm not sure what you mean by 'how many servers should be idle' .. I'd think you'd distribute the data across as many instances as are appropriate to meet your retrieval requirements for the given hardware. If your idle servers are 'passive nodes' within a failover cluster, then that depends on the reliability of your hardware and how crippled you'd be if two (or more) of your instances had to run on the same node.

Don

Load Balancing

need advice for below scenario

currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.

incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.

with regards

alby peter

The same options are available that would be available for a non-clustered system. Perhaps you can split your data into two separate sets on two separate instances and use distributed partitioned views (DPVs) or perhaps Peer-to-peer transactional replication to work with the data on each instance. Perhaps data dependent routing (DDR) can be used to have your client connect to the instance that likely has the data you want, and linked servers and DPVs can be used to access data that's on the other instance (or split across the two instances).

Don

|||

Dear Don,

thanks for the response.

i read about the above solutions is this type of real time replication in practice for real time servers. in peer to peer transactional replication how many servers could be idle for the best result.

thanks in advance

alby

|||

Replication does have some amount of latency, and though it might only be sub-second, you do need to evaluate your real-time requirements. DPVs access the actual data in its original location so there's no latency there.

I'm not sure what you mean by 'how many servers should be idle' .. I'd think you'd distribute the data across as many instances as are appropriate to meet your retrieval requirements for the given hardware. If your idle servers are 'passive nodes' within a failover cluster, then that depends on the reliability of your hardware and how crippled you'd be if two (or more) of your instances had to run on the same node.

Don

sql

Lo Disk Space-archive?- index reduction-slow server, need assistan

Drive space is being eaten away and we are still a couple of weeks from
moving to a newer larger server. There is concern over what is filling up
the data drives (not log) so quickly and there is a list of things to do that
are pretty drastic...
1-Drop indexes - not sure how to do this correctly - can indexes be dropped
if an index with the same number of items in it plus some extras are already
existing - and will this retrieve some disk space?
2-Archive more data to the data warehouse - most all that can be archived is
archived but there may be more we can do
3-Add ram - already done to the max
4-change all switches to gigabit switches (in process)
5-can the 10 templog files that are on the E:\ drive be removed and all temp
file logs go to the F:\ drive where space is adequate (both are raid 5) - not
sure how to remove the ten on the E drive - the big one is already on F:\
6-Can't think of one but would like to hear feedback
SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
\PAE boot.ini switch. Running Four processors...with affinity mask for all
400 maximum worker threads with Boost SQL Server - single processor used for
parallel processing, memory configured dynamically with a 1 Meg Minimum and
using configured values
--
Regards,
JamieHi
I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> Drive space is being eaten away and we are still a couple of weeks from
> moving to a newer larger server. There is concern over what is filling up
> the data drives (not log) so quickly and there is a list of things to do
> that
> are pretty drastic...
> 1-Drop indexes - not sure how to do this correctly - can indexes be
> dropped
> if an index with the same number of items in it plus some extras are
> already
> existing - and will this retrieve some disk space?
> 2-Archive more data to the data warehouse - most all that can be archived
> is
> archived but there may be more we can do
> 3-Add ram - already done to the max
> 4-change all switches to gigabit switches (in process)
> 5-can the 10 templog files that are on the E:\ drive be removed and all
> temp
> file logs go to the F:\ drive where space is adequate (both are raid 5) -
> not
> sure how to remove the ten on the E drive - the big one is already on F:\
> 6-Can't think of one but would like to hear feedback
> SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> \PAE boot.ini switch. Running Four processors...with affinity mask for
> all
> 400 maximum worker threads with Boost SQL Server - single processor used
> for
> parallel processing, memory configured dynamically with a 1 Meg Minimum
> and
> using configured values
> --
> Regards,
> Jamie|||No, not tempdb. It was actually our EDI server which accepts transmission
from partners. We're just running on the edge of space and time. I'm
looking for any improvement I can get. I optimized some views and added
indexing for them but adding more indexes will also add more disk space. I
don't know how to remove (other than random delete) indexes if they are not
needed. What would be the criteria to drop an index? IS there a way to
tell it is no longer needed? To the statistics tell me anything?
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> > Drive space is being eaten away and we are still a couple of weeks from
> > moving to a newer larger server. There is concern over what is filling up
> > the data drives (not log) so quickly and there is a list of things to do
> > that
> > are pretty drastic...
> > 1-Drop indexes - not sure how to do this correctly - can indexes be
> > dropped
> > if an index with the same number of items in it plus some extras are
> > already
> > existing - and will this retrieve some disk space?
> > 2-Archive more data to the data warehouse - most all that can be archived
> > is
> > archived but there may be more we can do
> > 3-Add ram - already done to the max
> > 4-change all switches to gigabit switches (in process)
> > 5-can the 10 templog files that are on the E:\ drive be removed and all
> > temp
> > file logs go to the F:\ drive where space is adequate (both are raid 5) -
> > not
> > sure how to remove the ten on the E drive - the big one is already on F:\
> > 6-Can't think of one but would like to hear feedback
> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> > \PAE boot.ini switch. Running Four processors...with affinity mask for
> > all
> > 400 maximum worker threads with Boost SQL Server - single processor used
> > for
> > parallel processing, memory configured dynamically with a 1 Meg Minimum
> > and
> > using configured values
> > --
> > Regards,
> > Jamie
>
>|||Hi
I'd turn on SQL Server Profiler and try to identify long running queries
(group by Duration/Reads/CPU) and then you have to optimize them (add/drop
indexes)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
> No, not tempdb. It was actually our EDI server which accepts transmission
> from partners. We're just running on the edge of space and time. I'm
> looking for any improvement I can get. I optimized some views and added
> indexing for them but adding more indexes will also add more disk space.
> I
> don't know how to remove (other than random delete) indexes if they are
> not
> needed. What would be the criteria to drop an index? IS there a way to
> tell it is no longer needed? To the statistics tell me anything?
> --
> Regards,
> Jamie
>
> "Uri Dimant" wrote:
>> Hi
>> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>>
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
>> > Drive space is being eaten away and we are still a couple of weeks from
>> > moving to a newer larger server. There is concern over what is filling
>> > up
>> > the data drives (not log) so quickly and there is a list of things to
>> > do
>> > that
>> > are pretty drastic...
>> > 1-Drop indexes - not sure how to do this correctly - can indexes be
>> > dropped
>> > if an index with the same number of items in it plus some extras are
>> > already
>> > existing - and will this retrieve some disk space?
>> > 2-Archive more data to the data warehouse - most all that can be
>> > archived
>> > is
>> > archived but there may be more we can do
>> > 3-Add ram - already done to the max
>> > 4-change all switches to gigabit switches (in process)
>> > 5-can the 10 templog files that are on the E:\ drive be removed and all
>> > temp
>> > file logs go to the F:\ drive where space is adequate (both are raid
>> > 5) -
>> > not
>> > sure how to remove the ten on the E drive - the big one is already on
>> > F:\
>> > 6-Can't think of one but would like to hear feedback
>> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
>> > the
>> > \PAE boot.ini switch. Running Four processors...with affinity mask for
>> > all
>> > 400 maximum worker threads with Boost SQL Server - single processor
>> > used
>> > for
>> > parallel processing, memory configured dynamically with a 1 Meg Minimum
>> > and
>> > using configured values
>> > --
>> > Regards,
>> > Jamie
>>|||I've been doing just that - that is, except the part where you drop indexes.
If the profiler happens to optimize something that runs at 8 AM and it
doesn't run again til 5 AM and recommends that an index be dropped because it
doesn't see usage on it and then about noon, that index is needed - but it
has been dropped and the system goes to its knees, I'm better off living with
the current issues. I think the answer is to take an entire day of profile
and optimize against that. Seems a bit extreme. Is there another way?
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> I'd turn on SQL Server Profiler and try to identify long running queries
> (group by Duration/Reads/CPU) and then you have to optimize them (add/drop
> indexes)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
> > No, not tempdb. It was actually our EDI server which accepts transmission
> > from partners. We're just running on the edge of space and time. I'm
> > looking for any improvement I can get. I optimized some views and added
> > indexing for them but adding more indexes will also add more disk space.
> > I
> > don't know how to remove (other than random delete) indexes if they are
> > not
> > needed. What would be the criteria to drop an index? IS there a way to
> > tell it is no longer needed? To the statistics tell me anything?
> > --
> > Regards,
> > Jamie
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
> >>
> >>
> >>
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> >> > Drive space is being eaten away and we are still a couple of weeks from
> >> > moving to a newer larger server. There is concern over what is filling
> >> > up
> >> > the data drives (not log) so quickly and there is a list of things to
> >> > do
> >> > that
> >> > are pretty drastic...
> >> > 1-Drop indexes - not sure how to do this correctly - can indexes be
> >> > dropped
> >> > if an index with the same number of items in it plus some extras are
> >> > already
> >> > existing - and will this retrieve some disk space?
> >> > 2-Archive more data to the data warehouse - most all that can be
> >> > archived
> >> > is
> >> > archived but there may be more we can do
> >> > 3-Add ram - already done to the max
> >> > 4-change all switches to gigabit switches (in process)
> >> > 5-can the 10 templog files that are on the E:\ drive be removed and all
> >> > temp
> >> > file logs go to the F:\ drive where space is adequate (both are raid
> >> > 5) -
> >> > not
> >> > sure how to remove the ten on the E drive - the big one is already on
> >> > F:\
> >> > 6-Can't think of one but would like to hear feedback
> >> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
> >> > the
> >> > \PAE boot.ini switch. Running Four processors...with affinity mask for
> >> > all
> >> > 400 maximum worker threads with Boost SQL Server - single processor
> >> > used
> >> > for
> >> > parallel processing, memory configured dynamically with a 1 Meg Minimum
> >> > and
> >> > using configured values
> >> > --
> >> > Regards,
> >> > Jamie
> >>
> >>
> >>
>
>|||Hi
http://www.sql-server-performance.com/articles/per/finding_duplicate_indexes_p1.aspx
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:7A3271B1-31DB-45A4-8882-89E3AD40D5BD@.microsoft.com...
> I've been doing just that - that is, except the part where you drop
> indexes.
> If the profiler happens to optimize something that runs at 8 AM and it
> doesn't run again til 5 AM and recommends that an index be dropped because
> it
> doesn't see usage on it and then about noon, that index is needed - but it
> has been dropped and the system goes to its knees, I'm better off living
> with
> the current issues. I think the answer is to take an entire day of
> profile
> and optimize against that. Seems a bit extreme. Is there another way?
> --
> Regards,
> Jamie
>
> "Uri Dimant" wrote:
>> Hi
>> I'd turn on SQL Server Profiler and try to identify long running queries
>> (group by Duration/Reads/CPU) and then you have to optimize them
>> (add/drop
>> indexes)
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
>> > No, not tempdb. It was actually our EDI server which accepts
>> > transmission
>> > from partners. We're just running on the edge of space and time. I'm
>> > looking for any improvement I can get. I optimized some views and
>> > added
>> > indexing for them but adding more indexes will also add more disk
>> > space.
>> > I
>> > don't know how to remove (other than random delete) indexes if they are
>> > not
>> > needed. What would be the criteria to drop an index? IS there a way
>> > to
>> > tell it is no longer needed? To the statistics tell me anything?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>> >>
>> >>
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
>> >> > Drive space is being eaten away and we are still a couple of weeks
>> >> > from
>> >> > moving to a newer larger server. There is concern over what is
>> >> > filling
>> >> > up
>> >> > the data drives (not log) so quickly and there is a list of things
>> >> > to
>> >> > do
>> >> > that
>> >> > are pretty drastic...
>> >> > 1-Drop indexes - not sure how to do this correctly - can indexes be
>> >> > dropped
>> >> > if an index with the same number of items in it plus some extras are
>> >> > already
>> >> > existing - and will this retrieve some disk space?
>> >> > 2-Archive more data to the data warehouse - most all that can be
>> >> > archived
>> >> > is
>> >> > archived but there may be more we can do
>> >> > 3-Add ram - already done to the max
>> >> > 4-change all switches to gigabit switches (in process)
>> >> > 5-can the 10 templog files that are on the E:\ drive be removed and
>> >> > all
>> >> > temp
>> >> > file logs go to the F:\ drive where space is adequate (both are raid
>> >> > 5) -
>> >> > not
>> >> > sure how to remove the ten on the E drive - the big one is already
>> >> > on
>> >> > F:\
>> >> > 6-Can't think of one but would like to hear feedback
>> >> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
>> >> > the
>> >> > \PAE boot.ini switch. Running Four processors...with affinity mask
>> >> > for
>> >> > all
>> >> > 400 maximum worker threads with Boost SQL Server - single processor
>> >> > used
>> >> > for
>> >> > parallel processing, memory configured dynamically with a 1 Meg
>> >> > Minimum
>> >> > and
>> >> > using configured values
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >>
>> >>
>> >>
>>|||See in-line for comments:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> Drive space is being eaten away and we are still a couple of weeks from
> moving to a newer larger server. There is concern over what is filling up
> the data drives (not log) so quickly and there is a list of things to do
> that
> are pretty drastic...
I hope the new server is better configured than the current one or you will
have similar issues. From the following comments you posted this system does
not appear to be configured properly. If you carry these same concepts over
to the new machine you may not have gained much. Before you change any
setting from the default you beet have a valid reason and know what that
change will do in advance.
> 1-Drop indexes - not sure how to do this correctly - can indexes be
> dropped
> if an index with the same number of items in it plus some extras are
> already
> existing - and will this retrieve some disk space?
That is too general a question to answer correctly with a yes or no. If the
index is truely duplicate then yes it can be removed. If you have indexes
with lots of columns then unless they are really being used effectively as
covering indexes you may be able to drop them in favor of smaller but
similar indexes. If for instance the smaller index is very selective already
the other columns may not add more overhead than necessary if again they are
not effective as a covering index.
> 2-Archive more data to the data warehouse - most all that can be archived
> is
> archived but there may be more we can do
Only you can decide that
> 3-Add ram - already done to the max
What will that do to alleviate your disk space issues?
> 4-change all switches to gigabit switches (in process)
Again that does nothign for disk space issues but is never a bad idea.
> 5-can the 10 templog files that are on the E:\ drive be removed and all
> temp
> file logs go to the F:\ drive where space is adequate (both are raid 5) -
> not
> sure how to remove the ten on the E drive - the big one is already on F:\
Why do you have 10 log files for any database? You gain nothing by having
more than 1 log file per db on a single array.
> 6-Can't think of one but would like to hear feedback
> SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> \PAE boot.ini switch. Running Four processors...with affinity mask for
> all
> 400 maximum worker threads with Boost SQL Server - single processor used
> for
> parallel processing, memory configured dynamically with a 1 Meg Minimum
> and
> using configured values
SQL 2000 Std will only use 2GB max so the 6GB you have is mostly wasted. Why
do you have the affinity mask set? Why did you change the MAX worker thread
count? Turn off the Boost priority.
> --
> Regards,
> Jamie

list of common maintenance tasks?

I'm new to SQL Server after moving an online database from Access to SQL
Server. My database is about 170mb now, and I really don't have any idea
what I need to be doing on a regular basis to keep it 'well-oiled'. What
are the common maintenance tasks in order to keep it running as quickly and
as efficiently as possible?
Any help or pointers to help would be great appreciated.
Thanks,
GlennThe place to start is to open up SQL Enterprise Manager. Select your server
and then choose the Tools | Database Maintenance Planner option. Then
follow the instruction on the Wizard screens.
Mike O.
"Glenn Carr" <glenn@.nospam-glenncarr.com> wrote in message
news:%23TWbJns2DHA.3436@.tk2msftngp13.phx.gbl...
> I'm new to SQL Server after moving an online database from Access to SQL
> Server. My database is about 170mb now, and I really don't have any idea
> what I need to be doing on a regular basis to keep it 'well-oiled'. What
> are the common maintenance tasks in order to keep it running as quickly
and
> as efficiently as possible?
> Any help or pointers to help would be great appreciated.
> Thanks,
> Glenn
>|||It will take a few hours, but please read the SQL Server 2000 Maintenance
and Operations Guide at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Glenn Carr" <glenn@.nospam-glenncarr.com> wrote in message
news:%23TWbJns2DHA.3436@.tk2msftngp13.phx.gbl...
> I'm new to SQL Server after moving an online database from Access to SQL
> Server. My database is about 170mb now, and I really don't have any idea
> what I need to be doing on a regular basis to keep it 'well-oiled'. What
> are the common maintenance tasks in order to keep it running as quickly
and
> as efficiently as possible?
> Any help or pointers to help would be great appreciated.
> Thanks,
> Glenn
>

list of common maintenance tasks?

I'm new to SQL Server after moving an online database from Access to SQL
Server. My database is about 170mb now, and I really don't have any idea
what I need to be doing on a regular basis to keep it 'well-oiled'. What
are the common maintenance tasks in order to keep it running as quickly and
as efficiently as possible?
Any help or pointers to help would be great appreciated.
Thanks,
GlennThe place to start is to open up SQL Enterprise Manager. Select your server
and then choose the Tools | Database Maintenance Planner option. Then
follow the instruction on the Wizard screens.
Mike O.
"Glenn Carr" <glenn@.nospam-glenncarr.com> wrote in message
news:%23TWbJns2DHA.3436@.tk2msftngp13.phx.gbl...

quote:

---

> I'm new to SQL Server after moving an online database from Access to SQL
> Server. My database is about 170mb now, and I really don't have any idea
> what I need to be doing on a regular basis to keep it 'well-oiled'. What
> are the common maintenance tasks in order to keep it running as quickly

---

and

quote:

---

> as efficiently as possible?
> Any help or pointers to help would be great appreciated.
> Thanks,
> Glenn
>

---

|||It will take a few hours, but please read the SQL Server 2000 Maintenance
and Operations Guide at
http://www.microsoft.com/technet/tr...ide/default.asp
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Glenn Carr" <glenn@.nospam-glenncarr.com> wrote in message
news:%23TWbJns2DHA.3436@.tk2msftngp13.phx.gbl...

quote:

---

> I'm new to SQL Server after moving an online database from Access to SQL
> Server. My database is about 170mb now, and I really don't have any idea
> what I need to be doing on a regular basis to keep it 'well-oiled'. What
> are the common maintenance tasks in order to keep it running as quickly

---

and

quote:

---

> as efficiently as possible?
> Any help or pointers to help would be great appreciated.
> Thanks,
> Glenn
>

---