Troubleshooting SQL Server 2008
Given all the things you can do with SQL Server 2008, it’s natural that you might get confused from time to time. What follows are a few of the most common predicaments that you’re likely to encounter when using SQL Server, and how to overcome them.
Problems installing the SQL software
Having some cool new software and not being able to get it installed isn’t much fun. Luckily, SQL Server 2008 usually gets up and running without a hitch. If you do encounter an obstacle, use the following checklist to help you avoid hot water:
Make sure you have sufficient permissions to add or remove software.
Generally, a good idea is to install or remove software as an administrator. Otherwise, the operating system might block you from making these changes.
Ascertain that your computer is powerful enough to support the product.
SQL Server gobbles memory, CPU, and disk resources. Trying to install it on a lightweight machine guarantees frustration.
Remove any previous versions (such as beta installations) of SQL Server 2008 via the Add/Remove Programs application within the Control Panel.
If you skip this step, a good chance exists that the installer will complain loudly and then keel over. Even though it’s tedious, take the time to clean things up before trying to install.
Make sure you have all necessary supporting software.
Generally, the SQL Server installer is quite intelligent and diligently acquires whatever is missing (for example, Windows Installer, .NET Framework 2.0, SQL Server Native Client, and so on). However, you might need to obtain these components yourself.
Problems administering your database
A database administrator’s work is never done. Fortunately, Microsoft didn’t skimp on the supporting tools. Here are two good choices to get the job done quickly and easily:
SQLCMD utility: This character-based tool ships with every copy of SQL Server. You can run just about any administrative task using direct Transact-SQL or one of the hundreds of built-in system stored procedures.
SQL Server Management Studio: If you have more of a hankering for graphical tools when it comes to administration, you want to look at this utility. You can perform just about any administrative chore you might ever face. And, whatever isn’t possible can be handled with direct Transact-SQL or system stored procedures.
Problems with your data, itself
Unless you believe in gremlins that descend from the ether and wreak havoc on your data, chances are that any information problem is because of an error or omission. Here’s what to watch out for:
Referential integrity issues: To help keep all your data synchronized, SQL Server offers referential integrity features. These prevent you or your applications from inadvertently altering rows from one table without making corresponding changes in another table.
Failure to use transactions: Transactions help certify that your database interactions happen in logically consistent groups. Without proper transactions, an operation may update one table but fail to do the same for other tables. The result is damaged data integrity.
Incorrectly defined columns: Believe it or not, sometimes database designers choose the wrong data type when setting up their tables. For example, a particular field might need to contain currency amounts, which include decimals. Yet when designers write the SQL to create the table, they choose the INTEGER data type — SQL Server discards any fractional amounts from the column.
Not enough space for character-based fields: Another common problem is database designers not providing enough space for character-based fields. Again, SQL Server cheerfully tosses away any extra data, which leads to damaged information and unhappy users.
A database server that’s 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. Take the time to ensure 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 tell if you’re running out of memory by launching the Windows Task Manager and viewing 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 whether you’re on the brink, but if you’ve exhausted all your options and you can’t coax any more speed from your server, then distribute your workload among multiple servers.
Are you taking advantage of SQL Server’s performance tools? There’s no need to guess about what’s causing a responsiveness problem because SQL Server offers a collection of excellent performance monitoring and management tools.
After setting up an SQL Server Reporting Services (SSRS) server, it’s common to “lose” the URL you need to access the reports. The actual URL you use depends on whether SSRS is installed in the default instance of SQL Server or a named instance.
You can access both the report server (which lists all your reports) and the Report Manager (which allows you to manage many of your reports) via web browsers.
Finding SSRS on a default instance: To access the SSRS report server (which lists all your reports) on a server named SRV1, use the following URL:
To access the Report Manager (which allows you to manage your reports) on a server named SRV1, use this URL:
Finding SSRS on a named instance: When accessing an SSRS that’s installed on a named instance, you need to add the instance name to the end of the URL preceded by an underscore.
For example, if your named instance were MyReports on a server named SRV1, the URL for the report server would be:
To access the Report Manager on a server named SRV1 with a named instance of MyReports, use this URL: