SQL Server Reference Guide

Hopefully you're a trained technical professional with lots of experience running large-scale database systems. If so, you can learn a lot about SQL Server programming and advanced concepts like Business Intelligence (BI) here at InformIT. Perhaps you're new to SQL Server and you're preparing to take over and manage one of the database platforms. Maybe you're a developer and you want to learn more about managing SQL Server. This guide is tailor-made to help you learn a great deal about SQL Server.

But perhaps your situation is a bit more dire than that. You might have a crisis on your hands, or you've been given a SQL Server system to manage with no training and only a little time to bring yourself up to speed. This article is for you.

I want to make sure that you understand the limitations you're under, and use this information accordingly. One tutorial is far too little space to make you an expert. You're not going to learn to install SQL Server properly, learn write good code or completely tune a database's performance in 10 minutes. What I'll do here is give you the base amount of information you absolutely need to know in three areas: Tools, Troubleshooting and Where to find more information. Along the way (as I've been doing already) I'll link to other articles I've written to help you learn more about that particular topic.

SQL Server Defined

I'll start with an introduction to the system itself. Look for links within this text (when you have time) to learn more – for now you need to know these basics. SQL Server is a Relational Database Management System (RDBMS) that runs on platforms as diverse as tablets and smartphones up to 64-bit Clustered Servers. In all “Editions”, SQL Server runs only on Microsoft operating systems. It can also run inside a Virtual Machine (again, the guest must be running almost any modern Microsoft operating system). By and large, that means that the database runs in one place, and lots of users access the database through programs on their desktop or through a website application. It's important to understand that while SQL Server must run in a Windows Operating system, the clients do not. They can run almost anything.

SQL Server is controlled by a set of Windows Services. The main service that starts the database is called MSSQLServer. If you see that service name followed by a $ sign and then more letters, your server has been installed more than once, called a Named Instance. An Instance is just a way of having more than one SQL Server running on a single box. Another service you'll see is called SQLServerAgent. This is a service that controls all of the automatic things that run on the server, sort of an AT command or a CRON system if you're used to those programs. If the system has automated maintenance on it (always a good idea) then you should see this service running. If you're just taking over a system, especially in a hurry, don't change anything yet.

The most likely versions you'll see of SQL Server are 2000, 2005 and 2008 and higher. I debated whether to leave the SQL Server 2000 information in this article, since at this writing it's over 11 years old. But I'll leave it here, and separate out the information into its own section. I'll show you how to check your version in a moment. If you do have SQL Server 2000 installed, you need to find out why, and then try to get it upgraded as soon as time allows.

All of the information I'll show you here is applicable to all three versions, and I'll point out the differences where they occur. That's mostly in the tool sets and abilities. For the most part, the versions of SQL Server through 2000 have tools that look similar. Version 2005 and higher uses different tools with different names. For that reason, I'll only briefly tour the graphical tools, and give you commands for the things you'll need to do that work in all three versions.

As I mentioned earlier, within the versions, there are different Editions. The Editions have to do with the limitations and capabilities. They range anywhere from the MSDE engine (in versions through 2000) and the Express edition (2005 and higher), to Personal, Standard, Workgroup (2005 and higher) and then Enterprise and Datacenter Editions. In the tools description below I'll show you how to find out which version you have.

On the security side, SQL Server can use Windows accounts to allow access to the server and then the databases and their objects (such as tables and views) or you can create new logins within SQL Server that don't require Windows accounts.

Tools

There are three methods to control SQL Server. There are graphical tools, you can type in commands, and you can write programs that control the server. Since you're in a hurry, I won't focus on the programmatic methods; I'll save those for another time.

Graphical Tools in Early Versions of SQL Server (2000 and Lower)

If you're not using SQL Server 2000, skip this section. You're in a hurry.

In versions 2000 and lower, the two tools you'll use the most are Enterprise Manager and Query Analyzer. Enterprise Manager is used to control and configure the server and databases. It is a graphical tool with a typical Microsoft Management Console layout, which has objects on the left and containers on the right. As you click an object, different things show up on the right.

You can expand or double-click objects on the left side of the tool and in some cases you're presented with sub-objects. You can right-click almost any object and locate its Properties panel, which will give you a wealth of information about it, and allow you to change the settings. Unless you know what they are for, don't.

If you right-click the Server's name and select View from the menu that appears, you can change the layout on the right-hand side to the TaskPad view. This is a web-like layout that gives you a lot of help along the way.

There are also Wizards in menu bar of Enterprise Manager under the Tools menu item that will guide you through most common tasks such as backup operations or adding users. The wizards are the safest way to perform these tasks when you're new to the platform.

Query Analyzer is also a graphical tool, but it is used primarily to enter commands to SQL Server. The language SQL Server uses is called Transact-SQL, or T-SQL for short. You'll often see it abbreviated that way in some of the resources I'll show you at the end of this article.

When you open Query Analyzer you're presented with a login screen. This wants the server name you want to connect to. If you want the main instance and you're running the tool on the server, just enter (local). That's parenthesis and all. If you have an instance on the server you want to connect to, enter the server name and the instance name like this: SERVERNAME\INSTANCENAME. Case doesn't matter. Remember, you can find that name after the $ in the Services on the Windows Server where SQL Server is running. 

Depending on the settings, you'll see two areas, one on the right, and one on the left. The left are is an object-browser, and you can double-click or right-click objects for actions just as you do in Enterprise Manager.

In the right-hand pane you type commands, and spacing around the commands doesn't matter. Nothing will happen (even if you press ENTER) until you press F5 or click the green triangle. In the menu bar you'll see the name of the database you're in, which is master for the default settings. Anything you type and run happens against that database. You can pull down the menu to select another database.

If you've located the Query Analyzer tool on your server and connected to the database engine, try the following command. Type it just as I have here:

EXEC xp_msver

And then press F5 or click the green triangle. Now you've got three areas on the screen, since the results pane has opened up on the bottom. This command shows you a lot of information about the server, including the version of SQL Server you're using along with the service pack. It's the number following the dot in the return value.

Graphical Tools in SQL Server Versions 2005 and Higher

Since SQL Server 2005 both of the previous tools have been replaced with the SQL Server Management Studio, or SSMS. You can find this tool in the Windows Start menu under the SQL Server grouping – best to run this on the server if you can for this exercise.

When you open SSMS you're presented with a login screen. This wants the server name you want to connect to. If you want the main instance and you're running the tool on the server, just enter (local). That's parenthesis and all. If you have an instance on the server you want to connect to, enter the server name and the instance name like this: SERVERNAME\INSTANCENAME. Case doesn't matter. Remember, you can find that name after the $ in the Services on the Windows Server where SQL Server is running.

SSMS has tabs showing the areas on the tool, and they are configurable. You can leave them on, auto-hide them, or have the menu float. You can also turn them off completely. If that's been done on your system, access the menu bar and on the View item select at least the Object Explorer and Summary panels. If you've inherited the system from someone else, then select the Window menu item and then Reset Window Layout. This will put everything back to standard, and show you what you need to see to start with.

You can right-click almost any item to find commands associated with it. Objects might also have sub-objects in this tool as well, and each menu is different.

You can click the New Query button on the icon bar at the top of the screen to open a panel where you can type T-SQL commands. The commands don't run until you press F5 or click the Execute button.

On the Summary panel to the right you'll see a small menu selection in the top left-hand side. The Report pull-down menu has a great deal of information you can use to find out what is happening on a server or in a database. Start with the Server Dashboard for a lot of information, including the version of what you are looking at.

In SQL Server Management Studio, much of the introduction functionality is embedded in the Tasks menu item when you right-click an object. For instance, if you need to back up a database, right-click that database and select Tasks and then Backup from the floating menu, and then follow the prompts. Once inside any panel you can click the Help button if you don't understand something.

The first thing you should do is to right-click the name of the server (the topmost object) and select Properties from the menu that appears. That will give you the information you need to find the version of the operating system and SQL Server that you are running.

From there, right-click the Server name (properly called an Instance) and select the Standard Reports area. All of these are valuable, but the one I want you to focus on is the Server Dashboard. It has lots of good information, and you can export it for future reference. It will show you the versions and Editions as well.

Next, expand the Databases node on the left and right-click each database in turn, and select Reports and then Standard Reports. Once again, all of these are valuable, but the one to focus on for now (for each database) is the Backup and Restore Events report. You can actually find out about the last backups of a database in lots of ways, but this report will also help you understand the time windows you need to worry about for your system. Note down or export all of this information for later.

Troubleshooting

If you're in a panic situation and something seems to be wrong with the server responding, think long and hard about whether you should try to fix it or not. In many cases, you can actually do more harm than good, so sometimes the best course of action is to pony-up and pay for someone to come in and help. DO NOT do anything to a server until you have checked the backups. You should keep in mind at all times that if you're not an expert and the server is experiencing an issue, there is a really good chance that you could make the system lose data. That's harsh, but it's true.

If you have no choice, or you know that you have good backups and could restore the system if you had to, then you can open Enterprise Manager or SQL Server Management Studio and try to connect to it. If you can't, open the Windows Event Viewer on the server and see if there are any problems that show up regarding SQL Server. Search the Internet for the error code and investigate what's causing the problem.

If the server is running and you're having an issue with a certain database, the first thing you should do is backup all the databases on the server. You can use the wizards in the graphical tools, or use this command on the server's Operating System:

SQL Server 2000 and lower:

osql –E –d master –Q"BACKUP DATABASE foo TO DISK='C:\TEMP\foo.BAK'"

SQL Server 2005 and higher:

SQLCMD –E –d master –Q"BACKUP DATABASE foo TO DISK='C:\TEMP\foo.BAK'"

The case matters this time. The parts you should change are the foo entries. Change those to the name of each database on the server, including master, and msdb. Make sure there is enough room on the hard drive location. You can change the C:\TEMP part to whatever you need, or point it to a share on the network if you have to. If you have to send it across the network, it can take a while.

For the graphical tools, right-click the database name and select Backup (2000 and lower) or All Tasks and then Backup (2005 and higher) from the menu that appears. You can backup to tape or a local or remote disk. Make sure you have enough space to handle the backup; right-click the database name and select Properties or use those reports you exported to find out how big it is now.

We've all heard the jokes about rebooting a Microsoft product when there's a problem. In this case, a reboot really can help. SQL Server tries to find and correct any errors on startup, so once you've made a backup, reboot the server. Check the system using Enterprise Manager or SQL Server Management Studio and see if the databases are all responding properly.

Next, scan the Windows Application Event Logs to locate any warnings or stop events regarding SQL Server. Search those on the web, and don't stop at the first one you find. Some errors show up in several places, so make sure you're fixing the right problem.

Don't do anything without consulting with the users and the developers on that machine. You might read that a service pack fixes the issue you're having, but the developers might be affected if you install it blindly.

Finding More Information

My first recommendation is to visit this site and listen carefully to what my good friend Brent Ozar says about his "Blitz Script". With the basics you've done here, you should be able to follow his instructions - and he's done most of the work for you. Don't copy and paste from the article - use the "Download" script instead: http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/ 

Books Online (Also in the Start menu) is the primary help area for all of SQL Server. It's the complete listing of all topics and also has an index, and it's fully searchable. There's also a Favorites area that works like a browser to record links to the items you found useful.

For SQL Server 2005 and higher, you have a lot more in-product helps. Open SQL Server Management Studio, and then click Help in the menu bar. The two most useful items here are How Do I and Tutorials. When the emergency is over, spend a little time in here to learn what you need to know. If you're still in the emergency, use the Community menu item to ask people on the web to help you out.

You can also find Books Online on the web. Here's the search link for SQL Server 2005, and here's the link for searching SQL Server 2008.

There is, of course, a lot more to managing the server. Once you're out of the problem make sure you get some time to study more. There's a wealth of information right here at InformIT to help you come up to speed quickly.

Other than this guide, search the Informit site for SQL Server. You'll get articles, guides, books and online-books a-plenty to help you learn more. http://www.informit.com/guides/content.aspx?g=windowsserver&seqNum=73 

I have a book on administration for you: Administrator's Guide to SQL Server 2005. Whether you're using SQL Server 2005 or higher, this is a great resource. For 2000, Essential SQL Server 2000: An Administration Handbook work should help you out.

Online Resources

The main site to start with is the SQL Server site at Microsoft. Their site will lead you to maintenance information, recovery processes, capabilities, and just about everything you ever wanted to know about SQL Server. 

There are a host of other resources you can use for SQL Server. I've asked friends to help me with their favorites, and keep in mind most of these have even more links to more sites. This is by no means a complete list, but here are some of the most popular:

http://sqlservercentral.com

http://SQLUniversity.org

http://www.sqlskills.com/

http://brentozar.com/

http://sqlblog.com/ 

http://blog.stackexchange.com/ 

http://www.sqlteam.com/ 

http://www.mssqltips.com/

http://www.sswug.org/

http://blogs.technet.com/b/fort_sql/

http://www.sqlpass.org/