Let’s set size aside for a second. Here’s the order in which you should try new technologies – whether it’s virtualization, cloud, storage, or whatever:
- Development servers with less than 100GB of data
- Production servers with less than 100GB of data
- Development servers with <1TB
- Production servers with <1TB
- Development servers with >1TB
- Production servers with >1TB
It’s simple: start with the lowest risk, easiest-to-manage servers first. Learn your lessons on smaller servers, then gradually use the technology on larger and larger servers.
If at any level, your users complain about performance and you can’t figure out how to fix it, stop at that level. Don’t go farther.
It’s not that 1TB of data is too big to virtualize or to move into the cloud – it’s not. But your skills may not be up to the task, and you need to sharpen those skills before you attack a larger performance problem.
It all comes back to my post, “How Many Databases Can I Put on a SQL Server?”
15 Comments. Leave new
size is one thing, but so much more to assess for a server
workload type/compute
memory
network transfer in/out
would be interesting to see the compare/contrast on all those (and other) factors on deciding what can or can’t be moved. – Steve
Steve – oh absolutely, but the more data you gather and try to make a decision on, the more you start to become paralyzed by the decision-making process. Sometimes you’ve gotta pick a number and start moving – especially if you’re not using any numbers at all before now!
Brent – an excellent question and good orbital level viewpoint but if I may attempt to lower the elevation a bit as this is a topic near and dear to my heart.
It would seem to me a missing question is, are there constraints inherent with the platforms/technology under consideration which should rule it out from consideration? (at least until it improves). I would like to be paid for every time I’ve witnessed a project just “assume” a technology meets requirements and wait for it to bite them leaving them bleeding profusely.
Case in point…I was interested last year in migrating a database to Azure and found out in just a few minutes of research that it didn’t support SSIS, didn’t allow databases over 500GB, and was basically stripped of pretty much any decent functionality that would make it a viable solution (for pretty much anything other than keeping track of what you ate for dinner last night). Yet people were asking, should we go to Azure for your application? Um….ya sure that sounds like a great idea. NOT
I’ve seen organizations waste incredible amounts of time and money attempting to implement technologies only to find out they can’t scale or don’t have 101 basic functionality until they’re well into the project trying to implement it. If a vendor advertises a feature and it doesn’t come true in testing, shame on them and we’ve done our due diligence (hopefully you didn’t give them any money yet!). But if we just trust vendors OR the if technology obviously doesn’t meet your needs and we didn’t bother to find out beforehand, shame on us for being incredibly lazy.
I had no idea that SQL Azure did not support SSIS. I guess I need to research this further. Hopefully there is a nice bullet point list of every feature that does not work.
Don – yep, Books Online is your friend there. It’s really strong these days for Azure.
Also, keep in mind that SSIS isn’t really a feature. It’s a totally different application that Microsoft throws into the box for free with the SQL Server engine. Azure SQL Database is only the engine – no other free toys are thrown in the box.
Noted; thank you!
It explains very well what I’ve struggled to put into words in the past.
I’ve been fortunate to work at a steadily growing company. It’s allowed me to learn my lessons gradually as load and db sizes increase.
But it can be a bit stressful. I always feel like I’ve been thrown into the deepend. Performance skills get sharpened as they’re needed (which may be a bit late).
I would have loved to have a mentor. Someone who could tell me things like: “You’re using CLR assemblies eh? Watch out when you get to 1000 executions per second” or “Hey, reevaluate your indexing strategy on tables like this when they get to 10 million rows” etc…
That’s just the DB-Dev side. I’m sure things are at least as tricky on the DB-Admin side.
Thanks sir!
This is a tremendous insight! Of course one could use other metrics as well and it’s not as clear cut usually. But generally, low risk to high risk is the right way to think about it. I did not realize that before.
Perhaps, you should expound upon this statement:
“It’s not that 1TB of data is too big to virtualize or to move into the cloud – it’s not. But your skills may not be up to the task, and you need to sharpen those skills before you attack a larger performance problem.”
Which kind of skills would these be? Would completely rewriting the application and redesigning the database be the skill you are looking for on this?
It appears you are saying that any size system can be virtualized purely based on a particular skillset. Now if that skillset is knowing that some systems should not have the added layer of virtualization, then I am with you. Or, if you are saying you can virtualize anything assuming you have enough money and resources to waste doing, then I am also with you.
But, if you are really saying, you can virtualize anything and if you can’t you are just too stupid, then we need talk, because that is a ridiculous self-serving statement.
Keith – thanks for stopping by. I’m certainly not calling anybody stupid – I’m just saying that there are skills you have to pick up in order to accomplish certain tasks.
You’re not stupid if you can’t fly a multi-engine jet plane – it just means you haven’t learned the skills required to fly that kind of plane yet. You start with props, master the basics, and then move your way on up the fleet.
Troubleshooting slow, unreliable physical servers is a skill. Learn the skills required for that first, and then when you add in the trickier layers of virtualization, you’ll have an easier time.
I think I have an easier way for you decide, in the case of virtualization: if your virtual server (guest) needs all the resources of your host to run, there is not much point in virtualizing it? Or, if your particular virtual infrastructure cannot get past IO latency requirement, virtualization may not be what is needed.
True cloud is a different story, that can pretty much handle any load so long as you can pay for it and you can write your application to support, but that is off my topic.
Keith – consulting has changed me in a bunch of ways. One of ’em is that now, when I’m presented with a solution somebody built, instead of trying to pick it apart, I’ll try to come up with a reason why they would have done it that way. I start with an assumption that everybody involved had the best intentions, and they may not have gotten around to documenting why they did things.
A great example of this is seeing a single VM on a host. Before consulting, I’d have totally agreed with you that it was a bad idea, but now, I’ve seen folks who ended up in that situation. Classic example – a company had an old SQL 2005 server that they’d virtualized years ago. It hosted a single application’s database, and that application’s use had grown a lot over the last several years. Unfortunately, though, the vendor who’d originally wrote the app had long ago stopped supporting it.
What’s the company supposed to do? They really do need as much horsepower as they can get, but they don’t need super-high uptime, and they don’t want to build a SQL 2005 cluster. The server was working just fine – it just needed more horsepower because the code was old and crappy. Should they rewrite the entire app or move away from it? Probably – but for now, the single VM on its own host worked fine, and it gave them the advantage of independence from hardware failure.
I’m not saying your viewpoint is wrong – it’s a good default, and I’d agree with that default – but just know that sometimes you have to adapt that viewpoint based on what the business needs and prioritizes.
Point taken, and I won’t be a dead horse, but sometimes, many times, that business requirement is the fastest system possible and that is usually not a vm, but a clustered physical machine.
I would add that in addition to your skills, your budget may not be up to the task of migrating a 1TB+ database to VM or Cloud. In VM the SQL license can eat dollars faster than a teenager prepping for prom. When I reported at the cost of 7TB of Tier 1 cloud storage in the cloud that whole conversation stopped cold. 🙂