1 / 35

Module 3: SQL Server 2005 Administrative Tools

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

nakia
Download Presentation

Module 3: SQL Server 2005 Administrative Tools

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 3: SQL Server 2005 Administrative Tools

  2. Overview • Using SQL Server Management Studio • Using SQL Computer Manager • Using the sqlcmd Utility • Using the SQL Management Objects

  3. 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

  4. 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

  5. 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

  6. What Is Object Explorer? • Window for browsing and managing objects • Object Explorer folders • Object Explorer buttons

  7. 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

  8. Demonstration: Using Transact-SQL in SQL Server Management Studio In this demonstration, you will see how to create and execute a query

  9. What Is a SQL Server Management Studio Solution? • Collection of connections and queries • Project templates

  10. 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

  11. Demonstration: Creating a Solution with Solution Explorer In this demonstration, you will see how to create a SQL Server Management Studio solution

  12. 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

  13. What Is SQL Computer Manager? • Console snap-in for managing SQL Server services and connectivity • Icons: • Services • Server Network Configuration • Client Network Configuration

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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] ]

  22. 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

  23. 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

  24. 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"

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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

  32. Practice: Using SMO In this practice, you will see how to: • Create an SMO application • Retrieve server information

  33. 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

  34. 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

  35. 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

More Related