module 3 sql server 2005 administrative tools n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Module 3: SQL Server 2005 Administrative Tools PowerPoint Presentation
Download Presentation
Module 3: SQL Server 2005 Administrative Tools

Loading in 2 Seconds...

play fullscreen
1 / 35

Module 3: SQL Server 2005 Administrative Tools - PowerPoint PPT Presentation


  • 151 Views
  • Uploaded on

Module 3: SQL Server 2005 Administrative Tools. Overview. Using SQL Server Management Studio Using SQL Computer Manager Using the sqlcmd Utility Using the SQL Management Objects. Lesson: Using SQL Server Management Studio. What Is SQL Server Management Studio ? How to Register a Server

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Module 3: SQL Server 2005 Administrative Tools' - nakia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
overview
Overview
  • Using SQL Server Management Studio
  • Using SQL Computer Manager
  • Using the sqlcmd Utility
  • Using the SQL Management Objects
lesson using sql server management studio
Lesson: Using SQL Server Management Studio
  • What Is SQL Server Management Studio?
  • How to Register a Server
  • What Is Object Explorer?
  • How to Execute Transact-SQL Queries
  • Demonstration: Using Transact-SQL in SQL Server Management Studio
  • What Is a SQL Server Management Studio Solution?
  • How to Create a SQL Server Management Studio Solution
  • Demonstration: Creating a Solution with Solution Explorer
what is sql server management studio
What Is SQL Server Management Studio?
  • Integrated management and development environment
  • Based on Visual Studio .NET
  • Incorporates functionality of Enterprise Manager, Query Analyzer, and Analysis Manager in previous releases
  • Used to manage relational databases, Analysis Services, Reporting Services, SQL Server Integration Services, andSQL Mobile databases
  • Includes tools for creating Transact-SQL, XMLA, MDX, and XQuery scripts
how to register a server

Open Registered Servers window

1

Provide SQL Server instance details

2

Test the registered server

3

Verify that the server appears under Registered Servers

4

How to Register a Server
what is object explorer
What Is Object Explorer?
  • Window for browsing and managing objects
  • Object Explorer folders
  • Object Explorer buttons
how to execute transact sql queries

Click New SQL Server Query

1

Type a Transact-SQL statement

2

Click Execute and connect to SQL Server

3

Browse the results

4

How to Execute Transact-SQL Queries
demonstration using transact sql in sql server management studio
Demonstration: Using Transact-SQL in SQL Server Management Studio

In this demonstration, you will see how to create and execute a query

what is a sql server management studio solution
What Is a SQL Server Management Studio Solution?
  • Collection of connections and queries
  • Project templates
how to create a sql server management studio solution
How to Create a SQL Server Management Studio Solution

Click File, New, Project

1

Select the SQL Server Scripts template

2

Add a connection

3

Set the connection properties

4

Create a new query

5

Set the query properties

6

Type the Transact-SQL statements

7

Save the project

8

demonstration creating a solution with solution explorer
Demonstration: Creating a Solution with Solution Explorer

In this demonstration, you will see how to create a SQL Server Management Studio solution

lesson using sql computer manager
Lesson: Using SQL Computer Manager
  • What Is SQL Computer Manager?
  • Demonstration: Using SQL Computer Manager
  • How to Control Services
  • How to View and Change Service Properties
  • How to Manage Server Network Connectivity
  • How to Manage Client Network Connectivity
what is sql computer manager
What Is SQL Computer Manager?
  • Console snap-in for managing SQL Server services and connectivity
  • Icons:
    • Services
    • Server Network Configuration
    • Client Network Configuration
demonstration using sql computer manager
Demonstration: Using SQL Computer Manager

In this demonstration, you will see how to:

  • Start SQL Computer Manager
  • View SQL Server 2005 Services
  • View Server Network Configuration
  • View Client Network Configuration
how to control services
How to Control Services

Start, stop, pause, resume, or restart a service

Open the SQL Computer Manager

1

Expand Services

2

Click a service and select a service instance

3

Click the action

4

how to view and change service properties

Click a service and select a service instance

1

Click Action, Properties

2

Click the Service or Advanced tab

Update the property and click OK

3

4

How to View and Change Service Properties
how to manage server network connectivity

View the Server Network Configuration node

Select an instance and a protocol

1

1

Select an instance and a protocol

Click Action, Properties

2

2

Click Action, Enable or Action, Disable

Modify settings and click OK

3

3

How to Manage Server Network Connectivity

Enable or disable a server protocol

Change address parameters of a server protocol

how to manage client network connectivity

View the Client Network Configuration node

Click Client Protocols and select a protocol

1

1

Click Client Protocols and select a protocol

Click Action, Properties

2

2

Click Action, Enable or Action, Disable

Modify settings and click OK

3

3

How to Manage Client Network Connectivity

Enable or disable a client protocol

Change parameters of a client protocol

lesson using the sqlcmd utility
Lesson: Using the sqlcmd Utility
  • What Is the sqlcmd Utility?
  • sqlcmd Command-Line Switches
  • How to Use the sqlcmd Utility Interactively
  • How to Execute Scripts Using the sqlcmd Utility
  • How to Use Variables With the sqlcmd Utility
  • Demonstration: Using Variables with the sqlcmd Utility
  • How to Use a Dedicated Administrator Connection
what is the sqlcmd utility
What Is the sqlcmd Utility?
  • Command-line tool for executing Transact-SQL statements and scripts
    • Uses OLE DB to run Transact-SQL batches
    • Replaces osql
  • Enhancements over osql
    • Variables
    • Query server information
    • Passes error information to calling environment
    • Dedicated Administrator Connection
    • Commands
sqlcmd command line switches
sqlcmd Command-Line Switches

sqlcmd

[-?] | [-L[c]] |

[

{

{-U login_id [-P password]} | –E

}

[-S server_name[\instance_name]] [-H wksta_name] [-d db_name]

[-l time_out] [-t time_out] [-h headers]

[-s col_separator] [-w column_width] [-a packet_size]

[-e] [-I]

[-c cmd_end] [-q "query"] [-Q "query"]

[-m error_level] [-r [0 | 1]]

[-i input_file[,file2…]] [-o output_file] [-p[1]]

[-b] [-u] [-R] [-v var="value"[var="value"…]][-A] [-X[1]]

[-V severitylevel]

]

how to use the sqlcmd utility interactively

Open a command prompt window

1

Execute sqlcmd

2

Type Transact-SQL statements and sqlcmd commands

3

Type GO

4

Type QUIT to close sqlcmd

5

How to Use the sqlcmd Utility Interactively
how to execute scripts using the sqlcmd utility

Create a file containing Transact-SQL statements and sqlcmd commands

1

Set the connection environment variables

2

Invoke sqlcmd specifying –i and –o switches

3

Examine ERRORLEVEL

4

Examine the output file

5

How to Execute Scripts Using the sqlcmd Utility
how to use variables with the sqlcmd utility

Create a sqlcmd script referencing variables

1

Invoke sqlcmd with –v to define the variables, or use environment variables

2

How to Use Variables with the sqlcmd Utility

SELECT $(colname)

FROM $(tabname)

GO

sqlcmd –i MyScript.sql –o MyScript.out –v colname="name" tabname="sys.databases"

demonstration using variables with the sqlcmd utility
Demonstration: Using Variables with the sqlcmd Utility

In this demonstration, you will see how to:

  • Reference variables in a script
  • Instantiate variables from the command line
  • Use environment variables
how to use a dedicated administrator connection

Connect using sqlcmd -A

1

Diagnose the problem, terminate misbehaving connections, or shut down cleanly

2

How to Use a Dedicated Administrator Connection
  • Useful if the server hangs or is unresponsive
    • DAC runs using its own private scheduler

1> CHECKPOINT

2> GO

1> SHUTDOWN WITH NOWAIT

2> GO

Server shut down by request

lesson using the sql management objects
Lesson: Using the SQL Management Objects
  • What Are the SQL Management Objects?
  • SMO Compared to SQL Server Distributed Management Objects
  • How to Create SMO Applications
  • How to Use SMO to Retrieve Server Information
  • How to Use SMO to Back Up a Database
  • Demonstration: Using SMO
what are the sql management objects
What Are the SQL Management Objects?

Server

ConnectionContext

JobServer

Databases

Jobs

Database

Job

Assemblies

Alerts

SqlAssembly

Alerts

FileGroups

Shared Schedules

FileGroup

Job Schedule

Tables

Table

smo compared with sql server distributed management objects
SMO Compared with SQL Server Distributed Management Objects
  • SMO is a replacement for SQL-DMO
  • SQL-DMO retained only for backward compatibility
  • SMO uses an administrator-centric view
  • SMO and WMI
    • For monitoring and configuring servers
  • SMO Scripting
    • Scripter class provides enhanced scripting features
how to create smo applications

Open Visual Studio 2005

1

Create a new project

2

Reference the SQL SMO assemblies

3

Import the SMO namespaces

4

Connect to the computer running SQL Server

Code your application

5

6

How to Create SMO Applications
how to use smo to retrieve server information
How to Use SMO to Retrieve Server Information

Use the properties of the Server.Information class

  • Edition
  • IsClustered
  • IsSingleUser
  • Language
  • NetName
  • OSVersion
  • Parent
  • PhysicalMemory
  • Platform
  • Processors
  • Product
  • ProductLevel
  • VersionString

strEdition = myServer.Information.Edition

practice using smo
Practice: Using SMO

In this practice, you will see how to:

  • Create an SMO application
  • Retrieve server information
how to use smo to back up a database

Connect to SQL Server

1

Declare and instantiate a Backup object

2

Set properties of Backup object

3

Call Devices.Add method of Backup object

4

Call SqlBackup method of Backup object

5

How to Use SMO to Back Up a Database
demonstration using smo
Demonstration: Using SMO

In this demonstration, you will see how to:

  • Use SMO to connect to a SQL Server
  • Use SMO to list databases on a SQL Server
  • Use SMO to create a new database
lab 3 administering sql server 2005
Lab 3: Administering SQL Server 2005
  • Exercise 1: Creating the Personnel Database
  • Exercise 2: Populating the Personnel Database
  • Exercise 3: Backing up the Personnel Database