Recently Rebuilt SQL2K Cluster

I have recently had to rebuild my SQL Server 2000 sp3a 2 node active/passive
cluster running on Windows 2000 AS SP4. We ran into issues about two weeks
ago after a SAN migration (I'll spare all the gory details). When my
previous cluster died, I used one of the nodes as a stand alone instance of
SQL Server (to run our production database). On the other former node, I
removed the OS clustering software and had to manually remove SQL Server. I
eventually (with the assistance of Donna L. at MS) was able to get a single
node cluster of SQL2K running on physical server PRODSQL01 (the virtual
server name is PRODSQLCL01). While the cluster was running as a single node
cluster, I applied sp3a and the 818 hotfix. I then migrated the data from
the stand alone instance to PRODSQLCL01 and started to use it for production.
I turned my attention to the other box and removed and reinstalled the
Windows 2000 cluster software. This box is now called PRODSQL02. I was able
to add PRODSQL02 to the windows cluster, and the successfully added it via
SQL Server setup to the virtual server. My issue now is that PRODSQL02 has
the RTM versions of the binaries. I am not sure if I have a setup issue or I
misunderstand point 3.10 of the sp3a readme. Currently the virtual server is
running on PRODSQL01. If I attempt follow the steps under "If you need to
rebuild a node in the failover cluster..." from PRODSQL02 I am only able to
select the Virtual Server, and if I continue setup I get this error "all
cluster disks available to this virtual server are owned by other node(s)"
and then "Setup was unable to verify the state of the server for an upgrade.
Verify the server is able to start and that you provided a valid sa password
and restart setup". I understand that the virtual resources are only
available on the currently active node, but the way I read the instructions I
should be able to run the service pack installation on the inactive node. Do
I need to rerun the SP (and hotfix) setup on SQL01 (since the virtual server
is running there)? Do I need to move the resources to SQL02 and run the SP
setup there? I just need clarification of where to run the SP setup since I
just added SQL02 to this cluster.
You should be able to run setup on the non-host node and it will upgrade the
local binaries. Try rebooting the newly added RTM node and see if it helps.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Justin Hoffmann" <Justin Hoffmann@.discussions.microsoft.com> wrote in
message news:A214ED19-F6CD-4D25-90CE-0A1D456750BD@.microsoft.com...
>I have recently had to rebuild my SQL Server 2000 sp3a 2 node
>active/passive
> cluster running on Windows 2000 AS SP4. We ran into issues about two
> weeks
> ago after a SAN migration (I'll spare all the gory details). When my
> previous cluster died, I used one of the nodes as a stand alone instance
> of
> SQL Server (to run our production database). On the other former node, I
> removed the OS clustering software and had to manually remove SQL Server.
> I
> eventually (with the assistance of Donna L. at MS) was able to get a
> single
> node cluster of SQL2K running on physical server PRODSQL01 (the virtual
> server name is PRODSQLCL01). While the cluster was running as a single
> node
> cluster, I applied sp3a and the 818 hotfix. I then migrated the data from
> the stand alone instance to PRODSQLCL01 and started to use it for
> production.
> I turned my attention to the other box and removed and reinstalled the
> Windows 2000 cluster software. This box is now called PRODSQL02. I was
> able
> to add PRODSQL02 to the windows cluster, and the successfully added it via
> SQL Server setup to the virtual server. My issue now is that PRODSQL02
> has
> the RTM versions of the binaries. I am not sure if I have a setup issue
> or I
> misunderstand point 3.10 of the sp3a readme. Currently the virtual server
> is
> running on PRODSQL01. If I attempt follow the steps under "If you need to
> rebuild a node in the failover cluster..." from PRODSQL02 I am only able
> to
> select the Virtual Server, and if I continue setup I get this error "all
> cluster disks available to this virtual server are owned by other node(s)"
> and then "Setup was unable to verify the state of the server for an
> upgrade.
> Verify the server is able to start and that you provided a valid sa
> password
> and restart setup". I understand that the virtual resources are only
> available on the currently active node, but the way I read the
> instructions I
> should be able to run the service pack installation on the inactive node.
> Do
> I need to rerun the SP (and hotfix) setup on SQL01 (since the virtual
> server
> is running there)? Do I need to move the resources to SQL02 and run the
> SP
> setup there? I just need clarification of where to run the SP setup since
> I
> just added SQL02 to this cluster.
>
|||Geoff:
I've rebooted the newly added RTM node several times and it doesn't seem to
help. When I launch SP3 setup (via setup.bat in the local sql2ksp3
directory) on the newly added RTM node I get to the screen that says Computer
Name. Local Computer is grayed out, there is a box for the existing Virtual
Server name. When I type the virtual server name in the box and press next,
I get the error messages I mentioned in my original post. Is there anything
else you can recommend?
Thanks,
Justin
"Geoff N. Hiten" wrote:

> You should be able to run setup on the non-host node and it will upgrade the
> local binaries. Try rebooting the newly added RTM node and see if it helps.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
|||This is how I wound up fixing this:
I ran sp3a setup from PRODSQL01, which was the machine in control of the SQL
Virtual server at the time. It updated the binaries on PRODSQL02, but in
order to do so, the SQL Server service was stopped. Once sp3a setup
completed, I had to reboot PRODSQL02. When it was back up, I verified the
version of the sqlservr.exe file, and it was 8.00.760 (right click,
properties, version). Once I established that sp3a took on the newly added
node, I started the setup for the 8.00.818 security hotfix. SQL Server went
down briefly again, and then the binaries on the newly added box were
updated. I then installed MDAC 2.8 on this node and rebooted. Once
PRODSQL02 was running again I tested moving the cluster resources from 01 to
02. It succeeded. While SQL was running on 02 I ran throught setting up
Imceda SQL Lite Speed (again, it was on 01 but not 02). Once that completed,
I was able to verify that my normally scheduled transaction log backups
completed successfully. Both machines run SQL Server and perform the
backups correctly. I am not sure about the root cause of the issue I
encountered with not being able to run the sp and hotfix on the newly added
node when it wasn't running the Virtual SQL Server (the sp3a readme seems to
indicate that you can), but I finally have a 2 node active passive cluster
running again.
|||Just to clarify, it is active passive, so only one node runs the Virtual SQL
Server at any time. I was trying to say that each node is able to run SQL
Server, and each node is able to run the SQL Lite Speed backups. I had been
concerned that somehow I would get PRODSQL02 all patched but then failing
over would not work correctly. Everything is working OK.
"Justin Hoffmann" wrote:

> This is how I wound up fixing this:
> I ran sp3a setup from PRODSQL01, which was the machine in control of the SQL
> Virtual server at the time. It updated the binaries on PRODSQL02, but in
> order to do so, the SQL Server service was stopped. Once sp3a setup
> completed, I had to reboot PRODSQL02. When it was back up, I verified the
> version of the sqlservr.exe file, and it was 8.00.760 (right click,
> properties, version). Once I established that sp3a took on the newly added
> node, I started the setup for the 8.00.818 security hotfix. SQL Server went
> down briefly again, and then the binaries on the newly added box were
> updated. I then installed MDAC 2.8 on this node and rebooted. Once
> PRODSQL02 was running again I tested moving the cluster resources from 01 to
> 02. It succeeded. While SQL was running on 02 I ran throught setting up
> Imceda SQL Lite Speed (again, it was on 01 but not 02). Once that completed,
> I was able to verify that my normally scheduled transaction log backups
> completed successfully. Both machines run SQL Server and perform the
> backups correctly. I am not sure about the root cause of the issue I
> encountered with not being able to run the sp and hotfix on the newly added
> node when it wasn't running the Virtual SQL Server (the sp3a readme seems to
> indicate that you can), but I finally have a 2 node active passive cluster
> running again.
|||The current correct term is "Single-Instance". "Active-Active" and its
cousins all refer to technology used specifically in SQL server 7.0 only.
Sometimes the binary only upgrade doesn't work right. Your solution is the
fallback solution but it does have the obvious downside of taking the SQL
server offline for a short time during the upgrade.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Adminstrator
"Justin Hoffmann" <JustinHoffmann@.discussions.microsoft.com> wrote in
message news:DA3E156A-EAFD-45D9-B72E-AB4A657D53A0@.microsoft.com...[vbcol=seagreen]
> Just to clarify, it is active passive, so only one node runs the Virtual
> SQL
> Server at any time. I was trying to say that each node is able to run SQL
> Server, and each node is able to run the SQL Lite Speed backups. I had
> been
> concerned that somehow I would get PRODSQL02 all patched but then failing
> over would not work correctly. Everything is working OK.
> "Justin Hoffmann" wrote:

rebuildm can not start sql server

Hi All,
I ran the rebuildm.exe and it had been showing "server configuration progress..." for more than 10 minutes. So I stopped that process manually. Now I can not start sql server. why?
When I start it, the status first changes to "starting" and again "stopped".
Please help...
Hi!
Rebuildm.exe copies master database files from CD (search for mast*.mdf and
mast*.ldf). When copied from CD, files are marked with read-only attribute.
This might be the reason for the problem. Copy the files mnually, change the
attribute and rerun rebuildm with copied files.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"suresh" <anonymous@.discussions.microsoft.com> wrote in message
news:F694DCC2-0823-48EC-ACCC-92291C1A4E23@.microsoft.com...
> Hi All,
> I ran the rebuildm.exe and it had been showing "server configuration
progress..." for more than 10 minutes. So I stopped that process manually.
Now I can not start sql server. why?
> When I start it, the status first changes to "starting" and again
"stopped".
> Please help...
|||No, it does not work !
|||Any errors in Windows Event Log and SQL Server Error Log?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"suresh" <anonymous@.discussions.microsoft.com> wrote in message
news:B1271FD4-A0E9-401D-9E8E-91F0E4BC8105@.microsoft.com...
> No, it does not work !
sql

Really large integer constants - confused

I ran the following in SQL Server 2000 and 2005 with the same results.
select 1234567890123456 / 1234567890123
select 1234567890123456 / convert(bigint,1234567890123)
select convert(bigint,1234567890123456) /
convert(bigint,1234567890123)
1000.00000000036936
---
1000.00000000036936000332
1000
Everything I have found in Books on Line (both 2000 and 2005 versions)
says that talks about constants says those numbers are "integer
constants":
"integer constants are represented by a string of numbers not enclosed
in quotation marks and do not contain decimal points. integer
constants must be whole numbers; they cannot contain decimals."
Obviously these long "strings of numbers not enclosed in quotation
marks" and not containing decimal points are not being treated as
Integers.
Can anyone (1) explain what is going on, and (2) point me to where it
is documented?
Thanks!
Roy> 1000.00000000036936
This post is not going to be helpful at all. Just wanted to post my
observations. My first reaction was that it looks like they are implicity
being converted to FLOAT. But this is not true, since you actually lose
precision in that case:
SELECT CONVERT(FLOAT, 1234567890123456) / CONVERT(FLOAT, 1234567890123)
--
1000.00000000037
But take a look at implicit conversion to decimal:
select 1.0 * 1234567890123456 / 1234567890123
--
1000.000000000369360
Pretty darn close...|||If you do this
select
1234567890123456 as c1,
1234567890123 as c2,
convert(bigint,1234567890123456) as c3,
convert(bigint,1234567890123) as c4
into sometable
and then look at the columns in the new table
it appears to show that integers bigger than 'int'
are converted to numeric with differing precisions.
I'm not aware of this in BOL.|||> and then look at the columns in the new table
> it appears to show that integers bigger than 'int'
> are converted to numeric with differing precisions.
> I'm not aware of this in BOL.
More specifically, it looks like numeric literals exceeding 2147483647 are
converted to numeric(n), where n is the number of digits in the literal.
Consequently, the expression result is also numeric according to standard
data type precedence rules. The result is not an integer because
SELECT 1234567890123456 / 1234567890123
is functionally identical to:
SELECT
CAST(1234567890123456 AS numeric(16)) /
CAST(1234567890123 AS numeric(13))
and the result is numeric(30, 14) to allow for the remainder of the division
expression.
IMHO, this is contrary to the documentation and not intuitive. The final
result should be converted to an integer:
SELECT
CAST(
CAST(1234567890123456 AS numeric(16)) /
CAST(1234567890123 AS numeric(13))
AS numeric(38))
Hope this helps.
Dan Guzman
SQL Server MVP
<markc600@.hotmail.com> wrote in message
news:1140958196.462553.282950@.i40g2000cwc.googlegroups.com...
> If you do this
> select
> 1234567890123456 as c1,
> 1234567890123 as c2,
> convert(bigint,1234567890123456) as c3,
> convert(bigint,1234567890123) as c4
> into sometable
> and then look at the columns in the new table
> it appears to show that integers bigger than 'int'
> are converted to numeric with differing precisions.
> I'm not aware of this in BOL.
>|||>More specifically, it looks like numeric literals exceeding 2147483647 are
>converted to numeric(n), where n is the number of digits in the literal.
I am sure that is it, Dan. It appears that when they added bigint as
a data type they never got around to adjusting the code related to
integer constants.
I should check the docs for 7.0 next w to see if it is mentioned
there.
Do the MVPs still have a more direct line for reporting these sorts of
things than the general public?
Roy|||
Roy Harvey wrote:

>I am sure that is it, Dan. It appears that when they added bigint as
>a data type they never got around to adjusting the code related to
>integer constants.
>I should check the docs for 7.0 next w to see if it is mentioned
>there.
>Do the MVPs still have a more direct line for reporting these sorts of
>things than the general public?
>
Roy,
We have an inside channel, but the best route nowadays is the public
bug-reporting forum at http://lab.msdn.microsoft.com/ProductFeedback/.
I'd encourage you to file a bug report (if you think the behavior is wrong)
or a suggestion (to improve documentation or change behavior). The latter
might be more successful, since a bug report can be closed as "by design",
after which the request to improve documentation might more easily be lost.
There are two issues here: one is how literals are typed. That is a messy
issue, and not uniquely messy to T-SQL. I've reported some cases where
the same literal is typed differently in different contexts, but this is
not quite
as bad, and could be improved with better documentation. (BOL says that
a literal decimal has a decimal point, and that's not helpful here.)
The other issue is the internal/intermediate use of floating-point
arithmetic
in decimal calculations. That behavior (questionable in my estimation) also
causes other oddities, such as power(2.,57) ending up divisible by 10.
Since
DECIMAL is billed as an "exact numeric type" (though it's no more exact
that float - all types represent the values they represent exactly),
maybe someone
will care.
Here's a more glaring example of the first behavior:
select 2000000001/2 -- returns 1000000000
select 20000000001/2 -- returns 10000000000.500000
or select 1000000000/1000000001, 10000000000/10000000001
You can play around with this repro to see more:
declare @.s sql_variant
set @.s = 2147483648
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = 2147483647
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = -2147483647
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = -2147483648
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = cast(-2147483648 as int)
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = cast(25 as smallint)
Any change would be a breaking change, of course.
Steve Kass
Drew University
:

>Roy
>|||With later technologies like SQL Server 2005, you are empowered to submit
bug reports and suggestions directly via the Product Feedback Center at
http://lab.msdn.microsoft.com/productfeedback/. Documentation feedback can
be submitted via the Books Online. The SQL devs do an great job of
monitoring and acting on the feedback.
I submitted this as a bug
<http://lab.msdn.microsoft.com/Produ...BK46
489>.
Feel free to vote on it.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:611402d49jv1siu85i6a5crcmt69g22vtp@.
4ax.com...
> I am sure that is it, Dan. It appears that when they added bigint as
> a data type they never got around to adjusting the code related to
> integer constants.
> I should check the docs for 7.0 next w to see if it is mentioned
> there.
> Do the MVPs still have a more direct line for reporting these sorts of
> things than the general public?
> Roy|||Dan,
Thanks!
I visited the bug you created and validated and voted. I was going to
add a comment, but it said it would "Post In Newsgroup
microsoft.private.msdn.productfeedback.vb". I was thrown off by the
.vb ending, which sure sounds like a different product to me!
The note I was going to add was that the same applies to SQL Server
2000.
I will try to add feedback for the docs too, since that is where
something might actually be done.
Roy
On Sun, 26 Feb 2006 15:40:06 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>With later technologies like SQL Server 2005, you are empowered to submit
>bug reports and suggestions directly via the Product Feedback Center at
>http://lab.msdn.microsoft.com/productfeedback/. Documentation feedback can
>be submitted via the Books Online. The SQL devs do an great job of
>monitoring and acting on the feedback.
>I submitted this as a bug
><http://lab.msdn.microsoft.com/Produ...BK4
6489>.
>Feel free to vote on it.