Multiple instances of SQL

Hi
I am considering consolidating our environment to a Single SQL enterprise
server. I'm curious about how the instances are handled.. if one instance
gets updated how does this impact the other instances that are running. What
effect does this have as far as the master db goes. Is there a single point
of failure in sql that may crash the whole environment? Any help would be
greatly appreciated.
thanks
D
Hello I have a number of servers that have multiple instances on them. When
patching everything is quite straight forward and works as you expect namely
only the instance that is being upgraded is affected. This includes the DLLs
and EXEs for each instance, master does count because its very obviously
different for each instance and as you run the patch one instance at a time
only one gets the update.
If this is prod server I would think about getting a box with enough CPU and
RAM to divvy up the resources to each instance. You’ll need to look into CPU
affinity.
We run this configuration when wanting to separate 3rd party applications
that are mostly poorly designed and our in house applications. We also use
this for spinning up multiple dev and test environments.
Hope that helps
John Vandervliet
"DS" wrote:

> Hi
> I am considering consolidating our environment to a Single SQL enterprise
> server. I'm curious about how the instances are handled.. if one instance
> gets updated how does this impact the other instances that are running. What
> effect does this have as far as the master db goes. Is there a single point
> of failure in sql that may crash the whole environment? Any help would be
> greatly appreciated.
> thanks
> D

Multiple Database hits vs Bulk Data parameters

I was curious to know if it the amount of data sent to the sql server mattered.I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.Thanks

For SQL Server, the cost of parsing parameters can always be ingonred compared with the cost of compiling a store procedure and producing a optimized execution plan for it.

From network I/O aspect, each execution of a stored procedure is considered as a 'batch' (batch in SQL means all data will be processed and returned at one time, which can greatly improve network performance). So if you use 3 stored procedures, you need 3 rounds; while 1 stored procedure need only 1 round.

So generally I suggest you use 1 stored procedure with 12 parameters.

Multiple company

Hi. We have a SQL database for one company with multi user access. I am
curious as to best practice solutions for expanding a db to multiple company
access.
Some parts of the db need to be shared, others are private to the company
logged in.
If every time a query was executed it required company filtering, it seems a
waste having company parameters in every minor query, and would require
entire code rewrite. Not being a programmer I don't know what options sql
server may offer, but I know enough about sql to have these sort of questions
distract your thinking until answered...
I hope this is the correct forum for general questions such as this.
Thanks in advance.
swasswas
Create a LOGIN for those 'outside' companies. Grant the database access to
this LOGIN .
You can create VIEWS to show the data that only this LOGIN needs and GRANT
it SELECT ON the VIEW (For more details please refer to the BOL)
As well , you can GRANT an EXECUTE permissions to the LOGIN for STORED
PROCEDURES and not granting permissions on underlaying tables
"swas" <swas@.discussions.microsoft.com> wrote in message
news:C49B53B0-9483-4E87-9166-109ED0495E24@.microsoft.com...
> Hi. We have a SQL database for one company with multi user access. I am
> curious as to best practice solutions for expanding a db to multiple
> company
> access.
> Some parts of the db need to be shared, others are private to the company
> logged in.
> If every time a query was executed it required company filtering, it seems
> a
> waste having company parameters in every minor query, and would require
> entire code rewrite. Not being a programmer I don't know what options sql
> server may offer, but I know enough about sql to have these sort of
> questions
> distract your thinking until answered...
> I hope this is the correct forum for general questions such as this.
> Thanks in advance.
>
> swas

Multiple Columns from WHERE?

Hey all,
I was curious if this was possible...
I basically have 2 queries I'd like to combine into one. The only difference in the queries is one clause in the WHERE statement

so here is an idea of what I'm talking about

SELECT COUNT(*) as HighStock FROM products WHERE qty > 100

now lets say I needed to do one for low I would have to run that query 2 times with different alias's and change the qty...

is there a way to get all that in one result set? Something like

SELECT COUNT(*) as HighStock, COUNT(*) as LowStock FROM products WHERE qty > 100 AND LowStock = qty < 20

so then my result would be
HighStock LowStock
50 10

anyone have any clues on that? thanks! :)We're going to take what Bugs Bunny used to call "a left turn at Albuquerque" on you to get to the same place:SELECT Sum(CASE WHEN qty > 100 THEN 1 END) AS HighStock
, Sum(CASE WHEN qty < 20 THEN 1 END) AS LowStock
FROM products-PatP|||Pat Phelan ur logic is gr8!! hats off 2 u|||hey Pat!
thanks a million, it actually worked :)
appreciate your help on that one!|||No problem! Always glad to do what I can to confuse things. ;)

-PatP|||PAt. Ur logic is gr8..Hats off to u|||Oh, that wascally Pat! Ain't he a stinker?|||Oh, that wascally Pat! Ain't he a stinker?He he he he he!

-PatP|||Hey Pat...
is it possible to add a DISTINCT to that sum statement somewhere? like give me the Sum(CASE WHEN qty > 100 THEN 1 END WHERE DISTINCT(myfield))
?
I'm sure that syntax is way off but I hope you get my meaning. I Appreciate your help on this one :)|||Ummm... Nope, clear as mud.

-PatP|||Pat, do you have any possible solutions as a workaround? It seems that I have to split up my query into a temp table get the distinct values into it, then query against the temp table. I was hoping to avoid having to make 4 seperate queries. thanks again|||I don't have any clue what you are asking, so I can't answer. I strongly suspect that there is a way to solve your problem, once I understand what you want.

An example would help a lot, showing a dozen or less rows of data, and the result set that you expect to be returned based on that data.

-PatP