Featured Article(s)
Service Oriented Architecture, Service Oriented Database Architecture and MS-SQL Server 2005
This explains the fundamentals of Service Oriented architecture, Service oriented database architecture and how database SQL server 2005 integrates the concepts
New SSWUG.ORG Site Feedback
Just a quick note of thanks for all of the great feedback and comments on the new SSWUG.ORG. I really appreciate hearing about how people love the new design and tools that are included. Can’t wait too to tell you about some great new things rolling out over the next few weeks! Keep those comments coming – just drop me a note here.
Final Feedback Bits on SQL Server/Shared Storage
Jason wrote in with some specific tips and feedback on some of the shared storage things we’ve been discussing here – as a final look at this (for now), I thought I’d include some of his feedback.
"I have been doing SQL on shared storage for a while and there are some things that I have learned or realized over time that may help some of the readers:
*Let me state that my understanding is of MS SQL and therefore Windows Server OS.
** Let me also correct the term "SAN" and say it should be called "Shared Storage". The SAN is just a part of your shared storage (typically the fiber channel part).
Create different volumes for your database, log and Temp DB (yes, this is and oldie but never lets you down). I would suggest to take it a step further: Create a separate volume for database, log and Temp DB files for each instance (using the MS term, some translation may be required for Oracle) since it may not make sense to have 1 pair of volumes for each database.
Think about your file structure, it will be important when you need to add volumes. The design varies depending on whether you will cluster or not (clustering requires separate drive letters per instance through SQL 2005 at least), but the key to remember is that you want to mount your volumes into folder mount points , i.e. R:<instanceName>DBUserDB or R:<instanceName>DB<specific DB> or R:<instanceName>DBTLogs. Defining a solid structure will allow you to grow and change without major changes later. Why does this matter you ask? First off you get to keep a very clean file structure so your environment is always laid out consistently and then theres the deeper and more valuable storage benefit. The more individual volumes you have the more flexibility you get from the shared storage. Almost all Quality shared storage has the ability now to move volumes around between sets of disks, and can do it without impacting application operation (other than what you are already seeing). You also gain the ability to take more granular advantage of built-in Shared Storage functions like snapshot, cloning, QOS (IO prioritization) and also be able narrow down performance bottleneck problems on the storage using the tools on the storage. This way you now gain the ability to do administrative work (defrags: SQL and file level, check disk, etc) on volumes while impacting fewer applications.
With all good things there are always bad though, so I will disclose that making more volumes does cause more administrative work for setup and some regular maintenance tasks (more file level defrags required), and also goes against one of the main benefits of shared storage – optimized space usage. Once you create a volume and define it for just 1 database that space is not available for other uses as it would be if you created 1 large volume. I suspect there are other negatives I have not encountered yet or perhaps neglected to include.
Don’t forget the Backups!! The last minute realization as I was setting up one of the off the shelf applications that was going to have a large database was that we had not planned during the design phase to accommodate the backups. I will spare you the walk through the weeds, but basically I had to steal storage from something else to support the requirement.
Create a comparison matrix, I suggest use a 10 point scale and score each product you are considering and the specify a weight for that feature/function related to the importance of that feature to your success. I have attached a sample I came up with and have used a few times. You need to modify the spreadsheet to meet your specific need, but I suggest at least looking it over. [From the editor: if you’d like a copy of this, email me and I’ll send it to you]
Watch out for vendor double speak – they all do it.
Don’t hesitate to ask clarification questions (or re-ask), you never know what you will learn.
Critical Piece of understanding for everyone: (this is coming from me as the SAN admin, not me as the SQL DBA, and it is almost a religious thing) !! You MUST understand that Shared Storage is almost always a function of $ per IO supported not a function of anything else (like $ per GB). You could get any kind of box, big or small, that you want, but if you design it incorrectly you could cause yourself a great amount of grief because you end up sharing IO loads that are either not compatible or that add up to more that those disks can support. You can have all your data co-exist within the same set of physical hard drives in your shared storage just like you can on DAS, that is not what I am saying.
What I am trying to convey is that you really need to understand the IO characteristics of your systems when purchasing your SAN or be willing to extend right away if you hit some sort of bump in the road. I have personally talked with a few admins who have either learned the hard way or had customers who put themselves in the position where $$$ were king and they bought the most cost effective device for the GB of storage they could, but they ended up with a tool that would not effectively run their applications because the disks were always IO bound. No matter how much cache you have, the spindles should be able to support the whole load. You then you have cache to cover for excessive load. (Yes it has directly impacted me personally)."
Featured White Paper(s)
Enhance SQL Reporting Services with Double-Take
Microsoft SQL Reporting Services is an exciting way for organizations to gain access and insight into their important busines… (read more)