An interview with Linchi Shea

 

by Douglas Reilly

Linchi Shea is a database expert specializing in enterprise management for SQL Server. He has written a series of articles for SQL Server Professional and what is arguably the book on SQL Server administration, Real World SQL Server Administration with Perl. I have seen Linchi on a number of SQL Server-related mailing lists, and his answers were always on point.

Linchi works at a major Wall Street firm and is responsible for SQL Server product engineering. He is a Microsoft MVP for SQL Server.

Linchi answered the following questions via email.

 

Doug: How did you get involved in database-related work? Was it a natural fit, or an accident of convenience?

 

Do you know someone who deserves to be a Database Geek of the Week? Or perhaps that someone is you? Send Doug an email and include “Database Geek of the Week suggestion” in the subject line.

 

Linchi: Though I had dabbled in academic database issues, I didn’t start working with a client/server DBMS professionally until about 1995 or 1996. The company I worked for was a Microsoft shop, and SQL Server was a natural fit for the scope of its applications. So it was more of an accident, but one I’ll never regret.

 

Doug: How did you become interested in Perl as a tool for managing SQL Server in the enterprise? Is there Unix in your past?

 

Linchi: There is Unix in my background, but it had nothing to do with how Perl got into the picture of managing SQL Server. Perl and SQL Server got together by accident.

In early 1996, when I started working in Vancouver as a SQL Server DBA, I was handed some scripts to help with the database releases of an application under development. One of the scripts was run prior to the actual execution of each database release. It retrieved the T-SQL scripts for the new table schemas from SourceSafe, retrieved the table schemas from the live production database, compared their differences, and printed a brief summary of the changes to be introduced in that release. The summary was reviewed against the release plan to ensure that those were the intended changes.

When all the release steps were finished, I would run the script again to verify that there was no schema difference between the version in SourceSafe and the one in production. This script, which was written in Perl, proved very useful; it gave us confidence before we started to change data en mass, and gave us reassurance after we had made changes.

I didn’t know anything about Perl back then, but I was impressed by how easy it made certain tasks. I have been hooked on Perl ever since as a tool for automating various SQL Server administrative tasks, big or small. Scripting in Perl is an empowering experience. When ready-made tools fail or are not available, or when existing scripts no longer work correctly-and sooner or later everyone will be in that situation-I know I have something reliable to resort to, and I can create the tool I need quickly.

I’m not a Perl die-hard, however. My universe is not centered on a particular programming language. While programming language fads come and go, the constant is problem solving. That is my primary concern.

 

Doug: Have you played with SQL Server 2005, especially with respect to administration in the enterprise?

 

Linchi: In terms of manageability in the enterprise, SQL Server 2005 has made significant progress. Examples to support this claim are numerous, but the best is the introduction of dynamic management views, which make it a lot easier for administrators to see what’s going on inside SQL Server. This should please the SQL Server capacity planning folks and the performance troubleshooting people in the enterprise.

The SQL Server 2005 cluster install left a favorable impression as well. Back in SQL Server 7.0 days, mess would be a nice word to describe installing SQL Server in a cluster. SQL Server 2000 made more pieces cluster-aware, but still came with annoying quirks. The SQL Server 2005 cluster install is more intelligent, resulting in a better user experience. As a case in point, I was delighted to find that you don’t need the disk resource group on the node where you are running the install. It just installs wherever the resource group may be. Wow!

For enterprise deployment, I’m interested in automating and customizing the install. SQL Server 2005 is a major step forward compared with SQL Server 2000. Now, you can do unattended install in a cluster, whereas that’s not supported in SQL Server 2000. I’m also happy with the inclusion of the system configuration checker as part of SQL Server 2005 setup. But there is still room for improvement in enterprise deployment.

I’d be ecstatic to see Microsoft provide an API into the system configuration checker, for example, or allow us more control over its operation from the command line, or enable the enterprise to add additional check steps and customize existing steps.

 

Doug: The administrative tools for SQL Server 2005 are a dramatic change from previous versions. Do you think you will have a problem moving to the new administrative tools?

 

Linchi: For the GUI tools, I don’t think there will be an issue after the initial learning curve, which shouldn’t be that steep. The learning curve will be steep when it comes to dynamic management views, SMO, and tools for managing database mirroring, security, CLR integration, and a slew of other new features.

SQL Server 2005 presents a challenge for people who want to glue together various SQL Server 2005 pieces to automate administrative tasks. There are simply a lot more things to deal with. And in addition to VBScript or Perl, one may be forced to venture beyond scripting to become comfortable in C# or VB.NET, for example, when needing to automate the administration of reporting services.

Learning C# or VB.NET isn’t a bad thing. But for automating SQL Server administrative tasks-or Windows administrative tasks, for that matter-I consider a unified and pervasive scripting language a better model for administrators. I’m looking forward to finding out whether the Microsoft Shell (MSH) planned for a future release of Windows can fill that bill.

 

Doug: The description for your book on administering SQL Server with Perl mentions that the tools provided with the book are not version-specific. Is there anything in your Perl scripts that had to be modified for SQL Server 2005?

 

Linchi: Many of the scripts will not work with SQL Server 2005. But the point has always been that you are expected to modify and adapt the scripts. When you script, you don’t always do so using a supported API. A script often takes the text output from one tool, parses it, and combines it with the output from another tool to get the job done.

Different releases of SQL Server may generate the same text output in different formats, and the script would have to be revised accordingly. This way of scripting may appear to be brittle compared with a program that is written to a documented API. But a fact of life in an administrator’s world is that you don’t always have an API to work with, or it’s not convenient to work with an API. There is no API for parsing SQL Server error logs, for example, if you want to do a summary report of all the error logs across your production servers. The ability to write a script quickly becomes the key.

 

Doug: What sort of work you are doing these days?

 

Linchi: I’m doing so-called SQL Server product engineering for my company. It involves maintaining the SQL Server roadmap, testing/certifying SQL Server components and products in various configurations, figuring out whether we can support them and how and when we can support them, creating SQL Server enterprise core builds, and handling SQL Server product-related issues escalated from operations. Part of my job is to shake down SQL Server in various hardware/software configurations. I like kicking tires.

One of the things I’ve been working on that is near completion is creating a TPC-C-style benchmark and stress-testing tool in C#. This would give us more flexibility than the benchmark kits from vendors in evaluating specific features of SQL Server under controlled synthetic workloads.

Another project is to evaluate SQL Server 2000/2005 on larger boxes, like 16-way, to help formulate a viable strategy to cope with the demand from applications outgrowing the processing capacity of any commodity servers.

 

Doug: What do you think about possibly using VB.NET or C# for stored procedures, functions and triggers? Especially in the enterprise, how do you think CLR stored procedures will fit in?

 

Linchi: I think it’s an exciting feature. People may misuse it, but that should not stop us from using it properly to do great things. Knowing how powerful regular expressions can be from Perl, I’d welcome the disappearance of all those hellishly messy T-SQL expressions for parsing even simple strings. A lot of DBAs are worried about what this may do to their production servers, and they may not have the time or skill to prevent crappy CLR procedures from slipping onto their production servers. But resistance is futile!

My guess is that CLR procedures will march into enterprise data centers no matter what. If they are not from internally developed applications, they’ll get there via vendor packages. If a critical business needs a package to make money, who’s going to say no if the package happens to use some CLR procedures in SQL Server? If you can’t fight them, join them! That’s what I’ll do.

 

Doug: Have you read any good database-related or general programming books lately?

 

Linchi: SQL Server white papers and web articles have occupied most of my reading list. I’m trying to digest all the SQL Server 2005 articles from the Microsoft MSDN/TechNet sites in particular. On the programming front, I’ve finished reading several O’Reilly Media books on C#, including Programming C# by Jesse Liberty, C# Cookbook by Jay Hilyard and Stephen Teilhet, and ADO.NET Cookbook by William Hamilton.

I’m a big fan of programming language cookbooks, and that almost got me into trouble. I remember once, after I had submitted a book receipt for reimbursement, I got an email asking me to justify why I needed a cookbook and for what project. It turned out to be The Perl Cookbook by Tom Christiansen and Nathan Torkington, also from O’Reilly Media.

 

Doug: Can you think of a cool tip or trick to help folks managing SQL Server in the enterprise?

 

Linchi: Automation leads to relaxation!

 

Doug: I see you are a fellow Microsoft MVP, in your case in SQL Server. What do you think of the MVP program? Will you be at the MVP Summit this year?

 

Linchi: I attended the SQL Server portion of the 2005 MVP Summit in Dallas. There was a schedule conflict between the MVP Summit and the SQL Server PASS Summit 2005 conference held in Dallas. So the SQL Server portion was moved to Dallas. I thoroughly enjoyed both PASS and the MVP Summit.

The MVP program is a great idea, and being part of the program has been a great experience. It has helped make the SQL Server communities as dynamic as they are. You know what happens when you get a bunch of these SQL Server guys together-you can almost feel the force :-).

I was a university lecturer for a few years, and I discovered that one of the most effective ways to learn something is to share it. Sharing a piece of information forces you to be careful, to research it, verify it and debate it from multiple angles. The MVP program reinforces that belief.

 

Doug: What music do you listen to these days?

 

Linchi: I enjoy all varieties of music from the 1980s. When you move to a totally new place, things that happen around that time become unforgettable. I came to North America in 1984, and was bombarded by music from bands like Talking Heads, Dire Straits, Huey Lewis and the News, Duran Duran, Jefferson Starship and Tears for Fears, and have had trouble kicking them off every since.

 

Do you know someone who deserves to be a Database Geek of the Week? Or perhaps that someone is you? Send me an email at editor@simple-talk.com and include “Database Geek of the Week suggestion” in the subject line.