Has anyone ever seen this fatal error?

We are getting the following error after execcuting an SP
on one of our clusters:
Server: Msg 7105, Level 22, State 6, Line 8
Page (1:16750), slot 4 for text, ntext, or image node does
not exist.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
We are running SQL Server 2000 with SP3.
DBCC CHECKDB showed a problem with an index which we fixed
but the error still shows when we run the SP. DBCC
CHECKDB now shows no problems.
Is there any other way to track down this problem and/or
repair it?To resolve this problem, obtain the lates service pack for
Microsoft SQL Server 2000.
To work around the problem, either:
- Insert a dummy row in the table to import into so that
the table is not empty, and then delete the dummy row
after you import the data.
-or-
- Do not use the TABLOCK hint.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>We are getting the following error after execcuting an SP
>on one of our clusters:
>Server: Msg 7105, Level 22, State 6, Line 8
>Page (1:16750), slot 4 for text, ntext, or image node
does
>not exist.
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>We are running SQL Server 2000 with SP3.
>DBCC CHECKDB showed a problem with an index which we
fixed
>but the error still shows when we run the SP. DBCC
>CHECKDB now shows no problems.
>Is there any other way to track down this problem and/or
>repair it?
>
>.
>

Hardware Logical Drives or OS Level Partitions?

Is it better to divide an array into multiple partitions (or volumes) at the
OS level or into multiple logical drives at the hardware level? Does it mak
e
any difference to track alignment? Will one give greater future flexibility
over the other?
I have to install SQL Server on an HP Proliant server with 6 drives bays - I
would prefer more, but that is what I have available. I plan to create 3
RAID 1 arrays using the HP Array Configuration Manager to use as follows:
Array 1 (disks 0 & 1)
C:\ OS
S:\ SQL Server Installation
T:\ TempDB
Array 2 (disks 2 & 3)
P:\ Page File
D:\ Data
Array 3 (disks 4 & 5)
L:\ Transaction Logs
X:\ Backups
All partitions will be formatted with NTFS. I plan a stripe and cluster
size of 64KB for the data and TempDB partitions - all the others will be
default sizes. DB backups will be made to a local drive and then backed up
to tape over the network.
This configuration gives good separation for resillience. It should reduce
file fragmentation and I hope will give reasonable performance.
Thanks for any commentsWhy not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
partitions to split stff up if you want.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Partitioning at the OS level does nothing to enhance or degrade performance.
The underlying RAID container has exactly the same IO capacity no matter how
you partition it. RAID containers can and do segment IO loads and can have
significant impact on performance.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||thanks Geoff
by RAID container I take it you mean a logical drive created at hearware
level with the array.
"Geoff N. Hiten" wrote:

> Partitioning at the OS level does nothing to enhance or degrade performanc
e.
> The underlying RAID container has exactly the same IO capacity no matter h
ow
> you partition it. RAID containers can and do segment IO loads and can hav
e
> significant impact on performance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>|||Thanks Jason
I did consider this, but thought I would be better with the separation. Do
you think 1 RAID 10 array would perform better than 2 RAID 1?
"jason" wrote:

> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still crea
te
> partitions to split stff up if you want.
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>|||Correct.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:D1EA04BC-EA2F-4F42-85B5-4057C9231E5C@.microsoft.com...[vbcol=seagreen]
> thanks Geoff
> by RAID container I take it you mean a logical drive created at hearware
> level with the array.
> "Geoff N. Hiten" wrote:
>|||You said "DB backups will be made to a local drive and then backed up to
tape over the network."
I don't know what way you'll use to copy your backups thru your network
however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
backup a database to a tape which is attached to a remote machine even if it
would be in the same network. The backup tape must be connected directly to
the SQL Server server to backup a database to tape.
Also, storing the OS and SQL Server binaries on the same drive would be OK
If tempdb is used in your environment frequently then you may consider
putting it on a dedicated disk which could be (if you don't need redundancy
or if you don't have enough resource for RAID1) RAID0.
However as Geoff mentioned, it would not make any difference to separate a
disk logically. You need physical disks to gain performance benefit for this
purpose.
You will want to give enough physical file space to your Transaction Log
file and Data files if you want less fragmentation.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Thanks Ekrem
Our backup plan is to run SQL backups to disk, then backup these files using
an enterprise backup solution along with system state etc. This eliminates
some of the additional complexities of backing up across the network (the
backup is less likely to fail if there are network problems or the other
server isn't available) and also means we have a local backup if we need it
for a partial restore or corrupt database.
I would love to put TempDB on a separate disk, but that is not possible in
this server as it only has 6 drive bays. I think separating logs and data
probably has a higher priority. I would hesitate to put TempDB on RAID 0 as
it still needs to be present, even if there is no data to preserve, or SQL
Server will fail.
We desperately need to replace some very old hardware right now and we
already have this server. Buying replacement hardware is a project for the
future - that box will have more drives!
"Ekrem ?nsoy" wrote:

> You said "DB backups will be made to a local drive and then backed up to
> tape over the network."
> I don't know what way you'll use to copy your backups thru your network
> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
to
> backup a database to a tape which is attached to a remote machine even if
it
> would be in the same network. The backup tape must be connected directly t
o
> the SQL Server server to backup a database to tape.
> Also, storing the OS and SQL Server binaries on the same drive would be OK
> If tempdb is used in your environment frequently then you may consider
> putting it on a dedicated disk which could be (if you don't need redundanc
y
> or if you don't have enough resource for RAID1) RAID0.
> However as Geoff mentioned, it would not make any difference to separate a
> disk logically. You need physical disks to gain performance benefit for th
is
> purpose.
> You will want to give enough physical file space to your Transaction Log
> file and Data files if you want less fragmentation.
> --
> Ekrem ?nsoy
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>|||Data and Log files have more priority for sure.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...[vbcol=seagreen]
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem ?nsoy" wrote:
>|||Yes especially with the limited amount of drives. You are separating the
data and logs but you are also dividing the max throughput. BTW, in a small
hp config like this, I would set the stripe size in the ACU at 64k and the
cache to 0% read and 100% write(providing you have enough RAM). Sector align
with diskpart using a 128k offset as well.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:2BD76934-6814-46D7-BA51-EDBF09438BF0@.microsoft.com...[vbcol=seagreen]
> Thanks Jason
> I did consider this, but thought I would be better with the separation.
> Do
> you think 1 RAID 10 array would perform better than 2 RAID 1?
> "jason" wrote:
>

Hardware Logical Drives or OS Level Partitions?

Is it better to divide an array into multiple partitions (or volumes) at the
OS level or into multiple logical drives at the hardware level? Does it make
any difference to track alignment? Will one give greater future flexibility
over the other?
I have to install SQL Server on an HP Proliant server with 6 drives bays - I
would prefer more, but that is what I have available. I plan to create 3
RAID 1 arrays using the HP Array Configuration Manager to use as follows:
Array 1 (disks 0 & 1)
C:\ OS
S:\ SQL Server Installation
T:\ TempDB
Array 2 (disks 2 & 3)
P:\ Page File
D:\ Data
Array 3 (disks 4 & 5)
L:\ Transaction Logs
X:\ Backups
All partitions will be formatted with NTFS. I plan a stripe and cluster
size of 64KB for the data and TempDB partitions - all the others will be
default sizes. DB backups will be made to a local drive and then backed up
to tape over the network.
This configuration gives good separation for resillience. It should reduce
file fragmentation and I hope will give reasonable performance.
Thanks for any comments
Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
partitions to split stff up if you want.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||Partitioning at the OS level does nothing to enhance or degrade performance.
The underlying RAID container has exactly the same IO capacity no matter how
you partition it. RAID containers can and do segment IO loads and can have
significant impact on performance.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||thanks Geoff
by RAID container I take it you mean a logical drive created at hearware
level with the array.
"Geoff N. Hiten" wrote:

> Partitioning at the OS level does nothing to enhance or degrade performance.
> The underlying RAID container has exactly the same IO capacity no matter how
> you partition it. RAID containers can and do segment IO loads and can have
> significant impact on performance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Thanks Jason
I did consider this, but thought I would be better with the separation. Do
you think 1 RAID 10 array would perform better than 2 RAID 1?
"jason" wrote:

> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
> partitions to split stff up if you want.
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Correct.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:D1EA04BC-EA2F-4F42-85B5-4057C9231E5C@.microsoft.com...[vbcol=seagreen]
> thanks Geoff
> by RAID container I take it you mean a logical drive created at hearware
> level with the array.
> "Geoff N. Hiten" wrote:
|||You said "DB backups will be made to a local drive and then backed up to
tape over the network."
I don't know what way you'll use to copy your backups thru your network
however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
backup a database to a tape which is attached to a remote machine even if it
would be in the same network. The backup tape must be connected directly to
the SQL Server server to backup a database to tape.
Also, storing the OS and SQL Server binaries on the same drive would be OK
If tempdb is used in your environment frequently then you may consider
putting it on a dedicated disk which could be (if you don't need redundancy
or if you don't have enough resource for RAID1) RAID0.
However as Geoff mentioned, it would not make any difference to separate a
disk logically. You need physical disks to gain performance benefit for this
purpose.
You will want to give enough physical file space to your Transaction Log
file and Data files if you want less fragmentation.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||Thanks Ekrem
Our backup plan is to run SQL backups to disk, then backup these files using
an enterprise backup solution along with system state etc. This eliminates
some of the additional complexities of backing up across the network (the
backup is less likely to fail if there are network problems or the other
server isn't available) and also means we have a local backup if we need it
for a partial restore or corrupt database.
I would love to put TempDB on a separate disk, but that is not possible in
this server as it only has 6 drive bays. I think separating logs and data
probably has a higher priority. I would hesitate to put TempDB on RAID 0 as
it still needs to be present, even if there is no data to preserve, or SQL
Server will fail.
We desperately need to replace some very old hardware right now and we
already have this server. Buying replacement hardware is a project for the
future - that box will have more drives!
"Ekrem ?nsoy" wrote:

> You said "DB backups will be made to a local drive and then backed up to
> tape over the network."
> I don't know what way you'll use to copy your backups thru your network
> however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
> backup a database to a tape which is attached to a remote machine even if it
> would be in the same network. The backup tape must be connected directly to
> the SQL Server server to backup a database to tape.
> Also, storing the OS and SQL Server binaries on the same drive would be OK
> If tempdb is used in your environment frequently then you may consider
> putting it on a dedicated disk which could be (if you don't need redundancy
> or if you don't have enough resource for RAID1) RAID0.
> However as Geoff mentioned, it would not make any difference to separate a
> disk logically. You need physical disks to gain performance benefit for this
> purpose.
> You will want to give enough physical file space to your Transaction Log
> file and Data files if you want less fragmentation.
> --
> Ekrem ?nsoy
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Data and Log files have more priority for sure.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...[vbcol=seagreen]
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem ?nsoy" wrote:
|||Yes especially with the limited amount of drives. You are separating the
data and logs but you are also dividing the max throughput. BTW, in a small
hp config like this, I would set the stripe size in the ACU at 64k and the
cache to 0% read and 100% write(providing you have enough RAM). Sector align
with diskpart using a 128k offset as well.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:2BD76934-6814-46D7-BA51-EDBF09438BF0@.microsoft.com...[vbcol=seagreen]
> Thanks Jason
> I did consider this, but thought I would be better with the separation.
> Do
> you think 1 RAID 10 array would perform better than 2 RAID 1?
> "jason" wrote:

Hardware Logical Drives or OS Level Partitions?

Is it better to divide an array into multiple partitions (or volumes) at the
OS level or into multiple logical drives at the hardware level? Does it make
any difference to track alignment? Will one give greater future flexibility
over the other?
I have to install SQL Server on an HP Proliant server with 6 drives bays - I
would prefer more, but that is what I have available. I plan to create 3
RAID 1 arrays using the HP Array Configuration Manager to use as follows:
Array 1 (disks 0 & 1)
C:\ OS
S:\ SQL Server Installation
T:\ TempDB
Array 2 (disks 2 & 3)
P:\ Page File
D:\ Data
Array 3 (disks 4 & 5)
L:\ Transaction Logs
X:\ Backups
All partitions will be formatted with NTFS. I plan a stripe and cluster
size of 64KB for the data and TempDB partitions - all the others will be
default sizes. DB backups will be made to a local drive and then backed up
to tape over the network.
This configuration gives good separation for resillience. It should reduce
file fragmentation and I hope will give reasonable performance.
Thanks for any commentsWhy not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
partitions to split stff up if you want.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Partitioning at the OS level does nothing to enhance or degrade performance.
The underlying RAID container has exactly the same IO capacity no matter how
you partition it. RAID containers can and do segment IO loads and can have
significant impact on performance.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||thanks Geoff
by RAID container I take it you mean a logical drive created at hearware
level with the array.
"Geoff N. Hiten" wrote:
> Partitioning at the OS level does nothing to enhance or degrade performance.
> The underlying RAID container has exactly the same IO capacity no matter how
> you partition it. RAID containers can and do segment IO loads and can have
> significant impact on performance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Thanks Jason
I did consider this, but thought I would be better with the separation. Do
you think 1 RAID 10 array would perform better than 2 RAID 1?
"jason" wrote:
> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
> partitions to split stff up if you want.
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Correct.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:D1EA04BC-EA2F-4F42-85B5-4057C9231E5C@.microsoft.com...
> thanks Geoff
> by RAID container I take it you mean a logical drive created at hearware
> level with the array.
> "Geoff N. Hiten" wrote:
>> Partitioning at the OS level does nothing to enhance or degrade
>> performance.
>> The underlying RAID container has exactly the same IO capacity no matter
>> how
>> you partition it. RAID containers can and do segment IO loads and can
>> have
>> significant impact on performance.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments
>>|||You said "DB backups will be made to a local drive and then backed up to
tape over the network."
I don't know what way you'll use to copy your backups thru your network
however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
backup a database to a tape which is attached to a remote machine even if it
would be in the same network. The backup tape must be connected directly to
the SQL Server server to backup a database to tape.
Also, storing the OS and SQL Server binaries on the same drive would be OK
If tempdb is used in your environment frequently then you may consider
putting it on a dedicated disk which could be (if you don't need redundancy
or if you don't have enough resource for RAID1) RAID0.
However as Geoff mentioned, it would not make any difference to separate a
disk logically. You need physical disks to gain performance benefit for this
purpose.
You will want to give enough physical file space to your Transaction Log
file and Data files if you want less fragmentation.
--
Ekrem Ã?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Thanks Ekrem
Our backup plan is to run SQL backups to disk, then backup these files using
an enterprise backup solution along with system state etc. This eliminates
some of the additional complexities of backing up across the network (the
backup is less likely to fail if there are network problems or the other
server isn't available) and also means we have a local backup if we need it
for a partial restore or corrupt database.
I would love to put TempDB on a separate disk, but that is not possible in
this server as it only has 6 drive bays. I think separating logs and data
probably has a higher priority. I would hesitate to put TempDB on RAID 0 as
it still needs to be present, even if there is no data to preserve, or SQL
Server will fail.
We desperately need to replace some very old hardware right now and we
already have this server. Buying replacement hardware is a project for the
future - that box will have more drives!
"Ekrem Ã?nsoy" wrote:
> You said "DB backups will be made to a local drive and then backed up to
> tape over the network."
> I don't know what way you'll use to copy your backups thru your network
> however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
> backup a database to a tape which is attached to a remote machine even if it
> would be in the same network. The backup tape must be connected directly to
> the SQL Server server to backup a database to tape.
> Also, storing the OS and SQL Server binaries on the same drive would be OK
> If tempdb is used in your environment frequently then you may consider
> putting it on a dedicated disk which could be (if you don't need redundancy
> or if you don't have enough resource for RAID1) RAID0.
> However as Geoff mentioned, it would not make any difference to separate a
> disk logically. You need physical disks to gain performance benefit for this
> purpose.
> You will want to give enough physical file space to your Transaction Log
> file and Data files if you want less fragmentation.
> --
> Ekrem Ã?nsoy
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Data and Log files have more priority for sure.
--
Ekrem Ã?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem Ã?nsoy" wrote:
>> You said "DB backups will be made to a local drive and then backed up to
>> tape over the network."
>> I don't know what way you'll use to copy your backups thru your network
>> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
>> to
>> backup a database to a tape which is attached to a remote machine even if
>> it
>> would be in the same network. The backup tape must be connected directly
>> to
>> the SQL Server server to backup a database to tape.
>> Also, storing the OS and SQL Server binaries on the same drive would be
>> OK
>> If tempdb is used in your environment frequently then you may consider
>> putting it on a dedicated disk which could be (if you don't need
>> redundancy
>> or if you don't have enough resource for RAID1) RAID0.
>> However as Geoff mentioned, it would not make any difference to separate
>> a
>> disk logically. You need physical disks to gain performance benefit for
>> this
>> purpose.
>> You will want to give enough physical file space to your Transaction Log
>> file and Data files if you want less fragmentation.
>> --
>> Ekrem Ã?nsoy
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Yes especially with the limited amount of drives. You are separating the
data and logs but you are also dividing the max throughput. BTW, in a small
hp config like this, I would set the stripe size in the ACU at 64k and the
cache to 0% read and 100% write(providing you have enough RAM). Sector align
with diskpart using a 128k offset as well.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:2BD76934-6814-46D7-BA51-EDBF09438BF0@.microsoft.com...
> Thanks Jason
> I did consider this, but thought I would be better with the separation.
> Do
> you think 1 RAID 10 array would perform better than 2 RAID 1?
> "jason" wrote:
>> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still
>> create
>> partitions to split stff up if you want.
>>
>> --
>> Jason Massie
>> www: http://statisticsio.com
>> rss: http://feeds.feedburner.com/statisticsio
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Agreed on the relative priority of how to split data files.
I do disagree on backing up and restoring across the network. I do it all
the time, even with third-party compression tools, and have very little
trouble. Once you get the permissions correct, it is a snap. Here is a KB
article on how to do it with SQL 2000. The same permission rules apply to
SQL 2005. Getting the backup onto another system quickly to safeguard
against hardware failure is worth the extra hassle of setting it up.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem Ã?nsoy" wrote:
>> You said "DB backups will be made to a local drive and then backed up to
>> tape over the network."
>> I don't know what way you'll use to copy your backups thru your network
>> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
>> to
>> backup a database to a tape which is attached to a remote machine even if
>> it
>> would be in the same network. The backup tape must be connected directly
>> to
>> the SQL Server server to backup a database to tape.
>> Also, storing the OS and SQL Server binaries on the same drive would be
>> OK
>> If tempdb is used in your environment frequently then you may consider
>> putting it on a dedicated disk which could be (if you don't need
>> redundancy
>> or if you don't have enough resource for RAID1) RAID0.
>> However as Geoff mentioned, it would not make any difference to separate
>> a
>> disk logically. You need physical disks to gain performance benefit for
>> this
>> purpose.
>> You will want to give enough physical file space to your Transaction Log
>> file and Data files if you want less fragmentation.
>> --
>> Ekrem Ã?nsoy
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Thanks guys, this is all really helpful stuff.
The whole area of storage config for SQL Server seems to be one where there
are lots of differing views out there on the web. It would be nice to have
the time to study the I/O patterns in detail over several weeks and
experiment with different setups, but it's difficult to justify that amount
of time in a busy schedule.
"Geoff N. Hiten" wrote:
> Agreed on the relative priority of how to split data files.
> I do disagree on backing up and restoring across the network. I do it all
> the time, even with third-party compression tools, and have very little
> trouble. Once you get the permissions correct, it is a snap. Here is a KB
> article on how to do it with SQL 2000. The same permission rules apply to
> SQL 2005. Getting the backup onto another system quickly to safeguard
> against hardware failure is worth the extra hassle of setting it up.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/kb/555128
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> > Thanks Ekrem
> >
> > Our backup plan is to run SQL backups to disk, then backup these files
> > using
> > an enterprise backup solution along with system state etc. This
> > eliminates
> > some of the additional complexities of backing up across the network (the
> > backup is less likely to fail if there are network problems or the other
> > server isn't available) and also means we have a local backup if we need
> > it
> > for a partial restore or corrupt database.
> >
> > I would love to put TempDB on a separate disk, but that is not possible in
> > this server as it only has 6 drive bays. I think separating logs and data
> > probably has a higher priority. I would hesitate to put TempDB on RAID 0
> > as
> > it still needs to be present, even if there is no data to preserve, or SQL
> > Server will fail.
> >
> > We desperately need to replace some very old hardware right now and we
> > already have this server. Buying replacement hardware is a project for
> > the
> > future - that box will have more drives!
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> >> You said "DB backups will be made to a local drive and then backed up to
> >> tape over the network."
> >>
> >> I don't know what way you'll use to copy your backups thru your network
> >> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
> >> to
> >> backup a database to a tape which is attached to a remote machine even if
> >> it
> >> would be in the same network. The backup tape must be connected directly
> >> to
> >> the SQL Server server to backup a database to tape.
> >>
> >> Also, storing the OS and SQL Server binaries on the same drive would be
> >> OK
> >> If tempdb is used in your environment frequently then you may consider
> >> putting it on a dedicated disk which could be (if you don't need
> >> redundancy
> >> or if you don't have enough resource for RAID1) RAID0.
> >>
> >> However as Geoff mentioned, it would not make any difference to separate
> >> a
> >> disk logically. You need physical disks to gain performance benefit for
> >> this
> >> purpose.
> >>
> >> You will want to give enough physical file space to your Transaction Log
> >> file and Data files if you want less fragmentation.
> >>
> >> --
> >> Ekrem Ã?nsoy
> >>
> >>
> >>
> >> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> >> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> >> > Is it better to divide an array into multiple partitions (or volumes)
> >> > at
> >> > the
> >> > OS level or into multiple logical drives at the hardware level? Does
> >> > it
> >> > make
> >> > any difference to track alignment? Will one give greater future
> >> > flexibility
> >> > over the other?
> >> >
> >> > I have to install SQL Server on an HP Proliant server with 6 drives
> >> > bays -
> >> > I
> >> > would prefer more, but that is what I have available. I plan to create
> >> > 3
> >> > RAID 1 arrays using the HP Array Configuration Manager to use as
> >> > follows:
> >> >
> >> > Array 1 (disks 0 & 1)
> >> > C:\ OS
> >> > S:\ SQL Server Installation
> >> > T:\ TempDB
> >> >
> >> > Array 2 (disks 2 & 3)
> >> > P:\ Page File
> >> > D:\ Data
> >> >
> >> > Array 3 (disks 4 & 5)
> >> > L:\ Transaction Logs
> >> > X:\ Backups
> >> >
> >> > All partitions will be formatted with NTFS. I plan a stripe and
> >> > cluster
> >> > size of 64KB for the data and TempDB partitions - all the others will
> >> > be
> >> > default sizes. DB backups will be made to a local drive and then
> >> > backed
> >> > up
> >> > to tape over the network.
> >> >
> >> > This configuration gives good separation for resillience. It should
> >> > reduce
> >> > file fragmentation and I hope will give reasonable performance.
> >> >
> >> > Thanks for any comments
> >>
>

handling failover at the application level with Sql2005 Mirroring

We're in the process of writing an application that we want to work with a
synchronous mirrored Sql 2005 database and a witness.
As I currently understand it the best way to handle failover with this setup
is to catch the server not available error and then retry the operation.
Any open transaction will be rolled back (in effect) so we need to retry the
entire transaction.
We have complicated transactional processes at our business logic layer, so
we have quite a few places where we begin a transaction, carry out a number
of operations that may involve multiple database calls, and then commit or
rollback on error.
In order to add this retry functionality it looks like I need to add a
try-catch around every call at the business logic layer as it needs to wrap
the transaction. This is possible, but messy.
Is there a better way of doing this? Ideally the handing of the retry
should be at the data access layer, but as the transaction in progress when
the failover happens will be lost it doesn't look possible to handle this in
individual data commands.
Any ideas?
Keith Henry
This isn't unique to Database Mirroring. You would have to do that in every
case where you are dealing with a failover configuration. There is no logic
that basically says "retry". You have to code this all yourself.
When the mirror fails over, you will get a disconnect and any transactions
in flight will be rolled back. The only thing your applications can take
advantage of if they are using the new MDAC library. In this situation,
there is code carried that will cache both the principal and mirror. Your
application can simply reconnect to the principal and the MDAC layer will
transparently redirect the connection and requests to the mirror.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Keith Henry" <k.henry@.link-hrsystems.com> wrote in message
news:u2mk5jjLGHA.984@.tk2msftngp13.phx.gbl...
> We're in the process of writing an application that we want to work with a
> synchronous mirrored Sql 2005 database and a witness.
> As I currently understand it the best way to handle failover with this
> setup is to catch the server not available error and then retry the
> operation. Any open transaction will be rolled back (in effect) so we need
> to retry the entire transaction.
> We have complicated transactional processes at our business logic layer,
> so we have quite a few places where we begin a transaction, carry out a
> number of operations that may involve multiple database calls, and then
> commit or rollback on error.
>
> In order to add this retry functionality it looks like I need to add a
> try-catch around every call at the business logic layer as it needs to
> wrap the transaction. This is possible, but messy.
>
> Is there a better way of doing this? Ideally the handing of the retry
> should be at the data access layer, but as the transaction in progress
> when the failover happens will be lost it doesn't look possible to handle
> this in individual data commands.
> Any ideas?
> Keith Henry
>
|||Thanks,
I guess we'll have to go with the retry from the top level then.
Keith Henry
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:udYhiKnLGHA.208@.tk2msftngp13.phx.gbl...
> This isn't unique to Database Mirroring. You would have to do that in
> every case where you are dealing with a failover configuration. There is
> no logic that basically says "retry". You have to code this all yourself.
> When the mirror fails over, you will get a disconnect and any transactions
> in flight will be rolled back. The only thing your applications can take
> advantage of if they are using the new MDAC library. In this situation,
> there is code carried that will cache both the principal and mirror. Your
> application can simply reconnect to the principal and the MDAC layer will
> transparently redirect the connection and requests to the mirror.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Keith Henry" <k.henry@.link-hrsystems.com> wrote in message
> news:u2mk5jjLGHA.984@.tk2msftngp13.phx.gbl...
>

Handling error level 16

Hello there
I have some store procedure that run on many views by cursor. One of the
views is failed on error level 16. And therefore the batch is being
terminated.
Is there a way not to terminate the procedure and continue?Roy Goldhammer (roy@.hotmail.com) writes:
>Date: Thu, 20 Apr 2006 10:04:29 +0200
Hey, I am in that time zone, and my watch is only 9:30!

> I have some store procedure that run on many views by cursor. One of the
> views is failed on error level 16. And therefore the batch is being
> terminated.
> Is there a way not to terminate the procedure and continue?
If you are on SQL 2000, you are basically out of luck. Some errors in SQL
Server terminates the batch, and there is no easy way to handle it.
If you are on SQL 2005, you can use the new TRY-CATCH construct.
If your aim is to run sp_refreshview on these views, search on Google
for FMTONLY + sp_refreshview. I saw a tip on that recently, but I don't
recall exactly in which newsgroup.
For more information about error handling in SQL Server, see
http://www.sommarskog.se/error-handling-I.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Whell Erland.
I'm using sql server 2000.
The main error that comes is error converting.
and it is level 16 and therefore the statement is being terminated
is there something i can do to solve it?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AB61A39F364Yazorman@.127.0.0.1...
> Roy Goldhammer (roy@.hotmail.com) writes:
> Hey, I am in that time zone, and my watch is only 9:30!
>
> If you are on SQL 2000, you are basically out of luck. Some errors in SQL
> Server terminates the batch, and there is no easy way to handle it.
> If you are on SQL 2005, you can use the new TRY-CATCH construct.
> If your aim is to run sp_refreshview on these views, search on Google
> for FMTONLY + sp_refreshview. I saw a tip on that recently, but I don't
> recall exactly in which newsgroup.
> For more information about error handling in SQL Server, see
> http://www.sommarskog.se/error-handling-I.html
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Roy
What does the script do? Do you convert dates?
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OI5duAFZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Whell Erland.
> I'm using sql server 2000.
> The main error that comes is error converting.
> and it is level 16 and therefore the statement is being terminated
> is there something i can do to solve it?
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97AB61A39F364Yazorman@.127.0.0.1...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> I'm using sql server 2000.
> The main error that comes is error converting.
> and it is level 16 and therefore the statement is being terminated
> is there something i can do to solve it?
The level does not matter. Error handling in SQL Server is inconsistent.
In my previous post I suggested a search, and gave a link to that article.
Rather than asking again, without telling what you are doing, why not try
the references you got?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Handling encryption at the application level

Developing an application, which is handling encrypting for a SQL server 2005 DB at the application level, using symmetric AES encryption. After being encrypted the data is being sent to the SQL server 2005 DB to be inserted. This is what I want to accomplish. But I have one or two questions as I am going through the research for my project.

1. Encrypting the data shouldn’t be that big a deal, but can SQL server 2005 handle to insert these data? If yes how? I am thinking just simple SQL INSERT statements?

2. When the data are inserted can SQL server, as it supports AES encryption, through the DBMS decrypt the data as they have been encrypted else where and then perform specific functions? (by specific function I just mean any function that may have to run, it could be any function one could think of)

3. How difficult would it be to take the encrypted data from SQL server to the application and then at the application level decrypt these?

Any help on the above questions is highly appreciated or if people have any comment on the subject I would be more than happy to receive these.

Thank you

Kenn Kikkenborg

1. Yes this is supported. Encryption is currently done manually. For more info, please check Laurentiu's blog:

http://blogs.msdn.com/lcris/archive/2005/06/09/427523.aspx

2. I'm not sure I understand the question. This seems like you are encrypting data externally to SQL Server? If this is the case then the two systems are not exactly compatible. It is possible to make it work, but this is somewhat tricky and involved, especially because symmetric keys cannot currently be imported or exported. If you are primarily concerned about transport level security (i.e. sending information in the clear over a connection) I suggest you explore using SSL instead:

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

3. Please see #2

Please let me know if you have any further questions or would like clarification on the above.

Sung

|||

Thanks for the follow-up to my question

As to point 2 in my question. As SQL server 2005 should do its internal encryption by calling the Windows CryptoAPI. I was wondering if SQL server was configured to handle AES encryption on specific columns, and I would find a way to use the CryptoAPI, that I then could decrypt the data from SQL server AND my own application. The possibility to run batch-scripts on the DB would then be available, as the BD would be able to decrypt the data. But I haven’t found a way to do this and I REALLY need to find out if this is possible as it would be an enormous gain for both the DB and application.

If you have any other ideas or comment then I would highly appreciate these…!!!

Thank you

Kenn Kikkenborg

|||

I'm still not sure why you need to call Windows CAPI directly against the database. Why do you want your application to handle decryption?

If this is because you want to run batch operations, you should still be able to do so by using views and triggers. For example, if you wanted to automatically encrypt data going into a table, you can simply create an insert trigger. If you want to automatically decrypt data, you can create a view that decrypts the columns and select from the view instead of from the table.

If instead you are concered about sensitive information being passed over your connecion in plaintext, the best solution would be to enable SSL.

Can you perhaps provide some use cases for your scenario?

Thanks,

Sung

Handling encryption at the application level

Developing an application, which is handling encrypting for a SQL server 2005 DB at the application level, using symmetric AES encryption. After being encrypted the data is being sent to the SQL server 2005 DB to be inserted. This is what I want to accomplish. But I have one or two questions as I am going through the research for my project.

1. Encrypting the data shouldn’t be that big a deal, but can SQL server 2005 handle to insert these data? If yes how? I am thinking just simple SQL INSERT statements.

2. When the data are inserted can SQL server, as it support AES encryption, through the DBMS decrypt the data as they have been encrypted else where and then perform specific functions? (by specific function I just mean any function that may have to run, it could be any function one could think of)

3. How difficult would it be to take the encrypted data from SQL server to the application and then at the application level decrypt these?

Any help on the above questions is highly appreciated or if people have any comment on the subject I would be more than happy to receive these.

Thank you

Kenn Kikkenborg

Hi,

1. AFter encryption the values you will pass to SQL Server will just be strings. SQL Server will not care about the content of the strings. As you are using client side encryption in your solution, the encrypted value will be handled by SQL Server as you would have entered a "real" value.

2.I don′t know if you can use the server side introduced decryption methods to decrypt the client side encrypted data, that should be worth a try. But if you expose your decryption functionality in an assembly, you can create a CLR function using this logic to decrypt the data.

3.If the data was not encrypted by SQL Server and only your client application, the same rules as above in 1. would apply. If you encrypted the data in SQL Server and want to decrypt it in your client application you would have to check the thoughts of 2.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de