Is there a MS SQL Backup tool that will zip or compress the backup files?

Currently we use a SQL maintenance plan to do a full backup of all our databases daily (about 40 databases on our production server). As you can imagine, this eats up disk space quickly so currently we manually zip the backup files and/or move them to an archive drive. I considered writing an application to walk through the backup folder structure and zip any .bak file it finds, but I know there are some third party tools out there that will backup/restore a MS SQL database.

I was wondering if any of these also zip the backups once they are created. Any recommendations or suggestions are welcome.

First of all, for the sake of your business and clients, I would keep an agreed-upon set of backups in the original state. While it hasn't happened to me often, I have had the problem of a corrupted zip file before. Anything that compresses data has the possibility for corruption. I would recommend at least a week (7 days) worth of backups; at least that's what I would want.

As for a quick, easy, and free way to compress files (using XP or higher), just send the backup files to a compressed folder. Create a new folder, right-click it, choose 'Properties, Click 'Advanced' under the General tab, and choose 'Compress contents to save disk space'. If you hover your mouse over the folder, it gives you the space the folder would take up if uncompressed; if you right-click the folder again and choose 'Properties', you'll see the original size of all the filesand the actual size of all the files on the disk. You can also do this with individual files by viewing properties.

Note: compressed files show in blue.

By compressing a folder, any new file created in it will be compressed automatically. However, if you simply 'Move' an existing file, the file will retain its current state (if it wasn't compressed to begin with, it won't compress).

Is there a difference?

Is there really a difference of approach?

I have several things to consider in an estimating database. Production, Shipping, Field Work, Field Hardware, etc...

All of the above have account numbers. Now I was wondering, would there be any benifit to having one table or several tables?

One Table Example

Account (PK) | Category | Description

or

Multiple tables

tbProduction
Account (PK) | Description

tbShipping
Account (PK) | Description

tbProducts
Account (PK) | Description
etc....

I like having the tables split up and/or all in one.

Any thoughts, pros / cons?

Mike BIn the one table version, it could be easier to check for duplicate Accounts. The downside is that a user directly accessing the table needs to know about the category (and consider). Besides that, you may want to consider how access to the table(s) is arranged from a security point of view, eventhough both the single table / multiple tables have their own solutions. Next; in future, would there be any other categories to consider?|||In the one table version, it could be easier to check for duplicate Accounts.

Good point

The downside is that a user directly accessing the table needs to know about the category (and consider).

The reason for my questioning.

Besides that, you may want to consider how access to the table(s) is arranged from a security point of view

Basically they are simply lookup tables for the account numbers, so read only for most users and read write for a small group.

When I say look up, in shipping for example you can have three accounts, Trailer Rentals, Services, and Cost Overrun.

Next; in future, would there be any other categories to consider? [/SIZE]

Good point, but if there are, it would probably need further modification to the database and application so adding another table would not make a difference since the only reason I see doing this would be to change the scope of the "estimate".

Mike B

Is SQLIOStress failure a definitive problem?

Over the past months I have used SQLIOStress extensively to validate
proposed hardware going to production. In most cases, the hardware has
failed to meet performance expectations and the SQLIOStress results
have not mattered.
However, today we have a piece of hardware which seems to perform very
well, yet *fails* the SQLIOStress test. Should we care?
Research
I called the sales rep, and they talked to their technical reps about
it...no useful information. Nothing more than I can search on the web.
I've contacted MSFT technical support, and they have yet to come back
with any specific information beyond what I can find in MSFT KB.
I've searched on the internet. Yet to find comments on why failures
are definitive no-go's for hardware
Failure details
Run SQLIOStress with /O to cause a reboot. (Note: the /O reboots the
computer not the storage, so it seems to be dated from a time when the
two were wired together. So we're really rebooting a Windows 2003 SP 1
server attached to storage which is still powered.)
Command line:
sqliostress
/fn:\stress.mdf/lm:\stress.ldf/S3072 /I11 /O10
Hardware: HP - MSA-500 G2 storage array and Proliant DL585 internal
drives. Quad HP Opteron is the machine which is using this storage.
>From the systems engineer who rebuilt the machine this week:
"The MSA500 G2 Firmware was upgraded from: Product ID 0x0E11E020,
Version 1.40 to 1.52
(http://h18023.www1.hp.com/support/fi...oad/23010.html).
We are also running the latest PSP and all of the other firmware is
being reported as up to date."
We are running SQLIOStress with SQLServer in the background. We have
discovered that with SQLServer in the background (dedicated 7 Gigs of
memory out of 8 or so) we more reliably see problems.
Configured the HP to 100% write cache.
Run SQLIOStress. It triggers a reboot. Restart SQLIOStress to
validate the written disk image and it generated a log file with the
following error:
06/30/05 17:37:11 00003192Verifying the integrity of the file.
06/30/05 17:39:05 00003192
06/30/05 17:39:05 00003192ERROR: Byte 1033 supposed to be [0x41] but
[0x42] was read
06/30/05 17:39:05 00003192ERROR: Byte 1034 supposed to be [0x41] but
[0x4F] was read
06/30/05 17:39:05 00003192ERROR: Byte 1035 supposed to be [0x41] but
[0x42] was read
06/30/05 17:39:05 00003192
06/30/05 17:39:05
00003192----
06/30/05 17:39:05 00003192Found pattern [A] in file for page 154455.
06/30/05 17:39:05 00003192Bytes read = 8192
06/30/05 17:39:05 00003192Potential torn write, lost write or stale
read may have been encountered
06/30/05 17:39:05
00003192----
06/30/05 17:39:05 00003192
06/30/05 17:39:05 00003192Sector: 0 LSN: 3914551 Page:
154455 Address: 0xA3280000
06/30/05 17:39:05
00003192[AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAA
AAAA]
06/30/05 17:39:05 00003192Hex:
[0x41414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141
41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414
14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141
41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 4141414141414141414141414141414141414141414141]
We've also seen this with 50% read/50% write cache (using earlier
firmware). (Upon reviewing another post, we had decided to do 100%
write cache to better align with optimal performance and avoid possible
failures due to read cacheing. The failure still occurred. It did not
take many attempts to cause it.)
Some people have suggested that a torn write is a fact of life...that
with disks writing 512 bytes at a time, there is always the possibility
that a portion of the page will be written. Is that true? Does that
mean that a sqliostress failure is not news?
(Note: we configured NTFS with 8K blocks and 64K stripes)
Reasoning: when NT sends data to the hard drive, some may be written to
disk before NT receives an ACK that data has been written. With a
partial data write you have a "torn page". Hence, the error we see
(which may or may not be a torn page?) would indicate an OS issue and
not reflect on the hardware at all. Is this true? Or are there caches
which guarantee not to let any data flow out to disk until the ack is
returned to NT? (In which case a battery backed cache can ensure no
loss of data.)
Have other companies tested with SQLIOStress, seen failures, and still
deployed hardware and feel good about that? Or should a failure of
SQLIOStress be treated as the kiss of death?
Please advise.
Mark Andersen
Follow up:
A helpful MSFT engineer from the SQL Server group spent some time on
the phone with me today. Thank you!
Conclusions:
/O can indicate problems, but not definitively. /O simply causes a
reboot while writing data.
Extensive testing we did at our company to pull the power plug during
both log and checkpoint operations provides a better sense of whether
the hardware works correctly.
MSFT technical support was unable to provide much help with SQLIOStress.
|||Hi Mark
I read your post with interest as I am getting very similar results from
SQLIOStress, but I am trying to prove that SQL Server is safe in a production
virtual environment under either Microsoft Virtual Server or VmWare ESX
server. I was concerned that there is something intrinsically unsafe about
virtual machines but since you are getting exact same errors under real
hardware, it now doesn't worry me as much anymore.
I guess I am going to have to do similar extensive testing as you - pulling
the plug on both the virtual guest and the operating system host and see the
results. How did you go about deciding a good time to power off and how did
you check for data errors?
"markandersen@.evare.com" wrote:

> Follow up:
> A helpful MSFT engineer from the SQL Server group spent some time on
> the phone with me today. Thank you!
> Conclusions:
> /O can indicate problems, but not definitively. /O simply causes a
> reboot while writing data.
> Extensive testing we did at our company to pull the power plug during
> both log and checkpoint operations provides a better sense of whether
> the hardware works correctly.
> MSFT technical support was unable to provide much help with SQLIOStress.
>
|||We have seen similar issues on an MSA 1000. In our case the problem is
resolved by a firmware upgrade to version 4.32 on the array controller. From
the release notes:
Fixes an issue found with SQL Server 2000 in which SQL may report the use of
stale cache
data under the following conditions:
?? Extremely heavy I/O load
?? Some percentage of MSA controller cache allocated to READ
?? Multiple small simultaneous writes to the same SCSI blocks
?? Write cache is full at the time of the request
If all of the above criteria is met SQL may report error ID’s 605, 644 and
823 when performing subsequent reads from the same SCSI blocks
"markandersen@.evare.com" wrote:

> Over the past months I have used SQLIOStress extensively to validate
> proposed hardware going to production. In most cases, the hardware has
> failed to meet performance expectations and the SQLIOStress results
> have not mattered.
> However, today we have a piece of hardware which seems to perform very
> well, yet *fails* the SQLIOStress test. Should we care?
> Research
> --
> I called the sales rep, and they talked to their technical reps about
> it...no useful information. Nothing more than I can search on the web.
> I've contacted MSFT technical support, and they have yet to come back
> with any specific information beyond what I can find in MSFT KB.
> I've searched on the internet. Yet to find comments on why failures
> are definitive no-go's for hardware
>
> Failure details
> --
> Run SQLIOStress with /O to cause a reboot. (Note: the /O reboots the
> computer not the storage, so it seems to be dated from a time when the
> two were wired together. So we're really rebooting a Windows 2003 SP 1
> server attached to storage which is still powered.)
> Command line:
> sqliostress
> /fn:\stress.mdf/lm:\stress.ldf/S3072 /I11 /O10
> Hardware: HP - MSA-500 G2 storage array and Proliant DL585 internal
> drives. Quad HP Opteron is the machine which is using this storage.
> "The MSA500 G2 Firmware was upgraded from: Product ID 0x0E11E020,
> Version 1.40 to 1.52
> (http://h18023.www1.hp.com/support/fi...oad/23010.html).
> We are also running the latest PSP and all of the other firmware is
> being reported as up to date."
> We are running SQLIOStress with SQLServer in the background. We have
> discovered that with SQLServer in the background (dedicated 7 Gigs of
> memory out of 8 or so) we more reliably see problems.
> Configured the HP to 100% write cache.
> Run SQLIOStress. It triggers a reboot. Restart SQLIOStress to
> validate the written disk image and it generated a log file with the
> following error:
> 06/30/05 17:37:11 00003192Verifying the integrity of the file.
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05 00003192ERROR: Byte 1033 supposed to be [0x41] but
> [0x42] was read
> 06/30/05 17:39:05 00003192ERROR: Byte 1034 supposed to be [0x41] but
> [0x4F] was read
> 06/30/05 17:39:05 00003192ERROR: Byte 1035 supposed to be [0x41] but
> [0x42] was read
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05
> 00003192----
> 06/30/05 17:39:05 00003192Found pattern [A] in file for page 154455.
> 06/30/05 17:39:05 00003192Bytes read = 8192
> 06/30/05 17:39:05 00003192Potential torn write, lost write or stale
> read may have been encountered
> 06/30/05 17:39:05
> 00003192----
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05 00003192Sector: 0 LSN: 3914551 Page:
> 154455 Address: 0xA3280000
> 06/30/05 17:39:05
> 00003192[AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAA
AAAAAA]
> 06/30/05 17:39:05 00003192Hex:
>
[0x41414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141
41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414
14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141414141414141414141414141414141414141414141414 14141
41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 41414141414141414141414141414141414141414141414141 4141414141414141414141414141414141414141414141]
> We've also seen this with 50% read/50% write cache (using earlier
> firmware). (Upon reviewing another post, we had decided to do 100%
> write cache to better align with optimal performance and avoid possible
> failures due to read cacheing. The failure still occurred. It did not
> take many attempts to cause it.)
> Some people have suggested that a torn write is a fact of life...that
> with disks writing 512 bytes at a time, there is always the possibility
> that a portion of the page will be written. Is that true? Does that
> mean that a sqliostress failure is not news?
> (Note: we configured NTFS with 8K blocks and 64K stripes)
> Reasoning: when NT sends data to the hard drive, some may be written to
> disk before NT receives an ACK that data has been written. With a
> partial data write you have a "torn page". Hence, the error we see
> (which may or may not be a torn page?) would indicate an OS issue and
> not reflect on the hardware at all. Is this true? Or are there caches
> which guarantee not to let any data flow out to disk until the ack is
> returned to NT? (In which case a battery backed cache can ensure no
> loss of data.)
> Have other companies tested with SQLIOStress, seen failures, and still
> deployed hardware and feel good about that? Or should a failure of
> SQLIOStress be treated as the kiss of death?
> Please advise.
> Mark Andersen
>

Is SQLIOStress failure a definitive problem?

Over the past months I have used SQLIOStress extensively to validate
proposed hardware going to production. In most cases, the hardware has
failed to meet performance expectations and the SQLIOStress results
have not mattered.
However, today we have a piece of hardware which seems to perform very
well, yet *fails* the SQLIOStress test. Should we care?
Research
--
I called the sales rep, and they talked to their technical reps about
it...no useful information. Nothing more than I can search on the web.
I've contacted MSFT technical support, and they have yet to come back
with any specific information beyond what I can find in MSFT KB.
I've searched on the internet. Yet to find comments on why failures
are definitive no-go's for hardware
Failure details
--
Run SQLIOStress with /O to cause a reboot. (Note: the /O reboots the
computer not the storage, so it seems to be dated from a time when the
two were wired together. So we're really rebooting a Windows 2003 SP 1
server attached to storage which is still powered.)
Command line:
sqliostress
/fn:\stress.mdf /lm:\stress.ldf /S3072 /I11 /O10
Hardware: HP - MSA-500 G2 storage array and Proliant DL585 internal
drives. Quad HP Opteron is the machine which is using this storage.
>From the systems engineer who rebuilt the machine this week:
"The MSA500 G2 Firmware was upgraded from: Product ID 0x0E11E020,
Version 1.40 to 1.52
(http://h18023.www1.hp.com/support/f...load/23010.html).
We are also running the latest PSP and all of the other firmware is
being reported as up to date."
We are running SQLIOStress with SQLServer in the background. We have
discovered that with SQLServer in the background (dedicated 7 Gigs of
memory out of 8 or so) we more reliably see problems.
Configured the HP to 100% write cache.
Run SQLIOStress. It triggers a reboot. Restart SQLIOStress to
validate the written disk image and it generated a log file with the
following error:
06/30/05 17:37:11 00003192 Verifying the integrity of the file.
06/30/05 17:39:05 00003192
06/30/05 17:39:05 00003192 ERROR: Byte 1033 supposed to be [0x41] but
[0x42] was read
06/30/05 17:39:05 00003192 ERROR: Byte 1034 supposed to be [0x41] but
[0x4F] was read
06/30/05 17:39:05 00003192 ERROR: Byte 1035 supposed to be [0x41] but
[0x42] was read
06/30/05 17:39:05 00003192
06/30/05 17:39:05
00003192 ---
--
06/30/05 17:39:05 00003192 Found pattern [A] in file for page 154455.
06/30/05 17:39:05 00003192 Bytes read = 8192
06/30/05 17:39:05 00003192 Potential torn write, lost write or stale
read may have been encountered
06/30/05 17:39:05
00003192 ---
--
06/30/05 17:39:05 00003192
06/30/05 17:39:05 00003192 Sector: 0 LSN: 3914551 Page:
154455 Address: 0xA3280000
06/30/05 17:39:05
00003192 & #91;AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAA]
06/30/05 17:39:05 00003192 Hex:
& #91;0x4141414141414141414141414141414141
41414141414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
1414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
414141414141414141]
We've also seen this with 50% read/50% write cache (using earlier
firmware). (Upon reviewing another post, we had decided to do 100%
write cache to better align with optimal performance and avoid possible
failures due to read cacheing. The failure still occurred. It did not
take many attempts to cause it.)
Some people have suggested that a torn write is a fact of life...that
with disks writing 512 bytes at a time, there is always the possibility
that a portion of the page will be written. Is that true? Does that
mean that a sqliostress failure is not news?
(Note: we configured NTFS with 8K blocks and 64K stripes)
Reasoning: when NT sends data to the hard drive, some may be written to
disk before NT receives an ACK that data has been written. With a
partial data write you have a "torn page". Hence, the error we see
(which may or may not be a torn page?) would indicate an OS issue and
not reflect on the hardware at all. Is this true? Or are there caches
which guarantee not to let any data flow out to disk until the ack is
returned to NT? (In which case a battery backed cache can ensure no
loss of data.)
Have other companies tested with SQLIOStress, seen failures, and still
deployed hardware and feel good about that? Or should a failure of
SQLIOStress be treated as the kiss of death?
Please advise.
Mark AndersenFollow up:
A helpful MSFT engineer from the SQL Server group spent some time on
the phone with me today. Thank you!
Conclusions:
/O can indicate problems, but not definitively. /O simply causes a
reboot while writing data.
Extensive testing we did at our company to pull the power plug during
both log and checkpoint operations provides a better sense of whether
the hardware works correctly.
MSFT technical support was unable to provide much help with SQLIOStress.|||Hi Mark
I read your post with interest as I am getting very similar results from
SQLIOStress, but I am trying to prove that SQL Server is safe in a productio
n
virtual environment under either Microsoft Virtual Server or VmWare ESX
server. I was concerned that there is something intrinsically unsafe about
virtual machines but since you are getting exact same errors under real
hardware, it now doesn't worry me as much anymore.
I guess I am going to have to do similar extensive testing as you - pulling
the plug on both the virtual guest and the operating system host and see the
results. How did you go about deciding a good time to power off and how did
you check for data errors?
"markandersen@.evare.com" wrote:

> Follow up:
> A helpful MSFT engineer from the SQL Server group spent some time on
> the phone with me today. Thank you!
> Conclusions:
> /O can indicate problems, but not definitively. /O simply causes a
> reboot while writing data.
> Extensive testing we did at our company to pull the power plug during
> both log and checkpoint operations provides a better sense of whether
> the hardware works correctly.
> MSFT technical support was unable to provide much help with SQLIOStress.
>|||We have seen similar issues on an MSA 1000. In our case the problem is
resolved by a firmware upgrade to version 4.32 on the array controller. From
the release notes:
Fixes an issue found with SQL Server 2000 in which SQL may report the use of
stale cache
data under the following conditions:
?? Extremely heavy I/O load
?? Some percentage of MSA controller cache allocated to READ
?? Multiple small simultaneous writes to the same SCSI blocks
?? Write cache is full at the time of the request
If all of the above criteria is met SQL may report error ID’s 605, 644 and
823 when performing subsequent reads from the same SCSI blocks
"markandersen@.evare.com" wrote:

> Over the past months I have used SQLIOStress extensively to validate
> proposed hardware going to production. In most cases, the hardware has
> failed to meet performance expectations and the SQLIOStress results
> have not mattered.
> However, today we have a piece of hardware which seems to perform very
> well, yet *fails* the SQLIOStress test. Should we care?
> Research
> --
> I called the sales rep, and they talked to their technical reps about
> it...no useful information. Nothing more than I can search on the web.
> I've contacted MSFT technical support, and they have yet to come back
> with any specific information beyond what I can find in MSFT KB.
> I've searched on the internet. Yet to find comments on why failures
> are definitive no-go's for hardware
>
> Failure details
> --
> Run SQLIOStress with /O to cause a reboot. (Note: the /O reboots the
> computer not the storage, so it seems to be dated from a time when the
> two were wired together. So we're really rebooting a Windows 2003 SP 1
> server attached to storage which is still powered.)
> Command line:
> sqliostress
> /fn:\stress.mdf /lm:\stress.ldf /S3072 /I11 /O10
> Hardware: HP - MSA-500 G2 storage array and Proliant DL585 internal
> drives. Quad HP Opteron is the machine which is using this storage.
> "The MSA500 G2 Firmware was upgraded from: Product ID 0x0E11E020,
> Version 1.40 to 1.52
> (http://h18023.www1.hp.com/support/f...load/23010.html).
> We are also running the latest PSP and all of the other firmware is
> being reported as up to date."
> We are running SQLIOStress with SQLServer in the background. We have
> discovered that with SQLServer in the background (dedicated 7 Gigs of
> memory out of 8 or so) we more reliably see problems.
> Configured the HP to 100% write cache.
> Run SQLIOStress. It triggers a reboot. Restart SQLIOStress to
> validate the written disk image and it generated a log file with the
> following error:
> 06/30/05 17:37:11 00003192 Verifying the integrity of the file.
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05 00003192 ERROR: Byte 1033 supposed to be [0x41] but
> [0x42] was read
> 06/30/05 17:39:05 00003192 ERROR: Byte 1034 supposed to be [0x41] but
> [0x4F] was read
> 06/30/05 17:39:05 00003192 ERROR: Byte 1035 supposed to be [0x41] but
> [0x42] was read
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05
> 00003192 ---
--
> 06/30/05 17:39:05 00003192 Found pattern [A] in file for page 154455.
> 06/30/05 17:39:05 00003192 Bytes read = 8192
> 06/30/05 17:39:05 00003192 Potential torn write, lost write or stale
> read may have been encountered
> 06/30/05 17:39:05
> 00003192 ---
--
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05 00003192 Sector: 0 LSN: 3914551 Page:
> 154455 Address: 0xA3280000
> 06/30/05 17:39:05
> 00003192 & #91;AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAA
AAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAA]
> 06/30/05 17:39:05 00003192 Hex:
>
& #91;0x4141414141414141414141414141414141
41414141414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
1414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141414141414141414141414141
414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
1414141414141414141414141414141414141414
141414141414141414141414141414141414
141414141414141414141414141
4141414141414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141
4141414141414141414141414141414141414141
4141414141414141414141414141414141414141
414141414141
4141414141414141414141414141414141414141
4141414141414141414141]
> We've also seen this with 50% read/50% write cache (using earlier
> firmware). (Upon reviewing another post, we had decided to do 100%
> write cache to better align with optimal performance and avoid possible
> failures due to read cacheing. The failure still occurred. It did not
> take many attempts to cause it.)
> Some people have suggested that a torn write is a fact of life...that
> with disks writing 512 bytes at a time, there is always the possibility
> that a portion of the page will be written. Is that true? Does that
> mean that a sqliostress failure is not news?
> (Note: we configured NTFS with 8K blocks and 64K stripes)
> Reasoning: when NT sends data to the hard drive, some may be written to
> disk before NT receives an ACK that data has been written. With a
> partial data write you have a "torn page". Hence, the error we see
> (which may or may not be a torn page?) would indicate an OS issue and
> not reflect on the hardware at all. Is this true? Or are there caches
> which guarantee not to let any data flow out to disk until the ack is
> returned to NT? (In which case a battery backed cache can ensure no
> loss of data.)
> Have other companies tested with SQLIOStress, seen failures, and still
> deployed hardware and feel good about that? Or should a failure of
> SQLIOStress be treated as the kiss of death?
> Please advise.
> Mark Andersen
>

Is SQLIOStress failure a definitive problem?

Over the past months I have used SQLIOStress extensively to validate
proposed hardware going to production. In most cases, the hardware has
failed to meet performance expectations and the SQLIOStress results
have not mattered.
However, today we have a piece of hardware which seems to perform very
well, yet *fails* the SQLIOStress test. Should we care?
Research
--
I called the sales rep, and they talked to their technical reps about
it...no useful information. Nothing more than I can search on the web.
I've contacted MSFT technical support, and they have yet to come back
with any specific information beyond what I can find in MSFT KB.
I've searched on the internet. Yet to find comments on why failures
are definitive no-go's for hardware
Failure details
--
Run SQLIOStress with /O to cause a reboot. (Note: the /O reboots the
computer not the storage, so it seems to be dated from a time when the
two were wired together. So we're really rebooting a Windows 2003 SP 1
server attached to storage which is still powered.)
Command line:
sqliostress
/fn:\stress.mdf /lm:\stress.ldf /S3072 /I11 /O10
Hardware: HP - MSA-500 G2 storage array and Proliant DL585 internal
drives. Quad HP Opteron is the machine which is using this storage.
>From the systems engineer who rebuilt the machine this week:
"The MSA500 G2 Firmware was upgraded from: Product ID 0x0E11E020,
Version 1.40 to 1.52
(http://h18023.www1.hp.com/support/files/server/us/download/23010.html).
We are also running the latest PSP and all of the other firmware is
being reported as up to date."
We are running SQLIOStress with SQLServer in the background. We have
discovered that with SQLServer in the background (dedicated 7 Gigs of
memory out of 8 or so) we more reliably see problems.
Configured the HP to 100% write cache.
Run SQLIOStress. It triggers a reboot. Restart SQLIOStress to
validate the written disk image and it generated a log file with the
following error:
06/30/05 17:37:11 00003192 Verifying the integrity of the file.
06/30/05 17:39:05 00003192
06/30/05 17:39:05 00003192 ERROR: Byte 1033 supposed to be [0x41] but
[0x42] was read
06/30/05 17:39:05 00003192 ERROR: Byte 1034 supposed to be [0x41] but
[0x4F] was read
06/30/05 17:39:05 00003192 ERROR: Byte 1035 supposed to be [0x41] but
[0x42] was read
06/30/05 17:39:05 00003192
06/30/05 17:39:05
00003192 ----
06/30/05 17:39:05 00003192 Found pattern [A] in file for page 154455.
06/30/05 17:39:05 00003192 Bytes read = 8192
06/30/05 17:39:05 00003192 Potential torn write, lost write or stale
read may have been encountered
06/30/05 17:39:05
00003192 ----
06/30/05 17:39:05 00003192
06/30/05 17:39:05 00003192 Sector: 0 LSN: 3914551 Page:
154455 Address: 0xA3280000
06/30/05 17:39:05
00003192 [AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA]
06/30/05 17:39:05 00003192 Hex:
[0x414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141]
We've also seen this with 50% read/50% write cache (using earlier
firmware). (Upon reviewing another post, we had decided to do 100%
write cache to better align with optimal performance and avoid possible
failures due to read cacheing. The failure still occurred. It did not
take many attempts to cause it.)
Some people have suggested that a torn write is a fact of life...that
with disks writing 512 bytes at a time, there is always the possibility
that a portion of the page will be written. Is that true? Does that
mean that a sqliostress failure is not news?
(Note: we configured NTFS with 8K blocks and 64K stripes)
Reasoning: when NT sends data to the hard drive, some may be written to
disk before NT receives an ACK that data has been written. With a
partial data write you have a "torn page". Hence, the error we see
(which may or may not be a torn page?) would indicate an OS issue and
not reflect on the hardware at all. Is this true? Or are there caches
which guarantee not to let any data flow out to disk until the ack is
returned to NT? (In which case a battery backed cache can ensure no
loss of data.)
Have other companies tested with SQLIOStress, seen failures, and still
deployed hardware and feel good about that? Or should a failure of
SQLIOStress be treated as the kiss of death?
Please advise.
Mark AndersenFollow up:
A helpful MSFT engineer from the SQL Server group spent some time on
the phone with me today. Thank you!
Conclusions:
/O can indicate problems, but not definitively. /O simply causes a
reboot while writing data.
Extensive testing we did at our company to pull the power plug during
both log and checkpoint operations provides a better sense of whether
the hardware works correctly.
MSFT technical support was unable to provide much help with SQLIOStress.|||Hi Mark
I read your post with interest as I am getting very similar results from
SQLIOStress, but I am trying to prove that SQL Server is safe in a production
virtual environment under either Microsoft Virtual Server or VmWare ESX
server. I was concerned that there is something intrinsically unsafe about
virtual machines but since you are getting exact same errors under real
hardware, it now doesn't worry me as much anymore.
I guess I am going to have to do similar extensive testing as you - pulling
the plug on both the virtual guest and the operating system host and see the
results. How did you go about deciding a good time to power off and how did
you check for data errors?
"markandersen@.evare.com" wrote:
> Follow up:
> A helpful MSFT engineer from the SQL Server group spent some time on
> the phone with me today. Thank you!
> Conclusions:
> /O can indicate problems, but not definitively. /O simply causes a
> reboot while writing data.
> Extensive testing we did at our company to pull the power plug during
> both log and checkpoint operations provides a better sense of whether
> the hardware works correctly.
> MSFT technical support was unable to provide much help with SQLIOStress.
>|||We have seen similar issues on an MSA 1000. In our case the problem is
resolved by a firmware upgrade to version 4.32 on the array controller. From
the release notes:
Fixes an issue found with SQL Server 2000 in which SQL may report the use of
stale cache
data under the following conditions:
ô'¾ Extremely heavy I/O load
ô'¾ Some percentage of MSA controller cache allocated to READ
ô'¾ Multiple small simultaneous writes to the same SCSI blocks
ô'¾ Write cache is full at the time of the request
If all of the above criteria is met SQL may report error IDâ's 605, 644 and
823 when performing subsequent reads from the same SCSI blocks
"markandersen@.evare.com" wrote:
> Over the past months I have used SQLIOStress extensively to validate
> proposed hardware going to production. In most cases, the hardware has
> failed to meet performance expectations and the SQLIOStress results
> have not mattered.
> However, today we have a piece of hardware which seems to perform very
> well, yet *fails* the SQLIOStress test. Should we care?
> Research
> --
> I called the sales rep, and they talked to their technical reps about
> it...no useful information. Nothing more than I can search on the web.
> I've contacted MSFT technical support, and they have yet to come back
> with any specific information beyond what I can find in MSFT KB.
> I've searched on the internet. Yet to find comments on why failures
> are definitive no-go's for hardware
>
> Failure details
> --
> Run SQLIOStress with /O to cause a reboot. (Note: the /O reboots the
> computer not the storage, so it seems to be dated from a time when the
> two were wired together. So we're really rebooting a Windows 2003 SP 1
> server attached to storage which is still powered.)
> Command line:
> sqliostress
> /fn:\stress.mdf /lm:\stress.ldf /S3072 /I11 /O10
> Hardware: HP - MSA-500 G2 storage array and Proliant DL585 internal
> drives. Quad HP Opteron is the machine which is using this storage.
> >From the systems engineer who rebuilt the machine this week:
> "The MSA500 G2 Firmware was upgraded from: Product ID 0x0E11E020,
> Version 1.40 to 1.52
> (http://h18023.www1.hp.com/support/files/server/us/download/23010.html).
> We are also running the latest PSP and all of the other firmware is
> being reported as up to date."
> We are running SQLIOStress with SQLServer in the background. We have
> discovered that with SQLServer in the background (dedicated 7 Gigs of
> memory out of 8 or so) we more reliably see problems.
> Configured the HP to 100% write cache.
> Run SQLIOStress. It triggers a reboot. Restart SQLIOStress to
> validate the written disk image and it generated a log file with the
> following error:
> 06/30/05 17:37:11 00003192 Verifying the integrity of the file.
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05 00003192 ERROR: Byte 1033 supposed to be [0x41] but
> [0x42] was read
> 06/30/05 17:39:05 00003192 ERROR: Byte 1034 supposed to be [0x41] but
> [0x4F] was read
> 06/30/05 17:39:05 00003192 ERROR: Byte 1035 supposed to be [0x41] but
> [0x42] was read
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05
> 00003192 ----
> 06/30/05 17:39:05 00003192 Found pattern [A] in file for page 154455.
> 06/30/05 17:39:05 00003192 Bytes read = 8192
> 06/30/05 17:39:05 00003192 Potential torn write, lost write or stale
> read may have been encountered
> 06/30/05 17:39:05
> 00003192 ----
> 06/30/05 17:39:05 00003192
> 06/30/05 17:39:05 00003192 Sector: 0 LSN: 3914551 Page:
> 154455 Address: 0xA3280000
> 06/30/05 17:39:05
> 00003192 [AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA]
> 06/30/05 17:39:05 00003192 Hex:
>
[0x414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141]
> We've also seen this with 50% read/50% write cache (using earlier
> firmware). (Upon reviewing another post, we had decided to do 100%
> write cache to better align with optimal performance and avoid possible
> failures due to read cacheing. The failure still occurred. It did not
> take many attempts to cause it.)
> Some people have suggested that a torn write is a fact of life...that
> with disks writing 512 bytes at a time, there is always the possibility
> that a portion of the page will be written. Is that true? Does that
> mean that a sqliostress failure is not news?
> (Note: we configured NTFS with 8K blocks and 64K stripes)
> Reasoning: when NT sends data to the hard drive, some may be written to
> disk before NT receives an ACK that data has been written. With a
> partial data write you have a "torn page". Hence, the error we see
> (which may or may not be a torn page?) would indicate an OS issue and
> not reflect on the hardware at all. Is this true? Or are there caches
> which guarantee not to let any data flow out to disk until the ack is
> returned to NT? (In which case a battery backed cache can ensure no
> loss of data.)
> Have other companies tested with SQLIOStress, seen failures, and still
> deployed hardware and feel good about that? Or should a failure of
> SQLIOStress be treated as the kiss of death?
> Please advise.
> Mark Andersen
>

Is SQL Server Express designed for production use?

Per the subject line :
Is SQL Server Express designed for production use?

hi Juan,

yes, it is.. are you experimenting inconveniences?

regards

|||

re:
> are you experimenting inconveniences?

None.

I was wondering, mainly, whether SQL Express differed from MSDE
...which wasn't designed for production use.

To further the question :

Express will only bind to one CPU at a time, and it cannot run queries in parallel.

Express cannot use more than 1 GB of RAM at a time for queries and data pages.
SQL Server 2005 will handle many more concurrent queries than the Express version.

No one database in Express can be larger than 4 GB.

Data mining, Data Transformation Services (DTS)
and reporting functions are not available for SQL Express.

Other functions not available in Express include clustering/mirroring,
full-text indexing/searching, SQLMail, indexed views, partitioned views and SQL Agent.

I.O.W., although it will work fine for small traffic websites,
I would have thought it isn't designed for heavy-duty production websites.

Maybe we need to distinguish between
"light traffic" and "heavy traffic" regarding SQL Express ?

Maybe I just have the wrong impression, and the limitations listed don't amount to much ?

|||

hi Juan,

Juan T. Llibre wrote:

re:
> are you experimenting inconveniences?

None.

I was wondering, mainly, whether SQL Express differed from MSDE
...which wasn't designed for production use.

actually MSDE was... it is "limited" compared to the full blown wditions of the relative product(s), but both MSDE and SQLExpress ARE ready for production..

you only have to see if they are "enought" to target your specific requirements, if the built-in limitations can be handled or have to be mandatory satisifed..

To further the question :

Express will only bind to one CPU at a time, and it cannot run queries in parallel.

Express cannot use more than 1 GB of RAM at a time for queries and data pages.
SQL Server 2005 will handle many more concurrent queries than the Express version.

No one database in Express can be larger than 4 GB.

Data mining, Data Transformation Services (DTS)

yep... that's true

and reporting functions are not available for SQL Express.

false ... the SQL Server Express Edition with Advanced Services provides (local server only) Reporting features as well

Other functions not available in Express include clustering/mirroring,

yep

full-text indexing/searching, indexed views, partitioned views

false, the very same edition with Advanced Services supports full text...

indexed views are "supported" as the engine will not throw exception, but the advanced optimizations on them will be not "used" by the optimizer...

SQLMail, and SQL Agent.

yep, but you can easely write a "kind of" replacement for Mail via simple SMTP CLR based procedure to send mail like my free and simple project, amDbobj ..

as regard SQL Agent, normal "stuffs" can be scheduled via native AT/SCHTASKS operating system scheduler, but, obviously, full blown jobs are often not completely transferrable to this kind of solution.. or you can write your own scheduler, like other devs already did

I.O.W., although it will work fine for small traffic websites,
I would have thought it isn't designed for heavy-duty production websites.

Maybe we need to distinguish between
"light traffic" and "heavy traffic" regarding SQL Express ?

Maybe I just have the wrong impression, and the limitations listed don't amount to much ?

it all dependes on the "final needs" and budget ..

I often am very satisfied with SQLExpress, but when the customer requires more "power", it's then very easy to just upgrade to the Standard edition..

regards

|||Thank you very much for your reply, Andrea.

I now have a clearer view.

Is SQL Server Express designed for production use ?

Per the subject line :

Is SQL Server Express designed for production use ?

As I know yes you can use it as databases for desktop application and is recomended to use as witness SQL server(because is free) in multiple SQL server environment.

see page below:

http://www.microsoft.com/sql/editions/express/default.mspx

Thanks

JPazgier

|||

Juan T. Llibre:

Per the subject line :

Is SQL Server Express designed for production use ?

Yes it is you can use it if you host your application Webform or Winform, the only thing not included with Express is the Agent so most SQL Server based automation you have to look for alternatives and there are many free solutions from SQL Server users. There is no Profiler but you can use the Developer edition profiler to profile your application. Try the link below for features compare in all editions. Hope this helps.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

Juan T. Llibre:

Per the subject line :

Is SQL Server Express designed for production use ?

For web applications in a shared hosting environment? No, not really.

|||

re:
>For web applications in a shared hosting environment? No, not really.

That's what I thought, but I was told by a 'softie that it is.
I'd like to have a definitive reason for this, whether it is, or not.

|||re:
> Yes it is you can use it if you host your application Webform or Winform,
> the only thing not included with Express is the Agent

To further the question :

Express will only bind to one CPU at a time, and it cannot run queries in parallel.

Express cannot use more than 1 GB of RAM at a time for queries and data pages.
SQL Server 2005 will handle many more concurrent queries than the Express version.

No one database in Express can be larger than 4 GB.

Data mining, Data Transformation Services (DTS)
and reporting functions are not available for SQL Express.

Other functions not available in Express include clustering/mirroring,
full-text indexing/searching, SQLMail, indexed views, partitioned views and SQL Agent.

I.O.W., although it will work fine for small traffic websites,
I would have thought it isn't designed for heavy-duty production websites.

Maybe we need to distinguish between
"light traffic" and "heavy traffic" regarding SQL Express ?

Maybe I just have the wrong impression, and the limitations listed don't amount to much ?|||

(Data mining, Data Transformation Services (DTS)
and reporting functions are not available for SQL Express.)

This is the calculus end it is not practical to expect it in a free edition and I would not run it even with the standard edition.

(Other functions not available in Express include clustering/mirroring,
full-text indexing/searching, SQLMail, indexed views, partitioned views and SQL Agent.)

Views are query rewrite edition is not relevant Partitioned Views are created with the UNION ALL SET operator and I don't think indexed views are affected either. Full text there is work around solution. Agent no and SQL mail is not there but the good SQL Server mail service is the Agent mail so you are not missing anything.

Parallel queries are seldom needed in OLTP(online transaction processing) and if you want all you have listed you need to host it in house with the Enterprise edition running on multi processor remember SQL Server license is per processor.

|||

Thanks, Caddre.

So, the next time I'm asked whether SQL Express is designed for production use, I should answer : yes ?

|||

Hi you are not exactly rights because in SQL Server Express SP 1 you have :

Microsoft SQL Server 2005 Express Edition Toolkit SP1
SQL Server Express Toolkit provides tools and resources to manage Microsoft SQL Server 2005 Express Edition, and to create reports by using SQL Server 2005 Reporting Services. The kit includes Connectivity Components, Business Intelligence Development Studio, Management Studio Express, and a Software Development Kit. To create reports by using Reporting Services, you must use Business Intelligence Development Studio.

Install Microsoft SQL Server 2005 Express Edition with Advanced Services(more...)

In addition to the features of SQL Server Express, certain additional capabilities are also available as part of Microsoft SQL Server 2005 Express Edition with Advanced Services (SQL Server Express). SQL Server Express with Advanced Services contains the following features:
SQL Server Management Studio Express, a graphical management tool based on SQL Server Management Studio that makes it easy to manage and administer SQL Server Express databases.
Reporting Services, an integrated report creation and design environment to create reports.
Full-Text Search, a powerful search engine for searching text-intensive data.

so you can do much more than in previous edition. check this page to see what you can get:

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

The only problem can be that Microsoft peoples say that SQL express is much slower than any other not free version of SQL Server, but based on my experience for small databases is not true, and I could not test for big because SQL express has a lot of size limits.

You can see all limitation of previous SQL Server express on page below, but SP1 remove some of this missing elements from the list:

http://msdn2.microsoft.com/en-us/library/ms165636.aspx

Thanks

JPazgier

|||

Juan T. Llibre:

Thanks, Caddre.

So, the next time I'm asked whether SQL Express is designed for production use, I should answer : yes ?

I am sorry I forgot to answer you yesterday, yes you can use Express to run small production site in house if you buy the Developer edition because you can use the Profiler and the database tuning advisor to improve performance your Express database.

Is SQL Expresss Production Ready?

I will be hosting my website in the next few months and doing more testing and I was having trouble finding a place to host the site that allows enough space for my sqlserver2005 database. I am estimating that the database will be around 40-50GB. Most hosting companies only give about 500MB for the database. Does anyone know of a company that provides more space? Also, is SQL Express production ready, or is it just for developing applications? If it is production ready, is there any "major" performace impact in using it over the full version. The reason I am asking is because hosting companies only give 500MB for a database, but alot more space for the web page. If I can put the SQLExpress file in the App_Data folder, then I will have more space. Does this even sound like it will work? Opinions are appreciated!

this is just off the top of my head, but I would think that, either more web page space, or more SQL Server space would always be available for a price, from a hosting company.

With previous hosting companies, once I'd reached the max, I was told that I could buy more space if I wanted.

|||

Thanks for the reply. From the companies I have looked at, they do offer more space but it only comes in small chucks(500MB) for an additional $5-$10 per month. I'm sure they would be more than happy to give me 50GB, but it would be hard on the wallet.

|||

I just realized that if I use SQL Express I cannot do hot backups. So I guess that is out of the question. But I'm still wondering what companies everyone else uses for hosting. Does anyone know of a place that allows for more storage for the database?

|||

A 40-50GB database is pretty large for a hosting company. You might want to consider something more along the lines of a VPS at that point.

|||

What is a VPS? To me 50G isn't that big. I mean storage is cheap these days. I work with multi-terabyte databases every day so I guess I expected a hosting company to support a 50G database lol. Thanks!!

|||

VPS = http://en.wikipedia.org/wiki/Virtual_private_server

|||

Thanks for the info. I did a google search and found many places that should work out.

|||

40-50G is a very small database when used as a data warehouse, ERP, etc. But for a typical web service that is actually extremely large. Remember that only a couple years ago that large hosting plans were for serving up 4-10GB of data transfer per MONTH. So having 40-50GB of data would be extremely odd to try and place in a shared hosing environment where the server is trying to handle 10,000+ websites.

Even a VPS may be too small for your needs, but it's definately a step up from a shared hosting plan. Of course if it is too small (or you outgrow it), it's usually trivial to move the VPS to it's own dedicated machine (or a larger machine). It's normally just copying a few files, and then changing a few configuration settings in the VPS software to allow your VPS to hog the whole box for itself.

|||

I think that VPS is a good place to start. The database will be smaller when I go live, but I expect it to grow up to 50GB in a short amount of time. I just want to make sure I have enough room and I have the flexibility to add more storage space for less cost then the $10 for 500MB that I can get with shared storage plans. Thanks for all the good info!!