SAN drives and the transaction logs

In the old days when we have physical disk drives in our SQLServers, we
always located the logs on a seperate volume from the data files for
performance reasons. In the new era of using SAN storage for the database
files, does it still make sense to use seperate logical SAN drives for the
data and log files? Or will it make any performance difference for the data
and log files to be on the same SAN drive since we don't know where the data
is really physically going 'under the covers' of the SAN technology?
SAN vendors will tell you that it doesn't make any difference. Then they
will tell you to RAID5 all the disks to lower the entry cost of the array.
I continue to use the same best practices with separate LUNs for Data and
Log files. Plus those LUNS must be from different physical disk sets in the
array. On the highest-end arrays, the SAN may move stuff around to tune
itself, but for most of us, we still have to provision the storage system
manually. Just because we go from a semi-dumb system with megabytes of
cache to a moderately bright system having gigabytes of cache does not mean
the fundamentals change.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jay Warmack" <JayWarmack@.discussions.microsoft.com> wrote in message
news:C4218672-9768-449F-AB68-95240B88828E@.microsoft.com...
> In the old days when we have physical disk drives in our SQLServers, we
> always located the logs on a seperate volume from the data files for
> performance reasons. In the new era of using SAN storage for the database
> files, does it still make sense to use seperate logical SAN drives for the
> data and log files? Or will it make any performance difference for the
> data
> and log files to be on the same SAN drive since we don't know where the
> data
> is really physically going 'under the covers' of the SAN technology?
|||"Jay Warmack" <JayWarmack@.discussions.microsoft.com> wrote in message
news:C4218672-9768-449F-AB68-95240B88828E@.microsoft.com...
> In the old days when we have physical disk drives in our SQLServers, we
> always located the logs on a seperate volume from the data files for
> performance reasons. In the new era of using SAN storage for the database
> files, does it still make sense to use seperate logical SAN drives for the
> data and log files?
Yes, it does. The SAN should be architected to provide multiple and separate
spindles.

> Or will it make any performance difference for the data
> and log files to be on the same SAN drive since we don't know where the
> data
> is really physically going 'under the covers' of the SAN technology?
Why would you not know?
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
|||I agree with what Geoff and Russ said. Just want to add that in a large
enterprise, there is often tension between what an individual app wants and
what can be effectively managed and scale from the central storage
perspective. Treating all the storage requests generically often facilitates
SAN manageability and scalability, but may be sub-optimal for a particular
app.
If you are in a smaller environment or if your app is relatively important
in the grand scheme of 'SAN things', you may have greater leverage in making
more specific storage configuration requests, and you should take full
advanatge of that.

> files, does it still make sense to use seperate logical SAN drives for the
> data and log files?
It still makes sense to have data and log on different LUNs even if these
LUNs are not from completely different spindles in the array. If you look at
the end-to-end I/O path, this alone does give you some isolation.
Linchi
"Jay Warmack" wrote:

> In the old days when we have physical disk drives in our SQLServers, we
> always located the logs on a seperate volume from the data files for
> performance reasons. In the new era of using SAN storage for the database
> files, does it still make sense to use seperate logical SAN drives for the
> data and log files? Or will it make any performance difference for the data
> and log files to be on the same SAN drive since we don't know where the data
> is really physically going 'under the covers' of the SAN technology?
|||Yes, it still does. Keep in mind that with virtualized storage this can mean
that other high write applications can be using the same drives as your SQL
Server data and log files, so a SAN can offer lower performance than locally
attached storage. Most SANs have the capability of doing Zoning which is
where you can carve out disks in your SAN which you can dedicate to your
logs, or to your data files. These disks can similarly be made RAID 10 or
RAID 5 - whatever makes most sense for your SQL Server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jay Warmack" <JayWarmack@.discussions.microsoft.com> wrote in message
news:C4218672-9768-449F-AB68-95240B88828E@.microsoft.com...
> In the old days when we have physical disk drives in our SQLServers, we
> always located the logs on a seperate volume from the data files for
> performance reasons. In the new era of using SAN storage for the database
> files, does it still make sense to use seperate logical SAN drives for the
> data and log files? Or will it make any performance difference for the
> data
> and log files to be on the same SAN drive since we don't know where the
> data
> is really physically going 'under the covers' of the SAN technology?
|||First of all, I want to say that I agree with all of the other respondents;
however, I would like to point out a few misconceptions when dealing with
consolidation, whether that may be Server and/or Storage consolidation.
So, to begin, at the nut of things, we are still working with physical
devices, from the host, through the bus, out through the network, between
the layers, and to the disks, but at the end of the day, they are still
disks.
Now, pick a disk, any disk, and let's say it's a 15K RPM drive. How many
sustained IOPS can it support? Does that change if the IO stream comes from
a single host or from an aggregated set of hosts? What if the IO is
directed by an intelligent controller?
Yes, it was rhetorical. The answer is that it makes absolutely no
difference. With slight variations among the vendors, a practical limit
would be about 150 to 200 IOPS per device, at best. Other than capacity and
rotational speed, not much has changed in disk throughput in nearly 20
years. To combat this physical limitation, RAID and controller cache was
created; thus, we stripe disks, and then we stripe again. We buy as much
memory as we can, on each of the sub-layers, and we cache. We do everything
in our power to keep from using those disks as often as we can.
This gets to the next point: consolidation. Now, let's say that we want to
build a single large SQL Server installation and consolidate all of the
little independent installations to this one. How do you size the
consolidated server?
Again, it is rhetorical. The same way as if you were building a single
large database installation. You have 4 main sub-systems to size: CPU,
Memory, Disk IO, and Network IO. The system must be built to handle the
aggregated load from all of the applications just as if they were all
directed at a single solution.
Now, consolidated systems tend to drive randomness to an extreme, which also
lowers the throughput of the underlying sub-systems.
Storage consolidation is no different. Just because we can buy larger
capacity drives and consolidate all of our disparate installations onto a
single storage fabric does not mean we have sufficient IO throughput. We
have to consider all metrics, just like system consolidation.
Unfortunately, disks capacities have grown far faster than their respective
IO limitations. 5,400 RPM drives can support about 50 random IOPS; 7,200
RPM, about 70 IOPS; 10K RPM, 100 to 120 IOPS; and 15K RPM, 150 IOPS.
What is the typical OLTP database size? What is a typical associated IOPS
for such a size?
My experience says about 1 to 10 GB, perhaps 50 to 100 GB would not be
unheard of. Anything larger and you would be needing to discuss archiving,
or at least partitioning some of the data, or you are not talking about a
strictly OLTP system anymore. Once you get into DSS and OLAP system
discusses, size and throughput start to converge; so, one does not tend to
see these problems as much on those types of systems. So, for now, let's
stay with the OLTP discussion.
Now, what about the IOPS? Well, that's the funny thing: there is no
correlation between size and usage. There may be a general linear increase
relationship, but I have seen busy, small and large, as well as idle small
and large. Nevertheless, 500 to 1,000 random IOPS would not be atypical for
any reasonably scaled solution. Coupled with the periodic Checkpoint
process, 10,000 IOPS and more would certainly not be out of the question.
In the case of server consolidation, these levels tend to be about the same;
however, the IOPS tend to reduce the burstiness and level off, but maintain
the same high IO request limits, but now, they are sustained levels.
By way of example, let us say we have 10 databases to consolidate, each
about 10 GB in size, each about 500 to 1,000 sustained random IOPS.
How many disks (internal or SAN-based) would be needed to store the data?
One, probably, perhaps 2 to 4 smaller ones. We would be talking about only
from 100 to 200 GB in aggregate.
How about the IOPS? How many disks? At least 30 to 70.
Now, after all the SAN Administrators pick their jaws up off the floor,
consider this.
What if we are NOT consolidating servers? What if, instead, we only
consolidate the storage on a SAN, but maintain the same number of 10
individual installations. If you virtualize the storage, ask yourself the
same questions. How many disks to store the data? How many disks to
support the IOPS load?
...
You better say the same number, as a minimum, and perhaps more because under
a consolidated server, the host knows about the IO requirements, but under
the Storage-only consolidation, each host is "blind" to the others, and, as
such, IO contention can increase.
If you answered any other way, then you know now exactly why your SAN is not
performing as well as dedicated arrays.
Finally, to make one last point, YOU BETTER KNOW THE UNDERLYING DISK
CONFIGURATION, you're the DBA; it's your job! You will need to spend lots
and lots of time with your SAN Administrators. I've always found bribes to
work best.
Sincerely,
Anthony Thomas

"Jay Warmack" <JayWarmack@.discussions.microsoft.com> wrote in message
news:C4218672-9768-449F-AB68-95240B88828E@.microsoft.com...
> In the old days when we have physical disk drives in our SQLServers, we
> always located the logs on a seperate volume from the data files for
> performance reasons. In the new era of using SAN storage for the database
> files, does it still make sense to use seperate logical SAN drives for the
> data and log files? Or will it make any performance difference for the
data
> and log files to be on the same SAN drive since we don't know where the
data
> is really physically going 'under the covers' of the SAN technology?

SAN Connectivity Issue

I have SQL 2000 SP3a EE on Compaq hardware. I have all user data and system
data on SAN Drives. I am noticing that even though SQL Server is set up as
automatic on start up still it doesn't stat the server automatically. I have
to manually start SQL Server service after the machine is booted up. In the
event log it shows that it had difficulties in finding SAN Drives though the
drives existed. After the boot up, I start SQL Server manually and see no
issues in starting up.
Has anyone sees this kind of issue and if so, what were the steps to resolve
this.
Thank you very much.
Hi
Looks like a Hardware/Driver issue. The fact that SQL Server can not find
the drives means that the OS has not presented them to SQL Server. Probably
it takes a bit long to find them, by then it is SQL Server's turn to be
started up as a service, and since the drives are not online, SQL Server
aborts.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:2A8660A3-6D8A-42E9-B13A-6DB3D38CDAB6@.microsoft.com...
> I have SQL 2000 SP3a EE on Compaq hardware. I have all user data and
system
> data on SAN Drives. I am noticing that even though SQL Server is set up as
> automatic on start up still it doesn't stat the server automatically. I
have
> to manually start SQL Server service after the machine is booted up. In
the
> event log it shows that it had difficulties in finding SAN Drives though
the
> drives existed. After the boot up, I start SQL Server manually and see no
> issues in starting up.
> Has anyone sees this kind of issue and if so, what were the steps to
resolve
> this.
> Thank you very much.
|||Hi Mike:
I don't think that it is a driver issue. If drivers are bad then drives
shouldn't come back at all. The fact is drives do come back after OS is fully
started and then when I start SQL Server manually, it works fine. I agree
that something is wrong with the Drives to be shown up during OS and since
drives are not present, SQL doesn't start. Problem is drives do come when I
look for them after OS is fully started so I am unsure as to what part of
software needs to be fixed.
-Mark
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Looks like a Hardware/Driver issue. The fact that SQL Server can not find
> the drives means that the OS has not presented them to SQL Server. Probably
> it takes a bit long to find them, by then it is SQL Server's turn to be
> started up as a service, and since the drives are not online, SQL Server
> aborts.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:2A8660A3-6D8A-42E9-B13A-6DB3D38CDAB6@.microsoft.com...
> system
> have
> the
> the
> resolve
>
>
|||Hi
It is not SQL Server. Next time, do not log onto the box, but start the
services manually though services applet on another computer (right click on
the local service and select connect to another computer...). If it fails,
then there is some dependency on you having to log in to get the drive to be
available.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:DE24AD09-360F-418B-9C70-22F8968B89F0@.microsoft.com...
> Hi Mike:
> I don't think that it is a driver issue. If drivers are bad then drives
> shouldn't come back at all. The fact is drives do come back after OS is
fully
> started and then when I start SQL Server manually, it works fine. I agree
> that something is wrong with the Drives to be shown up during OS and since
> drives are not present, SQL doesn't start. Problem is drives do come when
I[vbcol=seagreen]
> look for them after OS is fully started so I am unsure as to what part of
> software needs to be fixed.
> -Mark
> "Mike Epprecht (SQL MVP)" wrote:
find[vbcol=seagreen]
Probably[vbcol=seagreen]
up as[vbcol=seagreen]
I[vbcol=seagreen]
In[vbcol=seagreen]
though[vbcol=seagreen]
no[vbcol=seagreen]
|||Go to Administrative Tools/Services and setup automatic recovery for
MSSQLSERVER. Set it to restart after the first failure. Have it wait for 1
minute. See if that temporarily fixes the issue while you get the SAN issue
fixed. What type of SAN and fiber cards are you using? What version of
drivers do you have for the fiber cards?
You have any errors in the system event log?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:#RAM657MFHA.4028@.tk2msftngp13.phx.gbl...
> Hi
> It is not SQL Server. Next time, do not log onto the box, but start the
> services manually though services applet on another computer (right click
on
> the local service and select connect to another computer...). If it fails,
> then there is some dependency on you having to log in to get the drive to
be[vbcol=seagreen]
> available.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:DE24AD09-360F-418B-9C70-22F8968B89F0@.microsoft.com...
> fully
agree[vbcol=seagreen]
since[vbcol=seagreen]
when[vbcol=seagreen]
> I
of[vbcol=seagreen]
> find
> Probably
be[vbcol=seagreen]
Server[vbcol=seagreen]
> up as
automatically.[vbcol=seagreen]
> I
> In
> though
see
> no
>

SAN Connectivity Issue

I have SQL 2000 SP3a EE on Compaq hardware. I have all user data and system
data on SAN Drives. I am noticing that even though SQL Server is set up as
automatic on start up still it doesn't stat the server automatically. I have
to manually start SQL Server service after the machine is booted up. In the
event log it shows that it had difficulties in finding SAN Drives though the
drives existed. After the boot up, I start SQL Server manually and see no
issues in starting up.
Has anyone sees this kind of issue and if so, what were the steps to resolve
this.
Thank you very much.Hi
Looks like a Hardware/Driver issue. The fact that SQL Server can not find
the drives means that the OS has not presented them to SQL Server. Probably
it takes a bit long to find them, by then it is SQL Server's turn to be
started up as a service, and since the drives are not online, SQL Server
aborts.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:2A8660A3-6D8A-42E9-B13A-6DB3D38CDAB6@.microsoft.com...
> I have SQL 2000 SP3a EE on Compaq hardware. I have all user data and
system
> data on SAN Drives. I am noticing that even though SQL Server is set up as
> automatic on start up still it doesn't stat the server automatically. I
have
> to manually start SQL Server service after the machine is booted up. In
the
> event log it shows that it had difficulties in finding SAN Drives though
the
> drives existed. After the boot up, I start SQL Server manually and see no
> issues in starting up.
> Has anyone sees this kind of issue and if so, what were the steps to
resolve
> this.
> Thank you very much.|||Hi Mike:
I don't think that it is a driver issue. If drivers are bad then drives
shouldn't come back at all. The fact is drives do come back after OS is full
y
started and then when I start SQL Server manually, it works fine. I agree
that something is wrong with the Drives to be shown up during OS and since
drives are not present, SQL doesn't start. Problem is drives do come when I
look for them after OS is fully started so I am unsure as to what part of
software needs to be fixed.
-Mark
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Looks like a Hardware/Driver issue. The fact that SQL Server can not find
> the drives means that the OS has not presented them to SQL Server. Probabl
y
> it takes a bit long to find them, by then it is SQL Server's turn to be
> started up as a service, and since the drives are not online, SQL Server
> aborts.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:2A8660A3-6D8A-42E9-B13A-6DB3D38CDAB6@.microsoft.com...
> system
> have
> the
> the
> resolve
>
>|||Hi
It is not SQL Server. Next time, do not log onto the box, but start the
services manually though services applet on another computer (right click on
the local service and select connect to another computer...). If it fails,
then there is some dependency on you having to log in to get the drive to be
available.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:DE24AD09-360F-418B-9C70-22F8968B89F0@.microsoft.com...
> Hi Mike:
> I don't think that it is a driver issue. If drivers are bad then drives
> shouldn't come back at all. The fact is drives do come back after OS is
fully
> started and then when I start SQL Server manually, it works fine. I agree
> that something is wrong with the Drives to be shown up during OS and since
> drives are not present, SQL doesn't start. Problem is drives do come when
I[vbcol=seagreen]
> look for them after OS is fully started so I am unsure as to what part of
> software needs to be fixed.
> -Mark
> "Mike Epprecht (SQL MVP)" wrote:
>
find[vbcol=seagreen]
Probably[vbcol=seagreen]
up as[vbcol=seagreen]
I[vbcol=seagreen]
In[vbcol=seagreen]
though[vbcol=seagreen]
no[vbcol=seagreen]|||Go to Administrative Tools/Services and setup automatic recovery for
MSSQLSERVER. Set it to restart after the first failure. Have it wait for 1
minute. See if that temporarily fixes the issue while you get the SAN issue
fixed. What type of SAN and fiber cards are you using? What version of
drivers do you have for the fiber cards?
You have any errors in the system event log?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:#RAM657MFHA.4028@.tk2msftngp13.phx.gbl...
> Hi
> It is not SQL Server. Next time, do not log onto the box, but start the
> services manually though services applet on another computer (right click
on
> the local service and select connect to another computer...). If it fails,
> then there is some dependency on you having to log in to get the drive to
be
> available.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:DE24AD09-360F-418B-9C70-22F8968B89F0@.microsoft.com...
> fully
agree[vbcol=seagreen]
since[vbcol=seagreen]
when[vbcol=seagreen]
> I
of[vbcol=seagreen]
> find
> Probably
be[vbcol=seagreen]
Server[vbcol=seagreen]
> up as
automatically.[vbcol=seagreen]
> I
> In
> though
see[vbcol=seagreen]
> no
>

SAN Array setup

Our server team has set up an EMC Clarion SAN with 3 sets of RAID 5 arrays,
several RAID 1 arrays and other drives/arrays for snapshots. The first
server we are installing has the data going to an 80 GB slice an an 8 disk
RAID 5 array and the logs going to a 20 GB slice on a 5 disk RAID 5 array.
Eventually, several other database servers will be added to this SAN.
The first server will hold several small databases and one or more large
databases, all totaling 20 - 50 GB. Some of the large databases will be
replicates from other servers. I assume the replication will be
transactional with non updateable subscriptions. Some of the databases will
also replicate to other servers. I assume the replication for these will be
transactional with updateable subscriptions. All databases will be serving
up content for web applications.
Does anyone have experience using RAID 5 on an EMC Clarion for the log
files?
I do not know how much data will be replicated and I don't have any stats on
the servers the app was developed on. Would anyone hazard a guess as to the
MB/hour of data that may overwhelm this RAID 5 data volume?
Any educated opionions are welcome, especially if you can back it up with
some examples etc.
I avoid RAID 5 at all cost. See http://www.baarf.com/ if you are bored and
wanna read some experiences, horror stories, sillies, rants, etc..
That said, the nature of your application plays a huge role in the decision
on what RAID configuration. Stripping with parity is probably "ok" if you're
doing mostly reads and have solid backup and maintenance processes in place
(eg. swap out disks before MTBF). However, if all all possible, put your
data on a RAID 1+0. Yes, quite a bit of extra cost involved but if you're
paranoid about simultaneous disk failures and/or have high performance
requirements, definitely worth considering. I've done this stuff for 15
years and have only seen 1 CPU failure ever. I have seen a good number of
multiple drive failures either simultaneously or very close to each other.
There were 2 occurrence of a second drive failing shortly after a
replacement drive was stuck into the array and rebuilding had only just
begun. Good thing we had backups.
Transaction logs are perfectly happy sitting in its own disk or mirrored
disk for availability. I would not recommend RAID 5 for your logs. It's just
an unecessary performance hit (tlogs activities are sequential) plus it'll
use up more physical drives than a mirror anyway.
One thing you want to be careful with is what the SAN administrators are
really giving you when they assign you a LUN. There's a lot of black magic
inside a SAN box and I have met many SAN vendor reps that insist it doesn't
matter if we're getting a "slice" of an array or our own array because the
big caches. These guys obviously have not had to work with high transaction
environments with multiple workloads on the same SAN.
Btw, there are free tools available to stress your IO. See below for one
example from Microsoft.
http://www.microsoft.com/downloads/d...DisplayLang=en
joe.
"Dean" <deann@.dtn.com> wrote in message
news:%237z1llFFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> Our server team has set up an EMC Clarion SAN with 3 sets of RAID 5
> arrays,
> several RAID 1 arrays and other drives/arrays for snapshots. The first
> server we are installing has the data going to an 80 GB slice an an 8 disk
> RAID 5 array and the logs going to a 20 GB slice on a 5 disk RAID 5 array.
> Eventually, several other database servers will be added to this SAN.
> The first server will hold several small databases and one or more large
> databases, all totaling 20 - 50 GB. Some of the large databases will be
> replicates from other servers. I assume the replication will be
> transactional with non updateable subscriptions. Some of the databases
> will
> also replicate to other servers. I assume the replication for these will
> be
> transactional with updateable subscriptions. All databases will be serving
> up content for web applications.
> Does anyone have experience using RAID 5 on an EMC Clarion for the log
> files?
> I do not know how much data will be replicated and I don't have any stats
> on
> the servers the app was developed on. Would anyone hazard a guess as to
> the
> MB/hour of data that may overwhelm this RAID 5 data volume?
> Any educated opionions are welcome, especially if you can back it up with
> some examples etc.
>
|||Push back. We just went through the same issue with our local EMC technical
sales guy when we bought our CX-700.
It seems to me he tried to talk our SAN guy into going with a bunch of 4
drive RAID 5 arrays because he could show better MB/$ costs. His hope was
that they could make up for the RAID 5 overhead with their cache. When I
pushed back, he brought in their regional SQL Server specialists (that's how
they were positioned anyway). When we explained our throughput requirements
(the same ones we explained to our local tech rep), they both said, "You
need RAID 10".
"Dean" <deann@.dtn.com> wrote in message
news:%237z1llFFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> Our server team has set up an EMC Clarion SAN with 3 sets of RAID 5
arrays,
> several RAID 1 arrays and other drives/arrays for snapshots. The first
> server we are installing has the data going to an 80 GB slice an an 8 disk
> RAID 5 array and the logs going to a 20 GB slice on a 5 disk RAID 5 array.
> Eventually, several other database servers will be added to this SAN.
> The first server will hold several small databases and one or more large
> databases, all totaling 20 - 50 GB. Some of the large databases will be
> replicates from other servers. I assume the replication will be
> transactional with non updateable subscriptions. Some of the databases
will
> also replicate to other servers. I assume the replication for these will
be
> transactional with updateable subscriptions. All databases will be serving
> up content for web applications.
> Does anyone have experience using RAID 5 on an EMC Clarion for the log
> files?
> I do not know how much data will be replicated and I don't have any stats
on
> the servers the app was developed on. Would anyone hazard a guess as to
the
> MB/hour of data that may overwhelm this RAID 5 data volume?
> Any educated opionions are welcome, especially if you can back it up with
> some examples etc.
>

SAN and SQL Performance

Our company needs and will be moving to SAN environment.
I have a DELL 6650 with 16 gig of memory and 5 ative Perc 320 channels
with 15k rpm drives. There is about 5 drives on each channel in a raid
5 array. The databases are split across the channels to improve
performance because the servers are I/O bound. Moving from 8 gig to 16
gig of memory gave a 10 - 20% performance boost. The servers are Win
2003 running SQL 2k. The largest database is about 45 gig of data of
which about 5% is updated or added on daily basis and about 25% is
updated on each Sunday. The data updates come from the Access database
mentioned below.
There are SQL 2 other servers similarly configured.
There are several other smaller sql servers that I have no doubts would
benefit from the SAN.
The san would probably have at least 2 or 3 hundred gig of sql data plus
several hundred gig of other data files, exchange... Access is very
heavily used here. There are probably about 90 Access databases many
with over 500 meg of info in them.
I am not looking for SAN design. I just have concerns that the SAN will
not deliver up data as fast as the locally attached storage. Especially
when I toss in the competition from the other sql servers and the users.
I have been told that that the SAN will not only be able to replace the
current setup but will dramatically improve the current I/O performance.
Is that what you have seen in your experiences?
I know the SAN has benefits for the other data and welcome the SAN. We
run into way to many storage issues now that the SAN will solve.
Thanks for your feedback,
Ron> I am not looking for SAN design. I just have concerns that the SAN will
> not deliver up data as fast as the locally attached storage.
As long as you use fiber (or gig e) and reliable switches, you should see no
difference between local and network access. It's when you expect 10mb or
even 100mb to replicate performance of local drives where you get
disappointed.
At least that's our experience, with EMC Clariion (currently 10 TB).
You should use redundant HBAs as well, if you can...
Of course, we can't see your configuration, so other than relying on our
guessing, the best thing you can do to see if your concerns are reasonable,
is to TRY IT.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks for the feedback.
Testing is going to be done.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in
news:uC2dtG0vDHA.560@.TK2MSFTNGP11.phx.gbl:
>> I am not looking for SAN design. I just have concerns that the SAN
>> will not deliver up data as fast as the locally attached storage.
> As long as you use fiber (or gig e) and reliable switches, you should
> see no difference between local and network access. It's when you
> expect 10mb or even 100mb to replicate performance of local drives
> where you get disappointed.
> At least that's our experience, with EMC Clariion (currently 10 TB).
> You should use redundant HBAs as well, if you can...
> Of course, we can't see your configuration, so other than relying on
> our guessing, the best thing you can do to see if your concerns are
> reasonable, is to TRY IT.
>

SAN

Hi,
Planning to put a SANs in each of our data centers and have the D:\ and E:\
drives of a sql server replicating to the remote data center. HP guaranties
the proper replication of the drives with their asynchronous repliaction. I
will also have a server for disaster recover at the remote location with SQL
Server 2000 installed on it in case our main data center goes down. What
would be the most efficient way to bring the sql server up in a case of
disaster?
Thanks
--
Senior DBA
"I saw it work in a cartoon once so I am pretty sure I can do it."I'd be really careful with SQL Server working with asynchronous disk
replication. It may work out for you, but do test its behavior thoroughly. W
e
do synchronous disk replication. In that case, it is simply a matter of
ensuring the replicated volumes on the other side are visible to the host,
and start the SQL Server service. As far as SQL Server is concerned, it's th
e
same as the instance just stopped and restarted, and it would go through the
normal rollback roll forward process.
Linchi
"Sas" wrote:

> Hi,
> Planning to put a SANs in each of our data centers and have the D:\ and E:
\
> drives of a sql server replicating to the remote data center. HP guarantie
s
> the proper replication of the drives with their asynchronous repliaction.
I
> will also have a server for disaster recover at the remote location with S
QL
> Server 2000 installed on it in case our main data center goes down. What
> would be the most efficient way to bring the sql server up in a case of
> disaster?
> Thanks
> --
> --
> Senior DBA
> "I saw it work in a cartoon once so I am pretty sure I can do it."|||I will definelely test the synch/asynch replication. I probably did not
explain what I want to do properly but here is some more info.:
1. Host A at location A uses a SAN. The C:\ drive is local to the server,
the D:\ and E:\ drives are on the SAN
2. Host B is location B and has a local C:\ drive.
3. Drive D:\ and E:\ on Host A at location A are being replicated to the SAN
at location B.
What I basically want to know is HOW Host B can take over if location A is
completely out? (note that C:\ drive is not being replicated). Please give a
s
much info. as possible or how you would implement this!
Thank you for your time
--
Senior DBA
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Linchi Shea" wrote:
[vbcol=seagreen]
> I'd be really careful with SQL Server working with asynchronous disk
> replication. It may work out for you, but do test its behavior thoroughly.
We
> do synchronous disk replication. In that case, it is simply a matter of
> ensuring the replicated volumes on the other side are visible to the host,
> and start the SQL Server service. As far as SQL Server is concerned, it's
the
> same as the instance just stopped and restarted, and it would go through t
he
> normal rollback roll forward process.
> Linchi
> "Sas" wrote:
>

SAN

Hi,
Planning to put a SANs in each of our data centers and have the D:\ and E:\
drives of a sql server replicating to the remote data center. HP guaranties
the proper replication of the drives with their asynchronous repliaction. I
will also have a server for disaster recover at the remote location with SQL
Server 2000 installed on it in case our main data center goes down. What
would be the most efficient way to bring the sql server up in a case of
disaster?
Thanks
--
--
Senior DBA
"I saw it work in a cartoon once so I am pretty sure I can do it."I'd be really careful with SQL Server working with asynchronous disk
replication. It may work out for you, but do test its behavior thoroughly. We
do synchronous disk replication. In that case, it is simply a matter of
ensuring the replicated volumes on the other side are visible to the host,
and start the SQL Server service. As far as SQL Server is concerned, it's the
same as the instance just stopped and restarted, and it would go through the
normal rollback roll forward process.
Linchi
"Sas" wrote:
> Hi,
> Planning to put a SANs in each of our data centers and have the D:\ and E:\
> drives of a sql server replicating to the remote data center. HP guaranties
> the proper replication of the drives with their asynchronous repliaction. I
> will also have a server for disaster recover at the remote location with SQL
> Server 2000 installed on it in case our main data center goes down. What
> would be the most efficient way to bring the sql server up in a case of
> disaster?
> Thanks
> --
> --
> Senior DBA
> "I saw it work in a cartoon once so I am pretty sure I can do it."|||I will definelely test the synch/asynch replication. I probably did not
explain what I want to do properly but here is some more info.:
1. Host A at location A uses a SAN. The C:\ drive is local to the server,
the D:\ and E:\ drives are on the SAN
2. Host B is location B and has a local C:\ drive.
3. Drive D:\ and E:\ on Host A at location A are being replicated to the SAN
at location B.
What I basically want to know is HOW Host B can take over if location A is
completely out? (note that C:\ drive is not being replicated). Please give as
much info. as possible or how you would implement this!
Thank you for your time
--
Senior DBA
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Linchi Shea" wrote:
> I'd be really careful with SQL Server working with asynchronous disk
> replication. It may work out for you, but do test its behavior thoroughly. We
> do synchronous disk replication. In that case, it is simply a matter of
> ensuring the replicated volumes on the other side are visible to the host,
> and start the SQL Server service. As far as SQL Server is concerned, it's the
> same as the instance just stopped and restarted, and it would go through the
> normal rollback roll forward process.
> Linchi
> "Sas" wrote:
> > Hi,
> >
> > Planning to put a SANs in each of our data centers and have the D:\ and E:\
> > drives of a sql server replicating to the remote data center. HP guaranties
> > the proper replication of the drives with their asynchronous repliaction. I
> > will also have a server for disaster recover at the remote location with SQL
> > Server 2000 installed on it in case our main data center goes down. What
> > would be the most efficient way to bring the sql server up in a case of
> > disaster?
> >
> > Thanks
> >
> > --
> > --
> > Senior DBA
> > "I saw it work in a cartoon once so I am pretty sure I can do it."