Lesson 2. SQL Server Basics
There are some basics you should know when getting started with SQL Server. Knowledge of these basics will help you to make decisions about whether the product is right for you and will help you get started working with it. This lesson covers:
The available versions of SQL Server 2014
Components included with SQL Server
The basics of working with SQL Server Management Studio
How to connect to a database server
How to install the sample files
Versions of SQL Server 2014 Available
Microsoft recognizes that there is a plethora of database users with a large variety of disparate needs and has released the following six versions of SQL Server 2014:
SQL Server 2014 Express Edition
SQL Server 2014 Web Edition
SQL Server 2014 Business Intelligence Edition
SQL Server 2014 Standard Edition
SQL Server 2014 Enterprise Edition
SQL Server 2014 Express Edition
SQL Server 2014 Express provides a great means of getting started with SQL Server. It offers a robust, reliable, stable environment that is free and easy to use. It provides the same protection and information management provided by the more sophisticated versions of SQL Server. Other advantages of SQL Server Express include the following:
Is easy to install
Has a lightweight management and query editing tool
Includes support for Windows authentication
Features “Secure by Default” settings
Has royalty-free distribution
Offers rich database functionality, including triggers, stored procedures, functions, extended indexes, and Transact-SQL support
Includes XML support
SQL Server 2014 Express has a few disadvantages that make it unusable in many situations, including the following:
Support for only 1GB of RAM
Support for a 10GB maximum database size
Support for only one CPU
Absence of the SQL Agent Job Scheduling Service
Absence of the SQL Profiler
Absence of the Database Tuning Advisor
SQL Server 2014 Web Edition
SQL Server Web Edition is designed to support the workloads associated with Internet databases. It enables you to easily deploy web pages, applications, websites, and services. Other valuable features include the fact that it supports 64GB of RAM and a 524-petabyte (PB) database size. The main disadvantage of SQL Server 2014 Web Edition is that it does not include the SQL Profiler.
SQL Server 2014 Business Intelligence Edition
SQL Server 2014 Business Intelligence Edition enables you to deploy secure, scalable, and manageable self-service corporate business intelligence solutions. Valuable features include the fact that it supports 128GB of RAM and a 524PB database size.
SQL Server 2014 Standard Edition
SQL Server Standard Edition provides an affordable option for small- and medium-sized businesses. It includes all functionality required for noncritical e-commerce, data warehousing, and line-of-business solutions. The advantages of SQL Server 2014 Standard Edition include the following:
Supports up to 128GB of RAM
Supports a database size up to 524PB
Has database mirroring
Includes failover clustering
Includes the Database Tuning Advisor
Includes the full-featured Management Studio
Ships with the Profiler
Includes the SQL Agent Job Scheduling Service
The disadvantages of SQL Server 2014 Standard Edition are that it:
Supports only four CPUs
Does not support online indexing
Does not support online restore
Does not support fast recovery
SQL Server 2014 Enterprise Edition
SQL Server 2014 Enterprise Edition includes all of the tools you need to manage an enterprise database management system. It offers a complete set of enterprise management and business intelligence features and provides the highest levels of scalability and availability of all the SQL Server 2014 editions. It supports an unlimited number of CPUs and provides all of the features unavailable in the other versions of SQL Server 2014.
SQL Server Components
SQL Server includes numerous components that facilitate the process of creating, working with, and maintaining SQL Server databases. The components covered in this lesson include the following:
SQL Server Management Studio
SQL Profiler
SQL Server Agent
Database Tuning Advisor
SQL Server Management Studio is covered later in detail in the “Introduction to Microsoft SQL Server Management Studio” section. The other components are covered here.
SQL Profiler
You use the SQL Profiler to create and manage traces and to analyze and replay trace results. Most often, you use the SQL Profiler to troubleshoot database performance issues.
Using the SQL Profiler, you can accomplish the following:
Step through queries.
Diagnose slow-running queries.
Capture the T-SQL statements that are causing the problem.
Monitor performance.
When using the SQL Profiler, you can specify exactly which database events you want to capture. For example, you can trace when TSQL statements start and complete, when stored procedures start and complete, and a plethora of other events (see Figure 2.1). You can save your traces so that you can run them at any time. Lesson 23, “Performance Monitoring,” covers the details of working with the SQL Server Profiler.
SQL Server Agent
The SQL Server Agent enables you to easily schedule administrative tasks called jobs. A job is a series of actions that the agent performs at specified times, when the agent starts, one time, or when the CPU utilization of the server is considered idle.
You generally use the SQL Server Agent to perform backups, update indexes, or perform other tasks that either protect or maintain your database and its objects. The SQL Server Agent appears as a node of the Object Explorer. When you expand the node, it appears as in Figure 2.2. Using this node, you can view, create, and manage jobs. Lesson 22, “Maintaining the Databases That You Build,” covers the details of the SQL Server Agent.
FIGURE 2.2 The SQL Agent enables you to set up and maintain SQL Server jobs that perform a series of tasks.
Database Engine Tuning Advisor
The Database Engine Tuning Advisor enables you to optimize database performance without requiring you to have an expert understanding of the database and SQL Server. The Database Engine Tuning Advisor can perform the following tasks:
Recommend the best mix of indexes
Recommend indexed views when applicable
Analyze the effects of any proposed changes
Recommend ways to tune the database
Provide reports that summarize the effects of implementing recommendations
Figure 2.3 shows the Database Tuning Advisor. Lesson 22 covers the Database Tuning Advisor in additional detail.
FIGURE 2.3 The Database Engine Tuning Advisor enables you to optimize the performance of your database.
Introduction to Microsoft SQL Server Management Studio
Management Studio is a tool that enables you to create, work with, and manage database objects. In the sections that follow, you explore the various nodes available in Management Studio and find out what is available under each node.
The Databases Node
The Databases node is the first node in SQL Server Management Studio. Within the Databases node are one or more subnodes. The first subnode is System Databases. There are additional subnodes for each database contained on the server (see Figure 2.4). The sections that follow cover each of the system databases under the System Databases subnode.
The Master Database
The master database is the “database of all databases.” It keeps track of logon accounts, linked servers, system configuration settings, and more. It also contains initialization settings for SQL Server.
The Model Database
Model is a special database. Anything you place in Model is automatically propagated to all the databases you create thereafter. This means that, for example, you can add a State table to Model. That State table then appears in all the new databases you build. You work with Model just as you work with any other database. You can include almost any object in Model. This means that you easily can propagate tables, views, stored procedures, triggers, functions, and more. This not only provides you with standardization between databases, but provides you with a great jump start on creating the databases you need. If you modify Model, you do not affect any existing databases. All new databases will be affected by your changes.
The MSDB Database
The MSDB (stands for Microsoft Database) database is used by SQL Server, SQL Server Management Studio, and SQL Server Agent. All three of them use it to store data, including scheduling information and backup and restore history information. For example, SQL Server maintains a complete backup and restore history in MSDB. There are several ways that you can add to or modify information stored in the MSDB database. They include:
Scheduling tasks
Maintaining online backup and restore history
Replication
The TempDB Database
TempDB is a system database that acts as a resource to all users working with a particular instance of SQL Server. TempDB holds the following objects:
Temporary user objects such as temporary tables, temporary stored procedures, temporary table variables, or cursors
Internal objects used by the database engine to perform tasks such as sorting
Row versions that are generated in data modification transactions
The Security Node
As its name implies, the Security node enables you to manage SQL Server security. Using the Security node, you can work with logins, add to and remove people from server roles, and create credentials. This lesson provides an introduction to security. For more information, see Lesson 19, “Authentication,” and Lesson 20, “SQL Server Permissions Validation.”
The Logins Node
Logins represent the users and roles that have access to your system. Two types of icons appear under the Logins node. One is granting a role access to the database, and the other is granting a user access to the database.
The Server Roles Node
Server Roles are predefined roles supplied by SQL Server. Each Server Role possesses a pre-defined set of rights. Figure 2.5 shows the available Server Roles. You cannot add or remove Server Roles.
The Credentials Node
A credential is a record that contains the authentication information required for SQL Server to connect to an outside resource. Most credentials are made up of a Windows login and password.
Server Objects Node
Server Objects refer to a set of objects used at the server level (not at the database level). These objects include backup devices, linked servers, and server triggers.
Backup Devices
Backup devices include the tapes and disks you use to back up or restore your SQL Server. When creating a backup, you must designate the backup device you want to use (see Figure 2.6). You select from a list of backup devices you have created.
Linked Servers
Linked servers enable you to work with other SQL Servers, as well as databases other than SQL Server databases, right from within Management Studio. This offers a few advantages:
The capability to get remote server access
The capability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise
The capability to address diverse data sources in a similar manner
Server Triggers
Server triggers are DDL (Data Definition Language) triggers. They execute in response to changes being made to the structure of the database. They are great for both auditing and regulating database operations. For example, if SQL Server determined that there were more than a specified number of records in a table, it would not allow users to remove fields from the table.
The Replication Node
Data replication is the capability of a system to automatically make copies of its data and application objects in remote locations. You can easily propagate any changes to the original or data changes to the copies to all the other copies. Data replication allows users to make changes to data offline at remote locations. SQL Express synchronizes changes to either the original or the remote data with other instances of the database.
The original database is the design master. You can make changes to definitions of tables or other application objects only at the design master. You use the design master to make special copies called replicas. Although there is only one design master, replicas can make other replicas. The process of the design master and replicas sharing changes is synchronization.
To see an example of data replication at work, say you have a team of salespeople who are out on the road all day. At the end of the day, each salesperson logs on to one of the company’s servers through Terminal Services. The replication process sends each salesperson’s transactions to the server. If necessary, the process sends any changes to the server data to the salesperson.
This example illustrates just one of the several valuable uses of replication. In a nutshell, data replication is used to improve the availability and integrity of data throughout an organization or enterprise. The practical uses of data replication are many.
Management Node
The Management node contains tools that help you to manage your SQL Server. These tools include the capability to view both the SQL Server Logs and the Activity Monitor.
SQL Server Logs
SQL Server 2014 adds entries for certain system events to the SQL Server Error Log and to the Microsoft Windows application log. You can use these logs to identify the sources of problems. Using the SQL Server Management Studio Log File Viewer, you can integrate SQL Server, SQL Server Agent, and the Windows logs into a single list, making it easy to review all related events.
Activity Monitor
You use the Activity Monitor component of SQL Server Management Studio to get information about users’ connections to the database engine and the locks they hold.
Connecting to a Database Server
Before you can work with a database server and the objects it contains, you must first connect to it. Notice that when you launch Management Studio, the Connect to Server dialog box appears (see Figure 2.7). Generally, all you need to do is to verify that the server name is correct and then click Connect.
FIGURE 2.7 The Connect to Server dialog box enables you to specify connection information for the database.
Another method you can use to connect to a database server is to click Connect in the Object Explorer and then select Database Engine (see Figure 2.8). The Connect to Server dialog box appears, enabling you to modify the Server Name and Authentication mode.
Installing the Sample Files
This course utilizes the AdventureWorks2014 sample database for almost all its examples. You must install the database before you can work with it.
There are many different websites that provide a link that enables you to download the AdventureWorks2014 database. One of such links is https://msftdbprodsamples.codeplex.com/releases/view/125550. This link downloads a file called Adventure Works 2014 Full Database Backup.zip. Take the following steps to install the AdventureWorks2014 database on your computer:
1. Double-click the zip file to open it. A file called AdventureWorks2014.bak appears.
2. Place the AdventureWorks2014.bak file in the desired folder on your computer or network (see Figure 2.9).
3. In Management Studio, right-click the Databases node and select Restore Database. The Restore Database dialog box displays (see Figure 2.10).
FIGURE 2.10 The Restore Database dialog box enables you to specify information about the database you want to restore.
4. Click to select Device and then click the ellipsis (…). The Select Backup Device dialog box displays (see Figure 2.11).
FIGURE 2.11 The Select Backup Device dialog box enables you to designate the backup you want to restore.
5. Click Add. The Locate Backup File dialog box displays. Locate the folder and file that contains the backup file (see Figure 2.12) and then click OK. The Select Backup Devices dialog box displays.
6. Click OK.
7. Click OK one more time. You should receive a message that the file restored properly.
8. Right-click the Databases node and select Refresh. You should now see the AdventureWorks2014 database (see Figure 2.13).
Q&A
Q. Discuss the advantages of SQL Server Express.
A. SQL Server Express is free and is easy to use. It provides the same protection and information management provided by more sophisticated versions of SQL Server. It is easy to install, provides rich database functionality, and sports deep integration with Visual Studio 2013.
Q. Name some limitations of SQL Server Express.
A. SQL Server Express limits you to 1GB of RAM, 10GB maximum database size, and support for only one CPU. Furthermore, it does not come with either a job scheduling server or a database tuning advisor.
Workshop
Quiz
1. Name the version of SQL Server designed for small businesses or departments in larger enterprises.
2. What versions of SQL Server support 524PB databases?
3. SQL Server Enterprise Edition enables you to utilize all memory installed on the server (true/false).
4. You are prompted for authentication model during the installation of the SQL Server Express database engine (true/false).
5. Name the management tool you use to manage your databases.
6. Name the tree view that enables you to view the objects managed by your SQL Server.
Quiz Answers
1. SQL Server 2005 Standard Edition.
2. Standard, Business Intelligence, and Enterprise.
3. True.
4. True.
5. SQL Server Management Studio.
6. Object Explorer.
Activities
Download SQL Server Express Edition. Install both the database engine and SQL Server Express Management Studio. Launch Management Studio and practice expanding and contracting the nodes of the Object Explorer. Select different nodes and view the summary information. Finally, take the steps in this lesson to install the sample AdventureWorks2014 database.