Managing Your SQL Servers
- 2/15/2012
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.
)
Figure 1-1 Use SQL Server Management Studio to perform core administration tasks.
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.
Security. Manage SQL Server logins, server roles, stored credentials, cryptographic providers, and auditing.
Server objects. Configure backup devices, HTTP endpoints, linked servers, and server triggers.
Replication. Configure distribution databases, update replication passwords, and launch Replication Monitor.
Management. View SQL Server logs, create, view, and manage maintenance plans, Microsoft Distributed Transaction Coordinator (MSDTC), and Database Mail. Configure data collection, Resource Governor, and Policy-Based Management policies.
SQL Server Agent. Configure SQL Server Agent jobs, alerts, operators, proxies, and error logs.
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.
)
Figure 1-2 Use the Database Engine to access core SQL Server components and features.
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.
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.
Figure 1-3 Connect to a specific database.
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.
)