Tutorials: Databases: Servers

MySQL

MySQL

What Is SQL Server?

It is the database software (DBMS or Database Management System) that actually does all the work of storing, retrieving, managing, and manipulating data in a database. Microsoft SQL Server is a DBMS—that is, it is database software. Microsoft SQL Server has been around for a long time and is in use at millions of installations worldwide. Why do so many organizations and developers use a Microsoft SQL Server? Here are some of the reasons:

  • Performance— Microsoft SQL Server is fast (make that very fast).
  • Trusted—Microsoft SQL Server is used by some of the most important and prestigious organizations and sites, all of whom entrust it with their critical data.
  • Integration—Microsoft SQL Server is tightly integrated with other Microsoft offerings as well as the Microsoft Azure cloud.
  • Simplicity—Microsoft SQL Server is one of the easiest DBMSs to install and get up and running, and includes administrative tools that make management of the server painless and simple.

So why not use Microsoft SQL Server? First and foremost, Microsoft SQL Server runs only on computer running a Microsoft Windows operating system, and if your computer runs another operating system (such as Linux), then obviously you’ll not be able to use Microsoft SQL Server. In addition, Microsoft SQL Server is a commercial product, and for those interested in no-cost open-source offerings, other DBMSs may be more attractive.

Client Server Software

DBMSs fall into two categories: shared file–based and client/server. The former (which includes Microsoft Access) are designed for desktop use and are generally not intended for use on higher-end or more critical applications.

Databases such as SQL Server, Oracle, and MySQL are client/server–based databases. Client/server applications are split into two distinct parts. The server portion is a piece of software that is responsible for all data access and manipulation. This software runs on a computer called the database server.

Only the server software interacts with the data files. All requests for data, data additions and deletions, and data updates are funneled through the server software. These requests or changes come from computers running client software. The client is the piece of software with which the user interacts. If you request an alphabetical list of products, for example, the client software submits that request over the network to the server software. The server software processes the request; filters, discards, and sorts data as necessary; and sends the results back to your client software.

Note: How Many Computers?

The client and server software may be installed on two computers or on one computer. Regardless, the client software communicates with the server software for all database interaction, be it on the same machine or not.

All this action occurs transparently to you, the user. The fact that data is stored elsewhere or that a database server is even performing all this processing for you is hidden. You never need to access the data files directly. In fact, most networks are set up so that users have no access to the data, or even the drives on which it is stored. Why is this significant? Because to work with SQL Server, you need access to both a computer running the SQL Server software and client software with which to issue commands to SQL Server:

  • The server software is the SQL Server DBMS. You can run a locally installed copy, or you can connect to a copy running on a remote server to which you have access.

  • The client can be SQL Server–included tools, scripting languages, web application development languages, programming languages, and more.

SQL Server Versions

The current version of The current version of Microsoft SQL Server is SQL Server 2016 (although prior versions are in use in many organizations).

 SQL Server Tools

As just explained, SQL Server is a client/server DBMS, so to use SQL Server you need a client (an application that you use to interact with SQL Server), which you use to give it commands to be executed (in database parlance the term execute is used for run). There are lots of client application options, but when learning SQL Server (and indeed, when writing and testing SQL Server scripts) you are best off using a utility designed for simple script execution, and the ideal tool depends on the version of SQL Server being used. As of SQL Server 2005 Microsoft has been including a powerful and sophisticated client tool called Microsoft SQL Server Management Studio. This can be used to create and manage databases and tables, control database access and security, run wizards to optimize and fine-tune DBMS performance, and, of course, run SQL statements.

 Tip: Local or Remote

Microsoft SQL Server Management Studio can be used to connect to local or remote DBMSs. So long as the DBMS is configured to allow you to connect to it, you can connect to any database anywhere.

Getting Started with SQL Server and T-SQL

If you are new to SQL Server and T-SQL, here is what you need to know to get started.

Getting Access To a Server

To start using T-SQL and to follow along with the lessons in this book, you need access to a SQL Server and a client tool. There are three ways to access a server:

The best option is to download and install SQL Server on your own Windows computer.

If your school or place of work has an installed SQL Server, you may be able to use that. The server administrator must grant you a server account (a login name and password) before you’ll be able to use your part of the server for your own work.

SQL Server can also be hosted on popular cloud platforms, including Microsoft Azure, Amazon EC2, and Google Cloud.

Regardless of where the server is installed, you need the server address (it’s your own computer name for a local install) and the login information to access it. After you’re connected, there’s no difference whatsoever in how you use the server.

Tip: If You Can, Install a Local Server

For complete control, including access to commands and features that you will probably not be granted if using someone else’s SQL Server, install your own local server. Even if you end up using another local server as your final production DBMS, you’ll still benefit from having complete and unfettered access to all the server has to offer.

Regardless of whether you use a local server, you need client software (the program you use to actually run T-SQL commands), and as previously noted, Microsoft SQL Server Management Studio is the preferred tool for use with current versions of SQL Server.

Obtaining the Software

To learn more about Microsoft SQL Server, go to http://www.microsoft.com/sql/. This page contains links to trial software and other downloads. You may use any version of SQL Server with this book, including trial versions and the free SQL Server Express.

Note: SQL Server Express

SQL Server Express is a free version of SQL Server intended for personal use and for learning SQL. Previous versions of SQL Server Express were somewhat limited, but this is not the case anymore. The current SQL Server Express is the exact same software as the full-blown commercial version of SQL Server; however, it is limited in the amount of data it can store. But that limit is actually quite large, and so SQL Server Express is perfectly suited (and priced) for learning T-SQL with this book.

Installing the Software

Installing SQL Server is straightforward; the installation wizard walks you through the process, which includes the following options:

  • Setting an installation location. (The default is usually fine.)
  • Installing the documentation. (You are strongly encouraged to do so.)
  • Installing the tools. The most important one is SQL Server Management Studio, so make sure it is selected and installed.
  • Selecting from lots of other options. (You can generally use the default values.)
Note: Locating Microsoft SQL Server Management Studio

If you are using a locally installed copy of SQL Server, then Microsoft SQL Server Management Studio was probably installed when the server was installed. If you don’t have SQL Server Management Studio on your computer, or if you are working with a remote server, don’t worry. You can download the latest version of the tool directly from Microsoft’s website, just search for SQL Server Management Studio.