SQL Server 2005: Scaling Up and Out

Ron Talmage

Joining the CoDe Magazine E-Column and E-Mail Newsletter this month is the SQL Server Observer edited by Ron Talmage. Ron is a CoDe Magazine author, SQL Server MVP, and current president of the Pacific Northwest SQL Server User Group. SQL Server Observer will cover technical topics that bear on directions and trends in the SQL Server community.

It’s been a big year for SQL Server 2005. The release has done well, gets lots of good press, and despite some pain points, it is definitely a marked improvement over SQL Server 2000. What I’d like to do is summarize some important positives and then focus on what is shaping up to be the most important strategic direction for the database system: scaling out.

Huzzas

There’s been a lot of interesting buzz surrounding SQL Server 2005, which you can basically summarize as What’s New? It took Microsoft much longer than usual to develop SQL Server 2005, over six years, and so most of their focus has naturally been on new features. And they are legion, especially in the BI area.

The dramatic revisions in Analysis Services, as well as Integration Services and Reporting Services, have proven to cause some pain for those wishing to upgrade. In many cases, you simply must redesign cubes, rewrite packages, and recode reports.

On the database engine side, new features such as the integrated CLR as well as Database Mirroring have tended to grab most of the attention. In fact, the plethora of new features plus the ease of upgrading the database engine has tended to lead many into thinking that there aren’t significant changes in the engine itself.

But just take a quick look at the operation of Database Mail, and the dialogs for replication and log shipping in SQL Server 2005, and you’ll soon see that Microsoft improved a lot. Features that used to be painful, such as SQL Mail, or confusing, such as replication, now just work and are almost as close to intuitive as they can get. Features that used to take much more time than seemed right, such as creating a Full-Text catalog, are now vastly improved in performance as well as operations. The list goes on and on.

The Task Ahead

Despite all these major improvements, one area that SQL Server 2005 takes some small jabs at, without hitting squarely yet, is scaling out. When many experts discuss scalability they distinguish scaling up from scaling out. Scaling up means replacing or strengthening current server hardware, whereas scaling out means adding equivalent (preferably cheap) server hardware alongside the server currently in use.

Scaling Up

SQL Server 2005 makes some interesting strides in support of scaling up. You can install it on 64-bit hardware running Windows Server 2003 x64 version. The SQL Server 2005 Standard Edition has no limitation on RAM, so the x64 version can address the entire usable RAM on a 64-bit machine. You can even create a two-node cluster using SQL Server 2005 Standard Edition, whereas the Enterprise Edition now supports as many nodes as Windows failover clustering will support, as well as hot-add memory for Windows Server 2003 under most conditions.

Scaling Up tempdb

When tempdb becomes too active on a server, generally Microsoft recommends creating as many tempdb data files as there are CPUs on the machine (counting dual-core sockets as two processors.) That will enable SQL Server to use some asynchronous I/O to tempdb, but sometimes even that is not enough.

I recently saw an ASP site that used three instances of SQL Server 2005 on an x64 box in order to create three tempdbs. The old server had several independent databases on it that they divided across the three SQL Server instances. The machine has 8GB of RAM, so they simply assigned 2GB to each instance, leaving the remaining 2GB for Windows.

But this is just a workaround because there’s really no need for the multiple instances. In my opinion, the customer only needed one SQL Server 2005 Standard Edition instance to address the 8GB of RAM. Many DBAs have asked Microsoft for the ability to enable multiple tempdb databases within a single SQL Server instance. Some competing database systems do that, so perhaps Microsoft will take SQL Server in that direction.

However, you can scale tempdb out too, in some contexts, as you’ll see in the section on scalable shared databases below.

Scaling Out

Scaling up, i.e., replacing or beefing up the current server, works well for pain points such as CPU and memory bottlenecks. However, it does not generally address issues involving I/O, either for tempdb or for database files.

In addition, scaling up is expensive. Putting twice the power into a server may cost more than twice the price. What DBAs dream about is scaling out. That is, adding additional peer servers at a low cost and spreading out the application, perhaps using load balancing, across the peer servers.

Scaling Out: Separating Reads from Writes

The classic SQL Server solution for gaining additional CPU, RAM, and I/O capability by scaling out is to separate read activity from write activity, and put as much read activity on additional servers as possible. To keep the data up to date, the technology of choice is one-way transactional replication. With separate reporting servers that subscribe to data in near real-time, you can take the query load off the main server and direct it to the (often less expensive) reporting servers. This solution works well when you can safely separate reads from writes, and you can tolerate a few seconds of latency due to transactional replication. It is now somewhat common to see some reporting servers where the application uses standard load balancing techniques to spread the query load across a set of read-only subscriber servers.

Scalable Shared Databases

After the release of SQL Server 2005, Microsoft announced a new scale-out feature in a KB article (910378, dated November 23, 2005). For a scalable shared database, you can mount a read-only copy of a database onto a SAN volume that supports shared data. Then many different SQL Server instances can access the read-only data for querying.

Scalable shared databases effectively allow you to increase the CPU, RAM, and tempdb resources available for querying data, by adding additional peer servers. Each server adds its own CPU and RAM (for data cache) to the mix, but in a horizontal fashion. In addition, each server has its own tempdb, which can be helpful if the queries make extensive use of work tables and tempdb objects.

Well, does the I/O scale out? The answer is yes, but it takes a little explanation.

From the UNIX World: Clustered File Systems

For years the UNIX world has been working on a different type of clustering than Windows, known as clustered file systems. Numerous versions are out there, but the one most known to SQL Server DBAs is PolyServe. The PolyServe Matrix Server is a service that runs on servers attached to a SAN, and makes a set of them into a cluster (or matrix.) At the same time, the Matrix Server uses a Windows NTFS-compliant file system, called PSFS (PolyServe File System) that exposes selected SAN volumes as shared to all servers in the matrix.

Why is that interesting? Because then each server has its own independent I/O path to shared data files. For a scalable shared database, each server issues queries using its own HBAs (Host Bus Adaptor-the way of connecting to a SAN when using fibre channel) directly to the data. Now you’ve suddenly scaled out your I/O.

Of course, now the problem has been pushed down to the performance of the disk subsystem itself. But that’s something to address by other means, such as tuning the SAN configuration (and buying faster hard drives!)

The main limitation with scalable shared databases is that you must take them offline in order to refresh them. The reader servers must detach the databases; you place a new copy of the OLTP database in its place, mark it as read only, and then attach the databases back to the servers. So the scalable shared database is almost always going to be out of date compared to the original OLTP database. For those who require near real time data, the transactional replication solution is more viable.

Where Next?

There’s much more to the clustered file system story and how to scale out. The ultimate goal is not to scale out just reads, but also to scale out writes. In my next installment, I’ll look more closely at where SQL Server seems to be headed in order to reach that goal.