Showing posts with label machine. Show all posts
Showing posts with label machine. Show all posts

Monday, March 26, 2012

More details

I plan to upgrade my server to SQL 2005. It is a rather old machine, but still ok with the recommended hardware requirements.

It runs Windows 2000 (SBS) server, which is also a PDC, DHCP, DNS, Exchange server (I stop here but the list is long), with SQL Server 2000 Standard Edition (one single named instance). Everything localized (Italian).

I upgraded the database in a development machine and it works perfectly.

I installed SQL 2005 Standard Edition (English locale) on the server (default instance, MSSQLSERVER), and I have run in a lot of troubles.

The problem seems to reside in the TCPIP network protocols. If I enable TCPIP in the 2005 Server Configuration Manager, the new instance (MSSQLSERVER) does not start (this prevented me from upgrading to SP1 until I realized the cause, disabled TCPIP protocol and was finally able to update).

If I understand correctly, the problem is that both instances, the old 2000 one and the new one, try to listen to 1433 port. I changed manually the port to 1434 in the Server Configuration Manager. The result is that both SQL servers work, but none is working ok. I have several applications accessing the server. The asp application works. The .NET aspx one doesn't.

A tipical error I get is:

Codice evento: 3005
Messaggio evento: Eccezione non gestita. (Unhandled exception)
Ora evento: 04/05/2006 13.42.26
Ora evento (UTC): 04/05/2006 11.42.26
ID evento: 7c2749235b7d4c578591871217f74046
Sequenza evento: 175
Occorrenza evento: 24
Codice dettagli evento: 0
Informazioni applicazione:

Dominio applicazione: /LM/W3SVC/1/Root/intranet-1-127912101551093750
Livello di attendibilità: Full
Percorso virtuale applicazione: /intranet
Percorso applicazione: F:\Inetpub\wwwroot\intranet\
Nome computer: IQ
Informazioni processo:
ID processo: 684
Nome processo: aspnet_wp.exe
Nome account: mycompany\IWAM_IQ
Informazioni eccezione:
Tipo di eccezione: SqlException
Messaggio eccezione: Login failed for user 'mycompany\stefania'.
Informazioni richiesta:
URL richiesta: http://intranet/intranet/articoli/venditeProd.aspx?Codice=SOL T1
Percorso richiesta: /intranet/articoli/venditeProd.aspx
Indirizzo host utente: 10.0.0.127
Utente: mycompany\stefania
Autenticazione: True
Tipo di autenticazione: NTLM
Nome account thread: mycompany\IWAM_IQ
Informazioni thread:
ID thread: 1
Nome account thread: mycompany\IWAM_IQ
Rappresentazione: True
Analisi dello stack: in System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
in System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
in System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
in System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
in System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
in System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
in System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
in System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
in System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
in System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
in System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
in System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
in System.Data.SqlClient.SqlConnection.Open()
in intranet.Global.Session_Start(Object sender, EventArgs e)
in System.Web.SessionState.SessionStateModule.RaiseOnStart(EventArgs e)
in System.Web.SessionState.SessionStateModule.CompleteAcquireState()
in System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData)
in System.Web.HttpApplication.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
in System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

I also tried to use named pipes on the 2005, but I was unable to access the SQL 2005 server instance from a network computer running the Management Studio. I need to have the new SQL server working besides the old one, for a short period, to evaluate in the production environment if the legacy hardware is actually ok to support it. Any ideas?

You should probably try a different group than Setup & Upgrade. Looks like your upgrade went through fine but now have connectivity issues.|||

Actually this IS a "setup" issue, of course tied to a connectivity problem. I could not find a more appropriate "SQL connectivity" forum anyway.

I even run in installation issues, and maybe someone at MS is interested to know: the setup routine installed the SQL 2005 server engine, but was unable to install the reporting services because it was unable to run the engine (it turned on TCPIP listening on port 1433 by default, which collided with the old 2000 instance).

I did some more research yesterday. Things are really strange. I installed the SQL Server Management Studio Express also locally on the server (I didn't originally install the management tools with the 2005 installation, because I counted on administering from the network). If I turn on either named pipes or TCPIP on the new 2005 instance (which is the default instance), the mess begins as I described above. Some applications work, correctly connecting to the 2000 named instance (namely the old ASP application). Some other not (namely the new ASPX application, which probably tries to connect to the new 2005 instance, and reports the impossibility to authenticate, because the user login is not registered in that instance).

The funny thing is that if I run the Management Studio Express, I can connect to both the default instance (which is supposed to be 2005) and the named instance. But actually both connections point to the old instance (with the old db's, and a 8.0.2039 server version string). And the Management Studio shouldn't even use named pipes or TCPIP, but instead should use shared memory to connect to local db's!

If I turn off the TCPIP or named pipes protocols for the 2005 instance, everything works ok, and I correctly connect to both instances in Management Studio (but I cannot connect from the network!).

|||

Well, after more trying, here is what I found.

I disabled shared memory in both Network Configuration and Native Client on the server, using the SQL Server Configuration Manager. I enabled just TCPIP on port 1434. I started the 2005 instance. It starts ok, no problem with any application.

But I cannot see the 2005 instance, no way. The SQL Sever Management Studio (in both the server or a network client computer) seem to connect to both the default instance (2005) and the named instance (2000). But they actually connect to the same instance (the 2000 one).

At this point I think the problem arises installing a new 2005 default instance when there is a 2000 named instance. I must say in that server I previously had a 2000 default instance, which was uninstalled long time ago. Maybe that has left traces that now are messing up the configuration and confuse the SQL browser?

I could probably try installing a new 2005 named instance to see if this leads to anything. But I will instead migrate the databases to the 2005, assuming everything will work. I will do that through backup, shut down the 2000 for good, and restore in 2005 hoping there will be no necessity of turning back!

More Connect Issues

I have an instance of SQL Server 2005 on two different development machines.
At first we could not connect to Machine A until we read about how it comes
locked down. We went to SQL Server Config Mgr and enabled all necessary
protocols and can connect just fine to it. When we did the same to machine
B, nothing happened. Our client apps just hang. The server is ping-able
but sql test connections just fail. Any thoughts what or where I could look
at next?
Regards.
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.connect:47973
I'm sorry this is the error message I'm receiving:
TITLE: New Server Registration
Testing the registered server failed. Verify the server name, login
credentials, and database, and then click Test again.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click:
http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476
BUTTONS:
OK
"Trex" <randytrexler@.hotmail.com> wrote in message
news:1Xdvf.3139$%W1.71@.newsread2.news.atl.earthlin k.net...
>I have an instance of SQL Server 2005 on two different development
>machines. At first we could not connect to Machine A until we read about
>how it comes locked down. We went to SQL Server Config Mgr and enabled all
>necessary protocols and can connect just fine to it. When we did the same
>to machine B, nothing happened. Our client apps just hang. The server is
>ping-able but sql test connections just fail. Any thoughts what or where I
>could look at next?
> Regards.
>
|||Did you enable the "Allow Remote Connections" on that server?
Andrew J. Kelly SQL MVP
"Trex" <randytrexler@.hotmail.com> wrote in message
news:KZdvf.3140$%W1.115@.newsread2.news.atl.earthli nk.net...
> I'm sorry this is the error message I'm receiving:
> TITLE: New Server Registration
> --
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> --
> ADDITIONAL INFORMATION:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 26 - Error Locating
> Server/Instance Specified) (Microsoft SQL Server, Error: -1)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> "Trex" <randytrexler@.hotmail.com> wrote in message
> news:1Xdvf.3139$%W1.71@.newsread2.news.atl.earthlin k.net...
>

More Connect Issues

I have an instance of SQL Server 2005 on two different development machines.
At first we could not connect to Machine A until we read about how it comes
locked down. We went to SQL Server Config Mgr and enabled all necessary
protocols and can connect just fine to it. When we did the same to machine
B, nothing happened. Our client apps just hang. The server is ping-able
but sql test connections just fail. Any thoughts what or where I could look
at next?
Regards.Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.connect:47973
I'm sorry this is the error message I'm receiving:
TITLE: New Server Registration
--
Testing the registered server failed. Verify the server name, login
credentials, and database, and then click Test again.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click:
http://go.microsoft.com/fwlink?Prod...-1&LinkId=20476
BUTTONS:
OK
--
"Trex" <randytrexler@.hotmail.com> wrote in message
news:1Xdvf.3139$%W1.71@.newsread2.news.atl.earthlink.net...
>I have an instance of SQL Server 2005 on two different development
>machines. At first we could not connect to Machine A until we read about
>how it comes locked down. We went to SQL Server Config Mgr and enabled all
>necessary protocols and can connect just fine to it. When we did the same
>to machine B, nothing happened. Our client apps just hang. The server is
>ping-able but sql test connections just fail. Any thoughts what or where I
>could look at next?
> Regards.
>|||Did you enable the "Allow Remote Connections" on that server?
Andrew J. Kelly SQL MVP
"Trex" <randytrexler@.hotmail.com> wrote in message
news:KZdvf.3140$%W1.115@.newsread2.news.atl.earthlink.net...
> I'm sorry this is the error message I'm receiving:
> TITLE: New Server Registration
> --
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> --
> ADDITIONAL INFORMATION:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 26 - Error Locating
> Server/Instance Specified) (Microsoft SQL Server, Error: -1)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...-1&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> "Trex" <randytrexler@.hotmail.com> wrote in message
> news:1Xdvf.3139$%W1.71@.newsread2.news.atl.earthlink.net...
>

Friday, March 9, 2012

monitoring application performence

hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>

monitoring application performence

hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>

monitoring application performence

hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.
If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>