Developers vs Administrators – Productive Problem Solving
As the DBA for your company, it’s likely that the creation and backup of tables are not your only roles. In fact, if your company is using the Internet for its own web-presence, it’s likely that you’ve run into a whole new breed of developer – the Active Server Pages (ASP) developer – and found out very quickly that you may be providing a greatly enhanced level of support for their problems.
There is a very large contingent of ASP developers that are just starting to get into writing to and reading from a database. Many of these developers are HTML-converts, and are used to being able to exist in a relatively autonomous world. HTML development was typically done where it’s possible to pay close attention to the web site and the code that makes it up, but not really have too many worries for interoperation with other aspects of a business. In many cases, if an online store was developed, it may have been a means of simply sending an email to the appropriate address with the order information enclosed.
Now the users want more – more information about the orders, the buying habits of the users, the types of things being purchased within certain periods of time and more. The users are asking that the orders be entered directly into the orders database so the information doesn’t have to be re-entered. Suddenly you have developers that need access to your system and can’t understand why they can’t have “administrator” rights to the database to get their web pages done. You’re faced with a requirement to provide support, and to help them debug their application as they move from stand-alone web page developer to database-aware developers that need to play by the same rules you’d expect of any other application or developer getting to the database.
This puts you in both a policing mode and a support mode. The policing you’ll have to work out with your own tools and approaches. Suffice to say that DBO access is not necessarily the right way to go for web-based access to your database. The last bit of policing advice is that now is the time to be sure to change that SA password from [blank] to something meaningful.
Active Server Pages Survival for the DBA
The balance of this column will give you some good pointers on where to start looking for the problems that pop up the most frequently. Some will direct the ASP developer back to the code, but will at least provide them with a starting point.
There is one important thing to understand before getting started. There is a file in the root of the web application called GLOBAL.ASA. This file has much of the database connection information that you’ll be reviewing and that the developer sets up – the server name or IP address, the user name, password and database.
TCP/IP, not Named Pipes “Could not find specified SQL Server” is one of the more common problems. When developers work on their local system, the pages will debug fine, and the web-based application will have no problems working against the database. When the page is loaded to the server and then executed, the user gets the error message that the SQL Server could not be found.
Typically in these instances the Internet Information Server (IIS) is configured to use Named Pipes to connect to the SQL server, not TCP/IP. It’s important to remember that the code running to connect to the SQL Server runs from the IIS system, not the web browser. This is why many developers are confused by the message. They know their settings are correct on their workstation and that they connect every day to the database without a problem. A quick review of the settings on the IIS box will typically reveal the setting that is incorrect. To check the settings, from the desktop, use Start, Programs, Microsoft SQL Server (either version), Client Network Utility and make sure the default network library is TCP/IP.
This is a better protocol to use, even in cases where the IIS box and the SQL server are on a common network, making Named Pipes possible. TCP/IP will handle the queuing of requests better and will have better performance in general. Even if you’re not seeing this error, you should make sure TCP/IP is the protocol you’re using.
Tip: if you continue to have problems, make sure the SQL Server is also set up to use TCP/IP. If you run setup, you’ll be able to select the protocol and enable it for the server. It can run along side Named Pipes and other protocols without any problems.
Security
When you set up security for access from the web sites, make sure you create a new login and map it specifically to the rights that you need for a given web application. Remember, this site is providing access to those outside your company. Here are some guidelines:
- Create a new login for the web application – don’t use the developer’s login. By having the login separate and distinct, you can discretely manage it, changing and even dropping the access rights it represents. Also, if the developer leaves and you turn off his access to the systems in your company, you won’t break the web applications.
- Give the login only those rights it really needs. If it’s only querying the database tables, it really has no use for Execute rights, and certainly doesn’t need to be a DBO.
- Don’t assign one common logon to be used by all web applications. Create a separate and distinct login for each application (not page, application). This way if you ever need to, you can disable an application, without taking down your web site.
If the developers get authentication errors, have them check two things. First, look in the GLOBAL.ASA and see what they’ve set the user ID and password to. Second, on the page that is using that information to connect, they should check to make sure they’re referencing the correct values from the GLOBAL.ASA (check for typos).
Performance: Sessions, Application Variables and DBA Basics
The wars between you, the DBA, and them, the developer, start when performance starts to lag. Two places to look for these types of issues are the GLOBAL.ASA file on the web server, and then at the indexes and table structures that are being used.
Connections
There are three types of connections that can be done from a web page. First, the connection can be made at a “global” level – this is referred to as using Application-level variables (read footnote 1). This is good. It is good for performance because the web server can pool connections and manage the connections better overall to keep down the connect/disconnect overhead associated with working against the database.
This type of connection is also good because the connection information is managed in one location – the GLOBAL.ASA file. This means if you later change user name and password information or other connection configuration information, the developer will be able to update it once. The changes will be reflected site-wide.
The second type of connection is the Session-based connection. This is not as good. In a huge majority of cases, though, this is where the connection will be – at the session level. You can tell by looking in the GLOBAL.ASA. Session variables are not as good as Application variables for connections because they force more connect/disconnect overhead. Each time a user hits the site and starts a new connection, the IIS server re-establishes the connection to the database. On a well-trafficked site, this can be a substantial delay to the web user.
The third type of connection should simply be outlawed on your system. This is where the developer puts all of the connection information on each page that needs access to the database in the web application. When this is done, it cannot be managed centrally, cannot use the connection pooling, and is expensive for connect/disconnect times to the database. Avoid this if at all possible, and ask that your developers do not deploy applications written with this approach.
Database Design
There is often a rift between the developers and the DBA because the developers are not database design experts, and the DBAs see it as a matter of course – of COURSE you have to design it before you use it! This is one area that you can really help out – and you’ll want to because it pays big dividends to the developer, the users of the applications and you in your support of the application.
Things to consider and make sure are part of the overall implementation:
- Indexes
- Join structures
- Views
- Use of stored procedures
You can have a huge positive impact on the database and the overall performance of the application and your server by helping out in these areas. Typically the developer will set up the primary key because it’s required (or highly emphasized) by the development tools. Indexes, however, are another story. Just by adding indexes that make sense, you can shave significant blocks of time off the execution of the application.
Some Final Thoughts
Try to get involved in the development process from the start. By sitting in on a few meetings throughout the development cycles, you can offer help in the design, experience in running the server and what will be needed in terms of backups, replication, interaction with other applications and so forth.
It sounds obvious, but explain what you’re doing. I’ve seen a lot of DBAs get frustrated with developers (of all types) and run off exasperated and do what needs to be done – in a vacuum. Teach people about indexes and the things that make your servers sing. They’ll listen, it makes everyone look good when the application works well!
1. Note, we’re referring to application level connection variables, NOT making the connection to the db in the global.asa. You’ll need to open, use AND CLOSE the connection to the database in the ASP page itself. But defining the application variables that set up the connection is the key.
