i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
Showing posts with label price. Show all posts
Showing posts with label price. Show all posts
Friday, March 23, 2012
minimum value
i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero
"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
sql
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero
"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
sql
minimum value
i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
minimum price on earliest date
Ok my first posted question :
(This is related to a travel website)
I have the following table layout :
CREATE TABLE "public"."package" (
"id" BIGINT NOT NULL,
"accom_code" VARCHAR(4) NOT NULL,
"start_date" DATE NOT NULL,
"end_date" DATE NOT NULL,
"pricing_type" VARCHAR(2),
"indic_price" NUMERIC(7,2),
"unit_price" NUMERIC(7,2),
"adult_age_max_cnt" INTEGER,
CONSTRAINT "package_pkey" PRIMARY KEY("id")
) WITH OIDS;
The package table contains a list (a very large one) for holiday accomodation packages.
What i'm trying to get is the following :
The MINIMUM price using the following for "price" :
CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.unit_price
END AS NUMERIC(7,2))
AND
The minimum date (i.e. nearest start date) greater than today
WHERE the start_date equals the minimum start_date and the price equals the minimum price.
Any thoughts?
With any luck I will be able to give you an test data insert for this.
This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.What I have thus far :
SELECT p.accom_code
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?|||Ooh think I nearly got it, can someone verify this :
SELECT p.accom_code
,x.min_start
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
FROM package p
JOIN (
SELECT p.accom_code,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
) x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code,x.min_start
In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.
Edit : Just to finish it off I needed the start_date out of it as well ;) (added x.min_start to select and group clause)|||looks okay to me
i would not use the same alias "p" in more than one place in the query
and i would probably remove one of the following:AND p.duration = 7
AND p.duration = 7;)|||LOL oh yeah. I've cleaned it up in the above post now.
However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.|||Is there any way to remove the duplicate WHERE clauses in a query like thisdefine a view|||Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.|||That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.
What I DID want to be standard was the SQL query I was doing. :D|||Well, then you have to use delimited schema/table/column names in your query - as I said.|||I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
i.e. min price and min date is £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £189
They both match the minimum price and date and thus both records are output. DOH!
As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.|||Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably) ;)
(DB2, Oracle and SQLServer already have them in place.)
Your query, with CTEs, would become:WITH p AS
( SELECT accom_code, start_date,
MIN(CAST (CASE pricing_type
WHEN 'UN' THEN unit_price
WHEN 'PA' THEN indic_price*adult_age_max_cnt
ELSE indic_price
END AS NUMERIC(7,2))) as min_price
FROM package
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC',
'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
AND duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
),
x AS
( SELECT p.accom_code,MIN(p.start_date) as min_start
FROM p
GROUP BY p.accom_code
)
SELECT p.accom_code,x.min_start,p.min_price
FROM p INNER JOIN x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
GROUP BY p.accom_code,x.min_start
These are effectively "local view definitions", cf suggestion by r937.
(Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)sql
(This is related to a travel website)
I have the following table layout :
CREATE TABLE "public"."package" (
"id" BIGINT NOT NULL,
"accom_code" VARCHAR(4) NOT NULL,
"start_date" DATE NOT NULL,
"end_date" DATE NOT NULL,
"pricing_type" VARCHAR(2),
"indic_price" NUMERIC(7,2),
"unit_price" NUMERIC(7,2),
"adult_age_max_cnt" INTEGER,
CONSTRAINT "package_pkey" PRIMARY KEY("id")
) WITH OIDS;
The package table contains a list (a very large one) for holiday accomodation packages.
What i'm trying to get is the following :
The MINIMUM price using the following for "price" :
CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.unit_price
END AS NUMERIC(7,2))
AND
The minimum date (i.e. nearest start date) greater than today
WHERE the start_date equals the minimum start_date and the price equals the minimum price.
Any thoughts?
With any luck I will be able to give you an test data insert for this.
This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.What I have thus far :
SELECT p.accom_code
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?|||Ooh think I nearly got it, can someone verify this :
SELECT p.accom_code
,x.min_start
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
FROM package p
JOIN (
SELECT p.accom_code,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
) x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code,x.min_start
In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.
Edit : Just to finish it off I needed the start_date out of it as well ;) (added x.min_start to select and group clause)|||looks okay to me
i would not use the same alias "p" in more than one place in the query
and i would probably remove one of the following:AND p.duration = 7
AND p.duration = 7;)|||LOL oh yeah. I've cleaned it up in the above post now.
However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.|||Is there any way to remove the duplicate WHERE clauses in a query like thisdefine a view|||Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.|||That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.
What I DID want to be standard was the SQL query I was doing. :D|||Well, then you have to use delimited schema/table/column names in your query - as I said.|||I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
i.e. min price and min date is £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £189
They both match the minimum price and date and thus both records are output. DOH!
As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.|||Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably) ;)
(DB2, Oracle and SQLServer already have them in place.)
Your query, with CTEs, would become:WITH p AS
( SELECT accom_code, start_date,
MIN(CAST (CASE pricing_type
WHEN 'UN' THEN unit_price
WHEN 'PA' THEN indic_price*adult_age_max_cnt
ELSE indic_price
END AS NUMERIC(7,2))) as min_price
FROM package
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC',
'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
AND duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
),
x AS
( SELECT p.accom_code,MIN(p.start_date) as min_start
FROM p
GROUP BY p.accom_code
)
SELECT p.accom_code,x.min_start,p.min_price
FROM p INNER JOIN x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
GROUP BY p.accom_code,x.min_start
These are effectively "local view definitions", cf suggestion by r937.
(Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)sql
Monday, March 19, 2012
Miniature Clustering Lab
I'm looknig to set up a miniature clustering lab at home and looking for some
advice.
I would like to hopefully keep the price tag to around $750-$1250.
I've already found a couple of boxes that I'm going to use for my nodes
(dual node cluster). I can do all the internal stuff myself (NIC's, etc.).
They each are about $300.
What I need now is shared storage and a switch/hub. I'm new to the
networking side of things. What DO I need: switch, router, etc.? Also, is
there a way to set up things wirelessly? All my big stuff will be in a mini
server room in my basement and my office is upstairs.
If anyone has any advice, I'd really appreciate it.
Thank You!!!
Wireless is probably more of a pain thatn it is worth. A single switch or
hub will be fine. You can use a crossover cable for a two node cluster
heartbeat LAN. As for storage, you can use almost any SCSI drive. get a
self-powered enclosure ($50 or less) , a couple of cables, and a couple of
cheap controllers. I have done it with Adaptec cards, even with mis-matched
ones. It isn't particularly pretty, scalable or stable, but it does work.
One alternative that many people are using is Virtual Server from Microsoft.
Then you have one physical system that can simulate a cluster internally.
Good Luck,
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:C64DB097-820F-4443-BDCA-486493CCFAC7@.microsoft.com...
> I'm looknig to set up a miniature clustering lab at home and looking for
> some
> advice.
> I would like to hopefully keep the price tag to around $750-$1250.
> I've already found a couple of boxes that I'm going to use for my nodes
> (dual node cluster). I can do all the internal stuff myself (NIC's,
> etc.).
> They each are about $300.
> What I need now is shared storage and a switch/hub. I'm new to the
> networking side of things. What DO I need: switch, router, etc.? Also, is
> there a way to set up things wirelessly? All my big stuff will be in a
> mini
> server room in my basement and my office is upstairs.
> If anyone has any advice, I'd really appreciate it.
> Thank You!!!
|||Maybe you could give me your unofficial blessing on the following?
- Found a four port hub
- Two servers both with two NICS. One of the NIC's is connected directly to
the hub. This would constitute the two private IP addresses.
- The second NIC's would be a wireless adapters to act as the "public" IP
addresses. This is so I can see them from my office. Not a good idea?
- The network storage is a network storage device I found...160 GB. This
plugs directly into the hub as well? Once plugged into the hub, would I then
configure IP's, subnets, etc. What is the admin interface?
My next dumb question is this: when setting up the hub, do I just plug it in
and go? I've worked on network stuff before , but it's always been set up for
me. I just go in and assign IP addresses, network names, etc.
Thanks again!!
"Geoff N. Hiten" wrote:
> Wireless is probably more of a pain thatn it is worth. A single switch or
> hub will be fine. You can use a crossover cable for a two node cluster
> heartbeat LAN. As for storage, you can use almost any SCSI drive. get a
> self-powered enclosure ($50 or less) , a couple of cables, and a couple of
> cheap controllers. I have done it with Adaptec cards, even with mis-matched
> ones. It isn't particularly pretty, scalable or stable, but it does work.
> One alternative that many people are using is Virtual Server from Microsoft.
> Then you have one physical system that can simulate a cluster internally.
> Good Luck,
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:C64DB097-820F-4443-BDCA-486493CCFAC7@.microsoft.com...
>
>
|||Two problems. Wireless NICs almost always use DHCP addresses. Clusters
require hard-coded IP addresses.
NAS is a definite NO-NO for SQL in general and clusters in specific. There
is no way to arbitrate ownership of a NAS or any network storage type device
which completely rules them out for clustering. Direct Attach SCSI, iSCSI,
Virtual SCSI, or Fibre Channel are your options, even in a test and training
environment.
GNH
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:46BA24F2-5E44-46CE-95EA-A75825CC7BF3@.microsoft.com...[vbcol=seagreen]
> Maybe you could give me your unofficial blessing on the following?
> - Found a four port hub
> - Two servers both with two NICS. One of the NIC's is connected directly
> to
> the hub. This would constitute the two private IP addresses.
> - The second NIC's would be a wireless adapters to act as the "public" IP
> addresses. This is so I can see them from my office. Not a good idea?
> - The network storage is a network storage device I found...160 GB. This
> plugs directly into the hub as well? Once plugged into the hub, would I
> then
> configure IP's, subnets, etc. What is the admin interface?
> My next dumb question is this: when setting up the hub, do I just plug it
> in
> and go? I've worked on network stuff before , but it's always been set up
> for
> me. I just go in and assign IP addresses, network names, etc.
> Thanks again!!
> "Geoff N. Hiten" wrote:
|||You need a domain controller for the cluster service to even start. Unless
you plan on making one/both of the clustered nodes as DC's.
The hub should just work, plug in and go.
You can use wireless for the public, no worries and the hub for the private.
Your DC will have to be available on the wireless.
I still prefer VS 2005 to cheap hardware
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:46BA24F2-5E44-46CE-95EA-A75825CC7BF3@.microsoft.com...[vbcol=seagreen]
> Maybe you could give me your unofficial blessing on the following?
> - Found a four port hub
> - Two servers both with two NICS. One of the NIC's is connected directly
> to
> the hub. This would constitute the two private IP addresses.
> - The second NIC's would be a wireless adapters to act as the "public" IP
> addresses. This is so I can see them from my office. Not a good idea?
> - The network storage is a network storage device I found...160 GB. This
> plugs directly into the hub as well? Once plugged into the hub, would I
> then
> configure IP's, subnets, etc. What is the admin interface?
> My next dumb question is this: when setting up the hub, do I just plug it
> in
> and go? I've worked on network stuff before , but it's always been set up
> for
> me. I just go in and assign IP addresses, network names, etc.
> Thanks again!!
> "Geoff N. Hiten" wrote:
|||For the wireless router, I would be assigning a static IP (my linksys router
allows for this). I'd be configuring one of the nodes as the domain
controller.
A question about virtual server (pc): how does one simulate the shared
storage? Do I still need actual SCSI storage? Also, Does Virtual Server allow
you to simulate multiple NIC's per virtual machine?
Thanks!
"Rodney R. Fournier [MVP]" wrote:
> You need a domain controller for the cluster service to even start. Unless
> you plan on making one/both of the clustered nodes as DC's.
> The hub should just work, plug in and go.
> You can use wireless for the public, no worries and the hub for the private.
> Your DC will have to be available on the wireless.
> I still prefer VS 2005 to cheap hardware
>
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:46BA24F2-5E44-46CE-95EA-A75825CC7BF3@.microsoft.com...
>
>
|||Yes. VS allows you to attach multiple virtual server instances to a single
virtual SCSI array. You can also have multiple virtual NICs.
GNH
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:22485BE1-0A7D-477E-85B7-0ABD0B32D386@.microsoft.com...[vbcol=seagreen]
> For the wireless router, I would be assigning a static IP (my linksys
> router
> allows for this). I'd be configuring one of the nodes as the domain
> controller.
> A question about virtual server (pc): how does one simulate the shared
> storage? Do I still need actual SCSI storage? Also, Does Virtual Server
> allow
> you to simulate multiple NIC's per virtual machine?
> Thanks!
> "Rodney R. Fournier [MVP]" wrote:
|||As for VS, yes, no (Real SCSI Storage), and yes.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:22485BE1-0A7D-477E-85B7-0ABD0B32D386@.microsoft.com...[vbcol=seagreen]
> For the wireless router, I would be assigning a static IP (my linksys
> router
> allows for this). I'd be configuring one of the nodes as the domain
> controller.
> A question about virtual server (pc): how does one simulate the shared
> storage? Do I still need actual SCSI storage? Also, Does Virtual Server
> allow
> you to simulate multiple NIC's per virtual machine?
> Thanks!
> "Rodney R. Fournier [MVP]" wrote:
|||i have used VMware workstation(100bucks), have 4 different clusters running
on my laptop..
much easier then buying all the stuff. all you need is a vmworkstation 4.3
version(this is one older then what they have now) Workes like a VM lab for
me. simulations are much easier and faster ..
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:22485BE1-0A7D-477E-85B7-0ABD0B32D386@.microsoft.com...
> For the wireless router, I would be assigning a static IP (my linksys
router
> allows for this). I'd be configuring one of the nodes as the domain
> controller.
> A question about virtual server (pc): how does one simulate the shared
> storage? Do I still need actual SCSI storage? Also, Does Virtual Server
allow[vbcol=seagreen]
> you to simulate multiple NIC's per virtual machine?
> Thanks!
> "Rodney R. Fournier [MVP]" wrote:
Unless[vbcol=seagreen]
private.[vbcol=seagreen]
directly[vbcol=seagreen]
IP[vbcol=seagreen]
This[vbcol=seagreen]
I[vbcol=seagreen]
it[vbcol=seagreen]
up[vbcol=seagreen]
switch[vbcol=seagreen]
cluster[vbcol=seagreen]
get a[vbcol=seagreen]
couple[vbcol=seagreen]
internally.[vbcol=seagreen]
looking[vbcol=seagreen]
nodes[vbcol=seagreen]
(NIC's,[vbcol=seagreen]
Also,[vbcol=seagreen]
in a[vbcol=seagreen]
advice.
I would like to hopefully keep the price tag to around $750-$1250.
I've already found a couple of boxes that I'm going to use for my nodes
(dual node cluster). I can do all the internal stuff myself (NIC's, etc.).
They each are about $300.
What I need now is shared storage and a switch/hub. I'm new to the
networking side of things. What DO I need: switch, router, etc.? Also, is
there a way to set up things wirelessly? All my big stuff will be in a mini
server room in my basement and my office is upstairs.
If anyone has any advice, I'd really appreciate it.
Thank You!!!
Wireless is probably more of a pain thatn it is worth. A single switch or
hub will be fine. You can use a crossover cable for a two node cluster
heartbeat LAN. As for storage, you can use almost any SCSI drive. get a
self-powered enclosure ($50 or less) , a couple of cables, and a couple of
cheap controllers. I have done it with Adaptec cards, even with mis-matched
ones. It isn't particularly pretty, scalable or stable, but it does work.
One alternative that many people are using is Virtual Server from Microsoft.
Then you have one physical system that can simulate a cluster internally.
Good Luck,
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:C64DB097-820F-4443-BDCA-486493CCFAC7@.microsoft.com...
> I'm looknig to set up a miniature clustering lab at home and looking for
> some
> advice.
> I would like to hopefully keep the price tag to around $750-$1250.
> I've already found a couple of boxes that I'm going to use for my nodes
> (dual node cluster). I can do all the internal stuff myself (NIC's,
> etc.).
> They each are about $300.
> What I need now is shared storage and a switch/hub. I'm new to the
> networking side of things. What DO I need: switch, router, etc.? Also, is
> there a way to set up things wirelessly? All my big stuff will be in a
> mini
> server room in my basement and my office is upstairs.
> If anyone has any advice, I'd really appreciate it.
> Thank You!!!
|||Maybe you could give me your unofficial blessing on the following?
- Found a four port hub
- Two servers both with two NICS. One of the NIC's is connected directly to
the hub. This would constitute the two private IP addresses.
- The second NIC's would be a wireless adapters to act as the "public" IP
addresses. This is so I can see them from my office. Not a good idea?
- The network storage is a network storage device I found...160 GB. This
plugs directly into the hub as well? Once plugged into the hub, would I then
configure IP's, subnets, etc. What is the admin interface?
My next dumb question is this: when setting up the hub, do I just plug it in
and go? I've worked on network stuff before , but it's always been set up for
me. I just go in and assign IP addresses, network names, etc.
Thanks again!!
"Geoff N. Hiten" wrote:
> Wireless is probably more of a pain thatn it is worth. A single switch or
> hub will be fine. You can use a crossover cable for a two node cluster
> heartbeat LAN. As for storage, you can use almost any SCSI drive. get a
> self-powered enclosure ($50 or less) , a couple of cables, and a couple of
> cheap controllers. I have done it with Adaptec cards, even with mis-matched
> ones. It isn't particularly pretty, scalable or stable, but it does work.
> One alternative that many people are using is Virtual Server from Microsoft.
> Then you have one physical system that can simulate a cluster internally.
> Good Luck,
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:C64DB097-820F-4443-BDCA-486493CCFAC7@.microsoft.com...
>
>
|||Two problems. Wireless NICs almost always use DHCP addresses. Clusters
require hard-coded IP addresses.
NAS is a definite NO-NO for SQL in general and clusters in specific. There
is no way to arbitrate ownership of a NAS or any network storage type device
which completely rules them out for clustering. Direct Attach SCSI, iSCSI,
Virtual SCSI, or Fibre Channel are your options, even in a test and training
environment.
GNH
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:46BA24F2-5E44-46CE-95EA-A75825CC7BF3@.microsoft.com...[vbcol=seagreen]
> Maybe you could give me your unofficial blessing on the following?
> - Found a four port hub
> - Two servers both with two NICS. One of the NIC's is connected directly
> to
> the hub. This would constitute the two private IP addresses.
> - The second NIC's would be a wireless adapters to act as the "public" IP
> addresses. This is so I can see them from my office. Not a good idea?
> - The network storage is a network storage device I found...160 GB. This
> plugs directly into the hub as well? Once plugged into the hub, would I
> then
> configure IP's, subnets, etc. What is the admin interface?
> My next dumb question is this: when setting up the hub, do I just plug it
> in
> and go? I've worked on network stuff before , but it's always been set up
> for
> me. I just go in and assign IP addresses, network names, etc.
> Thanks again!!
> "Geoff N. Hiten" wrote:
|||You need a domain controller for the cluster service to even start. Unless
you plan on making one/both of the clustered nodes as DC's.
The hub should just work, plug in and go.
You can use wireless for the public, no worries and the hub for the private.
Your DC will have to be available on the wireless.
I still prefer VS 2005 to cheap hardware
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:46BA24F2-5E44-46CE-95EA-A75825CC7BF3@.microsoft.com...[vbcol=seagreen]
> Maybe you could give me your unofficial blessing on the following?
> - Found a four port hub
> - Two servers both with two NICS. One of the NIC's is connected directly
> to
> the hub. This would constitute the two private IP addresses.
> - The second NIC's would be a wireless adapters to act as the "public" IP
> addresses. This is so I can see them from my office. Not a good idea?
> - The network storage is a network storage device I found...160 GB. This
> plugs directly into the hub as well? Once plugged into the hub, would I
> then
> configure IP's, subnets, etc. What is the admin interface?
> My next dumb question is this: when setting up the hub, do I just plug it
> in
> and go? I've worked on network stuff before , but it's always been set up
> for
> me. I just go in and assign IP addresses, network names, etc.
> Thanks again!!
> "Geoff N. Hiten" wrote:
|||For the wireless router, I would be assigning a static IP (my linksys router
allows for this). I'd be configuring one of the nodes as the domain
controller.
A question about virtual server (pc): how does one simulate the shared
storage? Do I still need actual SCSI storage? Also, Does Virtual Server allow
you to simulate multiple NIC's per virtual machine?
Thanks!
"Rodney R. Fournier [MVP]" wrote:
> You need a domain controller for the cluster service to even start. Unless
> you plan on making one/both of the clustered nodes as DC's.
> The hub should just work, plug in and go.
> You can use wireless for the public, no worries and the hub for the private.
> Your DC will have to be available on the wireless.
> I still prefer VS 2005 to cheap hardware
>
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:46BA24F2-5E44-46CE-95EA-A75825CC7BF3@.microsoft.com...
>
>
|||Yes. VS allows you to attach multiple virtual server instances to a single
virtual SCSI array. You can also have multiple virtual NICs.
GNH
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:22485BE1-0A7D-477E-85B7-0ABD0B32D386@.microsoft.com...[vbcol=seagreen]
> For the wireless router, I would be assigning a static IP (my linksys
> router
> allows for this). I'd be configuring one of the nodes as the domain
> controller.
> A question about virtual server (pc): how does one simulate the shared
> storage? Do I still need actual SCSI storage? Also, Does Virtual Server
> allow
> you to simulate multiple NIC's per virtual machine?
> Thanks!
> "Rodney R. Fournier [MVP]" wrote:
|||As for VS, yes, no (Real SCSI Storage), and yes.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:22485BE1-0A7D-477E-85B7-0ABD0B32D386@.microsoft.com...[vbcol=seagreen]
> For the wireless router, I would be assigning a static IP (my linksys
> router
> allows for this). I'd be configuring one of the nodes as the domain
> controller.
> A question about virtual server (pc): how does one simulate the shared
> storage? Do I still need actual SCSI storage? Also, Does Virtual Server
> allow
> you to simulate multiple NIC's per virtual machine?
> Thanks!
> "Rodney R. Fournier [MVP]" wrote:
|||i have used VMware workstation(100bucks), have 4 different clusters running
on my laptop..
much easier then buying all the stuff. all you need is a vmworkstation 4.3
version(this is one older then what they have now) Workes like a VM lab for
me. simulations are much easier and faster ..
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:22485BE1-0A7D-477E-85B7-0ABD0B32D386@.microsoft.com...
> For the wireless router, I would be assigning a static IP (my linksys
router
> allows for this). I'd be configuring one of the nodes as the domain
> controller.
> A question about virtual server (pc): how does one simulate the shared
> storage? Do I still need actual SCSI storage? Also, Does Virtual Server
allow[vbcol=seagreen]
> you to simulate multiple NIC's per virtual machine?
> Thanks!
> "Rodney R. Fournier [MVP]" wrote:
Unless[vbcol=seagreen]
private.[vbcol=seagreen]
directly[vbcol=seagreen]
IP[vbcol=seagreen]
This[vbcol=seagreen]
I[vbcol=seagreen]
it[vbcol=seagreen]
up[vbcol=seagreen]
switch[vbcol=seagreen]
cluster[vbcol=seagreen]
get a[vbcol=seagreen]
couple[vbcol=seagreen]
internally.[vbcol=seagreen]
looking[vbcol=seagreen]
nodes[vbcol=seagreen]
(NIC's,[vbcol=seagreen]
Also,[vbcol=seagreen]
in a[vbcol=seagreen]
Friday, March 9, 2012
Migration to MySQL?
From a "price point of view" migrating from the actual platform (2 node
cluster Windows server 2003 with SQL Server 2000 Enterprise Edition)to MySQL
can be very attractive.
In fact our management starts to think that way...
My question is related to the faisability of this operation in a critical
production cluster server.
Is it recommanded?
Pros and Cons?
Why yes and why no.
Please advise.
Franco
It is difficult to answer this type of questions rigorously in a newsgroup
forum. I'd suggest that you invole all the stakeholders and conduct a
comparative risk analysis on the migration. The compartive risk analysis
should cover the key risk factors that are important to your organizaton.
Note that cost factors are but one category among many others.
Some of risk factors to consider include reliability and resilience (e.g.
high availability, stability of the DBMS, proven platform, etc), scalability
and performance (e.g. scalability in terms of database size, # of processors,
memory size, partitioning, index maintenance, etc), manageability (e.g. ease
of backup/restore), operations issus (e.g. need for consistency checks,
availability of expetise, monitoring tools, etc), portfolio impact (e.g. what
is the mix of your installed DBMS and apps using various DBMS, dependencies
among the databases, etc), and supportability (e.g. vendor support, user
community, etc). Obviously, you would have to rate these and other factors
per their relative importance to your apps and organization.
Linchi
"Franco" wrote:
> From a "price point of view" migrating from the actual platform (2 node
> cluster Windows server 2003 with SQL Server 2000 Enterprise Edition)to MySQL
> can be very attractive.
> In fact our management starts to think that way...
> My question is related to the faisability of this operation in a critical
> production cluster server.
> Is it recommanded?
> Pros and Cons?
> Why yes and why no.
> Please advise.
> --
> Franco
cluster Windows server 2003 with SQL Server 2000 Enterprise Edition)to MySQL
can be very attractive.
In fact our management starts to think that way...
My question is related to the faisability of this operation in a critical
production cluster server.
Is it recommanded?
Pros and Cons?
Why yes and why no.
Please advise.
Franco
It is difficult to answer this type of questions rigorously in a newsgroup
forum. I'd suggest that you invole all the stakeholders and conduct a
comparative risk analysis on the migration. The compartive risk analysis
should cover the key risk factors that are important to your organizaton.
Note that cost factors are but one category among many others.
Some of risk factors to consider include reliability and resilience (e.g.
high availability, stability of the DBMS, proven platform, etc), scalability
and performance (e.g. scalability in terms of database size, # of processors,
memory size, partitioning, index maintenance, etc), manageability (e.g. ease
of backup/restore), operations issus (e.g. need for consistency checks,
availability of expetise, monitoring tools, etc), portfolio impact (e.g. what
is the mix of your installed DBMS and apps using various DBMS, dependencies
among the databases, etc), and supportability (e.g. vendor support, user
community, etc). Obviously, you would have to rate these and other factors
per their relative importance to your apps and organization.
Linchi
"Franco" wrote:
> From a "price point of view" migrating from the actual platform (2 node
> cluster Windows server 2003 with SQL Server 2000 Enterprise Edition)to MySQL
> can be very attractive.
> In fact our management starts to think that way...
> My question is related to the faisability of this operation in a critical
> production cluster server.
> Is it recommanded?
> Pros and Cons?
> Why yes and why no.
> Please advise.
> --
> Franco
Subscribe to:
Posts (Atom)