By Terry Engelstad
MCP, MCSE, CCNA, MCDBA, MCTS, MCITP
AIS Network Operations Manager
On a data storage configuration project involving Microsoft SQL Server, a colleague asked me:
We need 700GB of storage, which must be expandable by 10X. We will be using Metalogix to externalize the 700GB of content. Metalogix puts markers in SQL pointing to the storage. Since the data is not actually residing in SQL, can we virtualize the Web front end (WFE), app server, and SQL server, and have the storage be in the AIS Network storage area network (SAN)?
Well, SQL Server 2008 introduced a feature called FileStream. This feature allows SQL Server to store data in an unstructured format outside of the database management system in the file system of the underlying operating system.
This feature is generally used only when the objects which SQL Server needs to access are 1) huge and 2) need very fast READ access.
FileStream puts these objects in the file system just as you would save a Word document. By doing so, the same set of problems exists as if these files were Word documents on your own hard drive. They must be backed up separately from the databases. They can be deleted either accidentally or intentionally. They are exposed to disk corruption just as any other file would be. Unless encrypted, their contents are viewable.
So to answer the question, yes, the data could be virtualized. After all, it’s just files on a hard disk. Is there an expectation of high performance? 700 GB (or 10 x 700 GB) far exceeds my heuristic, which says it should be physical, not virtual. Therefore, the fact that it is externalized does not change my recommendation that it be a physical machine.
The follow-up question was:
Can we have a virtual machine (VM) WFE, VM app server, physical SQL server, with storage in the SAN? If so, what size drive do we need in the SQL server?
The fact that data is stored externally really doesn’t matter. My concern is the amount of activity going back and forth thru a virtualized environment. If there are a lot of reads, a lot of writes, or a lot of both, then that’s a lot of traffic which could potentially affect other virtual machines in the environment.
So, yes, you can have a VM WFE, a VM app server, and a physical SQL server with storage in a SAN. This is exactly what one of our clients has. The physical SQL server would need enough disk space for the operating system and supporting application software – probably no more than 120 GB, and perhaps less. The drives on the SAN would get mapped to the physical server and would be connected directly via fiber optic cabling (very good performance, no startup problems) or via iSCSI (using copper Ethernet cables with possibly delayed start).
What configurations work for you? Let me hear from you below.