email article printer friendly more resources

Know Your System: Top Five SQL Server Questions
Here's a handy reference guide to the most popular SQL Server issues.
by Buck Woody

Posted November 20, 2003

Have you ever had someone who treats all technology as equally mysterious ask you to take over a SQL server? He or she assumed you could handle it—after all, you know all about that "computer stuff," right?

If you've ever found yourself in that situation, then you've probably run into a few problems along the way. More than likely you called a friend or searched the Web to find what you needed to get started, and once you got the issue fixed you left the system alone as long as it was working. But you've always had those questions keeping you up at night.

Well help is here. I've pulled together the five questions that I'm asked most often. I've provided some great SQL Server links in the Resources section if you need more info—luckily, many of the questions are interrelated.

Question 1: Help! I've been told that I have to manage a SQL Server database. I have five minutes—where do I start?

Answer: I hate to break it to you, but it might take more than five minutes. Here's what you need to know in the order that I think is most important:

SQL Server Versions, and What Differences They Make
There are quite a few versions of SQL Server out there, each with a corresponding edition. Understanding what you have is important because each version and edition has key strengths, weaknesses, and features.

4.2: This is a really early version of SQL Server, and it isn't supported anymore. You can't restore a database backup from this version to any of the versions available today. The best thing to do if you have it is to export the data into text files and import it into a newer version.

6.0: This version still kept a database compatibility with Sybase. You can't restore database backups from this version to the newer versions either, but it has a better export capability. Microsoft is still supporting this version but it isn't putting out service packs for it.

6.5: Microsoft kept the Sybase database format and some of the engine in this version. Microsoft still supports it, but not with service packs.

7.0: This version is still in high use as of this writing. It represented a complete rewrite of the database format and the engine. All of the tools completely changed in this version. It is still supported by Microsoft (including with service packs).

2000: This is the latest release, as of this writing. This version introduced improvements in almost every single area of the product, and it is quickly replacing 7.0 as the most popular installed base. Microsoft, of course, still supports it.

Those are the versions. Editions are the type of SQL Server installation. SQL Server only runs on Microsoft operating systems (from PDAs through the big iron), so there are specific designs for the code. Here's a quick rundown:

CE: The CE Edition of SQL Server needs CE 2.11 or later on PDAs. It has the same tools and SQL syntax of the big editions. You'll probably only see this version in a replication scheme with a larger edition of SQL Server.

MSDE (The Microsoft Data Engine): Organizations often install this edition with other applications such as Microsoft Office Professional. It looks and acts just like SQL Server Standard Edition, but it comes with no graphical tools and has some limitations on size and speed. For example, you're limited to databases of 2 GB or less, and anything with more than 5-10 connections starts to bog down.

Personal Edition: This edition of SQL Server works like its bigger brothers, but it won't use more than two processors.

Standard Edition: This is the primary edition of SQL Server. It can use up to four processors and up to 2 GB of RAM. It doesn't cluster, but it provides almost every other feature you could possibly need.

Enterprise Edition: This edition looks like the standard edition, but adds up to 32 processors and up to 64 GB of RAM, and it can cluster. It also has several other performance gains. Not surprisingly, you need a big box to run this edition properly.