3 node Cluster Question

Is it possible to have a node that is running one default and one named
instance of SQL Server 2000, A second node that's running a default instance
of sql server 2000 and have both failover to a third node? Any thoughts,
suggestions, or answers would eb appreciated particular if your're currently
running this conifiguration.
You only get one default instance per cluster. You can use DNS to alias an
old server name to the new server name. Three node clusters are legal using
Windows Server 2003 Enterprise Edition and SQL Server 2000 Enterprise
Edition. You can control the failover path for each instance independantly.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Roger Newton" <RogerNewton@.discussions.microsoft.com> wrote in message
news:257A442B-6027-4C21-A29E-A51C05C65E8D@.microsoft.com...
> Is it possible to have a node that is running one default and one named
> instance of SQL Server 2000, A second node that's running a default
instance
> of sql server 2000 and have both failover to a third node? Any thoughts,
> suggestions, or answers would eb appreciated particular if your're
currently
> running this conifiguration.
|||Thanks for responding,
So it sounds like that if I'm running windows 2003, SQL server 2000
enterprise have 1 default instance and 1 named instance on node A and 1 named
instance on node B I will be able to failover all instances to a third node
(node C) for failover purposes. Sound right to you?
"Geoff N. Hiten" wrote:

> You only get one default instance per cluster. You can use DNS to alias an
> old server name to the new server name. Three node clusters are legal using
> Windows Server 2003 Enterprise Edition and SQL Server 2000 Enterprise
> Edition. You can control the failover path for each instance independantly.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Roger Newton" <RogerNewton@.discussions.microsoft.com> wrote in message
> news:257A442B-6027-4C21-A29E-A51C05C65E8D@.microsoft.com...
> instance
> currently
>
>
|||You could look at it that way. Instances (default or named) don't really
have an owner node in SQL 2000. All nodes are equal, except during installs
or upgrades. You can set a preferred host order and limit which hosts are
allowed to run which instances, but you have to do that yourself after
setup. I personally run a N+1 cluster where I have three instances and four
nodes. My only gripe is I have to manually reset the preferred node order
if anybody fails over so I don't overcommit memory on a single node.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Roger Newton" <RogerNewton@.discussions.microsoft.com> wrote in message
news:C43C55D9-65E7-40FE-A4A5-4A3DC76EE9E3@.microsoft.com...
> Thanks for responding,
> So it sounds like that if I'm running windows 2003, SQL server 2000
> enterprise have 1 default instance and 1 named instance on node A and 1
named
> instance on node B I will be able to failover all instances to a third
node[vbcol=seagreen]
> (node C) for failover purposes. Sound right to you?
> "Geoff N. Hiten" wrote:
an[vbcol=seagreen]
using[vbcol=seagreen]
independantly.[vbcol=seagreen]
named[vbcol=seagreen]
thoughts,[vbcol=seagreen]
|||thanks for the help
"Geoff N. Hiten" wrote:

> You could look at it that way. Instances (default or named) don't really
> have an owner node in SQL 2000. All nodes are equal, except during installs
> or upgrades. You can set a preferred host order and limit which hosts are
> allowed to run which instances, but you have to do that yourself after
> setup. I personally run a N+1 cluster where I have three instances and four
> nodes. My only gripe is I have to manually reset the preferred node order
> if anybody fails over so I don't overcommit memory on a single node.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Roger Newton" <RogerNewton@.discussions.microsoft.com> wrote in message
> news:C43C55D9-65E7-40FE-A4A5-4A3DC76EE9E3@.microsoft.com...
> named
> node
> an
> using
> independantly.
> named
> thoughts,
>
>

3 Login failed every second

Hi.
In my system since 27th april every second appears in event\application
three failed connection with sa.

How can is possible ?

On this server there are all my website.

Can you help me ?

Saimon(Florence)Saimon (saimon181072@.supereva.it) writes:
> In my system since 27th april every second appears in event\application
> three failed connection with sa.
> How can is possible ?
> On this server there are all my website.

Is the server exposed on the Internet? In such case be glad as long as
the connection attempts fails.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

2-table join where only need one row of second table.

For discussion sake, I've got two tables: customers and orders. What I want
is a query that will return the most recent order for each customer. Let's
say the tables are like:

Customer: customerId, customerName
Orders: orderId, customerId, orderDate

The simple joins I've done give me back all of the orders for all of the
customers.

Any advice?Try

select * from customer c, orders o
where c.customerId = o.customerId
and c.orderId =
(
select max (o2.orderDate)
from orders o2
where o2.customerId = c.customerId
)

--
Regards Bagieta
~~~~~~~~~~~~~~~~~~~~~~~~~~~
dbDeveloper - Multiple databases editor
http://www.prominentus.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~

2nd Node does not come online

I have an "Active/Active" SQL 2000 cluster on Windows 2003 SP1. The first
node is the default instance and the second is a named instance. They are
both listenting on port 1433. When I fail the default instance over to the
2nd node, everything failsover and comes on line and I am able to connect and
query the data from both instances. I fail the node back over, again
everthing works.
When I fail the second node (named instance) over to the first node,
everthing fails over; however, the SQL Agent, Fulltext searching and the SQL
Server do not come on line. Any ideas on why this is happening? Thanks.
You can't have them both listening on port 1433. You can have one, say the
default, and the other DYNAMIC, which means SQL will randomly select a port.
When failed over it will attempt to acquire the same port as before, but not
always. The client rely on the Dynamic Discovery, UDP 1434, to detect which
port to use.
If you have these hard-coded, the named instance will fail whenever it fails
over to the node where the default instance has already acquired that port.
Consider it portetiquett, but no two TCP services can listen on the same
port on the same server. Sorry.
Sincerely,
Anthony Thomas

"MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
news:EC957BA9-76A8-42E4-AD8D-5F47354C149B@.microsoft.com...
> I have an "Active/Active" SQL 2000 cluster on Windows 2003 SP1. The first
> node is the default instance and the second is a named instance. They are
> both listenting on port 1433. When I fail the default instance over to the
> 2nd node, everything failsover and comes on line and I am able to connect
and
> query the data from both instances. I fail the node back over, again
> everthing works.
> When I fail the second node (named instance) over to the first node,
> everthing fails over; however, the SQL Agent, Fulltext searching and the
SQL
> Server do not come on line. Any ideas on why this is happening? Thanks.

2-nd edition of Celko Puzzle book in the works

2-nd edition of Celko Puzzle book in the works
I am getting material for a second edition of my SQL PUZZLES book
together of the next few months.
1) If anyone has a new puzzle, send it to me. You will get your name
in print, fame, glory and perhaps a free copy, depending on my
publisher.
2) If anyone has a new answer to an old puzzle, send it to me. In
particular, when I wrote the first edition, most products were still
using the SQL-86 Standards. That meant no OUTER JOIN, no CTE, no
derived tables, and limited standardized functions and so forth.
I'd like to see new answers with SQL-92 stuff and SQL-99 OLAP
extensions.
3) I will post some of the oldies on newsgroups and fish for new
answers. I will reward the best ones with an old IT book from my
library. I will try to keep my choice in the database area, but you
could get a copy of something weird, like an illustrated history of
punch cards.
4) Here is a starter, an oldie that appeared in one of my magazine
columns over a decade ago, before we had standard OUTER JOIN syntax.
My original answer was a complex nested nightmare; can you do better?
You are given an abbreviated table of personnel salary history
CREATE TABLE Salaries
(emp_name CHAR(10) NOT NULL,
sal_date DATETIME NOT NULL,
sal_amt DECIMAL (8,2) NOT NULL,
PRIMARY KEY (emp_name, sal_date));
INSERT INTO Salaries VALUES ('Tom', '1996-06-20', 500.00);
INSERT INTO Salaries VALUES ('Tom', '1996-08-20', 700.00);
INSERT INTO Salaries VALUES ('Tom', '1996-10-20', 800.00);
INSERT INTO Salaries VALUES ('Tom', '1996-12-20', 900.00);
INSERT INTO Salaries VALUES ('Dick', '1996-06-20', 500.00);
INSERT INTO Salaries VALUES ('Harry', '1996-07-20', 500.00);
INSERT INTO Salaries VALUES ('Harry', '1996-09-20', 700.00);
The goal is to produce a query that will show us the current salary and
the effective date of each employee, and his immediately previous
salary and effective date. If he is a new employee, then his previous
salary and effective date are shown as NULL. The results of the sample
data are:
Result
emp_name curr_date curr_amt prev_date prev_amt
========================================
==========
'Tom' '1996-12-20' 900.00 '1996-10-20' 800.00
'Harry' '1996-09-20' 700.00 '1996-07-20' 500.00
'Dick' '1996-06-20' 500.00 NULL NULLA new answer to an oldie?
WITH SalariesRank(emp_name,sal_date,sal_amt,P
os) AS
(SELECT emp_name,
sal_date,
sal_amt,
RANK() OVER(PARTITION BY emp_name ORDER BY sal_date DESC)
FROM Salaries)
SELECT c.emp_name,
c.sal_date AS curr_date,
c.sal_amt AS curr_amt,
p.sal_date AS prev_date,
p.sal_amt AS prev_amt
FROM SalariesRank c
LEFT OUTER JOIN SalariesRank p ON p.emp_name=c.emp_name AND p.Pos=2
WHERE c.Pos=1|||I like it!! here is the version I came up with, but have not tested:
SELECT S1.emp_name,
MAX (CASE WHEN rn = 1 THEN sal_date ELSE NULL END) AS curr_date,
MAX (CASE WHEN rn = 1 THEN sal_amt ELSE NULL END) AS curr_amt,
MAX (CASE WHEN rn = 2 THEN sal_date ELSE NULL END) AS prev_date,
MAX (CASE WHEN rn = 2 THEN sal_amt ELSE NULL END) AS prev_amt,
FROM (SELECT emp_name, sal_date, sal_amt,
RANK()OVER (PARTITION BY S1.emp_name ORDER BY sal_date
DESC)
FROM Salaries) AS S1 (emp_name, sal_date, sal_amt, rn)
WHERE rn < 3
GROUP BY S1.emp_name;
I avoid an OUTER JOIN, but at the expense of a GROUP BY.
Anybody else got one?|||Sorry, my format is no good, but this one is outer join wth correlated subs.
.
SELECT CS.Emp_name,
CS.Sal_date AS Curr_date,
CS.Sal_amt AS Curr_amt,
PS.Sal_date AS Prev_date,
PS.Sal_amt AS Prev_amt
FROM #Salaries CS LEFT OUTER JOIN
(SELECT Emp_name, Sal_date, Sal_amt FROM #Salaries t1
WHERE t1.Sal_Date = (SELECT MAX(Sal_date)
FROM #Salaries s2
WHERE s2.Emp_name = t1.Emp_name
AND s2.Sal_date < (SELECT MAX(Sal_date)
FROM #Salaries s3
WHERE s3.Emp_name = S2.Emp_name))) PS
ON CS.Emp_name = PS.Emp_name
WHERE CS.Sal_date = (SELECT MAX(Sal_date)
FROM #Salaries s1
WHERE s1.Emp_name = CS.Emp_name)|||> f anyone has a new answer to an old puzzle, send it to me
Joe,
what's the e-mail?|||jcelko212@.earthlink.net
I have the highest possible firewall, so I will need to let you in.|||How about a simple:
SELECT S1.emp_Name, S1.sal_date, S1.sal_amt, S2.Sal_date, S2.Sal_amt,
S3.sal_date
FROM Salaries S1
LEFT JOIN Salaries S2 ON S1.Emp_name = S2.Emp_name AND
S1.Sal_date > S2.Sal_date
LEFT JOIN Salaries S3 ON S1.Emp_name = S3.Emp_name AND
(S3.Sal_date > S1.Sal_date OR (S3.Sal_date > S2.Sal_date
AND S3.Sal_date < S1.Sal_date ))
WHERE S3.Emp_name IS NULL|||this is my attempt to solve the puzzle:
SELECT S1.emp AS emp_name, S1.sal_date AS curr_date, S1.sal_amt AS curr_amt,
CASE WHEN S2.sal_date <> S1.sal_date THEN S2.sal_date END AS prev_date,
CASE WHEN S2.sal_date <> S1.sal_date THEN S2.sal_amt END AS prev_amt
FROM Salaries AS S1
INNER JOIN Salaries AS S2
ON S2.emp = S1.emp
AND S2.sal_date = COALESCE((SELECT MAX(S4.sal_date)
FROM Salaries AS S4
WHERE S4.emp = S1.emp
AND S4.sal_date < S1.sal_date), S2.sal_date)
WHERE NOT EXISTS(SELECT *
FROM Salaries AS S3
WHERE S3.emp = S1.emp
AND S3.sal_date > S1.sal_date);
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)