Hardware design suggestion

I'm implementing a brand new project that consists in a soft-realtime application that executes scheduled operations (tcp commands) stored on a SqlServer 2005 express db. DB and app are always on the same workstation. Let's say that the workload is about 40000 events per day, with periods of activity and more idle periods. The DB is filled by external applications that provides insert/update using ad-hoc sprocs and providing only the 'preedceding event'. The major performance problem I see is given by the fact that each time an event is inserted the sproc must rebuild all the scheduled times (I've build an optimization that uses service broker as to update asynchronously the scheduled times). The application does not requires any special calculation and is very lightweight... it only fetches the db and sends tcp commands. The only 'hard work' is to guarantee the exact time for sending the commands.

Now I have to design the required hardware and I expect some tip from you on how to proceed to build a test workload and how to translate it into 'minimum hardware specification'. Consider that safety is not importand since system are redundant in other ways.
My questions are

- Should I put OS, tempdb, database and logs each on a separate disk array ? My idea is to have a two-disk RAID0 for OS and logfiles, and a n-disk RAID0 for tempdb and appdb

- How to extimate the number of disks to have in the RAID ?

- Should I also consider processor ? I have the idea that processor is not very important while I should pay attention to bus and memory bottlenecks: so I've thought to use DDR2 RAM with a FSB of 1066 MHz and a single dual or quad core cpu, so discarding XEON multiprocessor systems that requires FBDIMM, that AFAIK are slower that DDR2.

- any other suggestion ?

The process you are describing sounds like it could be very intensive. You may want to have others take a very close look at your design to make specific feedback on ways you could make this more efficient.

Regardless, it is very difficult with the information provided to give you a hardware recommendation. One thing I can recommend is you avoid the use of RAID 0 for this. With RAID 0, a single disk failure will cause you to lose the array.

Good luck,
Bryan Smith

|||

I would not use RAID 0 for anything for a project like this. If you lose any disks in a RAID 0 array, you are down.

I would put OS and page file on RAID 1, TempDB on a separate RAID 1, AppDB on separate RAID 10 or 1, and Log files on a separate RAID 10 or 1.

The Intel Tulsa MP 3.4 GHz Xeon (with 16MB of L3 cache) performs very well on SQL Server 2005. If you don't need four CPU's, I would look at the newer, Core2 Duo based CPU's, like the Xeon 5160. Make sure you have enough RAM to ensure you don't have memory pressure (which will also reduce I/O pressure). I would suggest 4GB minimum.

|||I see that both of you have suggested to avoid RAID0, and I want to clarify a point: in our system performance is more important than security on a single machine since the system is fully redundant in the sense that we have more machine that runs in parallel, each one with the same scheduling and performs the same task, plus an 'arbiter' that selects the active machine. So If I loose a disk I have another machine that is fully mirrored to the first one... a sort of RAID1 between machines.

Anyway I'm building a test environment and I expect some sort of failsafe RAID on production machines.
|||

If you have that kind of money to spend, go with RAID 10 (1+0). Even with what you have stated above, I would not recommend RAID 0.

B.

Hardware design suggestion

I'm implementing a brand new project that consists in a soft-realtime application that executes scheduled operations (tcp commands) stored on a SqlServer 2005 express db. DB and app are always on the same workstation. Let's say that the workload is about 40000 events per day, with periods of activity and more idle periods. The DB is filled by external applications that provides insert/update using ad-hoc sprocs and providing only the 'preedceding event'. The major performance problem I see is given by the fact that each time an event is inserted the sproc must rebuild all the scheduled times (I've build an optimization that uses service broker as to update asynchronously the scheduled times). The application does not requires any special calculation and is very lightweight... it only fetches the db and sends tcp commands. The only 'hard work' is to guarantee the exact time for sending the commands.

Now I have to design the required hardware and I expect some tip from you on how to proceed to build a test workload and how to translate it into 'minimum hardware specification'. Consider that safety is not importand since system are redundant in other ways.
My questions are

- Should I put OS, tempdb, database and logs each on a separate disk array ? My idea is to have a two-disk RAID0 for OS and logfiles, and a n-disk RAID0 for tempdb and appdb

- How to extimate the number of disks to have in the RAID ?

- Should I also consider processor ? I have the idea that processor is not very important while I should pay attention to bus and memory bottlenecks: so I've thought to use DDR2 RAM with a FSB of 1066 MHz and a single dual or quad core cpu, so discarding XEON multiprocessor systems that requires FBDIMM, that AFAIK are slower that DDR2.

- any other suggestion ?

The process you are describing sounds like it could be very intensive. You may want to have others take a very close look at your design to make specific feedback on ways you could make this more efficient.

Regardless, it is very difficult with the information provided to give you a hardware recommendation. One thing I can recommend is you avoid the use of RAID 0 for this. With RAID 0, a single disk failure will cause you to lose the array.

Good luck,
Bryan Smith

|||

I would not use RAID 0 for anything for a project like this. If you lose any disks in a RAID 0 array, you are down.

I would put OS and page file on RAID 1, TempDB on a separate RAID 1, AppDB on separate RAID 10 or 1, and Log files on a separate RAID 10 or 1.

The Intel Tulsa MP 3.4 GHz Xeon (with 16MB of L3 cache) performs very well on SQL Server 2005. If you don't need four CPU's, I would look at the newer, Core2 Duo based CPU's, like the Xeon 5160. Make sure you have enough RAM to ensure you don't have memory pressure (which will also reduce I/O pressure). I would suggest 4GB minimum.

|||I see that both of you have suggested to avoid RAID0, and I want to clarify a point: in our system performance is more important than security on a single machine since the system is fully redundant in the sense that we have more machine that runs in parallel, each one with the same scheduling and performs the same task, plus an 'arbiter' that selects the active machine. So If I loose a disk I have another machine that is fully mirrored to the first one... a sort of RAID1 between machines.

Anyway I'm building a test environment and I expect some sort of failsafe RAID on production machines.
|||

If you have that kind of money to spend, go with RAID 10 (1+0). Even with what you have stated above, I would not recommend RAID 0.

B.

HA requirements for Devs

What should Operations ask from Devs to consider when writing their code to
handle High Availability ?
On Feb 12, 10:35 am, "Hassan" <has...@.hotmail.com> wrote:
> What should Operations ask from Devs to consider when writing their code to
> handle High Availability ?
Make sure that they dont hard code the server name or the IP ... The
code should pick up theses variables from an ini file... cause when
the primary server fails & you want it to point to the
secondary ..... all you need to do is to inform the users to change
the IP or the server name to start using the application ...
|||If the applications are expected to gracefully handle a failover, developers
will need to include the appropriate retry code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uq9upemTHHA.2256@.TK2MSFTNGP02.phx.gbl...
> What should Operations ask from Devs to consider when writing their code
> to handle High Availability ?
>
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:074A70E2-830C-48E2-93E5-330E8F24E84E@.microsoft.com...
> If the applications are expected to gracefully handle a failover,
> developers will need to include the appropriate retry code.
>
Most HA failover scenarios appear like a brief server outage to the
application. Retrying transactions and hiding the fact that an outage
occurred is sometimes an option, but it's difficult to code and test, and if
the outage is not _very_ brief this strategy will fail anyway. More
critical is making sure that the application resumes functioning correctly
after a brief server outage.
I would say it's acceptable to fail all the pending transactions and give
the users an error message. But when the users try again after the outage,
the application needs to work. For instance if the application holds open
connections to the database, it needs to close and re-open the connections
on certain errors. Especially important is ensuring that any server
applications or services resume gracefully, and don't require administrative
intervention.
David
|||Thanks for adding this David. I agree that an error message during the is
failover is usually acceptable in the front-end and that it particularly
important that middle-tier service apps handle the brief failover outage
without manual intervention in order to maximize availability. Retrying
transactions can be tricky unless the app is architected with this in mind.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OOJgzNrTHHA.600@.TK2MSFTNGP05.phx.gbl...
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:074A70E2-830C-48E2-93E5-330E8F24E84E@.microsoft.com...
>
> Most HA failover scenarios appear like a brief server outage to the
> application. Retrying transactions and hiding the fact that an outage
> occurred is sometimes an option, but it's difficult to code and test, and
> if the outage is not _very_ brief this strategy will fail anyway. More
> critical is making sure that the application resumes functioning correctly
> after a brief server outage.
> I would say it's acceptable to fail all the pending transactions and give
> the users an error message. But when the users try again after the
> outage, the application needs to work. For instance if the application
> holds open connections to the database, it needs to close and re-open the
> connections on certain errors. Especially important is ensuring that any
> server applications or services resume gracefully, and don't require
> administrative intervention.
> David

HA requirements for Devs

What should Operations ask from Devs to consider when writing their code to
handle High Availability ?On Feb 12, 10:35 am, "Hassan" <has...@.hotmail.com> wrote:
> What should Operations ask from Devs to consider when writing their code t
o
> handle High Availability ?
Make sure that they dont hard code the server name or the IP ... The
code should pick up theses variables from an ini file... cause when
the primary server fails & you want it to point to the
secondary ..... all you need to do is to inform the users to change
the IP or the server name to start using the application ...|||If the applications are expected to gracefully handle a failover, developers
will need to include the appropriate retry code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uq9upemTHHA.2256@.TK2MSFTNGP02.phx.gbl...
> What should Operations ask from Devs to consider when writing their code
> to handle High Availability ?
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:074A70E2-830C-48E2-93E5-330E8F24E84E@.microsoft.com...
> If the applications are expected to gracefully handle a failover,
> developers will need to include the appropriate retry code.
>
Most HA failover scenarios appear like a brief server outage to the
application. Retrying transactions and hiding the fact that an outage
occurred is sometimes an option, but it's difficult to code and test, and if
the outage is not _very_ brief this strategy will fail anyway. More
critical is making sure that the application resumes functioning correctly
after a brief server outage.
I would say it's acceptable to fail all the pending transactions and give
the users an error message. But when the users try again after the outage,
the application needs to work. For instance if the application holds open
connections to the database, it needs to close and re-open the connections
on certain errors. Especially important is ensuring that any server
applications or services resume gracefully, and don't require administrative
intervention.
David|||Thanks for adding this David. I agree that an error message during the is
failover is usually acceptable in the front-end and that it particularly
important that middle-tier service apps handle the brief failover outage
without manual intervention in order to maximize availability. Retrying
transactions can be tricky unless the app is architected with this in mind.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OOJgzNrTHHA.600@.TK2MSFTNGP05.phx.gbl...
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:074A70E2-830C-48E2-93E5-330E8F24E84E@.microsoft.com...
>
> Most HA failover scenarios appear like a brief server outage to the
> application. Retrying transactions and hiding the fact that an outage
> occurred is sometimes an option, but it's difficult to code and test, and
> if the outage is not _very_ brief this strategy will fail anyway. More
> critical is making sure that the application resumes functioning correctly
> after a brief server outage.
> I would say it's acceptable to fail all the pending transactions and give
> the users an error message. But when the users try again after the
> outage, the application needs to work. For instance if the application
> holds open connections to the database, it needs to close and re-open the
> connections on certain errors. Especially important is ensuring that any
> server applications or services resume gracefully, and don't require
> administrative intervention.
> David

HA requirements for Devs

What should Operations ask from Devs to consider when writing their code to
handle High Availability ?On Feb 12, 10:35 am, "Hassan" <has...@.hotmail.com> wrote:
> What should Operations ask from Devs to consider when writing their code to
> handle High Availability ?
Make sure that they dont hard code the server name or the IP ... The
code should pick up theses variables from an ini file... cause when
the primary server fails & you want it to point to the
secondary ..... all you need to do is to inform the users to change
the IP or the server name to start using the application ...|||If the applications are expected to gracefully handle a failover, developers
will need to include the appropriate retry code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uq9upemTHHA.2256@.TK2MSFTNGP02.phx.gbl...
> What should Operations ask from Devs to consider when writing their code
> to handle High Availability ?
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:074A70E2-830C-48E2-93E5-330E8F24E84E@.microsoft.com...
> If the applications are expected to gracefully handle a failover,
> developers will need to include the appropriate retry code.
>
Most HA failover scenarios appear like a brief server outage to the
application. Retrying transactions and hiding the fact that an outage
occurred is sometimes an option, but it's difficult to code and test, and if
the outage is not _very_ brief this strategy will fail anyway. More
critical is making sure that the application resumes functioning correctly
after a brief server outage.
I would say it's acceptable to fail all the pending transactions and give
the users an error message. But when the users try again after the outage,
the application needs to work. For instance if the application holds open
connections to the database, it needs to close and re-open the connections
on certain errors. Especially important is ensuring that any server
applications or services resume gracefully, and don't require administrative
intervention.
David|||Thanks for adding this David. I agree that an error message during the is
failover is usually acceptable in the front-end and that it particularly
important that middle-tier service apps handle the brief failover outage
without manual intervention in order to maximize availability. Retrying
transactions can be tricky unless the app is architected with this in mind.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OOJgzNrTHHA.600@.TK2MSFTNGP05.phx.gbl...
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:074A70E2-830C-48E2-93E5-330E8F24E84E@.microsoft.com...
>> If the applications are expected to gracefully handle a failover,
>> developers will need to include the appropriate retry code.
>>
>
> Most HA failover scenarios appear like a brief server outage to the
> application. Retrying transactions and hiding the fact that an outage
> occurred is sometimes an option, but it's difficult to code and test, and
> if the outage is not _very_ brief this strategy will fail anyway. More
> critical is making sure that the application resumes functioning correctly
> after a brief server outage.
> I would say it's acceptable to fail all the pending transactions and give
> the users an error message. But when the users try again after the
> outage, the application needs to work. For instance if the application
> holds open connections to the database, it needs to close and re-open the
> connections on certain errors. Especially important is ensuring that any
> server applications or services resume gracefully, and don't require
> administrative intervention.
> David