In this chapter from Microsoft SQL Server 2012 Pocket Consultant, you will learn how to use SQL Server Management Studio.

Using SQL Server Management Studio

Managing SQL Server Groups

Managing Servers

Using Windows PowerShell for SQL Server Management

Starting, Stopping, and Configuring SQL Server Agent

Starting, Stopping, and Configuring MSDTC

Managing SQL Server Startup

Managing Server Activity

Microsoft SQL Server Management Studio is the primary tool you use
to manage databases and servers. Other tools available to manage local
and remote servers include SQL Server PowerShell, SQL Server
Configuration Manager, Database Engine Tuning Advisor, and SQL Server
Profiler. You use SQL Server Configuration Manager to manage SQL Server
services, networking, and client configurations. Database Engine Tuning
Advisor is available to help optimize indexes, indexed views, and
partitions, and SQL Server Profiler lets you examine events generated by
SQL Server, which can provide helpful details for troubleshooting. In
this chapter, you will learn how to use SQL Server Management Studio.
SQL Server Configuration Manager is discussed in Chapter 2. For details on
tuning and tracing, see Chapter 12.

Whenever you’re working with databases and servers, keep in mind
these concepts to help ensure your success:

Contained
databases. These databases are fully or partially isolated databases
that have no configuration dependencies on the instance of the SQL
Server Database Engine where they are installed. A fully contained
database does not allow any objects or functions that cross the
boundary between the application model and the
Database Engine instance. A partially contained database allows
objects or functions that cross the boundary between the
application model and the Database Engine instance. Contained
database users with passwords are authenticated by the database.
Authorized Microsoft Windows users and group members can connect
directly to the database and do not need logins in the master
database.

FileTable. Table structures act as virtual shares by storing FILESTREAM
data and directory data as rows within tables. Even though the
Database Engine manages the data at all times, a FileTable appears
as a Windows share for non-transactional file access, allowing you
to use MOVE, XCOPY, and other standard commands to load files when
you are working with the command line or a batch script. The root
of the hierarchy is established when you create the FileTable. A
FileTable cannot be replicated or selected into like other
tables.

Indirect
checkpoints. Checkpoints are triggered based on the targeted recovery
time you specify for a database, as opposed to automatic
checkpoints, which are based on the maximum number of log records
that can be processed in a particular recovery interval. A
database that has a targeted recovery time does not use automatic
checkpoints. Although indirect checkpoints can reduce read/write
spikes by continually writing in the background, this continuous
writing increases the total write load for the server instance,
which may degrade performance for online transactional
workloads.

You also should be aware of changes to the way the Database Engine
works. While there are many discontinued and deprecated features,
remember these important changes:

Databases must be set to at least compatibility level 90.
Level 90 is for Microsoft SQL Server 2005. Any earlier database is
updated automatically when you install Microsoft SQL Server
2012.

Indexes containing varchar(max),
nvarchar(max), and varbinary(max)
columns can now be rebuilt as an online operation.

Re-create triggers that have WITH APPEND clauses, as these are
no longer supported. Do the same for COMPUTE and COMPUTE BY, which
must be rewritten by using the ROLLUP clause.

Replace remote servers by using linked servers, and replace
aliases with user accounts and database roles as appropriate.

Replace the usage of SQL Mail with Database Mail and use ALTER
DATABASE instead of sp_dboption.

Use two-part table names following the syntax
schema.object with ALTER TABLE, rather than
four-part names, such as
server.database.schema.table.

Using SQL Server Management Studio

The SQL Server Management Studio graphical point-and-click
interface makes server, database, and resource management easy to
perform. Using SQL Server Management Studio, you can manage local and
remote server instances by establishing a connection to a SQL Server instance and
then administering its resources. If you have disabled remote server
connections to a particular server, you can work only with the server
locally (by logging in to the system at the keyboard or by
establishing a remote Terminal Server session in Windows and then
running the local management tools).

Getting Started with SQL Server Management Studio

When you start working with SQL Server Management Studio, you
see the Object Explorer view, shown in Figure 1-1. If this view
is not displayed, you can access it (and other views) from the View
menu. The following descriptions explain how to use each
view:

Object
Explorer. Allows you to view and connect to instances of SQL
Server, Analysis Services, Integration Services, and Reporting
Services. Once you have connected to a particular server, you
can view its components as an object tree and expand nodes to
work your way to lower levels of the tree.

Registered
Servers. Shows the currently registered servers. Use Registered
Servers to preserve login information for servers that you
access frequently. The top bar of the view allows you to
switch quickly between servers of a particular type (SQL
Server, Analysis Server, Integration Server, or Report
Server).

Template
Explorer. Provides quick access to the default Query Editor
templates, organized by action, and any custom templates you
create. You can create templates in any script language
supported by SQL Server Management Studio, SQL Server, and
Analysis Server.

Solution
Explorer. Provides quick access to existing SQL Server and
Analysis Server projects. A project details the connections,
queries, and other functions that are performed when the
project is executed.

To run SQL Server Management Studio, click Start, type
ssms.exe in the Search box, and
then press Enter. Alternatively, select the related option on the
Microsoft SQL Server 2012 menu. Next, you must connect to the server
you want to work with. There are several ways to do this:

Connect using a standard login to a server
instance.

Connect using a login to a specific database.

Connect using server groups and registered servers.

Connecting to a server instance allows you to work with that
particular server and its related components. (See Figure 1-2.) Typically,
you want to connect to a server’s Database Engine. The Database
Engine gives you access to the following components and
features:

Databases. Manage system databases, including the
master and model
databases, as well as user databases and database snapshots.
If you’ve installed Reporting Services, you also can access
the ReportServer and Report
ServerTempDB databases under this node.

You store server and login information by using the Registered
Servers feature. Registered servers can be organized using server
groups and then can be accessed quickly in the Registered Servers
view. Methods to manage server groups and register servers are
discussed in the Managing SQL Server Groups and
Managing Servers sections later in this
chapter.

Connecting to a Specific Server Instance

To connect to a specific server instance by using a
standard login, follow these steps:

Start SQL Server Management Studio. In the Connect To
Server dialog box, use the Server Type list to select the
database component you want to connect to, such as Database
Engine. (If you exited the Connect To Server dialog box, you can
display the Connect To Server dialog box by clicking File,
Connect Object Explorer in SQL Server Management Studio.)

In the Server Name box, type the fully qualified domain
name (FQDN) or host name of the server on which SQL Server is
running, such as EngDBSrv12.cpandl.com or EngDBSrv12, or select
Browse For More in the related drop-down list. In the Browse For
Servers dialog box, select the Local Servers or Network Servers
tab as appropriate. After the instance data has been retrieved,
expand the nodes provided, select the server instance, and then
click OK.

TIP

The list in the Browse For Servers dialog box is
populated by the SQL Server Browser service running on the
database servers. There are several reasons that a SQL Server
instance you want to work with might not be listed. The SQL
Server Browser service might not be running on the computer
running SQL Server. A firewall might be blocking User Datagram
Protocol (UDP) port 1434, which is required for browsing. Or
the HideInstance flag might be set on the SQL Server
instance.

Use the Authentication list to choose the option for
authentication type, which is either Windows Authentication or
SQL Server Authentication (based on the authentication types
selected when you installed the server). Provide a SQL Server
login ID and password as necessary.

Windows
Authentication. Uses your current domain account and password to
establish the database connection. This authentication
type works only if Windows authentication is enabled and
you have appropriate privileges.

SQL Server
Authentication. Allows you to specify a SQL Server login ID and
password. To save the password so that you do not have to
reenter it each time you connect, select Remember
Password.

Click Connect. Now you can use the Object Explorer view to
work with this server.

Connecting to a Specific Database

To connect to a specific database by using a standard login,
follow these steps:

Start SQL Server Management Studio. In the Connect To
Server dialog box, use the Server Type list to select the
database component you want to connect to, such as Database
Engine, and then, in the Server Name box, type the FQDN or host
name of the server on which SQL Server is running, such as EngDBSrv12.cpandl.com or EngDBSrv12. (If
you exited the Connect To Server dialog box, you can display the
Connect To Server dialog box by clicking File, Connect Object
Explorer in SQL Server Management Studio.)

Use the Authentication list to choose the option for
authentication type, which is either Windows Authentication or
SQL Server Authentication (based on the authentication types
selected when you installed the server). Provide a SQL Server
login ID and password as necessary.

Click Options to display the advanced view of the Connect
To Server dialog box. Select the Connection Properties tab,
shown in Figure 1-3.

In the Connect To Database box, type the name of the
database you want to connect to, such as Personnel, or select
Browse Server in the related drop-down list. When prompted,
click Yes to establish a connection to the previously designated
server. In the Browse Server For Database dialog box, select the
database you want to use, and then click OK.

Using the Network Protocol list, select the network
protocol and any other connection properties if you are prompted
to do so. Shared Memory is the default network protocol for
local connections. TCP/IP is the default for remote connections.
If you want, establish a secure connection by selecting the
Encrypt Connection check box.

Click Connect. You are now able to work with the specified
database in the Object Explorer view.