Hi
This is a long post, but all advice appreciated.
I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
2 x quad core processors recently. It has a p800 RAID controller with
2 x Storageworks MSA 50 disk arrays.
It's used as a reporting server during the day and as a transaction
processing server at night. Only a handful of users use it to run
reports but they are heavy duty reports!
Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
146 GB disks in RAID 5 for everything else. Not ideal, but we were
pushed for time and needed the disk space.
Recently I reconfigured the disks by buying the new p800 controller
and the 2 storage arrays plus a bunch of disks.
The config below uses all but 8 disk slots.
This is how I had it. The Req. column is the space required:
OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
- After moving data around and restarting sql, tempdb shrunk to a
fraction of its size and hasn't grown much so it's nearer 16 GB now
than 50 GB.
So far, so good. Bit of a no brainer. Now for the user dbs and logs:
All log files including tempdb log
- 2 x 72 GB 15k rpm, raid 1, 46% free space
- Not ideal having the logs on one array but a budgetary
consideration.
Financial History mdf
- 4 x 146 GB 10k, raid 10, 58% free
- this db gets written to each night. This seems a good config to me,
though I've done no performance monitoring.
- the fact that its log file is on a raid 1 array with all other log
files may be an issue.
Reporting mdf
- 2 x 146 GB 10k, raid 1, 49% free
- as the name implies, this is a reporting database. It's created
from some of the other databases and de-normalised.
- the performance of this db is dire. Performance monitor today
showed the RAID 1 array is being hammered while CPU does nothing
much. This is essentially what happened before the upgrade. I
figured that the load needs spreading over more disks. I'm
considering buying an additional disk and creating a RAID 5 array.
Adhoc Reporting mdf
- 4 x 146 GB 10k, raid 10, 57% free
- Another reporting database used by a different group of people.
This one, like tempdb, shrunk hugely when I moved it to a different
array. It used to be 126 GB but is probably half that now.
- The main person who uses this hasn't commented that performance has
improved, but we've not generated any performance stats to see what's
going on.
UserDb1 mdf
- 2 x 146 GB 10k, raid 1, 61% free
- this is a copy of a userDb that resides on another server.
- it's backed up and restored to this reporting server each night in
order to build the reporting data. The data is also available for use
in reports during the day.
- it's not written to, but I went for raid 1 because it's around 50 GB
and restored from the network nightly. The restore times have got a
lot faster since it was moved off the single big raid 5 array.
UserDb2, UserDb3, etc mdfs
- 2 x 72 GB 15k, raid 1, 67% free
- this is a copy of various smaller userDbs that reside on other
servers.
- again, these dbs are backed up and restored to this reporting server
each night.
- a couple of very small ones (1 GB or less) are written to but the
larger ones (between 4 and 9 GB) aren't.
So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
7 free slots.
Priority number 1 is to improve the performance of the Reporting db.
Today I moved the reporting mdf to the same array as the recently
shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
I see this as a short term measure.
I have, as I've said, 8 slots free. I can justify the purchase of 8
more disks if I can more or less guarantee significant performance
benefits.
Would I be better off giving 3 of the db logs and tempdb their very
own raid 1 arrays? The log files are small and this strikes me as
rather wasteful.
How about going back to the future so to speak and opting for a raid 5
array with say 6 disks for the two reporting databases?
I could go for a RAID 10 array for the two reporting databases with
say 8 disks in total. Might this be a better option than having 2 x
RAID 10 arrays of 4 disks each because the data will be striped over
more disks?
Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
about 70 GB in total) makes sense? If a db is only read, does the log
file need to be on a separate array? As I said earlier, the reason I
didn't go down this track is that these dbs are restored from backup
nightly so hence I went for raid 10. The thing is, the restore is
over the network so presumably that will be the bottleneck?You are right - LONG post. Perhaps you would be . . . no, let me rephrase
that. You will DEFINITELY be better served by getting an expert to give you
a few hours to perhaps a few days of his/her time to help you determine the
optimal configuration for this system.
I will make one general comment. When it comes to I/O the number of
spindles is HUGELY important. For example, IIRC you have just ONE active
spindle (2 drives RAID1) serving up your tempdb. This will almost certainly
be a bottleneck, especially during reporting periods when hashes and sorts
and work tables come into play for the reporting queries. I just got called
into a client where they had partitioned a new SAN all to heck and back and
it was a total DOG. They tried to set things up without expert DBA advice
and they did a bunch of other things wrong too, like not using appropriate
RAID set stripe sizes, setting controller cache ratios optimally, sector
aligning the partitions, setting format size, etc, etc. Please listen to my
advice above - or you will probably be wasting a lot of money for suboptimal
performance.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net|||I would put all of the data files for the reporting databases on raid 10
with the most spindles possible. Before you go out and fill the other slots,
I would make sure the indexing is tuned for the reports. Add a step to do
this with your nightly restore. Also what is memory\Page Life expectancy
looking like? Having the pages in memory is going to be fast than whatever
disk configuration you come up with. Lastly, it will be best to test to find
the optimal setting based on your IO patterns and hardware but I would start
off with a 64K stripe size in the ACU, 128 diskpart offset and 64k nt
allocation unit.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?|||Quad-core and SQL 2000'
It is likely your problem is too many competing databases for the 2 GB of
available memory. Monitor the page life expectancy and cache hit ratio.
First thought would be to spend any future money on 64 bit Windows and SQL
2005 then max out your RAM. The less you have to go to the disk for data,
the less spindles you will need...
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?
Showing posts with label advice. Show all posts
Showing posts with label advice. Show all posts
Wednesday, March 28, 2012
I/O performance tuning
Hi
This is a long post, but all advice appreciated.
I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
2 x quad core processors recently. It has a p800 RAID controller with
2 x Storageworks MSA 50 disk arrays.
It's used as a reporting server during the day and as a transaction
processing server at night. Only a handful of users use it to run
reports but they are heavy duty reports!
Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
146 GB disks in RAID 5 for everything else. Not ideal, but we were
pushed for time and needed the disk space.
Recently I reconfigured the disks by buying the new p800 controller
and the 2 storage arrays plus a bunch of disks.
The config below uses all but 8 disk slots.
This is how I had it. The Req. column is the space required:
OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
- After moving data around and restarting sql, tempdb shrunk to a
fraction of its size and hasn't grown much so it's nearer 16 GB now
than 50 GB.
So far, so good. Bit of a no brainer. Now for the user dbs and logs:
All log files including tempdb log
- 2 x 72 GB 15k rpm, raid 1, 46% free space
- Not ideal having the logs on one array but a budgetary
consideration.
Financial History mdf
- 4 x 146 GB 10k, raid 10, 58% free
- this db gets written to each night. This seems a good config to me,
though I've done no performance monitoring.
- the fact that its log file is on a raid 1 array with all other log
files may be an issue.
Reporting mdf
- 2 x 146 GB 10k, raid 1, 49% free
- as the name implies, this is a reporting database. It's created
from some of the other databases and de-normalised.
- the performance of this db is dire. Performance monitor today
showed the RAID 1 array is being hammered while CPU does nothing
much. This is essentially what happened before the upgrade. I
figured that the load needs spreading over more disks. I'm
considering buying an additional disk and creating a RAID 5 array.
Adhoc Reporting mdf
- 4 x 146 GB 10k, raid 10, 57% free
- Another reporting database used by a different group of people.
This one, like tempdb, shrunk hugely when I moved it to a different
array. It used to be 126 GB but is probably half that now.
- The main person who uses this hasn't commented that performance has
improved, but we've not generated any performance stats to see what's
going on.
UserDb1 mdf
- 2 x 146 GB 10k, raid 1, 61% free
- this is a copy of a userDb that resides on another server.
- it's backed up and restored to this reporting server each night in
order to build the reporting data. The data is also available for use
in reports during the day.
- it's not written to, but I went for raid 1 because it's around 50 GB
and restored from the network nightly. The restore times have got a
lot faster since it was moved off the single big raid 5 array.
UserDb2, UserDb3, etc mdfs
- 2 x 72 GB 15k, raid 1, 67% free
- this is a copy of various smaller userDbs that reside on other
servers.
- again, these dbs are backed up and restored to this reporting server
each night.
- a couple of very small ones (1 GB or less) are written to but the
larger ones (between 4 and 9 GB) aren't.
So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
7 free slots.
Priority number 1 is to improve the performance of the Reporting db.
Today I moved the reporting mdf to the same array as the recently
shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
I see this as a short term measure.
I have, as I've said, 8 slots free. I can justify the purchase of 8
more disks if I can more or less guarantee significant performance
benefits.
Would I be better off giving 3 of the db logs and tempdb their very
own raid 1 arrays? The log files are small and this strikes me as
rather wasteful.
How about going back to the future so to speak and opting for a raid 5
array with say 6 disks for the two reporting databases?
I could go for a RAID 10 array for the two reporting databases with
say 8 disks in total. Might this be a better option than having 2 x
RAID 10 arrays of 4 disks each because the data will be striped over
more disks?
Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
about 70 GB in total) makes sense? If a db is only read, does the log
file need to be on a separate array? As I said earlier, the reason I
didn't go down this track is that these dbs are restored from backup
nightly so hence I went for raid 10. The thing is, the restore is
over the network so presumably that will be the bottleneck?
You are right - LONG post. Perhaps you would be . . . no, let me rephrase
that. You will DEFINITELY be better served by getting an expert to give you
a few hours to perhaps a few days of his/her time to help you determine the
optimal configuration for this system.
I will make one general comment. When it comes to I/O the number of
spindles is HUGELY important. For example, IIRC you have just ONE active
spindle (2 drives RAID1) serving up your tempdb. This will almost certainly
be a bottleneck, especially during reporting periods when hashes and sorts
and work tables come into play for the reporting queries. I just got called
into a client where they had partitioned a new SAN all to heck and back and
it was a total DOG. They tried to set things up without expert DBA advice
and they did a bunch of other things wrong too, like not using appropriate
RAID set stripe sizes, setting controller cache ratios optimally, sector
aligning the partitions, setting format size, etc, etc. Please listen to my
advice above - or you will probably be wasting a lot of money for suboptimal
performance.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
|||I would put all of the data files for the reporting databases on raid 10
with the most spindles possible. Before you go out and fill the other slots,
I would make sure the indexing is tuned for the reports. Add a step to do
this with your nightly restore. Also what is memory\Page Life expectancy
looking like? Having the pages in memory is going to be fast than whatever
disk configuration you come up with. Lastly, it will be best to test to find
the optimal setting based on your IO patterns and hardware but I would start
off with a 64K stripe size in the ACU, 128 diskpart offset and 64k nt
allocation unit.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?
|||Quad-core and SQL 2000?
It is likely your problem is too many competing databases for the 2 GB of
available memory. Monitor the page life expectancy and cache hit ratio.
First thought would be to spend any future money on 64 bit Windows and SQL
2005 then max out your RAM. The less you have to go to the disk for data,
the less spindles you will need...
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?
This is a long post, but all advice appreciated.
I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
2 x quad core processors recently. It has a p800 RAID controller with
2 x Storageworks MSA 50 disk arrays.
It's used as a reporting server during the day and as a transaction
processing server at night. Only a handful of users use it to run
reports but they are heavy duty reports!
Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
146 GB disks in RAID 5 for everything else. Not ideal, but we were
pushed for time and needed the disk space.
Recently I reconfigured the disks by buying the new p800 controller
and the 2 storage arrays plus a bunch of disks.
The config below uses all but 8 disk slots.
This is how I had it. The Req. column is the space required:
OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
- After moving data around and restarting sql, tempdb shrunk to a
fraction of its size and hasn't grown much so it's nearer 16 GB now
than 50 GB.
So far, so good. Bit of a no brainer. Now for the user dbs and logs:
All log files including tempdb log
- 2 x 72 GB 15k rpm, raid 1, 46% free space
- Not ideal having the logs on one array but a budgetary
consideration.
Financial History mdf
- 4 x 146 GB 10k, raid 10, 58% free
- this db gets written to each night. This seems a good config to me,
though I've done no performance monitoring.
- the fact that its log file is on a raid 1 array with all other log
files may be an issue.
Reporting mdf
- 2 x 146 GB 10k, raid 1, 49% free
- as the name implies, this is a reporting database. It's created
from some of the other databases and de-normalised.
- the performance of this db is dire. Performance monitor today
showed the RAID 1 array is being hammered while CPU does nothing
much. This is essentially what happened before the upgrade. I
figured that the load needs spreading over more disks. I'm
considering buying an additional disk and creating a RAID 5 array.
Adhoc Reporting mdf
- 4 x 146 GB 10k, raid 10, 57% free
- Another reporting database used by a different group of people.
This one, like tempdb, shrunk hugely when I moved it to a different
array. It used to be 126 GB but is probably half that now.
- The main person who uses this hasn't commented that performance has
improved, but we've not generated any performance stats to see what's
going on.
UserDb1 mdf
- 2 x 146 GB 10k, raid 1, 61% free
- this is a copy of a userDb that resides on another server.
- it's backed up and restored to this reporting server each night in
order to build the reporting data. The data is also available for use
in reports during the day.
- it's not written to, but I went for raid 1 because it's around 50 GB
and restored from the network nightly. The restore times have got a
lot faster since it was moved off the single big raid 5 array.
UserDb2, UserDb3, etc mdfs
- 2 x 72 GB 15k, raid 1, 67% free
- this is a copy of various smaller userDbs that reside on other
servers.
- again, these dbs are backed up and restored to this reporting server
each night.
- a couple of very small ones (1 GB or less) are written to but the
larger ones (between 4 and 9 GB) aren't.
So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
7 free slots.
Priority number 1 is to improve the performance of the Reporting db.
Today I moved the reporting mdf to the same array as the recently
shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
I see this as a short term measure.
I have, as I've said, 8 slots free. I can justify the purchase of 8
more disks if I can more or less guarantee significant performance
benefits.
Would I be better off giving 3 of the db logs and tempdb their very
own raid 1 arrays? The log files are small and this strikes me as
rather wasteful.
How about going back to the future so to speak and opting for a raid 5
array with say 6 disks for the two reporting databases?
I could go for a RAID 10 array for the two reporting databases with
say 8 disks in total. Might this be a better option than having 2 x
RAID 10 arrays of 4 disks each because the data will be striped over
more disks?
Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
about 70 GB in total) makes sense? If a db is only read, does the log
file need to be on a separate array? As I said earlier, the reason I
didn't go down this track is that these dbs are restored from backup
nightly so hence I went for raid 10. The thing is, the restore is
over the network so presumably that will be the bottleneck?
You are right - LONG post. Perhaps you would be . . . no, let me rephrase
that. You will DEFINITELY be better served by getting an expert to give you
a few hours to perhaps a few days of his/her time to help you determine the
optimal configuration for this system.
I will make one general comment. When it comes to I/O the number of
spindles is HUGELY important. For example, IIRC you have just ONE active
spindle (2 drives RAID1) serving up your tempdb. This will almost certainly
be a bottleneck, especially during reporting periods when hashes and sorts
and work tables come into play for the reporting queries. I just got called
into a client where they had partitioned a new SAN all to heck and back and
it was a total DOG. They tried to set things up without expert DBA advice
and they did a bunch of other things wrong too, like not using appropriate
RAID set stripe sizes, setting controller cache ratios optimally, sector
aligning the partitions, setting format size, etc, etc. Please listen to my
advice above - or you will probably be wasting a lot of money for suboptimal
performance.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
|||I would put all of the data files for the reporting databases on raid 10
with the most spindles possible. Before you go out and fill the other slots,
I would make sure the indexing is tuned for the reports. Add a step to do
this with your nightly restore. Also what is memory\Page Life expectancy
looking like? Having the pages in memory is going to be fast than whatever
disk configuration you come up with. Lastly, it will be best to test to find
the optimal setting based on your IO patterns and hardware but I would start
off with a 64K stripe size in the ACU, 128 diskpart offset and 64k nt
allocation unit.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?
|||Quad-core and SQL 2000?
It is likely your problem is too many competing databases for the 2 GB of
available memory. Monitor the page life expectancy and cache hit ratio.
First thought would be to spend any future money on 64 bit Windows and SQL
2005 then max out your RAM. The less you have to go to the disk for data,
the less spindles you will need...
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?
Monday, March 12, 2012
I really need some advice.
Hi
Dont laugh but...
I am not entirely ignorant to web technologies, and best practices but i am having a bit of a planning dylema.
My company has a well established SQL 2000 database with windows application which has been created by myself, what i am planning on doing is creating a web site, using asp.net and publishing some of the information, so that our clients may use it, and stop pestering us on the phone. what i would like to know is what would be the best way forward, obviously i don't want to show them all our information, and don't want to put 5Gb worth of data onto a ISP website. What would you suggest i do?
Thanks in advance
BradHow important is it that the data be real-time?
You might consider implementing some DTS packages that extract key pieces of data that you want to share with your clients. The DTS package would write to a database on a separate server.
Terri
Friday, March 9, 2012
I need to make a minor change to a SQL Server script that is in production. Need advice so
Hello. I am not very strong with SQL server. But I know enough to get my way around. The reason I am asking for
help is that I need to change a SQL server script that resides on one of the production database servers of the
company I work for. I just need to add two new lines to the stored procedure.(See the lines that are bolded.) These
values are [MO_FAX],[MO_EMAIL].
Can someone tell me if there is a best way of going about doing this? Can I just simply open up SQL server and
quickly make the change? The big issue here is that this script is used in production. So I am just a little worried
about screwing something up. Anyway, I would appreciate some good advice on this?
/* Returns all data given the region and country */
ALTER PROCEDURE [dbo].[GetAllInfomration]
AS
SELECT
[COUNTRY],
[Company_NAME],
[CompanyAddress],
[CompanyPhone],
[MO_FAX]
[MO_EMAIL]
[CompanyFax],
FROM [InfoLookup].[dbo].[Company_Contact]
RETURN
Assuming the columns are in the same table, you need a comma after the column names.
/* Returns all data given the region and country */
ALTER PROCEDURE [dbo].[GetAllInfomration]
AS
SELECT
[COUNTRY],
[Company_NAME],
[CompanyAddress],
[CompanyPhone],
[MO_FAX],
[MO_EMAIL],
[CompanyFax],
FROM [InfoLookup].[dbo].[Company_Contact]
RETURN
Fix comma, and I would also recommend to move you new columns to the end of the result set so if any of your production application use columns ID number instead of name it will not blow up.
SELECT
[COUNTRY],
[Company_NAME],
[CompanyAddress],
[CompanyPhone],
[CompanyFax],
[MO_FAX],
[MO_EMAIL]
FROM [InfoLookup].[dbo].[Company_Contact]
Cool. Thank you for the advice, I will take it.
Just curious. Typically does it matter what order the columns are in? Specifically do they ever have to map exactly to the order of the columns defined in the data table?
For example if my table columns are in the following order
(PK) FirstName Varchar(40)
(FK) LastName Varchar(40)
Social Security Varchar(8)
Would a corresponding sql script need to be in the same order. i.e.
[]FirstName]
[Last Name]
Social Security.
Or can it for example be in the reverse order of the way it is defined in the table.
Social Security
[Last Name]
[Last Name]
|||No the order doesnt matter as long as your application is not referring to the columns by their position. I've seen some .NET code with datareader where the code accesses columns by their positions. Perhaps there are other scenarios other .NETters can explain..|||
Yes most programmers reference column by column order number when they try to get value from column because it is faster when by column name.
But as long as they use column names referencing to data it will work, but if you have no idea how you result is processed by other users just do not change columns order.
Sometimes is also possible that they have fixed schema for result from you SP and you new columns will generate errors if they will run your SP.
You also should not change column name format (small capital letters) because for example XML is very sensitive on it and it will not recognize columns correctly if they have different so Field is different then field for XML
Thanks
Friday, February 24, 2012
I need some advice before starting VB/SQL Project
I am just in the process of learning VB.NET so that I can upsize an access database that I developed for my company.
I need advise on two subjects:
First, I am going to be starting a new VB.NET / SQL project using MSDE installed on my development computer. What do I need to do to allow me to easily deploy the program on several workstations and a dedicated server once development is finished?
Second, I would like to have a transitional period where both the VBA in the current MDB and the new VB.NET project access the same data. The one solution that I came up with was creating queries on the SQL server that pointed to the MDB, then once I am comfortable with the VB.NET end of things I would transfer the data it's self to the SQL server and change the queries to point to the new data location... However I have no idea weather or not this will work or if it is even close to being a good idea.
Any advice would be greatly appreciated.I wouldn't waste my time on developing in Access and then moving to SQL Server. Just start with the latter!|||Michael Kaiser
Brett Smith
Geez I thought I was unique...
Anyway, take rdjabarov's advice...
You can easily set up an Access mdb and link the sql server tables to it...
Don't bother with Jet...or don't you have sql server installed yet?
Or is it an existing application already?
How big is the database (or how big will it be?)
Also, you don't mention anything about stored procedures...
Do you plan on using them...it would be a big plus, especially for you're design implementation...|||Absolutely. Creating queries in SQL Server that point to data in a transitional Access database is going to cause you more trouble than just building the tables in SQL Server to begin with.
Hey, I like Access a lot, but there is really no point in using it in your development if it is not going to be part of your final solution.
If your data is already in Access, then it is worth trying the Upsizing wizard. I've used it once or twice and I seem to recall that it did a decent job. If you need a transitional application, then move your existing forms and code into a Microsoft Access Data Project that is tied directly to your SQL server database.|||Unfortunately I am already up to my eyeballs in Access
This is at the moment the primary issue tracking system for the small consulting firm that I work for. Hundreds of contacts, thousands of related records... and it has only been in use for about 6 months.
What I need to do is develop the project in vb and slowly transfer functionality over as modules get debugged.
To answer the other question about stored procedures, I am definitely going to be using them.|||I'm a little confused (and that's on a good day)...
Is the data centraly located, or does each client have their own copy of the data?|||Then again, I think you would be best off if you transfered the data to Microsoft SQL server and converted your Access database into an Access Data Project while you transition to your final solution.|||The data is centrally located in a mdb that only contains tables and each client has their own MDB file which has all the forms reports and VBA and links to the tables on the main database
I won't even begin to try to explain how this configuration came about, just keep in mind that decisions like that came about via committee and I (the actual developer) was not invited.|||Well, that's not a bad configuration for a distributed Microsoft Acces solution, but it does make your upsizing more difficult.
You could still develop an Access Data Project file and distribute it to your users. If you import your forms and modules it should look identical to them. Somewhere, you are going to have to bite the administrative bullet and come up with the transition strategy that best suits your business.|||I guess at this point I am going to look into an ADP, what I was hoping to avoid was spending any more time in access when it's not in the final plan. I know that the upsize wizard will not work for most of my forms and two of my tables, but I may just have to do it and get it over with|||Just another thought, would it make senct to just migrate the data to SQL and then change the links in the client MDB files to point to the SQL tables via an ODBC connection rather than actually transfer everything over to an ADP?|||I would...
a data source is a data source...
Only downside depends on the complexity of the forms/reports...
could be a lot slower depending on the # of connections opened by a form or report..|||Sure. Just make sure your security is good. Also, chances are good that you performance will be significantly slower than if you used an ADP interface.|||This is looking like my best option.
The last question on that front would be does anyone know if I like an Access MDB to SQL via ODBC can I use a SQL View for the source and meke it appear to Access to be a linked table?
I would only want to do this for two of the tables, because I would like to make some changes to the table field names.|||Yeah...check it out...they'll appear just like tables...
Just make sure that the view is updatable...otherwise it'll be a problem...|||I would get away from linked objects all together and replace them with pass-through queries to guarantee a more-or-less reasonable performance (I still don't understand why you would want to stick to Access to be your middle man.)
And to answer your question, - yes you can set a view as a source for a linked object.|||I think I am on my way!
I am sure that I will be back soon with other questions, but this should at least allow me to get my data onto SQL wile making very minor changes to the current Access database.
I need advise on two subjects:
First, I am going to be starting a new VB.NET / SQL project using MSDE installed on my development computer. What do I need to do to allow me to easily deploy the program on several workstations and a dedicated server once development is finished?
Second, I would like to have a transitional period where both the VBA in the current MDB and the new VB.NET project access the same data. The one solution that I came up with was creating queries on the SQL server that pointed to the MDB, then once I am comfortable with the VB.NET end of things I would transfer the data it's self to the SQL server and change the queries to point to the new data location... However I have no idea weather or not this will work or if it is even close to being a good idea.
Any advice would be greatly appreciated.I wouldn't waste my time on developing in Access and then moving to SQL Server. Just start with the latter!|||Michael Kaiser
Brett Smith
Geez I thought I was unique...
Anyway, take rdjabarov's advice...
You can easily set up an Access mdb and link the sql server tables to it...
Don't bother with Jet...or don't you have sql server installed yet?
Or is it an existing application already?
How big is the database (or how big will it be?)
Also, you don't mention anything about stored procedures...
Do you plan on using them...it would be a big plus, especially for you're design implementation...|||Absolutely. Creating queries in SQL Server that point to data in a transitional Access database is going to cause you more trouble than just building the tables in SQL Server to begin with.
Hey, I like Access a lot, but there is really no point in using it in your development if it is not going to be part of your final solution.
If your data is already in Access, then it is worth trying the Upsizing wizard. I've used it once or twice and I seem to recall that it did a decent job. If you need a transitional application, then move your existing forms and code into a Microsoft Access Data Project that is tied directly to your SQL server database.|||Unfortunately I am already up to my eyeballs in Access
This is at the moment the primary issue tracking system for the small consulting firm that I work for. Hundreds of contacts, thousands of related records... and it has only been in use for about 6 months.
What I need to do is develop the project in vb and slowly transfer functionality over as modules get debugged.
To answer the other question about stored procedures, I am definitely going to be using them.|||I'm a little confused (and that's on a good day)...
Is the data centraly located, or does each client have their own copy of the data?|||Then again, I think you would be best off if you transfered the data to Microsoft SQL server and converted your Access database into an Access Data Project while you transition to your final solution.|||The data is centrally located in a mdb that only contains tables and each client has their own MDB file which has all the forms reports and VBA and links to the tables on the main database
I won't even begin to try to explain how this configuration came about, just keep in mind that decisions like that came about via committee and I (the actual developer) was not invited.|||Well, that's not a bad configuration for a distributed Microsoft Acces solution, but it does make your upsizing more difficult.
You could still develop an Access Data Project file and distribute it to your users. If you import your forms and modules it should look identical to them. Somewhere, you are going to have to bite the administrative bullet and come up with the transition strategy that best suits your business.|||I guess at this point I am going to look into an ADP, what I was hoping to avoid was spending any more time in access when it's not in the final plan. I know that the upsize wizard will not work for most of my forms and two of my tables, but I may just have to do it and get it over with|||Just another thought, would it make senct to just migrate the data to SQL and then change the links in the client MDB files to point to the SQL tables via an ODBC connection rather than actually transfer everything over to an ADP?|||I would...
a data source is a data source...
Only downside depends on the complexity of the forms/reports...
could be a lot slower depending on the # of connections opened by a form or report..|||Sure. Just make sure your security is good. Also, chances are good that you performance will be significantly slower than if you used an ADP interface.|||This is looking like my best option.
The last question on that front would be does anyone know if I like an Access MDB to SQL via ODBC can I use a SQL View for the source and meke it appear to Access to be a linked table?
I would only want to do this for two of the tables, because I would like to make some changes to the table field names.|||Yeah...check it out...they'll appear just like tables...
Just make sure that the view is updatable...otherwise it'll be a problem...|||I would get away from linked objects all together and replace them with pass-through queries to guarantee a more-or-less reasonable performance (I still don't understand why you would want to stick to Access to be your middle man.)
And to answer your question, - yes you can set a view as a source for a linked object.|||I think I am on my way!
I am sure that I will be back soon with other questions, but this should at least allow me to get my data onto SQL wile making very minor changes to the current Access database.
Subscribe to:
Posts (Atom)