A number of tools are available to manage a SQL Server instance

BCP

bcp is a command-line utility to both import or export data to/from a database. To import rows 5 to 10 from a data file use:

bcp DATABASE.schema.table in d:\path\to\input\file.txt 
	-e d:\path\to\error.log -m 10 -F 5 -L 10 -c -t "$" -r "\$n" -T

In the above command

DATABASE.schema.table

Specifies the fully-qualified name of the table to import data to

in

'in' specifies that you are importing data to the table: 'out' specifies that you are extracting data from the table

-e

Used to specify a file to store error output

-m

Specifies the number of errors that will cause the processing to terminate and the current batch to be rolled-back

-F

specifies the first row to process from the input file

-L

specifies the last row to process from the input file

-c

specifies that the input file contains character data

-t

used to specify the row terminator

-r

used to specify the line terminator

-T

establish a trusted connection to SQL Server, using the credentials of the account used to run the command

The equivalent command using BULK INSERT is:

BULK INSERT DATABASE.schema.table
FROM 'd:\path\to\input\file.txt'
  WITH (
	FIELDTERMINATOR = '$'
	ROWTERMINATOR = '$\n'
	MAXERRORS = 10,
	FIRSTROW = 5,
	LASTROW = 10,
	ERRORFILE = 'd:\path\to\error.log')

If you're creating an import file using MySQL on linux, use "line terminated by '\$\r\n" to create a ROWTERMINATOR of '$\n', unless your file transfer program supports line-terminator translation.

With the -f option to bcp or the FORMATFILE option for BULK INSERT, you can specify a format file to map fields between the input file and the database table. A format file allows you specify a mapping between fields in data file and the database table, and allows you to specify fields to skip. Format files can be specified in XML or non-XML format.

The -b option to bcp and the equivalent BATCHSIZE option to BULK INSERT, allows you to specify the number of rows to be inserted per batch before a commit is issued. Without a batch size specified, errors during the import will cause the whole import to be rolled-back, with no rows committed

SQL Server Agent

SQL Server Agent provides job scheduling capabilities for SQL Server along with Maintenance Plan Wizards and Alerting.

Jobs allow you to combine multiple tasks into a single package. Job steps can be constructed from:

• TSQL batches
• Replication tasks
• Operating system commands
• Analysis Services tasks
• Integration Services packages
• ActiveX scripts

Jobs normally run in the context of the owner of the job, although a proxy account can be specified instead. Each job can be configured with control flow, logging and notification settings for each job step. Operators are configured as recipients of notifications. To specify an operator for multiple email addresses use a semi-colon separated list.

The SQL Server Agent Maintenance Plan Wizards allow you to configure various common administrative tasks, including:

• Backups
• Shrinking databases
• Re-indexing
• Updating statistics
• Consistency checking

SQL Server Agent Alerts can be configured for SQL Server events and performance counters as well as for WMI events.

SQL Server Agent can be configured to use Database Mail to send notifications to operators. Right-Click the top node of the SQL Server Agent tree in Management Studio, and select 'Properties > Alerts' to enable Database Mail and associate a Mail profile.

You can check that Database Mail is working by right-clicking the Database Mail node in Management Studio and selecting 'Send a Test Message' or by using the stored procedure in msdb:

USE msdb
GO

EXEC sp_send_dbmail 
@profile_name='ProfileName'
@recipients='your.email@example.com'
@subject='Test Message'
@body='This is a test message'

SQLCMD

SQLCMD is a command-line tool provided as a replacement for OSQL

Tablediff

Tablediff.exe can be used to compare data between two tables and generate a script file with commands to bring destination table into synchronisation with source. Primarily used in replication environments

SQLDiag

SQLDiag can be used to collect diagnostic information for support and troubleshooting purposes

Resource Governor

Resource Govenor is used to configure rules to control resource allocation for defined groups

SQL Server Configuration Manager

Configuration Manger is used to control and configure SQL Server services. Configuration Manager can be used to stop or start a service, change account details, configure the startup type for a service and additional parameters. Never use Windows Services to change accounts used to run the SQL Server services, as this will not generate the master service key required to run SQL Server.

Configuration Manager can also be used to configure communication protocols for SQL Server. The use of encryption and whether services respond to enumeration requests can be configured via the properties dialogue of the 'Protocols for MSSQLSERVER' branch of the Configuration Manager. For security reasons, production servers should have 'Hide Instance' set to 'Yes'.

Management Studio

SQL Server 2008 Management Studio is the main tool for managing SQL Server 2008. Management Studio provides a variety of windows:

Registered Servers

Stores connection information

Template Explorer

Used to access predefined templates of SQL commands written in TSQL, MDX, XMLA, or DMX

Object Explorer

Provides access to appropriate actions for all objects in the SQL Server object heirarchy.

Almost all management tasks for SQL Server 2008 can be performed via Management Studio

Profiler

Profiler provides a graphical interface to the SQL Trace API, allowing you to define events and filtering options for profiling

Tuning Advisor

The Database Tuning Advisor (DTA) analyses query workload and recommends indexing and partitioning changes

Performance Studio

Performance Studio is targetted at enterprise-wide performance data and consists of the following components:

Performance Data Warehouse

User defined database to collect enterprise performance data

Data Collectors

Integration Services packages executed by SQL Server Agent scheduler to collect performance data

Performance Reports

Report Designer reports written on the Performance Data Warehouse database