Showing posts with label proper. Show all posts
Showing posts with label proper. Show all posts

Wednesday, March 28, 2012

Multiple instances of MSSQL and IP addresses

Hi.
Not sure if this is the correct newsgroup (could not find another suitable g
roup), and if
not please redirect me to the proper one...
We're running multiple instances of MSSQL 2000 on a server. This is done to
easily
incorporate client DBs into our testing operations. The problem is that all
instances
share the same IP address (but use different ports). So we can access each i
nstance
separately as we want, but we would like it better that each instance is lis
tening on its
own IP address.
But we can't find a way to change the IP address a named instance is listeni
ng on.
Anybody know how to do this?
NOTE: we prefer not to be using Clustering and/or Virtual Server. This is a
single
development server used for testing client scenario's...
Tx.Hi
Does this mean you have a network card for each instance? In which case you
may be able to disable the ports you don't want to use in the network
configurations IP Filtering options. Alternatively you could do it at the
firewall/router.
John
"Arjan de Haan" wrote:

> Hi.
> Not sure if this is the correct newsgroup (could not find another suitable
group), and if
> not please redirect me to the proper one...
> We're running multiple instances of MSSQL 2000 on a server. This is done
to easily
> incorporate client DBs into our testing operations. The problem is that al
l instances
> share the same IP address (but use different ports). So we can access each
instance
> separately as we want, but we would like it better that each instance is l
istening on its
> own IP address.
> But we can't find a way to change the IP address a named instance is liste
ning on.
> Anybody know how to do this?
> NOTE: we prefer not to be using Clustering and/or Virtual Server. This is
a single
> development server used for testing client scenario's...
> Tx.
>
>
>|||"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:BFD4DDB0-FAC1-4E24-9C33-999F9AAF902E@.microsoft.com...
> Hi
> Does this mean you have a network card for each instance? In which case yo
u
> may be able to disable the ports you don't want to use in the network
> configurations IP Filtering options. Alternatively you could do it at the
> firewall/router.
> John
>
No, the server has (currently) just one NIC.
To better clarify things:
The server running the multiple instances of MSSQL can be reached through a
number of IP
addresses. Is it possible, either through changing settings in Windows 2000
(which is
running on the server) or by changing some settings of each MSSQL instance t
o have each
instance receive packets directed to a specific IP address. For example,
3 running instances of MSSQL
SQL_A, 'binds' to IP xxx.xxx.0.1, port 1140,
SQL_B, 'binds' to IP xxx.xxx.0.2, port 1140, and
SQL_C, 'binds' to IP xxx.xxx.0.3, port 1140.
Currently all instances of MSSQL can be reached through the same IP address
but by using
different port numbers, like this:
3 running instances of MSSQL
SQL_A, 'binds' to IP xxx.xxx.0.1, port 1140,
SQL_B, 'binds' to IP xxx.xxx.0.1, port 1143, and
SQL_C, 'binds' to IP xxx.xxx.0.1, port 1146.
We prefer the first setup described. Is this possible without resorting to v
irtual servers
and clustering?
Thanks for your help.
...Arjan...|||Hi
I don't think there is a way to bind to a specific IP address, you are
limited to blocking ports.
How are these IP addresses assigned?
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1115966034.360116.66440@.o13g2000cwo.googlegroups.com...
> Hi
> I don't think there is a way to bind to a specific IP address, you are
> limited to blocking ports.
> How are these IP addresses assigned?
>
John,
the server running the MSSQL instances has been assigned multiple IP address
es (visible
thru Network properties / TCP IP properties / Advanced properties). This was
apparently
done on the server itself.
Just checked but it is indeed reachable under all its assigned IP addresses.
...Arjan...|||Hi
I think you can only do this at the firewall then.
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1115985697.036330.184570@.g14g2000cwa.googlegroups.com...
> Hi
> I think you can only do this at the firewall then.
>
In another newsgroup they suggested I need multiple NICs in the server for t
his, and even
then they were not sure it could be done.
Thanks anyway for answering.
...Arjan...|||Hi
See http://support.microsoft.com/defaul...kb;en-us;816792
which states
Note When you select this check box, you enable filtering for all
adaptors, but you configure the filters individually for each adaptor.
The same filters do not apply to all adaptors.
Which is what I pointed you to in the first post. As it is at adaptor
level having multiple IP addresses for the one adapter will not work.
John

Friday, March 9, 2012

Multiple Cross Apply

Good Afternoon,

I'm attempting to leverage SQL's new 'APPLY" operator and I guess I don't fully understand it proper usage.

This is a relatively simple request, first i want to count the models produced within a valid period of time. The first 'Cross Apply' gets the valid starting and ending dates and looks ups the number of models produced for the period of time. This section of code works perfectly fine.

The problem appears to be with the second "Cross Apply". What I'm attempting to accomplish is to count all models produced, regardless of time frame.

When executed the query appears to go into an loop and I end up canceling out the request.

Any ideas where I went wrong? Any help is greatly appreciated!

select b1.model as Model
,b1.MinDate as Mfg_Str_Date
,b1.MaxDate as Mfg_End_Date
,Count(b2.Model+B2.Serial) as Mfg_Date_Valid
,Count(b3.Model+B3.Serial) as All_Units

from (select b.model, min(b.build_date) as MinDate ,max(b.build_date) as MaxDate
from etbl_models_Serial as b
group by b.model) as b1

--These are Units produced within Valid Window
cross apply (select b2.model,b2.Serial
from etbl_Production as b2
where b2.Model = b1.Model
and b2.Mfg_Date between b1.MinDate and b1.MaxDate) as b2

--These are all units produced
cross apply (select b3.model,b3.Serial
from etbl_Production as b3
where b3.Model = b2.Model) as b3

Group by b1.Model, b1.MinDate, b1.MaxDate
Order by b1.Model

I'm not sure the second CROSS APPLY is right. It looks like

b1 cross apply b2 contains one row for every time this model

is produced within the production window. Then you cross

apply b3, so that for each of these very many rows, you

match a row for every time the model is produced at all.

For a given model, I think you'll be counting this many

rows:

(# produced according to etbl_Production

between extreme dates in etbl_Models_Serial)

*

(# produced according to etbl_Production)

If you produced 15,000 xyz-widgets and they were all

within the valid dates, you'll be counting 225,000,000

rows just for that item.

Maybe I'm not getting it, but here's my best guess

at a query that will answer your question, or come close:

with Mminmax(Model, MinDate, MaxDate) as (

select

M.Model as Model,

min(M.build_date),

max(M.build_date)

from etbl_Models_Serial as M

group by M.Model

)

select

Model,

MinDate as Mfg_Str_Date,

MaxDate as Mfg_End_Date,

count(

case when P.Mfg_Date >= Mminmax.MinDate

and P.Mfg_Date <= Mminmax.MaxDate

then 1 end) as Mfg_Date_Valid,

count(P.Serial) as All_Units

from Mminmax

cross apply (

select P.Serial, P.Mfg_Date

from etbl_Production as P

where P.Model = Mminmax.Model

) as P

group by Mminmax.Model, Mminmax.MinDate, Mminmax.MaxDate

If this isn't helpful, please post the CREATE TABLE statements

and a modest amount of data as INSERT statements, and give the

desired results. One thing I can't tell is whether you have or

want to count cases where Serial or Mfg_Date is NULL.

Steve Kass

Drew University

http://www.stevekass.com

sfmd@.discussions.microsoft.com wrote:

> This post has been edited either by the author or a moderator in the

> Microsoft Forums: http://forums.microsoft.com

>

>

> Good Afternoon,

>

> I'm attempting to leverage SQL's new 'APPLY" operator and I guess I

> don't fully understand it proper usage.

>

> This is a relatively simple request, first i want to count the models

> produced within a valid period of time. The first 'Cross Apply' gets the

> valid starting and ending dates and looks ups the number of models

> produced for the period of time. This section of code works perfectly

> fine.

>

> The problem appears to be with the second "Cross Apply". What I'm

> attempting to accomplish is to count all models produced, regardless of

> time frame.

>

> When executed the query appears to go into an loop and I end up

> canceling out the request.

>

> Any ideas where I went wrong? Any help is greatly appreciated!

>

>

>

> select b1.model as Model

> ,b1.MinDate as Mfg_Str_Date

> ,b1.MaxDate as Mfg_End_Date

> ,Count(b2.Model+B2.Serial) as Mfg_Date_Valid

> ,Count(b3.Model+B3.Serial) as All_Units

>

> from (select b.model, min(b.build_date) as MinDate

> ,max(b.build_date) as MaxDate

> from etbl_models_Serial as b

> group by b.model) as b1

>

> --These are Units produced within Valid Window

> cross apply (select b2.model,b2.Serial

> from etbl_Production as b2

> where b2.Model = b1.Model

> and b2.Mfg_Date between b1.MinDate and b1.MaxDate) as b2

>

> --These are all units produced

> cross apply (select b3.model,b3.Serial

> from etbl_Production as b3

> where b3.Model = b2.Model) as b3

>

> Group by b1.Model, b1.MinDate, b1.MaxDate

> Order by b1.Model

>

>|||

Steve,

Your answer provided the exact solution I was hoping to obtain. Mucho many thanks for your quick response!!! The only problem was the readabilty of the response. So for those of you who have trouble decifiering Steve's response. I'll post the working code below:

With Mminmax(Model, MinDate, MaxDate) as

(Select M.Model as Model,min(M.build_date), max(M.build_date)
From etbl_Models_Serial as M
Group by M.Model)

Select Model
,MinDate as Mfg_Str_Date
,MaxDate as Mfg_End_Date
,count(case when P.Mfg_Date >= Mminmax.MinDate and P.Mfg_Date <= Mminmax.MaxDate then 1 end)
as Mfg_Date_Valid
,count(P.Serial) as All_Units
From Mminmax
Cross Apply (Select P.Serial,P.Mfg_Date
From etbl_Production as P
Where P.Model = Mminmax.Model ) as P
group by Mminmax.Model, Mminmax.MinDate, Mminmax.MaxDate
Order by Mninmax.Model

|||Thanks for fixing this up. Glad it worked!

SK

sfmd@.discussions.microsoft.com wrote:

> Steve,

>

> Your answer provided the exact solution I was hoping to obtain. Mucho

> many thanks for your quick response!!! The only problem was the

> readabilty of the response. So for those of you who have trouble

> decifiering Steve's response. I'll post the working code below:

>

> With Mminmax(Model, MinDate, MaxDate) as

>

> (Select M.Model as Model,min(M.build_date), max(M.build_date)

> From etbl_Models_Serial as M

> Group by M.Model)

>

> Select Model

> ,MinDate as Mfg_Str_Date

> ,MaxDate as Mfg_End_Date

> ,count(case when P.Mfg_Date >= Mminmax.MinDate and P.Mfg_Date

> <= Mminmax.MaxDate then 1 end)

> as Mfg_Date_Valid

> ,count(P.Serial) as All_Units

> From Mminmax

> Cross Apply (Select P.Serial,P.Mfg_Date

> From etbl_Production as P

> Where P.Model = Mminmax.Model ) as P

> group by Mminmax.Model, Mminmax.MinDate, Mminmax.MaxDate

> Order by Mninmax.Model

>

>

>

>

>

>

>

>