Hoe to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

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

|||

Microsoft has a document published on how to install Integration Services on a cluster.

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

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?sql

Hoe to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

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

|||

Microsoft has a document published on how to install Integration Services on a cluster.

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

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?

highly appreciated.

I am trying to connect the named instance from my client computer... I can
easily connect the default instance of my remote machine but when I tried to
connect through named instance it gave me an error that SQL Server doesn't
exist or access denied...
In the client network utility I have defined the IP and alias of that server
but failed to connect.
Can any one give their expert idea.
Thanks
Roy wrote:
> I am trying to connect the named instance from my client computer...
> I can easily connect the default instance of my remote machine but
> when I tried to connect through named instance it gave me an error
> that SQL Server doesn't exist or access denied...
> In the client network utility I have defined the IP and alias of that
> server but failed to connect.
> Can any one give their expert idea.
> Thanks
Why are you defining an alias? Was it because you had no luck connecting
using the named instance? The named instance runs on a different port
than the default 1433. I think it might use 1434, but am not sure. Try
using the instance name first before resorting to using an alias. For
example, if you remote computer name is MY_COMPUTER and the named
instance is SQL2, try the following as the server name:
MY_COMPUTER\SQL2
If you still have trouble, it could be a firewall issue. Try temporarily
disabling any firewall software and see if that helps.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||First of all David, thanks for your reply...
I am at client computer and how client computer diagnose MY_COMPUTER\SQL2
until and unless I have to mention that the ip which is running behind the
MY_Computer... this My_Computer doesn't exist on the network... that's why I
have to mention this My_Computer IP in the client network utility so that it
can easily diagnose it... I am mentioning you what I did for connecting named
instance... if I am wrong then please correct me...
Step 1: I did findout the port No of named instance through SQL Server
Network Utility that was 1434 right.
Step 2: I have mantioned that port and IP in client network utility like
Netowork Utility > Alias >
Alias: RemoteServer
Server: 209.45.23.55
Port : 1434
Protocol: TCP/IP
when I registered this named instance server through enterprise manager I
have defiled RemoteServer/Instancename and then trying to connect but
failed... sql server doesn't exist or access denied...
David, as you said I have to connect simply through MY_COMPUTER\SQL2 but how
my client machine know MY_Computer IP ?
I hope you understand what I wanna tell you... I would really appreciate if
you reply me ASAP... thanks and have a great day,
"David Gugick" wrote:

> Roy wrote:
> Why are you defining an alias? Was it because you had no luck connecting
> using the named instance? The named instance runs on a different port
> than the default 1433. I think it might use 1434, but am not sure. Try
> using the instance name first before resorting to using an alias. For
> example, if you remote computer name is MY_COMPUTER and the named
> instance is SQL2, try the following as the server name:
> MY_COMPUTER\SQL2
> If you still have trouble, it could be a firewall issue. Try temporarily
> disabling any firewall software and see if that helps.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||David, what I am thinking right now... do I need to create linked server
before registering the named instance on my machine ?
"David Gugick" wrote:

> Roy wrote:
> Why are you defining an alias? Was it because you had no luck connecting
> using the named instance? The named instance runs on a different port
> than the default 1433. I think it might use 1434, but am not sure. Try
> using the instance name first before resorting to using an alias. For
> example, if you remote computer name is MY_COMPUTER and the named
> instance is SQL2, try the following as the server name:
> MY_COMPUTER\SQL2
> If you still have trouble, it could be a firewall issue. Try temporarily
> disabling any firewall software and see if that helps.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Rogers wrote:
> David, what I am thinking right now... do I need to create linked
> server before registering the named instance on my machine ?
>
If you're just connecting over the internet (which I assume from your
last post that you are), you're correct that you'll need to use the IP
address. This just makes me think there's a firewall issue on the remote
PC or remote network preventing communications on that IP over port
1434. To determine this, you could swap ports on the remote server and
make the named instance 1433 and cycle the SQL Server. If you then
connect to the named instance, you'll know it's a firewall issue.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David, you are very right but how can I change the port of default instance
to 1434 and named instance to 1433 ... basically this is our production
machine and couple of databases are running to hit default instance.. what u
think if i do this then all would be in trouble right...
Looking forward to your reply.
Thanks
"David Gugick" wrote:

> Rogers wrote:
> If you're just connecting over the internet (which I assume from your
> last post that you are), you're correct that you'll need to use the IP
> address. This just makes me think there's a firewall issue on the remote
> PC or remote network preventing communications on that IP over port
> 1434. To determine this, you could swap ports on the remote server and
> make the named instance 1433 and cycle the SQL Server. If you then
> connect to the named instance, you'll know it's a firewall issue.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Rogers wrote:
> David, you are very right but how can I change the port of default
> instance to 1434 and named instance to 1433 ... basically this is our
> production machine and couple of databases are running to hit default
> instance.. what u think if i do this then all would be in trouble
> right...
>
Talk to your network guys and see if the firewall is in play.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Alright..... David, tell me one thing just for information... we can change
the default and named instance port through sql server network utilities
right ?
Thanks and looking forward to your reply
thanks david !
"David Gugick" wrote:

> Rogers wrote:
> Talk to your network guys and see if the firewall is in play.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Rogers wrote:
> Alright..... David, tell me one thing just for information... we can
> change the default and named instance port through sql server network
> utilities right ?
> Thanks and looking forward to your reply
You should be able to, but since your default instance is an in-use
production database, I'd caution doing so during production hours.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes but if both the databases are not in used then I can change the port of
named and default instance right through SQL Server Network Utlitiy .
Thanks
"David Gugick" wrote:

> Rogers wrote:
> You should be able to, but since your default instance is an in-use
> production database, I'd caution doing so during production hours.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

high memory usage

Hi, i have an sql server machine reported high memory usage (2.5 GB), and
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.
Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
http://msdn.microsoft.com/library/de...onfig_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>
|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?[vbcol=seagreen]
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
sql

high memory usage

Hi, i have an sql server machine reported high memory usage (2.5 GB), and
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.
Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
http://msdn.microsoft.com/library/de...onfig_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>
|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?[vbcol=seagreen]
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>

high memory usage

Hi, i have an sql server machine reported high memory usage (2.5 GB), and
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
g_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d... />
g_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/d...config_9zfy.asp
[vbcol=seagreen]
and[vbcol=seagreen]
>|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/d...config_9zfy.asp
[vbcol=seagreen]
and[vbcol=seagreen]
>

High CPU usage on 64bit (8 Way dual core) machine

Hi
I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
clustered environment (both machines in the cluster are identical) and the
machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580 G4's) -
presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
However the new cluster - which would have expected to manage the workload
much better epsecially in terms of CPU is constantly at about 80-90% cpu on
all processors, and frequntly in the high 90's - I've seen a couple of KB
articles relating to problems like this - however none seems to quite fit out
situation.
Anyone got any ideas ?I suspect that the lower CPU usage was due to slower disks and less memory.
In that case, the CPU has to wait for data. If it's not in cache, then it
has to go to disk. During this time, the CPU waits, and CPU usage goes to
0.
In your newer system, you have more cache, which reduces the need to go to
disk as often. Because the data is already available, then the CPU can be
put to work right away to service the query.
Has query performance suffered or are you just concerned that you see higher
CPU usage?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
Hi
I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
clustered environment (both machines in the cluster are identical) and the
machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580 G4's) -
presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
However the new cluster - which would have expected to manage the workload
much better epsecially in terms of CPU is constantly at about 80-90% cpu on
all processors, and frequntly in the high 90's - I've seen a couple of KB
articles relating to problems like this - however none seems to quite fit
out
situation.
Anyone got any ideas ?|||Rabbers
Can you tell us what did you check so far? Long running queries? Blocking
/Loking?
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
"Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> Hi
> I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> clustered environment (both machines in the cluster are identical) and the
> machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> G4's) -
> presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> However the new cluster - which would have expected to manage the workload
> much better epsecially in terms of CPU is constantly at about 80-90% cpu
> on
> all processors, and frequntly in the high 90's - I've seen a couple of KB
> articles relating to problems like this - however none seems to quite fit
> out
> situation.
> Anyone got any ideas ?
>|||I agree with Tom in that you most likely had other bottlenecks that
prevented your CPU's from being constantly higher in the last system. High
CPU usage usually means you are getting a lot of work done and is not
necessarily a bad thing. But it can also indicate you have poorly optimized
queries and are getting a lot of compiles or recompiles.
--
Andrew J. Kelly SQL MVP
"Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> Hi
> I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> clustered environment (both machines in the cluster are identical) and the
> machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> G4's) -
> presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> However the new cluster - which would have expected to manage the workload
> much better epsecially in terms of CPU is constantly at about 80-90% cpu
> on
> all processors, and frequntly in the high 90's - I've seen a couple of KB
> articles relating to problems like this - however none seems to quite fit
> out
> situation.
> Anyone got any ideas ?
>|||Interesting - I had not thought of it in those terms, but my team are also
telling me that query performance has suffered. Incidently the disk sub
system is the same as on the old sub system it is an EMC CX300 SAN dual path
in active/active configuration, There does not appear to be any IO waiting
going on so I'm not sure this is oir was an issue. Workload is unchanged.
No blocking or locking going on. Although some big numbers from Uri's query.
14131421676 9670 32 0x0500050053D8DE7840039DC5000000000000000000000000
8347158363 30999 9 0x050005009014281C40C3ADB5000000000000000000000000
2376266762 1270 7 0x05000B00A39BCF6240C3AEE7000000000000000000000000
2202179405 4480 32 0x050005001AB4EA7740C3EE07010000000000000000000000
1997588436 2379 8 0x05000B00F82EF35F4003F5A9020000000000000000000000
1597112198 30677 3 0x05000500A3463C3F40A307AC000000000000000000000000
1456992451 19956 3 0x05000500541E7401406392BC000000000000000000000000
984281776 43805 13 0x05000500052FD03740A3A1C6000000000000000000000000
962083727 112113 18 0x05000700942C7D3E406339EA000000000000000000000000
843149798 577 7 0x05000B003153E7604023B6E9000000000000000000000000
752809408 5511 1 0x0500050088E47D5740631FC1000000000000000000000000
730983354 594 7 0x050005005AC2F3344083D8D7000000000000000000000000
538219354 3533651 5 0x050005009C23030F400304CF000000000000000000000000
405080101 463791 2 0x050005002ADB1A0D40233ADC000000000000000000000000
404464827 8912 3 0x05000500DAA5E13A406343BC000000000000000000000000
354858971 12888 9 0x050005006B2FB9524083CBDB000000000000000000000000
354788856 20840 2 0x0500070022E4943C40C3B2AF000000000000000000000000
311644988 223882 2 0x05000500D5F2636C40234B8A000000000000000000000000
278456532 1001 2 0x05000500354FCD53406346E1000000000000000000000000
270333901 837513 1 0x05000500286B5F2B4003AFAB000000000000000000000000
219689259 548657 3 0x050007008240FE2340C3F5DE000000000000000000000000
209456887 313 36 0x05000500C520C77A40639DBF010000000000000000000000
203146055 70 6 0x05000B006A77DB614003F289010000000000000000000000
187539837 8882 3 0x05000B0002B2A34140A38900010000000000000000000000
183248652 158 1 0x060005006B51F52940439A48020000000000000000000000
151190159 4904 12 0x050005003EA8575C40437BDD000000000000000000000000
148130918 2873 1 0x05000B0029EA182840A3DFE1020000000000000000000000
129011430 591 10 0x05000500FBC78E0540A3AB0A020000000000000000000000
122411767 9145 38 0x05000500A9B1977E4023BCD7000000000000000000000000
103452720 1947 4 0x05000500FC0D240E40A34CD7000000000000000000000000
99285280 433203 1 0x05000500162A4D6D404339BE000000000000000000000000
97455900 77 1 0x060005003E8B77394063E000030000000000000000000000
96981407 90 1 0x0600050061E19A1B40A3C087000000000000000000000000
94693286 9 9 0x05000600309B09704003E311030000000000000000000000
89323421 74 1 0x060005009BAE130440A3B7D8020000000000000000000000
75662449 1117974 2 0x05000500D547F70F40837AD0000000000000000000000000
74451891 84 4 0x06000B00CFFD2D3840C3FBF9000000000000000000000000
72410847 80 4 0x06000B0047B9FB1940A3FB6F010000000000000000000000
72244371 58 1 0x0600050005018F074083E895020000000000000000000000
70044701 5036752 1 0x05000B00B0B71B6A40A374E7000000000000000000000000
68605351 42 7 0x060005003488F60C4003CEE5020000000000000000000000
65495930 158 1 0x06000500FD821E3240233246020000000000000000000000
65255754 7922 8 0x05000500F9E6D05040C3D2D9000000000000000000000000
62199510 107 1 0x06000500A41D2C1E40E34E1A020000000000000000000000
60560005 34 2 0x06000B00D37CFA2240E3FAA7000000000000000000000000
56735759 127554 9 0x05000500FB72FB614043C1AB000000000000000000000000
54106604 110763 6 0x05000500735F631740E3A1C6000000000000000000000000
53255770 6 6 0x06000500C5224C2640833421020000000000000000000000
50681958 202 8 0x05000500B7F7095F40A337BA000000000000000000000000
50605253 1222 9 0x05000B00C3949C6940C30FC2000000000000000000000000
"Tom Moreau" wrote:
> I suspect that the lower CPU usage was due to slower disks and less memory.
> In that case, the CPU has to wait for data. If it's not in cache, then it
> has to go to disk. During this time, the CPU waits, and CPU usage goes to
> 0.
> In your newer system, you have more cache, which reduces the need to go to
> disk as often. Because the data is already available, then the CPU can be
> put to work right away to service the query.
> Has query performance suffered or are you just concerned that you see higher
> CPU usage?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> Hi
> I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> clustered environment (both machines in the cluster are identical) and the
> machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580 G4's) -
> presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> However the new cluster - which would have expected to manage the workload
> much better epsecially in terms of CPU is constantly at about 80-90% cpu on
> all processors, and frequntly in the high 90's - I've seen a couple of KB
> articles relating to problems like this - however none seems to quite fit
> out
> situation.
> Anyone got any ideas ?
>
>|||You may want to start profiling for long-running queries and/or those
queries that are using a lot of CPU.
BTW what service pack are you running?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
news:105535AF-20A9-41A4-A320-AD35C4CF28CA@.microsoft.com...
Interesting - I had not thought of it in those terms, but my team are also
telling me that query performance has suffered. Incidently the disk sub
system is the same as on the old sub system it is an EMC CX300 SAN dual path
in active/active configuration, There does not appear to be any IO waiting
going on so I'm not sure this is oir was an issue. Workload is unchanged.
No blocking or locking going on. Although some big numbers from Uri's query.
14131421676 9670 32 0x0500050053D8DE7840039DC5000000000000000000000000
8347158363 30999 9 0x050005009014281C40C3ADB5000000000000000000000000
2376266762 1270 7 0x05000B00A39BCF6240C3AEE7000000000000000000000000
2202179405 4480 32 0x050005001AB4EA7740C3EE07010000000000000000000000
1997588436 2379 8 0x05000B00F82EF35F4003F5A9020000000000000000000000
1597112198 30677 3 0x05000500A3463C3F40A307AC000000000000000000000000
1456992451 19956 3 0x05000500541E7401406392BC000000000000000000000000
984281776 43805 13 0x05000500052FD03740A3A1C6000000000000000000000000
962083727 112113 18 0x05000700942C7D3E406339EA000000000000000000000000
843149798 577 7 0x05000B003153E7604023B6E9000000000000000000000000
752809408 5511 1 0x0500050088E47D5740631FC1000000000000000000000000
730983354 594 7 0x050005005AC2F3344083D8D7000000000000000000000000
538219354 3533651 5 0x050005009C23030F400304CF000000000000000000000000
405080101 463791 2 0x050005002ADB1A0D40233ADC000000000000000000000000
404464827 8912 3 0x05000500DAA5E13A406343BC000000000000000000000000
354858971 12888 9 0x050005006B2FB9524083CBDB000000000000000000000000
354788856 20840 2 0x0500070022E4943C40C3B2AF000000000000000000000000
311644988 223882 2 0x05000500D5F2636C40234B8A000000000000000000000000
278456532 1001 2 0x05000500354FCD53406346E1000000000000000000000000
270333901 837513 1 0x05000500286B5F2B4003AFAB000000000000000000000000
219689259 548657 3 0x050007008240FE2340C3F5DE000000000000000000000000
209456887 313 36 0x05000500C520C77A40639DBF010000000000000000000000
203146055 70 6 0x05000B006A77DB614003F289010000000000000000000000
187539837 8882 3 0x05000B0002B2A34140A38900010000000000000000000000
183248652 158 1 0x060005006B51F52940439A48020000000000000000000000
151190159 4904 12 0x050005003EA8575C40437BDD000000000000000000000000
148130918 2873 1 0x05000B0029EA182840A3DFE1020000000000000000000000
129011430 591 10 0x05000500FBC78E0540A3AB0A020000000000000000000000
122411767 9145 38 0x05000500A9B1977E4023BCD7000000000000000000000000
103452720 1947 4 0x05000500FC0D240E40A34CD7000000000000000000000000
99285280 433203 1 0x05000500162A4D6D404339BE000000000000000000000000
97455900 77 1 0x060005003E8B77394063E000030000000000000000000000
96981407 90 1 0x0600050061E19A1B40A3C087000000000000000000000000
94693286 9 9 0x05000600309B09704003E311030000000000000000000000
89323421 74 1 0x060005009BAE130440A3B7D8020000000000000000000000
75662449 1117974 2 0x05000500D547F70F40837AD0000000000000000000000000
74451891 84 4 0x06000B00CFFD2D3840C3FBF9000000000000000000000000
72410847 80 4 0x06000B0047B9FB1940A3FB6F010000000000000000000000
72244371 58 1 0x0600050005018F074083E895020000000000000000000000
70044701 5036752 1 0x05000B00B0B71B6A40A374E7000000000000000000000000
68605351 42 7 0x060005003488F60C4003CEE5020000000000000000000000
65495930 158 1 0x06000500FD821E3240233246020000000000000000000000
65255754 7922 8 0x05000500F9E6D05040C3D2D9000000000000000000000000
62199510 107 1 0x06000500A41D2C1E40E34E1A020000000000000000000000
60560005 34 2 0x06000B00D37CFA2240E3FAA7000000000000000000000000
56735759 127554 9 0x05000500FB72FB614043C1AB000000000000000000000000
54106604 110763 6 0x05000500735F631740E3A1C6000000000000000000000000
53255770 6 6 0x06000500C5224C2640833421020000000000000000000000
50681958 202 8 0x05000500B7F7095F40A337BA000000000000000000000000
50605253 1222 9 0x05000B00C3949C6940C30FC2000000000000000000000000
"Tom Moreau" wrote:
> I suspect that the lower CPU usage was due to slower disks and less
> memory.
> In that case, the CPU has to wait for data. If it's not in cache, then it
> has to go to disk. During this time, the CPU waits, and CPU usage goes to
> 0.
> In your newer system, you have more cache, which reduces the need to go to
> disk as often. Because the data is already available, then the CPU can be
> put to work right away to service the query.
> Has query performance suffered or are you just concerned that you see
> higher
> CPU usage?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> Hi
> I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> clustered environment (both machines in the cluster are identical) and the
> machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> G4's) -
> presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> However the new cluster - which would have expected to manage the workload
> much better epsecially in terms of CPU is constantly at about 80-90% cpu
> on
> all processors, and frequntly in the high 90's - I've seen a couple of KB
> articles relating to problems like this - however none seems to quite fit
> out
> situation.
> Anyone got any ideas ?
>
>|||Have already looked at the query side of things - what surpirises me is that
CPU usage was much lower on the former server with the same workload..
Version is 9.00.3050.00 which is SP2
"Tom Moreau" wrote:
> You may want to start profiling for long-running queries and/or those
> queries that are using a lot of CPU.
> BTW what service pack are you running?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> news:105535AF-20A9-41A4-A320-AD35C4CF28CA@.microsoft.com...
> Interesting - I had not thought of it in those terms, but my team are also
> telling me that query performance has suffered. Incidently the disk sub
> system is the same as on the old sub system it is an EMC CX300 SAN dual path
> in active/active configuration, There does not appear to be any IO waiting
> going on so I'm not sure this is oir was an issue. Workload is unchanged.
> No blocking or locking going on. Although some big numbers from Uri's query.
> 14131421676 9670 32 0x0500050053D8DE7840039DC5000000000000000000000000
> 8347158363 30999 9 0x050005009014281C40C3ADB5000000000000000000000000
> 2376266762 1270 7 0x05000B00A39BCF6240C3AEE7000000000000000000000000
> 2202179405 4480 32 0x050005001AB4EA7740C3EE07010000000000000000000000
> 1997588436 2379 8 0x05000B00F82EF35F4003F5A9020000000000000000000000
> 1597112198 30677 3 0x05000500A3463C3F40A307AC000000000000000000000000
> 1456992451 19956 3 0x05000500541E7401406392BC000000000000000000000000
> 984281776 43805 13 0x05000500052FD03740A3A1C6000000000000000000000000
> 962083727 112113 18 0x05000700942C7D3E406339EA000000000000000000000000
> 843149798 577 7 0x05000B003153E7604023B6E9000000000000000000000000
> 752809408 5511 1 0x0500050088E47D5740631FC1000000000000000000000000
> 730983354 594 7 0x050005005AC2F3344083D8D7000000000000000000000000
> 538219354 3533651 5 0x050005009C23030F400304CF000000000000000000000000
> 405080101 463791 2 0x050005002ADB1A0D40233ADC000000000000000000000000
> 404464827 8912 3 0x05000500DAA5E13A406343BC000000000000000000000000
> 354858971 12888 9 0x050005006B2FB9524083CBDB000000000000000000000000
> 354788856 20840 2 0x0500070022E4943C40C3B2AF000000000000000000000000
> 311644988 223882 2 0x05000500D5F2636C40234B8A000000000000000000000000
> 278456532 1001 2 0x05000500354FCD53406346E1000000000000000000000000
> 270333901 837513 1 0x05000500286B5F2B4003AFAB000000000000000000000000
> 219689259 548657 3 0x050007008240FE2340C3F5DE000000000000000000000000
> 209456887 313 36 0x05000500C520C77A40639DBF010000000000000000000000
> 203146055 70 6 0x05000B006A77DB614003F289010000000000000000000000
> 187539837 8882 3 0x05000B0002B2A34140A38900010000000000000000000000
> 183248652 158 1 0x060005006B51F52940439A48020000000000000000000000
> 151190159 4904 12 0x050005003EA8575C40437BDD000000000000000000000000
> 148130918 2873 1 0x05000B0029EA182840A3DFE1020000000000000000000000
> 129011430 591 10 0x05000500FBC78E0540A3AB0A020000000000000000000000
> 122411767 9145 38 0x05000500A9B1977E4023BCD7000000000000000000000000
> 103452720 1947 4 0x05000500FC0D240E40A34CD7000000000000000000000000
> 99285280 433203 1 0x05000500162A4D6D404339BE000000000000000000000000
> 97455900 77 1 0x060005003E8B77394063E000030000000000000000000000
> 96981407 90 1 0x0600050061E19A1B40A3C087000000000000000000000000
> 94693286 9 9 0x05000600309B09704003E311030000000000000000000000
> 89323421 74 1 0x060005009BAE130440A3B7D8020000000000000000000000
> 75662449 1117974 2 0x05000500D547F70F40837AD0000000000000000000000000
> 74451891 84 4 0x06000B00CFFD2D3840C3FBF9000000000000000000000000
> 72410847 80 4 0x06000B0047B9FB1940A3FB6F010000000000000000000000
> 72244371 58 1 0x0600050005018F074083E895020000000000000000000000
> 70044701 5036752 1 0x05000B00B0B71B6A40A374E7000000000000000000000000
> 68605351 42 7 0x060005003488F60C4003CEE5020000000000000000000000
> 65495930 158 1 0x06000500FD821E3240233246020000000000000000000000
> 65255754 7922 8 0x05000500F9E6D05040C3D2D9000000000000000000000000
> 62199510 107 1 0x06000500A41D2C1E40E34E1A020000000000000000000000
> 60560005 34 2 0x06000B00D37CFA2240E3FAA7000000000000000000000000
> 56735759 127554 9 0x05000500FB72FB614043C1AB000000000000000000000000
> 54106604 110763 6 0x05000500735F631740E3A1C6000000000000000000000000
> 53255770 6 6 0x06000500C5224C2640833421020000000000000000000000
> 50681958 202 8 0x05000500B7F7095F40A337BA000000000000000000000000
> 50605253 1222 9 0x05000B00C3949C6940C30FC2000000000000000000000000
> "Tom Moreau" wrote:
> > I suspect that the lower CPU usage was due to slower disks and less
> > memory.
> > In that case, the CPU has to wait for data. If it's not in cache, then it
> > has to go to disk. During this time, the CPU waits, and CPU usage goes to
> > 0.
> >
> > In your newer system, you have more cache, which reduces the need to go to
> > disk as often. Because the data is already available, then the CPU can be
> > put to work right away to service the query.
> >
> > Has query performance suffered or are you just concerned that you see
> > higher
> > CPU usage?
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> > news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> > Hi
> >
> > I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> > clustered environment (both machines in the cluster are identical) and the
> > machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> > G4's) -
> > presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> > cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> >
> > However the new cluster - which would have expected to manage the workload
> > much better epsecially in terms of CPU is constantly at about 80-90% cpu
> > on
> > all processors, and frequntly in the high 90's - I've seen a couple of KB
> > articles relating to problems like this - however none seems to quite fit
> > out
> > situation.
> >
> > Anyone got any ideas ?
> >
> >
> >
>|||OK - I'll have a look at the query side of things...
"Andrew J. Kelly" wrote:
> I agree with Tom in that you most likely had other bottlenecks that
> prevented your CPU's from being constantly higher in the last system. High
> CPU usage usually means you are getting a lot of work done and is not
> necessarily a bad thing. But it can also indicate you have poorly optimized
> queries and are getting a lot of compiles or recompiles.
> --
> Andrew J. Kelly SQL MVP
> "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> > Hi
> >
> > I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> > clustered environment (both machines in the cluster are identical) and the
> > machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> > G4's) -
> > presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> > cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> >
> > However the new cluster - which would have expected to manage the workload
> > much better epsecially in terms of CPU is constantly at about 80-90% cpu
> > on
> > all processors, and frequntly in the high 90's - I've seen a couple of KB
> > articles relating to problems like this - however none seems to quite fit
> > out
> > situation.
> >
> > Anyone got any ideas ?
> >
> >
>
>

High Availability Options

From what I've read, there seems to be 4 ways to ensure that if your main
SQL Server 2005 machine dies, that you can have a backup machine
up-and-running relatively quickly.
1. Backup the databases on the master server and restore them to the backup
server on a regular basis using a maintenance plan.
2. Setup database replication, with the master server being the distributer
and publisher, and the backup server being the subscriber.
3. Use the unsupported database mirroring functionality in 2005.
4. Create a virtual server on a cluster.
Any others that I may have missed?
Now onto the pros and cons of the 4 choices, from what I can see anyway.
1. Slow and quite labour intensive when it comes to maintaining the backups,
but simple to implement.
2. A pig to setup, but then everything seems to go smoothly. Probably good
if using a single subscriber against multiple publishers.
3. Seems pretty straight forward and supports rollover, but is unsupported
and requires some jiggery-pokery to get it going.
4. The bees knees. Handles everything well but expensive.
OK, so I sort of summarised the above. There are other pros and cons that
are just too minor to deal with.
I'd like to go with 4, as I have a system with 150 databases that are in use
24/7, but I also have a limited budget and can't be forking out for a pair
of high-end servers and fiber connected SAN.
Any advice or suggestions?
TIA.Hi Andrew
Database mirroring is supported as of SP1.
One option you have missed is traditional log shipping.
For clustering there are multiple types, giving different levels of local
and failover support.
It sounds like you have already made up your mind based on cost! You may
want to read http://msdn2.microsoft.com/en-us/library/ms345600.aspx
John
"Andrew Hayes" wrote:
> From what I've read, there seems to be 4 ways to ensure that if your main
> SQL Server 2005 machine dies, that you can have a backup machine
> up-and-running relatively quickly.
> 1. Backup the databases on the master server and restore them to the backup
> server on a regular basis using a maintenance plan.
> 2. Setup database replication, with the master server being the distributer
> and publisher, and the backup server being the subscriber.
> 3. Use the unsupported database mirroring functionality in 2005.
> 4. Create a virtual server on a cluster.
> Any others that I may have missed?
> Now onto the pros and cons of the 4 choices, from what I can see anyway.
> 1. Slow and quite labour intensive when it comes to maintaining the backups,
> but simple to implement.
> 2. A pig to setup, but then everything seems to go smoothly. Probably good
> if using a single subscriber against multiple publishers.
> 3. Seems pretty straight forward and supports rollover, but is unsupported
> and requires some jiggery-pokery to get it going.
> 4. The bees knees. Handles everything well but expensive.
> OK, so I sort of summarised the above. There are other pros and cons that
> are just too minor to deal with.
> I'd like to go with 4, as I have a system with 150 databases that are in use
> 24/7, but I also have a limited budget and can't be forking out for a pair
> of high-end servers and fiber connected SAN.
> Any advice or suggestions?
> TIA.
>
>|||I personally don't consider replication a valid High Availability solution,
despite what MS says. The simple fact that it won't automatically replicate
new objects is enough for me to stop considering it. There are other gotchas
as well, this is just the tip of the iceburg.
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:u8y735YKHHA.4376@.TK2MSFTNGP03.phx.gbl...
> From what I've read, there seems to be 4 ways to ensure that if your main
> SQL Server 2005 machine dies, that you can have a backup machine
> up-and-running relatively quickly.
> 1. Backup the databases on the master server and restore them to the
backup
> server on a regular basis using a maintenance plan.
> 2. Setup database replication, with the master server being the
distributer
> and publisher, and the backup server being the subscriber.
> 3. Use the unsupported database mirroring functionality in 2005.
> 4. Create a virtual server on a cluster.
> Any others that I may have missed?
> Now onto the pros and cons of the 4 choices, from what I can see anyway.
> 1. Slow and quite labour intensive when it comes to maintaining the
backups,
> but simple to implement.
> 2. A pig to setup, but then everything seems to go smoothly. Probably good
> if using a single subscriber against multiple publishers.
> 3. Seems pretty straight forward and supports rollover, but is unsupported
> and requires some jiggery-pokery to get it going.
> 4. The bees knees. Handles everything well but expensive.
> OK, so I sort of summarised the above. There are other pros and cons that
> are just too minor to deal with.
> I'd like to go with 4, as I have a system with 150 databases that are in
use
> 24/7, but I also have a limited budget and can't be forking out for a pair
> of high-end servers and fiber connected SAN.
> Any advice or suggestions?
> TIA.
>|||"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:ORRSpucKHHA.2028@.TK2MSFTNGP03.phx.gbl...
>I personally don't consider replication a valid High Availability solution,
> despite what MS says.
Hmm, last I heard MS doesn't consider it a valid HA either.
Replication is great for spreading out load, but not really an HA option.
> The simple fact that it won't automatically replicate
> new objects is enough for me to stop considering it.
Honestly, that's not a big issue (and I thought as of SQL 2005 it could.)
Since really adding new objects, should be rare enough and controlled enough
that it's easy enough to to.
> There are other gotchas
> as well, this is just the tip of the iceburg.
I'll agree with this.
(for example, if you use Identity columns on the publisher, the subscriber
generally is NOT set up with Identity columns which can cause problems.)|||Did you want to participate in a solution for the poster, or just contradict
what I wrote?
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%2363aF9gKHHA.2140@.TK2MSFTNGP03.phx.gbl...
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:ORRSpucKHHA.2028@.TK2MSFTNGP03.phx.gbl...
> >I personally don't consider replication a valid High Availability
solution,
> > despite what MS says.
> Hmm, last I heard MS doesn't consider it a valid HA either.
> Replication is great for spreading out load, but not really an HA option.
> > The simple fact that it won't automatically replicate
> > new objects is enough for me to stop considering it.
> Honestly, that's not a big issue (and I thought as of SQL 2005 it could.)
> Since really adding new objects, should be rare enough and controlled
enough
> that it's easy enough to to.
> > There are other gotchas
> > as well, this is just the tip of the iceburg.
> I'll agree with this.
> (for example, if you use Identity columns on the publisher, the subscriber
> generally is NOT set up with Identity columns which can cause problems.)
>|||ChrisR wrote:
> Did you want to participate in a solution for the poster, or just contradict
> what I wrote?
I try to cut down on the caffeine after 6 P.M. Perhaps you should, too. ;-)
--
Message posted via http://www.sqlmonster.com|||Thanks for the comments so far.
John - I had lumped log shipping in with option 1, although that maybe just
my lack of understanding.
Linchi and ChrisR - Thanks for pointing out that replication is mainly for
data and not the schema. I had overlooked that part.
Now... database mirroring certainly has a lot going for it, and MS seem to
be pushing it ahead of using a virtual server. Certainly better than
replication or log shipping. But I don't want to have to deal with setting
up 150 odd mirrors, and then checking all the logs to see if there have been
any problems with any of them.
It's a shame they don't have Server Mirroring as an option, but then I guess
that would be a cluster. :-)|||ChrisR wrote:
> Did you want to participate in a solution for the poster, or just contradict
> what I wrote?
>
Expanding on someone else's answer IS contributing to a solution.
Welcome to newsgroups.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi Andrew:
You might try reviewing the literature at:
http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx
for information on High Availability solutions for SQL2K5.
Neither Backup/Restore nor Replication are considered to be "High
Availability" solutions. My understanding is that database mirroring
is to be supported with the upcoming release of SP2. There are 3 true
HA solutions for SQL2K5, in order from highest to lowest HA %:
* Database mirroring
* Virtual clustering
* Log Shipping (which you did not mention)
Most HA solutions I've worked with rely on clustering as their primary
solution. When even more HA reliability is desired we couple
clustering with database mirroring. Log shipping is an option, but
it's downtime is significantly higher than the other two options.
So my base recommendation is that you go with a virtual cluster if
you're looking for the most cost-effective HA solution. If you can't
afford the $ for a cluster with SAN, have you considered a cluster
tied to a local storage array? It sounds like your current
configuration consists of a standalone server and your recovery
solution relies on backups, correct? If you have a system that's truly
24/7, then you'll have to go back to your management and lay out the
risks of your current configuration and the cost to implement a
virtual cluster. Then present the worst-case scenario (i.e., server
hardware failure + damaged backups...that should be good for 2-3+ days
of outage and only a partial restore capability) ask them point blank
if under the current configuration they can sleep at night knowing
that an outage will result in significant downtime. If they say yes,
then you've done your job as DBA and when the outage occurs (and it
will) you'll do your job and remind them, as they're breating down
your neck to get the databases back online, that it was their decision
not to go with an HA solution...and you have the documentation to
prove it :-).
BTW, if they do go for the clustering solution, push for getting an
x64 platform instead of an x86 platform. You'll find that, even with
32-bit SQL2K5, performance is better and memory management issues are
easier to handle. For instance, if you don't install the CLR component
of SQL2K5 you'll be able to access 4 GB of base RAM (1 GB more than on
x86 systems) and AWE is both automatically enabled and better managed
by the OS under x64. An x64 platform is also more expandable, so if
you decide to add another instance at a later time you won't run into
the 16GB limit of the x86 platform.
Good luck to you!
On Wed, 27 Dec 2006 17:05:39 +0900, "Andrew Hayes"
<AndrewHayes@.discussions.microsoft.com> wrote:
>From what I've read, there seems to be 4 ways to ensure that if your main
>SQL Server 2005 machine dies, that you can have a backup machine
>up-and-running relatively quickly.
>1. Backup the databases on the master server and restore them to the backup
>server on a regular basis using a maintenance plan.
>2. Setup database replication, with the master server being the distributer
>and publisher, and the backup server being the subscriber.
>3. Use the unsupported database mirroring functionality in 2005.
>4. Create a virtual server on a cluster.
>Any others that I may have missed?
>Now onto the pros and cons of the 4 choices, from what I can see anyway.
>1. Slow and quite labour intensive when it comes to maintaining the backups,
>but simple to implement.
>2. A pig to setup, but then everything seems to go smoothly. Probably good
>if using a single subscriber against multiple publishers.
>3. Seems pretty straight forward and supports rollover, but is unsupported
>and requires some jiggery-pokery to get it going.
>4. The bees knees. Handles everything well but expensive.
>OK, so I sort of summarised the above. There are other pros and cons that
>are just too minor to deal with.
>I'd like to go with 4, as I have a system with 150 databases that are in use
>24/7, but I also have a limited budget and can't be forking out for a pair
>of high-end servers and fiber connected SAN.
>Any advice or suggestions?
>TIA.
>