Monthly Archives: February 2013

Upcoming Presentations and Events

I’m doing a few presentations in the next a few weeks.

February 16th, 2013: I’ll speak at IT Pro Camp in Sarasota, FL. I will present two sessions – “SQL Server – Practical Troubleshooting” and slightly refreshed version of “Between Ground and Clouds”.

February 28th, 2013: I’ll do online presentation for Moscow SQL Server User Group. I’ll talk about practical troubleshooting of SQL Server with wait statistics, DMV and Performance Counters.Will be presented in Russian – let me know if you want to attend.

March 1st, 2013: I’ll host SQL Saturday #192 one day pre-con – “SQL Server Internals from the Practical Angle”. I’m going to talk how SQL Server works under-the-hood in the way that helps you to apply the knowledge to day-to-day tasks.

March 2nd, 2013: I’ll present at SQL Saturday #192. Will have either one or two sessions about locking and blocking. We will use bag of candies as the replacement of Power Point

Finally, on March 21st, 2013 I’ll present at 24 Hours of PASS Russia. I’m going to talk about Data Partitioning. This is the different session that I did at TechEd, or, better say, a lot of content would be different – I’m not going to talk about scaling-out and Azure and focus on the regular installation of SQL Server. I’ll show a couple of deep-dive demos focusing on how to move data between different disk arrays keeping the system online. Even with the Standard edition of SQL Server.

Hope to see you there.

Data Partitioning – Scaling-Out (Part 3: Factors to consider)

Of course, there are plenty other ways to scale-out the system in addition to horizontal partitioning and data sharding. Although in every case we have the same set of factors we need to consider before making the final decision of separating the data between the physical servers. Let’s talk about them today.

Development cost:
Scaling-out would increase development cost. The system becomes more complicated. Often there are additional tiers (application server, data warehouse, etc.) that need to be developed, tested and supported. You also need to have different kind of people in the team – while you can possibly save something by having good rather than exceptional DBA, you’d need to have very good architects and backend developers there. With the current trends when hardware prices are decreasing and development cost is rising, you’d need to consider that aspect very carefully from the beginning.

And of course, there is the question about legacy software. Neither of us want to touch old, often obsolete, code. In some cases we could be lucky and keep everything as is (again, legacy is accessing only operational data and/or we have multiple desktop shortcuts). In other cases we could face very painful and expensive process of refactoring.

Support and maintenance cost:
Well, you’ll have more servers to maintain. And everything depends on how much manual work you are doing. There is not much difference between supporting ten or one hundred servers when everything is automated but it would be completely different story when manual work is involved. PowerShell, policy-based management, data collectors and other tools and technologies – learn them – they are your friends.

Hardware and software cost:
At bare minimum you’ll need to buy OS and SQL Server licenses for each server. A lot of things depend on SQL Server Edition – in some cases scaling out would allow you to use Standard editions on the smaller servers rather than stuck with the Enterprise when you don’t scale out. You can be forced to use Enterprise edition even if you don’t need edition features simply because of 64GB RAM limitation Standard edition has. In some cases you can live with lower end storage too. So you need to carefully plan everything.

And there are the Clouds especially when we talk about SQL Server within VM (Infrastructure As A Service approach). With Clouds the picture changes dramatically. We are paying by the hour – for what we are using. There is no (or minimal) upfront cost for hardware and software. And there are physical limitations in VM configuration (RAM, # of Cores, etc.) as well as slow IO performance. Those limitations could force us to scale out regardless of the cost.

High Availability:
This one is interesting. On the one hand, with multiple servers we have to implement HA strategy multiple times (think about automation again). It introduces additional work but on the other hand gives us better (or, perhaps, different) type of protection. When we use horizontal partitioning, we obviously need to keep our main (operational period) server online if we want the system to be operational. But if the server with historical data is down, it would not affect operational activity. Same with data sharding. If we have one out of ten servers down, only ten percent of the customers would be affected. It still bad but trust me, ten percent of complaining customers are better than one hundred percent.

Of course, everything is in “It depends” category. There is no such thing as strict set of rules when scaling out is appropriate. We need to look at the systems on case by case basis and do our homework all the time.