Handle Error: multiple

Showing posts with label multiple. Show all posts

Wednesday, March 28, 2012

Having Multiple ranges in the query

Hi All,
I have a problem to get this work done. I want to optimize this by
using multiple ranges in having cluase..
for the understanding i'm writing down the whole Query here ...
----
--
SELECT
DISTINCT VAR2 AS TotalCount,
Max(NetTime) as Time
FROM
XYZ
WHERE
(Talk > 0) AND (DateTime = '2006-05-15')
GROUP BY
VAR2, NetTime
Having
((NetTime > 0) AND (NetTime < 11))
----
--
I want to have data in different ranges like this in below:
0-10 11-20 21-30 31-40 41-50 51-60
61-120 Above 120
-- -- -- -- -- --
-- -- -- --
1779 1410 1109 633 569 560
1013 798
I would highly appriciate for any quick response
TIA
-- Atif Iqbal --Hi
Probably you will be better off doing such reports in the client side
"Atif Iqbal" <aatif.iqbal@.gmail.com> wrote in message
news:1147777689.050841.290500@.g10g2000cwb.googlegroups.com...
> Hi All,
> I have a problem to get this work done. I want to optimize this by
> using multiple ranges in having cluase..
> for the understanding i'm writing down the whole Query here ...
> ----
--
> SELECT
> DISTINCT VAR2 AS TotalCount,
> Max(NetTime) as Time
> FROM
> XYZ
> WHERE
> (Talk > 0) AND (DateTime = '2006-05-15')
> GROUP BY
> VAR2, NetTime
> Having
> ((NetTime > 0) AND (NetTime < 11))
> ----
--
> I want to have data in different ranges like this in below:
> 0-10 11-20 21-30 31-40 41-50 51-60
> 61-120 Above 120
> -- -- -- -- -- --
> -- -- -- --
> 1779 1410 1109 633 569 560
> 1013 798
> I would highly appriciate for any quick response
>
> TIA
> -- Atif Iqbal --
>|||This is best done client side, but something along these lines may help, if
you insist on doing it on the database. Dividing the nettime by 10 and
rounding down will give you a row for each range, although the ranges will
run from 0 to 9 for values ranging from 0 to less than 10, 10 to 19, etc.
It is not the entire solution, but may get you most of the way there. I
don't think you want the distinct in there, since the group by and count
should be handling that.
SELECT
VAR2 AS TotalCount
, count(var2)
, floor(NetTime/10) as TimeRangeLow
, floor(NetTime/10)+9 as TimeRangeHigh
FROM
XYZ
WHERE
(Talk > 0) AND (DateTime = '2006-05-15')
GROUP BY
VAR2, floor(NetTime/10)
"Atif Iqbal" <aatif.iqbal@.gmail.com> wrote in message
news:1147777689.050841.290500@.g10g2000cwb.googlegroups.com...
> Hi All,
> I have a problem to get this work done. I want to optimize this by
> using multiple ranges in having cluase..
> for the understanding i'm writing down the whole Query here ...
> ----
--
> SELECT
> DISTINCT VAR2 AS TotalCount,
> Max(NetTime) as Time
> FROM
> XYZ
> WHERE
> (Talk > 0) AND (DateTime = '2006-05-15')
> GROUP BY
> VAR2, NetTime
> Having
> ((NetTime > 0) AND (NetTime < 11))
> ----
--
> I want to have data in different ranges like this in below:
> 0-10 11-20 21-30 31-40 41-50 51-60
> 61-120 Above 120
> -- -- -- -- -- --
> -- -- -- --
> 1779 1410 1109 633 569 560
> 1013 798
> I would highly appriciate for any quick response
>
> TIA
> -- Atif Iqbal --
>

Monday, March 26, 2012

Having a MAJOR brain fart here...

Guys I'm sorry to be asking such a routine question...

I'm having trouble figuring out how to make this function dynamic enough to handle multiple insert statements.

1public int Add()23{45string SQL;67SQL ="INSERT INTO [BuildingInterior] (PropertyID, CeilingHeight, " +89"LoadingDocks, PassengerElevators, FreightElevators, PassengerEscalators, " +1011"FireSprinklersID, SecurityCameras, SmokeDetection, FireAlarms, " +1213"GasDetection, SecureAccess, HeatTypeID, AirConditioningID, " +1415"AirExchange, InternetAccessID, InteriorDescription) " +1617"VALUES ( @.PropertyID, @.CeilingHeight, " +1819"@.LoadingDocks, @.PassengerElevators, @.FreightElevators, @.PassengerEscalators, " +2021"@.FireSprinklersID, @.SecurityCameras, @.SmokeDetection, @.FireAlarms, " +2223"@.GasDetection, @.SecureAccess, @.HeatTypeID, @.AirConditioningID, " +2425"@.AirExchange, @.InternetAccessID, @.InteriorDescription)";2627PropertyDB myConnection =new PropertyDB();2829SqlConnection conn = myConnection.GetOpenConnection();3031SqlCommand cmd =new SqlCommand(SQL, conn);3233cmd.Parameters.Add("@.PropertyID", SqlDbType.Int).Value = PropertyID;3435cmd.Parameters.Add("@.CeilingHeight", SqlDbType.NVarChar, 50).Value = CeilingHeight;3637cmd.Parameters.Add("@.LoadingDocks", SqlDbType.NVarChar, 50).Value = LoadingDocks;3839cmd.Parameters.Add("@.PassengerElevators", SqlDbType.NVarChar, 50).Value = PassengerElevators;4041cmd.Parameters.Add("@.FreightElevators", SqlDbType.NVarChar, 50).Value = FreightElevators;4243cmd.Parameters.Add("@.PassengerEscalators", SqlDbType.NVarChar, 50).Value = PassengerEscalators;4445cmd.Parameters.Add("@.FireSprinklersID", SqlDbType.Int).Value = FireSprinklersID;4647cmd.Parameters.Add("@.SecurityCameras", SqlDbType.NVarChar, 50).Value = SecurityCameras;4849cmd.Parameters.Add("@.SecurityAlarms", SqlDbType.NVarChar, 50).Value = SecurityAlarms;5051cmd.Parameters.Add("@.SmokeDetection", SqlDbType.NVarChar, 50).Value = SmokeDetection;5253cmd.Parameters.Add("@.FireAlarms", SqlDbType.NVarChar, 50).Value = FireAlarms;5455cmd.Parameters.Add("@.GasDetection", SqlDbType.NVarChar, 50).Value = GasDetection;5657cmd.Parameters.Add("@.SecureAccess", SqlDbType.NVarChar, 50).Value = SecureAccess;5859cmd.Parameters.Add("@.HeatTypeID", SqlDbType.Int).Value = HeatTypeID;6061cmd.Parameters.Add("@.AirConditioningID", SqlDbType.Int).Value = AirConditioningID;6263cmd.Parameters.Add("@.AirExchange", SqlDbType.NVarChar, 50).Value = AirExchange;6465cmd.Parameters.Add("@.InternetAccessID", SqlDbType.Int).Value = InternetAccessID;6667cmd.Parameters.Add("@.InteriorDescription", SqlDbType.NVarChar, 50).Value = InteriorDescription;6869cmd.ExecuteNonQuery();7071cmd.CommandText ="SELECT @.@.IDENTITY";7273this.BuildingInteriorID = Int32.Parse(cmd.ExecuteScalar().ToString());7475conn.Close();7677return this.BuildingInteriorID;7879}80

Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array?

Any comments are greatly welcomed.

Hi eterry,

As far as I can see there is no better way to assign value by iterating through each column like this.

But I think there is one thing that you can improve in your code. You can put SELECT SCOPE_IDENTITY() (use SCOPE_IDENTITY() instead of @.@.IDENTITY in SQL Server)at the end of the INSERT statement. Seperate them with a ";", like

INSERT INTO ......; SELECT SCOPE_IDENTITY()

Then you can use ExecuteScalar to have the 2 statement executed at once. This will have 2 advantages.

1. Save a roundtrip to the server and gain better performance.
2. Prevent the concurrency issues. In your code, if 2 users do this together, there is possibility that they will get wrong identity if one's execution is interrupted by the other.

HTH. If this does not answer you question, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||

Thanks for the tip Kevin.

I was hoping that there would be a better way of doing, but it is what it is.

Monday, March 12, 2012

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
> >>
>

Friday, March 9, 2012

Hardware - Multiple Instances of SQL in a cluster

Can anyone advise as to the best practices for installing mulitple instances
of SQL into a cluster? Specifically as to the harddrive quantity and
configuration.
I already have 1 instance in a cluster and need to add more to it. So, my
cluster already has a drive for OS (Mirrored), a drive for MSDTC (Mirrored),
a drive for the quorum (Mirrored) and a drive for the database and
transaction logs (Raid5) (these are not high usage, so 1 drive for db and
logs should be more than sufficient).
I obviously need to add more hard drives to the cluster, but how many per
instance? I assume that I don't need more drives for MSDTC? Howabout the
quorum - is that one per instance? or is it just the databses ?
Thanks
Setesh
You should think of each virtual server as a separate real server. Decide
the disk layout as you would any stand-alone server. It takes at least one
physical disk to 'anchor' the virtual server.
You only need one Quorum disk and one MSDTC instance per cluster.
Geoff N. Hiten
Microsoft SQL Server MVP
"Setesh" <setesh@.nospam.NO> wrote in message
news:e177C7fZFHA.2076@.TK2MSFTNGP15.phx.gbl...
> Can anyone advise as to the best practices for installing mulitple
> instances of SQL into a cluster? Specifically as to the harddrive
> quantity and configuration.
> I already have 1 instance in a cluster and need to add more to it. So, my
> cluster already has a drive for OS (Mirrored), a drive for MSDTC
> (Mirrored), a drive for the quorum (Mirrored) and a drive for the database
> and transaction logs (Raid5) (these are not high usage, so 1 drive for db
> and logs should be more than sufficient).
> I obviously need to add more hard drives to the cluster, but how many per
> instance? I assume that I don't need more drives for MSDTC? Howabout the
> quorum - is that one per instance? or is it just the databses ?
> Thanks
> Setesh
>
|||Setesh
Here is some good information on SQL Clustering and Instances. In short you
will need additional disks only for SQL. The cluster quorum is a single
resource that is created when you install the cluster. You also have to plan
for each node to be able to scale to acceptable performance if a node has to
run more than one instance if their is a failure.
http://msdn.microsoft.com/library/de...ar_cs_9i5u.asp
Also if your installing a Named instance of SQL 2000 on a Windows 2003
cluster you will need to follow this article, setup will fail everytime until
you do. I had to learn this the hard way

, my loss is your gain...good
luck!
http://support.microsoft.com/?kbid=815431
Regards
CT
"Setesh" wrote:

> Can anyone advise as to the best practices for installing mulitple instances
> of SQL into a cluster? Specifically as to the harddrive quantity and
> configuration.
> I already have 1 instance in a cluster and need to add more to it. So, my
> cluster already has a drive for OS (Mirrored), a drive for MSDTC (Mirrored),
> a drive for the quorum (Mirrored) and a drive for the database and
> transaction logs (Raid5) (these are not high usage, so 1 drive for db and
> logs should be more than sufficient).
> I obviously need to add more hard drives to the cluster, but how many per
> instance? I assume that I don't need more drives for MSDTC? Howabout the
> quorum - is that one per instance? or is it just the databses ?
> Thanks
> Setesh
>
>
|||Thanks for the info guys, especially the info on installing a named instance
on a 2003 cluster - thats probably saved me hours of work

Cheers
S.
"Charles Tolento" <CharlesTolento@.discussions.microsoft.com> wrote in
message news:F696B9E9-FCB9-481A-8541-5CD4FFE666B7@.microsoft.com...[vbcol=seagreen]
> Setesh
> Here is some good information on SQL Clustering and Instances. In short
> you
> will need additional disks only for SQL. The cluster quorum is a single
> resource that is created when you install the cluster. You also have to
> plan
> for each node to be able to scale to acceptable performance if a node has
> to
> run more than one instance if their is a failure.
> http://msdn.microsoft.com/library/de...ar_cs_9i5u.asp
> Also if your installing a Named instance of SQL 2000 on a Windows 2003
> cluster you will need to follow this article, setup will fail everytime
> until
> you do. I had to learn this the hard way

, my loss is your gain...good
> luck!
> http://support.microsoft.com/?kbid=815431
> Regards
> CT
> "Setesh" wrote:

Friday, February 24, 2012

handling columns with multiple values

I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:

Key

NotANormalizedCol

1324, 5124, 5435,5467

423, 23, 5345

52334, 53443, 1224

12, 4, 1243,66

is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.

I am pretty new to this, so I apologize if this is a trivial questions

Look at http://www.sommarskog.se/arrays-in-sql.html|||Hi,

I once wrote a function for that which can be found here in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320221&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Wednesday, March 28, 2012

Having Multiple ranges in the query

Monday, March 26, 2012

Having a MAJOR brain fart here...

Monday, March 12, 2012

Hardware Logical Drives or OS Level Partitions?

Hardware Logical Drives or OS Level Partitions?

Hardware Logical Drives or OS Level Partitions?

Friday, March 9, 2012

Hardware - Multiple Instances of SQL in a cluster

Friday, February 24, 2012

handling columns with multiple values

Handle Error

Blog Archive

About Me