VMware Design: SQL on it's own Datastore or Shared with dependant VM's
Deathmage
Banned Posts: 2,496
Hey all,
So I'm now at the point to do the last 3 of our primary physical servers, our SQL, Application, and Web Server's. Right now we only have a need for 3.5 TB's of space between the three physical servers but our Equalogic has 21.6 TB's total and we haven't even used 8 TB's yet. I'll need about 5 TB's of space for a 2 month backup retention with Acronis.
My question is this, since in our cluster these three VM's will be on one host all to themselves would it make sense to make a datastore that only these three servers will be accessing/residing since they all tie directly into the SQL database or would it make more sense to keep them on separate datastores on the SAN?
Like I've been thinking of making a datastore for the SQL server by placing the OS/Database on a RAID 5 array and then placing the logs on a RAID 10. My logic is I'm thinking about the IO/IOPS requirements if I placed the SQL server on one datastore with two other VM's. But on the flip side they are the only VM's on the network that tie directly into the same database.
I know some people that virtualize SQL blame the platform on the problem for performance but I've done tons of research on the topic and I just want to design the SAN correctly so that contention of read/writes that occur on a datastore won't cause performance issues. Even though right now, the only platform that does most of the read/writes is the SQL since that data is organic, the data on the other two servers tie directly into the database, so to me the IOPS of those servers would be low since they will all be on the same host in the cluster so it shouldn't leave the local vSwitch since the data that is live will be in memory on that host.
Am I going about this too anally, am I too paranoid about the design?
So I'm now at the point to do the last 3 of our primary physical servers, our SQL, Application, and Web Server's. Right now we only have a need for 3.5 TB's of space between the three physical servers but our Equalogic has 21.6 TB's total and we haven't even used 8 TB's yet. I'll need about 5 TB's of space for a 2 month backup retention with Acronis.
My question is this, since in our cluster these three VM's will be on one host all to themselves would it make sense to make a datastore that only these three servers will be accessing/residing since they all tie directly into the SQL database or would it make more sense to keep them on separate datastores on the SAN?
Like I've been thinking of making a datastore for the SQL server by placing the OS/Database on a RAID 5 array and then placing the logs on a RAID 10. My logic is I'm thinking about the IO/IOPS requirements if I placed the SQL server on one datastore with two other VM's. But on the flip side they are the only VM's on the network that tie directly into the same database.
I know some people that virtualize SQL blame the platform on the problem for performance but I've done tons of research on the topic and I just want to design the SAN correctly so that contention of read/writes that occur on a datastore won't cause performance issues. Even though right now, the only platform that does most of the read/writes is the SQL since that data is organic, the data on the other two servers tie directly into the database, so to me the IOPS of those servers would be low since they will all be on the same host in the cluster so it shouldn't leave the local vSwitch since the data that is live will be in memory on that host.
Am I going about this too anally, am I too paranoid about the design?
Comments
-
dave330i
Member Posts: 2,091 ■■■■■■■■■■
RAID 10 has better read/write than RAID 5. OS & DB should be on RAID 10.2018 Certification Goals: Maybe VMware Sales Cert
"Simplify, then add lightness" -Colin Chapman -
joelsfood
Member Posts: 1,027 ■■■■■■□□□□
I would definitely separate out the SQL VM from the other VMs. How granular you want to get depends a lot on how heavily loaded the SQL server is. For my highest performance SQL servers, I have separate datastores for boot drive, temp, logs and data drives, each with their own virtual scsi controller (so that Windows doesn't see the drives on a single "controller" and command queue). This allows me to tune performance differently for logs vs tempsql vs data, etc. -
jibbajabba
Member Posts: 4,317 ■■■■■■■■□□
Have a look at the official VMWare Document:My own knowledge base made public: http://open902.com
-
kj0
Member Posts: 767
Grab Michael Webster's book Amazon.com: Virtualizing SQL Server with VMware: Doing IT Right (VMware Press Technology) (9780321927750): Michael Corey, Jeff Szastak, Michael Webster: BooksI have separate datastores for boot drive, temp, logs and data drives, each with their own virtual scsi controller (so that Windows doesn't see the drives on a single "controller" and command queue).
Also check out his blog posts. Long White Virtual Clouds | all things vmware, cloud and virtualizing business critical applications -
Deathmage
Banned Posts: 2,496
Thanks guys, much appreciated, just ordered that book off Amazon.
- will read that blog tonight after doing Extended/Named ACL's on the new perdy 3750G's I just got for the lab. 
RAID 10 has better read/write than RAID 5. OS & DB should be on RAID 10.
been doing the hard math, the cluster right now is on both of the R720xd's but on the local array, so I'm now moving them one-by-one to the SAN. I settled on RAID 10 after reading a few white papers on the subject today on Equalogic PS 4100x's and SQL. we managed to get all we wanted for $35k, so the 4100x is like the lowest end model of SAN's from Dell, hopefully it's enough (for now) although I couldn't get the switching fabric so these campus-based Dell 2824's (eeek, I know, so not powerful enough) will need to tough it out. I was promised I could get 4 Dell N3048 switches in 6 months, so the network fabric will need to wait, for now...
It gives me 9.2 TB's of space and I only have a need for 7.2 TB's atm. But this is also going to reduce, since the old SQL server had 7.9 TB's of space on it in a RAID 5, so I've made that into a archive server, and that will be for cold data and the file server on the SAN will be for hot data. Instead of all the data being on the file server, thus making the size of the file server in terms of storage much smaller, so I'm looking at like 5.6 TB's needed once all is said and done.I would definitely separate out the SQL VM from the other VMs. How granular you want to get depends a lot on how heavily loaded the SQL server is. For my highest performance SQL servers, I have separate datastores for boot drive, temp, logs and data drives, each with their own virtual scsi controller (so that Windows doesn't see the drives on a single "controller" and command queue). This allows me to tune performance differently for logs vs tempsql vs data, etc.
Currently the SQL is on the local array in one datastore but has multiple vmdk's, since the server prior to me virtualizing it, to fix a ton of the issues on the server I made 3 partitions; one for swap, one for the databases, and one for the logs. It was to me the best way I knew how to make it faster. So now that it's on the local datastore on the R720xd that is shared by the utility and application server only. I'm weighting the benefits of moving the SQL VM to it's own datastore or keeping it on the same datastore as the two VM's (mentioned a second ago) that are dependent on the SQL database. To me, keeping the VM's on the same datastore means less seek time if it was on a separate datastore altogether.
I mean the total IOPS of the SQL is 900 to 1200 at peak and the IOPS of the RAID 10 is like 3500 so I should be good...
I don't have much expereince with tuning SQL, but my co-worker who is a DB admin says you can tweak the crap out of SQL for years to get it performing better...... one thing were doing is compressing the database, we don't think it was EVER done....we have solid backups with Acronis to a 12 TB NAS atm so were fine in-case it breaks something.
thanks for the input joelsfood, I just want to make it built correctly the 1st time around instead of x months down the road have to fix something. So feedback like this is awesome.jibbajabba wrote: »Have a look at the official VMWare Document:
Koodos, going to read this on the Kindle while at the gym.