SQL Server, SANs and Virtualisation

Question

SQL Server, SANs and Virtualisation

Viewing 15 posts - 31 through 45 (of 68 total)

You must be logged in to reply to this topic. Login to reply

John Beggs SSCommitted Points: 1921 More actions · Answer 1

Well, 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.

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

Jonathan Kehayias (1/8/2009)
SQLBOT (1/8/2009)
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.
.

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 if they are using Ent. Ed. and processor licenses.

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

John Beggs SSCommitted Points: 1921 More actions · Answer 3

Colin, 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...

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

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.

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?

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

SQLBOT (1/8/2009)
Virtualization creates a safe sandbox environment for sa, but does add the overhead of providing a memory and disk space for the OS.

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 traffic

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

John Beggs SSCommitted Points: 1921 More actions · Answer 7

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

SQLBOT (1/8/2009)
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.

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.

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.

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

John Beggs (1/8/2009)
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.

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

colin Leversuch-Roberts (1/8/2009)
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?

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.

magarity kerns SSCrazy Points: 2763 More actions · Answer 11

henrik staun poulsen (1/8/2009)
How do you tell the difference between mediocre and great Admin?
We have two people working with the SANs, but are they any good???

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.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 12

henrik staun poulsen (1/8/2009)
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 regards
Henrik Staun Poulsen

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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

homebrew01 SSC Guru Points: 55548 More actions · Answer 13

colin Leversuch-Roberts (1/8/2009)
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.
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?

Uh-oh ... Ours is 900 G

homebrew01 SSC Guru Points: 55548 More actions · Answer 14

Jonathan Kehayias (1/8/2009)
colin Leversuch-Roberts (1/8/2009)
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?
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.

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.

homebrew01 SSC Guru Points: 55548 More actions · Answer 15

John Beggs (1/8/2009)
@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.

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

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