Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many[vbcol=seagreen]
> daily.
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professionalsql
Showing posts with label asp. Show all posts
Showing posts with label asp. Show all posts
Thursday, March 22, 2012
database size
Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> > Hi all, I have an asp application that allows users to upload files and
> > these are being stored in a sql database. The database size is growing
> > relatively fast (at about 1 GB) now. I know the recommended way of
storing
> > uploaded files is on the file system but I chose the database for
several
> > reasons including security and easy of backup since all data is in 1
> central
> > location. My question is does the size of the database effect the
overall
> > performance of the sql server as far as that db is concerned? I have
many
> > other tables in that database being used in various other operations
> daily.
> > Would there be any benefit in maybe moving the uploaded file tables to a
> > different database?
> >
> > much thanks in advance!
> >
> >
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> > Hi all, I have an asp application that allows users to upload files and
> > these are being stored in a sql database. The database size is growing
> > relatively fast (at about 1 GB) now. I know the recommended way of
storing
> > uploaded files is on the file system but I chose the database for
several
> > reasons including security and easy of backup since all data is in 1
> central
> > location. My question is does the size of the database effect the
overall
> > performance of the sql server as far as that db is concerned? I have
many
> > other tables in that database being used in various other operations
> daily.
> > Would there be any benefit in maybe moving the uploaded file tables to a
> > different database?
> >
> > much thanks in advance!
> >
> >
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
Wednesday, March 21, 2012
Database Size
Hi,
I have a web application (MS SQL 2000 / ASP). Database has grown from 20 MB
to 400 MB in the last year or so. I am a little concerned about the size and
how to be prepared when something needs to be done in this sense.
At what size should I be concerned and is there any site that tells what to
do when the database grows a lot ? Will I have performance issues ?
Any information is greatly appreciated
AleksIf this 400 MB is al data then you can not make it smaller without deleting
data
You can however shrink the transaction log, do you have autoshrink enabled,
does someone else shrink it on regularely?
400 MB is nothing for SQL Server we have a DB here that well over 100 GB
with some tables having 8 million records
If you see problems in the future you can always 'archive' old records into
another DB or Data WareHouse
http://sqlservercode.blogspot.com/
"Aleks" wrote:
> Hi,
> I have a web application (MS SQL 2000 / ASP). Database has grown from 20 M
B
> to 400 MB in the last year or so. I am a little concerned about the size a
nd
> how to be prepared when something needs to be done in this sense.
> At what size should I be concerned and is there any site that tells what t
o
> do when the database grows a lot ? Will I have performance issues ?
> Any information is greatly appreciated
> Aleks
>
>|||Are you concerned about database administration/maintenance of large
databases or optimizing the performance of queries? Actually, 400mb is not
large at all for a medium powered box running SQL Server. A typical database
(or collection of related databases) for an insurance or ecommerce company
with a few years of transactional data would be > 20 GB. It's not until you
reach 100 GB that you're talking about something serious. Basically you want
to start gradually introducing data warehousing concepts into your system
design.
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
http://www.microsoft.com/sql/techin...calability.mspx
http://www.microsoft.com/technet/co...ql/sql0326.mspx
http://msdn.microsoft.com/library/d...br />
9okw.asp
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWrusJEwFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a web application (MS SQL 2000 / ASP). Database has grown from 20
> MB to 400 MB in the last year or so. I am a little concerned about the
> size and how to be prepared when something needs to be done in this sense.
> At what size should I be concerned and is there any site that tells what
> to do when the database grows a lot ? Will I have performance issues ?
> Any information is greatly appreciated
> Aleks
>|||Thanks .. this gives me some peace of mind.
A
"JT" <someone@.microsoft.com> wrote in message
news:eae8N%23EwFHA.2960@.tk2msftngp13.phx.gbl...
> Are you concerned about database administration/maintenance of large
> databases or optimizing the performance of queries? Actually, 400mb is not
> large at all for a medium powered box running SQL Server. A typical
> database (or collection of related databases) for an insurance or
> ecommerce company with a few years of transactional data would be > 20 GB.
> It's not until you reach 100 GB that you're talking about something
> serious. Basically you want to start gradually introducing data
> warehousing concepts into your system design.
> http://www.microsoft.com/technet/pr...calability.mspx
> http://www.microsoft.com/technet/co...ql/sql0326.mspx
> http://msdn.microsoft.com/library/d... />
0_9okw.asp
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWrusJEwFHA.2556@.TK2MSFTNGP15.phx.gbl...
>
I have a web application (MS SQL 2000 / ASP). Database has grown from 20 MB
to 400 MB in the last year or so. I am a little concerned about the size and
how to be prepared when something needs to be done in this sense.
At what size should I be concerned and is there any site that tells what to
do when the database grows a lot ? Will I have performance issues ?
Any information is greatly appreciated
AleksIf this 400 MB is al data then you can not make it smaller without deleting
data
You can however shrink the transaction log, do you have autoshrink enabled,
does someone else shrink it on regularely?
400 MB is nothing for SQL Server we have a DB here that well over 100 GB
with some tables having 8 million records
If you see problems in the future you can always 'archive' old records into
another DB or Data WareHouse
http://sqlservercode.blogspot.com/
"Aleks" wrote:
> Hi,
> I have a web application (MS SQL 2000 / ASP). Database has grown from 20 M
B
> to 400 MB in the last year or so. I am a little concerned about the size a
nd
> how to be prepared when something needs to be done in this sense.
> At what size should I be concerned and is there any site that tells what t
o
> do when the database grows a lot ? Will I have performance issues ?
> Any information is greatly appreciated
> Aleks
>
>|||Are you concerned about database administration/maintenance of large
databases or optimizing the performance of queries? Actually, 400mb is not
large at all for a medium powered box running SQL Server. A typical database
(or collection of related databases) for an insurance or ecommerce company
with a few years of transactional data would be > 20 GB. It's not until you
reach 100 GB that you're talking about something serious. Basically you want
to start gradually introducing data warehousing concepts into your system
design.
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
http://www.microsoft.com/sql/techin...calability.mspx
http://www.microsoft.com/technet/co...ql/sql0326.mspx
http://msdn.microsoft.com/library/d...br />
9okw.asp
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWrusJEwFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a web application (MS SQL 2000 / ASP). Database has grown from 20
> MB to 400 MB in the last year or so. I am a little concerned about the
> size and how to be prepared when something needs to be done in this sense.
> At what size should I be concerned and is there any site that tells what
> to do when the database grows a lot ? Will I have performance issues ?
> Any information is greatly appreciated
> Aleks
>|||Thanks .. this gives me some peace of mind.
A
"JT" <someone@.microsoft.com> wrote in message
news:eae8N%23EwFHA.2960@.tk2msftngp13.phx.gbl...
> Are you concerned about database administration/maintenance of large
> databases or optimizing the performance of queries? Actually, 400mb is not
> large at all for a medium powered box running SQL Server. A typical
> database (or collection of related databases) for an insurance or
> ecommerce company with a few years of transactional data would be > 20 GB.
> It's not until you reach 100 GB that you're talking about something
> serious. Basically you want to start gradually introducing data
> warehousing concepts into your system design.
> http://www.microsoft.com/technet/pr...calability.mspx
> http://www.microsoft.com/technet/co...ql/sql0326.mspx
> http://msdn.microsoft.com/library/d... />
0_9okw.asp
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWrusJEwFHA.2556@.TK2MSFTNGP15.phx.gbl...
>
database size
Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!
Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>
|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)[vbcol=seagreen]
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many
> daily.
>
|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
sql
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!
Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>
|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)[vbcol=seagreen]
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many
> daily.
>
|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
sql
Monday, March 19, 2012
Database security design with ASP.net and form-based authentication
I'm setting up an ASP.Net intranet application with a SQL Server 2000
database. The application uses form-based authentication which is supported
by the following tables: User, Role, UserRole (where each user is assigned
specific roles). The system will have several different roles and users can
belong to multiple roles. As an example, let's say I have the following
roles: data entry, guest/view only, admin, report viewer. I'm guessing now
the system will have about 20 unique users. I've figured out how to
implement the role-based part on ASP.Net, but I'm stuck trying to decide the
best way to secure my database tables and stored procedures.
We're on a Novell network, so I'm using SQL Server authentication. At it's
simplest, I could just have one login for my database and lock down all the
tables and stored procedures to that one login. I'd like to have the
security a little tighter, though, so that only users who belong to the
administrative role can access the administrative procedures, only data
entry members can access the data entry procedures, etc.
I've thought of the following scenarios, but none makes me happy:
1) Create a SQL Server login for each user of the application and assign
them to roles. Then lock the tables and procedures down to the appropriate
roles.
I don't want to do this because I want an administrative user to be able to
create new application users through the Web application. This wouldn't be
possible as I don't have rights to create new SQL Server logins. I'd have
to go to my DB Admin each time we want to add a new user, which isn't really
acceptable.
2) Use SQL application roles to secure tables and procedures. We've used
these in other applications, but I'd like to stay away from them since
connection pooling doesn't work with them.
3) Use a set number of SQL Logins for each pre-defined role (data entry,
guest, admin, report viewer) and grant those logins permission to tables and
procedures as appropriate. I think this is my favorite method right now,
but then I'm not sure how to manage the multiple usernames and passwords.
Where do I store them and how does the application decide which one to use?
This is where maybe this question is more appropriate in an ASP.Net group,
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.Since you already have forms-based security, why not use a single SQL login
for all database access?
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I'm setting up an ASP.Net intranet application with a SQL Server 2000
> database. The application uses form-based authentication which is
> supported
> by the following tables: User, Role, UserRole (where each user is assigned
> specific roles). The system will have several different roles and users
> can
> belong to multiple roles. As an example, let's say I have the following
> roles: data entry, guest/view only, admin, report viewer. I'm guessing
> now
> the system will have about 20 unique users. I've figured out how to
> implement the role-based part on ASP.Net, but I'm stuck trying to decide
> the
> best way to secure my database tables and stored procedures.
> We're on a Novell network, so I'm using SQL Server authentication. At
> it's
> simplest, I could just have one login for my database and lock down all
> the
> tables and stored procedures to that one login. I'd like to have the
> security a little tighter, though, so that only users who belong to the
> administrative role can access the administrative procedures, only data
> entry members can access the data entry procedures, etc.
> I've thought of the following scenarios, but none makes me happy:
> 1) Create a SQL Server login for each user of the application and assign
> them to roles. Then lock the tables and procedures down to the
> appropriate
> roles.
> I don't want to do this because I want an administrative user to be able
> to
> create new application users through the Web application. This wouldn't
> be
> possible as I don't have rights to create new SQL Server logins. I'd have
> to go to my DB Admin each time we want to add a new user, which isn't
> really
> acceptable.
> 2) Use SQL application roles to secure tables and procedures. We've used
> these in other applications, but I'd like to stay away from them since
> connection pooling doesn't work with them.
> 3) Use a set number of SQL Logins for each pre-defined role (data entry,
> guest, admin, report viewer) and grant those logins permission to tables
> and
> procedures as appropriate. I think this is my favorite method right now,
> but then I'm not sure how to manage the multiple usernames and passwords.
> Where do I store them and how does the application decide which one to
> use?
> This is where maybe this question is more appropriate in an ASP.Net group,
> but I thought I'd try here first.
> I'm wondering what other people have done in this scenario?
> Thanks,
> Diane Y.
>|||That's actually the way I have it setup now and it's what I've mostly done
in the past. I just really liked how, when I used multiple application
roles, I was able to give only certain roles permission to certain stored
procedures. So, I was just wondering what others have done...
Diane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> Since you already have forms-based security, why not use a single SQL
login
> for all database access?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
> news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
assigned[vbcol=seagreen]
have[vbcol=seagreen]
used[vbcol=seagreen]
now,[vbcol=seagreen]
passwords.[vbcol=seagreen]
group,[vbcol=seagreen]
>|||> So, I was just wondering what others have done...
I usually opt for option #1 (individual logins/database role membership) for
intranet apps, . This allows SQL Server to control security from both
within and outside your application. Unfortunately, this isn't an option
for you due to the reasons you stated.
Application roles vs. role-based logins are similar approaches. These work
well when a user belongs to a single role so that you can use the same
security context for a given user's database access. However, this method
is problematic in your case because a user can belong to multiple roles
(cumulative permissions). The difficult question is how you decide which
database security context to enable when a user belongs to multiple roles
and multiple roles are associated with a particular application feature.
For example, if user Mary belongs to both DataEntry and ReportViewer roles
and your security is such that either role can view a report, which role
should be used as the database security context?
As long as you can define your business rules for identifying the
appropriate database security context, the implementation is easy. All you
need to do is store the application role name or login along with the
password (encrypted) in your Role table. You can then use that for database
access.
IMHO, the single login approach is best in your situation since you don't
want DBA involvement for security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:e8WPEH5QGHA.2300@.TK2MSFTNGP11.phx.gbl...
> That's actually the way I have it setup now and it's what I've mostly done
> in the past. I just really liked how, when I used multiple application
> roles, I was able to give only certain roles permission to certain stored
> procedures. So, I was just wondering what others have done...
> Diane
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> login
> assigned
> have
> used
> now,
> passwords.
> group,
>
database. The application uses form-based authentication which is supported
by the following tables: User, Role, UserRole (where each user is assigned
specific roles). The system will have several different roles and users can
belong to multiple roles. As an example, let's say I have the following
roles: data entry, guest/view only, admin, report viewer. I'm guessing now
the system will have about 20 unique users. I've figured out how to
implement the role-based part on ASP.Net, but I'm stuck trying to decide the
best way to secure my database tables and stored procedures.
We're on a Novell network, so I'm using SQL Server authentication. At it's
simplest, I could just have one login for my database and lock down all the
tables and stored procedures to that one login. I'd like to have the
security a little tighter, though, so that only users who belong to the
administrative role can access the administrative procedures, only data
entry members can access the data entry procedures, etc.
I've thought of the following scenarios, but none makes me happy:
1) Create a SQL Server login for each user of the application and assign
them to roles. Then lock the tables and procedures down to the appropriate
roles.
I don't want to do this because I want an administrative user to be able to
create new application users through the Web application. This wouldn't be
possible as I don't have rights to create new SQL Server logins. I'd have
to go to my DB Admin each time we want to add a new user, which isn't really
acceptable.
2) Use SQL application roles to secure tables and procedures. We've used
these in other applications, but I'd like to stay away from them since
connection pooling doesn't work with them.
3) Use a set number of SQL Logins for each pre-defined role (data entry,
guest, admin, report viewer) and grant those logins permission to tables and
procedures as appropriate. I think this is my favorite method right now,
but then I'm not sure how to manage the multiple usernames and passwords.
Where do I store them and how does the application decide which one to use?
This is where maybe this question is more appropriate in an ASP.Net group,
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.Since you already have forms-based security, why not use a single SQL login
for all database access?
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I'm setting up an ASP.Net intranet application with a SQL Server 2000
> database. The application uses form-based authentication which is
> supported
> by the following tables: User, Role, UserRole (where each user is assigned
> specific roles). The system will have several different roles and users
> can
> belong to multiple roles. As an example, let's say I have the following
> roles: data entry, guest/view only, admin, report viewer. I'm guessing
> now
> the system will have about 20 unique users. I've figured out how to
> implement the role-based part on ASP.Net, but I'm stuck trying to decide
> the
> best way to secure my database tables and stored procedures.
> We're on a Novell network, so I'm using SQL Server authentication. At
> it's
> simplest, I could just have one login for my database and lock down all
> the
> tables and stored procedures to that one login. I'd like to have the
> security a little tighter, though, so that only users who belong to the
> administrative role can access the administrative procedures, only data
> entry members can access the data entry procedures, etc.
> I've thought of the following scenarios, but none makes me happy:
> 1) Create a SQL Server login for each user of the application and assign
> them to roles. Then lock the tables and procedures down to the
> appropriate
> roles.
> I don't want to do this because I want an administrative user to be able
> to
> create new application users through the Web application. This wouldn't
> be
> possible as I don't have rights to create new SQL Server logins. I'd have
> to go to my DB Admin each time we want to add a new user, which isn't
> really
> acceptable.
> 2) Use SQL application roles to secure tables and procedures. We've used
> these in other applications, but I'd like to stay away from them since
> connection pooling doesn't work with them.
> 3) Use a set number of SQL Logins for each pre-defined role (data entry,
> guest, admin, report viewer) and grant those logins permission to tables
> and
> procedures as appropriate. I think this is my favorite method right now,
> but then I'm not sure how to manage the multiple usernames and passwords.
> Where do I store them and how does the application decide which one to
> use?
> This is where maybe this question is more appropriate in an ASP.Net group,
> but I thought I'd try here first.
> I'm wondering what other people have done in this scenario?
> Thanks,
> Diane Y.
>|||That's actually the way I have it setup now and it's what I've mostly done
in the past. I just really liked how, when I used multiple application
roles, I was able to give only certain roles permission to certain stored
procedures. So, I was just wondering what others have done...
Diane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> Since you already have forms-based security, why not use a single SQL
login
> for all database access?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
> news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
assigned[vbcol=seagreen]
have[vbcol=seagreen]
used[vbcol=seagreen]
now,[vbcol=seagreen]
passwords.[vbcol=seagreen]
group,[vbcol=seagreen]
>|||> So, I was just wondering what others have done...
I usually opt for option #1 (individual logins/database role membership) for
intranet apps, . This allows SQL Server to control security from both
within and outside your application. Unfortunately, this isn't an option
for you due to the reasons you stated.
Application roles vs. role-based logins are similar approaches. These work
well when a user belongs to a single role so that you can use the same
security context for a given user's database access. However, this method
is problematic in your case because a user can belong to multiple roles
(cumulative permissions). The difficult question is how you decide which
database security context to enable when a user belongs to multiple roles
and multiple roles are associated with a particular application feature.
For example, if user Mary belongs to both DataEntry and ReportViewer roles
and your security is such that either role can view a report, which role
should be used as the database security context?
As long as you can define your business rules for identifying the
appropriate database security context, the implementation is easy. All you
need to do is store the application role name or login along with the
password (encrypted) in your Role table. You can then use that for database
access.
IMHO, the single login approach is best in your situation since you don't
want DBA involvement for security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:e8WPEH5QGHA.2300@.TK2MSFTNGP11.phx.gbl...
> That's actually the way I have it setup now and it's what I've mostly done
> in the past. I just really liked how, when I used multiple application
> roles, I was able to give only certain roles permission to certain stored
> procedures. So, I was just wondering what others have done...
> Diane
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> login
> assigned
> have
> used
> now,
> passwords.
> group,
>
Friday, February 24, 2012
database Question..
hi everyone..
im using asp.net with vb.net.
i have a situation, there are two drop down list, 1)Car Model 2) Car Colours. A set of specific colours would fill up the second dropdown list based on the Car Model choosen on the first drop down list.
my question is how do i create a database table that enables me to extract the specific colours based on the car model chosen?.How to create database to accomodate this kinda specific info?
hope to get some feedback here.thank you.Hi
There are two ways to go about this and I suppose the option you choose to take will depend on how normalized you want your database to be.
OPTION 1:
----
One Entry for each individual car in the car table
Car Table
--> CarID
--> CarDescription
One entry for each combination of car/colour in the colour table.
Colour Table
--> ColourID
--> ColourDescription
--> CarID (Foreign Key)
Thus you might have 15 entries for the colour blue, but each of these entries will have a different CarID (i.e. the cars that are available in blue). When the car selected is then say the car with CarID 10, populate the colour dropdown with each colour from colour dropdown that has a CarID of 10.
OPTION 2:
----
One Entry for individual car in the car table
Car Table
--> CarID
--> CarDescription
One Entry for individual colour in the colour table
Colour Table
--> ColourID
--> ColourDescription
And then a third table that maps CarID's to ColourID's:
Car_Colour Table
--> CarID (Foreign Key)
--> ColourID (Foreign Key)
Now when CarID 10 is selected in the car dropdown, populate the colour dropdown with each colour that is mapped to CarID 10 in the Car_Colour table...
HTH|||Dear Daily...
Thanks a heap!!!...really appreicate it..:)
Sunday, February 19, 2012
Database Properties
is there anyway to retrieve the database properties and display in an asp.net web form as a quick over view rather than logging onto the server to check, items such as size, space available, last backup data etc?
Cheers
I have managed to get the database size using :
EXEC sp_MSforeachtable @.command1=EXEC sp_spaceused
|||
Hi,
Haven't tried it but you should be able to use theMicrosoft.SqlServer.Management.Smo to do so:
Here's an example :
http://www.aspfree.com/c/a/MS-SQL-Server/Retrieving-SQL-Server-2005-Database-Info-Using-SMO-Database-Info-Table-Info/1/
HTH,
Suprotim Agarwal
--
http://www.dotnetcurry.com
--
Database problems with loging into my site
I have build a ASP.NET website with a login form. I can create an account but I am not able to login into the site. I have checked the connection string. I have changed the database folder and moved files around to get the database runing from a different folder but it still wont work. ThanksTim
Which version of ASP.NET are you working with?
Which version of SQL Server are you working with?
Try copying the contents of your connection string to a expty text file, rename the file to X.UDL and double click on it. The connection dialog should appear. Click the test button.
|||i am using VWD 2005 Express and I am hosting it using IIS 5.1 (Windows XP) as my server. I am working on Workframe 2
Database problems
I am new to ASP.Net so I have a bunch of beginner problems here. My first BIG one is that every time I try and make a database connection in my code, I get
"Login failed for user 'sa'. Reason: Not associated with trusted SQL Server connection"
I know 'sa' is a valid user for the database, and I have retreived data other ways through swlDataAdapters, sqlConnections, and Datasets from design view. Any idea why I cannot expictitly connection to my DB in my code?? Or is there something stupid and small that I am overlooking?
Any help will be greatly appreciated.::Or is there something stupid and small that I am overlooking?
It is, but given the maount of people stumbling over it - you are not alone.
SQL Server simply is set not to look for the password but to use Windows integrated authentication ONLY. In this case access is checked against the account asp.net is running under, which has not enough security rights.
You need to reconfigure SQL Server to use mixed mode authentication, so that the username/password parts of the connection string actually have a real meaning. Especially the password part.
Friday, February 17, 2012
Database permissions
Hi,
In my web app I've got two databases (the asp profile one and my own custom one). If I take the code and data and use it to create another website on another box, I usually get a database error saying the permissions are wrong. In this case I usually just give everyone full control and it works, but this obviously isn't good practice!
So, I'm wondering what permissions on database files does ASP need? Which users need what permissions?
Thanks
The most reliable way to move a SQL Server database and permissions is Backup and Restore all other methods sometimes will not move permissions so you have to go in and add those as needed. There are two permissions in SQL Server needed to run Asp.net both are covered in the second thread and the first thread is a FAQ I created to help with moving SQL Server database from one computer to another. The permissions you give a user is at you discretion and that is covered under SQL Server object permissions in SQL Server BOL(books online). Hope this helps.
http://forums.asp.net/thread/1454694.aspx
http://forums.asp.net/thread/1492092.aspx
|||Hi,
That's great thanks. The only problem is that my database is stored on a .mdf file and I cannot open it in MS SQL server management studio to change the database permissions. Is there a way around this?
Thanks
|||
That is not good practice so the SQL Server team have created a tutorial to help you connect your user instance to Management Studio when you do that you can go in and change the permissions. I would not advice you to give the everyone group full control but don't remove it from your Asp.net folders either because Asp.net is also a member of that group. Post again if you still have questions. Hope this helps.
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
database performance management
Hi
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.
please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.
As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.
It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.
At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.
please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.
As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.
It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.
At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.
Tuesday, February 14, 2012
Database owner change
Hello,
I'm using tableadapters in VWD 2005 Express to link our ASP.NET application to a SQL Server 2000 database. Initially, I used Database Explorer to drop tables into the DataSet object, which picks up the owner of the tables from the database.
Recently, we had to change the owner of all objects in the database, including tables and stored procedures. When we run our application it chokes on stored procedures because the owner of the SP's has changed.
My question is: how can I update tables and tableadapters in the DataSet to reflect the change in owner, without having to recreate everything in DataSet?
We resolved this.
Subscribe to:
Posts (Atom)