T-log Shipping for Load Balancing

Posted: October 2, 2008 in Uncategorized
Tags: , , ,


A SQL on Call Client today got some ideas over the last couple of weeks about how to improve the performance of their database. They were under the impression that they could use database mirroring to take some of the load off the production SQL Server. The client had one single SQL Server where the reports were hitting the same database as the web store. I think the most active connections I have seen on the server had been 29 at one time. Yet processor utilization is way up, memory tipping the scales and disk I/O was light.

Somewhere along the way the client got the idea that they could use database mirroring to create a reporting server. Now from what I know this is a possibility, however it would require the mirror to be on Enterprise Edition and would require that the mirrored database to take database snapshots. When the client learned that the second node would require an upgrade and they did not like the idea. It got me thinking to some options that we had.

We ended up putting in T-Log shipping and changing the connection string on their reports to point at the secondary server. The good news is that the t-log shipping was a quick way to put in place a reporting type server. The bad news is it did not fix the issue. Now the reports may run faster but we are seeing a number of lock waits and dead locks on the production web server that we saw before but the client believed they were related to the reports. When we first started this we had a discussion about how much of their code needed to be moved from dynamic SQL and into compiled stored procedures. Well the first talk is going to be the correct solution. A couple things that I wanted to mention as I finish this up. One of the quickest ways that we were able to find the lock and dead locks was by installing a performance monitoring tool that we have. With in an hour I was getting notices that we were seeing some delayed code. I can assure this is what we will be working on this weekend. Second thing that I wanted to mention refers back to a discussion with Rob Bagby. Rob mentioned that good consultant is not going to look at just the task they are created but they are going to make sure that they understand the “why” around it. Why did the client want to do database mirroring, why did the client believe their system was slow? We had to get that base line on the reports and in the process the client did not supply us with web site baselines.

Think of it like this… If you drive a Ferrari a 0-60 in 8 seconds is going to be slow, but if you drive a skate board 0-60 in 8 seconds will rip your head off.

I am interested in how you improve communications with clients, how do you talk to them when sometimes they just are on a path that they will not deviate from? And speaking of asking for your input, Buck Woody commented on a post not to long ago. Buck thanks for the insight, I just got to see the interview you did with Stephen Wynkoop and I can not wait to see your sessions.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s