..newbie.. cluster multi-istance..

Hi,
I must install M$ SQL2005 standard on two node in cluster enviroment.
I wanna know some best practices to resolve the installation in the
better way.
The server is based on two blade dual processor dual core. I've a SAN
configured with 4 LUN, each with 1 partition (eventually a can
reconfigure the SAN).
I've some question:
a) SQL2005 is defined by M$ only active/passive on a cluster. This
means that can I work with two nodes, with two cluster groups each one
working with one instance? In other words, in which way can I
configure cluster's groups (or group)? Can I use only a group with two
instance? Can I use two groups? I can obtain a failover enviroment
relative to one instance with two active nodes? (SQL2005 is active/
active on nodes, but each instance is active/passive on nodes?) I
prefer obtain the failover option vs. load balance across two nodes,
but I must know the possibilities for future decision.
b) An istance SQL2005 is based on an installation path for the engine
of the instance and several paths for data files. On a cluster based
on SAN where I can/must install the instance? I've a local disk
partitioned with two logical unit (c: and d. Also I've have one or
more units on SAN. C: contains Windows2003 and D: is empty. I must
install the instance on the local disk of the node or I use the unit
(one or more) on SAN?. I know that on a normal enviroment it's better
use some different fisical disks/units (The best it's split/organize
datafile in order of cpu capabilities - e.g.: dual processor/dual core
tempdb on 4 files..) but with a SAN? It's also a best practice define
several LUN on a SAN in order of obtain an optimized architecture?. Or
can I use only a large disk with several files? I need install 4
instances and I want obtain the best architectural solution.
c) With the first installation I will make one instance on two nodes.
In which way I will install the other 3 instances (on a cluster
enviroment)?
If you can, tell me more infos about these tasks.
oecre.at.hotmail.dot.com
<marco.crespi@.gmail.com> wrote in message
news:1170322227.120566.94730@.m58g2000cwm.googlegro ups.com...
> Hi,
> I must install M$ SQL2005 standard on two node in cluster enviroment.
> I wanna know some best practices to resolve the installation in the
> better way.
> The server is based on two blade dual processor dual core. I've a SAN
> configured with 4 LUN, each with 1 partition (eventually a can
> reconfigure the SAN).
> I've some question:
> a) SQL2005 is defined by M$ only active/passive on a cluster. This
> means that can I work with two nodes, with two cluster groups each one
> working with one instance?
Yes, one and only one instance per group, you can have multiple groups

>In other words, in which way can I
> configure cluster's groups (or group)? Can I use only a group with two
> instance?
One instance per group

> Can I use two groups?
yes, you will have two instances

> I can obtain a failover enviroment
> relative to one instance with two active nodes?
An instance (group) is only online / "running" on one node. (Always on one
node only)
A Group (instance) is NEVER "running" on two nodes.

> (SQL2005 is active/
> active on nodes, but each instance is active/passive on nodes?)
One instance/group is only online on one node. when you have two groups,
then you can have one group online on one node and the second group online
on the second node. If a node fails, the surviving node is now running all
groups.

> I
> prefer obtain the failover option vs. load balance across two nodes,
> but I must know the possibilities for future decision.
Clustering is fail-over, not load balancing.

> b) An istance SQL2005 is based on an installation path for the engine
> of the instance and several paths for data files. On a cluster based
> on SAN where I can/must install the instance?
follow the instructions, the executables are in the default places, and data
goes on your clustered disks (on a SAN)

> I've a local disk
> partitioned with two logical unit (c: and d. Also I've have one or
> more units on SAN. C: contains Windows2003 and D: is empty. I must
> install the instance on the local disk of the node or I use the unit
> (one or more) on SAN?.
follow instructions, books on line is a good place to start
or here : http://msdn2.microsoft.com/en-us/library/ms179530.aspx

> I know that on a normal enviroment it's better
> use some different fisical disks/units (The best it's split/organize
> datafile in order of cpu capabilities - e.g.: dual processor/dual core
> tempdb on 4 files..) but with a SAN?
yes, depending on your application / IO patterns, but general rule is data
files and log files on different spindles

> It's also a best practice define
> several LUN on a SAN in order of obtain an optimized architecture?.
Absolutely, every SAN need to be configured properly, every LUN will have to
be created with certain parameters. However this is so dependent on the SAN
you have, you should contact your SAN vendor to get the best practices
(mention it is for SQL 2005).

> Or
> can I use only a large disk with several files? I need install 4
> instances and I want obtain the best architectural solution.
As above, more spindles = more IO = better performance (this is the general
rule)

> c) With the first installation I will make one instance on two nodes.
> In which way I will install the other 3 instances (on a cluster
> enviroment)?
as per documentation

> If you can, tell me more infos about these tasks.
If you have little experience with clusters, I would suggest to read up
about this as much as possible.
Install a cluster, A cluster with Virtual Machine Nodes will do, and
practice the setup. Make sure you are familiar with the tools, interfaces,
configurations, failover/back.
Practice and test first how all this works.
HTH,
_Edwin.

> oecre.at.hotmail.dot.com
>
|||Edwin's responses are all accurate, but for general knowledge, the SQL
Server 200 Failover Clustering guide is a good introduction.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
I realize you are speaking to SS2K5, but the document is "mostly" applicable
to both. Moreover, the document also contains a link to the updated white
paper dedicated for SS2K5. You would be wise to read them both.
In your situation, with only a 2-node cluster, running Standard Edition,
your choices are kind of limited. Basically, when you install each
instance, you will be required to provide a pre-existing cluster resource
group with an existing dedicated shared disk resource. During the
installation process, you will specify not only a unique instance name, but
also a unique virtual server name (Network Name resource) and associated IP
address.
This is where clustering is different that multi-instancing on a stand-alone
system, because each SQL Server instance must be segregated to an
independent dedicated resource group, it will also require a dedicated disk,
network name, and IP address.
The binaries will be installed to a local drive on each cluster node (either
C: or D: in your case). You only have to install once; the setup program
will distribute copies of the binaries to each cluster node you specify as a
potential owner of the clustered resources. The binaries may be collocated
on the same drive, but different installation paths will be chosen for each
dedicated instance.
The data files will be installed on one shared volume. After installation,
you can redistribute database files to other clustered volumes within the
same cluster resource group.
Due to the limitation of drive letters in a Windows system, you will be
limited on how many volumes you can maintain, and thus, how many
simultaneous instances you can support in a single clustered system.
With SS2K5 (as long as SS2K5 is the only version installed, no side-by-side
with any SS2K instances), you can have a single letter-assigned drive per
cluster group, but then use multiple mount-points on this drive to
distribute the data files appropriately to dedicated volumes.
Now, how many "volumes" should you have per cluster group/SQL Server
instance? At a minimum, you should have 3: one for the data files (user and
system), one for the transaction logs (user and system), and one for the
on-disk database backup files. In addition, we usually create another
volume to separate the tempdb data files (the tempdb log files are kept on
the shared log file volume). Not only should these be isolated on the host,
but they need to be partitioned in the SAN as well (usually called "data
fencing"). Basically, on the storage array(s), this is a way of grouping
RAID Group "sets" into similar, dedicated IO workload characteristics, based
on function. You wouldn't want to run your database files from the same SAN
RAID group that hosted another systems page files, especially if that other
system started memory-thrashing.
This is a baseline installation. Upon further testing, you may want to
plaid the database across multiple dedicated volumes, either through
multi-file filegroups, dedicated filegroups, a combination thereof, or
merely to isolate a single database from other database files. You can have
multiple databases, files, and/or filegroups sharing a single volume, or you
can distribute them to dedicated volume devices. The choice is dependent on
the workload characteristics, disk IOPS, and bandwidth requirements.
I specifically used the term "volumes;" this was on purpose because volume
and LUN construction is another complex discussion, and also like Edwin, I
would recommend having that discussion with your SAN administrator/engineer
or SAN vendor. The same workload and IO requirements will also dictate how
many dedicated IO buses, HBAs, switch connectors, and SAN channel adapters
your installation will require.
To give you an idea of what is going on, I'll list out a general
description, but you need to do some homework before you design and
construct. I cannot emphasize enough how important it will be for you to
get your SAN team involved in this design.
Nevertheless, there is disk plaiding and database plaiding, one residing and
dependent on the other. I mentioned a few of the components for database
plaiding above. Below, I will give list some of the volume construction
considerations.
For disk plaiding, LUNS are created in the SAN storage array(s) (yes,
arrays; host presented LUNs can be supported from more than one array
simultaneously; this is the "N" in SAN, which stands for network, both on
the host side as well as the storage side). Sometimes these are created
from hypervolumes (slices from a single disk) or RAID groups (slices of
standard RAID 0, 1, 3, 5, 6, 1+0, etc., etc., etc.). These LUNs can be
presented directly to the host or from these, meta-volumes or meta-LUNs
(second layer striping on the array) can be created.
Either array LUNs or these meta-LUN units are then presented to the host.
These show up as the DISK devices visible from the host side volume manager.
On Windows systems, using the native LDM volume manager, each of these
DISKs/array LUNs will be converted to basic disks with primary
partitions-dynamic disks are not supported for clustered installations
utilizing the physical disk resource type. You will need to consider
partition offsets, drive letters and/or mount points, and File System
Allocation Unit size. However, with third-party volume managers, you can
consider another layer of disk plaiding.
With these volume manager utilities, you can convert these array LUN backed
disk devices into dynamic disks and now consider various host-level striping
scenarios as well. We typically use RAID 0, but RAID 1 or JBOD could also
be considered. Multiple dynamic disk devices can be assembled to construct
a single volume, from which a drive or mounted device can be assigned to a
SQL Server clustered resource group.
The reason you would want to consider host-level striping in addition to the
storage array construction is in order to keep multiple HBAs using
multipathing software as evenly active as possible. Allowing only the
storage array to manage all RAID characteristics, you can limit the
bandwidth capabilities between the host to SAN interconnects. By striping
on the host, you can maximize this bandwidth.
One last thing before I let you go, you are considering running 4 instances
on a two-node cluster. I would recommend you spend some time on the VLDB
web site.
http://www.microsoft.com/sql/prodinfo/previousversions/scalability.mspx
Microsoft has provided studies that have shown that hosting multiple
databases on a single instance is more efficient than running multiple
instances in 64-bit mode. For 32-bit installations, because of limitations
of the Virtual Memory Address Space, multiple instances may provide
additional performance enhancements. So, if performance is your goal, stick
with 2 instances, one running on each node, and perhaps running the entire
installation in a single-instanced configuration on 64-bit equipment.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx#EDD
If money is a factor, and your are trying to maximize hardware resource
utilization, take a look into N + I clustered configurations, these can help
reduce the number of passive clustered nodes per active node, thus reducing
your cost without sacrificing performance.
Take a look at the Number of Nodes and Failover Plan section:
http://technet2.microsoft.com/WindowsServer/en/library/f41adff5-87df-483e-9edd-8484501d79921033.mspx?mfr=true
Best of luck.
Sincerely,
Anthony Thomas

<marco.crespi@.gmail.com> wrote in message
news:1170322227.120566.94730@.m58g2000cwm.googlegro ups.com...
> Hi,
> I must install M$ SQL2005 standard on two node in cluster enviroment.
> I wanna know some best practices to resolve the installation in the
> better way.
> The server is based on two blade dual processor dual core. I've a SAN
> configured with 4 LUN, each with 1 partition (eventually a can
> reconfigure the SAN).
> I've some question:
> a) SQL2005 is defined by M$ only active/passive on a cluster. This
> means that can I work with two nodes, with two cluster groups each one
> working with one instance? In other words, in which way can I
> configure cluster's groups (or group)? Can I use only a group with two
> instance? Can I use two groups? I can obtain a failover enviroment
> relative to one instance with two active nodes? (SQL2005 is active/
> active on nodes, but each instance is active/passive on nodes?) I
> prefer obtain the failover option vs. load balance across two nodes,
> but I must know the possibilities for future decision.
> b) An istance SQL2005 is based on an installation path for the engine
> of the instance and several paths for data files. On a cluster based
> on SAN where I can/must install the instance? I've a local disk
> partitioned with two logical unit (c: and d. Also I've have one or
> more units on SAN. C: contains Windows2003 and D: is empty. I must
> install the instance on the local disk of the node or I use the unit
> (one or more) on SAN?. I know that on a normal enviroment it's better
> use some different fisical disks/units (The best it's split/organize
> datafile in order of cpu capabilities - e.g.: dual processor/dual core
> tempdb on 4 files..) but with a SAN? It's also a best practice define
> several LUN on a SAN in order of obtain an optimized architecture?. Or
> can I use only a large disk with several files? I need install 4
> instances and I want obtain the best architectural solution.
> c) With the first installation I will make one instance on two nodes.
> In which way I will install the other 3 instances (on a cluster
> enviroment)?
> If you can, tell me more infos about these tasks.
> oecre.at.hotmail.dot.com
>
|||Perfect!!
I've read your notes and solve the task!!
Thanks!!
But..
But..
I've encountered a terribly problem..
When SQL Installation must remotely uses the second node to exec
parallel installation the setup hang...
No way to solve the problem...
I've found that if I give explicitly full rights on CMD.EXE to the
installation user I can make two installation (RPC are allowed:
Dameware does function).....
ONLY TWO INSTANCES!! The others no.
Pay attention: the installation user is administrator of the machine
and of the cluster !!?!?!?!?
To solve the task (4 instances requested..) I use one node for two
instances and the other node for others (two)
Only a question: if I will need another instance tomorrow?...

*** Urgent help please ...

Can someone please help me resolve the following -
I have a table consisting of sales orders information with salesmen who lead
the order.
Now I have a need to find average by adding individual salesman's records
and then dividing it by total no. of salesmen in the table.
Ex. Saleman No., Order No.
1, 1
1, 2
2,3
1,4
3,5
3,6
In this case the output should be something like
Salesman #orders
1 3
2 1
3 2
Average = #total no. of orders / #total no. of salesmen
my query is -
select firstname, lastname, count(*) from project
where
ISNULL(CompleteFLAG,'N') ='N' )
group by firstname, lastname
order by firstname
I would like to get the average by adding count(*) for each person/#total
no. of persons.
Thank you,
-Me
Try something like this.
select distinct(Saleman No),count(Order No) as OrderNo from vendor
group by Saleman No
Maninder
MCDBA
|||On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> Can someone please help me resolve the following -
> I have a table consisting of sales orders information with salesmen who lead
> the order.
> Now I have a need to find average by adding individual salesman's records
> and then dividing it by total no. of salesmen in the table.
> Ex. Saleman No., Order No.
> 1, 1
> 1, 2
> 2,3
> 1,4
> 3,5
> 3,6
> In this case the output should be something like
> Salesman #orders
> 1 3
> 2 1
> 3 2
> Average = #total no. of orders / #total no. of salesmen
> my query is -
> select firstname, lastname, count(*) from project
> where
> ISNULL(CompleteFLAG,'N') ='N' )
> group by firstname, lastname
> order by firstname
> I would like to get the average by adding count(*) for each person/#total
> no. of persons.
> Thank you,
> -Me
It seems like your query contradicts the sample data you used. What is
the key of the Project table? Do you really store the sales person's
name next to each order? Does every order have a sales person
associated with it? Please always include DDL so that we don't have to
guess these things.
Perhaps there are sales people who haven't yet got any orders to their
names? In that case you'll need a SalesPerson table (which I would
hope you have in any case). Here's one possibility:
SELECT COUNT(*)
/(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
FROM Project;
If you just want to include those who have sales, try:
SELECT COUNT(*)
/(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
FROM Project) AS AvgSales
FROM Project;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi David,
Thanks for the reply!
Your solution helped me resolve my issue.
Appreciate your help!
-Me
"David Portas" wrote:

> On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> It seems like your query contradicts the sample data you used. What is
> the key of the Project table? Do you really store the sales person's
> name next to each order? Does every order have a sales person
> associated with it? Please always include DDL so that we don't have to
> guess these things.
> Perhaps there are sales people who haven't yet got any orders to their
> names? In that case you'll need a SalesPerson table (which I would
> hope you have in any case). Here's one possibility:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
> FROM Project;
> If you just want to include those who have sales, try:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
> FROM Project) AS AvgSales
> FROM Project;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
>
|||Hi Maninder,
Thanks for the reply!
Actually I was looking for what David has in his thread.
But appreciate your keeping my tempo going.
Thank you again for your help!
-Me
"Maninder" wrote:

> Try something like this.
> select distinct(Saleman No),count(Order No) as OrderNo from vendor
> group by Saleman No
> Maninder
> MCDBA
>

*** Urgent help please ...

Can someone please help me resolve the following -
I have a table consisting of sales orders information with salesmen who lead
the order.
Now I have a need to find average by adding individual salesman's records
and then dividing it by total no. of salesmen in the table.
Ex. Saleman No., Order No.
1, 1
1, 2
2,3
1,4
3,5
3,6
In this case the output should be something like
Salesman #orders
1 3
2 1
3 2
Average = #total no. of orders / #total no. of salesmen
my query is -
select firstname, lastname, count(*) from project
where
ISNULL(CompleteFLAG,'N') ='N' )
group by firstname, lastname
order by firstname
I would like to get the average by adding count(*) for each person/#total
no. of persons.
Thank you,
-MeTry something like this.
select distinct(Saleman No),count(Order No) as OrderNo from vendor
group by Saleman No
Maninder
MCDBA|||On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> Can someone please help me resolve the following -
> I have a table consisting of sales orders information with salesmen who le
ad
> the order.
> Now I have a need to find average by adding individual salesman's records
> and then dividing it by total no. of salesmen in the table.
> Ex. Saleman No., Order No.
> 1, 1
> 1, 2
> 2,3
> 1,4
> 3,5
> 3,6
> In this case the output should be something like
> Salesman #orders
> 1 3
> 2 1
> 3 2
> Average = #total no. of orders / #total no. of salesmen
> my query is -
> select firstname, lastname, count(*) from project
> where
> ISNULL(CompleteFLAG,'N') ='N' )
> group by firstname, lastname
> order by firstname
> I would like to get the average by adding count(*) for each person/#total
> no. of persons.
> Thank you,
> -Me
It seems like your query contradicts the sample data you used. What is
the key of the Project table? Do you really store the sales person's
name next to each order? Does every order have a sales person
associated with it? Please always include DDL so that we don't have to
guess these things.
Perhaps there are sales people who haven't yet got any orders to their
names? In that case you'll need a SalesPerson table (which I would
hope you have in any case). Here's one possibility:
SELECT COUNT(*)
/(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
FROM Project;
If you just want to include those who have sales, try:
SELECT COUNT(*)
/(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
FROM Project) AS AvgSales
FROM Project;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
Thanks for the reply!
Your solution helped me resolve my issue.
Appreciate your help!
-Me
"David Portas" wrote:

> On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> It seems like your query contradicts the sample data you used. What is
> the key of the Project table? Do you really store the sales person's
> name next to each order? Does every order have a sales person
> associated with it? Please always include DDL so that we don't have to
> guess these things.
> Perhaps there are sales people who haven't yet got any orders to their
> names? In that case you'll need a SalesPerson table (which I would
> hope you have in any case). Here's one possibility:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
> FROM Project;
> If you just want to include those who have sales, try:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
> FROM Project) AS AvgSales
> FROM Project;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
>|||Hi Maninder,
Thanks for the reply!
Actually I was looking for what David has in his thread.
But appreciate your keeping my tempo going.
Thank you again for your help!
-Me
"Maninder" wrote:

> Try something like this.
> select distinct(Saleman No),count(Order No) as OrderNo from vendor
> group by Saleman No
> Maninder
> MCDBA
>

*** Urgent help please ...

Can someone please help me resolve the following -
I have a table consisting of sales orders information with salesmen who lead
the order.
Now I have a need to find average by adding individual salesman's records
and then dividing it by total no. of salesmen in the table.
Ex. Saleman No., Order No.
1, 1
1, 2
2,3
1,4
3,5
3,6
In this case the output should be something like
Salesman #orders
1 3
2 1
3 2
Average = #total no. of orders / #total no. of salesmen
my query is -
select firstname, lastname, count(*) from project
where
ISNULL(CompleteFLAG,'N') ='N' )
group by firstname, lastname
order by firstname
I would like to get the average by adding count(*) for each person/#total
no. of persons.
Thank you,
-MeTry something like this.
select distinct(Saleman No),count(Order No) as OrderNo from vendor
group by Saleman No
Maninder
MCDBA|||On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> Can someone please help me resolve the following -
> I have a table consisting of sales orders information with salesmen who lead
> the order.
> Now I have a need to find average by adding individual salesman's records
> and then dividing it by total no. of salesmen in the table.
> Ex. Saleman No., Order No.
> 1, 1
> 1, 2
> 2,3
> 1,4
> 3,5
> 3,6
> In this case the output should be something like
> Salesman #orders
> 1 3
> 2 1
> 3 2
> Average = #total no. of orders / #total no. of salesmen
> my query is -
> select firstname, lastname, count(*) from project
> where
> ISNULL(CompleteFLAG,'N') ='N' )
> group by firstname, lastname
> order by firstname
> I would like to get the average by adding count(*) for each person/#total
> no. of persons.
> Thank you,
> -Me
It seems like your query contradicts the sample data you used. What is
the key of the Project table? Do you really store the sales person's
name next to each order? Does every order have a sales person
associated with it? Please always include DDL so that we don't have to
guess these things.
Perhaps there are sales people who haven't yet got any orders to their
names? In that case you'll need a SalesPerson table (which I would
hope you have in any case). Here's one possibility:
SELECT COUNT(*)
/(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
FROM Project;
If you just want to include those who have sales, try:
SELECT COUNT(*)
/(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
FROM Project) AS AvgSales
FROM Project;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
Thanks for the reply!
Your solution helped me resolve my issue.
Appreciate your help!
-Me
"David Portas" wrote:
> On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> > Can someone please help me resolve the following -
> >
> > I have a table consisting of sales orders information with salesmen who lead
> > the order.
> >
> > Now I have a need to find average by adding individual salesman's records
> > and then dividing it by total no. of salesmen in the table.
> >
> > Ex. Saleman No., Order No.
> > 1, 1
> > 1, 2
> > 2,3
> > 1,4
> > 3,5
> > 3,6
> >
> > In this case the output should be something like
> >
> > Salesman #orders
> > 1 3
> > 2 1
> > 3 2
> >
> > Average = #total no. of orders / #total no. of salesmen
> >
> > my query is -
> >
> > select firstname, lastname, count(*) from project
> > where
> > ISNULL(CompleteFLAG,'N') ='N' )
> > group by firstname, lastname
> > order by firstname
> >
> > I would like to get the average by adding count(*) for each person/#total
> > no. of persons.
> >
> > Thank you,
> > -Me
> It seems like your query contradicts the sample data you used. What is
> the key of the Project table? Do you really store the sales person's
> name next to each order? Does every order have a sales person
> associated with it? Please always include DDL so that we don't have to
> guess these things.
> Perhaps there are sales people who haven't yet got any orders to their
> names? In that case you'll need a SalesPerson table (which I would
> hope you have in any case). Here's one possibility:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
> FROM Project;
> If you just want to include those who have sales, try:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
> FROM Project) AS AvgSales
> FROM Project;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
>|||Hi Maninder,
Thanks for the reply!
Actually I was looking for what David has in his thread.
But appreciate your keeping my tempo going.
Thank you again for your help!
-Me
"Maninder" wrote:
> Try something like this.
> select distinct(Saleman No),count(Order No) as OrderNo from vendor
> group by Saleman No
> Maninder
> MCDBA
>

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

ASP v2.0

I am getting this error when attempting to sign in to my site once uploaded to the server. Can anyone help me to resolve this error?

Server Error in '/' Application.

---

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
 System.Data.SqlClient.SqlConnection.Open() +111
 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121
 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105
 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42
 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83
 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160
 System.Web.UI.WebControls.Login.AttemptLogin() +105
 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

First, this stack trace is really useless for diagnosing your problem. Here's some questions to get you started.

What's your connection string? Are you using integrated security?

Does the SQL Server sit on the same machine as the web server? Is it part of the same domain?

Is the sql server behind a firewall? Can the web server "see" the SQL Server?

Does this work on your machine with the same username/password?

Can you log into the Web Server via remote desktop? If so can you ping the SQL Server? Can you connect using Query Analyzer?

|||I'm using the integrated security within ASPv2.0.

The issue comes up with the login on the server.

I can sign in on the development PC and everything runs fine, but I receive this error when I attempt to log on after I upload the site to the server.

I am not sure where the connection string is for v2.0 integrated security. I have just recently moved from v1.1 and am still discovering new things as I go.

I use Godaddy.com web hosting. So the server and the development PCs are separate. I have been using the Publish Web Site tool in VS2005 and then CuteFTP Pro to transfer the files. I have configured my godaddy account to accept v2.0 framework and activated the SQL server schema.

I have found that a test non-member page is having difficulty connecting to the access database I am using. It gives me an error stating "c:\access_db\Survey.mdb is not a valid location..." <-- something of that nature. Using the following connection string for this:
strConn = New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\access_db\Survey.mdb"))

I am not sure if that is caused by the same issue or if it is because of my connection string. I assume it is a connection string issue. It works fine on my development pc even though my hard drive is e: not c:, so I don't know what's up with that really. still working on it.|||

OK. Let's back up. Are you using Access, or MS SQL Server? If you're using Access, why are you posting to a SQL Server forum?

|||I apologize I am not trying to be confusing.

My issue that I posted here for is for the SQLSever database: ASPNETDB.MDF

This is where I am getting the issue from. The Access DB is an issue I think I can handle on my own at this time. I shouldn't have mentioned it.|||

So you're trying to mount this file, which is sitting on the same computer as the web server, right? And you're using integrated security, right? I believe that integrated security requires that the user have a Windows login to the box/domain that the app's running on. I'm not sure how you're doing that with your web app.

If you're developing using VS 2005, there's a lot that your app can do (because it's running under your credentials) that it can't do on a production server.

If you have a MSDN subscription, you might want to build a test Windows 2003 server using VirtualPC, and try deploying your app there. That'll give you a better feel for what's going on with the GDaddy box.(I did that last week to diagnose problems with a web service on a server running multiple web servers. It turns out using localhost doesn't always work.)

|||The web server is a completely different PC than my development. I am uploading the files to the server using CuteFTP Pro.GoDaddy.com owns the server.

I have setup my application to use internet secruity (see jpg). It should not require a Windows login. I do not have an MSDN subscription, so I cannot use a test server using VirtualPC.

|||Does the application require the user to log in? If so, how are you passing the user id/pw to the database?|||I am using the Login control in ASP.NET v2.0

I am not sure how it passes the username/password to the database. It all happens behind the scenes.|||Well, we've pretty much hit the limits of how I can help you. If you figure it out, try to post the answer in case someone else has a similar problem.|||I appreciate your attempt. Thank you for using your time to help me.

If anyone else has discovered a solution please reply. I will do the same.

Thank you|||

I have the EXACT same problem...Godaddy hosting trying to use SQLEXPRESS database file that is included with visual web designer.

Problem is... it won't work. According to Microsoft. (If Godaddy hostsmultiple sites that do not trust each other) The note below was taken from the msdn site.I underlined what I believe is the cause of your problem.

The resolution is to use plain old connection strings to plain old SQL server database instances.

good luck

Note

If you are deploying your SQL Server Express Edition database to a Web server that hosts multiple sites that do not trust each other, then you cannot use file-based connections or user instances to help ensure that your data is not exposed to other applications on the server. In this case, it is recommended that you migrate the contents your SQL Server Express Edition database to another version of SQL Server 2005 that your deployed ASP.NET application can access.

http://msdn2.microsoft.com/en-us/library/ms247257.aspx|||One solution I am curious about is this:

With Godaddy, you must use your host manager in order to set up the site to use Framework v2.0 and to use SQL Server. When it sets this up it creates a few files--
(2) Folder -- "_private" and "_vti_log"
(1) HTML File -- "_vti_inf.html"
(1) Asp file -- "gdform.asp"

-- I am not sure why these files are created or what they are used for. They may have something to do with this issue.

-- When you set up the server to enable using an MS Access Db, it creates a folder called "access_db." I think what it is doing is creating virtual folders to run off of. If you create your own folder on the server usinf FTP and place an access db file inside, you are not able to access the db. But if you place the db inside the "access_db" folder that GoDaddy creates, everything works fine.

-- The solution may be in figuring out the setup here. I am going to give GoDaddy a call and see if they are familiar with this issue and have a solution.

-- If there is a way to change the connection string that v2.0 uses to connect to that user db, then it may be ab easier solution. So far I have not found a way to change the connection string for the user db within v2.0.