Collation and Win2K3 Clustered Environment.

This past weekend I was challenged a little in resolving what I hoped was a
simple collation issue. We are running Win2K3 Ent. Clustering Services, SQL
Server 2000 Enterprise, b.8.00.760.
On Friday, 2.11, a user migrated an application to this new clustered setup
and immediately received the infamous 446 collation error.
I spent most of Friday evening and all day Saturday reviewing support topics
and news groups. I found that there was 1 issue that could be affecting us -
the build of SQL Server for Win2K3 Ent. Clustering is subtly different than
for Win2KAS Clustering - and all of my research came back to one thing: Run
rebuildm.exe and set the collation of this instance to be the same as the
existing development/production environment. The default setup on the cluster
was different than the previous production setup, as well as different than
the current development environment.
So, I backed everyone's database up, then the master objects, shut the
instance down, executed rebuild and set the collation to what I needed it to
be.
When I brought this user's database online - the same exact error occurs.
Prior to doing this, here is what we were faced with:
Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
The new clustered SQL=SQL Server Enterprise, w/Collate=Latin1_General_CI_AI.
The database/application in question, at the procedure where we receive the
collation error, basically calls a function that creates a temp table, then
another that pulls data for a report - however, the data is collected as a
JOIN on this tempdb temp object and a series of Views the programmer
previously setup. These views in turn hit various other static tables as well.
We were thinking that because of the collation difference on the new Win2K3
setup, as well as Collation/Locale settings differences with Win2K3
Clustering, we should at a minimum change the instance to match the
SQL_Latin1, etc. collation of development. This however, has not solved the
problem.
At this juncture, the Win2K3 Clustered server has the collation
SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
their current production environment, which is 2KAS nonclustered) is
SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now is
the accent sensitivity - but this should not be the issue.
If anyone else out there has any feedback, I'd be grateful for your time.
Thanks...
mhamilton"AT"nusoftsolutions"DOT"com
If the collation names are different you'll get the collation conflict
error -- a difference in accent sensitivity is sufficient to expose the
problem.
One option is rebuild master in dev or test (again) so that the two servers
have the same collation. You almost matched the collation last time, but
the different accent sensitivity setting is also critical.
Another option is to make sure that the T-SQL is written in a way that
makes it immune to the problem. For the scenario you describe you could do
this by making sure that your temp tables inherit the collation of the
current user database, not the collation of tempdb. A "COLLATE
database_default" clause will accomplish this. For example, when creating
the temp table in the stored proc:
CREATE TABLE #temp1 (
c1 int,
c2 varchar (30) COLLATE database_default,
c3 char(12) COLLATE database_default,
)
HTH,
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Collation and Win2K3 Clustered Environment.
| thread-index: AcUUNUWE2BoB7xCnSeWo8pOsRpOhYg==
| X-WBNR-Posting-Host: 12.227.130.93
| From: "=?Utf-8?B?TWlrZUg=?=" <MikeH@.discussions.microsoft.com>
| Subject: Collation and Win2K3 Clustered Environment.
| Date: Wed, 16 Feb 2005 06:39:08 -0800
| Lines: 51
| Message-ID: <87A19939-8444-4CEC-BBB4-ED092DFFD4D7@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.clustering
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.clustering:3114
| X-Tomcat-NG: microsoft.public.sqlserver.clustering
|
| This past weekend I was challenged a little in resolving what I hoped was
a
| simple collation issue. We are running Win2K3 Ent. Clustering Services,
SQL
| Server 2000 Enterprise, b.8.00.760.
|
| On Friday, 2.11, a user migrated an application to this new clustered
setup
| and immediately received the infamous 446 collation error.
|
| I spent most of Friday evening and all day Saturday reviewing support
topics
| and news groups. I found that there was 1 issue that could be affecting
us -
| the build of SQL Server for Win2K3 Ent. Clustering is subtly different
than
| for Win2KAS Clustering - and all of my research came back to one thing:
Run
| rebuildm.exe and set the collation of this instance to be the same as the
| existing development/production environment. The default setup on the
cluster
| was different than the previous production setup, as well as different
than
| the current development environment.
|
| So, I backed everyone's database up, then the master objects, shut the
| instance down, executed rebuild and set the collation to what I needed it
to
| be.
|
| When I brought this user's database online - the same exact error occurs.
|
| Prior to doing this, here is what we were faced with:
| Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
| Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
|
| The new clustered SQL=SQL Server Enterprise,
w/Collate=Latin1_General_CI_AI.
|
| The database/application in question, at the procedure where we receive
the
| collation error, basically calls a function that creates a temp table,
then
| another that pulls data for a report - however, the data is collected as
a
| JOIN on this tempdb temp object and a series of Views the programmer
| previously setup. These views in turn hit various other static tables as
well.
|
| We were thinking that because of the collation difference on the new
Win2K3
| setup, as well as Collation/Locale settings differences with Win2K3
| Clustering, we should at a minimum change the instance to match the
| SQL_Latin1, etc. collation of development. This however, has not solved
the
| problem.
|
| At this juncture, the Win2K3 Clustered server has the collation
| SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
| their current production environment, which is 2KAS nonclustered) is
| SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now
is
| the accent sensitivity - but this should not be the issue.
|
| If anyone else out there has any feedback, I'd be grateful for your time.
|
| Thanks...
|
| mhamilton"AT"nusoftsolutions"DOT"com
|
|||Bart, thanks for getting back to me.
I must confess, moving from 2KAS Clustering to 2K3 Clustering is NOT fun.
Simply because of the nuances I am finding.
Foremost, I did get the application working, and thus far I have not
experienced the infamous 446 collation error.
However...
This particular cluster is only running 6 instances of SQL Server. Each
instance has the Full Text Search engine/component installed - yet only 2 of
the instances are actually using it - and the problem I ran into is on 1 of
these 2 instances.
Specifically, this instance has the full text search active.
Now...
IF - and I say this 'loudly' - IF the full text server was 'offline' when I
did the rebuild - then restarted the instance and tested the app - the app
failed. Most interesting...
It took me a couple days to see what was happening. So... I did the same
with the full text 'online' and voila!!! I have joy... The application worked
fine.
Now... I have a few questions, but I'm sure you're not going to be able to
answer them anymore than I can. This notwithstanding, I find it interesting
that rebuilding the instance - with the SQL Full Text 'offline' when I do it
-could actually keep it from working. Yes, software is software - but this is
quirky at best.
Anyway... Thank you for responding... I will be in contact with PSS and my
manager on this issue, and if you have any other questions regarding the
setup, please feel free to contact me.
"Bart Duncan [MSFT]" wrote:

> If the collation names are different you'll get the collation conflict
> error -- a difference in accent sensitivity is sufficient to expose the
> problem.
> One option is rebuild master in dev or test (again) so that the two servers
> have the same collation. You almost matched the collation last time, but
> the different accent sensitivity setting is also critical.
> Another option is to make sure that the T-SQL is written in a way that
> makes it immune to the problem. For the scenario you describe you could do
> this by making sure that your temp tables inherit the collation of the
> current user database, not the collation of tempdb. A "COLLATE
> database_default" clause will accomplish this. For example, when creating
> the temp table in the stored proc:
> CREATE TABLE #temp1 (
> c1 int,
> c2 varchar (30) COLLATE database_default,
> c3 char(12) COLLATE database_default,
> )
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Collation and Win2K3 Clustered Environment.
> | thread-index: AcUUNUWE2BoB7xCnSeWo8pOsRpOhYg==
> | X-WBNR-Posting-Host: 12.227.130.93
> | From: "=?Utf-8?B?TWlrZUg=?=" <MikeH@.discussions.microsoft.com>
> | Subject: Collation and Win2K3 Clustered Environment.
> | Date: Wed, 16 Feb 2005 06:39:08 -0800
> | Lines: 51
> | Message-ID: <87A19939-8444-4CEC-BBB4-ED092DFFD4D7@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.clustering
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.clustering:3114
> | X-Tomcat-NG: microsoft.public.sqlserver.clustering
> |
> | This past weekend I was challenged a little in resolving what I hoped was
> a
> | simple collation issue. We are running Win2K3 Ent. Clustering Services,
> SQL
> | Server 2000 Enterprise, b.8.00.760.
> |
> | On Friday, 2.11, a user migrated an application to this new clustered
> setup
> | and immediately received the infamous 446 collation error.
> |
> | I spent most of Friday evening and all day Saturday reviewing support
> topics
> | and news groups. I found that there was 1 issue that could be affecting
> us -
> | the build of SQL Server for Win2K3 Ent. Clustering is subtly different
> than
> | for Win2KAS Clustering - and all of my research came back to one thing:
> Run
> | rebuildm.exe and set the collation of this instance to be the same as the
> | existing development/production environment. The default setup on the
> cluster
> | was different than the previous production setup, as well as different
> than
> | the current development environment.
> |
> | So, I backed everyone's database up, then the master objects, shut the
> | instance down, executed rebuild and set the collation to what I needed it
> to
> | be.
> |
> | When I brought this user's database online - the same exact error occurs.
> |
> | Prior to doing this, here is what we were faced with:
> | Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
> | Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
> |
> | The new clustered SQL=SQL Server Enterprise,
> w/Collate=Latin1_General_CI_AI.
> |
> | The database/application in question, at the procedure where we receive
> the
> | collation error, basically calls a function that creates a temp table,
> then
> | another that pulls data for a report - however, the data is collected as
> a
> | JOIN on this tempdb temp object and a series of Views the programmer
> | previously setup. These views in turn hit various other static tables as
> well.
> |
> | We were thinking that because of the collation difference on the new
> Win2K3
> | setup, as well as Collation/Locale settings differences with Win2K3
> | Clustering, we should at a minimum change the instance to match the
> | SQL_Latin1, etc. collation of development. This however, has not solved
> the
> | problem.
> |
> | At this juncture, the Win2K3 Clustered server has the collation
> | SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
> | their current production environment, which is 2KAS nonclustered) is
> | SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now
> is
> | the accent sensitivity - but this should not be the issue.
> |
> | If anyone else out there has any feedback, I'd be grateful for your time.
> |
> | Thanks...
> |
> | mhamilton"AT"nusoftsolutions"DOT"com
> |
>

Cluster will not fail over.

We had an error over the weekend of mass porportions(Sunday 3pm PST). Long story short; the model database was detached and the SQL Server was stopped, with it still detached. This happened to happen on our primary Production Database Clustered Server which is the bread-n-butter of the compay. (OUCH!)
It was time for some fast actions. We started the re-install SQL Server. In order to do so, the previous install had to be uninstalled. This seemed to go smoothly enough, but when re-applying the SP3a, we encountered an error. After researching the error, apparently in a clustered environment this will occur since the SP3a files still reside on the node(s). Microsoft states that if within a particular log file it results with an 'Installation was Successful', to disregard the error. I double checked the log file and sure enough the error was disregarded.
We moved along with the installation. We were able to restore all the user databases and all system databases with the exception of the master database. Unfortunately, even with starting SQL Server in single-user mode, the restore of the master database would not take. So it was not restored, but all other databases were. Fortunately, I ran a quick script to recover all the user logins previous to the disaster, which I reapplied to the new installation of SQL Server. Everything came back up and the QA Team successfully tested the production Application (Monday 4am PST). (Fhweeh)
After the succesful testing of the production environment, we tested the fail-over which resulted in SQL Server not starting on the secondary node. All the resources came right up on it, but not SQL Server. The only error that was that it was not able to locate the file on 'O\logs\mastlog.ldf'. This error did not make sense since SQL Server uses the same file for the primary node. We were pressed for time since it was closing to start of business East Coast time, so we left the server as is.
Throughout the day there were other issues that arose, one in particular was certain systems were not able to connect to the server via TCP/IP. In order to have them connect they needed to create an alias of the server and use Name Pipes. This seems to be a rising concern because there are users who need to connect via ODBC to a widely used particular Access Application, which seems to only like the TCP/IP route. I am somewhat sure this is related to the cluster failure.
Anyway, this is the first time I've had to take a breathe to revisit the problem at hand. We have been dealing with another server that crashed on the same day, resulting in a brand new build of a SQL Server Cluster environment (completely non related to the issue at hand).
I'm sorry for the long winded story. Would you have any idea as to why the cluster would fail on failover along with the TCP/IP issue?
Thanks in Advanced..
95% of the systems that were not able to connect to the server via TCP/IP
were Windows 2003 Server systems.
"Admiral" <admiral@.blackopsplatoon.com> wrote in message
news:uoXFWWbBGHA.216@.TK2MSFTNGP15.phx.gbl...
We had an error over the weekend of mass porportions(Sunday 3pm PST). Long
story short; the model database was detached and the SQL Server was stopped,
with it still detached. This happened to happen on our primary Production
Database Clustered Server which is the bread-n-butter of the compay.
(OUCH!)
It was time for some fast actions. We started the re-install SQL Server.
In order to do so, the previous install had to be uninstalled. This seemed
to go smoothly enough, but when re-applying the SP3a, we encountered an
error. After researching the error, apparently in a clustered environment
this will occur since the SP3a files still reside on the node(s).
Microsoft states that if within a particular log file it results with an
'Installation was Successful', to disregard the error. I double checked the
log file and sure enough the error was disregarded.
We moved along with the installation. We were able to restore all the user
databases and all system databases with the exception of the master
database. Unfortunately, even with starting SQL Server in single-user mode,
the restore of the master database would not take. So it was not restored,
but all other databases were. Fortunately, I ran a quick script to recover
all the user logins previous to the disaster, which I reapplied to the new
installation of SQL Server. Everything came back up and the QA Team
successfully tested the production Application (Monday 4am PST). (Fhweeh)
After the succesful testing of the production environment, we tested the
fail-over which resulted in SQL Server not starting on the secondary node.
All the resources came right up on it, but not SQL Server. The only error
that was that it was not able to locate the file on 'O\logs\mastlog.ldf'.
This error did not make sense since SQL Server uses the same file for the
primary node. We were pressed for time since it was closing to start of
business East Coast time, so we left the server as is.
Throughout the day there were other issues that arose, one in particular was
certain systems were not able to connect to the server via TCP/IP. In order
to have them connect they needed to create an alias of the server and use
Name Pipes. This seems to be a rising concern because there are users who
need to connect via ODBC to a widely used particular Access Application,
which seems to only like the TCP/IP route. I am somewhat sure this is
related to the cluster failure.
Anyway, this is the first time I've had to take a breathe to revisit the
problem at hand. We have been dealing with another server that crashed on
the same day, resulting in a brand new build of a SQL Server Cluster
environment (completely non related to the issue at hand).
I'm sorry for the long winded story. Would you have any idea as to why the
cluster would fail on failover along with the TCP/IP issue?
Thanks in Advanced..
|||Continued research..
I failed to connect thru isql + IP connection via command line, which leads
me to believe that TCP/IP is not correct.
Thanks again and please bare with me, I haven't slept since Saturday night.
"Admiral" <admiral@.blackopsplatoon.com> wrote in message
news:un7D9ZbBGHA.2840@.TK2MSFTNGP12.phx.gbl...
> 95% of the systems that were not able to connect to the server via TCP/IP
> were Windows 2003 Server systems.
>
> "Admiral" <admiral@.blackopsplatoon.com> wrote in message
> news:uoXFWWbBGHA.216@.TK2MSFTNGP15.phx.gbl...
> We had an error over the weekend of mass porportions(Sunday 3pm PST).
> Long story short; the model database was detached and the SQL Server was
> stopped, with it still detached. This happened to happen on our primary
> Production Database Clustered Server which is the bread-n-butter of the
> compay. (OUCH!)
> It was time for some fast actions. We started the re-install SQL Server.
> In order to do so, the previous install had to be uninstalled. This
> seemed to go smoothly enough, but when re-applying the SP3a, we
> encountered an error. After researching the error, apparently in a
> clustered environment this will occur since the SP3a files still reside on
> the node(s). Microsoft states that if within a particular log file it
> results with an 'Installation was Successful', to disregard the error. I
> double checked the log file and sure enough the error was disregarded.
> We moved along with the installation. We were able to restore all the
> user databases and all system databases with the exception of the master
> database. Unfortunately, even with starting SQL Server in single-user
> mode, the restore of the master database would not take. So it was not
> restored, but all other databases were. Fortunately, I ran a quick script
> to recover all the user logins previous to the disaster, which I reapplied
> to the new installation of SQL Server. Everything came back up and the QA
> Team successfully tested the production Application (Monday 4am PST).
> (Fhweeh)
> After the succesful testing of the production environment, we tested the
> fail-over which resulted in SQL Server not starting on the secondary node.
> All the resources came right up on it, but not SQL Server. The only error
> that was that it was not able to locate the file on 'O\logs\mastlog.ldf'.
> This error did not make sense since SQL Server uses the same file for the
> primary node. We were pressed for time since it was closing to start of
> business East Coast time, so we left the server as is.
> Throughout the day there were other issues that arose, one in particular
> was certain systems were not able to connect to the server via TCP/IP. In
> order to have them connect they needed to create an alias of the server
> and use Name Pipes. This seems to be a rising concern because there are
> users who need to connect via ODBC to a widely used particular Access
> Application, which seems to only like the TCP/IP route. I am somewhat
> sure this is related to the cluster failure.
> Anyway, this is the first time I've had to take a breathe to revisit the
> problem at hand. We have been dealing with another server that crashed on
> the same day, resulting in a brand new build of a SQL Server Cluster
> environment (completely non related to the issue at hand).
> I'm sorry for the long winded story. Would you have any idea as to why
> the cluster would fail on failover along with the TCP/IP issue?
> Thanks in Advanced..
>
>
>
|||First, what you should have done with a blown model DB:
Start SQL Server in single user mode with trace flag -T3608. This stops SQL from recovering anything except the master database. Reattach Model. If necessary, use files copied from another installation at the exact same SP and Hotfix level. Stop SQL Server and restart normally. Sorry, but it really is that simple. Oh, and lock whoever detached "model" out of the system. HE is too dangerous to allow near your system.
You didn't mention whether you blew the cluster away or not or just rebuilt SQL. If you blew the cluster away, make sure that each disk resource has the same drive letter on all nodes and the disk resources fail over correctly from node to node. Stop the resource group, move it, and start each disk resource independently to test.
The Named Pipes only issue sounds like an incomplete SP3a install. Windows 2003 will prevent TCP/IP access if it detects a pre-SP3a SQL installation. Follow this article and re-apply SP3a.
http://support.microsoft.com/default...b;en-us;815431
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Admiral" <admiral@.blackopsplatoon.com> wrote in message news:uoXFWWbBGHA.216@.TK2MSFTNGP15.phx.gbl...
We had an error over the weekend of mass porportions(Sunday 3pm PST). Long story short; the model database was detached and the SQL Server was stopped, with it still detached. This happened to happen on our primary Production Database Clustered Server which is the bread-n-butter of the compay. (OUCH!)
It was time for some fast actions. We started the re-install SQL Server. In order to do so, the previous install had to be uninstalled. This seemed to go smoothly enough, but when re-applying the SP3a, we encountered an error. After researching the error, apparently in a clustered environment this will occur since the SP3a files still reside on the node(s). Microsoft states that if within a particular log file it results with an 'Installation was Successful', to disregard the error. I double checked the log file and sure enough the error was disregarded.
We moved along with the installation. We were able to restore all the user databases and all system databases with the exception of the master database. Unfortunately, even with starting SQL Server in single-user mode, the restore of the master database would not take. So it was not restored, but all other databases were. Fortunately, I ran a quick script to recover all the user logins previous to the disaster, which I reapplied to the new installation of SQL Server. Everything came back up and the QA Team successfully tested the production Application (Monday 4am PST). (Fhweeh)
After the succesful testing of the production environment, we tested the fail-over which resulted in SQL Server not starting on the secondary node. All the resources came right up on it, but not SQL Server. The only error that was that it was not able to locate the file on 'O\logs\mastlog.ldf'. This error did not make sense since SQL Server uses the same file for the primary node. We were pressed for time since it was closing to start of business East Coast time, so we left the server as is.
Throughout the day there were other issues that arose, one in particular was certain systems were not able to connect to the server via TCP/IP. In order to have them connect they needed to create an alias of the server and use Name Pipes. This seems to be a rising concern because there are users who need to connect via ODBC to a widely used particular Access Application, which seems to only like the TCP/IP route. I am somewhat sure this is related to the cluster failure.
Anyway, this is the first time I've had to take a breathe to revisit the problem at hand. We have been dealing with another server that crashed on the same day, resulting in a brand new build of a SQL Server Cluster environment (completely non related to the issue at hand).
I'm sorry for the long winded story. Would you have any idea as to why the cluster would fail on failover along with the TCP/IP issue?
Thanks in Advanced..
|||This information helps.
Try this article:
http://support.microsoft.com/default...b;en-us;555017
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Admiral" <admiral@.blackopsplatoon.com> wrote in message
news:un7D9ZbBGHA.2840@.TK2MSFTNGP12.phx.gbl...
> 95% of the systems that were not able to connect to the server via TCP/IP
> were Windows 2003 Server systems.
>
> "Admiral" <admiral@.blackopsplatoon.com> wrote in message
> news:uoXFWWbBGHA.216@.TK2MSFTNGP15.phx.gbl...
> We had an error over the weekend of mass porportions(Sunday 3pm PST).
> Long story short; the model database was detached and the SQL Server was
> stopped, with it still detached. This happened to happen on our primary
> Production Database Clustered Server which is the bread-n-butter of the
> compay. (OUCH!)
> It was time for some fast actions. We started the re-install SQL Server.
> In order to do so, the previous install had to be uninstalled. This
> seemed to go smoothly enough, but when re-applying the SP3a, we
> encountered an error. After researching the error, apparently in a
> clustered environment this will occur since the SP3a files still reside on
> the node(s). Microsoft states that if within a particular log file it
> results with an 'Installation was Successful', to disregard the error. I
> double checked the log file and sure enough the error was disregarded.
> We moved along with the installation. We were able to restore all the
> user databases and all system databases with the exception of the master
> database. Unfortunately, even with starting SQL Server in single-user
> mode, the restore of the master database would not take. So it was not
> restored, but all other databases were. Fortunately, I ran a quick script
> to recover all the user logins previous to the disaster, which I reapplied
> to the new installation of SQL Server. Everything came back up and the QA
> Team successfully tested the production Application (Monday 4am PST).
> (Fhweeh)
> After the succesful testing of the production environment, we tested the
> fail-over which resulted in SQL Server not starting on the secondary node.
> All the resources came right up on it, but not SQL Server. The only error
> that was that it was not able to locate the file on 'O\logs\mastlog.ldf'.
> This error did not make sense since SQL Server uses the same file for the
> primary node. We were pressed for time since it was closing to start of
> business East Coast time, so we left the server as is.
> Throughout the day there were other issues that arose, one in particular
> was certain systems were not able to connect to the server via TCP/IP. In
> order to have them connect they needed to create an alias of the server
> and use Name Pipes. This seems to be a rising concern because there are
> users who need to connect via ODBC to a widely used particular Access
> Application, which seems to only like the TCP/IP route. I am somewhat
> sure this is related to the cluster failure.
> Anyway, this is the first time I've had to take a breathe to revisit the
> problem at hand. We have been dealing with another server that crashed on
> the same day, resulting in a brand new build of a SQL Server Cluster
> environment (completely non related to the issue at hand).
> I'm sorry for the long winded story. Would you have any idea as to why
> the cluster would fail on failover along with the TCP/IP issue?
> Thanks in Advanced..
>
>
>
|||Geoff,
I truly appreciate your response. I actually did try to reattach the model
using this method, unfortunately everytime I would attemp to attach the
model db, SQL Server would immediately turn off. At the time I was really
pressed for time, which led to the decision for a re-install. I do believe
there is more to the story that was not told to me.
Also, I do apologize if I led you to believe that the cluster is on a
Windows 2003 platform. It actually resides on a Windows 2000 Advanced
Server. The link you provided me, should still work for W2k? Like I
mentioned when it rains it pours and I've been putting out too many fires
for a Christmas week. I can't thank you enough for the response.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OFkKlhbBGHA.1088@.tk2msftngp13.phx.gbl...
First, what you should have done with a blown model DB:
Start SQL Server in single user mode with trace flag -T3608. This stops SQL
from recovering anything except the master database. Reattach Model. If
necessary, use files copied from another installation at the exact same SP
and Hotfix level. Stop SQL Server and restart normally. Sorry, but it
really is that simple. Oh, and lock whoever detached "model" out of the
system. HE is too dangerous to allow near your system.
You didn't mention whether you blew the cluster away or not or just rebuilt
SQL. If you blew the cluster away, make sure that each disk resource has
the same drive letter on all nodes and the disk resources fail over
correctly from node to node. Stop the resource group, move it, and start
each disk resource independently to test.
The Named Pipes only issue sounds like an incomplete SP3a install. Windows
2003 will prevent TCP/IP access if it detects a pre-SP3a SQL installation.
Follow this article and re-apply SP3a.
http://support.microsoft.com/default...b;en-us;815431
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Admiral" <admiral@.blackopsplatoon.com> wrote in message
news:uoXFWWbBGHA.216@.TK2MSFTNGP15.phx.gbl...
We had an error over the weekend of mass porportions(Sunday 3pm PST).
Long story short; the model database was detached and the SQL Server was
stopped, with it still detached. This happened to happen on our primary
Production Database Clustered Server which is the bread-n-butter of the
compay. (OUCH!)
It was time for some fast actions. We started the re-install SQL Server.
In order to do so, the previous install had to be uninstalled. This seemed
to go smoothly enough, but when re-applying the SP3a, we encountered an
error. After researching the error, apparently in a clustered environment
this will occur since the SP3a files still reside on the node(s).
Microsoft states that if within a particular log file it results with an
'Installation was Successful', to disregard the error. I double checked the
log file and sure enough the error was disregarded.
We moved along with the installation. We were able to restore all the
user databases and all system databases with the exception of the master
database. Unfortunately, even with starting SQL Server in single-user mode,
the restore of the master database would not take. So it was not restored,
but all other databases were. Fortunately, I ran a quick script to recover
all the user logins previous to the disaster, which I reapplied to the new
installation of SQL Server. Everything came back up and the QA Team
successfully tested the production Application (Monday 4am PST). (Fhweeh)
After the succesful testing of the production environment, we tested the
fail-over which resulted in SQL Server not starting on the secondary node.
All the resources came right up on it, but not SQL Server. The only error
that was that it was not able to locate the file on 'O\logs\mastlog.ldf'.
This error did not make sense since SQL Server uses the same file for the
primary node. We were pressed for time since it was closing to start of
business East Coast time, so we left the server as is.
Throughout the day there were other issues that arose, one in particular
was certain systems were not able to connect to the server via TCP/IP. In
order to have them connect they needed to create an alias of the server and
use Name Pipes. This seems to be a rising concern because there are users
who need to connect via ODBC to a widely used particular Access Application,
which seems to only like the TCP/IP route. I am somewhat sure this is
related to the cluster failure.
Anyway, this is the first time I've had to take a breathe to revisit the
problem at hand. We have been dealing with another server that crashed on
the same day, resulting in a brand new build of a SQL Server Cluster
environment (completely non related to the issue at hand).
I'm sorry for the long winded story. Would you have any idea as to why
the cluster would fail on failover along with the TCP/IP issue?
Thanks in Advanced..
|||The process is unnecessary for Windows 2000. The enhanced security of
Windows 2003 requires the extra steps.
Try and run comclust.exe on each node to set MSDTC to cluster mode. It
can't hurt and it may help. It definitely sounds like a "I didn't touch
anything" situation where you aren't getting the full story.
This one may be worth opening a PSS case. They can send you a diagnostic
package that will tell them exactly what is broken and they can walk you
through fixing it.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Admiral" <admiral@.blackopsplatoon.com> wrote in message
news:uXPvMDkBGHA.628@.TK2MSFTNGP14.phx.gbl...
> Geoff,
> I truly appreciate your response. I actually did try to reattach the
> model using this method, unfortunately everytime I would attemp to attach
> the model db, SQL Server would immediately turn off. At the time I was
> really pressed for time, which led to the decision for a re-install. I do
> believe there is more to the story that was not told to me.
> Also, I do apologize if I led you to believe that the cluster is on a
> Windows 2003 platform. It actually resides on a Windows 2000 Advanced
> Server. The link you provided me, should still work for W2k? Like I
> mentioned when it rains it pours and I've been putting out too many fires
> for a Christmas week. I can't thank you enough for the response.
>
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OFkKlhbBGHA.1088@.tk2msftngp13.phx.gbl...
> First, what you should have done with a blown model DB:
> Start SQL Server in single user mode with trace flag -T3608. This stops
> SQL from recovering anything except the master database. Reattach Model.
> If necessary, use files copied from another installation at the exact same
> SP and Hotfix level. Stop SQL Server and restart normally. Sorry, but it
> really is that simple. Oh, and lock whoever detached "model" out of the
> system. HE is too dangerous to allow near your system.
> You didn't mention whether you blew the cluster away or not or just
> rebuilt SQL. If you blew the cluster away, make sure that each disk
> resource has the same drive letter on all nodes and the disk resources
> fail over correctly from node to node. Stop the resource group, move it,
> and start each disk resource independently to test.
> The Named Pipes only issue sounds like an incomplete SP3a install.
> Windows 2003 will prevent TCP/IP access if it detects a pre-SP3a SQL
> installation. Follow this article and re-apply SP3a.
> http://support.microsoft.com/default...b;en-us;815431
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Admiral" <admiral@.blackopsplatoon.com> wrote in message
> news:uoXFWWbBGHA.216@.TK2MSFTNGP15.phx.gbl...
> We had an error over the weekend of mass porportions(Sunday 3pm PST).
> Long story short; the model database was detached and the SQL Server was
> stopped, with it still detached. This happened to happen on our primary
> Production Database Clustered Server which is the bread-n-butter of the
> compay. (OUCH!)
> It was time for some fast actions. We started the re-install SQL Server.
> In order to do so, the previous install had to be uninstalled. This
> seemed to go smoothly enough, but when re-applying the SP3a, we
> encountered an error. After researching the error, apparently in a
> clustered environment this will occur since the SP3a files still reside on
> the node(s). Microsoft states that if within a particular log file it
> results with an 'Installation was Successful', to disregard the error. I
> double checked the log file and sure enough the error was disregarded.
> We moved along with the installation. We were able to restore all the
> user databases and all system databases with the exception of the master
> database. Unfortunately, even with starting SQL Server in single-user
> mode, the restore of the master database would not take. So it was not
> restored, but all other databases were. Fortunately, I ran a quick script
> to recover all the user logins previous to the disaster, which I reapplied
> to the new installation of SQL Server. Everything came back up and the QA
> Team successfully tested the production Application (Monday 4am PST).
> (Fhweeh)
> After the succesful testing of the production environment, we tested the
> fail-over which resulted in SQL Server not starting on the secondary node.
> All the resources came right up on it, but not SQL Server. The only error
> that was that it was not able to locate the file on 'O\logs\mastlog.ldf'.
> This error did not make sense since SQL Server uses the same file for the
> primary node. We were pressed for time since it was closing to start of
> business East Coast time, so we left the server as is.
> Throughout the day there were other issues that arose, one in particular
> was certain systems were not able to connect to the server via TCP/IP. In
> order to have them connect they needed to create an alias of the server
> and use Name Pipes. This seems to be a rising concern because there are
> users who need to connect via ODBC to a widely used particular Access
> Application, which seems to only like the TCP/IP route. I am somewhat
> sure this is related to the cluster failure.
> Anyway, this is the first time I've had to take a breathe to revisit the
> problem at hand. We have been dealing with another server that crashed on
> the same day, resulting in a brand new build of a SQL Server Cluster
> environment (completely non related to the issue at hand).
> I'm sorry for the long winded story. Would you have any idea as to why
> the cluster would fail on failover along with the TCP/IP issue?
> Thanks in Advanced..
>
>
>
|||ouch...
this situation happened a while ago for us. one of our techs dettached both
the model AND msdb database (whilst in single user mode) and then sql server
was shut down. Well it would not start up.
I managed to execute the following command on the server itself:
sqlservr -c -f -s <instancename> /T3608
This worked bringing up the sql server in minimal mode. Thereupon i used
query analyser to connect and reattached both the msdb and model databases.
All was back to normal
Best of luck!
john
"Admiral" <admiral@.blackopsplatoon.com> wrote in message
news:uXPvMDkBGHA.628@.TK2MSFTNGP14.phx.gbl...
> Geoff,
> I truly appreciate your response. I actually did try to reattach the
> model using this method, unfortunately everytime I would attemp to attach
> the model db, SQL Server would immediately turn off. At the time I was
> really pressed for time, which led to the decision for a re-install. I do
> believe there is more to the story that was not told to me.
> Also, I do apologize if I led you to believe that the cluster is on a
> Windows 2003 platform. It actually resides on a Windows 2000 Advanced
> Server. The link you provided me, should still work for W2k? Like I
> mentioned when it rains it pours and I've been putting out too many fires
> for a Christmas week. I can't thank you enough for the response.
>
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OFkKlhbBGHA.1088@.tk2msftngp13.phx.gbl...
> First, what you should have done with a blown model DB:
> Start SQL Server in single user mode with trace flag -T3608. This stops
> SQL from recovering anything except the master database. Reattach Model.
> If necessary, use files copied from another installation at the exact same
> SP and Hotfix level. Stop SQL Server and restart normally. Sorry, but it
> really is that simple. Oh, and lock whoever detached "model" out of the
> system. HE is too dangerous to allow near your system.
> You didn't mention whether you blew the cluster away or not or just
> rebuilt SQL. If you blew the cluster away, make sure that each disk
> resource has the same drive letter on all nodes and the disk resources
> fail over correctly from node to node. Stop the resource group, move it,
> and start each disk resource independently to test.
> The Named Pipes only issue sounds like an incomplete SP3a install.
> Windows 2003 will prevent TCP/IP access if it detects a pre-SP3a SQL
> installation. Follow this article and re-apply SP3a.
> http://support.microsoft.com/default...b;en-us;815431
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Admiral" <admiral@.blackopsplatoon.com> wrote in message
> news:uoXFWWbBGHA.216@.TK2MSFTNGP15.phx.gbl...
> We had an error over the weekend of mass porportions(Sunday 3pm PST).
> Long story short; the model database was detached and the SQL Server was
> stopped, with it still detached. This happened to happen on our primary
> Production Database Clustered Server which is the bread-n-butter of the
> compay. (OUCH!)
> It was time for some fast actions. We started the re-install SQL Server.
> In order to do so, the previous install had to be uninstalled. This
> seemed to go smoothly enough, but when re-applying the SP3a, we
> encountered an error. After researching the error, apparently in a
> clustered environment this will occur since the SP3a files still reside on
> the node(s). Microsoft states that if within a particular log file it
> results with an 'Installation was Successful', to disregard the error. I
> double checked the log file and sure enough the error was disregarded.
> We moved along with the installation. We were able to restore all the
> user databases and all system databases with the exception of the master
> database. Unfortunately, even with starting SQL Server in single-user
> mode, the restore of the master database would not take. So it was not
> restored, but all other databases were. Fortunately, I ran a quick script
> to recover all the user logins previous to the disaster, which I reapplied
> to the new installation of SQL Server. Everything came back up and the QA
> Team successfully tested the production Application (Monday 4am PST).
> (Fhweeh)
> After the succesful testing of the production environment, we tested the
> fail-over which resulted in SQL Server not starting on the secondary node.
> All the resources came right up on it, but not SQL Server. The only error
> that was that it was not able to locate the file on 'O\logs\mastlog.ldf'.
> This error did not make sense since SQL Server uses the same file for the
> primary node. We were pressed for time since it was closing to start of
> business East Coast time, so we left the server as is.
> Throughout the day there were other issues that arose, one in particular
> was certain systems were not able to connect to the server via TCP/IP. In
> order to have them connect they needed to create an alias of the server
> and use Name Pipes. This seems to be a rising concern because there are
> users who need to connect via ODBC to a widely used particular Access
> Application, which seems to only like the TCP/IP route. I am somewhat
> sure this is related to the cluster failure.
> Anyway, this is the first time I've had to take a breathe to revisit the
> problem at hand. We have been dealing with another server that crashed on
> the same day, resulting in a brand new build of a SQL Server Cluster
> environment (completely non related to the issue at hand).
> I'm sorry for the long winded story. Would you have any idea as to why
> the cluster would fail on failover along with the TCP/IP issue?
> Thanks in Advanced..
>
>
>
|||I will give comclust.exe an attempt. I am looking into a solution for this
and of course if needed we will open a PSS case. I do have the impression
that SP3 was not fully installed even though it stated on the Microsoft site
otherwise. Is it worth a try to use the suggested stated on the link you
provided?
I honestly did not have time to investigate as to why the cluster would not
fail over. I quickly did give it a try to make sure the disks failed over
successfully, but will do so again tonight.
I thank you again for your continued help.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OmJuIWkBGHA.2704@.TK2MSFTNGP15.phx.gbl...
> The process is unnecessary for Windows 2000. The enhanced security of
> Windows 2003 requires the extra steps.
> Try and run comclust.exe on each node to set MSDTC to cluster mode. It
> can't hurt and it may help. It definitely sounds like a "I didn't touch
> anything" situation where you aren't getting the full story.
> This one may be worth opening a PSS case. They can send you a diagnostic
> package that will tell them exactly what is broken and they can walk you
> through fixing it.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Admiral" <admiral@.blackopsplatoon.com> wrote in message
> news:uXPvMDkBGHA.628@.TK2MSFTNGP14.phx.gbl...
>
|||The named pipes alias is necessary for Windows 2003. It is not necessary
for Windows 2000. I would definitely try and reinstall SP3a and see if it
helps.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Admiral" <admiral@.blackopsplatoon.com> wrote in message
news:OdF8h1kBGHA.2704@.TK2MSFTNGP15.phx.gbl...
>I will give comclust.exe an attempt. I am looking into a solution for this
>and of course if needed we will open a PSS case. I do have the impression
>that SP3 was not fully installed even though it stated on the Microsoft
>site otherwise. Is it worth a try to use the suggested stated on the link
>you provided?
> I honestly did not have time to investigate as to why the cluster would
> not fail over. I quickly did give it a try to make sure the disks failed
> over successfully, but will do so again tonight.
> I thank you again for your continued help.
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OmJuIWkBGHA.2704@.TK2MSFTNGP15.phx.gbl...
>