InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 00:45:30 GMT20http://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]george sibbald (1/21/2009)[/b][hr]Perry, I'll find out the exact versions and LUN configuration if only because I want to know and am a little worried we might be missing a trick here.[/quote]cool, this info would enable us all to give you a better opinionWed, 21 Jan 2009 13:44:28 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxPerry, I'll find out the exact versions and LUN configuration if only because I want to know and am a little worried we might be missing a trick here.I would be pretty sure most of the other vms would be application servers. We have not virtualised many SQL servers and those we have are small 3rd party apps that run quite happily on a physical server with a C drive for OS and an E for the databases, data and log and backup (with TSM to offsite). This one app of that type did not perform on vmware would be the busiest of these small apps we support.I am quite interested in making use of virtualisation where possible, though I admit my main driver is the DR advantages it offers.Wed, 21 Jan 2009 12:29:14 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Jonathan Kehayias (1/11/2009)[/b][hr]One other thing to test for your larger database is the effect of parallelism waits CXPACKET on performance. My experience with VM's is that parallel processing is a performance killer rather than a performance enhancer. The reason for this is that the VM host software is configured to manage context switching and the multiple threads are no longer going against multiple processors necessarily. Generally speaking, I see much better performance from my VM's be setting the Max Degree of Parallelism to 1. If your 1TB+ database currently benefits from parallel processing, you definately want to test what the performance will be like under a VM.[/quote]Please make sure that you read my entire statement above. I am not saying to always set maxdop 1, but monitor it. If you find high level of CXPacket waits, then you are having parallel bottlenecking. The only servers I have ever had issues with this are virtual, and it generally on the larger 100GB+ databases. I rarely see CXPacket waits on servers with DB's that are small, no matter how many databases there are on the server. You have to test this to know for certain whether it affects you.Wed, 21 Jan 2009 11:33:04 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxColin, i have never encountered changing the max degree of parallelism either.[quote][b]george sibbald (1/21/2009)[/b][hr]the article mentioned that VM streamlines the DR process, but for that to be fully true the SAN would have to be mirrored as well, yes?[/quote]yes, ideally.[quote][b]george sibbald (1/21/2009)[/b][hr]for what its worth we have hit performance problems on VMWARE, this was with a fairly small (20GB ) but heavily used database. I was not involved in the design of the solution (sigh) but I understand it is an up to date version of vmware[/quote]what version exactly?[quote][b]george sibbald (1/21/2009)[/b][hr] but all vms on the box were sharing the same LUN, so the problem was likely the SAN set up?[/quote]all VM's sharing the same LUN? I'm guessing they werent all sql vm's? How many vm's and what type of services (file, application, sql)? As we know for performance you would want at least 3 separate storage areas for sql (OS, data and logs) thats without considering any othe rvm's that were hosted.I would use processor affinity in VMWare with caution as cpu resource assignments could surpass the physical resources available, a classic example of this is virtualising a citrix server. Its also important to only use the minimum cpu resources and only implement SMP where it is actually used (SQL server for example)Wed, 21 Jan 2009 10:43:59 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxthanks for the replies.The VM would have had either 2 or 4 cores (cannot remember for sure) with maxdop = 0, so parallel queries would have been possible.We are using SRDF for SAN replication, just wanted to be sure without that the DR setup is not complete.I don't know too much on the details of the VM builds, but will make it my business to find out., I know they average about 10 VMs per host.I'm still getting to grips with this so excuse my ignorance, but due to the way the LUN is attached to the host it is not possible to failover a single VM in a DR situation, its the whole Host or nothing (well I am told it is possible but complicated so SAN guys not keen to do it and it would have to be done put of hours). therefore to get quick HA for a single SQL VM on the host I am having to also logship to another VM in the other data centre. Is this type of belt and braces approach typical?Wed, 21 Jan 2009 10:05:31 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]colin Leversuch-Roberts (1/21/2009)[/b][hr]much as I dislike VMs I have never encountered turning off parallelism, that could be a serious hit and I'm pretty sure this is untrue. I guess some vms may be able to allocate virtual cpus or may leave HT turned on, in which case this is not the way to configure for sql server. In all the VMs I've used cores are allocated to each vm.[/quote]If you allocate cores to each VM directly, this isn't a problem, but then if you do a vMotion of the server, those settings don't follow the server. Even then, you have to manage this for all of your VM's on the host, or you still can have context switching issues between VM's. In our environment, this isn't something the server team is going to do consistently.Wed, 21 Jan 2009 08:24:15 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]george sibbald (1/21/2009)[/b][hr]a long thread here but I don't think this has been asked\answered - the article mentioned that VM streamlines the DR process, but for that to be fully true the SAN would have to be mirrored as well, yes?for what its worth we have hit performance problems on VMWARE, this was with a fairly small (20GB ) but heavily used database. I was not involved in the design of the solution (sigh) but I understand it is an up to date version of vmware but all vms on the box were sharing the same LUN, so the problem was likely the SAN set up?[/quote]We use SAN Replication for our DR. It isn't quite real time, but it isn't far behind it unless there is a lot of disk movement happening on the SAN at which point it will queue up until it either tips over and replication splits, paging our SAN Admin, or the activity slows so that the replication catches up. Without a full picture of the Host, # VM's, types of VM's # spindles assigned to the LUN it is impossible to actually tell what was most problemattic for your particular example. Having a shared LUN is certainly somewhere up there in the list near the top. I got a email from someone last year asking how I could recommend SQL on VMware after they attended a session where I discussed this topic. They had 4 SQL Servers on a single host and were having huge performance problems. Turned out that they were using DAS in a RAID 5 with 4 300GB disks. There is no way in the world that you will get good performance with that configuration. I personally would have issues running that configuration for a single production SQL Server physically, let alone 4 of them virtualized on that hardware.The up front cost to building a Virtual Infrastructure that is going to provide good performance can be very steep initally especially if you don't have a SAN, and if you are going from a bunch of small 1 or 2 way servers to a 4 or 8 way host type machine. Sure Virtualization is touted as a way to make better use of your existing hardware, but you need to take a serious look at what your existing hardware actually is, and then size that up with your actual expectations.Wed, 21 Jan 2009 07:59:52 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxmuch as I dislike VMs I have never encountered turning off parallelism, that could be a serious hit and I'm pretty sure this is untrue. I guess some vms may be able to allocate virtual cpus or may leave HT turned on, in which case this is not the way to configure for sql server. In all the VMs I've used cores are allocated to each vm.Wed, 21 Jan 2009 07:58:14 GMTcolin.Leversuch-Robertshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]george sibbald (1/21/2009)[/b][hr]a long thread here but I don't think this has been asked\answered - the article mentioned that VM streamlines the DR process, but for that to be fully true the SAN would have to be mirrored as well, yes?for what its worth we have hit performance problems on VMWARE, this was with a fairly small (20GB ) but heavily used database. I was not involved in the design of the solution (sigh) but I understand it is an up to date version of vmware but all vms on the box were sharing the same LUN, so the problem was likely the SAN set up?[/quote]Was the max degree of parallelism greater than 1 on your instance?VMs do not handle parallelism well, from what I have heard. This could partly the problem here.Jonathan Kehayas has actually mentioned this earlier in this thread.Wed, 21 Jan 2009 07:42:18 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxa long thread here but I don't think this has been asked\answered - the article mentioned that VM streamlines the DR process, but for that to be fully true the SAN would have to be mirrored as well, yes?for what its worth we have hit performance problems on VMWARE, this was with a fairly small (20GB ) but heavily used database. I was not involved in the design of the solution (sigh) but I understand it is an up to date version of vmware but all vms on the box were sharing the same LUN, so the problem was likely the SAN set up?Wed, 21 Jan 2009 06:26:45 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]colin Leversuch-Roberts (1/12/2009)[/b][hr]see what a hornets nest this stirs up. [/quote]you're not wrong there sir. A great thread going on here! who will win the speed war copper or fibre optic?Mon, 12 Jan 2009 16:02:26 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxsee what a hornets nest this stirs up. I'm pretty sure iscsi sans work on 1gb ethernet, that's going to be one hell of a bottleneck. all luns across all disks eh? Yeah the one I can't name is one of those - so far all I have is inconsistant test results and I broke one san due to a lack of backplane bandwidth. 300 spindles was mentioned to me but I don't have the full spec sadly. Put it like this my dedicated luns on my current emc san at least give consistant results, all still outperformed by 4 x 10k sas disks in a raid 10.vms I've used for dev and test systems where performance isn't critical - I have no issues with that at all. As to parallelism and vms - I think that has to be absolute vendor bull**** and as I have worked almost exclusively with multi proc boxes since sql 6.5 I can't find any truth with that at all. But I think we should leave that aspect out of this. Great thread people keep it up - fascinating!Mon, 12 Jan 2009 15:33:11 GMTcolin.Leversuch-Robertshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Sir Slicendice (1/12/2009)[/b][hr][quote][b]John Beggs (1/11/2009)[/b]Keep in mind though, sharing spindles doesn't have to be bad. If you know the work load of the other devices sharing the spindles, you can gain from sharing. Which would you rather have, 10 dedicated spindles for your 1tb db or 90 shared spindles? Depends on how much load the other members that are sharing the spindles will add.[/quote]And it depends on the access pattern of your application. If your pattern is a SQL-based data warehouse, where you see very large sequential transfers, the 10 spindles as direct-attach storage may well beat the 90 spindles on a SAN. (It can be very, very expensive or even impossible to match the bandwidth of the direct-attach storage in the SAN solution.) I didn't believe this until I actually saw it! Of course, if your application is a transaction processing app with lots of small disk accesses, the SAN will handily beat the direct-attach....[/quote]Yes, I think I said in one of my earlier posts (unless it didn't make it into the "final" draft...) that comparable DAS will always beat SAN storage. Though I am not sure the 10-90 example is comparable... ;)Mon, 12 Jan 2009 11:16:36 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]John Beggs (1/11/2009)[/b]Keep in mind though, sharing spindles doesn't have to be bad. If you know the work load of the other devices sharing the spindles, you can gain from sharing. Which would you rather have, 10 dedicated spindles for your 1tb db or 90 shared spindles? Depends on how much load the other members that are sharing the spindles will add.[/quote]And it depends on the access pattern of your application. If your pattern is a SQL-based data warehouse, where you see very large sequential transfers, the 10 spindles as direct-attach storage may well beat the 90 spindles on a SAN. (It can be very, very expensive or even impossible to match the bandwidth of the direct-attach storage in the SAN solution.) I didn't believe this until I actually saw it! Of course, if your application is a transaction processing app with lots of small disk accesses, the SAN will handily beat the direct-attach....Mon, 12 Jan 2009 08:42:50 GMTSir Slicendicehttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxits worth noting if you licence the cpus for Enterprise edition of SQL server at the host level, it will cover you for unlimited sql server cpu licences on any VM's on that hostMon, 12 Jan 2009 08:00:28 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Jonathan Kehayias (1/11/2009)[/b][hr]One other thing to test for your larger database is the effect of parallelism waits CXPACKET on performance. My experience with VM's is that parallel processing is a performance killer rather than a performance enhancer. The reason for this is that the VM host software is configured to manage context switching and the multiple threads are no longer going against multiple processors necessarily. Generally speaking, I see much better performance from my VM's be setting the Max Degree of Parallelism to 1. If your 1TB+ database currently benefits from parallel processing, you definitely want to test what the performance will be like under a VM.[/quote]I absolutely agree with this. Our Windows administrators thought the VM DR advantages were worth VM'ing one physical machine as one large VM machine. This was a server with several 100+ GB data marts and lots of parallel query plans. Performance was 25% to 400% slower in the VM world compared to the pure physical. Obviously your milage may vary, but bottom line is you should really test VM versus physical before you decide.Having said that, SQL Server on VM works great for us in other areas. Generally these are smaller home grown or third party apps. Like one of the earlier posters, we find that many 3rd party apps assume that they are the only user of the DB server and expect sa user and xp_cmdshell. I'd like to say don't buy it if the vendor doesn't know how to code it responsibly, but I don't get to make all of those decisions, so we just VM it.Mon, 12 Jan 2009 07:02:45 GMTDBA_Robhttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Perry Whittle (1/11/2009)[/b][hr][quote][b]homebrew01 (1/10/2009)[/b][hr][quote][b]Perry Whittle (1/10/2009)[/b][hr][quote][b]homebrew01 (1/10/2009)[/b][hr]Our plan is to take our 2 heaviest hit SQL servers and put them onto their own physical machines running VM. They will have the VM overhead, but not sharing with any other servers. We like the VMotion DR type aspects.[/quote][b]if you're putting each VM onto a dedicated box of their own its pointless spending the $$$$$$$ on the vmware licences, they may just as well be physical sql servers straight off.[/b] No problem with storing a 1TB db on the SAN at all, better in fact i would think.[/quote]No DR or other advantages with using VM in this case ?[/quote]possibly but for the cost of the licences you could possibly find other ways. The general rule of thumb is if you get to the point where you dedicate a whole host to a VM it may as well be a physical machine[/quote]One other thing to test for your larger database is the effect of parallelism waits CXPACKET on performance. My experience with VM's is that parallel processing is a performance killer rather than a performance enhancer. The reason for this is that the VM host software is configured to manage context switching and the multiple threads are no longer going against multiple processors necessarily. Generally speaking, I see much better performance from my VM's be setting the Max Degree of Parallelism to 1. If your 1TB+ database currently benefits from parallel processing, you definately want to test what the performance will be like under a VM.Sun, 11 Jan 2009 17:24:05 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]SQLBOT (1/11/2009)[/b][hr]SA can enable xp_cmdshell, so I don't get your point here.I like what you're saying and it's worth noting that I think all SA access for vendors should be kept in check... sometimes it can't be. For me personally it's [b]never [/b]worth the risk to multi-instance a machine if one of the instances has a user with SA.... ever.My line of thinking is that if they require SA, I'm not going to bend over backward, but rather cut a new VM, wash my hands and let the infosec and OS teams monitor them like they do all other production systems. ~BOT[/quote]If that is an acceptable trade off for you and your company, and it works for you, then it is what it is. In SQL 2008, you can set a policy on the server that prevents enabling xp_cmdshell. Even if they do enable xp_cmdshell, if you separate Service accounts, they only get access to the level of priviledge of the service account, so the risk is minimum if you configure your service accounts properly.For my company, there is no way that $5K per CPU license and the extra resources for a SQL Instance for one database does not make sense, especially in the tougher economic times we face, and I don't work for a small company. Any place that I can save/cut costs, is well worth it, especially when it requires less than a few hours of work forcing a software vendor to do what they should have done responsibly in the first place.Sun, 11 Jan 2009 17:11:39 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Perry Whittle (1/11/2009)[/b][hr][quote][b]homebrew01 (1/10/2009)[/b][hr][quote][b]Perry Whittle (1/10/2009)[/b][hr][quote][b]homebrew01 (1/10/2009)[/b][hr]Our plan is to take our 2 heaviest hit SQL servers and put them onto their own physical machines running VM. They will have the VM overhead, but not sharing with any other servers. We like the VMotion DR type aspects.[/quote][b]if you're putting each VM onto a dedicated box of their own its pointless spending the $$$$$$$ on the vmware licences, they may just as well be physical sql servers straight off.[/b] No problem with storing a 1TB db on the SAN at all, better in fact i would think.[/quote]No DR or other advantages with using VM in this case ?[/quote]possibly but for the cost of the licences you could possibly find other ways. The general rule of thumb is if you get to the point where you dedicate a whole host to a VM it may as well be a physical machine[/quote]I absolutely agree. You would probably be better served clustering the two servers (HA) using boot from SAN technology and SAN mirroring (DR), and putting the additional cost of VM licensing into more RAM / stronger hardware and segmenting the databases into different instances on the cluster. You can't address the same amount of memory or processors in a VM that you can physically. This isn't a practical use of putting SQL in a VM, from two people who run SQL in VM's standpoints.Sun, 11 Jan 2009 17:05:29 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Jonathan Kehayias (1/8/2009)[/b][hr][quote][b]SQLBOT (1/8/2009)[/b][hr]I think it depends on your rationale.Normally I go for a single host\instance for the performance. The reason we use virtualization is for vended applications that require any sort of sa access. SA has OS access... I don't want somebody taking down a server with multiple instances on it using xp_cmdshell, or while performing a poorly written upgrade script that fills up the OS drive...Virtualization creates a safe sandbox environment for sa, but does add the overhead of providing a memory and disk space for the OS.[/quote]Further, xp_cmdshell is disabled by default in SQL 2005, and very few applications being built today require it to be enabled in SQL Server. [/quote]SA can enable xp_cmdshell, so I don't get your point here.I like what you're saying and it's worth noting that I think all SA access for vendors should be kept in check... sometimes it can't be. For me personally it's [b]never [/b]worth the risk to multi-instance a machine if one of the instances has a user with SA.... ever.My line of thinking is that if they require SA, I'm not going to bend over backward, but rather cut a new VM, wash my hands and let the infosec and OS teams monitor them like they do all other production systems. ~BOTSun, 11 Jan 2009 11:59:25 GMTSQLBOThttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]homebrew01 (1/10/2009)[/b][hr][quote][b]Perry Whittle (1/10/2009)[/b][hr][quote][b]homebrew01 (1/10/2009)[/b][hr]Our plan is to take our 2 heaviest hit SQL servers and put them onto their own physical machines running VM. They will have the VM overhead, but not sharing with any other servers. We like the VMotion DR type aspects.[/quote][b]if you're putting each VM onto a dedicated box of their own its pointless spending the $$$$$$$ on the vmware licences, they may just as well be physical sql servers straight off.[/b] No problem with storing a 1TB db on the SAN at all, better in fact i would think.[/quote]No DR or other advantages with using VM in this case ?[/quote]possibly but for the cost of the licences you could possibly find other ways. The general rule of thumb is if you get to the point where you dedicate a whole host to a VM it may as well be a physical machineSun, 11 Jan 2009 02:39:42 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxI wouldn't say he lied to you...but that doesn't mean you have the right picture either. My point was, the fabric architecture does not define how the data is stored, the software of the solution does. With Equallogic, I am pretty sure that you can specify the spindles (disks) that you want the data to reside on. The question becomes however, do you want to? The vendor will most likely try to tell you that you need to/should let the storage system decide where to move it all. This is especially true of those sales engineers that have limited understanding of anything not made by their company. Which means most, in my experience. ;)Keep in mind though, sharing spindles doesn't have to be bad. If you know the work load of the other devices sharing the spindles, you can gain from sharing. Which would you rather have, 10 dedicated spindles for your 1tb db or 90 shared spindles? Depends on how much load the other members that are sharing the spindles will add.Sun, 11 Jan 2009 01:19:45 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Perry Whittle (1/10/2009)[/b][hr][quote][b]homebrew01 (1/10/2009)[/b][hr]Our plan is to take our 2 heaviest hit SQL servers and put them onto their own physical machines running VM. They will have the VM overhead, but not sharing with any other servers. We like the VMotion DR type aspects.[/quote][b]if you're putting each VM onto a dedicated box of their own its pointless spending the $$$$$$$ on the vmware licences, they may just as well be physical sql servers straight off.[/b] No problem with storing a 1TB db on the SAN at all, better in fact i would think.[/quote]No DR or other advantages with using VM in this case ?Sat, 10 Jan 2009 21:35:35 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]homebrew01 (1/10/2009)[/b][hr]Our plan is to take our 2 heaviest hit SQL servers and put them onto their own physical machines running VM. They will have the VM overhead, but not sharing with any other servers. We like the VMotion DR type aspects.[/quote]if you're putting each VM onto a dedicated box of their own its pointless spending the $$$$$$$ on the vmware licences, they may just as well be physical sql servers straight off. No problem with storing a 1TB db on the SAN at all, better in fact i would think.Sat, 10 Jan 2009 19:30:53 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]John Beggs (1/8/2009)[/b][hr]@homebrew01 -- Any differences in data dispersement over your physical disks is based upon the software controlling the solution, not the access method. Neither iSCSI or FC-AL mandates how the data is stored.[/quote]So he lied to me ?? I'm not hardware expert (nor SQL expert either). With the FC SAN at my old place, I liked being able assign specific DB files to specific LUNs made up of specific drives.Now I'm told that because we're buying as ISCSI SAN, the LUNs are spread across all drives, so I can't isolate the Production Database files onto dedicated drives away from Test & Dev. This is a Dell setup with Equalogic SAN .... hmmmmOne problem is that I'm not hardware literate enough to ask the right questions of the vendor, and out network admin seems a bit over his head too when it comes to VM.Sat, 10 Jan 2009 18:31:47 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Jonathan Kehayias (1/8/2009)[/b][hr][quote][b]colin Leversuch-Roberts (1/8/2009)[/b][hr]My experience with virtual sql servers has been very poor unless the db is small - ever tried a 1.5TB databse on a virtual server?[/quote]I've done a number of presentations on running SQL Virtualized, one for VMware at its Orlando Show, a few for local user groups, and a few at code camps and SQL Saturday events. They are always packed with people, and there are always tons of questions. Maybe I should take that presentation and write it up as an article. The very first thing that I cover is how I define the size of a SQL Server and whether you should consider virtualizing them. For your particular example, you would be crazy to try and virtualize that, and I wouldn't consider it a candidate because you will be at the limits of the VM's. We push the limits of where I would virtualize SQL at my job where I have one SQL Server that manages close to 1TB of total data across multiple 100GB+ databases, one of which is the 300GB DW I mentioned earlier, but it didn't start off like this. This server started with under 300GB of total data on it and grew over the last two years to the size it is at.[/quote]So our plan to VM our 1 Tb box isn't good idea ? Why does size matter so much since the data is on the SAN, not on the VM box. Our plan is to take our 2 heaviest hit SQL servers and put them onto their own physical machines running VM. They will have the VM overhead, but not sharing with any other servers. We like the VMotion DR type aspects.Sat, 10 Jan 2009 18:26:17 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]colin Leversuch-Roberts (1/8/2009)[/b][hr]Yeah I'm not very popular with a certain vendor currently - I'm off to scale some big databases, maybe around 100TB so should get to grips with some serious storage - watch my blog for details I appreciate the "shared" and "improved storage management" but as a production DBA I have to be able to guarantee performance which is predictable, there's a lot of questionable half truths about sans which vendors trot out, and many supposed advantages of a san often never materialise, e.g snapshots, expandability - assumes you have capacity to hand - same goes for the switching resource in and out for virtualised servers - how many companies will leave storage, memory and cpu idle just so they can be switched in if required? Very few I might suggest.[b]My experience with virtual sql servers has been very poor unless the db is small - ever tried a 1.5TB databse on a virtual server?[/b][/quote]Uh-oh ... Ours is 900 GSat, 10 Jan 2009 18:16:04 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]henrik staun poulsen (1/8/2009)[/b][hr]Hi mararity,How do you tell the difference between mediocre and great Admin? We have two people working with the SANs, but are they any good???I would like to be able to verify that we get the best performance that is likely possible, but how?Best regardsHenrik Staun Poulsen[/quote]If many of the waits in your system are disk-based, for example WRITELOG or PAGEIOLATCH_SH, it is a good indication that the SAN may be the problem. This is especially true if you are getting widely varying performance at different times for similar workloads.Fri, 09 Jan 2009 03:29:36 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]henrik staun poulsen (1/8/2009)[/b][hr]How do you tell the difference between mediocre and great Admin? We have two people working with the SANs, but are they any good???[/quote]The mediocre SAN admin will say "I've set up the SAN and it is the fastest thing ever. Just tell me how much space you need and don't hassle me with the details because my SAN is so fast it doesn't matter."The good SAN admin will say "Oh, let me give you a quick overview about the SAN options we have concerning shelves, storage processors, etc, and I'll be happy to listen to your database needs like placement of primary data files, dedicated index files, and log files. There's probably something I can recommend on my SAN, fast as it is, that can be done to help you out."(Exact verbage may vary) That's how you tell the difference.Thu, 08 Jan 2009 18:51:08 GMTmagarity kernshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]colin Leversuch-Roberts (1/8/2009)[/b][hr]My experience with virtual sql servers has been very poor unless the db is small - ever tried a 1.5TB databse on a virtual server?[/quote]I've done a number of presentations on running SQL Virtualized, one for VMware at its Orlando Show, a few for local user groups, and a few at code camps and SQL Saturday events. They are always packed with people, and there are always tons of questions. Maybe I should take that presentation and write it up as an article. The very first thing that I cover is how I define the size of a SQL Server and whether you should consider virtualizing them. For your particular example, you would be crazy to try and virtualize that, and I wouldn't consider it a candidate because you will be at the limits of the VM's. We push the limits of where I would virtualize SQL at my job where I have one SQL Server that manages close to 1TB of total data across multiple 100GB+ databases, one of which is the 300GB DW I mentioned earlier, but it didn't start off like this. This server started with under 300GB of total data on it and grew over the last two years to the size it is at.Thu, 08 Jan 2009 16:10:01 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]John Beggs (1/8/2009)[/b][hr]Is it possible that the article you read was referring to Server '08? I have read that there are changes there that have eliminated the need for the offset, but I haven't researched it further than that.[/quote]Server 2008 uses a 1MB offset by default which is compatible with all of the currently possible disk configurations because they will all fall on a 1MB offset since they are all multiple of 64K, 128K, 256K and 512K.Give it a few years and this won't be the case I am sure.Thu, 08 Jan 2009 16:00:23 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]SQLBOT (1/8/2009)[/b][hr]I think it depends on your rationale.Normally I go for a single host\instance for the performance. The reason we use virtualization is for vended applications that require any sort of sa access. SA has OS access... I don't want somebody taking down a server with multiple instances on it using xp_cmdshell, or while performing a poorly written upgrade script that fills up the OS drive...Virtualization creates a safe sandbox environment for sa, but does add the overhead of providing a memory and disk space for the OS.[/quote]You can accomplish the same safe sandbox with a multi-instance SQL Server if you architect it properly. For example, each instance is issolated from the others, so different SA passwords for each will keep them safe. You can run each instance with a different local user for the service account and restrict each service account to only have access to the necessary drives/folders required for that instance. If you put the data paths for each instance on dedicated drives for that instance for data/logging, and restrict the access to that drive only, it can only fill up its own drive.Further, xp_cmdshell is disabled by default in SQL 2005, and very few applications being built today require it to be enabled in SQL Server. Generally, if you challenge the vendor for why they need a sysadmin user, you will find out that they don't. I have had more than a dozen tell me that they have to have sysadmin rights, and only one has ever provided specifically what functionality they use that required sysadmin rights. All the others have been able to run under less priviledges. You have to be willing to argue it, and you have to be willing to put the time and effort in to identify/design the correct security roles for them. I had one vendor offer to work with me to do this, if I would provide them the exact permissions required so they could do the same elsewhere. I have no problem doing that to keep my environment secure.[quote]As someone stated earlier, VM's can also provide superior DR if architected that way, so there are a couple of good reasons to virtualize.[/quote]Boot from SAN can provide the same DR architecture that VM's can. The entire boot image is on the SAN LUN, and the server boots from that image. The same type of SAN replication can be used for DR purposes for both, and with ILO cards, you can power up the remote site over the internet from anywhere in the world with the correct permissions.Thu, 08 Jan 2009 15:58:01 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx@homebrew01 -- Any differences in data dispersement over your physical disks is based upon the software controlling the solution, not the access method. Neither iSCSI or FC-AL mandates how the data is stored.@Colin -- If you haven't already, try managing snapshots on a high utilization sql server where the application runs from 15+ DBs (don't ask...). Quiescing databases increases in difficulty proportionally (maybe even geometrically) with the number of databases that need quiescing at one time. Not fun.Thu, 08 Jan 2009 15:46:59 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]SQLBOT (1/8/2009)[/b][hr]Virtualization creates a safe sandbox environment for sa, but does add the overhead of providing a memory and disk space for the OS.[/quote]the only caveat here is unless the virtual network the VM is on is segregated it will have network access to the rest of the network in the same way as a physical box. Installing Lophtcrack and hacking SAM's is still possible. Virtual networking is full of pitfalls and needs to be addressed carefully. VLAN tagging is a good way of segregating the trafficThu, 08 Jan 2009 15:41:39 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxYeah I'm not very popular with a certain vendor currently - I'm off to scale some big databases, maybe around 100TB so should get to grips with some serious storage - watch my blog for details I appreciate the "shared" and "improved storage management" but as a production DBA I have to be able to guarantee performance which is predictable, there's a lot of questionable half truths about sans which vendors trot out, and many supposed advantages of a san often never materialise, e.g snapshots, expandability - assumes you have capacity to hand - same goes for the switching resource in and out for virtualised servers - how many companies will leave storage, memory and cpu idle just so they can be switched in if required? Very few I might suggest.My experience with virtual sql servers has been very poor unless the db is small - ever tried a 1.5TB databse on a virtual server?Thu, 08 Jan 2009 15:00:37 GMTcolin.Leversuch-Robertshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxISCSI vs Fibre Channel. We're setting up a VM environment, and the Dell rep told me that with the ISCSI SAN, the LUNs are automatically spread across all the drives, whereas the FC SAN as used in my previous job, a LUN could be assigned specific drives, giving more control.Thu, 08 Jan 2009 14:57:21 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxColin, you bring up a very good point. The value of a SAN is in its shared storage, shared performance and better storage utilization. Unfortunately, that's a lot of "shared" goals.For a single system, a SAN will never out perform comparable directly attached storage. Think of it as a multiple tier application, you don't implement it to increase the performance of a single request, but rather many requests at once. As to the "SAN dealers", my first experience with a SAN was on a pre-installed base SAN and the company was looking to expand in size and support for DR. Working with the vendor, we were promised repeatedly that their replication solution (which was at the SAN block level) would keep SQL server up at the DR site, even if the connection between sites broke. Despite my arguments, management was sold on the idea and it was implemented. Within 2 days of having the setup in house, though not in production, I had proven that a break in connection was likely to cause corrupt DBs on the remote side. Those sales engineers didn't appreciate me much after that...Thu, 08 Jan 2009 14:38:14 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspx[quote][b]Jonathan Kehayias (1/8/2009)[/b][hr][quote][b]SQLBOT (1/8/2009)[/b][hr][img]http://i.microsoft.com/global/sqlserver/2005/en/us/PublishingImages/SpC_diag-sql05lic-vm4.gif[/img][/quote]Using this same image and configuration, Buck has discussed this before, that you would be better off going physical for this implementation using 5 instances on the physical server, and I would have to agree..[/quote]I think it depends on your rationale.Normally I go for a single host\instance for the performance. The reason we use virtualization is for vended applications that require any sort of sa access. SA has OS access... I don't want somebody taking down a server with multiple instances on it using xp_cmdshell, or while performing a poorly written upgrade script that fills up the OS drive...Virtualization creates a safe sandbox environment for sa, but does add the overhead of providing a memory and disk space for the OS.As someone stated earlier, VM's can also provide superior DR if architected that way, so there are a couple of good reasons to virtualize.At the end of the day, what to use will always depend... but licensing costs shouldn't drive people from using VM's [i]if[/i] they are using Ent. Ed. and processor licenses. Thu, 08 Jan 2009 14:35:10 GMTSQLBOThttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxWell, I am just starting my foray into the VMWare administration world, so I don't really have a clue about what they use. It's very possible that it is not an issue there, but that would also depend on your setup. I seem to recall (again, very new to VMWare) that with ESX you can set up "native" drives to your VMs. In that case, I think the offset would still help, if I am understanding correctly what they are doing.Is it possible that the article you read was referring to Server '08? I have read that there are changes there that have eliminated the need for the offset, but I haven't researched it further than that.Thu, 08 Jan 2009 14:24:07 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic632083-1452-1.aspxthis isn't a plug for my blog, but I've been actually testing 3 sans for performance as part of a data centre move, becuase of a nda I can't name one san manufacturer. You might want to glance over my blog http://sqlblogcasts.com/blogs/grumpyolddba/ as I'm putting up a series of posts concerning performance.Note that windows 2008 does not require partition alignment and some san manufacturers claim it's irrelevant. There are a lot of factors concerning a storage area network and the article gave a good insight. My personal experience tends to indicate real performance on a san is very hard to achieve and there is far more contention than vendors care to admit. Probably the most difficult factor is performance monitoring which seems to be sadly lacking on most sans.Beware of using "tools" to measure disk subsystem storage; I've watched tools used to prove san performance, when i figure out how to blog about it accurately I will, the upshot was that I had claimed our application performance was degraded on a new san, the vendor produced a series of non sql tests which proved otherwise - in the end I was able to find a number of application code samples and run them in loops along with backups, restores, index rebuilds which showed the new san was around 50% slower than the existing san .. it's a long story but do be careful of claims about san technology.Thu, 08 Jan 2009 14:17:16 GMTcolin.Leversuch-Roberts