So You Want to Scale Out?
In the first post, I stated that it is better to scale up than out(my opinion) but I have seen applications try to scale out in several ways. Some worked better than others. I am going to talk about three different scenarios I have worked with in regards to scaling out.
Separating different databases
This sounds like a no brainer and an easy thing to do. However, developers tend to begin using data across databases after a few years of the same db’s being on the same server. Now you have dependencies that require linked servers or replication if you split databases across servers. Also other technologies like mirroring encourages designing everything in one(or very few) databases.
If you think this is a route that you are going to go, design it like that from the beginning if you can. Try to keep each database a complete self contained unit. If you have small, fairly static lookup tables that are shared between each database, keep a copy in each database updated through a nightly job or other method. If you have very large highly used tables that you want to split across servers, you need to look at a different method.
The key point is avoiding dependencies that will require cross database queries.
Replication Scale out
I have worked with apps that used merge replication for read\write scale out and transactional replication for read only scale out.
I have never seen an app use merge replication that was so large it could not scale up. It was mainly used to keep a live near real time copy of the database in another datacenter for a live disaster recovery site.
Sounds pretty cool, huh? The down sides include the fact that a GUID column gets added to every table. This may require app changes if you are not following best practices. Special consideration needs to be made for some schema changes. This is also expensive. You have to have an additional server, storage and licensing. That is the cheap part. You also have to have a global load balancing network device like one from F5 or Netscaler. On top of all of that, you have to manage all of this.
Disclaimer: I have yet to see an app that needed to scale beyond 24-48 cores use this method and I suspect it would be a management nightmare so test thoroughly.
The other replication scale out method use transactional replication for read only scale out.
The downsides to this includes the facts that it requires more app dev to split off the reads from the writes, the read servers still have to take all the writes, the log of the write server now has a lot of read activity and data is duplicated X number of times. On top of that, the hardware is split X number of times. As you can tell, I don’t like this one too much. I will expand on my experiences if you there are questions about it.
Horizontal Partitioning
If you want to scale out, this is the way to go. However, it will be really hard to get there if you do not start this way from the beginning. I cringe at the thought of trying to get a 1TB+ database there after years in production.
This method has also been called sharding in recent years. The basic idea is having the entire database partitioned by a key in a single table. Let’s say the users table. We split it by UserID with 10 million users per server. You must also keep all data in every other table related to that user in that DB on that server. Depending on how you partition, you would have a lookup SQL server to tell the app where the user is or just hardcode UserID 1-10,000,000 = Server1 as an application scoped variable.
Some challenges include dynamic data in tables that do not rely on the application partitioning key. Avoid parent\child relationships where children are on multiple servers if you have to report on that in any way. Do not partition on a key like last name. Having to split the “S server” into Sa-Se and Sf-Sz servers would be a real pain. I am sure you can also imagine the increase in application complexity especially in database design.
On the plus side, you can add servers relatively easy. Data is not duplicated except small lookup tables. There is very little overhead of of the scale out itself. This scale out method also adds very little overhead to database administration as well.
That said, it is a lot easier to write a few paragraphs than to actually do this and I am still of the opinion that you should scale up unless the application is truly going to be huge.
There are other methods of scaling out SQL Server like with peer to peer replication and 3rd party products but I do not have any experience with them. I would love to hear your experiences.
My RSS
-
My LifeStream
My Presence
