Detecting Feature Pack components on a SQL Server server

We have a product that uses, and will bootstrap, several of the feature pack components when it is installed (xmo, MSOLAP9, etc). However, if our product is installed on a machine that has SQL Server 2005 already installed, all of the feature pack components will have been installed as part of the server install, so we don't want to bootstrap them in that case. Is there a good way to detect this case? Clearly, simply checking that the feature pack MSI hasn't been installed isn't good enough, since SQL Server has its own MSIs that are different from the FP MSIs.

Can you try to check the registry keys under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server or use WMI to detect the SQL Server 2005 components? WMI should be a better choice.

|||

I'm not sure that would be wise in that case, since if SQL Server 2005 was removed it would break your application. You are best off to install the MSIs all the time, and to use the product ref-counting mechanism available in the redists to make sure they won't be removed unless your application has been. This is done by running the redist MSIs with the APPGUID property set on the command line, with the value of the property being the product code of your application that is being installed. What this will do is block the user from uninstalling those applications until your product has been removed first.

Design question - stored prosedures vs. components

It is a design question which affects SQL Server.
Scenario A. Business logic in SQL Server stored procedures. No middle tier
components is used. As a result the stored procedures are very long, up to
5,000 lines.
Scenario B. Business logic is in components. Stored procedures are used for
elementary operations, insert, update, delete and therefore are very short.
Components have code that glues everything together. Components can run on a
separate server.
In my opinion, scenario A doesn't scale well and causes very high cpu
utilization on SQL Server but I would like to listen somebody else's opinion
on that ...
Thanks,
-Stan"Stan" <nospam@.yahoo.com> wrote in message
news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> It is a design question which affects SQL Server.
> Scenario A. Business logic in SQL Server stored procedures. No middle tier
> components is used. As a result the stored procedures are very long, up to
> 5,000 lines.
> Scenario B. Business logic is in components. Stored procedures are used
for
> elementary operations, insert, update, delete and therefore are very
short.
> Components have code that glues everything together. Components can run on
a
> separate server.
> In my opinion, scenario A doesn't scale well and causes very high cpu
> utilization on SQL Server but I would like to listen somebody else's
opinion
> on that ...
> Thanks,
> -Stan
Stan,
Let me give you my opinion. We have a similar set up at our location.
We developed a system where all business logic laid in SQLServer SPs,
triggers, etc, and no middle tier. Only light validations were performed at
the UI and the heavy stuff took place at the database server. Pros: Any
change in business rules affected 80%-100% the database layer (SPs) and
0%-20% on the client side. This made it a lot easier for us to modify or
correct code, save and go, without having too much impact on the client
layer. Also, having all database objects at hand (tables, views, other SPs,
etc.) is always a great advantage if you place your business logic in SPs.
Cons: As you said, it causes very high CPU utilization, but that will always
depend on the hardware you are using, and how intense will be used your
application. This drawback may be compensated with a good state-of-the-art
server, if feasible.
Having business logic in a middle tier also has its great advantages, and
may free your database server machine of hardwork. But then, in my opinion,
you may have to deal with C++/VB (or whatever language your are using) code,
that definitely is more difficult to maintain than SQL statements.
In conclusion, there is actually no better approach between these two. That
will depend on the requirements specifications for your system (how
complicate it will be, what would fit better based on your requirements) and
the expected usage.
C.|||I agree entirely with Carlitos, but would like to add... for MAXIMUM
scalability you must go to a middle tier... Like Carlitos, I find loading
the back end, easy and quick to create and maintain... However, if you need
HIGHEST scalability, when you have already purchased the largest SQL Server
box that money can buy, what do you do?... you have to move work OFF of the
SQL Server onto another box - middle tier...
There is no best answer - except the one that meets your needs
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Stan" <nospam@.yahoo.com> wrote in message
news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> It is a design question which affects SQL Server.
> Scenario A. Business logic in SQL Server stored procedures. No middle tier
> components is used. As a result the stored procedures are very long, up to
> 5,000 lines.
> Scenario B. Business logic is in components. Stored procedures are used
for
> elementary operations, insert, update, delete and therefore are very
short.
> Components have code that glues everything together. Components can run on
a
> separate server.
> In my opinion, scenario A doesn't scale well and causes very high cpu
> utilization on SQL Server but I would like to listen somebody else's
opinion
> on that ...
> Thanks,
> -Stan
>|||I concur with Wayne in what he added.
C.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I agree entirely with Carlitos, but would like to add... for MAXIMUM
> scalability you must go to a middle tier... Like Carlitos, I find loading
> the back end, easy and quick to create and maintain... However, if you
need
> HIGHEST scalability, when you have already purchased the largest SQL
Server
> box that money can buy, what do you do?... you have to move work OFF of
the
> SQL Server onto another box - middle tier...
> There is no best answer - except the one that meets your needs
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Stan" <nospam@.yahoo.com> wrote in message
> news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > It is a design question which affects SQL Server.
> >
> > Scenario A. Business logic in SQL Server stored procedures. No middle
tier
> > components is used. As a result the stored procedures are very long, up
to
> > 5,000 lines.
> >
> > Scenario B. Business logic is in components. Stored procedures are used
> for
> > elementary operations, insert, update, delete and therefore are very
> short.
> > Components have code that glues everything together. Components can run
on
> a
> > separate server.
> >
> > In my opinion, scenario A doesn't scale well and causes very high cpu
> > utilization on SQL Server but I would like to listen somebody else's
> opinion
> > on that ...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||I agree - it all depends on situation.
I am trying to feel the magnitude of a difference between these two
scenarios.
It is amazingly easy, though, to load 8-way the-best-money-can-buy SQL
server box
with stored procedures that are long, run often and use a lot of temp tables
...
Despite that, there are a lot of vendors and developers who opt for keeping
business logic on SQL Server...
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I agree entirely with Carlitos, but would like to add... for MAXIMUM
> scalability you must go to a middle tier... Like Carlitos, I find loading
> the back end, easy and quick to create and maintain... However, if you
need
> HIGHEST scalability, when you have already purchased the largest SQL
Server
> box that money can buy, what do you do?... you have to move work OFF of
the
> SQL Server onto another box - middle tier...
> There is no best answer - except the one that meets your needs
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Stan" <nospam@.yahoo.com> wrote in message
> news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > It is a design question which affects SQL Server.
> >
> > Scenario A. Business logic in SQL Server stored procedures. No middle
tier
> > components is used. As a result the stored procedures are very long, up
to
> > 5,000 lines.
> >
> > Scenario B. Business logic is in components. Stored procedures are used
> for
> > elementary operations, insert, update, delete and therefore are very
> short.
> > Components have code that glues everything together. Components can run
on
> a
> > separate server.
> >
> > In my opinion, scenario A doesn't scale well and causes very high cpu
> > utilization on SQL Server but I would like to listen somebody else's
> opinion
> > on that ...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||You have to get rid of the temp tables then, they are expensive resource
wise. A lot of cases I have seen where temporary tables were used in stored
procedures they could be rewritten into one SQL statement, usually with
derived tables, and performance went up by what I think is the rule-of-thumb
for these kind of scenarios: 50 times. Yes, you read that correct, writing
proper SQL code instead of using a procedural approach and temp tables (and
even worse cursors) makes your code run 50 times as fast.
Jacco Schalkwijk
SQL Server MVP
"Stan" <nospam@.yahoo.com> wrote in message
news:O8Nbr9pjDHA.3688@.TK2MSFTNGP11.phx.gbl...
> I agree - it all depends on situation.
> I am trying to feel the magnitude of a difference between these two
> scenarios.
> It is amazingly easy, though, to load 8-way the-best-money-can-buy SQL
> server box
> with stored procedures that are long, run often and use a lot of temp
tables
> ...
> Despite that, there are a lot of vendors and developers who opt for
keeping
> business logic on SQL Server...
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > I agree entirely with Carlitos, but would like to add... for MAXIMUM
> > scalability you must go to a middle tier... Like Carlitos, I find
loading
> > the back end, easy and quick to create and maintain... However, if you
> need
> > HIGHEST scalability, when you have already purchased the largest SQL
> Server
> > box that money can buy, what do you do?... you have to move work OFF of
> the
> > SQL Server onto another box - middle tier...
> >
> > There is no best answer - except the one that meets your needs
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > > It is a design question which affects SQL Server.
> > >
> > > Scenario A. Business logic in SQL Server stored procedures. No middle
> tier
> > > components is used. As a result the stored procedures are very long,
up
> to
> > > 5,000 lines.
> > >
> > > Scenario B. Business logic is in components. Stored procedures are
used
> > for
> > > elementary operations, insert, update, delete and therefore are very
> > short.
> > > Components have code that glues everything together. Components can
run
> on
> > a
> > > separate server.
> > >
> > > In my opinion, scenario A doesn't scale well and causes very high cpu
> > > utilization on SQL Server but I would like to listen somebody else's
> > opinion
> > > on that ...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||On Thu, 9 Oct 2003 21:57:14 +0100, "Jacco Schalkwijk"
<NOSPAMjaccos@.eurostop.co.uk> wrote:
>You have to get rid of the temp tables then, they are expensive resource
>wise. A lot of cases I have seen where temporary tables were used in stored
>procedures they could be rewritten into one SQL statement, usually with
>derived tables, and performance went up by what I think is the rule-of-thumb
>for these kind of scenarios: 50 times. Yes, you read that correct, writing
>proper SQL code instead of using a procedural approach and temp tables (and
>even worse cursors) makes your code run 50 times as fast.
I just want to concur with what so far is an amazingly consistent
thread!
Putting everything in the back end is probably extreme, as is putting
nothing in the back end. I will share this wisdom with you, that what
the back end is all about is not tables, but a data model. If all
your middle tier asks of the database is simple stuff, it is extreme
in another dimension, and doing things the hard way.
5,000 lines is way too long for a single SP, I'd guestimate from just
that, that something is majorly wrong. Lots of cursors, too, I'll
bet, unnecessary and horribly slow cursors, is what I mean. Clean up
the database tier, and the entire problem might just vanish. I've
heard that it's easy to load up an 8-way system with SQLServer because
neither Wintel nor SQLServer is very good at it, but the only case
I've actually seen had such crude code running on it, that it was
really inappropriate to bitch about the hardware.
No easy answers.
Joshua Stern|||Hi Stan,
Yes, there is no easy answer. I'm including some supplemental information
below:
Where Should Business Logic Go?
http://www.4guysfromrolla.com/webtech/121501-1.shtml
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Design help please.....

Hello,

I am a newbie and I need to know if I am creating this database properly.

Here is the situation. I have three main components to the database: A user, a report and a profile. Basically it is a security database deciding the access rights to the report defined by the updatable profile.

Here is how I have created the database:

USERS
----
UID [FK]
PWD
First_Name
Last_Name
Profile_Id

PROFILES
----
Profile_Id [PK]
Profile_Desc

REPORTS
----
Report_Id
Report_Desc

PROFILE_REPORTS_LINK
----------
Index [PK]
Profile_Id [PK]
Report_Id [PK]

REPORT_ACCESS
-------
Index
Access_Selections

If I start off with a UID, my app. would need to retrieve the access_selections based upon the current report selected and the profile_id previously defined by which user is logged in. I hope this makes sense... I would like to know if there is a better way of doing this and if I am to choose this way, how do I update the PROFILE_REPORTS_LINK table every time I add a new profile or a new report?

Thanks in advance.
MORI0043Is it 1-to-1 relationship between USERS and PROFILES? Is UID supposed to be a PK, not FK, and Profile_Id in USERS to be FK? And in PROFILE_REPORTS_LINK Index to stay PK while all other the fields listed are supposed to be FK, not PK, right? And REPORT_ACCESS.Index to be FK? Lastly, can a profile belong to more than one user, which means it's not 1-to-1, but rather 0/1-to-many relationship between PROFILES and USERS? If the answer is YES to all the above but the first one, - then I have one more questions, - what is the purpose of PROFILES table? Unless some additional info is present, - you can easily remove PROFILES and put UID in place of Profile_Id into PROFILE_REPORTS_LINK. Of course, this is only if I am not completely off target :)|||-Sorry... Mistype. UID is a PK.
-Yes it is a 1 to 1 relationship between USERS and PROFILES
-Sorry again.. I was going too fast. Yes in the PROFILE_LINK table both IDs are [FK]s
-You can have a profile for 0-many users... Basically it is a group profile where there would be administrators group and general group and so forth
-I don't think that you remove PROFILES because this table defines the profile which can exist for 0-many users.

Am I off on this? Am I doing this right? When I go to create a report or how do I make sure that the PROFILE_REPORT_LINK table is updated for every existing PROFILE and the same with when I add a PROFILE?

Thanks again...|||I think your design looks good. A query like this:

select Report_Access.*
from Report_Access
inner join Profile_Reports....
inner join Profiles...
inner join Users...
where Profile_Reports.Report_ID = @.Active_Report_ID
and Users.UID = @.Active_User_UID

easily gets the info you need.

You could make your schema simpler, but only at the cost of flexibility and ongoing user and report administration.

To update the profile_reports table, create a form that shows all the assigned profiles for a given report, and allows you to add or delete profiles. (I assume not all profiles have access to all reports?)

I doubt that you will find a way to automatically add profile_reports records whenever a new report or profile is added, unless you have pre-established report_access values by default.

blindman|||Thanks,

But what about using a trigger to update the PROFILE_REPORT_LINK table with the updated record id value in a loop adding for each opposite table id. For example: If I were to add a new PROFILE I would add a new take the new Profile_id and add a new record to PROFILE_REPORT_LINK for every existing Report_id in the REPORT table?

I am not quite sure how to do this though? What do you think?

Thanks.|||Sure, but what would the defaults be for the report _access table values?

You are setting up a security system here, so I would advise against automating new report or profile set up on principle unless you have rigidly defined business rules.

If all your profiles have access to all the reports with the same Access_Selections, then you could go with a simlper design.

blindman|||Blindman,

All USERS have access to all of the REPORTS but all with different SELECTIONS. The SELECTIONS are different depending on the Profile_ID. The Adminstrator will have access to add a new PROFILE with new SELECTIONS. The Reports will only be added by the developer in the future. There are three records in REPORTS now, but this will surely be added upon in the future.

Maybe I should force the user to define a SELECTION for each existing REPORT when the user creates a new profile from within my Front End code.

Do you think that this is a better option?

Thanks.|||Sorry, by SELECTIONS I meant to say REPORT_ACCESS|||rdj - it looks like profiles has 1-to-0/many with users table.

I think having a set of sps to handle the entire structure is the way to go:

usp_AddNewProfile
usp_AddNewUser
usp_AddNewReport
...
etc.|||Here is what I am doing now and it seems to work....

CREATE TRIGGER updated_profile on Profiles
FOR INSERT
AS

DECLARE @.Temp_Profile_Id as int
DECLARE @.Temp_Report_Id as int
DECLARE C1 CURSOR FOR
SELECT Report_Id FROM Reports

SET @.Temp_Profile_Id = (Select Profile_Id from Inserted)

OPEN C1

FETCH NEXT FROM C1
INTO @.Temp_Report_Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO Profile_Report_Link(Profile_Id, Report_Id)
VALUES(@.Temp_Profile_Id, @.Temp_Report_Id)
FETCH NEXT FROM C1
INTO @.Temp_Report_Id
END

CLOSE C1
DEALLOCATE C1]

Now I just have to create Triggers for when I add a Report.|||I like the SPs better than the triggers, again just on the principle of not automating a lot of security routines, but if it works for you then go for it.

blindman|||Thanks for all of the help.