Pros/Cons of using Win 2003 over Win 2000 for SQL Server Enterprise ?

We've recently got a new Server in.
The server has 16Gb of RAM, 8 cpus etc
We now have a choice of sticking with Windows 2000 Advanced Server or
going with Windows 2003 Enterprise edition.
Besides being able to use more RAM and having to use Service pack 3a
in Win 2003
Any pros/cons of both ?
Any recommendations ?
We use Full text, replication, analysis services etc
What issues / Enhancements has MS introduced for sp3A ?
I know that once Service pack 3A is installed all full text indexes
are automatically rebuilt..
In a replication scenario Is it necessary or just a recommendation
that all participants in the replication scenario are using the same
service pack ?
Anything else to look out for ?Win2003 has several enhancements that canmake it very worth while. The cpu
scheduling and parallism is improved along with many improvements in the
file and I/O areas. Win2003 is just a better choice overall for an
enterprise server in my opinion.
--
Andrew J. Kelly
SQL Server MVP
"Steve" <stevekeilygroups@.yahoo.com.au> wrote in message
news:70809952.0311191644.338d1eb3@.posting.google.com...
> We've recently got a new Server in.
> The server has 16Gb of RAM, 8 cpus etc
> We now have a choice of sticking with Windows 2000 Advanced Server or
> going with Windows 2003 Enterprise edition.
> Besides being able to use more RAM and having to use Service pack 3a
> in Win 2003
> Any pros/cons of both ?
> Any recommendations ?
> We use Full text, replication, analysis services etc
> What issues / Enhancements has MS introduced for sp3A ?
> I know that once Service pack 3A is installed all full text indexes
> are automatically rebuilt..
> In a replication scenario Is it necessary or just a recommendation
> that all participants in the replication scenario are using the same
> service pack ?
>
> Anything else to look out for ?

pros/cons of using Default instance and Named instance for Prod Se

Hi,
What is the pros and cons of using Default instance and Named instance for
Production Server?
I have been working with Default and I had no need for named instances.
We have a physical/vm servers for all environments.
Production always have a physical server.
So I have not needed a named instance.
What do you recommend to use in Production?
Thanks in advance.SQLapprentice wrote:
> Hi,
> What is the pros and cons of using Default instance and Named
> instance for Production Server?
> I have been working with Default and I had no need for named
> instances. We have a physical/vm servers for all environments.
> Production always have a physical server.
> So I have not needed a named instance.
> What do you recommend to use in Production?
> Thanks in advance.
I don't believe it makes any difference. You can install named instances on
the same server without additional licensing concerns (there is a limit I
believe of 8). However, you cannot use a SQL Server on a VM without a valid
license.
David Gugick
Quest Software|||I vote for a named instance. The advantage of names instances is that
they isolate the physical from the logical. Failing to do that has
caused me countless problems over the years, so I never pass up a
chance to avoid repeating that mistake.
The payback comes with configurations you knew for certain would never
change, change anyway. Example: There are problems with your
production server and have to bring up your production database on a
different box. With names instances the name can move from one box to
the other. With default, you either have to change all the apps that
point to the server, or rename the server (and then name it back when
you shift back to the original server.)
Roy
On Tue, 15 Nov 2005 10:41:06 -0800, you wrote:

>Hi,
>What is the pros and cons of using Default instance and Named instance for
>Production Server?
>I have been working with Default and I had no need for named instances.
>We have a physical/vm servers for all environments.
>Production always have a physical server.
>So I have not needed a named instance.
>What do you recommend to use in Production?
>Thanks in advance.

pros/cons of using Access 2003 as a front-end for SQL 2005

Hi,

I'm creating a SQL 2005 database for a small company. I'm leaning towards using Access 2003 as a front-end for them, since it has a decent report writer and the adp projects seem to preserve SQL's schema relationships.

But I've read some posts where Microsoft is frowning on adp projects. It would cost this company more money in the short-term, but am I better off building a custom .net winforms application instead and avoid Access 2003?

I've done a lot of asp.net coding, but not too much Access or WinForms...so I have a slight learning curve either way.

I've looked at some RAD Tools like Iron Speed Designer, but I'm not sure they'll spend the $$ on that and it seemed complicated to customize the generated code.

thanks,
Bruce
I would recommend to ask this question on access forum instead.

Pros/Cons of Multivalue Database Columns

I'm just wondering what any pros and cons of using multivalue columns in a database are.

I'm designing a database which will have a column for FABRIC_TYPES_AVAILABLE for a certain FURNITURE_ITEM. Each FURNITURE_ITEM can have multiple FABRIC_TYPES_AVAILABLE of course. So I was just going to store a 2 or 3 digit number of the FABRIC_TYPES_AVAILABLE in that row. So I would have something like...34,24,453,32,23,45,67,65,43,21,21,45.

Anyway...thanks in advance for any information. Links I could read would be great too...b/c I did do a bit of searching, but didnt find much.::I'm just wondering what any pros and cons of using multivalue columns in a database are.

I suggest you read a book about database design. This is pretty well explained in about every into chapter about noirmalization that I have ever seen so far.

Look for (at amazon or in your local Bookstore):

"SQL for Dummies".|||do a look up table. they're faster to query than text based columns|||thona thx.

but i do know its not good for normalization; dont exactly have time to go read a book. hehe.

anyway...im going to go head and go for it, or i will use a lookup table.

thx fellaz|||http://www.sqlservercentral.com/columnists/dsumlin/lookupstrategy_printversion.asp

thats about the best info i could find about lookup tables. could you tell me a little bit more about them?

are they basically, just another table, with ID and Value columns for a certain datatype?

If thats true, i understand that...but then in the table where the values from the lookup table go...do you still store your ID as 23,32,43,54,3,25? I mean...if not...how else would a person know what rows to get from the reference table?

this is the way i was originally doing it; so im assuming there is a different/more correct way to do it, which i'm asking you about.|||you will normalize the database now, or you will normalize it in the future. If you don't exactly have the time to read a book, and you don't know how to normalize a database, I question your strength as a developer. I read constantly, and I always make room to read about something. Even if it's in the restroom. (yeah, I got tech mags in there. :))|||You will have three tables:
1. Furniture_Item
Furniture_ItemID
Furnite_Item (description, etc)
Blah blah (but no fabric type info on this table)

Your lookup table:
2.Fabric_Type
Fabric_TypeID
Fabric_Type (the description)

A link table because you have a many to many relationship between your Furniture_Items and Fabric_Type.
3.Item_FabricType
Item_FabricTypeID
Furniture_ItemID
Fabric_TypeID

The third table contains all the FabricTypes per Item,: so you have no FabricTypes on your Furniture_Item table.

I also suggest you do a bit of reading about database design; it's for your own good. :)

HTH

pros/cons keeping master as default db

Is there any concern in leaving master as a user's default database or
should it be changed to another db?
Thanks
BrianOne downside changing it is that most tools doesn't allow you to login if yo
u remove your default
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1179339438.486854.196200@.q23g2000hsg.googlegroups.com...
> Is there any concern in leaving master as a user's default database or
> should it be changed to another db?
> Thanks
> Brian
>|||Brian,
My take is that master is a safe default for everybody who does not have
rights to modify master. For those of us who _can_ modify master, please be
careful.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1179339438.486854.196200@.q23g2000hsg.googlegroups.com...
> Is there any concern in leaving master as a user's default database or
> should it be changed to another db?
> Thanks
> Brian
>sql

pros/cons keeping master as default db

Is there any concern in leaving master as a user's default database or
should it be changed to another db?
Thanks
BrianOne downside changing it is that most tools doesn't allow you to login if you remove your default
database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1179339438.486854.196200@.q23g2000hsg.googlegroups.com...
> Is there any concern in leaving master as a user's default database or
> should it be changed to another db?
> Thanks
> Brian
>|||Brian,
My take is that master is a safe default for everybody who does not have
rights to modify master. For those of us who _can_ modify master, please be
careful.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1179339438.486854.196200@.q23g2000hsg.googlegroups.com...
> Is there any concern in leaving master as a user's default database or
> should it be changed to another db?
> Thanks
> Brian
>

pros/cons keeping master as default db

Is there any concern in leaving master as a user's default database or
should it be changed to another db?
Thanks
Brian
One downside changing it is that most tools doesn't allow you to login if you remove your default
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1179339438.486854.196200@.q23g2000hsg.googlegr oups.com...
> Is there any concern in leaving master as a user's default database or
> should it be changed to another db?
> Thanks
> Brian
>
|||Brian,
My take is that master is a safe default for everybody who does not have
rights to modify master. For those of us who _can_ modify master, please be
careful.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1179339438.486854.196200@.q23g2000hsg.googlegr oups.com...
> Is there any concern in leaving master as a user's default database or
> should it be changed to another db?
> Thanks
> Brian
>

Pros vs Cons of installing MSDE on Win98 SE

Hi All,
Since I have not been able to get MSDE to work under Win2000 Pro while
logged in as a 'Power User' I have to come up with an alternative. I
have searched the MS knowledge base and posted a couple of questions
here but no one has been able to help me with this problem. I notice
that some other people have the same problem and they too were not able
to fix it.
So, my idea is to install MSDE on a Win98 SE computer. Anyone know if
this is a good/bad idea?
Thanks for any help,
Charles
"Charles E Finkenbiner" <CharlesEF_@._MailandNews.Com> wrote in message
news:eEzPbQ0EFHA.2824@.tk2msftngp13.phx.gbl...
> Hi All,
> Since I have not been able to get MSDE to work under Win2000 Pro while
> logged in as a 'Power User' I have to come up with an alternative. I have
> searched the MS knowledge base and posted a couple of questions here but
> no one has been able to help me with this problem. I notice that some
> other people have the same problem and they too were not able to fix it.
> So, my idea is to install MSDE on a Win98 SE computer. Anyone know if
> this is a good/bad idea?
MSDE on Win98SE works fine. With the notable exception of it not
automatically starting the services. Strangely this does not seem to happen
on all 98 installations. There is a workaround for (google that one).

>
> Thanks for any help,
> Charles
|||Plus, in our experience, it's much slower and way less reliable on FAT32
than on NTFS. FAT32 is great for sequential access, not good for random (as
in most databases). NTFS is a journalled filesystem. Words like
"cross-linked clusters" etc. are not words you want associated with your
database. If you are going to use it on Win98, I'd suggest leaving AutoClose
database option on for safety.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Tim Bird" <tim.bird@.nospamprettypleasehebron.co.uk> wrote in message
news:%23KgOn40EFHA.2700@.TK2MSFTNGP14.phx.gbl...
> "Charles E Finkenbiner" <CharlesEF_@._MailandNews.Com> wrote in message
> news:eEzPbQ0EFHA.2824@.tk2msftngp13.phx.gbl...
> MSDE on Win98SE works fine. With the notable exception of it not
> automatically starting the services. Strangely this does not seem to
> happen on all 98 installations. There is a workaround for (google that
> one).
>
>
|||Power User is not enough to install applications under. Get used to it as W2K
onwards, you have to live with it. Security. Security.Security.
Win 98 Or 98SE is no longer fully supported:
http://support.microsoft.com/gp/lifewin
Regards
Mike
"Greg Low [MVP]" wrote:

> Plus, in our experience, it's much slower and way less reliable on FAT32
> than on NTFS. FAT32 is great for sequential access, not good for random (as
> in most databases). NTFS is a journalled filesystem. Words like
> "cross-linked clusters" etc. are not words you want associated with your
> database. If you are going to use it on Win98, I'd suggest leaving AutoClose
> database option on for safety.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Tim Bird" <tim.bird@.nospamprettypleasehebron.co.uk> wrote in message
> news:%23KgOn40EFHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:64304A06-E8CD-4CEF-9BAA-93D53520CDB6@.microsoft.com...
> Power User is not enough to install applications under. Get used to it as
> W2K
> onwards, you have to live with it. Security. Security.Security.
> Win 98 Or 98SE is no longer fully supported:
> http://support.microsoft.com/gp/lifewin
I have to agree with both of you. MSDE will run on Win98 - but not as well.
Even though the sooner the message gets through to users about 98SE the
better, in the "real" world us developers still have to support Win98 and
sometimes even Win95 (Though thankfully not very often)!!!. And we still
occasionally get sales enquiries for Win3.11 installations!!!!!
[vbcol=seagreen]
> Regards
> Mike
> "Greg Low [MVP]" wrote:
|||Hi Mike,
I install MSDE while logged in as 'Administrator' and it installs fine.
It works fine as long as the person logged in has Administrator
rights. The problem is that I do not want to allow anyone to have
Administrator rights. Even I do not use a login with Administrator
rights. I, like all my users, only have 'Power User' rights and that's
the way I want to keep it.
When a 'Power User' logs in the service does not appear to start. The
SQL icon in the taskbar is blank (white circle). When I open it up
there is no computer name or services shown in the drop down lists. If
I type in the computer name and hit the 'Refresh' button then MSDE
starts to run but when I load a certain program (my ISP dialer software)
the MSI installer kicks in. Sometimes it appears to be installing but
then it rolls back and the little icon in the taskbar is gone. Other
times it just sits there and does nothing. Whatever happens the MSI
installer kicks in every time I load my ISP dialer software.
So, I install MSDE while logged in as 'Administrator' but I want MSDE to
run while anyone is logged in as a 'Power User' (without the MSI
installer kicking in every single time).
Thanks for any help,
Charles
On 2/15/2005 5:29 AM, Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Power User is not enough to install applications under. Get used to it as W2K
> onwards, you have to live with it. Security. Security.Security.
> Win 98 Or 98SE is no longer fully supported:
> http://support.microsoft.com/gp/lifewin
> Regards
> Mike
> "Greg Low [MVP]" wrote:
>
|||I believe MSDE is still running on your system when logged in as a Power
User. The issue is that you do not have permissions to enumerate the names
of services. If you bring up Task Manager do you see sqlservr.exe running?
If you do then MSDE is up and ready for connections.
Jim
"Charles E Finkenbiner" <CharlesEF_@._MailandNews.Com> wrote in message
news:uKM%23xO2EFHA.2176@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Hi Mike,
> I install MSDE while logged in as 'Administrator' and it installs fine. It
> works fine as long as the person logged in has Administrator rights. The
> problem is that I do not want to allow anyone to have Administrator
> rights. Even I do not use a login with Administrator rights. I, like all
> my users, only have 'Power User' rights and that's the way I want to keep
> it.
> When a 'Power User' logs in the service does not appear to start. The SQL
> icon in the taskbar is blank (white circle). When I open it up there is
> no computer name or services shown in the drop down lists. If I type in
> the computer name and hit the 'Refresh' button then MSDE starts to run but
> when I load a certain program (my ISP dialer software) the MSI installer
> kicks in. Sometimes it appears to be installing but then it rolls back
> and the little icon in the taskbar is gone. Other times it just sits
> there and does nothing. Whatever happens the MSI installer kicks in every
> time I load my ISP dialer software.
> So, I install MSDE while logged in as 'Administrator' but I want MSDE to
> run while anyone is logged in as a 'Power User' (without the MSI installer
> kicking in every single time).
>
> Thanks for any help,
> Charles
> On 2/15/2005 5:29 AM, Mike Epprecht (SQL MVP) wrote:
|||Hi,
At the moment I have MSDE un-installed. I will re-install it later
today and report back.
Let's assume that what you say is true then my next questions are:
1. Why does the SQL icon even load up in the first place? If a 'Power
User' does not have the correct rights then the icon is useless.
2. Why does the MSDE installer kick in every single time my ISP dialer
is loaded? Any ideas on how to stop this?
Thanks for any help,
Charles
On 2/15/2005 9:50 AM, Jim Young wrote:[vbcol=seagreen]
> I believe MSDE is still running on your system when logged in as a Power
> User. The issue is that you do not have permissions to enumerate the names
> of services. If you bring up Task Manager do you see sqlservr.exe running?
> If you do then MSDE is up and ready for connections.
> Jim
> "Charles E Finkenbiner" <CharlesEF_@._MailandNews.Com> wrote in message
> news:uKM%23xO2EFHA.2176@.TK2MSFTNGP15.phx.gbl...
|||1.SQL Service Manager is placed in the Startup folder for All Users, so it
will run no matter who logs in. If you want you can remove it after MSDE is
installed.
2. Haven't a clue.
Jim
"Charles E Finkenbiner" <CharlesEF_@._MailandNews.Com> wrote in message
news:%23whnwW4EFHA.2608@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> At the moment I have MSDE un-installed. I will re-install it later today
> and report back.
> Let's assume that what you say is true then my next questions are:
> 1. Why does the SQL icon even load up in the first place? If a 'Power
> User' does not have the correct rights then the icon is useless.
> 2. Why does the MSDE installer kick in every single time my ISP dialer is
> loaded? Any ideas on how to stop this?
>
> Thanks for any help,
> Charles
> On 2/15/2005 9:50 AM, Jim Young wrote:
|||Hi,
Ok, I have re-installed MSDE SP3 on my Win2000 Pro computer, then I
applied the KB815495 patch (this is the same process I have done
before). This time the SQL icon does work when logged in as a 'Power
User' but only until I load my ISP dialer software, then MSI installer
kicks in and fails, then the SQL icon disappears from the taskbar.
This time I checked the task manager and I did see 'sqlservr.exe'
process. I 'assume' this means that the SQL server is running. I did
not have time to actually test but will try in a couple of days.
The only problem that remains is the MSDE installer that kicks in every
time the ISP dialer software is loaded. This happens to all 'Power
Users' not just me. If I change the users group from 'Power User' to
'Administrators' then the problem disappears. But, I do not want to do
this. When I change the users group back to 'Power Users' the problem
appears again.
Now, if I can figure out why this happens, so I can stop it, I will be a
happy camper.
Thanks for your help,
Charles
On 2/15/2005 1:10 PM, Jim Young wrote:[vbcol=seagreen]
> 1.SQL Service Manager is placed in the Startup folder for All Users, so it
> will run no matter who logs in. If you want you can remove it after MSDE is
> installed.
> 2. Haven't a clue.
> Jim
> "Charles E Finkenbiner" <CharlesEF_@._MailandNews.Com> wrote in message
> news:%23whnwW4EFHA.2608@.TK2MSFTNGP10.phx.gbl...

Pros and cons with table lock

I use an ole db destination which I simply use to insert data into database.
If it's configured to use Fast Load as access mode, then it is possible to switch on/off the Lock table property, which is set to true as default.

Are there any drawbacks with switching off the Lock table property? What do I gain when using it?

Thanks for your help!

Its a performance issue. Having a table lock means that the load process doesn't have to worry about lower level locks, and so the load should be quicker.
But it does mean no one else can use that table

pros and cons of using transaction replication doing initialization from database backups

I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?

Hi,

Transactional replication provides several different sync method to help alleviate locking issue on publication DB. You can take a look at @.sync_method in sp_addpublication (http://msdn2.microsoft.com/en-us/library/ms188738.aspx).

You can try with concurrent/concurrent_c. And if you are using SQL 2005 SP2 and you are using enterprise edition, you can also use "database snapshot/database snapshot character". Snapshot agent creates a DB snapshot on the publication DB and then generates replication snapshot based on the DB snapshot. So it significantly reduces the possiblities of deadlocking on the publication DB.

Peng

pros and cons of using stored procedures for VB app ?

Hi,
Just wondering what sort of problems and advantages people have found using
stored procedures. I have an app developed in VB6 & VB.NET and our
developers are starting to re-write some of the code in stored procedures
(im advocating encryption of them). When deploying an application however
stored procedure seem to add another level of complexity to installation. In
future we also plan to have an basic ASP app with some of the functionality
of the VB app.
What are the pros and cons of using stored procedures in the situation?
PRO
- can call same stored procedure for ASP pages which saves us writing again
CON
- more complex deployment (i.e sending a bug fix will involve shipping DLL
and SQL script for stored procedure).
Thanks for any input
Scott
pros and cons of using stored procedures for VB app ?
Performance, security, maintainability (db can be maintained
independently of app changes) are some of the advantages. Your
colleagues are right. ALL data access for updates, inserts, deletes and
selects should be done through procs unless you have exceptional
reasons not to. This is the design pattern followed by all good SQL
developers. See:
http://msdn.microsoft.com/library/de...un_1a_6x45.asp
http://www.sql-server-performance.co...procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx
A potential disadvantage if you are a software vendor is that the code
in the DB is exposed to the customer. The protection offered by the
WITH ENCRYPTION option is pretty worthless from a security perspective
- it can easily be circumvented. I don't see this as a major issue
myself because the best protection for your intellectual property ought
to be a licence agreement, not a software obstruction that hampers the
customer's DBA in doing his job.
David Portas
SQL Server MVP
|||many thanks for the reply David. That helps clear a few things up.
You suggested that the "ENCRYPTION" can easily be reversed. My main worry is
customer/users amending deployed stored procedures - hence we thought the
"with enctyption" method would be the best way to protect against this.
Would you use a licence agreement to stop this also ?
cheers
scott
|||Even without decrptying it, the WITH ENCRYPTION option won't stop the
DBA replacing procs. Some decryption solutions are here by the way:
http://www.planetsourcecode.com/vb/s...d=505&lngWId=5
http://www.securiteam.com/tools/6J00S003GU.html
"Users" (non administrators) should not have ddladmin role so should
never be able to modify procs, unless you are talking about a single
user setup where the end user has total control.
You could verify the existence of each proc or even recreate them at
install time for each release. You might also verify them as part of
your support diagnostics. In principle you could store and verify their
CHECKSUMs based on the text in the syscomments table. Be cautious about
how you interpret any checksum though. I can imagine legitimate acts of
scripting and re-creating proc could easily introduce extra line breaks
and whitespace that would change the checksum.
David Portas
SQL Server MVP
|||thanks again. this quote kind of says it in a nutshell...
"since encrypting something that can be decrypted without user interaction
(i.e., entering of a password) isn't anything else than the infamous
security by obscurity."
thanks for your help
Scott

pros and cons of using stored procedures for VB app ?

Hi,
Just wondering what sort of problems and advantages people have found using
stored procedures. I have an app developed in VB6 & VB.NET and our
developers are starting to re-write some of the code in stored procedures
(im advocating encryption of them). When deploying an application however
stored procedure seem to add another level of complexity to installation. In
future we also plan to have an basic ASP app with some of the functionality
of the VB app.
What are the pros and cons of using stored procedures in the situation?
PRO
- can call same stored procedure for ASP pages which saves us writing again
CON
- more complex deployment (i.e sending a bug fix will involve shipping DLL
and SQL script for stored procedure).
Thanks for any input
Scott
pros and cons of using stored procedures for VB app ?Performance, security, maintainability (db can be maintained
independently of app changes) are some of the advantages. Your
colleagues are right. ALL data access for updates, inserts, deletes and
selects should be done through procs unless you have exceptional
reasons not to. This is the design pattern followed by all good SQL
developers. See:
http://msdn.microsoft.com/library/d.../>
1a_6x45.asp
http://www.sql-server-performance.c..._procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx
A potential disadvantage if you are a software vendor is that the code
in the DB is exposed to the customer. The protection offered by the
WITH ENCRYPTION option is pretty worthless from a security perspective
- it can easily be circumvented. I don't see this as a major issue
myself because the best protection for your intellectual property ought
to be a licence agreement, not a software obstruction that hampers the
customer's DBA in doing his job.
David Portas
SQL Server MVP
--|||many thanks for the reply David. That helps clear a few things up.
You suggested that the "ENCRYPTION" can easily be reversed. My main worry is
customer/users amending deployed stored procedures - hence we thought the
"with enctyption" method would be the best way to protect against this.
Would you use a licence agreement to stop this also ?
cheers
scott|||Even without decrptying it, the WITH ENCRYPTION option won't stop the
DBA replacing procs. Some decryption solutions are here by the way:
http://www.planetsourcecode.com/vb/...6J00S003GU.html
"Users" (non administrators) should not have ddladmin role so should
never be able to modify procs, unless you are talking about a single
user setup where the end user has total control.
You could verify the existence of each proc or even recreate them at
install time for each release. You might also verify them as part of
your support diagnostics. In principle you could store and verify their
CHECKSUMs based on the text in the syscomments table. Be cautious about
how you interpret any checksum though. I can imagine legitimate acts of
scripting and re-creating proc could easily introduce extra line breaks
and whitespace that would change the checksum.
David Portas
SQL Server MVP
--|||thanks again. this quote kind of says it in a nutshell...
"since encrypting something that can be decrypted without user interaction
(i.e., entering of a password) isn't anything else than the infamous
security by obscurity."
thanks for your help
Scottsql

pros and cons of using stored procedures for VB app ?

Hi,
Just wondering what sort of problems and advantages people have found using
stored procedures. I have an app developed in VB6 & VB.NET and our
developers are starting to re-write some of the code in stored procedures
(im advocating encryption of them). When deploying an application however
stored procedure seem to add another level of complexity to installation. In
future we also plan to have an basic ASP app with some of the functionality
of the VB app.
What are the pros and cons of using stored procedures in the situation?
PRO
- can call same stored procedure for ASP pages which saves us writing again
CON
- more complex deployment (i.e sending a bug fix will involve shipping DLL
and SQL script for stored procedure).
Thanks for any input
Scott
pros and cons of using stored procedures for VB app ?Performance, security, maintainability (db can be maintained
independently of app changes) are some of the advantages. Your
colleagues are right. ALL data access for updates, inserts, deletes and
selects should be done through procs unless you have exceptional
reasons not to. This is the design pattern followed by all good SQL
developers. See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_6x45.asp
http://www.sql-server-performance.com/stored_procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
A potential disadvantage if you are a software vendor is that the code
in the DB is exposed to the customer. The protection offered by the
WITH ENCRYPTION option is pretty worthless from a security perspective
- it can easily be circumvented. I don't see this as a major issue
myself because the best protection for your intellectual property ought
to be a licence agreement, not a software obstruction that hampers the
customer's DBA in doing his job.
--
David Portas
SQL Server MVP
--|||many thanks for the reply David. That helps clear a few things up.
You suggested that the "ENCRYPTION" can easily be reversed. My main worry is
customer/users amending deployed stored procedures - hence we thought the
"with enctyption" method would be the best way to protect against this.
Would you use a licence agreement to stop this also ?
cheers
scott|||Even without decrptying it, the WITH ENCRYPTION option won't stop the
DBA replacing procs. Some decryption solutions are here by the way:
http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
http://www.securiteam.com/tools/6J00S003GU.html
"Users" (non administrators) should not have ddladmin role so should
never be able to modify procs, unless you are talking about a single
user setup where the end user has total control.
You could verify the existence of each proc or even recreate them at
install time for each release. You might also verify them as part of
your support diagnostics. In principle you could store and verify their
CHECKSUMs based on the text in the syscomments table. Be cautious about
how you interpret any checksum though. I can imagine legitimate acts of
scripting and re-creating proc could easily introduce extra line breaks
and whitespace that would change the checksum.
--
David Portas
SQL Server MVP
--|||thanks again. this quote kind of says it in a nutshell...
"since encrypting something that can be decrypted without user interaction
(i.e., entering of a password) isn't anything else than the infamous
security by obscurity."
thanks for your help
Scott

Pros and cons of using image data type

Can anybody let me know the pros and cons of storing
images using SQL server image data type as opposed to just
storing the image file in system file directories. I'm
more concern on the efficiency and the space used if I
store images using SQL server 2000. Anything bad that
might occur?That's a sensible question to ask ... And many out there forget when they
use image datatypes ... BTW, Iam not a against image datatype but I always
prefer in storing them in the web server itself rather than passing them
around to and forth to the SQL Server. Moreover storing large files at the
sql server is also not advisable ... But if you are storing just a small
signature image for example .. It is fine to use SQL Server ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?|||The Pros of storing the images in SQL Server are pretty simple: You gain
all the "ACID" properties of a database system. The images are backed up
with the data in a coordinated fashion. All "pointers" between your
structured data and the images are maintained. No "broken link" problems,
no out of sync problems, no multiple namespaces, etc. If you store the data
in the database and the images in the file system then you have multiple
backups and various ways for the database and image information to end up
out of sync. Plus, you have to manage two different security environments.
The big negative of storing images in SQL Server is performance. There are
three issues here. The first is that SQL Server breaks images up into
chunks that fit on database pages. This makes reassembling the images
slower than if they are stored, without additional internal structure, in a
file. It also makes it impossible to use the operating system's built-in
facilities to transmit a file directly from disk out over a communications
link in kernel mode. So, from the standpoint of serving the image out onto
the web it is definitely much slower. Second, images stored in SQL Server
are returned to the application via the TDS protocol and the data access
APIs. Again, these are not optimal for image processing and impose overhead
that doesn't exist with a file. Third, most applications that process
images read and write them from the file system. So, if the image is stored
in SQL Server then you have to read the image out of the server, write it to
a temporary file, then invoke the image processing software against the
temporary file. It is these performance issues that lead many people to
store the images outside the database itself.
The real suitability of storing images inside SQL Server versus in the file
system comes down to the analysis of the application itself. For example,
if you have an HR application and one of the pieces of information that you
store about an employee is the picture that is on their id card then I think
you should store that image inside the database. Why? Well, you don't
access it very often. You aren't serving it up to the web constantly. It's
always accessed in conjunction with other employee data. You need to
protect access to the images under your HR policies. The performance hit is
thus not a significant factor when compared with the application and
operational issues.
If I had a server whose primary function was to serve up images all day,
then I'd store them in the file system. Or, at least maybe I would. I
actually have a preferred architecture for this scenario that addresses both
the management and performance issues. But it cost disk space. I would
store the authoritative copies of the images in the database, and then
create copies (a cache) in the file system. I would serve up the images
from the file system, but my recovery procedures would blast the
authoritative copies from the database into the filesystem. So I get the
best of both worlds. And with 120GB disks going for less money than I
usually carry around in my wallet, the duplication hardly seems to be a
problem for most situations.
One system that runs counter to conventional wisdom is the TerraServer
(http://terraserver-usa.com/). You can get details of how it works from
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-99-29.
Even though it serves up images all day long the images are stored in SQL
Server. This was done for a few reasons, chief among them to show that SQL
Server was capable of hosting such an application. Terraserver has been
operational since the summer of 1998, at times serving up several million
images per day.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?

Pros and Cons of Stored Procedures

can anyone explain the Pros and Cons of Stored Procedures ??

thanksThere's plenty of pro's as well as con's to procedures.

One of which is how dynamic you can have these sql statements. However, since it's not so dynamic, the chances of sql injection is far less of an issue.

Furthermore, procedures compile an execution path, and will usually execute much faster. If you have multiple requests you need to make, your procedure can consolidate many calls into one, reducing round trips.

Can you be a bit more specific? This question seems beaten to death, and I'd really suggest just going through the threads or using the search feature.|||See this...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/storedprocsnetdev2.asp

Pros and Cons of starting the SQL services with a user account instead of the lsm

What are the pros and cons of starting the SQL services (MSSQLSERVER &
SQLAgent) with a domain wide user account instead of using the Local
System Account that does not have network access. To me it would seem
more secure to start the services using an account that cannot access
the network.
Thanks
MarcosI suspect that using a different account might force you to run the ole db
drivers for linked servers inside the same process as sql server, check my
earlier thread on october 21.
I am not sure about this but no one has given me a clear answer...
Otherwise we're using different accounts to run our sql servers without
problems (i think, i'm not the one that set up the servers...)
"Marcos Marrero" wrote:
> What are the pros and cons of starting the SQL services (MSSQLSERVER &
> SQLAgent) with a domain wide user account instead of using the Local
> System Account that does not have network access. To me it would seem
> more secure to start the services using an account that cannot access
> the network.
> Thanks
> Marcos
>

PROS and CONS of seperate databases for CACHING...

I have a main database...for this large Web site...and Im wonderingwhat would be the PROS and CONS of using another database (located onthe same, or on another SQL Server). Im just thinking this would begood incase we ever needed to take some load off one of the servers.

Also, we will be integrating Community Server into this Web site. Ofcourse you know CS adds its own database objects which crowd up ourmain database objects.

We were thinking of giving CS its own database also; bad practice, or...it doesn't matter much?

Thank youIf for ASPSTATE, then a separate server (now or in the future) would improve scalability.

If you mean multiple instances of SQL Server on the same server box (SQL Server 2000 and 2005 standard and up), then it is not a good idea as there is an overhead (plus licensing cost).

If you mean multiple databases within the same SQL instance, then that is the way to go.|||multiple databases within the same instance of sql server if its on the same machine...

sort of like this...

BOX1-SQL1 (all in same instance of sql server)
caching db
state db
auditing db

BOX2-SQL2 (all in same instance of sql server)
web site db
forum db

So THATS the way to go? Eh?
|||bumpsql

Pros and Cons of saving to serverstorage versus file system

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

Lito wrote:

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

I agree. The single biggest issue I have with server deployment is that you bring a whole new layer of management into play if you're using the Execute Package task (which likely many people will be).
i.e. At design time you use a file connection manager for calling other packages...at runtime you use OLE DB connection manager. So not only do you have to tell the package which environment its running in so that it knows which connection manager to use...promoting from dev-->test-->live becomes a real headache because you don't have uniformity across environments.

Just my 2 penneth worth!

-Jamie|||Kirk has blogged about some of the Pros and Cons of saving to SQL Server Vs File System. See

http://sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

- Ranjeeta

Pros and Cons of saving to serverstorage versus file system

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

Lito wrote:

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

I agree. The single biggest issue I have with server deployment is that you bring a whole new layer of management into play if you're using the Execute Package task (which likely many people will be).
i.e. At design time you use a file connection manager for calling other packages...at runtime you use OLE DB connection manager. So not only do you have to tell the package which environment its running in so that it knows which connection manager to use...promoting from dev-->test-->live becomes a real headache because you don't have uniformity across environments.

Just my 2 penneth worth!

-Jamie|||Kirk has blogged about some of the Pros and Cons of saving to SQL Server Vs File System. See

http://sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

- Ranjeeta

Pros and cons of reindexing

Hi we have a debate here,
Should we reindex periodically our database? If yes at what frequency? This
will help for performance?
Second question after the reindexing the transactions log have 5 Gig. After
a backup should we shrink the log? This will help performance?
Thank for your help
Dany"Dany Marois" <dany.marois@.videotron.ca> wrote in message
news:uBGRsL9BEHA.3404@.TK2MSFTNGP10.phx.gbl...
> Hi we have a debate here,
> Should we reindex periodically our database?
yes it will because reindexing will defragment the database. As for
frequency, depends on the database. my PeopleSoft db takes 19 hours to
reindex so I reserve weekends for this chore but I do it every week
If yes at what frequency? This
> will help for performance?
>

> Second question after the reindexing the transactions log have 5 Gig.
After
> a backup should we shrink the log? This will help performance?
>
not much but it will save space ...:-)
> Thank for your help
> Dany
>
Neil MacMurchy
"you'd do what, to who for how many biscuits?"|||> Should we reindex periodically our database?
If you need it, i.e., it helps performance, then yes.

> If yes at what frequency?
It depends on the level of fragmentation occurs. I.e., volume of data, the
pattern of your modifications and index layouts and stuff like that.

> This
> will help for performance?
Possibly, but again it depends. You can use DBCC SHOWCONTIG to check the
fragmentation level. And you can time your queries between going against
fragmented data and non-fragmented data to determine how much defrag will
improve performance. Mileage does vary.

> Second question after the reindexing the transactions log have 5 Gig.
After
> a backup should we shrink the log? This will help performance?
Why would you want to shrink it? It will only grow to that size as the users
do modifications and the file need to grow. I.e., shrinking the file will
hurt performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dany Marois" <dany.marois@.videotron.ca> wrote in message
news:uBGRsL9BEHA.3404@.TK2MSFTNGP10.phx.gbl...
> Hi we have a debate here,
> Should we reindex periodically our database? If yes at what frequency?
This
> will help for performance?
> Second question after the reindexing the transactions log have 5 Gig.
After
> a backup should we shrink the log? This will help performance?
> Thank for your help
> Dany
>|||Read this whitepaper which will answer all of your questions:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dany Marois" <dany.marois@.videotron.ca> wrote in message
news:uBGRsL9BEHA.3404@.TK2MSFTNGP10.phx.gbl...
> Hi we have a debate here,
> Should we reindex periodically our database? If yes at what frequency?
This
> will help for performance?
> Second question after the reindexing the transactions log have 5 Gig.
After
> a backup should we shrink the log? This will help performance?
> Thank for your help
> Dany
>