My company is currently setting up an application for a client that requires a Database server on the backend. We have selected SQL Server 2000 Enterprise as our DB but we are not sure how to size the hardware of the server. I have searched around and it appears that DB sizing is more experience/trial & error based than formula-based. I hope someone here can provide me with some advice if I provide the performance requirements of the server.
5 GB of data currently (will double in size every year) Approx. 10 million records (will double in size as well) 85,000 Transactions / hour 200 Concurrent users Client requires no greater than 1/2 second response time.
Questions:
How many CPU's should be needed? Why? How much RAM should be needed? Why?
RAID 10 was the recommended fault tolerance. Agree? Disagree?
Thanks in advance
JBAs you guessed there is no magical answer but here are some things to think about.
The actual size of the db is not too critical as long as you plan for growth and take into account how the drive arrays should be set up to achieve good performance. 85K per hour is less than 30 a second and although I wouldn't try that on a single processor box it is not too bad. (I routinely do over 800 a second with 4 Processors).
But since you need to handle Peak amounts and want less than 1 second response time you should be particularly aware of minimums. Ram will depend on how much of the data you actually use on a regular basis. You want to aim for enough ram to have all the data that you access on a regular basis in cache at one time leaving room for the procedure cache etc. Just because you have 5GB of data doesn't mean you will need 5GB of ram. You can always add ram if needed later just make sure you plan for growth ahead of time and save some ram slots so you don't have to throw away ram later to add more. It's always better to have more ram than not enough (don't forget to leave some for the OS too).
As for the hardwareI would shoot for the following:
RAID 1 (or 10) for the Log file(s).
RAID 5 or 10 for the data files (smaller and more disks vs less larger ones).
Raid 1 for the OS and SQL System files.
Depending on how much you will use tempdb (sorting etc) you may or may not want a separate Raid for tempdb.
If you do disk backups you may want to think about another RAID or 5 for the backups. In all cases make sure the arrays are expandable for future growth.
As for processors you will probably want to start with a 4 or 8 processor box with less than all the procs to begin with. The number of procs will depend on so many things but if you don't have enough you will probably see less than 1/2 second response time in peak loads while the rest of the time it will be fine. Poor code or schema is usually the reasons for needing more processors.
Andrewsql
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Database size question
I have a small application that I've developed using MSDE (2K). I'm
going to put this in to production by setting up a new computer and it
seems that Server 2005 Express edition will be adequate for our needs.
I want to check that the size of the databases I'm using are less than
4GB. How do I do that? I've looked around in SQL Server Enterprise
Manager but I don't see file sizes shown anywhere.
In Query Analyzer, try this:
EXECUTE sp_spaceused
In Enterprise Mangler, right click on the database, choose [Properties]. The
file size is in the middle of the page on the [General] tab,
Also, you can use Windows Explorer to view the database file. (It's not a
precise measure, but it's close.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
|||Thanks, Arnie - I don't know why I couldn't find that
My db's are all in the single-digit MB size, so I guess that the 4GB
limit of the Express version won't be an issue. And of course, if it
ever becomes an issue, we just have to upgrade.
Thanks again.
On Sat, 11 Nov 2006 09:10:03 -0800, "Arnie Rowland" <arnie@.1568.com>
wrote:
>In Query Analyzer, try this:
>EXECUTE sp_spaceused
>In Enterprise Mangler, right click on the database, choose [Properties]. The
>file size is in the middle of the page on the [General] tab,
>Also, you can use Windows Explorer to view the database file. (It's not a
>precise measure, but it's close.)
|||The maximum size of an MSDE database is 2GB so you can be sure your
databases are under 4GB
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
going to put this in to production by setting up a new computer and it
seems that Server 2005 Express edition will be adequate for our needs.
I want to check that the size of the databases I'm using are less than
4GB. How do I do that? I've looked around in SQL Server Enterprise
Manager but I don't see file sizes shown anywhere.
In Query Analyzer, try this:
EXECUTE sp_spaceused
In Enterprise Mangler, right click on the database, choose [Properties]. The
file size is in the middle of the page on the [General] tab,
Also, you can use Windows Explorer to view the database file. (It's not a
precise measure, but it's close.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
|||Thanks, Arnie - I don't know why I couldn't find that
My db's are all in the single-digit MB size, so I guess that the 4GB
limit of the Express version won't be an issue. And of course, if it
ever becomes an issue, we just have to upgrade.
Thanks again.
On Sat, 11 Nov 2006 09:10:03 -0800, "Arnie Rowland" <arnie@.1568.com>
wrote:
>In Query Analyzer, try this:
>EXECUTE sp_spaceused
>In Enterprise Mangler, right click on the database, choose [Properties]. The
>file size is in the middle of the page on the [General] tab,
>Also, you can use Windows Explorer to view the database file. (It's not a
>precise measure, but it's close.)
|||The maximum size of an MSDE database is 2GB so you can be sure your
databases are under 4GB
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
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...
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
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
Database size
Hi all,
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/e...des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/bo...TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx
.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/e...des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/bo...TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx
.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
Database size
Hi all,
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/books/toc/4944.asp#TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/books/toc/4944.asp#TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
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 need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
Hari
Hi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en...es_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/boo...ableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
Hari
Hi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en...es_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/boo...ableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
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 server connection
I am trying to using sqlserver as back-end for an application(web) running
on the window 2X, one thing I am not sure is that once I establish the
connection between database server and application, should I keep this
connection open while application is running, or only open it when
there is a requet to qury records from database?
on the window 2X, one thing I am not sure is that once I establish the
connection between database server and application, should I keep this
connection open while application is running, or only open it when
there is a requet to qury records from database?
Thanks for your help."Leon" <penghao98@.hotmail.com> wrote in message
news:89a0a7de.0409221926.6c394129@.posting.google.c om...
>I am trying to using sqlserver as back-end for an application(web) running
> on the window 2X, one thing I am not sure is that once I establish the
> connection between database server and application, should I keep this
> connection open while application is running, or only open it when
> there is a requet to qury records from database?
> Thanks for your help.
I believe the usual approach is to leave the connection open, to avoid the
overhead associated with opening and closing the connection again and again.
This is one reason for using connection pooling, for example.
You might want to post in a forum for your client library (eg ADO, JDBC) or
development environment (eg ASP, PHP) to see if there are any specific
considerations for your application.
Simon|||penghao98@.hotmail.com (Leon) wrote in message news:<89a0a7de.0409221926.6c394129@.posting.google.com>...
> I am trying to using sqlserver as back-end for an application(web) running
> on the window 2X, one thing I am not sure is that once I establish the
> connection between database server and application, should I keep this
> connection open while application is running, or only open it when
> there is a requet to qury records from database?
> Thanks for your help.
Hi,
This is question basically regarding connection pooling.
Which technology you are using for database acess? like ADO,JDO, JDBc
etc..?
In all these you can create a pool of connections and maintain it. So
the best thing which I feel is ,during boot up of your application
create a pool of connections, store it and use the connection when
needed.
This is purely dependent on your application also.
Some technologies like ADO and JDBC now support disconnected rowsets
also like CachedRowset in Java where internally it will(means
driver/proider) handle reconnecting to the server when a request
comes.
But from performance point of view, better to create connections
during start up.
How many connections? etc depending on the usage of your application
Regards
Zunil
Cordys
DataBase security problems
Hello every one
We have made a application which cancreate and restoredata base for MS SQLwe also set the password. we want to make it secure. problem is thatif data base files are copy from one system to other system and try to open then . they are openedwith out asking data basePass word can any told me solution so that our data base become secure
Thank
Hello!
I developed database driven VC++ application. I faced a problem, which is "how to protect my database against direct access". E.g. .when i copy data files from one server to another and then using to attach the database to the new server the data base files are openedwith out asking password .
I use MS SQL Server 2000 enterprise Edition as a DBMS and appropriate database.
I want to make possible to manipulate with data in my database only through my client application.
1. How do I define SA password and instance name in silent mode of MS SQL 2000 EE installation with Mixed type of Authentication?
2. If my database be attached to my new instance. Is it possible to copy my database, attach it to another instance and get a direct access to its objects?
|||Hi,actually you can′t secure it. The magic word in this case is prevention. Secure the directory that noone can connect to the server directory except the SQL Server Service user and administrator.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
This topic has been discussed already here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1.
Thanks
Laurentiu
Database security on a Local Network
This is regarding general protection of a database hosted on a network. I am developing a database application for my college library using VB.NET, that will reside on a network.
For some reasons, I did not want to hardcode the Database location in the application. Instead, when a user logs in, he can choose the database location using a folder browser control, if the location has changed.
1) My database is in Access
2) And, I am not moving the Database at run-time. The database will remain in its non-shared folder. And there will be a text file, that will act as a sort of pointer to the database location for my application, as I will read the DB path from the text file.|||
For some reasons, I did not want to hardcode the Database location in the application. Instead, when a user logs in, he can choose the database location using a folder browser control, if the location has changed.
Now, I realize that for this, I have to put the database in a shared folder, which makes it quite vulnerable. Having pondered over the problem for sometime, a solution that comes to my mind is to place a Text file in the same shared folder that always contains the correct path of the database. When a user chooses that folder, I will read the actual path of the database from the text file, and move the database to a non-shared folder.
I haven't yet implemented this approach, but felt it better to consult someone before. So, would this approach work, and is it a good idea.
For information purposes, I consider it important to mention that the database is in MS Access. I know this is not a place for discussing it, but this is a general security concern. So, I thought
people would not mind answering it....
Hi,
how aout securing the MDB file using the appropiate NTFS permissions and eventually additional Access password security or using an ldb file ? I don′t know if there can be concurrent users on the database, but coyping the database file to a shared folder will allow other users also to copy the file to another folder and working on it, for you having the trouble to bring the data together afterwards.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||More than the problem of bringing it together afterwards, I am worried about someone manipulating it mischievously. That's why I thought of putting the database in a non-shared folder on the server and a text file in a shared folder, which will always
contain the correct path of the DB on the server.
So, when a user logs in, he will select the path of the text file. As he
will do so with a folder chooser, he will not know that the folder contains a
text file & not the actual DB. Internally, I will read the database path from
that file in my application, & use that path to construct the connection
string...
I think this approach will shield the database from direct access on the network, using an explorer etc.
I already have Access password security, but still I dont want the database to be directly accessible on the network.
Can you elaborate a bit more on securing the Database on the server with NTFS permissions, in a way that my application can still access & manipulate it?|||
One thing, you cannot perform such move/copy with a SQL Server database as that will be in exclusive use of SQLengine.
Refer to KBA http://support.microsoft.com/kb/295234, http://support.microsoft.com/kb/307901 and link http://vb123.com/toolshed/links/map/opr.htm for more information.
|||There are two things I will mention again here...1) My database is in Access
2) And, I am not moving the Database at run-time. The database will remain in its non-shared folder. And there will be a text file, that will act as a sort of pointer to the database location for my application, as I will read the DB path from the text file.|||
I suggest posting this question on a Microsoft Access or Microsoft Visual Basic forum instead of this one. This forum is used for posting questions related to Microsoft SQL Server security features, as you observed, and your question is Access specific.
Thanks
Laurentiu
Hi,
You can do this well with NTFS permission with Read right for everyone in Group so that everyone can read the database files (this will not make user to able to copy files/folder too) , and give write/modify permission to specific users who need to insert/update/delete records in your access database. Refer www.windowsecurity.com/articles/
HTH
Hemantgiri S. Goswami
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,
>
database schema for HelpDesk application
hello
i have to make helpdesk application for teh IT Department of my company,so if anyone can help by supporting me by database schema for HelpDesk application.
thank you for the help
i have to make helpdesk application for teh IT Department of my company,so if anyone can help by supporting me by database schema for HelpDesk application.
thank you for the help
You can use the Asp.net 2.0 built in profile as one of the tables for a small application and use Trigger with fake time stamp to write to the incident table. The time stamp you need should be fake because the current SQL Server time stamp is a derived data type used by SQL Server. Another option is to use the database, tables and constraints from the ISSUE Tracker starter kit. Try the links below for the fake time stamp trigger and download the Issue tracker starter kit. Hope this helps.
http://forums.asp.net/832746/showpost.aspx
http://www.aspfaq.com/show.asp?id=2448
Database schema comparison
Hello all. I am using Sql Compact Edition for a small standalone application, I create and build the initial database schema on initial startup. What I am looking for is a way to upgrade the software and on initial startup of the new version, I would like it to compare the existing database with a new schema and then update the database based on the difference. This way I can have a version that will update the database without me having to know what version it is to begin with. Is there a way to do this or am I asking too much?
Thank you,
Jim
This is something you will have to code yourself, however the key to doing this is to tap into the INFORMATION_SCHEMA views in SQL Compact Edition.
You can get metadata about all aspects of the schema of a SQL CE/SQL Mobile database using this view and then make determinations about old vs new schema as you compare two database versions. Books Online covers the INFORMATION_SCHEMA view and what it contains.
Regards,
Darren Shaffer
Sunday, March 11, 2012
Database Role & Application Roles
In my environment we have all database roles..
Where we can setup application roles and can it will be more secure then
database roles.. will it be easy to manage,,Hi,
Application Roles:This is the best method for controlling user activities
regardless of the application used to communicate
with SQL Server
With the use of application roles you can restrict the users the usage of
Enterprise manager and Query Analyzer.
Say for your application to run you need give INSERT/DELETE and UPDATE
previlages to a user, if you
gave those previlages to user then he can login to Query analyzer and do any
thing on tables.
To overcome these you can assign all the previlages to a app role and enable
the app role inside the application.
App role will get enabled only by providing the right password, which is
defined inside the application. So even
if the user login using query analyzer he cant do any thing.
Thanks
Hari
SQL Server, MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:5A9F6EEA-F56F-4598-AEDC-E17374F80BEE@.microsoft.com...
> In my environment we have all database roles..
> Where we can setup application roles and can it will be more secure then
> database roles.. will it be easy to manage,,
Where we can setup application roles and can it will be more secure then
database roles.. will it be easy to manage,,Hi,
Application Roles:This is the best method for controlling user activities
regardless of the application used to communicate
with SQL Server
With the use of application roles you can restrict the users the usage of
Enterprise manager and Query Analyzer.
Say for your application to run you need give INSERT/DELETE and UPDATE
previlages to a user, if you
gave those previlages to user then he can login to Query analyzer and do any
thing on tables.
To overcome these you can assign all the previlages to a app role and enable
the app role inside the application.
App role will get enabled only by providing the right password, which is
defined inside the application. So even
if the user login using query analyzer he cant do any thing.
Thanks
Hari
SQL Server, MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:5A9F6EEA-F56F-4598-AEDC-E17374F80BEE@.microsoft.com...
> In my environment we have all database roles..
> Where we can setup application roles and can it will be more secure then
> database roles.. will it be easy to manage,,
Database Role & Application
What is the basic difference between these roles..
I know as per books but how it can be used in real life. We have all roles
defined as database roles.. where we can use app roleDave,
here are some good articles:
http://www.databasejournal.com/feat...cle.php/3363521
http://www.sqlteam.com/item.asp?ItemID=864
http://vyaskn.tripod.com/sql_server...t_practices.htm
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1D3ECF9A-E3C5-403A-8F26-96733B267561@.microsoft.com...
> What is the basic difference between these roles..
> I know as per books but how it can be used in real life. We have all roles
> defined as database roles.. where we can use app role
I know as per books but how it can be used in real life. We have all roles
defined as database roles.. where we can use app roleDave,
here are some good articles:
http://www.databasejournal.com/feat...cle.php/3363521
http://www.sqlteam.com/item.asp?ItemID=864
http://vyaskn.tripod.com/sql_server...t_practices.htm
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1D3ECF9A-E3C5-403A-8F26-96733B267561@.microsoft.com...
> What is the basic difference between these roles..
> I know as per books but how it can be used in real life. We have all roles
> defined as database roles.. where we can use app role
DataBase reusability
Hello,
I am developing several database centric applications.
Each application has gotten one or more databases. I
discovered that some applications share some
functionalities around database tables. For example,
Application A will query the Members, Groups,
MemberPreferences tables in database A, and Application
B queries the Members, Groups, MemberPreferences tables
in database B. Those tables named Members, Groups, and
MemberPreferences have the identical table schema, and
have the same select/insert/update/delete stored
procedures, and triggers.
Is there any good solution to maintain those kind of
databases easily? Using the data import/export provided
by the SQL Enterprise Manager might help a bit. Any
other efficient alternatives to synchronize the table
schema and stored procedures among those databases?
W. JordanW,Jordan
Are there any reasons to have two databases if as you said they have the
same table's structure?
use master
create table t(c1 varchar(50)) insert t values('master')
go
create proc sp_test as select * from t
GO
use northwind
create table t(c1 varchar(50)) insert t values('northwind')
use pubs
create table t(c1 varchar(50)) insert t values('pubs')
use pubs
exec sp_test --returns 'master'
use master
exec sp_MS_marksystemobject sp_test
use pubs
exec sp_test --returns 'pubs'
use northwind
exec sp_test --returns 'northwind'
"W. Jordan" <wmjordan@.163.com.spam.proof> wrote in message
news:OUhzd6VHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am developing several database centric applications.
> Each application has gotten one or more databases. I
> discovered that some applications share some
> functionalities around database tables. For example,
> Application A will query the Members, Groups,
> MemberPreferences tables in database A, and Application
> B queries the Members, Groups, MemberPreferences tables
> in database B. Those tables named Members, Groups, and
> MemberPreferences have the identical table schema, and
> have the same select/insert/update/delete stored
> procedures, and triggers.
> Is there any good solution to maintain those kind of
> databases easily? Using the data import/export provided
> by the SQL Enterprise Manager might help a bit. Any
> other efficient alternatives to synchronize the table
> schema and stored procedures among those databases?
> W. Jordan
>|||check out DB Ghost (http://www.dbghost.com)
"W. Jordan" wrote:
> Hello,
> I am developing several database centric applications.
> Each application has gotten one or more databases. I
> discovered that some applications share some
> functionalities around database tables. For example,
> Application A will query the Members, Groups,
> MemberPreferences tables in database A, and Application
> B queries the Members, Groups, MemberPreferences tables
> in database B. Those tables named Members, Groups, and
> MemberPreferences have the identical table schema, and
> have the same select/insert/update/delete stored
> procedures, and triggers.
> Is there any good solution to maintain those kind of
> databases easily? Using the data import/export provided
> by the SQL Enterprise Manager might help a bit. Any
> other efficient alternatives to synchronize the table
> schema and stored procedures among those databases?
> W. Jordan
>
>|||Hello Uri,
Yes, the database items with similar structure are used by different
applications, although, which share some identical behaviors.
We are developing several applications on a single centric machine.
For portability on a development machine, I obviously can not have
those applications sharing one table in one database since the data
will get conflicted.
Using the master table is a trick for stored procedures. But, is it a
good practice to do so?
Best Regards,
W. Jordan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#RGY3LWHFHA.2936@.TK2MSFTNGP15.phx.gbl...
: W,Jordan
: Are there any reasons to have two databases if as you said they have the
: same table's structure?
:
: use master
: create table t(c1 varchar(50)) insert t values('master')
: go
: create proc sp_test as select * from t
: GO
: use northwind
: create table t(c1 varchar(50)) insert t values('northwind')
: use pubs
: create table t(c1 varchar(50)) insert t values('pubs')
: use pubs
: exec sp_test --returns 'master'
: use master
: exec sp_MS_marksystemobject sp_test
: use pubs
: exec sp_test --returns 'pubs'
: use northwind
: exec sp_test --returns 'northwind'
:
:
:
: "W. Jordan" <wmjordan@.163.com.spam.proof> wrote in message
: news:OUhzd6VHFHA.2936@.TK2MSFTNGP15.phx.gbl...
: > Hello,
: >
: > I am developing several database centric applications.
: > Each application has gotten one or more databases. I
: > discovered that some applications share some
: > functionalities around database tables. For example,
: > Application A will query the Members, Groups,
: > MemberPreferences tables in database A, and Application
: > B queries the Members, Groups, MemberPreferences tables
: > in database B. Those tables named Members, Groups, and
: > MemberPreferences have the identical table schema, and
: > have the same select/insert/update/delete stored
: > procedures, and triggers.
: >
: > Is there any good solution to maintain those kind of
: > databases easily? Using the data import/export provided
: > by the SQL Enterprise Manager might help a bit. Any
: > other efficient alternatives to synchronize the table
: > schema and stored procedures among those databases?
: >
: > W. Jordan
: >
: >
:
:|||Hello Mark,
DB Ghost seems to be able to solve my problem.
But I think that is not a thing we can afford.
Best Regards,
W. Jordan
"mark baekdal" <markbaekdal@.discussions.microsoft.com>
wrote in message
news:A9386D77-1460-40D7-AD10-E00438E096EC@.microsoft.com...
: check out DB Ghost (http://www.dbghost.com)
:
:|||Hello Welman.
the question could also be "Can you afford not to have it?". What ever you
decide thanks for looking.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Welman Jordan" wrote:
> Hello Mark,
> DB Ghost seems to be able to solve my problem.
> But I think that is not a thing we can afford.
> --
> Best Regards,
> W. Jordan
>
> "mark baekdal" <markbaekdal@.discussions.microsoft.com>
> wrote in message
> news:A9386D77-1460-40D7-AD10-E00438E096EC@.microsoft.com...
> : check out DB Ghost (http://www.dbghost.com)
> :
> :
>
>
I am developing several database centric applications.
Each application has gotten one or more databases. I
discovered that some applications share some
functionalities around database tables. For example,
Application A will query the Members, Groups,
MemberPreferences tables in database A, and Application
B queries the Members, Groups, MemberPreferences tables
in database B. Those tables named Members, Groups, and
MemberPreferences have the identical table schema, and
have the same select/insert/update/delete stored
procedures, and triggers.
Is there any good solution to maintain those kind of
databases easily? Using the data import/export provided
by the SQL Enterprise Manager might help a bit. Any
other efficient alternatives to synchronize the table
schema and stored procedures among those databases?
W. JordanW,Jordan
Are there any reasons to have two databases if as you said they have the
same table's structure?
use master
create table t(c1 varchar(50)) insert t values('master')
go
create proc sp_test as select * from t
GO
use northwind
create table t(c1 varchar(50)) insert t values('northwind')
use pubs
create table t(c1 varchar(50)) insert t values('pubs')
use pubs
exec sp_test --returns 'master'
use master
exec sp_MS_marksystemobject sp_test
use pubs
exec sp_test --returns 'pubs'
use northwind
exec sp_test --returns 'northwind'
"W. Jordan" <wmjordan@.163.com.spam.proof> wrote in message
news:OUhzd6VHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am developing several database centric applications.
> Each application has gotten one or more databases. I
> discovered that some applications share some
> functionalities around database tables. For example,
> Application A will query the Members, Groups,
> MemberPreferences tables in database A, and Application
> B queries the Members, Groups, MemberPreferences tables
> in database B. Those tables named Members, Groups, and
> MemberPreferences have the identical table schema, and
> have the same select/insert/update/delete stored
> procedures, and triggers.
> Is there any good solution to maintain those kind of
> databases easily? Using the data import/export provided
> by the SQL Enterprise Manager might help a bit. Any
> other efficient alternatives to synchronize the table
> schema and stored procedures among those databases?
> W. Jordan
>|||check out DB Ghost (http://www.dbghost.com)
"W. Jordan" wrote:
> Hello,
> I am developing several database centric applications.
> Each application has gotten one or more databases. I
> discovered that some applications share some
> functionalities around database tables. For example,
> Application A will query the Members, Groups,
> MemberPreferences tables in database A, and Application
> B queries the Members, Groups, MemberPreferences tables
> in database B. Those tables named Members, Groups, and
> MemberPreferences have the identical table schema, and
> have the same select/insert/update/delete stored
> procedures, and triggers.
> Is there any good solution to maintain those kind of
> databases easily? Using the data import/export provided
> by the SQL Enterprise Manager might help a bit. Any
> other efficient alternatives to synchronize the table
> schema and stored procedures among those databases?
> W. Jordan
>
>|||Hello Uri,
Yes, the database items with similar structure are used by different
applications, although, which share some identical behaviors.
We are developing several applications on a single centric machine.
For portability on a development machine, I obviously can not have
those applications sharing one table in one database since the data
will get conflicted.
Using the master table is a trick for stored procedures. But, is it a
good practice to do so?
Best Regards,
W. Jordan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#RGY3LWHFHA.2936@.TK2MSFTNGP15.phx.gbl...
: W,Jordan
: Are there any reasons to have two databases if as you said they have the
: same table's structure?
:
: use master
: create table t(c1 varchar(50)) insert t values('master')
: go
: create proc sp_test as select * from t
: GO
: use northwind
: create table t(c1 varchar(50)) insert t values('northwind')
: use pubs
: create table t(c1 varchar(50)) insert t values('pubs')
: use pubs
: exec sp_test --returns 'master'
: use master
: exec sp_MS_marksystemobject sp_test
: use pubs
: exec sp_test --returns 'pubs'
: use northwind
: exec sp_test --returns 'northwind'
:
:
:
: "W. Jordan" <wmjordan@.163.com.spam.proof> wrote in message
: news:OUhzd6VHFHA.2936@.TK2MSFTNGP15.phx.gbl...
: > Hello,
: >
: > I am developing several database centric applications.
: > Each application has gotten one or more databases. I
: > discovered that some applications share some
: > functionalities around database tables. For example,
: > Application A will query the Members, Groups,
: > MemberPreferences tables in database A, and Application
: > B queries the Members, Groups, MemberPreferences tables
: > in database B. Those tables named Members, Groups, and
: > MemberPreferences have the identical table schema, and
: > have the same select/insert/update/delete stored
: > procedures, and triggers.
: >
: > Is there any good solution to maintain those kind of
: > databases easily? Using the data import/export provided
: > by the SQL Enterprise Manager might help a bit. Any
: > other efficient alternatives to synchronize the table
: > schema and stored procedures among those databases?
: >
: > W. Jordan
: >
: >
:
:|||Hello Mark,
DB Ghost seems to be able to solve my problem.
But I think that is not a thing we can afford.
Best Regards,
W. Jordan
"mark baekdal" <markbaekdal@.discussions.microsoft.com>
wrote in message
news:A9386D77-1460-40D7-AD10-E00438E096EC@.microsoft.com...
: check out DB Ghost (http://www.dbghost.com)
:
:|||Hello Welman.
the question could also be "Can you afford not to have it?". What ever you
decide thanks for looking.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Welman Jordan" wrote:
> Hello Mark,
> DB Ghost seems to be able to solve my problem.
> But I think that is not a thing we can afford.
> --
> Best Regards,
> W. Jordan
>
> "mark baekdal" <markbaekdal@.discussions.microsoft.com>
> wrote in message
> news:A9386D77-1460-40D7-AD10-E00438E096EC@.microsoft.com...
> : check out DB Ghost (http://www.dbghost.com)
> :
> :
>
>
Saturday, February 25, 2012
Database reorganization
Database reorganization
We use SQL 2000 and our application database is around 2 Gb.
A third party consultant (Microsoft Certified) counseled us to perform
database reorganization, at least once each year.
The way he recommend us to execute the database reorganization is the
following:
1) backup the app database
2) delete the app database
3) restore the app database with another name oldDB
4) create a new app database
5) export all objects and data from oldDB to app DB by DTS
The questions I would like to ask you are:
Is that really necessary?
Is that really effective?
Is there another way to perform database reorganization?
Thanks,
MarcosWhy? Looks very strange.
You should have proper DataBase Disaster Recovery Strategy , means backup
all user databases as well as system databases and many many other things
<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Not sure why you would go through all that. Personally I would never delete
my database to restore it back again. What happens if your restore does not
work? If you really wanted to do that I would do 2 and 3 the other way round
that way you have not lost anything if it does not go to plan.
My database is 288GB and I run a reorg job on it a few times a year
(although I am planning to run a regular job), by using one of the
maintenance plans. I would suggest you take a look at these before you start
deleting databases. Having said all that though unless your database is
growing rapidly you will probably see no noticable improvement, mine is
growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
after this database it was 150GB and it had never had a reorg job run on it,
performance was fine, but when I first ran it there was a very noticable
difference. I'm no SQL guru but this way works for me and it lets me sleep
easy, when we do a restore I''m usually at work all night.
Gav|||mripplinger74@.yahoo.com.br wrote:
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> mripplinger74@.yahoo.com.br wrote:
> Gosh, I'm also Microsoft certified, and I'm going to tell you that
> consultant is all wet. Guess you have to wonder how useful those
> certifications are, huh?
> A 2GB database is NOTHING - I've been working with 100+GB database for
> several years. There is no need to do any "reorganization". Keep
> your indexes defragged, keep the database files properly sized, don't
> shrink your database or log files, and be vigilant about making sure
> the queries hitting your database are written properly and efficiently.
>
..well...nobody said that this consultant was certified on anything
related to SQL server... ;-)
Regards
Steen Schlter Persson (...who isn't certified in anything but just
knows something about SQL server...)
Databaseadministrator / Systemadministrator|||Uri.
We have a Disaster Recovery Plan but my question here is how to
reorganize database trying to improve his performance.
Thanks,
Marcos
Uri Dimant escreveu:
> Why? Looks very strange.
> You should have proper DataBase Disaster Recovery Strategy , means backup
> all user databases as well as system databases and many many other things
>|||Gav,
Just to be sure, when you say "reorg job" you are talking about use
a maintenance plan with the option reorganize data and index page
selected, right?
Do you believe it is enough? Even in the tables where the PK is not a
clustered index?
Thanks,
Marcos
Gav escreveu:
> Not sure why you would go through all that. Personally I would never delet
e
> my database to restore it back again. What happens if your restore does no
t
> work? If you really wanted to do that I would do 2 and 3 the other way rou
nd
> that way you have not lost anything if it does not go to plan.
> My database is 288GB and I run a reorg job on it a few times a year
> (although I am planning to run a regular job), by using one of the
> maintenance plans. I would suggest you take a look at these before you sta
rt
> deleting databases. Having said all that though unless your database is
> growing rapidly you will probably see no noticable improvement, mine is
> growing at a rate of 1.5 to 2GB a week. Incidentally when I started lookin
g
> after this database it was 150GB and it had never had a reorg job run on i
t,
> performance was fine, but when I first ran it there was a very noticable
> difference. I'm no SQL guru but this way works for me and it lets me sleep
> easy, when we do a restore I''m usually at work all night.
> Gav|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152202785.006599.122210@.75g2000cwc.googlegroups.com...
> Gav,
> Just to be sure, when you say "reorg job" you are talking about use
> a maintenance plan with the option reorganize data and index page
> selected, right?
> Do you believe it is enough? Even in the tables where the PK is not a
> clustered index?
>
> Thanks,
> Marcos
Yes thats what I am talking about. My database contains approx 38,000
tables, the majority of these have clustered indexes. I have no idea how
many don't but the ones I have come accross so far are fairly insignificant
anyway. If you know of some tables without clustered indexes in your
database I would run a DBCC SHOWCONTIG before and after running a reorg job
and see what the effect is.
I plot database stats weekly and monthly, when the responce times start to
climb I run a reorg job and the responce times fall again. So from my point
of view it works fine.
Gav|||Tracy,
I agree that our database is not big, but neither our servers are. In
someplace the database is running in a workstation or in a laptop,
that's why I do care about it.
We have a maintenance plan to reorganize data and index page and we
schedule it to run every weekend, but I questioned if is it enough?
What the procedure detailed above give me more than this maintenance
plan?
Regards,
Marcos|||Sorry, I forgot to mention the guy is MCDBA :D
We use SQL 2000 and our application database is around 2 Gb.
A third party consultant (Microsoft Certified) counseled us to perform
database reorganization, at least once each year.
The way he recommend us to execute the database reorganization is the
following:
1) backup the app database
2) delete the app database
3) restore the app database with another name oldDB
4) create a new app database
5) export all objects and data from oldDB to app DB by DTS
The questions I would like to ask you are:
Is that really necessary?
Is that really effective?
Is there another way to perform database reorganization?
Thanks,
MarcosWhy? Looks very strange.
You should have proper DataBase Disaster Recovery Strategy , means backup
all user databases as well as system databases and many many other things
<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Not sure why you would go through all that. Personally I would never delete
my database to restore it back again. What happens if your restore does not
work? If you really wanted to do that I would do 2 and 3 the other way round
that way you have not lost anything if it does not go to plan.
My database is 288GB and I run a reorg job on it a few times a year
(although I am planning to run a regular job), by using one of the
maintenance plans. I would suggest you take a look at these before you start
deleting databases. Having said all that though unless your database is
growing rapidly you will probably see no noticable improvement, mine is
growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
after this database it was 150GB and it had never had a reorg job run on it,
performance was fine, but when I first ran it there was a very noticable
difference. I'm no SQL guru but this way works for me and it lets me sleep
easy, when we do a restore I''m usually at work all night.
Gav|||mripplinger74@.yahoo.com.br wrote:
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> mripplinger74@.yahoo.com.br wrote:
> Gosh, I'm also Microsoft certified, and I'm going to tell you that
> consultant is all wet. Guess you have to wonder how useful those
> certifications are, huh?
> A 2GB database is NOTHING - I've been working with 100+GB database for
> several years. There is no need to do any "reorganization". Keep
> your indexes defragged, keep the database files properly sized, don't
> shrink your database or log files, and be vigilant about making sure
> the queries hitting your database are written properly and efficiently.
>
..well...nobody said that this consultant was certified on anything
related to SQL server... ;-)
Regards
Steen Schlter Persson (...who isn't certified in anything but just
knows something about SQL server...)
Databaseadministrator / Systemadministrator|||Uri.
We have a Disaster Recovery Plan but my question here is how to
reorganize database trying to improve his performance.
Thanks,
Marcos
Uri Dimant escreveu:
> Why? Looks very strange.
> You should have proper DataBase Disaster Recovery Strategy , means backup
> all user databases as well as system databases and many many other things
>|||Gav,
Just to be sure, when you say "reorg job" you are talking about use
a maintenance plan with the option reorganize data and index page
selected, right?
Do you believe it is enough? Even in the tables where the PK is not a
clustered index?
Thanks,
Marcos
Gav escreveu:
> Not sure why you would go through all that. Personally I would never delet
e
> my database to restore it back again. What happens if your restore does no
t
> work? If you really wanted to do that I would do 2 and 3 the other way rou
nd
> that way you have not lost anything if it does not go to plan.
> My database is 288GB and I run a reorg job on it a few times a year
> (although I am planning to run a regular job), by using one of the
> maintenance plans. I would suggest you take a look at these before you sta
rt
> deleting databases. Having said all that though unless your database is
> growing rapidly you will probably see no noticable improvement, mine is
> growing at a rate of 1.5 to 2GB a week. Incidentally when I started lookin
g
> after this database it was 150GB and it had never had a reorg job run on i
t,
> performance was fine, but when I first ran it there was a very noticable
> difference. I'm no SQL guru but this way works for me and it lets me sleep
> easy, when we do a restore I''m usually at work all night.
> Gav|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152202785.006599.122210@.75g2000cwc.googlegroups.com...
> Gav,
> Just to be sure, when you say "reorg job" you are talking about use
> a maintenance plan with the option reorganize data and index page
> selected, right?
> Do you believe it is enough? Even in the tables where the PK is not a
> clustered index?
>
> Thanks,
> Marcos
Yes thats what I am talking about. My database contains approx 38,000
tables, the majority of these have clustered indexes. I have no idea how
many don't but the ones I have come accross so far are fairly insignificant
anyway. If you know of some tables without clustered indexes in your
database I would run a DBCC SHOWCONTIG before and after running a reorg job
and see what the effect is.
I plot database stats weekly and monthly, when the responce times start to
climb I run a reorg job and the responce times fall again. So from my point
of view it works fine.
Gav|||Tracy,
I agree that our database is not big, but neither our servers are. In
someplace the database is running in a workstation or in a laptop,
that's why I do care about it.
We have a maintenance plan to reorganize data and index page and we
schedule it to run every weekend, but I questioned if is it enough?
What the procedure detailed above give me more than this maintenance
plan?
Regards,
Marcos|||Sorry, I forgot to mention the guy is MCDBA :D
Database reorganization
Database reorganization
We use SQL 2000 and our application database is around 2 Gb.
A third party consultant (Microsoft Certified) counseled us to perform
database reorganization, at least once each year.
The way he recommend us to execute the database reorganization is the
following:
1) backup the app database
2) delete the app database
3) restore the app database with another name oldDB
4) create a new app database
5) export all objects and data from oldDB to app DB by DTS
The questions I would like to ask you are:
Is that really necessary?
Is that really effective?
Is there another way to perform database reorganization?
Thanks,
MarcosWhy? Looks very strange.
You should have proper DataBase Disaster Recovery Strategy , means backup
all user databases as well as system databases and many many other things
<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Not sure why you would go through all that. Personally I would never delete
my database to restore it back again. What happens if your restore does not
work? If you really wanted to do that I would do 2 and 3 the other way round
that way you have not lost anything if it does not go to plan.
My database is 288GB and I run a reorg job on it a few times a year
(although I am planning to run a regular job), by using one of the
maintenance plans. I would suggest you take a look at these before you start
deleting databases. Having said all that though unless your database is
growing rapidly you will probably see no noticable improvement, mine is
growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
after this database it was 150GB and it had never had a reorg job run on it,
performance was fine, but when I first ran it there was a very noticable
difference. I'm no SQL guru but this way works for me and it lets me sleep
easy, when we do a restore I''m usually at work all night.
Gav|||mripplinger74@.yahoo.com.br wrote:
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--060703000702060505050207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Tracy McKibben wrote:
> mripplinger74@.yahoo.com.br wrote:
>> Database reorganization
>> We use SQL 2000 and our application database is around 2 Gb.
>> A third party consultant (Microsoft Certified) counseled us to perform
>> database reorganization, at least once each year.
>> The way he recommend us to execute the database reorganization is the
>> following:
>> 1) backup the app database
>> 2) delete the app database
>> 3) restore the app database with another name oldDB
>> 4) create a new app database
>> 5) export all objects and data from oldDB to app DB by DTS
>> The questions I would like to ask you are:
>> Is that really necessary?
>> Is that really effective?
>> Is there another way to perform database reorganization?
>>
>> Thanks,
>> Marcos
> Gosh, I'm also Microsoft certified, and I'm going to tell you that
> consultant is all wet. Guess you have to wonder how useful those
> certifications are, huh?
> A 2GB database is NOTHING - I've been working with 100+GB database for
> several years. There is no need to do any "reorganization". Keep
> your indexes defragged, keep the database files properly sized, don't
> shrink your database or log files, and be vigilant about making sure
> the queries hitting your database are written properly and efficiently.
>
..well...nobody said that this consultant was certified on anything
related to SQL server... ;-)
Regards
Steen Schlüter Persson (...who isn't certified in anything but just
knows something about SQL server...)
Databaseadministrator / Systemadministrator
--060703000702060505050207
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tracy McKibben wrote:
<blockquote cite="miduJIjCXGoGHA.4432@.TK2MSFTNGP05.phx.gbl" type="cite"><a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:mripplinger74@.yahoo.com.br">mripplinger74@.yahoo.com.br</a>
wrote:
<br>
<blockquote type="cite">Database reorganization
<br>
<br>
We use SQL 2000 and our application database is around 2 Gb.
<br>
<br>
A third party consultant (Microsoft Certified) counseled us to perform
<br>
database reorganization, at least once each year.
<br>
<br>
The way he recommend us to execute the database reorganization is the
<br>
following:
<br>
<br>
1) backup the app database
<br>
2) delete the app database
<br>
3) restore the app database with another name oldDB
<br>
4) create a new app database
<br>
5) export all objects and data from oldDB to app DB by DTS
<br>
<br>
The questions I would like to ask you are:
<br>
<br>
Is that really necessary?
<br>
<br>
Is that really effective?
<br>
<br>
Is there another way to perform database reorganization?
<br>
<br>
<br>
Thanks,
<br>
<br>
Marcos
<br>
<br>
</blockquote>
<br>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
<br>
<br>
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
<br>
<br>
<br>
</blockquote>
<font size="-1"><font face="Arial">..well...nobody said that this
consultant was certified on anything related to SQL server...<span
class="moz-smiley-s3"><span> ;-) </span></span><br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson (...who isn't certified in anything but just
knows something about SQL server...)<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--060703000702060505050207--|||Uri.
We have a Disaster Recovery Plan but my question here is how to
reorganize database trying to improve his performance.
Thanks,
Marcos
Uri Dimant escreveu:
> Why? Looks very strange.
> You should have proper DataBase Disaster Recovery Strategy , means backup
> all user databases as well as system databases and many many other things
>|||Gav,
Just to be sure, when you say "reorg job" you are talking about use
a maintenance plan with the option reorganize data and index page
selected, right?
Do you believe it is enough? Even in the tables where the PK is not a
clustered index?
Thanks,
Marcos
Gav escreveu:
> Not sure why you would go through all that. Personally I would never delete
> my database to restore it back again. What happens if your restore does not
> work? If you really wanted to do that I would do 2 and 3 the other way round
> that way you have not lost anything if it does not go to plan.
> My database is 288GB and I run a reorg job on it a few times a year
> (although I am planning to run a regular job), by using one of the
> maintenance plans. I would suggest you take a look at these before you start
> deleting databases. Having said all that though unless your database is
> growing rapidly you will probably see no noticable improvement, mine is
> growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
> after this database it was 150GB and it had never had a reorg job run on it,
> performance was fine, but when I first ran it there was a very noticable
> difference. I'm no SQL guru but this way works for me and it lets me sleep
> easy, when we do a restore I''m usually at work all night.
> Gav|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152202785.006599.122210@.75g2000cwc.googlegroups.com...
> Gav,
> Just to be sure, when you say "reorg job" you are talking about use
> a maintenance plan with the option reorganize data and index page
> selected, right?
> Do you believe it is enough? Even in the tables where the PK is not a
> clustered index?
>
> Thanks,
> Marcos
Yes thats what I am talking about. My database contains approx 38,000
tables, the majority of these have clustered indexes. I have no idea how
many don't but the ones I have come accross so far are fairly insignificant
anyway. If you know of some tables without clustered indexes in your
database I would run a DBCC SHOWCONTIG before and after running a reorg job
and see what the effect is.
I plot database stats weekly and monthly, when the responce times start to
climb I run a reorg job and the responce times fall again. So from my point
of view it works fine.
Gav|||Tracy,
I agree that our database is not big, but neither our servers are. In
someplace the database is running in a workstation or in a laptop,
that's why I do care about it.
We have a maintenance plan to reorganize data and index page and we
schedule it to run every weekend, but I questioned if is it enough?
What the procedure detailed above give me more than this maintenance
plan?
Regards,
Marcos|||Sorry, I forgot to mention the guy is MCDBA :D|||mripplinger74@.yahoo.com.br wrote:
> Tracy,
> I agree that our database is not big, but neither our servers are. In
> someplace the database is running in a workstation or in a laptop,
> that's why I do care about it.
> We have a maintenance plan to reorganize data and index page and we
> schedule it to run every weekend, but I questioned if is it enough?
> What the procedure detailed above give me more than this maintenance
> plan?
>
> Regards,
> Marcos
>
There are basically four things that will cause a database to "slow down":
1. Bad queries - poorly written code, cursors, lack of supporting
indexes, these things are by far the most common reason for a database
to suddenly slow down.
2. Index fragmentation - as indexes grow due to new data being added to
tables, they suffer from page splits. These page splits lead to
fragmented indexes, which hinder performance because the query engine
has to jump around looking for the next index page. DBCC DBREINDEX will
rebuild those indexes, putting them back together into contiguous blocks.
3. Disk fragmentation - repeated shrinking/growing of a database or
transaction log file will cause it to become fragmented on disk. Just
as index fragmentation hurts performance, so do physical fragmentation.
This can be prevented by properly sizing the data files so that they
don't need to grow often, and NEVER shrink them.
4. Outdated statistics - when decided what indexes to use, the query
engine looks at database statistics to get a sampling of how data is
distributed in the tables. If these stats are not current, then indexes
can't be fully utilized, leading to table or index scans, which are the
slowest form of data access. Generally, turning on the auto-stats
options on your databases will be enough to prevent this, but
inserting/deleting large amounts of data may require manually updating
the stats.
That's it. If you make it a point to stay on top of those four things,
you should not see a performance degradation. You can easily manage
these without a maintenance plan. There is nothing in those wizards
that will make up for insufficient hardware, they simply provide a
point-and-click front-end for managing the 4 tasks above (also backups
and CHECKDB). Learn how to manage these things without the aid of the
wizards, you'll learn alot, and be better equipped to deal with problems
when they do arise.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Fri, 07 Jul 2006 08:26:25 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>There are basically four things that will cause a database to "slow down":
2gb database might "speed up" if you run it on machines with at least
that much RAM, which maybe you (OP) haven't been doing?
Josh
We use SQL 2000 and our application database is around 2 Gb.
A third party consultant (Microsoft Certified) counseled us to perform
database reorganization, at least once each year.
The way he recommend us to execute the database reorganization is the
following:
1) backup the app database
2) delete the app database
3) restore the app database with another name oldDB
4) create a new app database
5) export all objects and data from oldDB to app DB by DTS
The questions I would like to ask you are:
Is that really necessary?
Is that really effective?
Is there another way to perform database reorganization?
Thanks,
MarcosWhy? Looks very strange.
You should have proper DataBase Disaster Recovery Strategy , means backup
all user databases as well as system databases and many many other things
<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Not sure why you would go through all that. Personally I would never delete
my database to restore it back again. What happens if your restore does not
work? If you really wanted to do that I would do 2 and 3 the other way round
that way you have not lost anything if it does not go to plan.
My database is 288GB and I run a reorg job on it a few times a year
(although I am planning to run a regular job), by using one of the
maintenance plans. I would suggest you take a look at these before you start
deleting databases. Having said all that though unless your database is
growing rapidly you will probably see no noticable improvement, mine is
growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
after this database it was 150GB and it had never had a reorg job run on it,
performance was fine, but when I first ran it there was a very noticable
difference. I'm no SQL guru but this way works for me and it lets me sleep
easy, when we do a restore I''m usually at work all night.
Gav|||mripplinger74@.yahoo.com.br wrote:
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--060703000702060505050207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Tracy McKibben wrote:
> mripplinger74@.yahoo.com.br wrote:
>> Database reorganization
>> We use SQL 2000 and our application database is around 2 Gb.
>> A third party consultant (Microsoft Certified) counseled us to perform
>> database reorganization, at least once each year.
>> The way he recommend us to execute the database reorganization is the
>> following:
>> 1) backup the app database
>> 2) delete the app database
>> 3) restore the app database with another name oldDB
>> 4) create a new app database
>> 5) export all objects and data from oldDB to app DB by DTS
>> The questions I would like to ask you are:
>> Is that really necessary?
>> Is that really effective?
>> Is there another way to perform database reorganization?
>>
>> Thanks,
>> Marcos
> Gosh, I'm also Microsoft certified, and I'm going to tell you that
> consultant is all wet. Guess you have to wonder how useful those
> certifications are, huh?
> A 2GB database is NOTHING - I've been working with 100+GB database for
> several years. There is no need to do any "reorganization". Keep
> your indexes defragged, keep the database files properly sized, don't
> shrink your database or log files, and be vigilant about making sure
> the queries hitting your database are written properly and efficiently.
>
..well...nobody said that this consultant was certified on anything
related to SQL server... ;-)
Regards
Steen Schlüter Persson (...who isn't certified in anything but just
knows something about SQL server...)
Databaseadministrator / Systemadministrator
--060703000702060505050207
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tracy McKibben wrote:
<blockquote cite="miduJIjCXGoGHA.4432@.TK2MSFTNGP05.phx.gbl" type="cite"><a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:mripplinger74@.yahoo.com.br">mripplinger74@.yahoo.com.br</a>
wrote:
<br>
<blockquote type="cite">Database reorganization
<br>
<br>
We use SQL 2000 and our application database is around 2 Gb.
<br>
<br>
A third party consultant (Microsoft Certified) counseled us to perform
<br>
database reorganization, at least once each year.
<br>
<br>
The way he recommend us to execute the database reorganization is the
<br>
following:
<br>
<br>
1) backup the app database
<br>
2) delete the app database
<br>
3) restore the app database with another name oldDB
<br>
4) create a new app database
<br>
5) export all objects and data from oldDB to app DB by DTS
<br>
<br>
The questions I would like to ask you are:
<br>
<br>
Is that really necessary?
<br>
<br>
Is that really effective?
<br>
<br>
Is there another way to perform database reorganization?
<br>
<br>
<br>
Thanks,
<br>
<br>
Marcos
<br>
<br>
</blockquote>
<br>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
<br>
<br>
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
<br>
<br>
<br>
</blockquote>
<font size="-1"><font face="Arial">..well...nobody said that this
consultant was certified on anything related to SQL server...<span
class="moz-smiley-s3"><span> ;-) </span></span><br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson (...who isn't certified in anything but just
knows something about SQL server...)<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--060703000702060505050207--|||Uri.
We have a Disaster Recovery Plan but my question here is how to
reorganize database trying to improve his performance.
Thanks,
Marcos
Uri Dimant escreveu:
> Why? Looks very strange.
> You should have proper DataBase Disaster Recovery Strategy , means backup
> all user databases as well as system databases and many many other things
>|||Gav,
Just to be sure, when you say "reorg job" you are talking about use
a maintenance plan with the option reorganize data and index page
selected, right?
Do you believe it is enough? Even in the tables where the PK is not a
clustered index?
Thanks,
Marcos
Gav escreveu:
> Not sure why you would go through all that. Personally I would never delete
> my database to restore it back again. What happens if your restore does not
> work? If you really wanted to do that I would do 2 and 3 the other way round
> that way you have not lost anything if it does not go to plan.
> My database is 288GB and I run a reorg job on it a few times a year
> (although I am planning to run a regular job), by using one of the
> maintenance plans. I would suggest you take a look at these before you start
> deleting databases. Having said all that though unless your database is
> growing rapidly you will probably see no noticable improvement, mine is
> growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
> after this database it was 150GB and it had never had a reorg job run on it,
> performance was fine, but when I first ran it there was a very noticable
> difference. I'm no SQL guru but this way works for me and it lets me sleep
> easy, when we do a restore I''m usually at work all night.
> Gav|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152202785.006599.122210@.75g2000cwc.googlegroups.com...
> Gav,
> Just to be sure, when you say "reorg job" you are talking about use
> a maintenance plan with the option reorganize data and index page
> selected, right?
> Do you believe it is enough? Even in the tables where the PK is not a
> clustered index?
>
> Thanks,
> Marcos
Yes thats what I am talking about. My database contains approx 38,000
tables, the majority of these have clustered indexes. I have no idea how
many don't but the ones I have come accross so far are fairly insignificant
anyway. If you know of some tables without clustered indexes in your
database I would run a DBCC SHOWCONTIG before and after running a reorg job
and see what the effect is.
I plot database stats weekly and monthly, when the responce times start to
climb I run a reorg job and the responce times fall again. So from my point
of view it works fine.
Gav|||Tracy,
I agree that our database is not big, but neither our servers are. In
someplace the database is running in a workstation or in a laptop,
that's why I do care about it.
We have a maintenance plan to reorganize data and index page and we
schedule it to run every weekend, but I questioned if is it enough?
What the procedure detailed above give me more than this maintenance
plan?
Regards,
Marcos|||Sorry, I forgot to mention the guy is MCDBA :D|||mripplinger74@.yahoo.com.br wrote:
> Tracy,
> I agree that our database is not big, but neither our servers are. In
> someplace the database is running in a workstation or in a laptop,
> that's why I do care about it.
> We have a maintenance plan to reorganize data and index page and we
> schedule it to run every weekend, but I questioned if is it enough?
> What the procedure detailed above give me more than this maintenance
> plan?
>
> Regards,
> Marcos
>
There are basically four things that will cause a database to "slow down":
1. Bad queries - poorly written code, cursors, lack of supporting
indexes, these things are by far the most common reason for a database
to suddenly slow down.
2. Index fragmentation - as indexes grow due to new data being added to
tables, they suffer from page splits. These page splits lead to
fragmented indexes, which hinder performance because the query engine
has to jump around looking for the next index page. DBCC DBREINDEX will
rebuild those indexes, putting them back together into contiguous blocks.
3. Disk fragmentation - repeated shrinking/growing of a database or
transaction log file will cause it to become fragmented on disk. Just
as index fragmentation hurts performance, so do physical fragmentation.
This can be prevented by properly sizing the data files so that they
don't need to grow often, and NEVER shrink them.
4. Outdated statistics - when decided what indexes to use, the query
engine looks at database statistics to get a sampling of how data is
distributed in the tables. If these stats are not current, then indexes
can't be fully utilized, leading to table or index scans, which are the
slowest form of data access. Generally, turning on the auto-stats
options on your databases will be enough to prevent this, but
inserting/deleting large amounts of data may require manually updating
the stats.
That's it. If you make it a point to stay on top of those four things,
you should not see a performance degradation. You can easily manage
these without a maintenance plan. There is nothing in those wizards
that will make up for insufficient hardware, they simply provide a
point-and-click front-end for managing the 4 tasks above (also backups
and CHECKDB). Learn how to manage these things without the aid of the
wizards, you'll learn alot, and be better equipped to deal with problems
when they do arise.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Fri, 07 Jul 2006 08:26:25 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>There are basically four things that will cause a database to "slow down":
2gb database might "speed up" if you run it on machines with at least
that much RAM, which maybe you (OP) haven't been doing?
Josh
Subscribe to:
Posts (Atom)