More SQL Server 2005 Express Troubleshooting Tips - dummies

By Robert D. Schneider

Predicaments are possible with SQL Server 2005. Here’s a smattering of those that you’re likely to encounter.

I want to automate some operations

SQL Server 2005 Express offers two very helpful features that you can use to help streamline common database tasks:

  • Stored procedures and functions. Stored procedures and functions are bits of logically grouped application software that you can write in a variety of programming languages, including Transact-SQL, Visual Basic, Visual C#, and so on. After you create them, you then place these procedures inside the SQL Server engine, where anyone with the right permission can run them. They centralize your application logic, and generally help performance to boot.
  • Triggers. Think of a trigger as a very specialized stored procedure, one that gets run when a certain event happens. For example, you may want to send an e-mail alert when inventory drops below a certain level. That’s a great use of a trigger; you can probably think of many more that apply in your organization. You can also use triggers to help you administer your database server, as well as run administrative operations.

I want to simplify my data

As a database administrator, making sense of your information can be confusing, especially if your environment sports a substantial number of tables with complex interrelationships. If you find it difficult, imagine how laborious it is for your users and application developers. Luckily, none of you has to suffer in silence. One way to create a more transparent picture of your data is to take advantage of views.

Think of a view as a window into your information, one that can span the entire database to retrieve results. By pre-building all the joins and stripping out any extraneous details, you can make this window much simpler than the underlying data. The end result is that your users and developers can work with the view, rather than the base database tables.

I want to build good software

If you’re looking to construct some high quality software, here’s some good news. A wide range of excellent tools work really well with all the SQL Server products, including the Express edition. Here are three that you should look into:

  • Visual Studio: This flagship of Microsoft’s development tool product family is feature-packed, supports several popular programming languages, and you can use it to build the most rich and complex applications. On the other hand, if you don’t need all that power, check out the next products on my list.
  • Visual Web Developer 2005 Express: This entry-level product is designed and priced so that a large audience can use the technology to create Web-driven, database-ready applications.
  • Visual Basic Express: Visual Basic is an extremely popular programming language, especially for traditional client/server applications. This Express version is aimed at the same audience as Visual Web Developer 2005: Developers who want a low-priced, easier-to-use tool that still offers substantial capabilities.

If you have a different taste in programming languages, don’t worry: You can develop software that works with SQL Server 2005 Express in just about any language. In fact, Microsoft offers Express editions of its Visual Studio product for other languages, such as Java, C++, and C#.

My database server is too slow!

Before you toss your slow-running database server out the window, you can run a few effortless checks to identify and remedy the source of the headache:

  • Are your tables indexed correctly? Without a doubt, improper or missing indexes cause most of the performance problems that plague the average database application. You need to take the time to make sure that you’ve placed indexes in the right places.
  • Is there enough memory? Don’t shortchange your database server by denying it the memory it needs to get the job done quickly. You can quickly tell if you’re running out of memory by launching the Windows Task Manager, and studying the amount of available physical memory. If this number is approaching zero, you’re asking your server to do too much work with too little memory.
  • Are there too many users and applications? Sometimes, no matter how much memory you install, or how well your tables are indexed, you approach the limit of what a database server can handle. There’s no hard-and-fast way to tell if you’re on the brink, but if you exhaust all your other options and you can’t coax any more speed out of your server, you should distribute your workload among multiple servers.