An interview with Scott Forsyth

by Douglas Reilly

After interviewing a number of database geeks, it struck me that many of them focus on one area of database development. Hilary Cotter specializes in replication, for example, while Itzik Ben-Gan focuses on Transact-SQL (see http://www.simple-talk.com/categories/sql-articles). Scott Forsyth is no different, but his area of expertise is more unusual: web hosting using .NET technology.

Scott is the director of IT for a premier web hosting company called ORCS Web, and has spent a lot of time working on SQL Server 2005 issues related to that business. Whereas most SQL Server developers spend time figuring out how to do certain tasks, Scott spends his time restricting what developers can do so they don’t bring down shared database servers.

When walking to an evening event at the Microsoft MVP Summit, I spoke with Scott about his previous jobs, and wanted to know what career choices led to his current position. While swapping dot-com war stories, he told me about an interesting company he started, and I followed up via email with additional questions. Our conversation appears below.

Doug: Can you elaborate on our conversation regarding your restaurant-related dot-com company?

Scott: In 1999, a partner and I started an online restaurant ordering company called Let’s Dine Online, which enabled people to order dine-in, takeout or delivery meals over the Internet.

We partnered with a few dozen restaurants with different cuisines and put their menus online, and teamed with a local company that specialized in delivering hot food. The dine-in service targeted the lunchtime business crowd; people could order in advance, have a hot meal waiting when they arrived, and enjoy a nice meal at the speed of a fast-food restaurant.

I was proud of the system and thought it was the best restaurant ordering system at that time. It was built on Classic ASP. The prototype started off in Microsoft Access, but moved to SQL Server 2000 as time went on.

It was a great experience for me as a programmer and a businessperson. I had been doing web development for three years prior to starting the company, but the experience helped build my love for technology. I also learned to manage an office and staff, prepare detailed business plans, understand corporation finances, negotiate, and present the business to investors after the dot-com bust.

Unfortunately, even with daily customers, the business wasn’t self-sustaining. Difficult as it was, we finally considered the business a failure and abandoned it.

Doug: When I’ve been involved with a hosting company, it has been daunting to use Enterprise Manager to connect to a database server that has dozens or even hundreds of databases, each representing a different customer’s database. Does SQL Server 2005 make it easier to separate one user’s database from the others?

Scott: Security isolation between databases has been strong within SQL Server for years, and a database owner on a well-maintained, shared database server can feel confident that his or her data is secure.

A user still sees a list of all databases on the server, however, even if he or she has access to only one of them. In January 2005, Microsoft released a solution for this problem for SQL 2000. We implemented it at ORCS Web immediately after testing it, and have been pleased with the results.

In theory, SQL Server 2005 restricts access to all objects, so only people with adequate permissions are able to tell if the object exists. In other words, if you tried to query a table for which you don’t have permission, you’ll get a message that says either the object doesn’t exist or access is denied.

This is a great step for security, because users without proper permissions can gain very little information about other objects on the same server. One exception is sys.databases, and SQL Server Management Studio lists all databases for anyone who has the least bit of permissions on the server.

This is a step back to pre-January 2005, which is a disappointment. Here is a recent discussion I had with Steven Twitchell, who was very helpful in answering this question: http://forums.microsoft.com/msdn/showpost.aspx?postid=116473&siteid=1.

One of the goals of SQL 2005 and many of the Microsoft development tools is to use Visual Studio 2005 and/or Visual Web Developer for database administration. The SQL tools included with these development tools are excellent and can be used instead of Management Studio for remote development.

Visual Studio 2005 and Visual Web Developer were developed with a shared environment in mind, and list only the databases to which you have access. It’s too bad that Management Studio didn’t do the same.

Apart from listing databases on the server, SQL Server 2005 adds quite a number of major security enhancements to SQL Server 2000, making it obvious that Microsoft has made security a top priority.

Doug: One feature of SQL Server 2005 Express that was initially encouraging but turned out to be disappointing was Xcopy database deployment. What did you originally hope to do, and why didn’t it work?

Scott: In the planning days of SQL Express, the goal was to allow for XCopy deployment to shared hosting environments. It was an exciting idea for third-party product vendors, starter kits and other pre-packaged solutions, not to mention making general development and deployment easier. The idea was that you could create a database on your local computer, and through FTP or some other XCopy method, copy the database to the server.

It works like Microsoft Access, but with performance up to the level of SQL Server. SQL Express, unlike Microsoft Desktop Engine, doesn’t have a governor. It is limited to a single CPU and has RAM and disk space limits, but for the general web application, there are a lot of fairly busy sites that would do well with it.

In a shared hosting environment, the show-stopper is that the sqlservr.exe process does a RevertToSelf. So even if the host turns on user impersonation, the process will revert back and run under the permission of the application pool, assuming IIS6. Most shared environments will put sites in bundles and share the same application pool, so all the sites in the same application will need to have the same write permissions and be able to read each other’s databases.

This refers to the default setting, which enables “user instancing.” Mdf files will be automatically linked to the SQL Server instance, without the server administrator provisioning the database. It can be turned off, and the security hole can be addressed, but the benefits of XCopy deployment disappear.

I won’t go into further detail except to say that during beta testing we concluded that we had three options. The first is that user instancing will have an unfixable security hole. The second is that service instances and XCopy deployment will have different sets of issues, leading to the third option, which is go back to a stand-alone SQL Server. In some ways this is a relief to a hosting company, but it is not as convenient for the developer.

All hope is not lost. Microsoft is working on a solution to enable XCopy deployment of SQL Express databases. A developer will be able to upload a database and in one click through a control panel, migrate it to a full-fledged SQL Server instance that the host provides. Although still in development, some information is available at: http://weblogs.asp.net/scottgu/archive/2005/10/15/427581.aspx.

Doug: As someone monitoring a number of shared database servers, you must see all kinds of databases, from the elegantly designed and tuned to the ones upsized from Microsoft Access that have field names containing spaces and punctuation marks. From your vantage point, do you have advice on whether to use stored procedures or not?

Scott: That’s a loaded question because it encompasses performance, maintainability, nTeir architecture, and even consideration of the expertise and preferences of the developers. With tools like LLBLGen and other O/R mappers, many complex projects don’t use stored procedures at all. Yet there are many obvious situations in which a stored procedure is essential to performance.

Consider a case in which there are multiple datasets retrieved and only a subset of data returned across the wire. There are other situations in which wrapping a single select statement in a stored procedure doesn’t have noticeable performance gains either.

From a hosting perspective, it is often difficult to tell what is at fault. Often we’ll troubleshoot an issue and give the developer a list of queries that need to be reevaluated, or indexes that need to be added to the database. Our surface-level understanding of the application isn’t enough to tell if moving some database logic into the database would be beneficial or not.

I agree with the idea that performance and maintainability are better when stored procedures are used, but each situation must be considered individually.

Doug: What tools do you use to troubleshoot database performance problems?

Scott: Before touching any tool it’s important to reproduce the problem. If I don’t test in advance and then tweak a setting or two and performance improves, I can’t tell if the problem has been resolved, regardless of how good the tool is.

If I can consistently reproduce the problem, I’m 80 percent of the way toward resolving it. There are few issues that can’t be resolved if they can be reproduced. The most difficult problems are the vague ones that happen intermittently.

Different tools are required for difference situations. For a performance-related problem, I’ll check Task Manager and/or Perfmon to find out which system resource is the bottleneck. There are a variety of stored procedures that help isolate locking and other problems. We use an excellent CPU tool put together by Bill Graziano of www.sqlteam.com. It tells you which database user is using the CPU over a certain period of time.

Profiler, another favorite of mine, is excellent for seeing what is happening. As for third-party tools, I use different ones here and there, but none on a regular basis.

Doug: What do you think of the developer tools included with SQL Server 2005? What is your favorite part?

Scott: It feels strange to use an environment so similar to the one I use for ASP.NET development while working with the database. While it requires a different mindset than SQL 2000 and earlier, that is my favorite part. I’m already familiar with the tools, so the working environment doesn’t get in my way.

Doug: The addition of CLR stored procedures and triggers in SQL Server 2005 presents a different set of issues for you at a hosting company, compared with the average user. What do you expect to do about CLR in a hosted database environment?

Scott: Our tests have been good and it doesn’t take anything special to manage it, but we decided to leave the CLR option disabled on our shared database servers for the time being. We want to give the market a “burn-in” time and see what the response is, and if there are any surprises with regard to performance and security. We don’t want to run into new CLR problems on a shared production server so early in the game.

Doug: When you program in .NET, what is your favored language?

Scott: My love for programming started with GWBasic when I was 11, and after Classic ASP using vbScript, I went in the VB.NET direction. I can understand C# when I’m reading it, but I haven’t taken the time to master it. In VB I don’t have to think about the syntax while programming, so I prefer VB.NET when given a choice. I would not have a problem standardizing on C# if the team I was working with used C#, but 95 percent of what I do is in VB.NET.

Doug: I understand you have been working on a new control panel application for ORCS Web. How is that coming along, and how have you found working with .NET 2.0?

Scott: It’s our highest priority project right now, but it’s amazing how many things come up that delay working on it. We have a unique way of servicing our customers at ORCS Web. Our response time is fast, every time. And we’ve made our niche in the market with personal, friendly support, as if we are an extension of our customers’ business.

Customers can send us email and we take care of their problems directly. This method is archaic and manual to some people, so we’re looking forward to offering personal email support and a control panel option, enabling customers to use the method they prefer.

When deciding how to provide a control panel, we considered pre-built solutions, but none met all of our objectives. One problem with third-party solutions is that they require that an agent be installed on the servers. We’re developing one that will be agent-less; we will be able to deploy a new server by setting its name, IP and role, and it will be ready for use. Maintaining agents brings a level of instability, maintainability and performance overhead we don’t want to have.

As for developing in .NET 2.0, I love it. The ASP.NET team has done a great job and I am impressed by what I find in the framework and in the 2005 generation of development tools. Because development on this project was started after Beta 2 was released, we were able to start fresh, without the need to maintain legacy code.

A clean start on .NET 2.0 using Visual Studio 2005 has been a refreshing experience.

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.